Excel — Listy rozwijane zależne czyszczenie przy zmianach — porada 384

W tym poście nauczymy się jak przy listach rozwijanych zależnych sprawić, żeby lista zależna od listy głównej odpowiednio się podświetlała, jeśli jest wypełniona nieprawidłową wartością. W naszym przykładzie mamy dwie listy: w jednej możemy wybrać Region (Centrum, Wschód, Zachód, Północ i Południe), a w drugiej Sprzedawcę. Istotne jest, że w każdym regionie mamy innych sprzedawców. Jeśli zmienimy region, to nazwa sprzedawcy podświetli się (pokaże, że mamy błąd wynikający z tego, że danego sprzedawcy nie ma w wybranym przez nas regionie). Widać to na rys. nr 1.

Rys. nr 1 – podświetlony sprzedawca niewystępujący w danym regionie

Zagadnienie to omówimy na podstawie przykładowych danych z rys. nr 2.

Rys. nr 2 – przykładowe dane

Naszym zadaniem jest sprawienie, aby komórka z elementem listy zależnej była albo podświetlona albo wyczyszczona po zmianie regiony na głównej liście. Rozwiązanie z podświetleniem nazwy sprzedawcy uzyskamy za pomocą formatowania warunkowego. Natomiast rozwiązanie z czyszczeniem komórki otrzymamy przy użyciu kodu VBA.

Mamy przygotowane wcześniej listy (główną i zależną) oraz powiązaną z tymi listami tabelę, która wyświetla dane dla wybranych wartości. Tabelki te zostały przedstawione na rys. nr 3.

Rys. nr 3 – listy rozwijane

Najpierw musimy sprawdzić czy dana pozycja występuje. Do tego wykorzystamy funkcję PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli komórka z aktualnie wybranym sprzedawcą (u nas G2). Drugi argument funkcji to przeszukiwana_tab, czyli kolumna Sprzedawcy (K2:K6). Trzeci argument to typ_porównania, wybieramy dopasowanie dokładne, czyli wpisujemy wartość 0. Zapis formuły powinien wyglądać następująco:

=PODAJ.POZYCJĘ(G2;K2:K6;0)

Jeśli danego sprzedawcy nie będzie w wybranym regionie to funkcja PODAJ.POZYCJĘ wyświetli nam błąd #N/D (rys. nr 4), co dla formatowania warunkowego oznacza, że nie nakładamy żadnego formatowania.

Rys. nr 4 – błąd wyświetlany przez funkcję PODAJ.POZYCJĘ

Jeśli zmienimy sprzedawcę na takiego, który występuje w wybranym regionie funkcja PODAJ.POZYCJE zwróci nam wartość 1, 2 itd.

Formatowanie warunkowe liczby całkowite różne od zera, traktuje jako wartość logiczną PRAWDA. Za pomocą formatowania warunkowego sprawimy, że kolorystyka z komórki G2 zniknie. Mamy napisaną formułę, którą wykorzystamy w formatowaniu warunkowym (zapis funkcji PODAJ.POZYCJĘ). Kopiujemy zapis formuły w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C. Zaznaczamy komórkę G2 i rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rys. nr 5) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Nowa reguła (punkt 3).

Rys. nr 5 – polecenie Nowa reguła

Otworzy nam się okno Nowej reguły formatowania, gdzie w polu Wybierz typ reguły, wybieramy opcję Użyj formuły do określenia komórek, które należy sformatować (punkt 1 na rys. nr 6). Następnie w polu Edytuj opis reguły wklejamy wcześniej skopiowaną formułę (punkt 2). Teraz wybieramy przycisk Formatuj, aby wybrać rodzaj formatowania (punkt 3).

Rys. nr 6 – okno Nowej reguły formatowania

Otworzy nam się okno Formatowania komórek, gdzie przechodzimy na zakładkę Wypełnienie (punkt 1 na rys. nr 7), następnie wybieramy w polu Kolor tła Brak koloru (punkt 2). Takie ustawienie formatowania zatwierdzamy przyciskiem OK.

Rys. nr 7 – okno Formatowania komórek

