Krótka odpowiedź — kiedy której formuły użyć?
Do podsumowań wykorzystaj SUMA i ŚREDNIA, do podejmowania decyzji — JEŻELI oraz ORAZ i LUB, a do wyszukiwania danych — X.WYSZUKAJ lub, w starszych wersjach programu, WYSZUKAJ.PIONOWO. Tekst możesz łączyć funkcją ZŁĄCZ.TEKST albo operatorem &, a przetwarzać za pomocą funkcji takich jak LEWY, PRAWY i FRAGMENT.TEKSTU. Te podstawowe formuły w Excelu wystarczą do wielu codziennych zadań.
Podstawowe formuły i gotowe przykłady do wklejenia
W Excelu każda formuła zaczyna się od znaku równości (=). Podczas wpisywania programu podpowiada dostępne funkcje i ich argumenty. W polskich ustawieniach najczęściej separatorem argumentów jest średnik (;), ale zależy to od ustawień regionalnych. Jeśli formuła zgłasza błąd składni, sprawdź, jaki separator jest skonfigurowany w Twoim Excelu.
SUMA — szybkie sumowanie
Cel: zsumowanie liczb w kolumnie.
Formuła: =SUMA(A1:A10)
Wynik: suma wartości ze wszystkich komórek od A1 do A10. Puste komórki są pomijane.
ŚREDNIA — obliczanie średniej
Cel: obliczenie średniej arytmetycznej.
Formuła: =ŚREDNIA(A1:A10)
Wynik: średnia z wartości liczbowych w podanym zakresie. Puste komórki są ignorowane, podobnie jak komórki zawierające tekst.
Łączenie tekstu — ZŁĄCZ.TEKST i operator &
Cel: połączenie imienia i nazwiska w jeden ciąg tekstowy.
- Funkcja:
=ZŁĄCZ.TEKST(B2;" ";C2) - Operator:
=B2&" "&C2
Obie metody zwrócą wynik „Imię Nazwisko”. Funkcja jest przydatna przy łączeniu wielu elementów, a operator & sprawdza się w prostych przypadkach.
Funkcje tekstowe — LEWY, PRAWY i FRAGMENT.TEKSTU
Cel: wyodrębnianie fragmentów tekstu z komórki.
- Pierwsze trzy znaki:
=LEWY(B2;3) - Ostatnie trzy znaki:
=PRAWY(B2;3) - Pięć znaków od czwartego miejsca:
=FRAGMENT.TEKSTU(B2;4;5)
Funkcje te przydają się np. do wyodrębniania prefiksów, końcówek kodów produktów i fragmentów identyfikatorów.
Wyszukiwanie danych: X.WYSZUKAJ vs WYSZUKAJ.PIONOWO
Jeśli korzystasz z nowszego Excela, warto preferować funkcję X.WYSZUKAJ. Pozwala wyszukiwać dane w dowolnym kierunku, domyślnie stosuje dokładne dopasowanie i nie wymaga określania numeru kolumny wynikowej. W starszych wersjach możesz użyć funkcji WYSZUKAJ.PIONOWO.
| Potrzeba | Funkcja | Przykład |
|---|---|---|
| Wyszukiwanie wartości w dowolnej kolumnie i zwracanie wyniku z innej | X.WYSZUKAJ | =X.WYSZUKAJ(F2;B2:B11;D2:D11) |
| Wyszukiwanie w starszych wersjach Excela | WYSZUKAJ.PIONOWO | =WYSZUKAJ.PIONOWO(A2;A10:C20;2;FAŁSZ) |
Ważne: X.WYSZUKAJ nie jest dostępna w Excelu 2016 i Excelu 2019. W tych wersjach używaj WYSZUKAJ.PIONOWO. Pamiętaj, że kolumna z szukaną wartością musi znajdować się po lewej stronie kolumny wynikowej, a czwarty argument FAŁSZ wymusza dokładne dopasowanie.
Logika i zagnieżdżanie: JEŻELI, ORAZ i LUB
Funkcja JEŻELI pozwala zwrócić jedną wartość, gdy warunek jest spełniony, i inną, gdy nie jest.
Składnia: =JEŻELI(test_logiczny;wartość_jeżeli_prawda;wartość_jeżeli_fałsz)
Przykłady:
- Prosty warunek:
=JEŻELI(C2="Tak";1;0)— zwróci 1, jeśli w C2 znajduje się tekst „Tak”, w przeciwnym razie 0. - Kilka warunków jednocześnie:
=JEŻELI(ORAZ(D2>0;E2>0);"OK";"Sprawdź")— zwróci „OK”, jeśli oba warunki są spełnione. - Alternatywne warunki:
=JEŻELI(LUB(D2>0;E2>0);"OK";"Sprawdź")— zwróci „OK”, jeśli przynajmniej jeden warunek jest spełniony.
Gdy zagnieżdżasz wiele funkcji JEŻELI, formuła może stać się trudna do odczytania. W przypadku kilku progów warto rozważyć funkcję WARUNKI, która pozwala sprawdzać kolejne warunki bez tworzenia długiej kaskady JEŻELI. Jest dostępna m.in. w Excelu 2019, 2021 i Microsoft 365.
Formuły tablicowe — kiedy i jak używać?
Formuły tablicowe wykonują obliczenia na wielu wartościach jednocześnie. W nowszych wersjach Excela dostępne są tablice dynamiczne, których wyniki mogą automatycznie rozlewać się na sąsiednie komórki po zatwierdzeniu formuły klawiszem Enter. W starszych wersjach niektóre formuły tablicowe wymagały zatwierdzenia kombinacją Ctrl+Shift+Enter (CSE).
Przykłady:
- Suma iloczynów:
=SUMA.ILOCZYNÓW(F10:F19;G10:G19)— mnoży wartości z odpowiadających sobie wierszy i sumuje wyniki. Nie wymaga zatwierdzania jako formuła tablicowa. - Filtrowanie danych:
=FILTRUJ(B2:B100;C2:C100>0;"Brak wyników")— zwraca wartości z kolumny B tylko dla wierszy, w których odpowiadająca im wartość w kolumnie C jest większa od zera.
Funkcja FILTRUJ jest dostępna w Microsoft 365 oraz nowszych wersjach Excela, m.in. 2021 i 2024. Wynik może rozlać się na kolejne komórki. Jeśli obszar wynikowy jest zajęty, Excel zwróci błąd #ROZLANIE!.
W starszych wersjach Excela nie wszystkie funkcje tablic dynamicznych są dostępne. Dlatego przy tworzeniu formuł warto uwzględnić wersję programu, z której korzystają odbiorcy arkusza.
Pokazywanie formuł i podstawowe debugowanie
Jeśli chcesz zobaczyć formuły zamiast wyników w komórkach, wybierz kartę Formuły → Pokaż formuły. Możesz też skorzystać ze skrótu Ctrl+` (akcent słaby). Skrót może różnić się w zależności od systemu i układu klawiatury.
Najczęstsze przyczyny błędów:
- Brak znaku
=na początku formuły. - Nieprawidłowa liczba nawiasów.
- Niewłaściwy separator argumentów (
;lub,). - Nieprawidłowy adres komórki lub zakresu.
- Nieznana nazwa funkcji albo funkcja niedostępna w używanej wersji Excela.
- Liczby zapisane jako tekst zamiast wartości liczbowych.
Warto również korzystać z narzędzi sprawdzania błędów i opcji Formuły → Szacuj formułę, która pozwala prześledzić kolejne etapy obliczeń.
Jeśli chcesz ukryć formuły przed innymi użytkownikami, zaznacz komórki, otwórz formatowanie komórek, przejdź do zakładki Ochrona i zaznacz opcję „Ukryte”. Następnie włącz ochronę arkusza. Samo zaznaczenie tej opcji nie wystarczy — formuły zostaną ukryte dopiero po zabezpieczeniu arkusza.
Szybka lista gotowych formuł do wklejenia
- Sumowanie sprzedaży:
=SUMA(C2:C31)— łączna wartość sprzedaży. - Średnia ocena:
=ŚREDNIA(D2:D101)— średnia z ocen. - Wyszukiwanie w nowszym Excelu:
=X.WYSZUKAJ(F2;B2:B11;D2:D11)— wartość z dopasowanego wiersza. - Wyszukiwanie w starszym Excelu:
=WYSZUKAJ.PIONOWO(A2;A10:C20;2;FAŁSZ)— wartość z drugiej kolumny tabeli. - Łączenie imienia i nazwiska:
=ZŁĄCZ.TEKST(B2;" ";C2)lub=B2&" "&C2. - Warunek „Tak/Nie” na liczby:
=JEŻELI(C2="Tak";1;0)— zwraca 1 lub 0. - Suma ważona:
=SUMA.ILOCZYNÓW(F10:F19;G10:G19)— suma iloczynów odpowiadających sobie wartości. - Ekstrakcja prefiksu:
=LEWY(B2;3)— pierwsze trzy znaki tekstu.
Gdy formuła nie działa
- Sprawdź separator argumentów — zamień średniki na przecinki lub odwrotnie, jeśli wymagają tego ustawienia programu.
- Zweryfikuj format komórek — jeśli liczby są zapisane jako tekst, możesz użyć opcji Tekst jako kolumny lub odpowiednio przekonwertować dane.
- Sprawdź odwołania względne i bezwzględne — jeśli podczas kopiowania formuły zakres się przesuwa, zablokuj go znakiem
$, np.$A$1:$A$10. - W WYSZUKAJ.PIONOWO używaj
FAŁSZ, jeśli potrzebujesz dokładnego dopasowania. - Sprawdź wersję Excela — jeśli X.WYSZUKAJ lub FILTRUJ nie są rozpoznawane, użyj odpowiednika dostępnego w starszej wersji.
To zestaw podstawowych formuł w Excelu, który wystarcza do wielu codziennych zadań: sumowania, obliczania średnich, podejmowania decyzji, wyszukiwania danych i przetwarzania tekstu. W miarę potrzeb możesz rozszerzyć go o funkcje daty, zaokrąglania i analizy danych.