Jak automatycznie SUMOWAĆ wybrany wiersz lub kolumnę?

Z lekcji dowiesz się:

• Jak automatycznie sumować dane np. dla miesiąca lub danego działu?

• Do czego służą funkcje: PRZESUNIĘCIE i INDEKS?

• Jak wybierać kryteria sumowania z listy rozwijanej?

W tabeli znajdują się przykładowe dane sprzedażowe (rys. 1).

(Rys. 1) Przykładowe dane

Jeśli dane przedstawione są w postaci tabelarycznej, to w łatwy sposób można dołożyć podsumowania do wierszy i kolumn. Wystarczy zaznaczyć zakres danych o jeden wiersz i kolumnę szerszy niż dane, a następnie korzystając ze skrótu klawiszowego Alt+= włączyć sumy po odpowiednich wierszach i kolumnach (rys. 2).

(Rys. 2) Sumy po wierszach i kolumnach (skrót klawiszowy Alt+=)

Naszym celem będzie jednak wyciągnięcie tych informacji niejako na żądanie, a nie żeby te wszystkie sumy cały czas się wyświetlały. Chodzi tutaj o zsumowanie odpowiednich danych według określonych kryteriów, np. miesięcy lub działu. Kryteria te będzie można wybrać z listy rozwijanej. W przykładowych danych jest mały zakres danych, żeby łatwiej było sprawdzić czy wyniki są prawidłowe.

Osiągnięcie założonego efektu możliwe jest przy użyciu różnych funkcjonalności Excela, w przedstawimy dwa rozwiązania, za pomocą funkcji: PRZESUNIĘCIE i INDEKS.

PRZESUNIĘCIE

Funkcja PRZESUNIĘCIE jest o tyle przydatna, że podany zakres można przesuwać i dowolnie zmieniać. Zakładamy, że chcemy uzyskać sumę z konkretnego miesiąca. Podawanie zakresów można rozpocząć od nagłówków. W tym momencie nie trzeba przejmować się odwołaniami ponieważ pracujemy na pojedynczych komórkach. Zaczynamy od nagłówków ponieważ, jeśli będziemy przesuwać ten zakres, to po przesunięciu o jeden wiersz w dół otrzymamy Styczeń itd.

Pierwszym argumentem funkcji PRZESUNIĘCIE jest odwołanie, czyli wiersz, dla którego chcemy uzyskać sumę. Drugi argument funkcji to wiersze. Aby mieć możliwość przesuwania zakresu musimy użyć dodatkowo funkcji PODAJ.POZYCJĘ (jako drugi argument funkcji PRZESUNIĘCIE). Funkcja ta będzie podawać numer wybranego wiersza z listy rozwijanej.

Pierwszym argumentem funkcji PODAJ.POZYCJĘ jest szukana_wartość, czyli miesiąc, który aktualnie jest wybrany w liście rozwijanej (komórka B12). Drugi argument to przesukiwana_tab, czyli zakres ze wszystkimi miesiącami w tabeli z przykładowych danych (zakres A2:A7).

Trzeci argument funkcji to typ_porównania, w przykładzie wybieramy dopasowanie dokładne, czyli wpisujemy w formułę wartość 0. Zapis funkcji PODAJ.POZYCJĘ, czyli drugiego argumentu funkcji PRZESUNIĘCIE, powinien wyglądać następująco:

PODAJ.POZYCJĘ(B12;A2:A7;0)

Trzeci argument funkcji PRZESUNIĘCIE to kolumny, czyli liczba kolumn, o jaką chcemy się przesunąć. W przykładzie będziemy przesuwać się tylko o wiersze, więc w tym argumencie wpisujemy wartość 0.

W funkcji PRZESUNIĘCIE możemy podać argumenty opcjonalne, czyli wysokość i szerokość. Nie będziemy zmieniać rozmiarów zakresu, więc te argumenty można pominąć. Zapis całej formuły powinien wyglądać następująco:

=PRZESUNIĘCIE(B1:E1;PODAJ.POZYCJĘ(B12;A2:A7;0);0)

