Czy kiedykolwiek zastanawiałeś się, jak odkryć pełnię możliwości programu Microsoft Excel i Google Sheets, korzystając z formuł i funkcji? W poprzednim artykule zapoznaliśmy się z podstawowymi formułami, które są niezbędne do efektywnej pracy z tymi narzędziami. Teraz, przygotuj się na odkrycie kolejnych zaawansowanych formuł, które pozwolą Ci na jeszcze większą kontrolę nad danymi i zwiększą Twoją efektywność pracy. Czy jesteś gotowy na kolejny krok w swojej przygodzie z Microsoft Excel i Google Sheets? Zapraszam do lektury!
Do każdego przykładu przygotowałem prostą tabelę, na której przećwiczymy sobie poniższe funkcje. Dodatkowo, przy każdej funkcji podałem jej angielski odpowiednik w nawiasie. Czasem również będę przeplatał nazwy polskie z angielskimi, aby ułatwić przyswajanie tych formuł – przydatne, jeśli Twoje arkusze są w języku angielskim.
Formuły do wyszukiwania i odwołań
| ID | Imię | Nazwisko | Adres e-mail |
|---|---|---|---|
| 1 | John | Doe | john@abc.com |
| 2 | Jane | Doe | jane@abc.com |
| 3 | Jim | Beam | jim@abc.com |
| 4 | Jack | Daniels | jack@abc.com |
| 5 | Alice | Smith | alice@abc.com |
| 6 | Bob | Johnson | bob@abc.com |
| 7 | Charlie | Brown | charlie@abc.com |
| 8 | David | Williams | david@abc.com |
| 9 | Eva | Jones | eva@abc.com |
| 10 | Frank | Miller | frank@abc.com |
WYSZUKAJ.PIONOWO (VLOOKUP)
Funkcja WYSZUKAJ.PIONOWO pozwala na wyszukiwanie konkretnych danych w tabeli lub zakresie komórek. Jest to chyba najczęściej wykorzystywana przeze mnie funkcja (i pewnie nie tylko mnie) – jest to zupełna podstawa, którą każdy powinien znać. Pozwala w prosty sposób przyporządkować dane bazując na unikalnej referencji.
Aby wywołać tę funkcję, należy wpisać =WYSZUKAJ.PIONOWO(co_szukamy (komórka/tekst/itd.); gdzie_szukamy (tabela/tablica); numer_kolumny (którą ma zwrócić w kolejności od pierwszej kolumny w zakresie przeszukiwanym) ; [przeszukiwany zakres - opcjonalne - fałsz oznacza dokładne dopasowanie, a prawda przybliżone]). Na przykład, aby znaleźć adres e-mail pracownika o imieniu “John“, wpisz =VLOOKUP("John"; B1:D11; 3; FALSE). Pamiętaj tylko, że gdy będziesz miał kilku “John’ów“, to funkcja zwróci pierwszy znaleziony wynik.
INDEKS (INDEX)
Funkcja INDEKS zwraca wartość lub odwołanie do wartości z tabeli lub zakresu na podstawie numeru wiersza i kolumny. Aby wywołać tę funkcję, wpisz =INDEKS(zakres; numer_wiersza; [numer_kolumny - opcjonalne]). Na przykład, aby uzyskać wartość z trzeciego wiersza i czwartej kolumny, wpisz =INDEX(A1:D11;3;4).
PODAJ.POZYCJĘ (MATCH)
Funkcja PODAJ.POZYCJĘ zwraca pozycję określonej wartości w danym zakresie (jedna kolumna bądź jeden wiersz). Aby wywołać tę funkcję, wpisz =PODAJ.POZYCJĘ(co_szukamy; gdzie_szukamy; [typ_dopasowania - opcjonalne]). Na przykład, aby znaleźć, w którym wierszu znajduje się “John”, wpisz =MATCH("John";$B$1:$B$11;0).
Funkcję INDEKS oraz PODAJ.POZYCJĘ można łączyć w dość ciekawy sposób. Dzięki temu możesz stworzyć dobrą formułę do przeszukiwania – tak jak WYSZUKAJ.PIONOWO, ale bez obawy o kolejność zwracania wartości. Chcąc na przykład znaleźć adres e-mail osoby o imieniu “John”, możemy skorzystać z następującej formuły: =INDEX(D2:D11; MATCH("John"; B2:B11; 0)).
Formuły do operacji matematycznych i statystycznych
| Imię | Miesiąc | Sprzedaż |
|---|---|---|
| John | Styczeń | 200 |
| Jane | Styczeń | 150 |
| John | Styczeń | 50 |
| Jack | Luty | 250 |
| John | Marzec | 350 |
| Jane | Marzec | 400 |
| Alice | Kwiecień | 500 |
| Bob | Kwiecień | 450 |
| Charlie | Maj | 600 |
| David | Maj | 550 |
| Eva | Czerwiec | 700 |
| Frank | Czerwiec | 650 |
SUMA.WARUNKÓW (SUMIFS)
Już wcześniej opisywałem działanie podobnej funkcji, a mianowicie SUMA.JEŻELI. Funkcja SUMA.WARUNKÓW jest bardzo podobna i pozwala na sumowanie wartości, które spełniają wiele kryteriów, a nie tylko jedno. Aby wywołać tę funkcję, wpisz =SUMA.WARUNKÓW(zakres_sumowania; zakres_kryteriów1; kryterium1; [zakres_kryteriów2; kryterium2; ...]). Na przykład, aby zsumować wartość sprzedaży dla “Johna” w styczniu, wpisz =SUMIFS(C2:C13; A2:A13; "John"; B2:B13; "Styczeń").
LICZ.WARUNKI (COUNTIFS)
Kolejna funkcja, którą praktycznie omówiliśmy w poprzednim materiale (LICZ.JEŻELI). Formuła LICZ.WARUNKI pozwala na zliczenie komórek spełniających wiele kryteriów. Aby wywołać tę funkcję, wpisz =LICZ.WARUNKI(zakres_kryteriów1; kryterium1; [zakres_kryteriów2; kryterium2; ...]). Na przykład, aby zliczyć liczbę transakcji sprzedaży powyżej 200 zł dla “Johna”, wpisz =COUNTIFS(A2:A13; "John"; C2:C13; ">200").
WARIANCJA (VAR)
Funkcja WARIANCJA sprawdza, jak bardzo liczby, które podajesz, różnią się od siebie. To jak mierzenie, jak duże są różnice między tym, co sprzedajesz w sklepie każdego dnia. Wpisujesz to tak: =WARIANCJA(i podajesz zakres liczb, które chcesz sprawdzić). Na przykład, jeśli chcesz zobaczyć, jak bardzo różne były Twoje sprzedaże zapisane w komórkach od C2 do C13, wpiszesz =VAR(C2:C13).
Kiedy otrzymasz wynik, pokaże Ci to, jak stabilne są Twoje zarobki. Jeśli wynik jest duży, to oznacza, że kwoty, które zarabiasz każdego dnia, są bardzo różne – jednego dnia możesz zarobić dużo, a innego niewiele. Jeśli wynik jest mały, to oznacza, że Twoje zarobki są mniej więcej stałe – nie ma dużych różnic między tym, ile zarabiasz każdego dnia. To pomaga Ci zrozumieć, jak duże są wahania w Twoich danych.
W omawianej tabeli wygląda to następująco:
Widać, że wariancja jest ogromna i nawet nie bliska 0. A co w przypadku, gdy te liczby zbliżymy do siebie?
Zobacz, od razu mniejszy wynik, co równa się większej powtarzalności sprzedaży.
ODCH.STANDARD (STDEV)
Odchylenie standardowe to narzędzie statystyczne mówiące nam, jak bardzo nasze dane różnią się od średniej (przeciętnej) wartości. Możemy to obliczyć za pomocą funkcji =ODCH.STANDARD(zakres) w Excelu, podając jej zakres danych, które nas interesują.
Jeśli odchylenie jest małe (bliskie 0), to większość danych jest blisko średniej, czyli wyniki są do siebie podobne. Jeśli odchylenie jest duże, to wyniki są bardzo zróżnicowane – niektóre są dużo wyższe, a niektóre dużo niższe od średniej. Daje nam to informację, jak stabilne są nasze wyniki. Na przykład, aby obliczyć odchylenie standardowe dla kwoty sprzedaży, wpisz =STDEV(C2:C13).
Widać, że odchylenie jest dosyć spore. Spróbujmy uśrednić wartości i wtedy zobaczyć wynik.
Formuły do operacji na tekście
| Adres e-mail |
|---|
| john@abc.com |
| jane@abc.com |
| jim@abc.com |
| jack@abc.com |
| alice@abc.com |
| bob@abc.com |
| charlie@abc.com |
| david@abc.com |
| eva@abc.com |
| frank@abc.com |
ZNAJDŹ (FIND)
Funkcja ZNAJDŹ zwraca pozycję określonego tekstu w ciągu tekstowym. Aby wywołać tę funkcję, wpisz =ZNAJDŹ(co_szukamy; gdzie_szukamy; [początek - opcjonalne]). Na przykład, aby znaleźć pozycję “@” w adresie e-mail, wpisz =FIND("@"; A2).
LEWY (LEFT)
Funkcja LEWY zwraca określoną liczbę znaków od lewej strony ciągu tekstowego. Aby wywołać tę funkcję, wpisz =LEWY(tekst; [liczba_znaków - opcjonalne]). Na przykład, aby uzyskać pierwsze 4 znaków z adresu e-mail, wpisz =LEFT(A2; 4).
PRAWY (RIGHT)
Funkcja PRAWY zwraca określoną liczbę znaków od prawej strony ciągu tekstowego. Aby wywołać tę funkcję, wpisz =PRAWY(tekst; [liczba_znaków - opcjonalne]). Na przykład, aby uzyskać ostatnie 7 znaków z adresu e-mail, wpisz =RIGHT(A2; 7).
FRAGMENT.TEKSTU (MID)
Funkcja FRAGMENT.TEKSTU zwraca określoną liczbę znaków z ciągu tekstowego, zaczynając od określonej pozycji. Aby wywołać tę funkcję, wpisz =FRAGMENT.TEKSTU(tekst; początek; liczba_znaków). Na przykład, aby uzyskać 8 znaków z adresu e-mail, zaczynając od 5. pozycji, wpisz =MID(A2; 5; 8).
Formuły do operacji na datach i czasie
| Data transakcji |
|---|
| 2023-01-01 |
| 2023-02-15 |
| 2023-03-30 |
| 2023-04-14 |
| 2023-05-29 |
| 2023-06-13 |
| 2023-07-28 |
| 2023-08-12 |
| 2023-09-26 |
| 2023-10-11 |
DZIŚ (TODAY)
Funkcja DZIŚ zwraca bieżącą datę. Aby wywołać tę funkcję, wpisz =TODAY().
Na przykład, aby obliczyć, ile dni minęło od daty sprzedaży do dzisiaj, wpisz =TODAY()-A2 i zmień format danych na ogólny bądź liczbowy, aby pokazała się cyfra.
TERAZ (NOW)
Funkcja TERAZ zwraca bieżącą datę i czas. Aby wywołać tę funkcję, wpisz =NOW().
ROK (YEAR), MIESIĄC (MONTH), DZIEŃ (DAY)
Funkcje ROK, MIESIĄC i DZIEŃ zwracają odpowiednio rok, miesiąc i dzień z określonej daty. Aby wywołać te funkcje, wpisz =ROK(data), =MIESIĄC(data), =DZIEŃ(data). Na przykład, aby uzyskać rok z daty sprzedaży, wpisz =YEAR(F2) i zmień format danych na ogólny bądź liczbowy.
To samo dla miesiąca…
i dnia.
Podsumowanie
Excel i Google Sheets oferują wiele zaawansowanych formuł, które mogą znacznie ułatwić i przyspieszyć pracę z arkuszami danych. W tym artykule przedstawiłem tylko kilka z nich, ale warto pamiętać, że ich liczba jest znacznie większa – 16 innych pokazuje w poprzednim artykule – dodatkowo 12 unikalnych dla Google Sheets (tak, Microsoft Excel ich nie posiada) możesz sprawdzić w tym wpisie.
Jeśli interesuje Cię oficjalna dokumentacja/instrukcja dotycząca funkcji w excelu, to kliknij tutaj. Natomiast jeśli korzystasz z Google Sheets, to link do dokumentacji jest tutaj.
Będę rozwijał temat – w kolejnych artykułach pokażę więcej przykładów, aby zaznajomić Cię z ich zastosowaniem i wykorzystaniem w bardziej skomplikowanych przypadkach.
