Funkcje Excela, z którymi faktycznie zajdziesz daleko – przegląd według zastosowania

Dlaczego lepiej uczyć się funkcji według problemu, nie alfabetycznie
Listy "50 najważniejszych funkcji Excela" rzadko komuś pomagają, bo funkcja wyrwana z kontekstu nie zapada w pamięć. Łatwiej zapamiętać LICZ.JEŻELI, gdy wiesz, że odpowiada na pytanie "ile razy coś się powtarza", niż gdy czytasz samą definicję. Ten przegląd jest podzielony według tego, co chcesz policzyć, nie według kolejności alfabetycznej.
Liczenie i sumowanie
| Funkcja | Odpowiada na pytanie |
|---|---|
SUMA | Ile wynosi suma zakresu liczb? |
ŚREDNIA | Jaka jest średnia wartość zakresu? |
LICZ.JEŻELI | Ile komórek spełnia jeden warunek? |
LICZ.WARUNKI | Ile komórek spełnia kilka warunków naraz? |
SUMA.JEŻELI | Jaka jest suma tylko tych wartości, które spełniają jeden warunek? |
SUMA.WARUNKÓW | Jaka jest suma wartości spełniających kilka warunków naraz? |
ILE.LICZB | Ile komórek zawiera liczby (pomija puste i tekst)? |
ILE.NIEPUSTYCH | Ile komórek w zakresie nie jest pustych? |
Przykład z życia: masz listę 500 zamówień i chcesz policzyć wartość zamówień tylko z jednego regionu i tylko za styczeń:
=SUMA.WARUNKÓW(D2:D500;B2:B500;"Północ";C2:C500;"Styczeń")
Logika i warunki
| Funkcja | Odpowiada na pytanie |
|---|---|
JEŻELI | Co ma się stać, jeśli warunek jest prawdziwy, a co jeśli nie? |
WARUNKI | Który z kilku warunków jest prawdziwy (bez zagnieżdżania JEŻELI)? |
ORAZ | Czy wszystkie podane warunki są prawdziwe naraz? |
LUB | Czy przynajmniej jeden z podanych warunków jest prawdziwy? |
CZY.BŁĄD | Czy formuła zwraca błąd? (przydatne do obsługi błędów) |
JEŻELI.BŁĄD | Co wyświetlić, jeśli formuła zwraca błąd, zamiast samego komunikatu błędu? |
Przykład – zamiast wyświetlać #N/D!, gdy WYSZUKAJ.PIONOWO nie znajdzie dopasowania:
=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(A2;Cennik!A:B;2;FAŁSZ);"Brak w cenniku")
Wyszukiwanie i dopasowywanie danych
| Funkcja | Odpowiada na pytanie |
|---|---|
WYSZUKAJ.PIONOWO | Jaka wartość odpowiada tej wartości w innej tabeli (szukanie w prawo)? |
X.WYSZUKAJ | Jak wyżej, ale szuka w obu kierunkach i ma wbudowaną obsługę braku dopasowania |
INDEKS + PODAJ.POZYCJĘ | Jak WYSZUKAJ.PIONOWO, ale może szukać też w lewo |
PODAJ.POZYCJĘ | Na której pozycji w zakresie znajduje się dana wartość? |
Funkcje tekstowe
| Funkcja | Odpowiada na pytanie |
|---|---|
ZŁĄCZ.TEKSTY (lub &) | Jak połączyć kilka wartości w jeden tekst? |
LEWY, PRAWY, FRAGMENT.TEKSTU | Jak wyciągnąć część tekstu z komórki? |
DŁ | Ile znaków ma tekst w komórce? |
USUŃ.ZNAKI | Jak usunąć niewidoczne spacje i znaki na końcach tekstu? |
ZNAJDŹ / SZUKAJ.TEKST | Na której pozycji w tekście znajduje się dany fragment? |
LITERY.MAŁE, LITERY.WIELKIE, Z.WIELKIEJ.LITERY | Jak zmienić wielkość liter w tekście? |
Przykład – wyciągnięcie samej nazwy miasta z adresu zapisanego jako "Warszawa, ul. Przykładowa 1":
=LEWY(A2;ZNAJDŹ(",";A2)-1)
Funkcje daty i czasu
| Funkcja | Odpowiada na pytanie |
|---|---|
DZIŚ | Jaka jest dzisiejsza data (aktualizuje się automatycznie)? |
DNI.ROBOCZE | Jaka data wypadnie po dodaniu określonej liczby dni roboczych? |
NETWORKDAYS / DNI.ROBOCZE.NIESTANDARDOWE | Ile dni roboczych jest między dwiema datami (z możliwością wskazania świąt)? |
ROK, MIESIĄC, DZIEŃ | Jak wyciągnąć sam rok, miesiąc lub dzień z daty? |
DATA.RÓŻNICA | Ile lat/miesięcy/dni minęło między dwiema datami? |
Przykład – ile dni roboczych zostało do terminu projektu, nie licząc weekendów:
=DNI.ROBOCZE(DZIŚ();B2)
Funkcje matematyczne poza podstawowym dodawaniem
| Funkcja | Odpowiada na pytanie |
|---|---|
ZAOKR | Jak zaokrąglić liczbę do określonej liczby miejsc po przecinku? |
ZAOKR.GÓRA, ZAOKR.DÓŁ | Jak zawsze zaokrąglić w górę albo w dół, niezależnie od reguł zaokrąglania? |
SUMA.ILOCZYNÓW | Jak zsumować wynik mnożenia par wartości z dwóch zakresów bez kolumny pomocniczej? |
MOD | Jaka jest reszta z dzielenia (przydatne np. do sprawdzania parzystości)? |
LOSOWA.LICZBA.ZAKR | Jak wygenerować losową liczbę całkowitą w podanym zakresie? |
Jak wybrać właściwą funkcję, gdy nie znasz jej nazwy
Najszybsza droga to zacząć wpisywać w komórce = i pierwsze litery problemu, jaki chcesz rozwiązać, po angielsku lub polsku – np. =LICZ pokaże podpowiedzi wszystkich funkcji zaczynających się od "LICZ" wraz z krótkim opisem. Druga droga: karta Formuły > Wstaw funkcję, gdzie można przeszukiwać funkcje po kategorii (Matematyczne, Logiczne, Tekstowe, Data i godzina, Wyszukiwanie i odwołania).
Funkcje, które brzmią podobnie, ale robią coś innego
LICZ.JEŻELIliczy komórki spełniające warunek.SUMA.JEŻELIsumuje wartości z innej kolumny, gdy warunek jest spełniony. To nie są synonimy – pierwsza odpowiada "ile", druga odpowiada "jaka suma".ZNAJDŹrozróżnia wielkość liter,SZUKAJ.TEKSTnie. Jeśli szukasz "a" w tekście "Ala",ZNAJDŹznajdzie tylko małe "a",SZUKAJ.TEKSTznajdzie oba.PODAJ.POZYCJĘzwraca numer pozycji, nie samą wartość – typowy błąd początkujących to oczekiwanie, że ta funkcja zwróci wartość komórki, a nie jej "adres" w zakresie.
Jedna uwaga na koniec o nazwach funkcji
Nazwy funkcji w polskiej wersji Excela różnią się od angielskiej (np. VLOOKUP to WYSZUKAJ.PIONOWO, SUMIF to SUMA.JEŻELI, COUNTIF to LICZ.JEŻELI). Jeśli szukasz pomocy w internecie i trafiasz na materiał w angielskiej wersji, warto znać odpowiedniki – Excel sam podpowiada nazwę funkcji w drugim języku w oknie Wstaw funkcję, jeśli zmienisz ustawienia języka formuł w opcjach programu.
Funkcje statystyczne, o których rzadziej się mówi
| Funkcja | Odpowiada na pytanie |
|---|---|
MEDIANA | Jaka jest wartość środkowa zakresu (mniej czuła na wartości odstające niż średnia)? |
MODUŁ.LICZBY (ABS) | Jaka jest wartość bez znaku (zawsze dodatnia)? |
MAX.K, MIN.K | Jaka jest n-ta największa lub najmniejsza wartość w zakresie? |
CZĘSTOŚĆ | Ile wartości wpada w każdy z zadanych przedziałów? |
RANG | Na którym miejscu w rankingu jest dana wartość? |
Przykład wykorzystania MAX.K – znalezienie trzech najwyższych wyników sprzedaży bez sortowania całej tabeli:
{=MAX.K(B4:B50;{1;2;3})}
To formuła tablicowa zwracająca jednocześnie pierwszy, drugi i trzeci najwyższy wynik z zakresu B4:B50 – przydatne, gdy potrzebujesz tylko kilku najlepszych wyników bez przebudowy całej tabeli przez sortowanie.
Warianty tej samej funkcji – JEŻELI.BŁĄD kontra CZY.BŁĄD
Często myli się te dwie funkcje, choć robią coś innego. CZY.BŁĄD zwraca PRAWDA albo FAŁSZ (sam nie "naprawia" niczego, tylko informuje) – używa się jej głównie w połączeniu z JEŻELI:
=JEŻELI(CZY.BŁĄD(A2/B2);"Błąd dzielenia";A2/B2)
JEŻELI.BŁĄD robi to samo krócej, bez potrzeby powtarzania formuły dwa razy:
=JEŻELI.BŁĄD(A2/B2;"Błąd dzielenia")
Różnica staje się ważna, gdy sama formuła jest długa i skomplikowana – CZY.BŁĄD wymaga wpisania jej dwukrotnie (raz w warunku, raz w "wartości gdy prawda"), co jest podatne na błędy przy edycji, JEŻELI.BŁĄD wymaga wpisania jej tylko raz.
Częste błędy przy doborze funkcji
- Używanie SUMA.ILOCZYNÓW tam, gdzie wystarczy SUMA.WARUNKÓW.
SUMA.ILOCZYNÓWjest bardziej elastyczna, ale też trudniejsza do odczytania przez kogoś innego – jeśli prostsza funkcja robi to samo, czytelność wygrywa. - Zagnieżdżanie więcej niż trzech poziomów JEŻELI. Taka formuła działa, ale jest praktycznie nieczytelna po tygodniu przerwy –
WARUNKIrozwiązuje ten problem czytelniej. - Nieznajomość różnicy między funkcjami zaokrąglającymi.
ZAOKRzaokrągla według standardowych reguł matematycznych,ZAOKR.GÓRAiZAOKR.DÓŁzawsze w jedną stronę niezależnie od wartości – pomylenie ich przy liczeniu np. opłat czy podatków daje systematycznie błędne, choć niewielkie, różnice.