Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
W
dzisiejszym poście nauczymy się jak stworzyć błyskawiczną listę rozwijaną na
pomocą skrótu klawiszowego Alt + Strzałka w dół. Film ten powstał na podstawie
komentarza Pana Jarosława pod filmem z poradą nr 322 (https://www.youtube.com/watch?v=9hpcn-KuwnI).
Zadanie to
wykonamy na podstawie przykładowych danych z rysunku nr 1.
Rys. nr 1 – przykładowe dane
Kiedy już stworzymy sobie listę rozwijaną za pomocą polecenia Sprawdzanie poprawności danych z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Sprawdzanie poprawności danych
Otrzymamy listę rozwijaną przedstawioną na rys. nr 3.
Rys. nr 3 – stworzona lista rozwijana
Powyższą listę rozwijaną możemy rozwijać bez używania myszki, korzystając ze skrótu klawiszowego Alt+strzałka w dół (rys. nr 4).
Rys. nr 4 – rozwijanie listy za pomocą skrótu klawiszowego Alt+Strzałka w dół
Warto zapamiętać, że ten skrót klawiszowy działa również na zwykłych danych wpisanych w dowolną kolumnę. Mianowicie jeśli mamy np. listę produktów i ustawimy aktywną komórkę pod naszymi danymi (komórka A11), następnie skorzystamy ze skrótu klawiszowego Alt+strzałka w dół, otrzymamy automatyczną listę danych (rys. nr 5).
Rys. nr 5 – automatyczna lista danych
Istotne jest, że ta lista jest unikatowa. Wybierzemy z powyższej listy np. produkt Kokosy, następnie przejdziemy na komórkę poniżej i ponownie skorzystamy ze skrótu klawiszowego Alt+Strzałka w dół, to otrzymamy unikatową listę rozwijaną, czyli żadne elementy nie będą się w niej powtarzać (rys. nr 6).
Rys. nr 6 – automatyczna unikatowa lista rozwijana
Możemy
zauważyć, że produkt Kokosy występują na naszej liście dwukrotnie, natomiast na
liście rozwijanej są tylko raz. Jest to niewątpliwe duże udogodnienie w pracy w
Excelu.
Kolejnym
plusem tego sposobu tworzenia listy rozwijanej jest sytuacja, kiedy wpiszemy w
komórkę pierwszą literę produktu i skorzystamy ze skrótu klawiszowego, to
pojawi nam się lista rozwijana, gdzie aktywny element ustawi się na pierwszym
elemencie rozpoczynającym się od danej, wpisanej przez nas litery (rys. nr 7).
Rys. nr 7 – automatyczne przejście na liście do produktów rozpoczynających się wpisaną literą
Można wpisać
więcej znaków, wtedy na liście podświetlony zostanie element odpowiadający tym
znakom.
Nie jest to
lista rozwijana z pełnym wyszukiwaniem, ale jest to szybkie rozwiązanie, które
w wielu sytuacjach ułatwi i usprawni nam pracę. Lista uzyskana ze sprawdzania
poprawności danych nie daje nam możliwości wpisania znaków, od których ma się
zaczynać nasz szukany element (rys. nr 8).
Rys. nr 8 – możliwości listy rozwijanej ze sprawdzania poprawności danych
Jeśli interesuje Cię lista rozwijana z pełną opcją wyszukiwania to polecam dodatek Jona Acampora. W poradzie nr 379 pokazywałem jak zainstalować Add-inn (https://www.youtube.com/watch?v=ChAG8UtGkPE), natomiast w poradzie nr 380 jak wyciągnąć z niej kod (https://www.youtube.com/watch?v=ChAG8UtGkPE). Wyciągnięcie kodu jest potrzebne, aby ten dodatek przenosił się wraz z plikiem przy jego kopiowaniu, a nie był podpięty do jednego komputera.
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 będziemy kontynuować temat sprawdzania poprawności danych.
Nauczymy się tak ustalać sprawdzanie poprawności (walidację) danych, aby było
możliwe wpisanie daty, która przypada na dzień pracujący, pomija weekendy oraz
święta. Zadanie to wykonamy na podstawie przykładowych danych z rysunku nr 1,
gdzie dodatkowo zostały wypisane dni świąteczne wypadające w roku 2019.
Rys. nr 1 – przykładowe dane
W Excelu
wystarczy nam do tego zadania funkcja DNI.ROBOCZE, która zwraca dni
robocze pomiędzy dwiema datami. Pierwszym argumentem funkcji jest data_pocz,
czyli data początkowa. Drugi argument funkcji to data_końc, czyli data
końcowa. Trzecim argumentem (opcjonalnym) są święta, czyli dni
świąteczne, które chcemy odliczyć z dni roboczych. Nasuwa się tutaj pytanie, co
się stanie, kiedy w zapisie funkcji jako dzień początkowy i końcowy podamy tę
samą datę. Zapis funkcji powinien wyglądać następująco:
=DNI.ROBOCZE(B3;B3)
W takiej
sytuacji funkcja DNI.ROBOCZE zwróci nam wartość 1, kiedy ten dzień będzie dniem
pracującym lub wartość 0, kiedy data będzie dniem wolnym od pracy (weekend lub
święta) – rys. nr 2.
Rys. nr 2 –wartość 0 zwrócona przez funkcję DNI.ROBOCZE dla daty z weekendu
Taki zapis
wystarczy nam do określenia warunku poprawności danych, ponieważ wartość 0
zostanie zinterpretowane przez Excela jako FAŁSZ, czyli Excel odrzuci nam taką
datę. Natomiast po wpisaniu poprawnej daty otrzymamy wartość 1, odpowiadającą
wartości logicznej PRAWDA. Do poprzedniego zapisu funkcji dołożymy sobie tylko
opcjonalny trzeci argument funkcji, czyli święta określone w osobnej tabeli.
Zakres ten blokujemy bezwzględnie za pomocą klawisza F4. Zapis funkcji powinien
wyglądać następująco:
=DNI.ROBOCZE(B3;B3;$I$2:$I$14)
Rys. nr 3 – poprawny zapis funkcji DNI.ROBOCZE z uwzględnieniem świąt
W trybie
edycji komórki kopiujemy powyższą formułę za pomocą skrótu klawiszowego Ctrl+C,
następnie użyjemy jej do sprawdzenia poprawności danych dla naszych
przykładowych dat zakupów z kolumny Data.
Zaznaczamy komórki,
na których chcemy użyć sprawdzania poprawności danych (B3:B6) i wybieramy ikonę
polecenia Poprawność danych z karty Dane (rys. nr 4).
Rys. nr 4 – polecenie Poprawność danych
Otworzy nam
się okno Sprawdzania poprawności danych, gdzie w karcie Ustawienia, w polu Dozwolone
wybieramy typ Niestandardowe. Następnie w polu Formuła wklejamy
wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Tak
ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK
(rys. nr 5).
Rys. nr 5 – okno sprawdzania poprawności danych
Po
zatwierdzeniu sprawdzania poprawności danych możemy sprawdzić jego działanie,
tzn. wpisujemy dowolną datę. Jeśli wpisana data będzie wypadała w weekend lub w
dni świąteczne to pojawi się komunikat przedstawiony na rys. nr 6.
Rys. nr 6 – komunikat dla błędnej daty (daty dnia wolnego od pracy)
Możemy
dodatkowo wykonać sprawdzanie poprawności danych dla dostaw do klienta tylko w
dniach od poniedziałku do czwartku, czyli z wyłączeniem piątku oprócz weekendów
i świąt. Aby widzieć na jaki dzień tygodnia wypada wpisana przez nas data,
otwieramy okno Formatowania komórek za pomocą skrótu klawiszowego Ctrl+1,
następnie przechodzimy do kategorii Niestandardowe i w polu Typ wpisujemy skrót
ddd. Zapis powinien wyglądać następująco: rrrr-mm-dd, ddd. Ustawienie
formatowania zatwierdzamy przyciskiem OK (rys., nr 7).
Rys. nr 7 – okno formatowania komórek
Otrzymamy
datę z oznaczeniem dnia tygodnia przedstawioną na rys. nr 8.
Rys. nr 8 – data z oznaczeniem dnia tygodnia
Z tym
zadaniem możemy poradzić sobie wykorzystując niejako funkcję rozwijającą
możliwości standardowej funkcji DNI.ROBOCZE. Użyjemy tutaj funkcji DNI.ROBOCZE.NIESTAND,
która zwraca dni robocze między dwoma datami z niestandardowymi parametrami
dotyczącymi weekendów. Dwa pierwsze argumenty są takie same jak dla
standardowej funkcji. Trzeci argument funkcji to weekend, czyli dni
tygodnia jakie chcemy przyjąć za weekend. Mamy tutaj listę możliwości
przedstawioną na rys. nr 9.
Rys. nr 9 – lista możliwości wyboru dni, które chcemy traktować jako dni wolne od pracy (weekend).
Trzeci
argument możemy również ustalić jako ciąg cyfr 0 i 1, gdzie wartość 0 odpowiada
dniom pracującym a wartość 1 to dni wolne od pracy. Zatem zapis formuły
powinien wyglądać następująco:
=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111")
Mamy
określony trzeci argument funkcji, czyli dni jakie chcemy traktować jako
weekend. Pozostaje nam określić dni świąteczne, czyli czwarty parametr funkcji
– święta. Tak jak dla poprzedniego przykładu temu parametrowi odpowiada
zakres z tabeli święta (I2:I14), zablokowany bezwzględnie za pomocą klawisza
F4. Zapis całej funkcji powinien wyglądać następująco:
=DNI.ROBOCZE.NIESTAND(F3;F3;"0000111";$I$2:$I$14)
Wpisaną formułę kopiujemy w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C. Zaznaczamy obszar na jaki chcemy nałożyć sprawdzanie poprawności danych, następnie wybieramy ikonę polecenia Poprawność danych z karty Dane (tak jak na rys. nr 4). Otworzy nam się okno Sprawdzania poprawności danych, gdzie karcie Ustawienia, w polu Dozwolone wybieramy typ Niestandardowe. Następnie w polu Formuła wklejamy wcześniej skopiowaną formułę za pomocą skrótu klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK (rys. nr 10).
Rys. nr 10 – okno sprawdzania poprawności danych
Dzięki
sprawdzaniu poprawności danych Excel pozwoli nam wpisać datę, odpowiadającą
tylko dniom od poniedziałku do czwartku. Jeśli spróbujemy wpisać datę z
weekendu lub święto wyświetli nam się komunikat przedstawiony na rys. nr 11.
Rys. nr 11 – komunikat o błędnym wpisaniu daty
Podsumowując
nasza formuła działa prawidłowo, mianowicie sprawdza czy nasza data jest dniem
wolnym od pracy (założenie weekend i piątek)oraz czy nie wypada w święta.
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 nauczymy się pisać taką walidację danych (sprawdzanie
poprawności danych), żeby ograniczyć możliwość wpisywania daty do maksymalnie
30 dni w przyszłość. Zadanie to wykonamy na podstawie przykładowych danych z
rysunku nr 1.
Rys. nr 1 – przykładowe dane
W naszym
przykładzie wartością graniczną będzie 30 dni, ale zasada jest taka sama dla
dowolnej liczby wpisanej w konkretną komórkę. Zaczniemy od wpisania formuły dla
funkcji DZIŚ, która zwraca aktualną datę. Otrzymamy komórkę z dzisiejszą
datą przedstawioną w komórce C3 na rys. nr 2.
Rys. nr 2 – wartość zwrócona przez funkcję DZIŚ
Kolejnym
etapem jest dodanie do funkcji DZIŚ wartości 30 dni lub najlepiej odwołania do
komórki, w której mamy podaną ilość dni, u nas jest to komórka E2. Musimy
pamiętać o zablokowaniu tej komórki bezwzględnie za pomocą klawisza F4. Zapis
formuły powinien wyglądać następująco:
=DZIEŃ()+$E$2
Po
zatwierdzeniu formuły otrzymamy datę po upływie 30 dni, czy maksymalną datę
jaką użytkownik może wpisać w dane w kolumnie Data. Ponadto formułę tą
kopiujemy na wiersze poniżej dla wszystkich danych (rys. nr 3).
Rys. nr 3 – Data po upływie 30 dni od dzisiejszej daty
Nasze
zadanie polega na sprawdzeniu czy wpisana data dostawy nie przekracza progu 30
dni od dnia dzisiejszego. Ustaliliśmy datę graniczną, czyli datę jaka będzie po
upływie 30 dni. Teraz w trybie edycji komórki kopiujemy cała formułę za pomocą
skrótu klawiszowego Ctrl+C, następnie zaznaczamy wszystkie komórki, które mają
mieć sprawdzanie poprawności danych i wybieramy polecenie Sprawdzanie
poprawności danych z karty Dane (rys. nr 4).
Rys. nr 4 – polecenie Sprawdzanie poprawności danych
Otworzy nam
się okno Sprawdzania poprawności danych, gdzie w Kryteriach poprawności,
w polu Dozwolone wybieramy z listy rozwijanej typ Data. W polu Wartości
danych wybieramy z listy rozwijanej Mniejsza niż lub równa, natomiast w
polu Data końcowa wklejamy wcześniej skopiowaną formułę za pomocą skrótu
klawiszowego Ctrl+V. Tak ustawione parametry sprawdzania poprawności danych
zatwierdzamy przyciskiem OK (rys. nr 5).
Rys. nr 5 – parametry sprawdzania poprawności danych
Teraz Excel
w zaznaczonych komórkach pozwoli wpisać nam datę dzisiejszą, datę wcześniejszą
i każdą datę poniżej 30 dni w przód. Excel dzięki sprawdzaniu poprawności
danych nie pozwoli nam natomiast wpisać w komórki innych danych, np. tekstu,
czy innych dowolnych znaków, jak również daty przekraczającej datę graniczną,
czyli data dzisiejsza plus 30 dni. Po wpisaniu błędnej daty wyświetli nam się
komunikat o błędnym wpisaniu danych przedstawiony na rys. nr 6.
Rys. nr 6 – komunikat o wpisaniu danych, nie spełniających kryteriów poprawności danych
Nie możemy
wstawić daty przekraczającej 30 dni od daty dzisiejszej, ale możemy zmienić
kryterium ustawione w komórce E2. Wtedy automatycznie zmieni nam się kryterium
poprawności danych (rys. nr 7).
Rys. nr 7 – zmiana kryterium poprawności danych
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 dzisiejszym wpisie omówimy polecenie Przechodzenia do —
specjalnie. Jest to polecenie umieszczone na końcu karty Narzędzia główne (rys.
nr 1).
rys. nr 1 — Polecenie Przechodzenie do — specjalnie
Zagadnienie to omówimy sobie na podstawie przykładowych
danych z rysunku nr 2.
rys. nr 2 — Przykładowe dane
W pierwszej kolejności omówimy sobie polecenie Przejdź do.
Z karty Narzędzia główne wybieramy polecenie Znajdź i zaznacz, a następnie
Przejdź do. Otworzy nam się okno Przechodzenie do, które służy głównie do
przechodzenia do nazwanych zakresów danych. My potrzebujemy bardziej
specjalistycznego podejścia, więc wciskamy przycisk Specjalnie, pokazany na
rysunku nr 3.
rys. nr 3 — Okno Przechodzenie do
Otworzy nam się okno Przechodzenie do – specjalnie. Okno
to możemy otworzyć za pomocą skrótów klawiszowych – Ctrl+G, aby otworzyć okno
Przechodzenie do, a następnie Alt+S, aby przejść do okna Przechodzenie do –
specjalnie (rys. nr 4).
rys. nr 4 — Okno Przechodzenie do — specjalnie
W tym oknie mamy wiele możliwości. Pierwszą opcją są
Komentarze. Jeśli mamy zaznaczona jedną komórkę i skorzystamy z opcji
Komentarze, Excel zaznaczy nam wszystkie komórki w arkuszu, gdzie są
umieszczone jakieś komentarze. Kiedy zaznaczymy zakres danych (przynajmniej 2
komórki) i skorzystamy z tego samego polecenia, Excel zaznaczy nam komórki z
komentarzem tylko w zaznaczonym zakresie.
W oknie Przechodzenie do – specjalnie, możemy wybrać
wartości Stale lub Formuły w danym zakresie, do jednych i drugich obowiązują
checkboxy z rysunku nr 5.
rys. nr 5 — checkboxy dla Formuł i Stałych
W naszym przykładzie chcemy, aby Excel zaznaczył nam
wartości Stałe, będące Liczbami bądź Tekstem, czyli w checkboxie odznaczamy
Logiczne i Błędy. Otrzymujemy dane z rysunku nr 6 z zaznaczonymi tylko
komórkami z wartościami liczbowymi lub tekstem. (skreślone komórki nie
zaznaczone).
rys. nr 6 — Zaznaczone komórki z Liczbami i Tekstem
Zaznaczyliśmy Stałe wartości, więc możemy się domyślić, że
komórki niezaznaczone zawierają formuły.
Możemy wybrać opcje Puste, wtedy Excel zaznaczy nam puste
komórki na zaznaczonym zakresie. Kolejną opcją jest Bieżący obszar w stosunku
do komórki dla której wybraliśmy tą opcję. Bieżący obszar możemy również
zaznaczyć za pomocą skrótów klawiszowych Ctrl+A lub Ctrl+Shift+8.
Kolejnym polecenie jest Bieżąca tablica, w naszym
przykładzie Excel nie znajdzie takich danych, bo nie mamy w naszych danych
formuły tablicowej (rys. nr 7)
rys. nr 7 ‑Komunikat nie znaleziono wyników
Jeśli zaznaczymy jakiś zakres (fragment tablicy, ale aktywna
komórka będzie się znajdować w zakresie tej tablicy), gdzie mamy takie formuły,
to Excel zaznaczy nam całą tablicę. Natomiast jeśli zaznaczymy jakiś zakres
danych, gdzie aktywna komórka będzie znajdować się poza tablicą (mimo iż część
zakresu będzie w tej tablicy) otrzymamy komunikat, że nie znaleziono komórek.
Następną opcją są Obiekty, czyli Excel zaznaczy nam wszystkie
obiekty w arkuszu(rys. nr 8).
rys. nr 8 — Polecenie Zaznaczanie obiektów
Dla mnie lepszą opcją do zaznaczania obiektów jest polecenie
Okienko zaznaczenia (karta Narzędzia główne, polecenie Znajdź i zaznacz) – rys.
nr 9, ponieważ tu mogę jasno kontrolować elementy, które chce zaznaczyć.
rys. nr 10 — Polecenie Znajdź i zaznacz
W naszych danych mamy umieszczony ukryty wiersz. W opcjach
okna Przechodzenie do – specjalnie jest opcja Tylko widoczne komórki, która zaznaczy
nam obszar bez ukrytych komórek. W celu zaznaczenia tylko widocznych komórek
możemy również użyć skrótu klawiszowego Ctrl+;.
Kolejną opcją jest zaznaczenie Różnic w wierszach (rys. nr
10). Analogicznie zadziała polecenie Różnice w kolumnach – otrzymamy zaznaczone
komórki z innym wynikiem. Wtedy łatwo jest np. zmienić im kolor, aby móc
sprawdzić skąd wynika ta różnica.
rys. nr 10 — Polecenie Różnice w wierszach
Kolejną opcją są Poprzedniki, czyli jeśli jakieś formuły
odwołują się do komórek, to te komórki będą zaznaczone.
Następnie mamy polecenie Zależności, czyli w sytuacji jeśli
mamy formułę odwołującą się do innej komórki. W tym przypadku dla mnie
wygodniej jest skorzystać z poleceń na karcie Formuły – Śledź poprzedniki lub
zależności (rys. nr 11). To rozwiązanie jest dla mnie lepsze bo fizycznie
pokazuje te zależności między komórkami, która od której jest zależna.
rys. nr 11 — Polecenie Śledź poprzedniki
Polecenie Ostatnia komórka zaznaczy nam ostatnią używaną
komórkę. Ogólnie rzecz biorąc ta komórka może być pusta, ale jest to komórka
wyznaczona niejako przez przecięcie się ostatniego używanego wiersza z ostatnią
używaną kolumną. Ostatnią komórkę możemy również wyznaczyć za pomocą skrótu
klawiszowego Ctrl+End.
W oknie Przechodzenie do – specjalnie mamy również opcję
Sprawdzanie poprawności danych. Polecenie to wyznaczy nam komórki, których
zadaniem jest sprawdzanie poprawności danych. Zostaną zaznaczone wszystkie
takie komórki na danym zakresie lub takie same – o takim samym sprawdzaniu poprawności danych.
Opcja Formatowanie warunkowe Wszystkie sprawi, że Excel
zaznaczy obszar z formatowaniem warunkowym w całym arkuszu. Łatwiejszym
sposobem dla mnie jest użycie polecenia Zarządzaj regułami z Formatowania
warunkowego na karcie Narzędzia główne (rys. nr 12).
rys. nr 12 — Polecenie Zarządzaj regułami
Wyświetli nam się Meneger reguł formatowania warunkowego, w
którym mamy listę wszystkich komórek z takim formatowaniem w arkuszu (rys. nr 13).
rys. nr 13 — Okno Meneger reguł formatowania warunkowego
Podsumowując Przechodzenie do – specjalnie ułatwia pracę w
Excelu poprzez zaznaczanie komórek, które nas interesują, spełniających jakieś
kryterium.
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.
