Jak podsumować tabelę której rozmiar się zmienia?

Zacznę od odpowiedzi banalnej – zrobić to nad tabelą.

Ale zdarza się, że podsumowanie musi koniecznie być po prawej lub na dole. Tabele przestawne jakoś sobie z tym radzą, więc spróbujmy zrobić to przy pomocy formuł arkuszowych.

Do wpisu przyda się korzystanie z pliku roboczego, gdzie są wszystkie przykłady.

Najprostszy przykład – podsumujmy kolumnę

Mamy zakres mieszczący się w jednej kolumnie. Jeśli jego wymiar jest stały, to robimy to następująco:

Używamy zapisu klamrowego – ustawiamy najpierw zakres, a pod nim jego sumę (średnik służy do układania zakresów jeden nad drugim, a backslash \ do ustawiania obok siebie). W plikach z ustawieniami angielskimi zamiast backslasha jest przecinek.

Niby jest nieźle, ale warto pokazać co to za liczba na końcu. Dodajmy więc z automatu słowo SUMA:

“SUMA: ” – dzieki zastosowaniu cudzysłowu, słowo jest czytane “dosłownie”, a znal & łączy je z następnym wyrażeniem.

Ale, ale – widać że ta suma jest pogrubiona. To nie jest zwykłe pogrubienie (Ctrl+B), tylko formatowanie warunkowe. Jeśli tabela będzie dłuższa – komórka z wyrażeniem “SUMA: ” – również zostanie pogrubiona. Osiągamy to zaznaczając całą kolumnę i używając Menu –> Formatuj –> Formatowanie warunkowe.

Jako regułę wybieramy “Tekst zaczyna się od” i wpisujemy “SUMA: “. Następnie ustalamy styl.

Podsumowanie niezależne od ilości wierszy w kolumnie

Jeśli zawartość kolumny B może się wydłużyć (ktoś dopisze więcej liczb), musimy użyć do sumowania całej kolumny. Czyli zapisu B2:B . Problem w tym, że tam jest mnóstwo pustych komórek. Żeby się ich pozbyć opakowuję zapis w formułę Query (opisaną w osobnym wpisie), która wyświetla tylko komórki z zawartością.

Prosty przykład – podsumujmy fakturę

Weźmy tabelę z kilkoma kolumnami A3:G7 – np. listę pozycji na fakturze. I podsumujmy.

={A3:G7;

""\""\""\""\""\"RAZEM: "&sum(F4:F7)\"RAZEM: "&round(sum(G4:G7);2)&" zł"}

O co tu chodzi?

Najpierw jest zapis klamrowy, który mówi, że będziemy budować tabelę.

A3:G7 to kopia tabeli, średnik oznacza, że będziemy tworzyć treść pod tą tabelą.

“”\””\””\””\””\ – to 5 pustych komórek, które musimy dopisać przed podsumowaniem, żeby “dolne piętro” tabeli było tej samej szerokości

“RAZEM: “&sum(F4:F7)\”RAZEM: “&round(sum(G4:G7);2)&” zł” – tutaj stawiamy dwie komórki podsumowujące. Druga z nich ma jeszcze zaokrąglenie i dopisaną jednostkę (bo w przypadku łączenia wartości przy pomocy znaku & gubi się formatowanie walutowe).

Ktoś może powiedzieć, że to banał i że wystarczy pod spodem dopisać formułę podsumowującą. Tak. Ale tylko w tym najprostszym przypadku.

Podsumujmy tabelę (tu fakturę), która powstaje w wyniku filtrowania

Załóżmy, że generujemy fakturę na podstawie formularza zamówienia. I chcemy mieć na niej wyłącznie zamówione towary, a nie cały formularz. W zależności od tego ile pozycji wybierzemy, zmieni się liczba wierszy. Tutaj założyłem również, że lista z ofertą (tabela po lewej) może się powiększać

Formuła wygląda tak:

={query(A13:G;"select * where F is not null"); ""\""\""\""\""\"RAZEM: "&sum(F14:F)\"RAZEM: "&sum(G14:G)&" zł"}

Query służy do przefiltrowania tabeli A13:G (pokaż wszystkie wiersze tabeli,w których kolumna F nie jest pusta).

A dalej jest już tak samo jak w poprzednim przykładzie.

Podsumujmy od spodu tabelę, która może mieć różną liczbę kolumn

Weźmy taki przykład. Podsumowujemy sprzedaż wg regionów w poszczególnych miesiącach roku.

Zaczniemy od tego co formuła potrafi zrobić: Potrafi wyświetlić podsumować tabelę o nazwie TABLE bez względu na to ile miesięcy zawiera i ilu handlowców.

