Kurs Excel: Formuły wyszukiwania
od Podstaw
6 godz. 10 min · Excel · Biznes i Automatyzacje
Michał KowalczykTrener i założyciel w Excellent Work - Skuteczna Nauka ExcelaCzego się nauczysz
- Zyska lepsze zrozumienie działania programu Excel.
- Zyska umiejętność pracy na dużych zestawach danych (filtrowanie, przekształcanie, raportowanie).
- Dowie się jak doszukiwać i łączyć potrzebne dane z innych plików i arkuszy.
- Pozna sposoby wyciągania z komórki istotnych fragmentów danych.
- Pozna podstawy analizy dużych zestawów danych z użyciem Tabel Przestawnych.
- Będzie umiał podsumować dane według kryteriów (np. sprzedaż w rozbiciu na kategorie).
- Będzie umiał zliczyć wystąpienia według kryteriów (np. liczba transakcji nieopłaconych).
- Nauczy się tworzyć proste algorytmy przypisujące wartości na podstawie spełnionych kryteriów.
Formatowanie i zarządzanie danymi
Efektywna praca w Excelu zaczyna się od płynnego poruszania się w arkuszach i tabelach. W tej części kursu skupimy się na wprowadzaniu danych, ich edycji i tworzeniu układów, które znacznie poprawią czytelność raportów. Nauczysz się też, jak sprawnie sortować i filtrować duże zestawy informacji, by w kilka chwil wyciągać z nich najważniejsze wnioski. Pokażę Ci również sztuczki przydatne na co dzień, dzięki którym oszczędzisz mnóstwo czasu.
Formuły i kalkulacje
Excel oferuje imponującą liczbę funkcji, które pozwalają na głęboką analizę dowolnych danych – od formuł tekstowych, przez przetwarzanie dat, po budowanie złożonych wyrażeń logicznych. W tej części kursu zobaczysz, jak łączyć różne funkcje w jedno, aby poszerzyć ich możliwości. Dowiesz się, jak tworzyć warunkowe formuły, które będą automatycznie reagować na zmiany w arkuszu. Dzięki temu aktualizacje danych w tabeli wyliczą się same, bez dodatkowej pracy z Twojej strony.
Praca z tabelami przestawnymi i wykresami
Nawet najlepsze dane nie mają większej wartości, jeśli nie umiesz ich właściwie zaprezentować. Dlatego w kolejnej sekcji nauczysz się tworzyć klarowne tabele przestawne i atrakcyjne wizualizacje. Wykorzystasz listy wyboru, by ograniczyć liczbę błędów przy wprowadzaniu danych, a następnie za pomocą wykresów pokażesz wyniki w jasny i przystępny sposób. Zyskasz praktyczne umiejętności tworzenia zestawień w oparciu o Tabele Przestawne.
Podsumowanie i praktyczne wskazówki
Na koniec kursu zebrałem najczęstsze błędy, na jakie trafiają nawet zaawansowani użytkownicy, i pokazałem, jak skutecznie ich unikać. Dostaniesz pakiet najlepszych praktyk, przydatnych skrótów klawiszowych oraz zestaw praktycznych rad, które pomogą Ci rozwiązać typowe problemy w Excelu. Do zobaczenia w kursie!
Dla kogo jest ten kurs?
Ten kurs powstał z myślą o osobach, które pracują już z Excelem oraz tych, które dopiero rozpoczynają przygodę z tym programem. Z kursu najwięcej wyniosą osoby, które:
Witam Cię w lekcji poświęconej ćwiczeniom związanym z funkcją wyszukiwania.
Przed nami zadanie szukania województw i liczby pracowników do
konkretnych wydziałów.
Będziemy to robili na podstawie danych, które znajdują się w innym
arkuszu naszego pliku w arkuszu. Informacje.
No i tutaj znajduje się siedziba, kolejno wydział, kolejno województwo,
a następnie liczba pracowników.
Gdy korzystamy z funkcji wyszukiwania, należy pamiętać o tym, że kolumna, na
podstawie której będziemy wyszukiwali powinna zawierać dane unikatowe, czyli w
naszym przypadku są to numery ID wydziałów.
No i oczywiście możemy zawsze łatwo zweryfikować to na przykład korzystając
z sprawdzenia duplikujących się wartości.
Zakładając tutaj formatowanie warunkowe nie ma w tym przypadku duplikatów.
Właśnie na tym nam zależy.
Do tej tabeli będziemy doszukiwali informacje, a więc możemy
zabrać się już do pracy.
I teraz jeżeli masz do dyspozycji funkcję wyszukaj to Zachęcam Cię do tego,
aby wybrać właśnie tę opcję.
Jeżeli nie masz funkcji wyszukaj na spokojnie.
Zrobimy też ten przykład przy użyciu funkcji Wyszukaj pionowo.
Zacznijmy od funkcji x.
wyszukaj, co jest u nas szukaną wartością.
Ustaliliśmy, że wydział będzie świetnie się do tego sprawdzał.
Z racji tego, że będziemy sobie formułę przeciągali do prawej, zależy nam na tym,
żeby dane były zawsze pobierane właśnie z kolumny, a więc zablokujemy sobie komórkę,
ale tylko jeżeli chodzi o kolumny.
Czyli dolar pojawia się przed a użyłem klawisza F4 trzykrotnie.
Możesz też alternatywnie użyć funkcji F4, jeżeli tego wymaga Twoja klawiatura.
Co dalej? Szukana tablica.
Wciskam średnik.
Przechodzę do szukanej tablicy, gdzie chciałbym, aby ten
wydział został znaleziony.
Przechodzę na kartę Informacje i zwróć uwagę co teraz się stało.
W adresie formuły.
Została tutaj zanotowana informacja o tym, że zmieniłem arkusz.
Mamy informacja wykrzyknik.
To jest notatka dla Excela, żeby wiedział, że pobieramy dane z innego
arkusza informacje, wykrzyknik.
No i zaznaczam sobie obszar chcę szukać w kolumnie wydziału, blokuję klawiszem F4.
Muszę to zrobić, bo będę przeciągał formułę w dół, a także w prawo.
No i już tutaj widzę, że informacja jest prawidłowa.
I teraz pewne niebezpieczeństwo.
Jeżeli zablokowałem formułę i przełączę się teraz z powrotem
na arkusz, a nie wcisnę średnika.
To zwróć uwagę, że tutaj to odwołanie niestety nie działa w sposób prawidłowy.
A więc pamiętajmy o tym, aby.
Będąc już tutaj, blokując sobie zakres klawiszem F4 dopiero wcisnąć
średnik, aby ta nasza.
Tutaj informacja już się nie zmieniła.
Gdy przełączymy się na arkusz.
Jestem już na arkuszu ćwiczenia.
Excel mnie tutaj automatycznie zabrał.
Gdy sobie zablokowałem zakres jestem już w kolejnym elemencie.
Zwracana tablica. Znowu przechodzę sobie do informacji.
No i w tym konkretnym przypadku chciałbym zwrócić sobie województwo i również muszę
zablokować ten mój zakres w odpowiedni sposób.
Mamy województwo i liczbę pracowników, a więc jeżeli zablokuje raz, no to będę
odwoływał się bezpośrednio do tej kolumny województwo, ale ja będę przeciągał
formułę w prawo, więc chciałbym, żeby najpierw było województwo, potem liczba
pracowników, więc odblokuję sobie zakres, jeżeli chodzi o kolumny, zablokuję tylko
wiersze, dlatego że chcę, aby Excel działał w tych wierszach, ale
chcę, żeby kolumny się zmieniały.
Gdy będę przeciągał formułę, zamykam nawias, zatwierdzamy Enterem,
przeciągam sobie w dół. Zobaczmy, czy to działa.
Jeżeli chodzi o moją formułę, mamy wyd.
021, województwo małopolskie, wyd.
021, województwo małopolskie. Ok.
Przeciągnijmy formułę do prawej.
Zrobiliśmy ją w taki sprytny sposób i zobaczmy, czy to działa
tak, jakbyśmy chcieli. Przeciągamy w dół.
Zobaczmy. Dobrze.
Wyd. 01, Kraków.
132 Informacje. Wyd.
01, Kraków 132. Super.
A gdybyśmy sprawdzili sobie jeszcze. Wyd.
021 170, wyd.
021 170.
Świetnie ta funkcja działa właściwie.
Gdybyśmy spojrzeli, co teraz się wydarzyło.
No to stało się dokładnie to, co powiedziałem.
Czyli przeciągając formułę w prawo, blokując sobie kolumny, nadal
odwołujemy się do właściwego miejsca.
Pozycja kolumny z wydziałem, która się znajduje w karcie Informacja
nie ulega zmianie.
Na tym nam właśnie zależało, ale zmianie oczywiście uległa kolumna z informacjami,
dlatego, że tam były te kolumny w takiej samej kolejności.
To była kolumna C, to była kolumna D.
Mogliśmy skorzystać z zablokowania tylko wierszy, tak aby kolumna się zmieniła.
No i faktycznie w tej komórce mamy kolumnę D, a w tej komórce mamy kolumnę C.
Dzięki temu możemy sobie budować funkcje elastyczniej.
Wspomniałem o tym, że skorzystamy z funkcji wyszukaj pionowo do
rozwiązania tego zadania. Tak też zróbmy.
Przesuńmy sobie tutaj na bok nieco pole tekstowe i użyjmy
analogicznego rozwiązania.
Wpiszmy sobie tutaj.
Wyszukaj pionowo.
Szukaną wartością u nas jest wydział.
Również zablokujemy sobie jednostronnie, dlatego, że będziemy formułę
przeciągali do prawej.
TabelaTablica.
Przejdźmy sobie na kartę Informacje i w przypadku tabeli tablicy szukamy po
pierwszej kolumnie, czyli nie po siedzibie, a po wydziale.
Zaznaczamy.
W ten sposób zaznaczamy również z liczbą pracowników, bo będziemy
chcieli ją wykorzystać.
Blokujemy klawiszem F4, wciskamy średnik.
No i numer indeksu kolumny dla województwa to kolumna numer 2.
Dlaczego?
No dlatego, że zaznaczyliśmy sobie obszar w ten sposób.
A więc to jest nasza kolumna pierwsza.
W drugiej kolumnie są województwa, a w trzeciej są pracownicy, A więc zaznaczamy
względem tego wcześniejszego zaznaczenia.
Świetnie, A więc nas interesują dane z dwójki.
No i średnik 0.
Pamiętajmy o tym zerze na końcu. Dlaczego?
Dlatego, że interesuje nas dokładne dopasowanie.
Jeżeli przeciągniemy sobie teraz formułę do dołu, no to wyniki są identyczne.
A więc to się zgadza.
Przeciągnijmy teraz formułę do prawej i przeciągnijmy ją sobie też od razu w dół.
Okazuje się, że tutaj nic się nie zmieniło.
Dlaczego się tu nic nie zmieniło?
Nie zmieniło się tutaj nic ze względu na tą dwójkę.
Ta dwójka jest wpisana przez nas na stałe.
To znaczy, że formuła zwraca nam dane z drugiej kolumny zaznaczonego zakresu,
a tam przecież są województwa.
Przydałoby się zmienić tę dwójkę na trójkę.
No i teraz pytanie czy za każdym razem musimy to robić ręcznie?
Szczególnie jeżeli mamy dużo takich wartości do wymiany?
Jest kilka formuł, które sobie z tym radzą.
Natomiast zostawmy to z racji tego, że jest to trochę wyższy poziom niż
ten, który jest realizowany w kursie.
Zrobimy taki trick, że tu sobie wpiszemy dwójkę i trójkę i odwołamy się po prostu
do komórki powyżej, czyli usunę sobie numer indeksu kolumny.
Klikam sobie tutaj na numer indeksu kolumny i backspace.
Aby go usunąć odwołam się do komórki powyżej i muszę pamiętać oczywiście
o tym, żeby ją właściwie zablokować.
Tutaj blokujemy wiersz, bo przesuwamy się w prawo.
Chcemy, aby dane były pobierane z tego wiersza, ale żeby kolumny się zmieniały,
gdy będziemy przesuwali komórkę.
Zatwierdzam sobie enterem, przeciągam do prawej, przeciągam w dół.
No i voila!
Teraz widzę, że te wyniki są tożsame, a więc jak najbardziej.
Moja funkcja wyszukiwania działa w sposób prawidłowy.
Bardzo istotna sprawa przy funkcji wyszukaj pionowo.
Pewien minus względem funkcji wyszukaj No to to, że musimy pamiętać o tym, że przy
funkcji wyszukiwania poziomego ta funkcja szuka zawsze w prawym kierunku.
Co to znaczy?
To znaczy, że dane, które chcemy doszukać muszą być na prawo od kolumny, która jest
dla nas kolumną, po której wyszukujemy.
Czyli jeżeli wyszukujemy po działach, no to nie ma teraz możliwości skonstruowania
funkcji wyszukaj pionowo bez umieszczania w tej funkcji jakichś innych funkcji, tak
aby te dane przed tą kolumną zostały znalezione.
No niestety nie jest to do zrobienia.
Trzeba w takim przypadku posłużyć się albo dodatkowymi funkcjami bardziej
zaawansowanymi, albo złapać sobie ten wydział, najechać myszą na lewą krawędź
i przeciągnąć go sobie tutaj do lewej.
No i to powoduje, że możemy sobie już na spokojnie wyszukiwać tak jak robiliśmy
to poprzednio w ćwiczeniu powyżej.
To tyle w tej lekcji.
Zapraszam Cię do kolejnej, w której zajmiemy się funkcją warunkową.
Jeżeli. Do zobaczenia.