Excel wróci do okna Nowa reguła formatowania, które należy zatwierdzić przyciskiem OK.

Jak widać na rys. nr 8, Stanisław jest sprzedawcą z regionu Północ, więc formatowanie warunkowe zadziałało w ten sposób, że zmieniło kolor na Brak wypełnienia.

Rys. nr 8 – brak wypełnienia w komórce G2

Jeśli teraz w komórce G1 wybierzemy np. Zachód, nazwa Sprzedawcy podświetli się nam znowu na czerwono. Wynika to z tego, że funkcja PODAJ.POZYCJĘ zwróciła błąd (komórka H1) – rys. nr 9.

Rys. nr 9 – podświetlenie sprzedawcy nie występującego Zachód

W ten sposób uzyskaliśmy efekt podświetlania kolorystycznego w zależności czy wybrany sprzedawca występuje w danym regionie. Pokazaliśmy jak rozwiązać taki problem za pomocą formatowania warunkowego.

W drugim rozwiązaniu będzie nam potrzebny kod VBA, który jest relatywnie prosty w porównaniu do niektórych kodów. Jednak żeby dobrze go zrozumieć, trzeba się trochę orientować w VBA. Aby przejść do języka VBA korzystamy ze skrótu klawiszowego Alt+F11. Otworzy nam się okno VBA pokazane na rys. nr 10.

Rys. nr 10 – okno VBA

Interesuje nas sytuacja, kiedy zmienimy wartość w komórce G1, czyli Region. W VBA chcemy uzyskać rozwiązanie, które będzie polegało na tym, żeby komórka G2 wyczyściła się ,kiedy zmienimy wartość w komórce G1. Czyli kiedy zmienimy region, chcemy aby komórka zawierająca nazwę sprzedawcy została wyczyszczona.

Pracujemy na arkuszu p384_k, więc w panelu bocznym VBA znajdujemy odpowiednią nazwę (rys. nr 11) i klikamy na nią dwukrotnie.

Rys. nr 11 – wybór odpowiedniego arkusza

Otworzy się nam moduł kodu VBA dla tego arkusza, w którym będziemy pisać kod. W pierwszym polu zamiast domyślnego General wybieramy Worsheet (punkt 1 na rys. nr 12), a w drugiem zamiast SelectionChange wybieramy z listy rozwijanej Change (punkt 2).

Rys. nr 12 – zmiana ustawień kodu dla wybranego arkusza

Ostatnie dwie linijki kodu możemy skasować, są zbędne. Będziemy chcieli zastosować znany kod na przecięcie elementu/ wartości. Kod ten wykorzystuje funkcję Intersect, dla której podajemy dwa zakresy i sprawdzamy czy one się przecięły. Pierwszym naszym zakresem będzie wartość Target, czyli jeśli zmieni się arkusz, to jaki zakres w tym arkuszu ulegnie zmianie. Target to w skrócie zakres, który uległ zmianie. Nasze zadanie polega na sprawdzeniu, czy ten zakres ma część wspólną z drugim zakresem (z komórką G1). Drugi zakres zapisujemy jako Range("G1"). Cały zapis funkcji powinien wyglądać następująco:

Intersect(Target, Range("G1"))

Funkcja ta, jeśli znajdzie część wspólną, zwróci właśnie tą wartość. Jeśli zakresy nie będą miały części wspólnej funkcja zwróci Nothing, czyli pustą komórkę.

Ciężej jest zorientować się jaki zakres zwraca ta funkcja, więc musimy sprawdzić czy nasza funkcja zwraca nic. W tym celu do zapisu funkcji należy dopisać sformułowanie "is nothing". Zapis powinien wyglądać następująco:

Intersect(Target, Range("G1")) is nothing

Jeśli funkcja zwraca nothing, oznacza to, że przecięcia nie było. Nas interesuje odwrotna sytuacja, więc przed nazwą funkcji Intersect musimy dopisać Not. Zapis powinien wyglądać następująco:

Not (Intersect(Target, Range("G1")) is Nothing)

