Excel — Rozrastająca się lista rozwijana — porada 322

W dzisiejszym poście nauczymy się jak stworzyć rozrastająco się listę rozwijaną w Excelu. Załóżmy, że mamy listę kilku sprzedawców a chcemy mieć możliwość dopisania do niej sprzedawcy spoza naszej listy, który automatycznie zostanie dołączony do listy rozwijanej. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Przede wszystkim musimy stworzyć sobie listę unikatowych sprzedawców, aby żaden z nich się nie powtórzył. Aby stworzyć listę unikatowych wartości, używam funkcji LICZ.JEŻELI. Pierwszym argumentem funkcji jest Zakres, czyli od komórki powyżej naszej formuły do komórki powyżej naszej formuły. Wpisujemy zakres $E$1:E1, pierwsza wartość blokujemy bo jest to wartość skrajna a druga ma nam się rozrastać w dół. Drugim argumentem funkcji są kryteria, czyli wszystkie wartości z naszej tabeli (wartości z listy sprzedawców).

Zapis funkcji będzie wyglądał następująco: =LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]).

Zatwierdzamy funkcję. Jako początkowy wynik otrzymujemy 0. Możemy sobie rozwinąć ten wynik za pomocą skrótu klawiszowego F9, aby otrzymać ={0;0;0;0;0}. Taki wynik jest następstwem tego, że żaden sprzedawca nie pojawił się w komórce powyżej aktualnej komórki z formułą. Pożądany efekt uzyskamy dodając kolejne kroki, bo chcemy aby nasza formuła zwróciła nam kolejnego sprzedawcę. Musimy skorzystać z funkcji PODAJ.POZYCJĘ, która będzie szukała wartości 0 w zakresie z funkcji LICZ.JEŻELI na zasadzie dokładnego dopasowania. Zapis funkcji będzie wyglądał następująco:

=PODAJ.POZYCJĘ(LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]);0), czyli funkcja znajdzie pierwszą wartość 0 jaka się pojawi.

Musimy pamiętać, że jest to formuła tablicowa i należy ją zatwierdzić klawiszami Ctrl+Shift+Enter. Otrzymamy wartość 1, ale nas nie interesuje cyfra, tylko wartość jaka znajduje się na pierwszej pozycji (nazwa sprzedawcy). Aby uzyskać taki wynik musimy użyć funkcji INDEKS po kolumnie ze sprzedawcami. Zapis formuły będzie wyglądał następująco:

=INDEKS(tSprzedawcy[Sprzedawca]PODAJ.POZYCJĘ(LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]);0)), zatwierdzamy Ctrl+Shift+Enter. Tak przygotowaną formułę przeciągamy w dół (rys. nr 2).

rys. nr 2 — funkcja INDEKS

Dzięki tej formule otrzymujemy listę sprzedawców bez duplikatów, nie uwzględnia ona powtarzających się wartości. Robimy ją tak długą, żeby mieć pewność, że nie zabraknie nam w niej miejsca. Jednak musimy ją sobie ograniczyć tylko do prawidłowych wartości. Możemy to zrobić za pomocą funkcji LICZ.JEŻELI. Pierwszym argumentem funkcji jest Zakres, czyli komórki E2:E13, musimy pamiętać zablokować bezwzględnie zakres za pomocą skrótu klawiszowego F4. Drugim argumentem to szukane wartości, czyli "?*". Są to dwa symbole wieloznaczne w Excelu, wykorzystywane między innymi w funkcji LICZ.JEŻELI. Zapis formuły będzie wyglądał następująco: =LICZ.JEŻELI($E$2:$E$13; "?*")

Zapis ten oznacza, że szukamy komórki w której pojawi się przynajmniej jeden z tych znaków. Jak sobie zatwierdzimy formułę, otrzymamy cyfrę 4 (ilość pozycji spełniających warunek). Funkcja ta nie zwraca uwagę na wartość 0 i na błędy (rys. nr 3).

rys. nr 3 — Funkcja LICZ.JEŻELI

Listę rozwijaną tworzy się często za pomocą funkcji PRZESUNIĘCIE, ale ta funkcja jest nietrwała i często się przelicza. My stworzymy listę rozwijaną za pomocą funkcji INDEKS, która zwraca odwołanie do zakresu (blokujemy za pomocą klawisza F4) i numer wiersza G2.

