W dzisiejszym poście omówimy temat rozrastającej się listy rozwijanej z unikatowymi wartościami w Power Query. Rozwiązanie tego zadania w Excelu przedstawione zostało w poradzie nr 322 https://exceliadam.pl/?s=porada+322 . W danych źródłowych mamy już listę rozwijaną a naszym zadaniem jest dodanie do niej kolejnych elementów, chcemy dopisać do listy nowe osoby. Dane, na których omówimy to zagadnienie zostały przedstawione na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Rozwiążemy takie zadanie nie korzystając z formuł, ale przy użyciu Power Query – dodatku do Excela. Pierwszym krokiem jest zaczytanie danych do Power Query. Wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 — Z tabeli

Otworzy nam się edytor zapytań z wczytaną tabelą tSprzedawcy.

rys. nr 3 — Edytor zapytań

Usuwamy krok Zmieniono typ z zastosowanych kroków, bo jest on zbędny. Następnie odfiltrowujemy kolumnę Sprzedawca po wartościach null, czyli klikamy na ikonkę trójkąta w nazwie kolumny Sprzedawca i w podręcznym menu ozdnaczamy checkbox przy wartości null (rys. nr 4). Nasz filtr zatwierdzamy przyciskiem OK.

rys. nr 4 — Odfiltruj dane po wartości null

W kolejnym kroku usuwamy inne kolumny, czyli klikamy prawym przyciskiem myszy na tytuł kolumny Sprzedawca i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).

rys. nr 5 — Usuń inne kolumny

Otrzymamy dane przedstawione na rysunku nr 6.

rys. nr 6

Interesuje nas tylko kolumna Sprzedawca. Chcemy mieć unikatową listę sprzedawców, więc rozwijamy polecenie Usuń wiersze (punkt nr 2 na rysunku nr 7) z karty Narzędzia główne, a następnie wybieramy polecenie Usuń duplikaty (punkt nr 3 na rysunku nr 7).

rys. nr 7 — Usuń duplikaty

Tak przygotowaną listę danych możemy załadować do Excela. W tym celu rozwijamy polecenie Zamknij i załaduj (punkt nr 2 na rysunku nr 8) z karty Narzędzia główne, a następnie wybieramy polecenie Zamknij i załaduj do (punkt nr 3 na rysunku nr 8).

rys. nr 8 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a nastepnie określamy lokalizaję wstawienia danych – Istaniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem Załaduj (rys. nr 9).

rys. nr 9 — Okno Ładowania do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 10.

rys. nr 10 — Dane wczytane do Excela

Zaznaczamy zakres danych w tabeli z zapytania z Power Query a następnie w polu obok paska formuły zmieniamy nazwę tego zakresu na Sprzedawcy (pole oznaczone zieloną strzałką na rysunku nr 11).

rys. nr 11 — Zmiana nazwy zakresu

W kolejnym etapie zaznaczamy zakres w tabeli z danymi źródłowymi i wybieramy polecenie Poprawność danych (punkr nr 2 na rysunku nr 12) z karty Dane.

rys. nr 12 — Poprawność danych

Otworzy nam się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia (rys. nr 13) ustalamy Kryteria poprawności danych i podajemy źródło danych (klawisz F3) – wcześniej nazwany zakres Sprzedawcy z tabeli zaczytanej z Power Query. W karcie Komunikat wejściowy odznaczamy checkbox przy opcji Pokazuj komunikat wejściowy przy wyborze komórki. W karcie Alert o błędzie odznaczamy checkbox przy opcji Pokazuj alerty po wprowadzeniu nieprawidłowych danych. Nie chcemy informacji o błędnie wpisanych danych ponieważ chcemy dopisywać nowe osoby do listy sprzedawców. Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 13 — Użycie klawisza F3

Teraz możemy sobie dopisać sprzedawcę w tabeli z danymi źródłowymi, ale nie ma jej na liście rozwijanej w tej tabeli co przedstawia rysunek nr 14.

rys. nr 14 — Lista rozwijana

Wynika to z podstawowej wady Power Query – nie odświeża się automatycznie. Musimy kliknąc prawym przyciskiem myszy na dowolną komórkę w zakresie Sprzedawcy i z podręcznego menu wybrać polecenie Odśwież (rys. nr 15).

rys. nr 15 — Odśwież

Po odświeżeniu danych z Power Query  dodany sprzedawca będzie widoczny na liście rozwijanej (rys. nr 16).

rys. nr 16

Istnieje możliwość ustawienia automatycznego odświeżania danych za pomocą kodu VBA. Korzystając ze skrótu klawiszowego Alt+F11 możemy przejść do okna Edytora VBA. Jest tam wcześniej przygotowany kod (rys. nr 17).

rys. nr 17 — Alt+F4 przejście do VBA

W arkuszu (Arkusz4 (PQ31)- punkt nr 1 na rysunku nr 18), w którym mamy te listy , musimy dopisać kod VBA. Kod ten będzie działał tylko w momencie, kiedy w naszym arkuszu (Worksheet – punkt nr 2 na rysunku nr 18)) dokona się zmiana (Change – Punkt nr 3 na rysunku nr 18). W sytuacji zmiany w kolumnie A, chcemy aby odpalił się kod VBA i sprawdził czy zmieniane komórki miały część wspólną z kolumną A. Konkretnie sprawdzamy czy zakres który był zmieniany ma część wspólną z kolumną która nas interesuje. Jeśli zmiana nastąpiła w kolumnie A, to nastąpi automatyczne odświeżenie danych w tabeli z Power Query.

rys. nr 18 — Edytor VBA

Zapisujemy nasz kod za pomocą skrótu klawiszowego Ctrl+S. Przechodzimy do Excela i możemy sprawdzić działanie kodu VBA. Dopisujemy kolejnego sprzedawcę (Agnieszka) do danych źródłowych i dane automatycznie się odświeżą i nasz nowy sprzedawca zostanie dodany do listy rozwijanej, co widać na rysunku nr 19.

rys. nr 19 — Dane z kodem z VBA

Podsumowując rozrastającą się listę rozwijaną z unikatowymi wartościami robi się prościej za pomocą Power Query, ale niestety nie jest automatyczna i musimy pamiętać o odświeżaniu danych. Jedynym sposobem na zautomatyzowanie jest dodanie kodu VBA, ale to już temat dla bardziej zaawansowanych użytkowników Excela.

Możemy również zarejestrować makro odświeżania danych w karcie Deweloper, wybierając polecenie Rejestruj makro (punkt nr 2 na rysunku nr 20).

rys. nr 20 — Zarejestruj makro

Otworzy się okno Rejestrowania makra, gdzie wpisujemy nazwę makra i zatwierdzamy przyciskiem OK (rys. nr 21).

rys. nr 21 — Okno rejestrowania makra

Następnie klikamy prawym przyciskiem myszy na dowolną komórkę z zakresu zapytania z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 22).

rys. nr 22 — Odśwież

Następnie klikamy polecenie Zatrzymaj rejestrowanie (punkt nr 2 na rysunku nr 23) z karty Deweloper.

rys. nr 23 — zatrzymaj rejestrowanie makra

Teraz w VBA mamy dostępny nowy Moduł, odpowiadający odświeżeniu danych (zaznaczony zieloną strzałką na rysunku nr 24).

rys. nr 24 — Nowy moduł

Dzięki stworzeniu takiego makra mamy automatycznie rozrastającą się listę rozwijaną.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.