Jak zsumować dane z wielu arkuszy Excel w jednym podsumowaniu?

Witam!
potrzebuje pomocy!!! mam taką tabelkę jak w załączniku i potrzebna jest mi formuła do zakładki ZAKUP, która zsumuje mi wszystkie AA, BB itd. z każdej zakładki. (IV+V+VI+....)= ???. Udało mi się zrobić żeby każdy miesiąc podsumował mi się bez dublowania nazw, ale nie umiem zrobić tego w łącznym podsumowaniu wszystkich miesięcy. Wiem, że chaotycznie napisane ale może ktoś mi pomoże ??

asdcxz napisał:

potrzebna jest mi formuła do zakładki ZAKUP, która zsumuje mi wszystkie AA, BB itd. z każdej zakładki.

A w jakiej sytuacji formuła zastosowana w załączonym pliku (oparta na funkcji suma.jeżeli ) nie spełnia Twoich oczekiwań

No dopiero po dłuższej analizie znalazłem Twój kardynalny błąd - dla wszystkich arkuszy miesięcznych (ze względu na ich identyczną budowę) formuła winna być identyczna tzn. np =SUMA.JEŻELI('Z IV'!$K$5:$K$13;$B4;'Z IV'!$L$5:$L$13)+SUMA.JEŻELI('Z V'!$K$5:$K$13;$B4;'Z V'!$L$5:$L$13)... itd

Można to zrobić używając Power Query dodatku do Excela 2010 i 2013 od 2016 wbudowany

Prot napisał:

asdcxz napisał:

potrzebna jest mi formuła do zakładki ZAKUP, która zsumuje mi wszystkie AA, BB itd. z każdej zakładki.

A w jakiej sytuacji formuła zastosowana w załączonym pliku (oparta na funkcji suma.jeżeli ) nie spełnia Twoich oczekiwań

No dopiero po dłuższej analizie znalazłem Twój kardynalny błąd - dla wszystkich arkuszy miesięcznych (ze względu na ich identyczną budowę) formuła winna być identyczna tzn. np =SUMA.JEŻELI('Z IV'!$K$5:$K$13;$B4;'Z IV'!$L$5:$L$13)+SUMA.JEŻELI('Z V'!$K$5:$K$13;$B4;'Z V'!$L$5:$L$13)... itd

Ok wiem, że powinna być taka sama i wtedy liczy ok. Tylko moje pytanie jak zrobić żeby lista miast zaczytywała mi się automatycznie do tabeli w zakładce ZAKUP (na tą chwilę muszę ręcznie wpisywać i chodzi o to żeby nie pominąć jakiegoś miasta). Podsyłam nowy poprawiony plik excel.

Można to zrobić np. za pomocą VBA. Napisałem funkcję UDF, która tworzy listę (tablicę) nazw miejscowości odczytanych z zakresów wg podanego schematu.
Wywołanie funkcji:

schemat adresu zawiera nazwę arkusza ze znakami zastępczymi w postaci akceptowanej przez operator Like w VBA, to znaczy w tym przypadku:

Tu nazwy arkuszy zaczynają się od litery Z i spacji, a dalszą część nazwy zastępujemy gwiazdką. Dalej po wykrzykniku adres zakresu w każdym arkuszu.
Wywołanie podane powyżej zwraca tablicę, ale ponieważ nie wiemy z góry ile tych miast będzie proponuję wywoływać to indywidualnie w taki sposób:
jakkolwiek ten sposób jest mniej efektywny, bo obliczenia są powtarzane za każdym razem (w każdej komórce).
Funkcja jest umieszczona w module uniwersalnym:

Załączam plik w formacie .xls ze względu na ograniczenia tego forum. Proszę po odczytaniu zapisać w formacie .xlsm.

asdcxz napisał:

jak zrobić żeby lista miast zaczytywała mi się automatycznie

Można tak przenieść 12 tablic cząstkowych do arkusza Zakup z nich utworzyć tablicę wartości unikalnych i połączyć ją z Twoją tablicą "Okres sprawozdawczy"
Przykład takiego rozwiązania "formułowego" widać na zrzucie i w załączonym pliku
2020-04...png (53.96 kB)Musisz być zalogowany, aby pobrać ten załącznik.
ProtZes..xlsx (85.18 kB)Musisz być zalogowany, aby pobrać ten załącznik.

Maciej Gonet napisał:

Można to zrobić np. za pomocą VBA. Napisałem funkcję UDF, która tworzy listę (tablicę) nazw miejscowości odczytanych z zakresów wg podanego schematu.
Wywołanie funkcji:

Kod: Text

Zaloguj się, aby zobaczyć kod

schemat adresu zawiera nazwę arkusza ze znakami zastępczymi w postaci akceptowanej przez operator Like w VBA, to znaczy w tym przypadku:

Kod: Text

Zaloguj się, aby zobaczyć kod

Tu nazwy arkuszy zaczynają się od litery Z i spacji, a dalszą część nazwy zastępujemy gwiazdką. Dalej po wykrzykniku adres zakresu w każdym arkuszu.
Wywołanie podane powyżej zwraca tablicę, ale ponieważ nie wiemy z góry ile tych miast będzie proponuję wywoływać to indywidualnie w taki sposób:

Kod: Text

Zaloguj się, aby zobaczyć kod

jakkolwiek ten sposób jest mniej efektywny, bo obliczenia są powtarzane za każdym razem (w każdej komórce).
Funkcja jest umieszczona w module uniwersalnym:

Kod: VBScript

Zaloguj się, aby zobaczyć kod

Załączam plik w formacie .xls ze względu na ograniczenia tego forum. Proszę po odczytaniu zapisać w formacie .xlsm.

Dziękuję :)