Zapis formuły będzie wyglądał następująco: =$E$2:INDEKS($E$2:$E$13;$G$2).

Funkcja INDEKS zwróci nam ostatnią prawidłową wartość – w naszym przypadku "Wojciech" (rys. nr 4).

rys. nr 4 — Podejrzenie formuły klawiszem F9

W formule otrzymujemy zakres od komórki E2 do wyniku funkcji INDEKS, czyli jeśli podejrzymy sobie wyniki formuły za pomocą klawisza F4 to okazuje się, że wynikiem są nazwy wszystkich unikalnych sprzedawców (rys. nr 5).

rys. nr 5 — Podejrzenie wyników formuły za pomocą klawisza F4

Końcowy zapis formuły będzie wyglądał następująco:

=$E$2:INDEKS($E$2:$E$13; LICZ.JEŻELI($E$2:$E$13; "?*"))

Zastąpiliśmy odwołanie do komórki G2, zapisem formuły, z której powstał tam wynik. Otrzymaliśmy cały zbudowany zakres. Formułę tą musimy skopiować w trybie edycji komórki. Korzystając ze skrótu klawiszowego Ctrl+F3 przechodzimy do okna Menedżera nazw (rys. nr 6)

rys. nr 6 — okno Menedżer nazw

Kolejnym krokiem jest stworzenie nowej nazwy. Zmieniamy nazwę na Sprzedawcy, a następnie w miejscu Odwołuje się do wkleić naszą skopiowaną formułę (rys. nr 7). Zatwierdzamy klikając OK.

rys. nr 7 — Okno Nowa nazwa

Zamykamy Menedżera nazw. Następnie zaznaczamy kolumnę z nazwami sprzedawców i wybieramy polecenie Poprawność danych z karty Dane (rys. nr 8).

rys. nr 8 — polecenie Poprawność danych

Otworzy nam się okno Sprawdzanie poprawności danych, gdzie jako kryterium poprawności wybieramy Listę, a jako źródło ustawiamy nazwę Sprzedawcy(rys. nr 9).

rys. nr 9 — Sprawdzanie poprawności danych

Aby wybrać źródło, rozwijamy okno Wklejanie nazwy za pomocą klawisza F3 i wybieramy nazwę naszego zakresu (rys. nr 10).

rys. nr 10 — Okno Wklejanie nazwy

Zatwierdzamy przyciskiem OK. W efekcie przy każdej komórce z nazwą sprzedawcy utworzyła nam się lista rozwijana.

rys. nr 11 — Listy rozwijane w każdej komórce

Zapomniałem zrobić jednej ważnej rzeczy przy sprawdzaniu poprawności danych, mianowicie w karcie Alert o błędzie musimy odznaczyć Pokazuj alerty po wprowadzeniu nieprawidłowych danych (rys. nr 12). Przypomniałem sobie o tej funkcjonalności oglądając, któryś z filmów Maliny z malinowego bloga o Excelu. Musimy pamiętać o tej czynności, ponieważ chcemy mieć możliwość wprowadzania dodatkowych wartości spoza listy.

rys. nr 12 —
Pokazuj alerty po wprowadzeniu nieprawidłowych danych

Teraz kiedy dopiszemy kolejnego sprzedawcę, będzie on uwzględniony w liście rozwijanej w każdej komórce z nazwami sprzedawców. Możemy budować sobie listę, wybierać sprzedawców z list i wstawiać ich jeszcze raz lub dodawać nowych.


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

Excel — Wyszukiwanie binarne, czyli wyszukiwanie przybliżone w Excelu — porada 319

W dzisiejszej poradzie omówimy wyszukiwanie binarne, czyli wyszukiwanie przybliżone w Excelu. Temat ten omówimy na podstawie przykładowych danych z rysunku nr 1. Wpis ten jest niejako kontynuacją tematu z poprzedniego postu, dotyczącego funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym. Opisaliśmy wtedy obrazowo jak działa dopasowanie przybliżone.

rys. nr 1 — Przykładowe dane