Wykonamy teraz prosty test logiczny, czyli przed zapisem wpisujemy if, natomiast po Then. Pod zapisem tej formuły wstawiamy zakończenie funkcji If w postaci zapisu End If.

Jeśli funkcja Intersect zwraca nic, oznacza to, że nasze zakresy nie mają przecięcia. W takiej sytuacji chcemy otrzymać pusty ciąg znaków (""). Zapis powinien wyglądać następująco (rys. nr 13):

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      Range("G2") = ""

End If

Rys. nr 13 – kod VBA wykorzystujący funkcję If

Możemy łatwo sprawdzić działanie tego kodu. Dla pewności zapisujemy dane z kodu za pomocą skrótu klawiszowego Ctrl+S. Przechodzimy do naszego arkusza w Excelu, gdzie zmieniamy nazwę regionu. Jak widać na rys. nr 14, zmiana nazwy regionu na taki, w którym nie ma aktualnego sprzedawcy (brak przecięcia zakresów) sprawiło, że komórka G2 została wyczyszczona.

Rys. nr 14 – wyczyszczona komórka G2 po zmianie regionu w komórce G1

Możemy pójść o krok dalej w kodzie VBA i np. zamiast wyczyszczenia komórki (pusty ciąg znaków), możemy podać tekst "Podaj sprzedawcę". Zapis kodu wyglądałby wtedy następująco:

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      Range("G2") = "Podaj sprzedawcę"

End If

Wtedy po zmianie region w komórce G1, w komórce G2 wyświetli się napis Podaj sprzedawcę, co widać na rys. nr 15.

Rys. nr 15 – wyświetlenie napisu Podaj sprzedawcę

Jest mały problem, kiedy chcemy zmienić nazwę regionu i się rozmyślimy. VBA automatycznie po rozwinięciu listy rozwijanej (mimo że nie wybierzemy innego regionu) zadziała i wyświetli nam napis Podaj sprzedawcę.

Teraz wykonamy bardziej zaawansowane działania w celu zabezpieczenia się przed taką sytuacją. Przechodzimy do okna VBA, w którym pisaliśmy nasz kod. Możemy użyć funkcji Find. Najpierw musimy określić zakres, na którym ta funkcja ma działać(w naszym przykładzie K2:K8). Funkcja Find będzie szukała naszego wybranego sprzedawcy, czyli musimy podać zakres (Range("G2")). Jak funkcja Find znajdzie wybranego sprzedawcę to powinna zwracać odpowiedni zakres, natomiast jeśli nie znajdzie, powinna zwracać nothing, czyli nic. Zapis całej formuły powinien wyglądać następująco (rys. nr 16):

If Not (Intersect(Target, Range("G1")) is Nothing)  Then

      If Range("K2:K8").Find (Range("G2")).Value, LookIn:=xlValues) is Nothing   Then

           Range("G2") = "Podaj sprzedawcę"

      End If

End If

Rys. nr 16 – kod VBA

Zastosowanie funkcji Find w funkcji If sprawiło, że wyświetli się napis Podaj sprzedawcę tylko wtedy, kiedy faktycznie ta zmiana w komórce G1 nastąpi.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

Power Query 31 — Rozrastająca się lista rozwijana

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ą.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama

VBA #23 — Jak wykorzystać funkcje makr 4.0 w VBA

W poradzie 305 wspominałem o funkcjach makr 4.0. Pomogły mi wyciągnąć listę plików z katalogu, ale był z nimi jeden problem dla Polaków. Ciężko było znaleźć ich nazwę po Polsku.

Mi przyszedł do głowy jeden pomysł na poprawne tłumaczenie ich nazw za pomocą króciutkiego kodu VBA. Zadaniem tego kodu jest po prostu dodanie nazwy, która będzie odwoływać się do przykładowej funkcji makr 4.0.

