Niedawno nagrałam film na YB, w którym pokazywałam metodę na wyodrębnienie klientów, którym zostało przypisanych kilka numerów, a powinien być tylko jeden. W filmie pokazywałam metodę bardzo prostą: najpierw formułami wyodrębniłam wszystkich poprawnych i niepoprawnych klientów, następnie filtrem wybrałam tylko tych błędnych, a na koniec za pomocą narzędzia usuń duplikaty wyciągnęłam unikalne wpisy tych błędnych. Metoda prosta jak budowa cepa i skuteczna 🙂 Zobaczcie sami:

Takie rozwiązanie przyszło mi na szybko i załatwiło problem, z którym zgłosiła się do mnie Agnieszka – uczestniczka jednego z moich szkoleń. Natomiast przyznam szczerze, że nie byłam z tego rozwiązania zadowolona. Miało ono zbyt dużo kroków. Ja lubię tworzyć rozwiązania, które w przyszłości da się łatwo wykorzystać ponownie z minimalnym, a najlepiej żadnym effortem. To zdecydowanie takie nie było, więc siedziało mi w głowie i nie dawało spokoju 🙂 Wiedziałam, że lepsze i łatwo-powtarzalne rozwiązanie istnieje. Nie chciałam zaraz pisać makra, bo to byłoby pójście trochę na łatwiznę 🙂 Pomyślałam więc o Power Query, którego kilka dni wcześniej zaczęłam się uczyć. I wymyśliłam 🙂 Tym właśnie rozwiązaniem chciałabym się dziś z Wami podzielić. Przyznam się, że bardzo to narzędzie polubiłam i widzę w nim ogromny potencjał. Dlatego na blogu sukcesywnie będę coraz więcej o nim pisać 🙂

Najpierw zacznę od omówienia problemu, a potem pokażę, jak go rozwiązać za pomocą Power Query. Pamiętajcie jednak, że jeśli korzystacie z Excela w wersji 2010 lub 2013, to Power Query jest dodatkiem do Excela i trzeba go zainstalować. W wersji 2016 – jest już wbudowany. O tym, jak zainstalować Power Query możesz przeczytać tutaj.

Formatka do zadania wygląda tak:

Formatka

Zobaczcie, że klienci zaznaczeni na obrazku na żółto mają przypisane więcej niż jeden numer. Poprawienie by było, gdyby mieli tylko jeden. Moim zadaniem jest więc wyszukanie wszystkich tych, którzy mają przypisanych kilka różnych. Chciałabym więc otrzymać listę z klientami a, c i e.

Przygotowanie danych

Najpierw stworzymy z naszej listy danych Tabelę. Będzie to potrzebne Power Query do zaciągnięcia danych z tej listy. Aby to zrobić, ustaw się w dowolnym miejscu danych i użyj skrótu klawiszowego Ctrl + t i zatwierdź komunikat Excela o tworzeniu tabeli. Excel nazwie sobie tabelę jako np. Tabela1. Jeśli chcesz tę nazwę zmienić – przejdź do karty Narzędzia tabel/Projektowanie do sekcji Właściwości i tam znajdziesz pole z nazwą tabeli. Nie jest to jednak konieczne do prawidłowego jej działania. Raczej kosmetyka, ponieważ tak Excel nazwie zapytanie w Power Query. Ja zostawiłam Tabela1 – w każdej chwili można to zmienić.

Zabawa w Power Query

Aby utworzyć zapytanie w Power Query, trzeba ustawić się w tabeli, którą przed chwilką stworzyliśmy i z karty Power Query na wstążce nacisnąć przycisk From Table/Range, czy też po polsku: Z tabeli/Zakresu. Ja mam polską wersję Excela, ale akurat ten przycisk mam nie przetłumaczony, zobaczcie:

Przycisk

Dotyczy to Excela 2010 i 2013. W 2016 ten przycisk znajduje się na karcie Dane w sekcji Pobieranie i przekształcanie i nazywa się Z tabeli.

Zaraz po wciśnięciu tego przycisku od razu wchodzimy do okienka Power Query, w którym zaczyna się całą zabawa…

Okienko Power Query