Dopasowanie przybliżone jest trochę bardziej skomplikowane od dokładnego, ale działa szybciej. Musimy pamiętać, żeby zawsze mieć posortowane dane (rosnąco, od najmniejszej do największej lub od A do Z). Kiedy nasze dane nie będą posortowane to funkcja WYSZUKAJ.PIONOWO z dopasowaniem dokładnym znajdzie wynik dla Miś (szuka od góry do dołu), ale jeśli mamy tych wyników mamy bardzo dużą ilość zajmie to sporo czasu. Funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym działa dużo szybciej, ale wymogiem jest, aby dane były posortowane, bo inaczej funkcja może zwrócić nam nieprawidłowy wynik.

Aby dobrze zrozumieć dlaczego może pojawić się nieprawidłowy wynik opiszemy to na konkretnym przykładzie. Wyszukiwanie binarne można powiedzieć, że dzieli na pół nasze tablice, listy danych. Załóżmy, że szukamy Misia, w dopasowaniu przybliżonym nie zaczynamy go szukać od początku tylko od środka listy danych. W naszym przykładzie na środku jest Kowboj, K jest mniejsze od M (bo w alfabecie K jest przed M), więc wszystkie wartości powyżej przestają nas interesować (rys. nr 2).

rys. nr 2 — Działanie dopasowania przybliżonego

Po pominięciu wyników powyżej zostaje nam mniejsza tabelka, dokładnie Pastereczka, Dinozaur i Lalka. Teraz na środku znajduje się Dinozaur, D jest mniejsze od M, więc po raz kolejny pomijamy wszystkie wyniki powyżej Dinozaura (czyli tego który aktualnie sprawdzamy). Zostaje nam jeden wynik – Lalka i otrzymujemy jako wynik całej funkcji cenę Lalki (rys. nr 3).

rys. nr 3 — Sposób działania funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym

Dzieląc tak listy na pół, możemy bardzo szybko znaleźć szukaną wartość, bo nie musimy sprawdzać niejako wszystkich wyników. Pamiętajmy, że funkcja WYSZUKAJ.PIONOWO znajduje tylko jedną wartość – pierwszą na którą trafi.

Podsumowując funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym może zwrócić nam nieprawidłowy wynik dla nieposortowanych danych.

W drugim przykładzie mamy posortowane dane. Zaczynamy szukać od polowy listy i trafiamy akurat na szukaną nazwę, więc nie kontynuujemy tej drogi z poprzedniego przypadku, bo Miś=Miś (rys. nr 4).

rys. nr 3 — Wyszukiwanie binarne

Należy pamiętać, że Excel robi coś jeszcze przy dopasowaniu przybliżonym  nie kończy wyszukiwania gdy znajdzie wartość tylko sprawdza dane poniżej, czy nie powtórzyła się szukana wartość. Jeśli pod spodem jest więcej danych o takiej samej nazwie (Miś), to Excel zawsze jako ostateczny wynik poda nam wartość przypisaną dla ostatniego wystąpienia tej nazwy (rys. nr 5), ale takie same wartości muszą być tuż pod sobą. Jeśli nazwa Miś powtórzy się drugi raz, ale w odstępie kilku nazw to Excel już jej nie znajdzie.

rys. nr 5 — Ostatnia znaleziona wartość jako stateczny wynik

Analogicznie wygląda sytuacja z liczbami, a nawet jest dużo prostsza do zrozumienia (rys. nr 6).

rys. nr 6 — Wyszukiwanie z dopasowanie przybliżonym na wartościach liczbowych

Szukamy wartości 62, aby wystawić ocenę. Sprawdzamy wartość w połowie tabelki, otrzymujemy 70. Widzimy, iż 70>62, więc pomijamy wyniki poniżej wartości którą rozpatrywaliśmy (rys. nr 7). Skracamy nasze dane do wartości powyżej 70.

rys. nr 7 — Zasada działania wyszukiwania z dopasowaniem przybliżonym na liczbach

