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.
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.
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.
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.
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.
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
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.
W oknie, które się otworzy
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.
Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.
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.
Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.
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
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
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 😉