Powyższą formułę zatwierdzamy. Ze względu na to, że pracujemy w Excelu tablicowym, wynik formuły rozleje się na taką ilość kolumn, jaką obejmował zaznaczony zakres (4), co widać na rys. 3.

(Rys. 3) Rozlane wyniki funkcji PRZESUNIĘCIE

Otrzymaliśmy dane z tabeli z danymi przykładowymi dla Stycznia, ponieważ właśnie ten miesiąc mieliśmy wybrany z listy rozwijanej. Możemy zmienić miesiąc na np. Kwiecień - na liście rozwijanej i automatycznie otrzymamy wyniki dla tego miesiąca (rys. 4).

(Rys. 4) Wyniki po zmianie miesiąca na liście rozwijanej

Pozostaje teraz zsumować te wartości, czyli musimy wstawić zapis funkcji PRZESUNIĘCIE w funkcję SUMA. Zapis powinien wyglądać następująco:

=SUMA(PRZESUNIĘCIE(B1:E1;PODAJ.POZYCJĘ(B12;A2:A7;0);0))

Po zatwierdzeniu tej formuły otrzymamy zsumowany wiersz dla miesiąca Kwiecień przedstawiony na rys. 5.

(Rys. 5) Suma wiersza dla miesiąca Kwiecień

Mimo, iż funkcja PRZESUNIĘCIE zwróciła tablicę danych, nie musimy zatwierdzać powyższej formuły klawisza Ctrl+Shift+Enter.

Analogicznie możemy obliczyć sumę po wybranej kolumnie. Wykorzystamy tak jak wcześniej funkcję PRZESUNIĘCIE. Tym razem w argumencie odwołanie podamy zakres kolumny, czyli nagłówki wierszy (A2:A7 - nazwy miesięcy).

W drugim argumencie podamy wartość 0, ponieważ nie chcemy się przesuwać o żaden wiersz (argument wiersze). W tym przypadku będziemy się przesuwać o kolumny, do czego użyjemy funkcji PODAJ.POZYCJĘ.

Argument szukana_wartość to będzie komórka B13, czyli wybrana nazwa kolumny z listy rozwijanej. Drugi argument, czyli przesukiwana_tab to będzie zakres nagłówków kolumn (B1:E1).

Trzeci argument funkcji, czyli typ_porównania to analogicznie dopasowanie dokładne, czyli wstawimy tutaj wartość 0. Otrzymamy odpowiednią ilość przesunięć. Opcjonalnych argumentów funkcji PRZESUNIĘCIE nie podajemy, ponieważ nie chcemy zmieniać wysokości ani szerokości naszych wyników. Zapis formuły powinien wyglądać następująco:

=PRZESUNIĘCIE(A2:A7;0;PODAJ.POZYCJĘ(B13;B1:E1);0)

Po zatwierdzeniu formuły otrzymamy rozlany wynik przedstawiony na rys. 6.

(Rys. 6) Rozlany wynik funkcji PRZESUNIĘCIE

Teraz analogicznie jak w przypadku wierszy możemy zsumować te wyniki poprzez zamknięcie funkcji PRZESUNIĘCIE w funkcji SUMA. Zapis powinien wyglądać następująco:

=SUMA(PRZESUNIĘCIE(A2:A7;0;PODAJ.POZYCJĘ(B13;B1:E1);0))

Po zatwierdzeniu formuły otrzymamy sumę danych z wybranej kolumny (rys. 7).

(Rys. 7) Suma po wybranej kolumnie (Marketing i Sprzedaż)

INDEKS

Przechodzimy teraz do rozwiązania z funkcją INDEKS. Rozwiązanie to wygląda podobnie, ale ma jeden plus. Mianowicie funkcja PRZESUNIĘCIE jest tzn. funkcją ulotną, ponieważ przelicza się za każdym razem, kiedy coś zmieni się w pliku- natomiast funkcja INDEKS przeliczy się tylko wtedy, kiedy zmienimy coś w zakresach z nią związanych.