Rozpatrujemy teraz górną połowę wyników, sprawdzamy wartość 50 (w środku danych), wiemy że 50<60, więc pomijamy wartości powyżej 50 w tabeli. Pozostaje nam jedna wartość 60, w ten sposób otrzymujemy wynik – Przeciętnie. Myślę, że wyjaśniłem wystarczająco temat wyszukiwania binarnego. Jeśli chcesz poznać bardziej precyzyjnie zasady działania wyszukiwania przybliżonego w Excelu przeczytaj post na forum Billa Szysz http://www.excelforum.pl/topics1/formula-zwracajaca-tekst-ze-zbioru-komorek-vt51013.htm


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

Wyłączenie sprawdzania poprawności formuł — porada #69

Jak wyłączyć irytujące sprawdzanie poprawności formuł w Excelu?

Z tego wideo dowiesz się jak w Excelu wyłączyć tą irytującą funkcję sprawdzania poprawności formuł. Dzięki czemu już nigdy nie zobaczysz tych irytujących zielonych kwadracików (a może trójkątów ;)) za każdym razem, gdy Excel stwierdzi, że formuła jest błędna.


Wyłączenie sprawdzania poprawności formuł — porada #69

Wystarczy, że odznaczysz jeden checkbox tak jak na obrazie poniżej:

Odznaczony checkbox Włącz sprawdzanie błędów w tle

Odznaczony checkbox Włącz sprawdzanie błędów w tle

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Wyłączenie sprawdzania poprawności formuł — porada #69

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

Jak zaznaczyć urlop w kalendarzu — widzowie #116

Załóżmy, że masz kalendarz i chcesz w nim zaznaczyć dni na które przypada urlop. Kolejne urlopy mamy zapisane jako daty od do.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 01

Żeby to zrobić musimy najpierw napisać formułę, która będzie sprawdzać, czy dana data jest jednocześnie większa bądź równa dacie od i mniejsza bądź równa dacie do. Tylko nie będziemy tego robić na pojedynczych datach tylko od razu na kolumnie dat od i kolumnie dat do. Standardowo przy formatowaniu warunkowym najpierw piszemy formułę w komórkach obok, a jak ją przetestujemy to przeklejamy do formatowania warunkowego.

Czyli zaczynamy od porównania pojedynczej daty (komórka C3) z kolumną dat od:

=C3>=$AT$2:$AT$14

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 02

Powyższa operacja porównania zwróci nam tablicę wartości PRAWDA i FAŁSZ. Tą operację porównania musimy połączyć jeszcze ze sprawdzeniem czy pojedyncza data (C3) jest mniejsza bądź równa od dat do (końca urlopu). Najszybciej połączymy te operacje porównania za pomocą mnożenia. Musimy tylko pamiętać o kolejności operacji dlatego operacje porównania musimy wstawić w nawiasach.

=(C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14)

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 03

Tym razem gdybyśmy podejrzeli wynik naszej formuły to otrzymalibyśmy tablicę 0 i 1, najczęściej tylko zer. Jedynka pojawiłaby się tylko wtedy, kiedy przypadałby urlop na podstawie konkretnej pary od do.

={0;0;0;0;0;0;0;0;0;0;0;0;0}

Teraz potrzebujemy zsumować wynikową tablicę. Najlepiej to zrobić funkcją SUMA.ILOCZYNÓW – dzięki temu nie będziemy się musieli martwić, że pracujemy z formułami tablicowymi.

=SUMA.ILOCZYNÓW((C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14))

Po przeciągnięciu formuły w bok i dół uzyskamy 1 tam gdzie przypada urlop.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 04

Teraz wystarczy skopiować formułę do formatowania warunkowego po zakresie dat naszego kalendarza i już będę one podświetlały się ponieważ formatowanie warunkowe 0 potraktują jako wartości FAŁSZ, a 1 jako wartości PRAWDA.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 05

P.S. Jeśli nie chcesz, żeby w tym mini kalendarzu urlopy zaznaczały się podwójnie, czyli na datach, które nie przypadają na konkretny miesiąc w wierszu musisz dopisać jeszcze jeden warunek do formuły:

=SUMA.ILOCZYNÓW((C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14)*(MIESIĄC($B3)=MIESIĄC(C3)))

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 06

Mini kalendarz w arkuszu — porada #167
https://www.youtube.com/watch?v=xfsTU4IVP_k

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