Po prawej stronie, w Ustawieniach zapytań, Power Query zapisuje kolejne kroki, które będzie wykonywało z naszymi danymi. Naszym zadaniem jest zdefiniowanie tych kroków. Na wstępnie, zaraz po wczytaniu danych, Power Query proponuje nam 2 kroki: Źródło i Zmieniono typ. Krok Zmieniono typ możemy spokojnie wyrzucić (kliknij na czerwony x obok nazwy  tego kroku).

Teraz naszym zadaniem będzie połączenie obu kolumn. Najpierw więc je należy zaznaczyć, a następnie wybrać polecenie z karty Dodaj kolumnę/ Scal kolumny. POjawi się następujące okienko, w którym wybieramy separator (ja mam średnik, ale nie ma to większego znaczenia dla takich danych) i określamy nazwę scalonej kolumny (ja zostawiłam tę zaproponowaną przez Excela: Scalone):

Scalanie kolumn – okienko

W wyniku dostaliśmy dodatkową kolumnę, która jest połączeniem dwóch poprzednich.

Teraz będziemy chcieli zgrupować nasze dane pod względem nazwy kontrahenta i wyniku scalonej kolumny i przy okazji zliczymy unikalne wiersze w tych grupach. Nową kolumnę nazwijmy Grupa. Oto okienko grupowania:

Grupowanie

Takie dwustopniowe grupowanie: w obrębie kontrahenta grupujemy połączenia kontrahent-numer. Podczas tego grupowania chcemy policzyć unikalne wiersze w takiej grupie, czyli np. klient C zostanie zredukowany do 2 wierszy, gdzie jeden będzie miał połączenie C;5, a drugi C;6. Proces ten doskonale pokazuje poniższy rysunek:

Proces grupowania

Teraz kolumna Scalone nie będzie nam już do niczego potrzebna, więc można ją sobie dla porządku usunąć. Aby to zrobić, kliknij na nią prawym przyciskiem myszy i wybierz opcję Usuń. Efekt będzie taki:

Usunięcie kolumny

I teraz aż się prosi, aby widoczne dane jeszcze raz zgrupować według nazwy kontrahenta i przy okazji zsumować wartości w kolumnie Grupa. Czyli zaznacz kolumnę z nazwą kontrahenta i z karty Przekształć wybierz Grupowanie według i zaznacz następujące opcje:

Grupowanie

W wyniku otrzymamy coś takiego:

Wynik grupowania

Zauważ, że na tej liście mamy liczbę numerów przypisanych do danego kontrahenta. Ponieważ interesują nas tylko ci błędni – wystarczy teraz odfiltrować tych, którzy w kolumnie Liczność mają 1. Robimy to zwykłym filtrem, czyli klikamy na strzałeczkę obok nagłówka Liczność i odhaczamy 1:

Filtrowanie prawidłowych

Teraz pozostaje nam już tylko usunięcie kolumny Liczność, gdyż nie chcemy jej widzieć jako wynik. Wystarczy nam tylko kolumna z nazwą kontrahenta. Klikamy więc prawym przyciskiem myszy na kolumnę Liczność i wybieramy opcję Usuń. Wynik jest następujący:

Wynik

Ostatnim krokiem jet załadowanie wyniku do Excela, czyli przycisk Zamknij i załaduj na karcie Narzędzia główne. W wyniku wszystkiego otrzymujemy kompletnie nową Tabelę w arkuszu Excela. Rozwiązanie to jest o tyle cudowne, że jeśli użytkownik dopisze coś do naszej listy nowych kontrahentów – wystarczy odświeżyć tabelę z wynikiem pracy Power Query (np. Alt + F5) i poznamy wtedy wszystkich kontrahentów o błędnych numerach. Tak wygląda u mnie tabela wynikowa w Excelu:

Wynik w Excelu

Mała tabeleczka, o którą tyle zachodu. Mam nadzieję, że Wam się podobało. Przyznam, że ja jestem zachwycona Power Query i tym rozwiązaniem!

A na koniec tradycyjnie mam dla ciebie film wideo, prezentujący krok po kroku opisane rozwiązanie:

Powiązane produkty

• Power Query – wstęp dla laików – Celem tej lekcji jest wprowadzenie w magiczny świat Power Query wszystkich tych, którzy jeszcze nie mieli do czynienia z tym cudownym narzędziem. Coś tam kiedyś słyszeli, coś widzieli, ale jeszcze nic samodzielnie nie robili.