Kurs Excel: Formuły wyszukiwania - ćwiczenie
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 kolejnym module, w którym pochylamy się głównie nad funkcjami
wyszukiwania, ale także poznasz funkcje. Jeżeli.
A więc takie funkcje jak X, wyszukaj, wyszukaj pionowo, wyszukaj
poziomo i funkcja.
Jeżeli to jest zakres, który pokryjemy w tym module.
Zacznijmy od funkcji dostępnej w niektórych wersjach Excela 2016.
We wszystkich 2019 i nowszych.
Funkcja x wyszukaj ma stanowić taką solidną alternatywę dla funkcji
wyszukiwania poziomego, która jest bardzo lubiana i mocno rozpowszechniona
w organizacjach. Jak korzystać z tej funkcji?
Z jakich ona elementów się tutaj składa?
Pierwszym elementem funkcji wyszukiwania jest szukana wartość.
Szukana wartość to wartość, po której będziemy szukali
interesujących nas danych.
W naszym przypadku chcemy doszukać dostawcę na podstawie konkretnego
produktu, a więc ta nazwa produktu stanowi właśnie naszą szukaną wartość.
Kolejnym krokiem jest szukana tablica Szukana tablica.
W naszym konkretnym przypadku jest to kolumna, w której chcemy znaleźć
ten nasz konkretny produkt.
No i jest tutaj faktycznie kolumna produktu.
Następnym krokiem jest wskazanie kolumny, z której chcielibyśmy uzyskać rezultat.
W naszym przypadku jest to dostawca, a więc będziemy zaznaczali naszego dostawcę.
Kolejne trzy elementy dotyczą tego, co ma funkcja zwrócić.
Jeżeli jakichś danych nie znaleziono, z jakiego trybu dopasowania ma korzystać
funkcja, czyli sytuacja, w której nie chcemy np.
Wyszukiwać precyzyjnie danych, tylko interesuje nas szukanie
danych przybliżonych.
No i ostatni element funkcji wyszukaj to tryb wyszukiwania, czyli czy chcemy szukać
od góry do dołu, czy może odwrotnie.
Ok, zacznijmy wpisywać funkcję X.
Wyszukaj.
Szukaną wartością jest nasz widelec strzałka w lewo.
Ja tutaj nie blokuję komórek, po prostu zaznaczam sobie komórkę A12.
Strzałką wciskam średnik. Szukana tablica.
I teraz nie ma znaczenia, czy zaznaczymy tę naszą szukaną tablicę z nagłówkiem, czy
też bez, bo i tak będziemy operowali na produktach, więc może zaznaczmy z
nagłówkiem, aby było to spójne z grafiką, którą przygotowałem
tutaj dla Ciebie powyżej.
Te kolumny sobie zablokujemy klawiszem F4, dlatego, że gdy będziemy przesuwali naszą
formułę w dół, zależy nam na tym, aby zawsze odwoływać się właśnie
do kolumny z produktami.
Wciskamy średnik i zwracana tablica.
No i w naszym przypadku tą zwracaną tablicą jest kolumna Dostawcy
zablokujemy sobie klawiszem F4.
I teraz istotną sprawą jest to, że jeżeli zdecydowaliśmy się zaznaczyć nasze
produkty z nagłówkami to trzymamy jedną wysokość, jeżeli chodzi o
zaznaczenie też kolumny rezultatu. Pamiętajmy o tym.
To jest dokładnie taka sama zasada jak przy funkcjach warunkowego zliczania.
No i teraz tak moglibyśmy zamknąć tę funkcję już na tej wysokości,
bo pozwala nam na to Excel.
Zwróćmy uwagę wszystkie pozostałe elementy są elementami opcjonalnymi, czyli
nie ma tutaj potrzeby ich wpisywać. Tak też zróbmy.
Zamknijmy nawias, zatwierdzimy enter.
Przeciągnijmy sobie w dół.
No i zobaczmy, czy nasza funkcja działa w sposób prawidłowy.
Dostawcą noża jest knajpę bis.
Zobaczmy nóż knajpę bis.
Widelec to Vitpol.
Widelec to Vitpol.
Wygląda na to, że funkcja działa w sposób prawidłowy.
Gdyby na przykład w naszej funkcji pojawiły się jakieś braki danych, czyli
załóżmy, że pojawiłby się jakiś tutaj jeszcze kolejny sztuciec,
na przykład widelczyk.
No i tutaj pojawia się brak danych.
No właśnie, co w tej sytuacji?
Do tego świetnie sprawdza się funkcja wyszukaj, a w zasadzie jej czwarty
element, czyli wciskam sobie tutaj średnik.
Jeżeli nie znaleziono, mogę sobie tutaj wpisać na przykład w cudzysłowie brak.
Pamiętajmy brak jest tekstem, a więc abym umieścił go w mojej funkcji, muszę zrobić
to właśnie w cudzysłowie przeciągamy sobie w dół.
No i w ten oto sposób, jeżeli gdzieś pojawiłby się błąd braku danych,
pojawi się po prostu informacja Brak.
Więc to jest bardzo fajna przewaga funkcji.
Tryb dopasowania to coś, co również występuje w funkcjach wyszukaj pionowo czy
wyszukaj poziomo, a mianowicie czy chcemy dokładnego dopasowania, czy
chcemy dopasowania przybliżonego.
Mamy tutaj też warianty tego dopasowania Pasowanie przybliżonego.
Dopasowanie przybliżone to temat bardziej. Średniozaawansowany.
Zaawansowane.
Przydaje się w momencie, gdy przy pomocy funkcji wyszukiwania chcemy doszukać
przedział do naszych liczb, więc ten temat sobie pominiemy.
Wciśniemy średnik po raz kolejny.
I tu sytuacja, w której chcielibyśmy np.
Szukać od dołu do góry, a nie tak jak jest domyślnie, czyli od góry do dołu, to
możemy ustawić sobie w parametrze 6 funkcji wyszukiwania.
Również to pomijamy.
A więc usuńmy sobie nasze 2 średniki, zatwierdzimy enter i
funkcja x wyszukaj za nami.
Rozwińmy sobie kolejną funkcję, na której przyjdzie nam pracować.
Jest to funkcja wyszukaj pionowo.
Ja gorąco zachęcam Cię do tego, aby jednak korzystać z X.
Wyszukaj.
Natomiast muszę opowiedzieć Ci o funkcji Wyszukaj pionowo ze
względu na jej popularność.
Mimo, że ta funkcja już powoli odchodzi do lamusa, to nadal jest dominująca.
Jeszcze wiele wody w Wiśle upłynie zanim wszyscy przestawimy się na X.
Wyszukaj.
Tym bardziej, że jeżeli ktoś ma starszego Excela, czyli na przykład 2007, 2010, 2013
albo jakieś przyzwyczajenia ze starszych Exceli.
No to korzysta z WYSZUKAJ. PIONOWO nawet w tych nowych excelach.
Zobaczmy jak ta funkcja jest skonstruowana i jaka jest różnica pomiędzy
Wyszukaj pionowo a x. Wyszukaj.
Funkcja ta składa się z czterech elementów.
Pierwszym elementem jest szukana wartość, a więc dokładnie tak, jak miało
to miejsce w funkcji wyszukaj.
W naszym przypadku będziemy szukali konkretnego produktu.
Jest to widelec.
W drugim elemencie funkcji wcześniej zaznaczyliśmy tylko kolumnę produkt, a
tutaj trzeba zaznaczyć całą tabelę tablicę.
Czym jest tabela Tablica.
W naszym przypadku jest to obszar, na podstawie którego będziemy szukali.
Będziemy zaznaczali go w ten oto sposób.
I teraz co dalej?
Numer indeksu kolumny.
Czym jest numer indeksu kolumny?
Numer indeksu kolumny to nic innego jak informacja o tym, z której kolumny
chcemy uzyskać dane jako rezultat. Wyszukaj pionowo.
Zawsze szuka po pierwszej kolumnie. Co to znaczy?
To znaczy, że zawsze szuka naszej szukanej wartości w pierwszej zaznaczonej kolumnie.
Za pierwszą zaznaczoną kolumnę uznaliśmy produkt, a więc będzie przyrównywała
wartość widelca właśnie z kolumną Produkt.
Gdyby tutaj wcześniej była jakaś kolumna, to również musielibyśmy zaznaczyć dane w
ten sposób, aby właśnie produkt był porównywany do produktu.
A element trzeci funkcji pozwala nam wskazać, z której kolumny chcemy
uzyskać rezultat, licząc od zaznaczenia.
Czyli mamy pierwszą kolumnę, produkt, drugą kolumnę.
Cena i trzecia kolumna to dostawca, a więc tutaj będzie pojawiała nam się trójka.
Na samym końcu mamy pytanie o przeszukiwany zakres,
czyli ten typ przeliczenia.
Domyślnie funkcja traktuje przeliczenie jako sposób przybliżony, czyli ten, o
którym wspominałem Ci w przypadku X wyszukaj jako ten bardziej zaawansowany.
My będziemy natomiast musieli pamiętać o tym, aby wpisywać sobie na końcu 0 i
korzystać z dokładnego dopasowania.
Ten parametr niestety nie jest parametrem domyślnym, co powoduje, że
zawsze musimy o tym pamiętać.
W przypadku funkcji WYSZUKAJ.
PIONowo teorie już mamy.
Zabierzmy się więc za praktykę.
Zacznijmy wpisywać naszą funkcję równa się strzałeczka w dół.
Wyszukaj pionowo.
Szukaną wartością jest to, co się znajduje w komórce A 39.
Więc wciskam strzałkę w lewo, żeby tę komórkę zaznaczyć.
Średnik. Tabela.
Tablica to ten obszar.
Możemy zaznaczyć go myszą albo skrótami klawiszowymi, czyli Shift i strzałka w
prawo trzy razy, a potem Ctrl+F, strzałka w dół albo po prostu Ctrl+F
blokujemy klawiszem F4.
Upewniamy się, że tutaj ta funkcja została zablokowana z obu stron.
Wygląda na to, że tak właśnie się stało. Wciskamy średnik.
No i teraz numer indeksu kolumny. Tutaj pięknie.
Funkcja prowadzi nas przez kolejne swoje elementy.
My ustaliliśmy, że to jest trzecia kolumna, bo w trzeciej kolumnie
zaznaczonego obszaru znajdują się nasi dostawcy.
No i na samym końcu przeszukiwany zakres przybliżone dopasowanie
lub dokładne dokładne.
Czyli jeżeli dokładnie widelec zostanie znaleziony, to dla widelca chcemy
uzyskać informację o dostawcy.
Domyślnie, gdybyśmy zamknęli nawias, funkcja uznałaby, że wpisaliśmy
prawdę albo uznaliśmy prawdę.
My chcemy, żeby tutaj był fałsz, ale żeby nie pisać fałszu, bo to długo trwa.
Możemy wpisać 0 dla Excela w funkcjach 0 reprezentuje fałsz, a jedynka reprezentuje
prawdę, czyli zamykamy nawias, zatwierdzamy enterem.
No i gotowe.
Jeżeli przeciągniemy sobie funkcję do dołu, możemy sprawdzić widelec
Wid-Pol widelec Wid-Pol nóż Knife bis.
Nóż Knife bis.
Wygląda na to, że nam tutaj to fajnie działa.
Taką siostrą bliźniaczką, ale dwu jajową.
Funkcji wyszukaj pionowo jest funkcja wyszukaj poziomo.
No i teraz ta funkcja wyszukaj poziomo.
Działa bardzo podobnie jak funkcja wyszukaj pionowo.
Tyle że no.
Różni się nam tutaj tabela, w której wyszukujemy.
Czyli to jest taka tabela, można powiedzieć przed transpozycją.
W jednej z poprzednich lekcji rozmawialiśmy o wklejeniu z transpozycją.
No i tak wyglądała właśnie nasza tabela startowa.
Zobaczmy jak zbudować funkcję.
Wyszukaj poziomo w funkcji Wyszukaj poziomo.
Elementy nazywają się trochę inaczej, ale w zasadzie jest to to
samo odniesienie dla nas.
To jest nic innego jak to, czego szukamy, czyli w naszym przypadku komórka 54
konkretny produkt, tablica po raz kolejny obszar, w którym szukamy.
Znów zaznaczamy w ten sposób o ile wcześniej funkcja wyszukaj pionowo szukała
zawsze w pierwszej kolumnie, o tyle funkcja wyszukaj poziomo szuka.
W pierwszym wierszu więc zaznaczyliśmy od wiersza z produktem.
Blokujemy oczywiście F4 ten nasz konkretny obszar, bo zawsze odwołujemy się do
tej tabeli, jakbyśmy przeciągali w dół.
Wciskamy średnik, numer wiersza.
No i wcześniej numerowaliśmy kolumny.
Teraz numerujemy wiersze od zaznaczenia.
Czyli mamy pierwszy zaznaczony wiersz, drugi zaznaczony wiersz i
trzeci zaznaczony wiersz.
Trzeci to jest ten, z którego chcemy uzyskać rezultat, więc wpisujemy trójkę i
analogicznie jak w przypadku funkcji wyszukaj pionowo 0 na końcu,
bo chcemy dokładne dopasowanie.
Zamykamy nawias, przeciągamy w dół i również z takich tabeli, które są można
powiedzieć tak kolokwialnie kopnięte jesteśmy w stanie wyciągnąć rezultaty.
One działają nóż, knajpę, bis, nóż, knajpę bis.
Świetnie!
Rozwińmy jeszcze takie, można by było powiedzieć podręczne porównanie
pomiędzy tymi dwoma funkcjami.
Wyszukaj pionowo i poziomo.
Jeżeli chodzi o elementy, zwróćmy uwagę, jak one się nazywały.
Szukana wartość. Tu jest odniesienie.
To jest w zasadzie to samo. Tabela.
Tablica w funkcji wyszukaj poziomo to nazywa się tablica.
I jedyną różnicą jest to, że mamy numer indeksu kolumny.
Tutaj mamy numer wiersza.
No tutaj faktycznie ze względu na to, że wyszukaj.
PIONowo szuka po kolumnach podajemy numer kolumny.
Z racji tego, że WYSZUKAJ poziomo szuka po wierszach podajemy numer wiersza.
No i przeszukiwany zakres.
Tutaj też zero na końcu zazwyczaj korzystamy z dokładnego dopasowania.
Co ciekawe funkcja wyszukaj ma tą przewagę nad funkcją WYSZUKAJ.
PIONOWO.
Kolejną zresztą, że możemy w niej również szukać w poziomie.
Czyli jeżeli wybiorę X wyszukaj i będę sobie szukał tej mojej szukanej wartości
wcześniej wskazanej i szukana tablica wybiorę wiersz, zablokuje go klawiszem F4,
wcisnę sobie średnik i w zwracanej tablicy wybiorę wiersz, z którego
chciałbym mieć rezultat.
A pamiętam, że w tym wierszu właśnie ten rezultat się znajduje i zamknę
sobie nawias i przeciągnę do dołu.
To zwróć uwagę, że funkcja x wyszukaj również potrafi szukać w poziomie.
Czyli można to kojarzyć z tym X na początku, że szuka nie tylko w poziomie,
ale szuka także w pionie wedle naszych preferencji, tak jak X to robi w
praktycznie każdym możliwym kierunku.
To tyle w tej lekcji.
W kolejnej poćwiczymy trochę pracy z funkcjami wyszukiwania.
Do zobaczenia!