Kurs Excel: Złożone formuły warunkowe
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 kolejnej lekcji poświęconej funkcjom warunkowego zliczania i w tym
przypadku bierzemy pod uwagę funkcję suma warunków z angielskiego.
Sam if z angielskiego nazwa różni się tylko literą S.
Suma Jeżeli to sam if suma warunków to sumy i dlaczego różni
się to tylko literą S?
Bo wynika to ze sposobu działania funkcji suma.
Jeżeli daje nam możliwość sumowania na podstawie tylko jednego kryterium.
Suma warunków pozwala sumować na podstawie dużo większej liczby
kryteriów nawet ponad 100.
A więc bierzmy się do pracy i zobaczmy, jak skonstruowana jest ta funkcja.
Pewnie pamiętasz z poprzedniej lekcji, że zaczynaliśmy od kryterium zakres i potem
wybieraliśmy kryterium, a dopiero na samym końcu zaznaczaliśmy suma zakres.
Tak działała funkcja suma. Jeżeli.
Tu wygląda to trochę inaczej.
Dlaczego to wygląda trochę inaczej?
A no dlatego, że tych warunków tutaj, tych par kryteriów mamy znacznie więcej, a więc
suma, zakres, czyli ta kolumna, z której chcemy mieć wyliczoną sumę,
będzie kolumną pierwszą.
A potem pojawiają się pary, drugi i trzeci element naszej formuły.
I tych par tutaj oczywiście może być więcej.
Ja w konstrukcji funkcji podałem tylko dwie takie pary.
Zabierzmy się zatem do pracy.
Jak wygląda budowa funkcji suma?
Warunków zatwierdzam sobie tabulatorem i zaczynamy faktycznie
od tego, co nam tutaj. Funkcja na dole podpowiada.
Suma. Podłoga.
Zakres.
Idę sobie do kolumny, którą chciałbym zsumować.
Ctrl Shift strzałka w dół.
Blokuję klawiszem F4 i teraz to już jest pierwszy krok.
Będę miał sumy na podstawie tejże zaznaczonej kolumny.
Wciskam średnik i czytam dalej, gdzie prowadzi mnie funkcja Kryteria Zakres 1
czyli teraz kryteria Zakres.
To znaczy, że obszar, w którym znajdują się moje kryteria przeciągam sobie
strzałką w lewo i wciskam Ctrl Shift strzałka w dół, zaznaczając
kolumnę, w której mam rodzaj mebla.
Blokuję sobie klawiszem F4, wciskam średnik.
No i teraz muszę podać kryterium dla tej kolumny, którą przed momentem zaznaczyłem.
Moim kryterium w tym przypadku jest rodzaj mebla.
Ja tutaj strzałką w lewo sobie pojechałem do szafki.
Oczywiście można byłoby to też zrobić myszą, bo akurat ta komórka jest widoczna,
więc jak najbardziej i tutaj nie muszę blokować, dlatego, że będę
sobie przeciągał formułę w dół.
Mogę zamknąć nawias tak na moment, zatwierdzić sobie enter
i przeciągnąć w dół.
I do tego momentu funkcja suma warunków działa jak funkcja suma.
Jeżeli różnicą w naszym przypadku będzie to, że po prostu wyniki dla szafek są
tutaj zdublowane, dla łóżek również, więc nie moglibyśmy skorzystać z metody
sprawdzenia zaznaczenia tego i zaznaczenia tego, no bo po prostu
wynik jest zdublowany.
Tutaj mamy 163 tysiące, a tutaj mamy 327000, a więc faktycznie wynik razy dwa,
dlatego, że dwa razy występuje nam tutaj szafka.
Wróćmy do naszej formuły.
No i zobaczmy pierwsze kryterium załatwione.
Jeżeli wrócimy sobie na koniec formuły i wciśniemy średnik, to zwróć uwagę,
co tutaj dzieje się na dole.
Otwiera nam się możliwość utworzenia drugiej pary kryteriów.
Co więcej, gdy utworzymy sobie tą drugą parę, możemy oczywiście tych kryteriów
tutaj dodawać znacznie więcej, o czym informuje nas symbol
tych trzech kropeczek.
Zwróć też uwagę, że te kryteria są w nawiasie kwadratowym, a więc to znaczy,
że możemy je podawać, ale nie musimy.
Czyli faktycznie możemy skończyć na pierwszej parze.
To jest takie absolutne minimum, bo zwróć uwagę przy pierwszej parze tych nawiasów
kwadratowych nie ma, czyli minimum jedna para.
Zakres kryterium. Przejdźmy do naszej drugiej pary.
Wciskam sobie teraz strzałkę w lewo, ale z racji tego, że klikałem trochę
po formule nie mogę tego zrobić.
Muszę poprosić wymusić na Excelu to, żebym mógł używać strzałek do
poruszania się po zakresie. Jak mogę to zrobić?
Klikam sobie w dowolną komórkę i teraz już mogę strzałkami podróżować
po moim obszarze.
Jestem w komórce B11, wciskam Ctrl+Shift strzałka w dół i blokuję klawiszem F4.
Świetnie zablokowane.
Teraz potrzebuję dostać się do kryterium koloru.
No i to kryterium jest tutaj ukryte, więc wciskam sobie średnik.
No i strzałka w lewo.
Widzę komórkę F11.
Ona się tutaj znajduje, jest przykryta formułą.
Mógłbym również skorzystać z klawiatury i po prostu wpisać F11.
Jestem w stanie to zrobić. Dlaczego?
No dlatego, że widzę gdzie znajduje się moje kryterium.
Ono się znajduje na przecięciu właśnie kolumny F i rzędu 11.
Świetnie, Mamy drugą parę, zamykamy nawias, przeciągamy sobie w dół.
No i możemy po raz kolejny wykonać sprawdzenie.
Pierwsze sprawdzenie w zasadzie już zrobiliśmy.
Mamy faktycznie dobry wynik 163 1800.
Pamiętamy ten wynik, bo zsumowaliśmy sobie go w poprzedniej lekcji.
No ale możemy zobaczyć np. Łóżko.
Dąb. Szukamy łóżek w kolorze dębu.
Mamy tutaj pierwsze takie łóżko. 15.
200. Trzymam sobie lewy Ctrl.
Łóżko. Dąb.
Tutaj mamy drugie.
No i mamy tutaj łóżko. Dąb.
Trzecie.
Zobaczmy, czy jeszcze gdzieś występuje łóżko w kolorze dębu.
Jest tutaj jeszcze łóżko w kolorze dębu.
No i wychodzi na to, że tutaj na dole mamy sumę 45200,
co jest tożsame z sumą 45200, którą zwróciła nam formuła, więc
jak najbardziej nam to działa.
Na koniec taki fajny trik związany z szybkością działania formuły, które akurat
w tym naszym konkretnym przypadku nie miałby zastosowania.
Z racji tego, że mamy mały zestaw danych, pamiętajmy o tym, aby zaznaczać w
pierwszej kolejności kryteria, które wyrzucają jak najwięcej wyników.
Czyli gdybyśmy na przykład mieli tutaj szafki i łóżka, i tych szafek, i łóżek
mieli załóżmy z 10 wierszy, a kolorów z 5 różnych kolorów, to zacznijmy jako
pierwsze kryterium wybierać kolory dlatego, że kolory odrzucą nam więcej
wierszy, czyli formuła będzie działała szybciej.
Ta zasada dotyczy oczywiście dużo większych zestawów niż
ten, który tutaj mamy.
No ale uznałem, że warto, aby o tym wiedzieć.
Zaznaczamy najpierw jeszcze raz to powiem o kryterium, które wyrzuca
jak najwięcej wyników.
Chodzi po prostu o optymalizację działania formuły.
Przejdźmy do kolejnej formuły.
Formuła średnia warunków Average IFS różniąca się od funkcji średnia Jeżeli
tym, że mamy average if w języku angielskim.
No i podobna zasada działania.
Możemy zbudować sobie tę funkcję. Wpisujemy sobie.
Średnia. Przeciągamy w dół.
Strzałkami w dół. Dojeżdżamy do warunków.
Zatwierdzamy tabulatorem.
Średnia zakres.
Czyli tak jak wcześniej w funkcji.
Średnia warunków mieliśmy. Suma.
Zakres. Tutaj mamy.
Średnia zakres Ctrl+Shift strzałka w dół mamy zaznaczony obszar.
Blokujemy klawiszem F4, wciskamy średnik i zaczynamy budować pary.
Pierwsza para to od komórki A26 Control Shift strzałka w dół do dołu.
Mamy F4.
Blokujemy parę związaną z właśnie rodzajem mebla.
Wciskamy sobie średnik.
Teraz potrzebujemy kryterium do tego rodzaju mebla.
Świetnie. Idziemy dalej.
Średnik. Kryteria.
Zakres 2.
Czyli w naszym przypadku jest to kolor Ctrl Shift strzałka w dół.
Blokujemy klawiszem F4, wciskamy średnik i zaznaczamy sobie kolor.
Strzałka w lewo. Pojawiła się komórka F26.
Jeżeli przyglądniesz się na video zobaczysz, że tam faktycznie
ta komórka jest zaznaczona.
Zamykamy nawias, zatwierdzamy enterem.
Przeciągamy w dół.
No i możemy po raz kolejny dokonać sprawdzenia.
Tym razem może na łóżku w kolorze heban zaznaczam sobie łóżko w kolorze
heban i trzymam lewy ctrl.
Szukam jeszcze łóżek w kolorze heban.
Tutaj mamy takie łóżko.
I to w zasadzie są chyba jedyne dwa łóżka.
No i faktycznie średnia tutaj wynosi 1200, tutaj też 1200, więc
funkcja działa prawidłowo.
W jaki sposób działa funkcja suma warunków?
Kolokwialnie mówiąc pod maską?
Pozwól, że Ci to rozrysuję.
Czyli najpierw dokonaliśmy zaznaczenia kolumny, z której wykonywana
jest nasza średnia. To była ta kolumna.
Potem zaczęliśmy sprawdzać poszczególne warunki.
Pierwszą parą była szafka, czyli co teraz robił Excel?
Excel sobie sprawdzał, czy mamy tutaj w tej kolumnie szafkę.
Zaznaczył sobie wszystkie szafki.
Pozwól, że ja sobie zrobię tutaj przy wszystkich szafkach.
Akurat wybraliśmy tak, że jest tych szafek całkiem sporo, a pozostałe wyniki uznał za
nieistotne, czyli już w tym momencie nie bierze ich pod uwagę.
One są traktowane dla formuły, dla Excela jako zera, czyli nie będą brane pod
uwagę w wyliczaniu naszej średniej.
Jaki jest kolejny krok?
W kolejnym kroku Excel sprawdził sobie drugi warunek, czyli teraz patrzy, czy
gdzieś w komórce z kolorem znajduje się dąb.
Jeżeli znajduje się dąb, to bierze pod uwagę tę komórkę, czyli bierze pod uwagę
wszystkie komórki, które mają w sobie wartość dębu, a pozostałe już wyrzuca.
Czyli pomimo tego, że pierwszy warunek został spełniony, to dla Excela nie ma
znaczenia, dlatego że drugi nie został spełniony, a to powoduje, że
kolumna nie jest brana pod uwagę.
I z tych pozostałych kolumn Excel dokonał sobie wyliczenia średniej.
Te były brane pod uwagę przy liczeniu średniej i stąd wzięła się ta wartość.
Dokładnie w taki sam sposób działa funkcja suma warunków, z tym, że nie jest
wyciągana na końcu średnia, tylko jest wykonywane sumowanie.
To tyle w tej lekcji.
Zapraszam Cię do kolejnej, w której pochylimy się nad tematem funkcji
zliczania pod konkretnymi warunkami. Do zobaczenia!