Dodano po 30 [sekundy]:

Prot napisał:

asdcxz napisał:

jak zrobić żeby lista miast zaczytywała mi się automatycznie

Można tak przenieść 12 tablic cząstkowych do arkusza Zakup z nich utworzyć tablicę wartości unikalnych i połączyć ją z Twoją tablicą "Okres sprawozdawczy"
Przykład takiego rozwiązania "formułowego" widać na zrzucie i w załączonym pliku

Dziękuję

Tak jak pisałem wcześniej można to zrobić prościej i szybciej . Wystarczy kilkanaście kliknięć w PQ i tabela się robi

asdcxz napisał:

żeby lista miast zaczytywała mi się automatycznie

Z tych 3 rozwiązań, które Ci zaproponowano warunek automatyczności spełnia jedynie rozwiązanie "formułowe"
Proponuje zrobienie testu = poprzez dopisanie w którymś miesiącu nowej nazwy miasta (np. MAKÓW MAZOWIECKI )
W wariancie z PQ konieczne będzie odświeżenie zapytania (o ile nie zginiesz wcześniej w ustawieniach źródła danych - tu pozdrowienia dla kolegi @Andrzeja59, który źródła pięknej tabelki wyniósł na dysk E: ).
W wariancie z funkcją UDF w zasadzie powinno wystarczyć ponowne przeliczenie arkusza , ale i tu można się zetknąć z pewnymi niezrozumiałymi (jak dla mnie ) błędami np taki jak na zrzucie
2020-04-1..2).png (42.78 kB)Musisz być zalogowany, aby pobrać ten załącznik.

Prot napisał:

asdcxz napisał:

jak zrobić żeby lista miast zaczytywała mi się automatycznie

Można tak przenieść 12 tablic cząstkowych do arkusza Zakup z nich utworzyć tablicę wartości unikalnych i połączyć ją z Twoją tablicą "Okres sprawozdawczy"
Przykład takiego rozwiązania "formułowego" widać na zrzucie i w załączonym pliku

już się gubię nie ogarniam niektórych nazw, sporadycznie mam styczność z excel w takim wydaniu
Jak mam zmienić formułę (przykład w załączonym screenie), aby formuła ='Z IV'!$K$5:$K$10 zaczytywała mi kolumnę K do 16 wiersza??
Potrzebuję prostych wskazówek

Masz sztywne odwołania ($)
a formuła będzie wyglądać tak:
='Z IV'!$K$5:$K$16

Zbych034 napisał:

Masz sztywne odwołania ($)
a formuła będzie wyglądać tak:
='Z IV'!$K$5:$K$16

jak wpiszę taką formułę to #ARG!

To sprawdź komórki źródłowe, wstaw w nie np 1, może tam jest błąd.

asdcxz napisał:

Jak mam zmienić formułę (przykład w załączonym screenie), aby formuła ='Z IV'!$K$5:$K$10 zaczytywała mi kolumnę K do 16 wiersza??

Uważaj koleżanko na tak poważne zmiany bo operujemy w takim arkuszu formułami tablicowymi - przenosimy elementarne tabelki do arkusza zakupy - jeśli chcesz to przykładowo ='Z IV'!$K$5:$K$16, ale blokowo (ta funkcja musi być jednocześnie wprowadzona do 12 wierszy) i zatwierdzona klawiszami ctr+shift+enter . W arkuszu zakupy całą kolumnę musisz odpowiednio powiększyć, żeby zmieściło się 12 x 12 wierszowych tablic, oraz stosownie rozszerzyć zakresy formuł sąsiednich.

tu pozdrowienia dla kolegi @Andrzeja59, który źródła pięknej tabelki wyniósł na dysk E: ).
Nie widzę problemu ze zmianą źródła Skoro dotarłeś do tego gdzie jest plik źródłowy to przecież można go zmienić . Taka jest " uroda " PQ że przenoszenie pliku z komputera na komputer trzeba zmieniać źródło

Prot napisał:

asdcxz napisał:

Jak mam zmienić formułę (przykład w załączonym screenie), aby formuła ='Z IV'!$K$5:$K$10 zaczytywała mi kolumnę K do 16 wiersza??

Uważaj koleżanko na tak poważne zmiany bo operujemy w takim arkuszu formułami tablicowymi - przenosimy elementarne tabelki do arkusza zakupy - jeśli chcesz to przykładowo ='Z IV'!$K$5:$K$16, ale blokowo (ta funkcja musi być jednocześnie wprowadzona do 12 wierszy) i zatwierdzona klawiszami ctr+shift+enter . W arkuszu zakupy całą kolumnę musisz odpowiednio powiększyć, żeby zmieściło się 12 x 12 wierszowych tablic, oraz stosownie rozszerzyć zakresy formuł sąsiednich.

dziękuję działa, trochę skomplikowane dla mnie ale w potrzebnych mi zestawieniach działa tak jak chciałam

Jeśli chodzi o moje rozwiązanie, to aby formuła przeliczała się automatycznie przy każdym przeliczeniu skoroszytu trzeba dopisać linijkę
na początku funkcji, czyli po deklaracjach przed linijką:W razie czego przeliczenie skoroszytu można wymusić przez naciśnięcie klawisza F9, a gdyby nie było tej instrukcji Volatile, to Ctrl+Alt+F9.
Zakres komórek do analizy podaje się w argumencie. Można go powiększyć w miarę potrzeby, ja przyjąłem dość ograniczony, bo w kilku arkuszach pod miastami były wpisy "OD POCZĄTKU", które dopisałyby się do listy miast. Takich sytuacji należy unikać. Komórki poniżej miast powinny być puste tak, żeby można było bez obawy powiększać zakres do analizy.