={query({TABLE};"Select * where Col1 is not null");"Podsumowanie:"\index(query({TABLE;split(rept("0 ";columns(TABLE));" ")};"Select "&textjoin(", ";1;INDEX("sum(Col"&sequence(1;columns(TABLE)-1;2)&")"));0);2)}

Zamiast TABLE możemy tu wstawić dowolną tabelę z zawartością pochodzącą z filtrowania, czy z importu z sieci. Jak Wam wytłumaczę jak podsumowanie powstaje, będziecie umieli (mam nadzieję) przerabiać funkcję wg waszych potrzeb.

O do tu chodzi?

{Nawias klamrowy} mówi, że robimy tabelę.

query({TABLE};”Select * where Col1 is not null”) – przepisuje tabelę TABLE pozbawiając ją pustych wierszy

; mówi, że będziemy coś układać poniżej pierwszej tabeli.

“Podsumowanie:”\ – to tworzy tekst i przechodzi do kolejnej komórki w prawo

index(………….;2) – oznacza, że ze wszystkiego co zostanie stworzone wewnątrz, weźmiemy drugi wiersz (bo pierwszy wiersz stanowić będą niepotrzebne nagłówki

query({TABLE;split(rept(“0 “;columns(TABLE));” “)} – to pierwsza część zapytania. Mówi o tym,że do zapytania wykorzystamy tabelę TABLE, a pod nią wsadzimy wiersz z zerami o tej samej szerokości. Ten wiersz zer To zabezpieczenie potrzebne do obsługiwania tabel, które mają puste kolumny lub kolumny bez liczb. Inaczej by wyskoczyły błędy.

“Select “&textjoin(“, “;1;INDEX(“sum(Col”&sequence(1;columns(TABLE)-1;2)&”)”))) – to druga część zapytania query. Tu musimy stworzyć wyrażenie, które pobierze odpowiednią liczbę kolumn. Np. dla 3 kolumn ma ono wyglądać. “Select sum(Col1), Sum(Col2), Sum(Col3)”.To wyrażenie pisze “Select, a potem powtarza sum(Col X ), tyle razy ile jest kolumn w tabeli.

Podsumujmy z prawej strony tabelę, która może mieć różną liczbę wierszy

Tutaj zadziałamy podobnie jak w przykładzie z podsumowywaniem pod spodem. Musimy jednak pamiętać, że funkcja Query umie podsumowywać kolumny, a nie ma takiego polecenia jak “podsumuj wiersz X”. Zatem, żeby uzyskać sumę dla poszczególnych wierszy, należy obrócić tabelę o 90 stopni. Wówczas już będziemy mogli skorzystać z funkcji query. Na koniec wrócimy tabelę do pierwotnego ustawienia.

Zatem potrzebujemy wziąć poprzednią formułę i w każdym miejscu gdzie używaliśmy TABLE, musimy wpisać:

transpose(query({TABLE};"Select * where Col1 is not null")) – czyli wziąć tabelę obróconą i pozbawioną wcześniej pustych wierszy (query nie potrafi łatwo usuwać pustych kolumn, stąd ta operacja.

Musimy też użyć funkcji transpose na sam koniec, żeby całość formuły przywrócić.

Uniwersalna formuła podsumowująca tabelę po prawej stronie wygląda tak:

=Transpose({query({transpose(query({TABLE};"Select * where Col1 is not null"))};"Select * where Col1 is not null");"Podsumowanie:"\index(query({transpose(query({TABLE};"Select * where Col1 is not null"));split(rept("0 ";columns(transpose(query({TABLE};"Select * where Col1 is not null"))));" ")};"Select "&textjoin(", ";1;INDEX("sum(Col"&sequence(1;columns(transpose(query({TABLE};"Select * where Col1 is not null")))-1;2)&")"));0);2)})

W przypadku długich formuł warto korzystać z opcji Znajdź – Zamień (Ctrl +H), żeby wymienić fragmenty formuły. Należy wybrać odpowiedni zakres działania i zaznaczyć opcję wyszukiwania w formułach

Blaise Pascal (chyba)

Przykład zastosowania

Żeby pokazać Wam jak to działa, zróbmy kombo. Czyli weźmy tabelę podumowaną po prawej, wstawmy do formuły podsumowującej pod spodem i zobaczmy czy się uda uzyskać tabelę podsumowaną i tu i tu. Oczywiście wyjdzie z tego monstrum formułowe, ale warto spróbować się z nim!

Da się prościej?

Oczywiście, że się da. Prezentowane tu rozwiązania można łatwo i bez pisania żmudnych formuł uzyskać, korzystając z tabel przestawnych (Menu –> Dane –> Tabela przestawna).