Pierwszym argumentem funkcji INDEKS jest tablica, czyli cały zakres danych, jakie będziemy rozpatrywać. Drugi argument to nr_wiersza, czyli interesujący nas wiersz. W tym argumencie do podania numeru wiersza musimy użyć funkcji PODAJ.POZYCJĘ, w której szukamy wybranego miesiąca (B12) na liście wszystkich miesięcy (A2:A7) z dopasowaniem dokładnym. Zapis formuły powinien wyglądać następująco:

=INDEKS(B2:E7;PODAJ.POZYCJĘ(B12;A2:A7;0);0)

Istotne jest tutaj, że właśnie otrzymaliśmy numer wiersza, a nam zależy na tym aby zostały zwrócone wszystkie wartości z tego wiersza. Dlatego w argumencie nr_kolumny musimy wpisać wartość 0 lub ewentualnie pozostawić go pustym. Po zatwierdzeniu powyższej formuły skrótem klawiszowym Ctrl+Enter otrzymamy rozlane wyniki funkcji INDEKS (rys. 8).

(Rys. 8) Rozlany wynik funkcji INDEKS

Powyższą formułę musimy wstawić do funkcji SUMA, aby otrzymać zsumowany wiersz. Zapis powinien wyglądać następująco:

=SUMA(INDEKS(B2:E7;PODAJ.POZYCJĘ(B12;A2:A7;0);0))

Po zatwierdzeniu formuły otrzymamy prawidłowy wynik sumy dla wybranego wiersza (rys. 9).

(Rys. 9) Suma dla wybranego miesiąca - Kwiecień

Funkcja INDEKS z dodatkowym argumentem o wartości 0, może być mniej zrozumiała, ale ten argument sprawi, że będzie się ona rzadziej przeliczać.

Teraz przejdziemy do zsumowania wartości z wybranej kolumny. Cały zapis od razu wstawimy do funkcji SUMA. Następnie wstawimy funkcję INDEKS, której pierwszy argument to tablica, czyli zakres danych B2:E7. Tym razem interesuje nas kolumna danych, więc w drugim argumencie funkcji INDEKS wstawimy wartość 0 (nr_wiersza). Natomiast w argumencie nr_kolumny wstawimy funkcję PODAJ.POZYCJĘ, która zwróci całą kolumnę danych.

Pierwszy argument funkcji PODAJ.POZYCJĘ to szukana_wartość, czyli komórka B13, która zawiera nazwę wybranej kolumny. Przeszukiwana_tab to zakres nagłówków kolumn, czyli B1:E1. Musimy podać również typ_porównania, czyli w tym przykładzie będzie to dopasowanie dokładne (wartość 0). Zapis całej formuły powinien wyglądać następująco:

=SUMA(INDEKS(B2:E7;0;PODAJ.POZYCJĘ(B13;B1:E1;0)))

Powyższą formułę zatwierdzamy i otrzymamy sumę wartości dla wybranej kolumny (rys. 10).

(Rys. 10) Suma wartości dla wybranej kolumny - Marketing i Sprzedaż

Otrzymaliśmy takie same wyniki z dwóch różnych funkcji: PRZESUNIĘCIE i INDEKS. Możemy sprawdzić działanie tych funkcji wybierając inne wiersze i kolumny, dla których chcemy otrzymać sumę. Wybierzmy np. wiersz Luty oraz kolumnę HR. Otrzymamy prawidłowe wyniki przedstawione na rys. 11.

(Rys. 11) Prawidłowe wyniki sumy dla wybranego wiersza i kolumny

Poprawność wyników możemy sobie sprawdzić z rys. 2, gdzie mamy podane wyniki sum dla wszystkich wierszy i kolumn. W tym zadaniu zależało nam, aby wyniki powstawały z wyboru, a nie były wyświetlane wszystkie jednocześnie. Dzięki takiemu rozwiązaniu przy dużej ilości danych, możemy łatwo taki wynik odczytać i zinterpretować.

Adam Kopeć

Przejrzyj także:

• Jak wyszukać auto funkcjami: ORAZ, LUB i formułą JEŻELI?
• Jak pobrać dane o sprzedaży z różnych arkuszy?