Potrzebujemy policzyć komórki, które zawierają konkretne słowa/frazy. Utrudnieniem jest to, że musimy osobno policzyć, które mają wspólną część (np.: Ciasteczka i Ciasteczka1). Posłużymy się prostym przykładem, gdzie mamy ‘paczki’, do których wkładamy produkty.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 01

To trudne zadanie, ponieważ przykładowa formuła, której używaliśmy w poradzie Excel – Jak policzyć komórki zawierające konkretny tekst – Widzowie #89 (pod nią zostało właśnie zadane pytania o liczenie podobnych fraz) będzie zliczać więcej produktów niż potrzeba (Ciasteczka będą zliczały również Ciasteczka1).

=LICZ.WARUNKI($C$2:$C$25;”*”&F3&”*”;$B$2:$B$25;E3)

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 02

Możemy sobie z tym poradzić ponieważ, poszczególne produkty, są oddzielone od siebie ogranicznikami (przecinkiem i spacją). Dzięki temu moglibyśmy w Excelu podzielić kolumnę za pomocą Tekst jako kolumny na poszczególne produkty i później je zliczać, ale to kłopotliwy i jednorazowy proces. Dlatego my wykorzystamy do tego PowerQuery.

Pobieramy nasze dane z tabeli do PowerQuery (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).

Nie jest nam potrzebny krok zmieniania typu danych, więc go usuwamy. Również nie potrzebujemy kolumny z datą, ale ją przy okazji usunie późniejszy krok.

Jeśli nie szukalibyśmy podobnych słów, to moglibyśmy przefiltrować dane i wybrać tylko te wiersze, które je zawierają.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 03

My jednak chcemy na początek wziąć wszystkie produkty, żebyśmy je mogli policzyć. Dopiero później chcemy skupić się na tych podobnych (Ciasteczka i Ciasteczka1). Wracamy do tego, że nasze produkty w kolumnie są rozdzielane kombinacją znaków (przecinek i spacja). Ważne, że chcemy podzielić produkty w ‘paczce’ na wiersze, a nie kolumny. Wystarczy, że wybierzemy kolumnę Paczka i polecenie Podziel kolumny z karty Narzędzia główne.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 04

A następnie przejdziemy do opcji zaawansowanych, gdzie możemy zmienić standardowy podział po ogranicznikach na kolumny na wiersze. Musimy również ustawić nasz niestandardowy ogranicznik.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 05

Wyniki ograniczymy sobie (przefiltrujemy) tylko do produktów Ciasteczka i Ciasteczka1.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 06

Teraz możemy je policzyć – wystarczy, że pogrupujemy dane (polecenie Grupowanie według na karcie Narzędzia główne.
PQ 5 - Jak policzyć komórki zawierające konkretny tekst 07

Chcemy grupować zarówno po kolumnie Sprzedawca jak i Paczka, więc w zależności od Twojej wersji PowerQuery możliwe, że będziesz musiał zaznaczyć opcje zaawansowane zanim będziesz musiał dodać kolejne kolumny do grupowania. Ważne, że operację jaką chcemy wykonać przy grupowaniu to Zliczanie wierszy. (Ten krok, również usunął kolumny, które nie biorą udziału w operacji grupowania – w tym przykładzie to kolumna Data).

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 08

To jest właśnie nasz wynik (ograniczony do 2 produktów). Wystarczy, go załadować do Excela.

PQ 8 - Jak policzyć komórki zawierające konkretny tekst 09

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP