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.
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)
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ą.
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.
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.
Wyniki ograniczymy sobie (przefiltrujemy) tylko do produktów Ciasteczka i Ciasteczka1.
Teraz możemy je policzyć – wystarczy, że pogrupujemy dane (polecenie Grupowanie według na karcie Narzędzia główne.
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).
To jest właśnie nasz wynik (ograniczony do 2 produktów). Wystarczy, go załadować do Excela.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP