Zaawansowane zliczanie wartości tekstowych

Analizy i raporty, które chcemy sporządzić, bardzo często opierają się na zestawieniach cząstkowych przygotowanych przez inne osoby. Nie wszyscy wprowadzają dane do arkusza w odpowiedni sposób i zwykle nie przywiązują wagi do ujednoliconego zapisu.

Na szczęście przy pewnych zadaniach możemy uniknąć ręcznego porządkowania danych. Wystarczy umiejętne zastosowanie formuł przy analizach, tak aby nieistotne informacje zostały pominięte w obliczeniach. Weźmy przykładowo proste zestawienie, w którym chcemy policzyć, ile lekcji poprowadził dany nauczyciel. Plik do pobrania z przykładowymi danymi znajduje się pod filmem.

Na początku wstawiamy wiersze pomocnicze, do których wprowadzamy kryterium zliczania, a także formułę.

W tym celu:

1. 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.

2. W komórce A3 wpisz Nauczyciel, a w A4 wprowadź opis: Liczba zajęć:.

Teraz możemy zacząć obliczenia.

3. 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)

4. 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.

Jak działa ta formuła? 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 nauczyciela (30/5).

RMsOx7P0Q5c4N

Film nawiązujący do treści zaawansowanego zliczania wartości tekstowych.

Film nawiązujący do treści zaawansowanego zliczania wartości tekstowych.

Film dostępny pod adresem /preview/resource/RMsOx7P0Q5c4N

Film nawiązujący do treści zaawansowanego zliczania wartości tekstowych.

Pobierz przykładowe dane:

Ćwiczenie 1

Pracujesz dorywczo w kawiarni. Co tydzień, wraz ze współpracownikami, ustalacie grafik i wpisujecie się, w jakich godzinach możecie pracować. Twój szef poprosił cię, abyś zliczył, ile każda z osób przepracuje godzin w tym tygodniu. Utwórz arkusz kalkulacyjny, w którym wykonasz potrzebne obliczenia. Załóż, że imiona każdego z pracowników nie powtarzają się.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RhfHH19XUQ9v9

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B C, D, E, F i G wprowadzono dane. W komórce A1 umieszczono tytuł Dzień tygodnia W pierwszym wierszu w komórkach od B1 do H1 wpisano nazwy kolejnych dni tygodnia. W kolumnie A, w komórce A2 wpisano tytuł Godziny. W drugim wierszu, komórkach od B2 do H2 wpisano tytuł: Pracownik. W kolumnie A, w komórkach od A3 do A11 wpisano kolejne godziny pracy. W kolumnie B w komórkach od B3 do B11 wpisano imiona i nazwiska pracowników. W kolumnie C w komórkach od C3 do C11 wpisano imiona i nazwiska pracowników. W kolumnie D w komórkach od D3 do D11 wpisano imiona i nazwiska pracowników. W kolumnie E w komórkach od E3 do E11 wpisano imiona i nazwiska pracowników. W kolumnie F w komórkach od F3 do F11 wpisano imiona i nazwiska pracowników. W kolumnie G w komórkach od G3 do G11 wpisano imiona i nazwiska pracowników. W kolumnie H w komórkach od H3 do H11 wpisano imiona i nazwiska pracowników. W kolumnie B, w komórce B13 wpisano tytuł Pracownik. W komórkach od B14 do B18 wpisano imiona pracowników. W kolumnie C, w komórce C13 wpisano tytuł Ile godzin. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce C14. Brzmi ona następująco =(SUMA(DŁ($B$3:$H$11))-SUMA(DŁ(PODSTAW($B$3:$H$11;B14;””))))/DŁ(B14) Dalej, w komórkach od C15 do C18 wpisano dane liczbowe.

Ćwiczenie 2

Razem ze znajomymi chcecie zacząć pomagać w lokalnym schronisku dla zwierząt. Macie zamiar tam chodzić w maksymalnie 3‑osobowych grupach. Policz, ile razy każdy z was weźmie udział w tym przedsięwzięciu. Znów załóż, że nie będzie dwóch osób z takim samym imieniem.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R1ZC9YAV6wHyV

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 umieszczono tytuł Dzień W komórce B1 umieszczono tytuł 1 osoba. W komórce C1 umieszczono tytuł 2 osoba. W komórce D1 umieszczono tytuł 3 osoba. W kolumnie A, w komórkach od A2 do A13 wpisano kolejne liczby porządkowe. W kolumnie B w komórkach od B2 do B13 wpisano imiona. W kolumnie C w komórkach C2, C3, C4, C5, C6, C7, C9, C10, C11 wpisano imiona. W kolumnie D w komórkach D2, D3, D4, D5, D7 wpisano imiona. Dodatkowo zaprezentowano formułę do obliczeń. W kolumnie F, w komórce F1 wpisano tytuł Osoba. W komórkach od F2 do F7 wpisano imiona. W kolumnie G, w komórce G1 pisano tytuł Ile razy. W komórce G2 wpisano formułę, która brzmi następująco =(SUMA(DŁ($B$2:$D$13))-SUMA(DŁ(PODSTAW($B$2:$D$13;F2;””))))/DŁ(F2) Poniżej w komórkach od G3 do G7 wpisano dane liczbowe.