Zaawansowane zliczanie wartości tekstowych

Autor: Piotr Dynia

Analizy i raporty, które chcesz sporządzić, bardzo często opierają się na zestawieniach cząstkowych przygotowanych przez innych pracowników. Nie wszyscy wprowadzają dane do arkusza w odpowiedni sposób i zwykle nie przywiązują wagi do ujednoliconego zapisu. Niestety, nadanie spójności wartościom w arkuszu spada później na Ciebie.

Okazuje się, że przy pewnych zadaniach można uniknąć ręcznego porządkowania danych. Wystarczy umiejętne zastosowanie formuł przy analizach, tak aby nieistotne informacje zostały pominięte w obliczeniach. Za przykład weźmy proste zestawienie szkoleń, w którym chcesz policzyć, ile lekcji poprowadził dany trener.

W pierwszej kolejności wstaw wiersze pomocnicze, do których wprowadzisz kryterium zliczania, a także formułę.

Rys. 1. Przykładowe zestawienie z nazwiskami trenerów

W tym celu:

Zaznacz cały 3. wiersz, klikając jego etykietę z numerem i 3-krotnie skorzystaj z kombinacji klawiszy Ctrl + Shift + = (znak równości). Powyżej zestawienia powinny pojawić się puste wiersze.
W komórce A3 wpisz Trener, a w A4 wprowadź opis: Liczba zajęć:

Rys. 2. Pomocniczy zakres komórek

Teraz można już przystąpić do obliczeń.

Do komórki B3 wpisz nazwisko, np. Nowak, a w komórce B4 wprowadź następującą formułę:

=(SUMA(DŁ(B7:D11))-SUMA(DŁ(PODSTAW(B7:D11;B3;""))))/DŁ(B3)

Zatwierdź ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Poprawnie wprowadzona zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły.

Rys. 3. Formuła zwróciła poprawny wynik

Wyjaśnienie działania formuły:

W pierwszej kolejności za pomocą funkcji DŁ sprawdzane jest, ile znaków jest wpisane do każdej komórki zakresu B7:D11. Na tej podstawie tworzona jest tablica wartości i funkcja SUMA dodaje do siebie wszystkie liczby znaków. W tym przypadku podsumowanie daje w wyniku liczbę 160. Drugi człon formuły oddzielony minusem ma bardzo podobne zadanie z tą różnicą, że w każdej ze sprawdzanych komórek nazwisko Nowak jest zastępowane pustym ciągiem znaków. Po tej korekcie zliczane są liczby znaków i przekazywane funkcji SUMA, która w wyniku zwróci wartość 130. Następnie wynik pochodzący z pierwszego członu formuły (160) jest odejmowany od wyniku otrzymanego w drugim członie (130). Na koniec Otrzymana wartość jest dzielona przez liczbę znaków, z ilu składa się szukane nazwisko trenera (3/5).

Piotr Dynia

Zobacz także:

Tagi: excel