Skocz do zawartości


Close Open
Close Open
Zdjęcie
- - - - -

Excel - sumowanie ze wskazanych arkuszy


  • Please log in to reply
9 replies to this topic

#1 MadDogOfHell

MadDogOfHell

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 259 Postów:

Napisany 31 sierpień 2006 - 14:03

Witam serdecznie.
Przygotowuję model budżetowy. Jak to w modelach bywa, mam kilka arkuszy, w jednakowym układzie, dotyczące poszczególnych grup pracowników.

Arkuszy jest w sumie 6.

Chodzi o to, żeby zsumować dane z tych arkuszy, czyli zagregować dane.

Można prosto:
=suma(odwołanie1;odwołanie2)

Chodzi o to, że tych sposobów agregacji będzie kilka (czyli będzie arkusz 1 z 3, później 1, 4 i 5 itp). Potrzebuję formułę, która będzie z zakresu pobierała nazwy arkuszy, które ma zsumować i sumować dane znajdujące się w komórce o tym samym adresie.

Wymyśliłem coś takiego - jako formułę tablicową:

=SUMA(ADR.POŚR(PRZESUNIĘCIE($C$8;1;1;;$B$8)&"!W"&WIERSZ()&"K"&NR.KOLUMNY();FAŁSZ))

Gdzie przesunięcie() - to odwołanie do nazw arkuszy $B$8 - liczba arkuszy do sumowania.


Ale zwraca mi #ADR.

Jakieś pomysły ?
Mad Dog Of Hell
________________________
Motto życiowe [nowe wskutek działania cenzury]:

Czas to pieniądz,
Bo pieniądz jest czasem,
.
.
A czasem go nie ma

#2 ryba191

ryba191

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 346 Postów:

Napisany 31 sierpień 2006 - 16:16

Witaj.
Wydaje mi sie, ze popelniles blad w samej definicji funkcji przesuniecia, zabraklo Ci parametru "wysokosc", ponizej definicja z helpa:
PRZESUNIĘCIE(odwołanie;wiersze;kolumny;wysokość;szerokość)
- wszystkie parametry sa wymagane.

No i nie do konca wiem co znaczy ten wykrzyknik przed literka "W", ale wiem, ze bez niego powinno dzialac:)
Pozdrawiam

#3 Mały_Rycho

Mały_Rycho

    eremita

  • Forumowicze
  • PipPipPipPipPipPipPip
  • 2014 Postów:
  • Płeć:Mężczyzna
  • Lokalizacja:Sulęcin

Napisany 31 sierpień 2006 - 17:25