Ponieważ VBA jest angielsko języczny, więc funkcję makr 4.0 wpisujemy w nim po angielsku (listę funkcji makr 4.0 z dokładnym opisem po angielsku możesz pobrać na stronie:
https://www.myonlinetraininghub.com/excel-4-macro-functions).


Sub Makro1()
ActiveWorkbook.Names.Add Name:="Test1", RefersToR1C1:="=FILES(Sheet1!R2C1)"
ActiveWorkbook.Names.Add Name:="Test2", RefersToR1C1:="=GET.CELL(42)"
End Sub

Po uruchomieniu tego makra do nazw Excela (patrz Menadżer nazw na karcie Formuły) dodadzą się dwie nazwy korzystające już z polskich nazw funkcji makr 4.0, gdyż mój Excel ‘mówi’ po polsku 😉 Po prostu między VBA, a Excelem funkcje zostały przetłumaczone. Dzięki temu będziemy już mogli korzystać z potrzebnych nam funkcji makr 4.0 już bez pomocy VBA.

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

Odświeżanie zapytań PowerQuery za pomocą VBA #22

W tym wpisie chcemy napisać kod VBA, który odświeża wszystkie zapytania albo tylko pojedyncze zapytanie PowerQuery. Odświeżanie wszystkich zapytań jest prostsze, ponieważ możesz nagrać ten kod sam i później podpiąć go do przycisku.
Najpierw musimy włączyć rejestrowanie makr — polecenie Zarejestruj makro z karty Deweloper.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 01

W oknie, które się otworzy

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 02

nadajesz nazwę makru (np.: Refresh), ewentualnie podpinasz skrót klawiszowy. To trochę bez sensu ponieważ polecenie odśwież wszystko z karty Dane (rys. 3) ma już przypisany do siebie skrót klawiszowy (Ctrl + Alt + F5). Upewniasz się, że makro zostanie zapisane do tego skoroszytu i naciskasz klawisz OK. Teraz ważne, żebyś nie klikał nigdzie w komórki arkusza, tylko od razu przeszedł do karty Dane i kliknął polecenie odśwież wszystko.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 03

Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.
VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 04

Teraz nawet bez zaglądania do kodu makra możesz wstawić dowolny kształt lub obraz i kliknąć na niego prawym przyciskiem myszy, by z podręcznego menu wybrać opcję przypisz makro.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 05

Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.

VBA 22 - Odświeżanie zapytań PowerQuery za pomocą VBA 06

I już możesz odświeżać wszystkie zapytania PowerQuery (oraz tabele przestawne i inne połączenia) za pomocą kliknięcia w ‘przycisk’ (kształt/obraz).

Cały kod makra wygląda tak:

Sub Refresh()
ActiveWorkbook.RefreshAll
End Sub

Jeśli chciałbyś odświeżać tylko pojedyncze zapytanie to trudniejsze zadanie i rejestrator makr Ci raczej w tym nie pomoże (patrz film), za to możesz skorzystać z kodu poniżej.

Sub Makro1()
ActiveWorkbook.Connections("Zapytanie — tProdukty_k").Refresh
End Sub

Ważne, że do nazwy zapytania dodajesz prefiks "Zapytanie — " (w innych język ten prefiks jest inny), a i myślnik nie jest standardowy, więc najlepiej, żebyś skopiować cały kod VBA (w Excelu ten myślnik ma KOD = 151).

Na koniec jeszcze pętla (jaką możesz znaleźć na różnych portalach w internecie), która pomoże Ci odświeżyć wszystkie zapytania PowerQuery w pliku (zmieniłem tylko prefiks na polski):

Sub Makro1()

Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
If Left(con.Name, 12) = "Zapytanie — " Then
Cname = con.Name
With ActiveWorkbook.Connections(Cname).OLEDBConnection
.BackgroundQuery = False 'or true, up to you
.Refresh
End With
End If
Next

End Sub

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

Kwota słownie liczba słownie — odcinek #21

Chyba każdy program księgowy ma taką funkcjonalności, że potrafi zamienić liczbową reprezentację kwoty na jej reprezentację słowną, czyli przykładowo:

123, 45zł -> sto dwadzieścia trzy złote i czterdzieści pięć groszy

Jak to zrobić w Excelu? Potrzeba odpowiedni kod. Niestety nie jest znany mi jego autor, bo na żadnej stronie, gdzie go widziałem nikt nie wiedział kto go napisał. Mi on się bardzo podoba, bo jak się go odpowiednio podzieli jest naprawdę prosty do zrozumienia.
Ja żeby lepiej go zrozumieć dołożyłem do niego jeszcze część odpowiedzialną za przekształcenie miliardów.

Kod składa się z dwóch funkcji, które z komentarzami zamieszczam poniżej:

Function Słownie(x As Variant) As String 'dla liczb od -999 999 999 999.99 do 999 999 999 999.99
'Sprawdzamy czy podana kwota jest mniejsza od zera jeśli jest to dopisujemy do kwoty słownie słowo minus
If x < 0 Then w = w & "minus "

'Konwertujemy liczbę na zapis, który będzie pokazywał zawsze miliardy miliony, tysiące i jednostki
'nawet jeśli będą to same zera
x = Format(Abs(x), "000 000 000 000.00")
'części odpowiedzialne za miliardy, miliony itd. przypisujemy do oddzielnych zmiennych 3 cyfrowych
'wiodące zera również są w nich brane pod uwagę, a do groszy musimy dodać jedna zero na początku
'żeby później działało odpowiednio z funckją trzy
mld = Left(x, 3): m = Mid(x, 5, 3): t = Mid(x, 9, 3): j = Mid(x, 13, 3): g = "0" & Right(x, 2)

'Sprawdzamy miliardy i w zależności od ich ilości nadajemy inne końcówki
'analogicznie robimy z milionami, tysiącami, jednościami i groszami
Select Case mld
Case 0
Case 1
w = "jeden miliard "
Case Else
'za pomocą funkcji trzy sprawdzamy jaka jest liczba miliardów, a późniejszych częściach milionów itd.
w = w & trzy(mld)
'dodajemy odpowiedni "końcówek" na podstawie połączeniu odpowiednich warunków
If Mid(mld, 2, 1) <> 1 And (Right(mld, 1) = 2 Or Right(mld, 1) = 3 Or Right(mld, 1) = 4) Then w = w & "miliardy " Else w = w & "miliardów "
End Select

'Sprawdzanie milionów analogicznie jak miliardów
Select Case m
Case 0
Case 1
w = "jeden milion "
Case Else
w = w & trzy(m)
If Mid(m, 2, 1) <> 1 And (Right(m, 1) = 2 Or Right(m, 1) = 3 Or Right(m, 1) = 4) Then w = w & "miliony " Else w = w & "milionów "
End Select

'Sprawdzanie tysięcy analogicznie jak miliardów
Select Case t
Case 0
Case 1
w = w & "jeden tysiąc "
Case Else
w = w & trzy(t)
If Mid(t, 2, 1) <> 1 And (Right(t, 1) = 2 Or Right(t, 1) = 3 Or Right(t, 1) = 4) Then w = w & "tysiące " Else w = w & "tysięcy "
End Select

'Sprawdzanie jedności analogicznie jak miliardów
Select Case j
Case 0
If mld = 0 And m = 0 And t = 0 Then w = w & "zero złotych " Else w = w & "złotych "
Case 1
If mld = 0 And m = 0 And t = 0 Then w = w & "jeden złoty " Else w = w & "jeden złotych "
Case Else
w = w & trzy(j)
If Mid(j, 2, 1) <> 1 And (Right(j, 1) = 2 Or Right(j, 1) = 3 Or Right(j, 1) = 4) Then w = w & "złote " Else w = w & "złotych "
End Select

'sprawdzanie groszy analogicznie jak miliardów
Select Case g
Case 0
w = w '& "zero groszy"
Case 1
w = w & "jeden grosz"
Case Else
w = w & trzy(g)
If Mid(g, 2, 1) <> 1 And (Right(g, 1) = 2 Or Right(g, 1) = 3 Or Right(g, 1) = 4) Then w = w & "grosze" Else w = w & "groszy"
End Select

Słownie = w
End Function


Function trzy(x As Variant) As String
'dzielimy trzy cyfry na część odpowiedzialną za setki, dziesiątki i jedności
x3 = Val(Left(x, 1)): x2 = Val(Mid(x, 2, 1)): x1 = Val(Right(x, 1))
'Spradzamy jaka to setka
If x3 = 9 Then w = w & "dziewięćset "
If x3 = 8 Then w = w & "osiemset "
If x3 = 7 Then w = w & "siedemset "
If x3 = 6 Then w = w & "sześćset "
If x3 = 5 Then w = w & "pięćset "
If x3 = 4 Then w = w & "czterysta "
If x3 = 3 Then w = w & "trzysta "
If x3 = 2 Then w = w & "dwieście "
If x3 = 1 Then w = w & "sto "
'Sprawdzamy jaka to dziesiątka łącząc z ewentualną setką
If x2 = 9 Then w = w & "dziewięćdziesiąt "
If x2 = 8 Then w = w & "osiemdziesiąt "
If x2 = 7 Then w = w & "siedemdziesiąt "
If x2 = 6 Then w = w & "sześćdziesiąt "
If x2 = 5 Then w = w & "pięćdziesiąt "
If x2 = 4 Then w = w & "czterdzieści "
If x2 = 3 Then w = w & "trzydzieści "
If x2 = 2 Then w = w & "dwadzieścia "
'Sprawdzamy czy to nie jest nastka łącząc z ewentualną setką
If x2 = 1 Then
If x1 = 9 Then w = w & "dziewiętnaście "
If x1 = 8 Then w = w & "osiemnaście "
If x1 = 7 Then w = w & "siedemnaście "
If x1 = 6 Then w = w & "szesnaście "
If x1 = 5 Then w = w & "piętnaście "
If x1 = 4 Then w = w & "czternaście "
If x1 = 3 Then w = w & "trzynaście "
If x1 = 2 Then w = w & "dwanaście "
If x1 = 1 Then w = w & "jedenaście "
If x1 = 0 Then w = w & "dziesięć "
End If
'Sprawdzamy jaka to cyfra jedności i dołączamy do ewentualnej wcześniejszej części
If x2 <> 1 Then
If x1 = 9 Then w = w & "dziewięć "
If x1 = 8 Then w = w & "osiem "
If x1 = 7 Then w = w & "siedem "
If x1 = 6 Then w = w & "sześć "
If x1 = 5 Then w = w & "pięć "
If x1 = 4 Then w = w & "cztery "
If x1 = 3 Then w = w & "trzy "
If x1 = 2 Then w = w & "dwa "
If x1 = 1 Then w = w & "jeden "
End If
trzy = w
End Function

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

Jak cofnąć makro — odcinek #20

Ogólnie jeśli chcesz Twoje makro ruszy arkusz, czy też dane w komórkach, to również wyczyści bufor cofania i nie będziesz mógł cofnąć, ani czynności, które wykonało makro, ani czynności, które wcześniej wykonywałeś — Ctrl + Z nie zadziała.

Jeśli Twoje makro nie rusza arkusza, np: wyświetla komunikat za pomocą funkcji Msgbox, to bufor cofania nie jest czyszczony i dalej możesz cofnąć swoje wcześniejsze czynności za pomocą skrótu klawiszowe.

Niektórzy chcieliby móc cofać swoje makra, dlatego został stworzony kod, który wykorzystuje pewną sztuczkę, a mianowicie w swoim makrze na samym jego początku dodajesz linijkę kodu, która zapisuje Twój plik:
ActiveWorkbook.Save

Następnie jeśli Twoje makro zadziałało niepoprawnie uruchamiasz makro, które powinieneś przechowywać w innym pliku np: skoroszycie makr osobistych, które pobiera nazwę Twojego pliku (aktywnego pliku), zamyka go bez zapisywania, a następnie ponownie uruchamia:

Sub Cofanie()
Dim AktualnyArkusz As String
'Zapisujemy nazwę aktywnego skoroszytu/pliku
AktualnyArkusz = ActiveWorkbook.FullName
'zamykamy go bez zapisywania zmian
ActiveWorkbook.Close SaveChanges:=False
'ponownie go otwieramy
Workbooks.Open Filename:=AktualnyArkusz
End Sub

Czyli symuluje to co musiałbyś zrobić ręcznie, żeby odzyskać swoje dane 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela