W dzisiejszym poście poznamy sposób jak policzyć komórki zawierające konkretny tekst na przykładzie ilości paczek zawierających ciasteczka. Przykładowe dane do tego zadania zostały przedstawione na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Rozwiązanie tego zadania w Excelu zostało omówione w pytaniu od widzów, w filmie Excel — Jak policzyć komórki zawierające konkretny tekst — Widzowie #89 (https://www.youtube.com/watch?v=LghJGhKo9Gw). Pytanie od widzów polegało na znalezieniu formuły, aby policzyć komórki, w których dany produkt wystąpił więcej niż jeden raz.

W Power Query rozwiązanie tego zadania jest proste. W pierwszym kroku musimy zaczytać nasze dane bazowe do Power Query , w tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli/zakresu

Otworzy nam się edytor zapytań z wczytaną tabelą tPaczki2 (rys. nr 3).

rys. nr 3 — Edytor zapytań

Kolumna Data jest nam niepotrzebna, więc możemy ją sobie usunąć. Zaznaczamy tą kolumnę i klikamy prawym przyciskiem myszy na jej nazwę, następnie z podręcznego menu wybieramy polecenie Usuń (rys. nr 4).

rys. nr 4 — Polecenie Usuń

Otrzymamy dane przedstawione na rysunku nr 5, zawierające kolumnę z nazwą sprzedawcy oraz zawartością paczki dla danego sprzedawcy.

rys. nr 5 — Dane po usunięciu kolumny

W Power Query zadanie wyznaczenia ilości występowania produktu ciasteczka jest bardzo łatwe. Zaznaczamy kolumnę Produkt, rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 6) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt 3 na rys. nr 6).

rys. nr 6 — Według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu Wybierz lub wprowadź ogranicznik z listy rozwijanej musimy wybrać niestandardowe, a następnie w polu niżej wpisać nasz ogranicznik, czyli przecinek i spację. Rozwijamy również opcje zaawansowane, aby podzielić dane na Wiersze, ponieważ jest to idealne rozwiązanie dla naszego przykładu. Tak przygotowane parametry podziału kolumny zatwierdzamy przyciskiem OK (rys. nr 7).

rys. nr 7 — Dzielenie według ogranicznika

Otrzymamy dane przedstawione na rysunku nr 8. W tych danych każda paczka zostaje podzielona na pojedyncze produkty.

rys. nr 8 — Podzielone dane

Power Query zmienił automatycznie typ danych, więc usuwamy krok Zmieniono typ z Zastosowanych kroków.

Na tym etapie zaznaczamy obie kolumny, a następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 9).

rys. nr 9 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie Power Query automatycznie wybrał zaawansowane grupowanie z obiema kolumnami. W polu nazwa nowej kolumny wpisujemy Ilość, wybieramy operację Zlicz wiersze. Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 10).

rys. nr 10 — Parametry grupowania według

Otrzymamy dane przedstawione na rysunku nr 11.

rys. nr 11 — Pogrupowane dane

Otrzymaliśmy w kolumnie Ilość wynik grupowania, czyli ilość wystąpienia w danych konkretnych produktów. Tak przygotowane dane wystarczy załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 12).

rys. nr 12 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie musimy określić sposób wyświetlania danych – tabela oraz miejsce ich wstawienia – Istniejący arkusz i wskazać konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 13).

rys. nr 13 — Importowanie danych

Otrzymamy dane w Excelu przedstawione na rysunku nr 14.

rys. nr 14 — dane zaimportowane do Excela

Kiedy posortujemy dane w kolumnie Produkt, możemy zauważyć, że produkt ciasteczka występuje na liście tylko 3 razy (rys. nr 15).

rys. nr 15 — posortowane dane

Wynika to z tego, że jeden sprzedawca (Robert) nie sprzedał żadnych ciasteczek. Grupowanie Power Query działa podobnie jak grupowanie w tabelach przestawnych, czyli jeśli jakiegoś produktu jest zero, to nie pokaże się on na liście.

W Power Query jest również możliwość wypisania wszystkich sprzedawców wraz ze wszystkimi produktami, mimo że ich ilość wynosi 0, ale jest to skomplikowane zadanie dla bardziej zaawansowanych użytkowników.