Hej.
A sprawdziłeś czy formuła np.
=SUMA(Arkusz2:Arkusz4!W12K2)
daje poprawny wynik? :( Nie wybrałeś przecież, w opcjach arkusza, stylu odwołania w1k1 a i 'mieszac' też nie można.
Poprawnie byłoby =SUMA(Arkusz2:Arkusz4!B12)

Wydaje się, że właściwe odwołanie można uzyskać formułą:
=ADRES(WIERSZ();NR.KOLUMNY();;;$B$8)
i jeśli w B8 umieści się tekst: Arkusz2:Arkusz4
to formuła (likwidująca dodatkowo niepotrzebnie wstawione apostrofy) mogła by wyglądać tak:
=SUMA(ADR.POŚR(PODSTAW(ADRES(WIERSZ();NR.KOLUMNY();;;$B$8);"'";"")))
ale tu też 'kicha' :(

=SUMA(ADR.POŚR("Arkusz2:Arkusz4!B12"))
też jest błędna, bo przecież ADR.POŚR zwaca odwołanie wyłącznie do zakresu w jednym arkuszu :(

I tu stanąłem a i chyba nie do końca rozumiem ideii wpisywania nazw arkuszy 'do sumy'.
Jak wrócę do domku, to późnym wieczorem, wrócę do tego tematu.
Pozdrawiam
Mały Rycho

Jeśli mówisz prawdę, nie musisz niczego pamiętać. Mark Twain.

#4 MadDogOfHell

MadDogOfHell

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 259 Postów:

Napisany 01 wrzesień 2006 - 11:47

Ryba - 2 ostatnie parametry nie są wymagane w przesunięciu.
Wykrzyknik daje się po nazwie arkusza, do którego sięgamy.

No :) Rycho - wyzwanie, jak to sprzed roku :) ciężkie.
suma(arkusz2:arkusz4!...) daje sumę z arkuszy znajdujących się między tymi arkuszami, a nie tylko tych dwóch.
A jak napisałem, chciałbym określić, z jakich arkuszy ma brać dane.

Co do odwołania W1K1 - wartość parametru "fałsz" w adr.pośr określa sposób odwołania.

Koncepcja była taka:
1. Wpisuję w jakimś zakresie (kilka komórek w wierszu) nazwy arkuszy.
2. Tworzę adres pośredni z wykorzystaniem nazw, oraz W (nr bieżącego wiersza) K (nr bieżącej kolumny). Odwołanie ustawiam na W1K1 parametrem FAŁSZ.
3. Sumuję zwrócone wartości, a jako że nazwy są w tablicy, to wrzucam Ctrl+Shift+Enter zapisując formułę tablicową.
Można też wykorzystać f-cję ADRES, która zwróci odwołanie, ale zwraca to tekst typu: "Arkusz2!A12", czyli muszę użyć ADR.POŚR, żeby wydobyć wartość, a to już nie do końca ma sens, bo to o jeden zbędny krok więcej.

A co do koncepcji wpisywania arkuszy - robiąc kilka różnych agregacji danych, przygotowuję 1 szablon arkusza i kopiuję go tyle razy ile muszę zrobić agregacji, po czym wprowadzam nazwy arkuszy, które mają być zagredowane i nie przejmuję się niczym.

Pozdrawiam
Mad Dog Of Hell
________________________
Motto życiowe [nowe wskutek działania cenzury]:

Czas to pieniądz,
Bo pieniądz jest czasem,
.
.
A czasem go nie ma

#5 MadDogOfHell

MadDogOfHell

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 259 Postów:

Napisany 01 wrzesień 2006 - 11:49

Ryba - 2 ostatnie parametry nie są wymagane w przesunięciu.
Wykrzyknik daje się po nazwie arkusza, do którego sięgamy.

No :) Rycho - wyzwanie, jak to sprzed roku :) ciężkie.
suma(arkusz2:arkusz4!...) daje sumę z arkuszy znajdujących się między tymi arkuszami, a nie tylko tych dwóch.
A jak napisałem, chciałbym określić, z jakich arkuszy ma brać dane.

Co do odwołania W1K1 - wartość parametru "fałsz" w adr.pośr określa sposób odwołania.

Koncepcja była taka:
1. Wpisuję w jakimś zakresie (kilka komórek w wierszu) nazwy arkuszy.
2. Tworzę adres pośredni z wykorzystaniem nazw, oraz W (nr bieżącego wiersza) K (nr bieżącej kolumny). Odwołanie ustawiam na W1K1 parametrem FAŁSZ.
3. Sumuję zwrócone wartości, a jako że nazwy są w tablicy, to wrzucam Ctrl+Shift+Enter zapisując formułę tablicową.
Można też wykorzystać f-cję ADRES, która zwróci odwołanie, ale zwraca to tekst typu: "Arkusz2!A12", czyli muszę użyć ADR.POŚR, żeby wydobyć wartość, a to już nie do końca ma sens, bo to o jeden zbędny krok więcej.

A co do koncepcji wpisywania arkuszy - robiąc kilka różnych agregacji danych, przygotowuję 1 szablon arkusza i kopiuję go tyle razy ile muszę zrobić agregacji, po czym wprowadzam nazwy arkuszy, które mają być zagredowane i nie przejmuję się niczym.

Pozdrawiam
Mad Dog Of Hell
________________________
Motto życiowe [nowe wskutek działania cenzury]:

Czas to pieniądz,
Bo pieniądz jest czasem,
.
.
A czasem go nie ma

#6 Mały_Rycho

Mały_Rycho

    eremita

  • Forumowicze
  • PipPipPipPipPipPipPip
  • 2014 Postów:
  • Płeć:Mężczyzna
  • Lokalizacja:Sulęcin

Napisany 01 wrzesień 2006 - 12:44

Hej.
Nie zrozumiałeś mnie, MadDogOfHell, gdy mówiłem o odwołaniu W1K1.
Ja nie potrafię wpisać do arkusza formuły
=arkusz2!w1k1
excel się buntuje! Używam stylu odwołania 'A1' i Ty raczej też.

W Twoim przypadku, odwołanie do innego arkusza, powinno być raczej konstruowane przy pomocy funkcji ADRES np:
=ADR.POŚR(ADRES(WIERSZ();NR.KOLUMNY();;;"arkusz2"))
i dla jednego arkusza jest OK.

