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.
Zaawansowane zliczanie wartości tekstowych
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.
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).
Zobacz także:
Tagi: excel
Zaloguj się, aby dodać komentarz
Nie masz konta? Zarejestruj się »
