Zliczanie wyników dla grupy przedmiotów

Z lekcji dowiesz się:

• Jak policzyć ile razy wystąpiły łącznie produkty z danej grupy?

• Co to są funkcje bazodanowe?

• Jak zahardkodować nazwę kolumny?

Zadanie wykonamy na podstawie przykładowych danych przedstawionych na rys. 1.

Rys. 1. Przykładowe dane

Możemy to zadanie wykonać na trzy sposoby. Pierwszy z nich wykorzystuje funkcję LICZ.JEŻELI. Pierwszym argumentem funkcji jest zakres, czyli zakres danych z kolumny Nazwa kursu zablokowany bezwzględnie za pomocą klawisza F4. Drugim argumentem funkcji są kryteria, czyli zakres kryteriów z kolumny F (Nazwa kursu). Zapis formuły powinien wyglądać następująco:

=LICZ.JEŻELI($B$2:$B$732;F2:F4)

Możemy podejrzeć wyniki takiej funkcji za pomocą klawisza F9 i otrzymamy trzy wyniki przedstawione na rys. 2.

Rys. 2. Podgląd wyników funkcji LICZ.JEŻELI

Z trybu podglądu wyników funkcji wychodzimy skrótem klawiszowym Ctrl+Z. Otrzymaliśmy trzy wyniki, ponieważ podaliśmy 3 kryteria z kolumny F. W Excelu klasycznym wynik z tej funkcji nie będzie poprawnie wyświetlany, ponieważ jest tablicowy i nie mieści się w jednej komórce (rys. 3).

Rys. 3. Niepoprawnie wyświetlany wynik funkcji LICZ.JEŻELI

Możemy sobie z tym poradzić jeśli wynik funkcji LICZ.JEŻELI wstawimy do funkcji SUMA.ILOCZYNÓW. Jest to funkcja, która radzi sobie z obliczeniami na tablicach. Zapis funkcji powinien wyglądać następująco:

=SUMA.ILOCZYNÓW(LICZ.JEŻELI($B$2:$B$732;F2:F4))

Otrzymamy zsumowaną ilość kursów spełniających podane kryteria przedstawioną na rys. 4.

Rys. 4. Wynik funkcji SUMA.ILOCZYNÓW

Możemy przeciągnąć formułę z komórki F5 na G5. Dzięki temu, że nie zablokowaliśmy zakresu drugiego argumentu funkcji LICZ.JEŻELI, formuła zadziała poprawnie również dla kursów w kolumnie G. Zapis formuły dla kolumny G (po przeciągnięciu formuły) powinien wyglądać następująco:

=SUMA.ILOCZYNÓW(LICZ.JEŻELI($B$2:$B$732;G2:G4))

Otrzymamy wyniki przedstawione na rys. 5.

Rys. 5. Wyniki funkcji SUMA.ILOCZYNÓW po przeciągnięciu formuły

Drugi sposób wykonania zadania polega na wykorzystaniu funkcji z rodziny bazodanowych, mianowicie funkcji, która liczy rekordy (rys. 6).

Rys. 6. Rodzina funkcji bazodanowych

Wykorzystamy tutaj funkcję BD.ILE.REKORDÓW.A, która zlicza liczbę niepustych komórek w kolumnie rekordów bazy danych spełniających podane kryterium. Działa ona na zasadzie filtrów zaawansowanych. Pierwszym argumentem funkcji jest baza, czyli zakres wszystkich danych w tabeli (przykładowe dane). Zaznaczamy pierwszą komórkę tabeli, a następnie przechodzimy na koniec danych za pomocą skrótu klawiszowego Ctrl+Shift+strzałka w dół. Zaznaczony zakres blokujemy bezwzględnie za pomocą klawisza F4. Drugim argumentem funkcji jest pole, czyli kolumna z której chcemy wyciągnąć rekordy. Nie zaznaczamy tutaj wybranej kolumny, lecz wpisujemy jej kolejny numer licząc kolumny od lewej strony, czyli w naszym przykładzie wpisujemy wartość 2 (rys. 7).

Rys. 7. Numer kolumny do wpisania jako drugi argument funkcji (pole)

W argumencie pole, możemy również zrobić odwołanie do komórki z nazwą kolumny (B1) lub możemy nazwę kolumny zahardkodować, czyli wpisać ją w cudzysłowie ("Nazwa kursu"). Trzecim argumentem funkcji są kryteria, czyli zakres danych jakich szukamy. Co istotne, tak jak przy filtrach zaawansowanych, musimy zaznaczyć zarówno nazwę kolumny, jak i komórki z danymi. Ponadto każdy kolejny wiersz w kryteriach "lub", dzięki czemu od razu otrzymamy zsumowane wszystkie wyniki według podanych kryteriów. Zapis całej formuły powinien wyglądać następująco:

=BD.ILE.REKORDÓW.A($A$1:$C$732;"Nazwa kursu";F1:F4)

Po zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. 8.

Rys. 8. Wynik funkcji BD.ILE.REKORDÓW.A

Formułę z komórki F6 kopiujemy na komórkę obok (przeciągamy). Otrzymamy wówczas poprawny wynik dla kryteriów w kolumnie G przedstawiony na rys. 9. Poprawny wynik uzyskamy dzięki odpowiedniemu blokowaniu zakresów oraz braku blokowania zakresu trzeciego argumentu funkcji.

Rys. 9 Wynik skopiowania formuły na kolumnę G

Musimy pamiętać o tym, że filtry zaawansowane niepoprawnie interpretują puste komórki. W sytuacji, kiedy usuniemy dane z jednej komórki otrzymamy błędny wynik (rys. 10). Puste komórki w filtrach zaawansowanych są interpretowane jako dowolna wartość.

Rys. 10. Błędne działanie funkcji bazodanowej dla pustych komórek (komórka G6)

W przypadku funkcji LICZ.JEŻELI należy uważać z kolei na symbole wieloznaczne - czyli jeśli w danych wstawimy symbol np. gwiazdkę (*), to funkcja podliczy wszystkie elementy w danych źródłowych (rys. 11).

Rys. 11. Błędne działanie funkcji LICZ.JEŻELI dla symboli wieloznacznych (np.*)

Na zakończenie przedstawimy jeszcze jedno rozwiązanie wykorzystujące nowe funkcje tablicowe w Excelu, a dokładniej funkcję FILTRUJ. Do użycia tej funkcji musimy najpierw nasze dane odpowiednio przefiltrować, czyli nałożyć kryteria - testy logiczne na poszczególne komórki w danych wejściowych. Wykorzystamy w tym celu funkcję PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli cała kolumna Nazwa kursu bez nagłówka. Drugim argumentem funkcji jest przeszukiwana_tab, czyli zakres grupy produktów z kolumny F.Trzeci argument funkcji to typ_porównania, w tym przypadku wybierzemy dopasowanie dokładne, czyli wpiszemy w formułę wartość 0. Zapis formuły powinien wyglądać nastepująco:

=PODAJ.POZYCJĘ(B2:B732;F2:F4;0)

Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter otrzymamy całą tablicę danych, ponieważ pracujemy na Excelu tablicowym (rys. 12).

Rys. 12. Wynik funkcji PODAJ.POZYCJĘ jako tablica danych

Jak widać na rysunku powyżej pierwsze 4 produkty z kolumny B nie należą do grupy produktów z kolumny F, dlatego funkcja PODAJ.POZYCJĘ zwraca błąd. Podsumowując, wtedy kiedy funkcja PODAJ.POZYCJĘ zwraca liczbę, to znaczy że znaleźliśmy produkt z listy w kolumnie F. Naszym zadaniem teraz będzie takie przekształcenie tych wyników aby uzyskać wyniki w postaci wartości logicznych PRAWDA lub FAŁSZ. Użyjemy do tego funkcji CZY.LICZBA. Zapis funkcji powinien wyglądać następująco:

=CZY.LICZBA(PODAJ.POZYCJĘ(B2:B732;F2:F4;0))

Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter otrzymamy dane w postaci wartości logicznych przedstawione na rys. 13.

Rys. 13. Wynik funkcji CZY.LICZBA w postaci wartości logicznych

Jeśli w wynikach wystąpi wartość logiczna PRAWDA, to ten wiersz zostanie zachowany przez funkcję FILTRUJ. Do poprzedniego zapisu formuły dopisujemy na początku funkcję FILTRUJ. Pierwszym argumentem funkcji jest tablica, czyli kolumna B z danych wejściowych bez nagłówka. Drugi argument funkcji to uwzględnienie, czyli wyniki funkcji CZY.LICZBA, którą rozpisaliśmy wcześniej. Zapis funkcji powinien wyglądać następująco:

=FILTRUJ(B2:B732;CZY.LICZBA(PODAJ.POZYCJĘ(B2:B732;F2:F4;0)))

Po zatwierdzeniu formuły (Ctrl+Enter) otrzymamy wyniki przedstawione na rys. 14.

Rys. 14. Wyniki funkcji FILTRUJ

Otrzymaliśmy listę tylko tych produktów, które wystepują w danych w kolumnie F. Na tym etapie wystarczy podliczyć ile jest wierszy na tej liście. Użyjemy w tym celu funkcji ILE.WIERSZY. Zapis funkcji powinien wyglądać nastepująco:

=ILE.WIERSZY(FILTRUJ(B2:B732;CZY.LICZBA(PODAJ.POZYCJĘ(B2:B732;F2:F4;0))))

Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter otrzymamy prawidłowy wynik przedstawiony na rys. 15.

Rys. 15. Wynik funkcji ILE.WIERSZY

Ze względu na to, że nie zwracaliśmy uwagi na rodzaje odwołań przy wprowadzaniu formuły, nie możemy jej przeciągnąć na komórkę obok. Musimy ją skopiować w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C, a następnie wkleić za pomocą skrótu Ctrl+V w komórkę G7 i wprowadzić w niej odpowiednie zmiany. Zmieniamy zakres F2:F4 na G2:G4. Zapis formuły powinien wyglądać nastepująco:

=ILE.WIERSZY(FILTRUJ(B2:B732;CZY.LICZBA(PODAJ.POZYCJĘ(B2:B732;F2:F4;0))))

Po zatwierdzeniu formuły (Ctrl+Enter) otrzymamy poprawny wynik przedstawiony na rys. 16.

Rys. 16. Wyniki funkcji ILE.WIERSZY po zmianie zakresu danych

Istotne jest, że w tym rozwiązaniu puste komórki są interpretowane jako brak danych i nie są brane pod uwagę przy obliczeniach, co oznacza że otrzymamy poprawny wynik po usunięciu części danych (rys. 17). Podobnie działa funkcja SUMA.ILOCZYNÓW, mianowicie nie bierze pod uwagę pustych komórek w kryteriach.

Rys. 17. Poprawny wynik funkcji FILTRUJ po usunięciu części danych (puste komórki)

Przypomnijmy sobie, że w sytuacji wpisania w kryteriach symbolu wieloznacznego (np.*) zarówno funkcja SUMA.ILOCZYNÓW, jak i funkcje bazodanowe nie działają prawidłowo. Natomiast Excel tablicowy, a konkretnie filtry, które zastosowaliśmy nie interpretują symboli wieloznacznych, czyli po wstawieniu takiego symbolu, wynik nie ulegnie zmianie (rys. 18).

Rys. 18. Poprawny wynik funkcji FILTRUJ po wstawieniu symbolu wieloznacznego

   Przeczytaj także:

• Jak w Excelu tablicowym stworzyć dynamiczny raport?

• Fragmentator dla roku fiskalnego!