Ale formuły tablicowe nie chcą zwracać danych tekstowych z kolejnych komórek. Można na nich dokonywać jakichś operacji/testów, ale wynik nie może być takstem.
U mnie w efekcie zawsze zwracany jest wyłącznie tekst z pierwszej komórki zakresu i nie udaje się zbudować odwołań do kolejnych arkuszy w jednej formule tablicowej.
Np. formuła tablicowa
=ZŁĄCZ.TEKSTY(A1:A3)
jest błędna i zwraca wartość wyłącznie z komórki A1,
ale mogę policzyć ile w tym zakresie jest "a"
=SUMA((A1:A3="a")*1)

'cza' chyba pomyśleć o rozwiązaniu przy pomocy VBA? :(
Mały Rycho

Jeśli mówisz prawdę, nie musisz niczego pamiętać. Mark Twain.

#7 MadDogOfHell

MadDogOfHell

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 259 Postów:

Napisany 01 wrzesień 2006 - 12:46

Tja :) Tyle że mam wrażenie, że już to kiedyś jakoś zrobiłem :) A może gryzłem się z podobnym problemem i rozwiązałem go inaczej :)
Myślę dalej :)
Mad Dog Of Hell
________________________
Motto życiowe [nowe wskutek działania cenzury]:

Czas to pieniądz,
Bo pieniądz jest czasem,
.
.
A czasem go nie ma

#8 Mały_Rycho

Mały_Rycho

    eremita

  • Forumowicze
  • PipPipPipPipPipPipPip
  • 2014 Postów:
  • Płeć:Mężczyzna
  • Lokalizacja:Sulęcin

Napisany 01 wrzesień 2006 - 12:54

Też mam takie wrażenie. :o
Piszę funkcję użytkownika ale mam petentów na karku. :( Nie dadzą mi spokojnie popracować :(
Mały Rycho

Jeśli mówisz prawdę, nie musisz niczego pamiętać. Mark Twain.

#9 MadDogOfHell

MadDogOfHell

    Bywalec

  • Forumowicze
  • PipPipPipPipPip
  • 259 Postów:

Napisany 01 wrzesień 2006 - 13:00

A co do makra, to widziałem kiedyś właśnie takie makro kiedyś w akcji.
Kolesie zrobili patent taki, że odpowiednio pokolorowali komórki i makro w zależności od koloru, sumowało albo nie sumowało danych.
W części komórek musiały być formuły i For each cell in ... zsumowałoby całe arkusze nadpisując formuły :)
Ciekawe rozwiązanie.
Mad Dog Of Hell
________________________
Motto życiowe [nowe wskutek działania cenzury]:

Czas to pieniądz,
Bo pieniądz jest czasem,
.
.
A czasem go nie ma

#10 Mały_Rycho

Mały_Rycho

    eremita

  • Forumowicze
  • PipPipPipPipPipPipPip
  • 2014 Postów:
  • Płeć:Mężczyzna
  • Lokalizacja:Sulęcin

Napisany 01 wrzesień 2006 - 15:07

hej.
Funkcja wymaga jeszcze dopracowania i testów, ale to sobie zostawię na jutro :)

Function SumaA(zakres As Range, Optional komorka As String)

'  01-09-2006 Mały Rycho
'
' Funkcja zwraca sumę liczb w komórkach o takim samym adresie
' z wielu arkuszy.
'
' Parametry:
'
' zakres  - zakres komórek arkusza, w którym umieszczone są nazwy arkuszy
'           z komórkami do sumowania
'
' komorka - opcjonalnie - adres sumowanych komórek w każdym z arkuszy.
'           Jeśli pominięto, będzie brany pod uwagę adres komórki,
'           w której jest umieszczona funkcja.
'
  
  Dim ws As Worksheet
  Dim kom As Range
  Dim suma As Double
  Dim w As Long, k As Integer
  
  If komorka <> "" Then
  
    With Range(komorka)
      w = .Row
      k = .Column
    End With
  
  Else
  
    With Application.Caller
      w = .Row
      k = .Column
    End With
  
  End If
  
  suma = 0
  
  For Each kom In zakres
  
    On Error GoTo etError_SumaA
    Set ws = ThisWorkbook.Sheets(kom.Value)
    On Error GoTo 0
    
    If IsNumeric(ws.Cells(w, k).Value) Then
      suma = suma + ws.Cells(w, k)
    End If
    
  Next kom
  
  SumaA = suma
  
Exit Function

etError_SumaA:
  SumaA = "BŁĄD. " & kom.Value
  
End Function

Mały Rycho

Jeśli mówisz prawdę, nie musisz niczego pamiętać. Mark Twain.




0 Użytkowników czyta ten temat

0 użytkowników, 0 gości, 0 anonimowych użytkowników