Formuły ·

Formuła w Excelu – jak ją poprawnie zbudować, żeby nie rozsypała się po skopiowaniu

Arkusz Excela z formułami obliczającymi wartość zamówienia

Formuła to nie to samo co liczba wpisana na oko

Różnica między arkuszem kalkulacyjnym a kartką papieru polega właśnie na formułach – komórka nie przechowuje wyniku, przechowuje przepis na jego obliczenie. Każda formuła w Excelu zaczyna się znakiem =. Bez tego znaku to, co wpiszesz, zostanie potraktowane jako zwykły tekst lub liczba, nie jako instrukcja do obliczenia.

Załóżmy, że masz taką tabelę:

A (Produkt)B (Ilość)C (Cena)
Monitor3650
Klawiatura5120

W komórce D2 chcesz policzyć wartość zamówienia:

=B2*C2

To najprostsza możliwa formuła – odwołanie do dwóch komórek i operator mnożenia. Po wpisaniu =B2*C2 i naciśnięciu Enter, Excel pokaże wynik (1950), ale w pasku formuły (pod wstążką, nad arkuszem) zawsze widać oryginalny wzór, nie wynik.

Operatory, bez których się nie obejdzie

OperatorDziałaniePrzykład
+dodawanie=A1+A2
-odejmowanie=A1-A2
*mnożenie=A1*A2
/dzielenie=A1/A2
^potęgowanie=A1^2
&łączenie tekstu=A1&" "&B1
=, <>, >, <porównania=A1>100

Excel respektuje standardową kolejność wykonywania operacji matematycznych (najpierw potęgowanie, potem mnożenie/dzielenie, potem dodawanie/odejmowanie). Jeśli chcesz wymusić inną kolejność, używasz nawiasów, tak jak w matematyce szkolnej: =(A1+A2)*B1 da inny wynik niż =A1+A2*B1.

Odwołania względne i absolutne – skąd bierze się ten znak dolara

To jest temat, który najczęściej psuje formuły skopiowane do wielu wierszy albo kolumn. Odwołanie A1 to odwołanie względne – kiedy skopiujesz formułę z komórki B2 do B3, Excel automatycznie zmieni A1 na A2, bo "przesunął się" wiersz. To zwykle jest dokładnie to, czego chcesz.

Problem zaczyna się, gdy formuła odwołuje się do jednej, stałej komórki – na przykład do kursu waluty albo do stawki VAT, która ma być taka sama dla każdego wiersza. Załóżmy, że w komórce F1 masz stawkę VAT (0,23), a w kolumnie D chcesz policzyć wartość z VAT:

=C2*(1+F1)

Skopiowana w dół do wiersza 3, ta formuła zmieni się na =C3*(1+F2) – a w F2 nic nie ma, bo stawka VAT jest tylko w F1. Rozwiązanie to zablokowanie odwołania do F1 znakiem dolara:

=C2*(1+$F$1)

Teraz niezależnie od tego, do którego wiersza skopiujesz formułę, część $F$1 zostanie niezmieniona. $ przed literą blokuje kolumnę, $ przed cyfrą blokuje wiersz – można zablokować tylko jedno z nich (np. $A1 albo A$1), jeśli taka jest potrzeba.

Funkcja JEŻELI – pierwszy krok w stronę logiki warunkowej

JEŻELI to funkcja, która sprawdza warunek i zwraca jedną z dwóch wartości, zależnie od tego, czy warunek jest prawdziwy:

=JEŻELI(C2>500;"Drogi";"Standardowy")

Składnia: JEŻELI(warunek; wartość_gdy_prawda; wartość_gdy_fałsz). Funkcje JEŻELI można zagnieżdżać, żeby obsłużyć więcej niż dwa przypadki:

=JEŻELI(C2>1000;"Bardzo drogi";JEŻELI(C2>500;"Drogi";"Standardowy"))

Przy więcej niż dwóch-trzech poziomach zagnieżdżenia formuła staje się nieczytelna – w takich sytuacjach lepiej sięgnąć po funkcję WARUNKI (dostępną w nowszych wersjach Excela), która sprawdza kilka warunków po kolei bez zagnieżdżania.

Funkcje sumujące z warunkiem

Jedna z najczęściej potrzebnych rzeczy w codziennej pracy to zsumowanie tylko części danych, spełniającej konkretny warunek:

=SUMA.JEŻELI(A2:A100;"Monitor";C2:C100)

Ta formuła sumuje wartości z kolumny C, ale tylko w tych wierszach, gdzie w kolumnie A jest "Monitor". Jeśli potrzebujesz więcej niż jednego warunku naraz (np. produkt ORAZ miesiąc), używasz SUMA.WARUNKÓW:

=SUMA.WARUNKÓW(C2:C100;A2:A100;"Monitor";B2:B100;"Styczeń")

SUMA.ILOCZYNÓW – funkcja niedoceniana, a bardzo praktyczna

SUMA.ILOCZYNÓW mnoży odpowiadające sobie elementy dwóch (lub więcej) zakresów i sumuje wyniki. Klasyczne zastosowanie to policzenie wartości całego zamówienia bez pomocniczej kolumny:

=SUMA.ILOCZYNÓW(B2:B10;C2:C10)

To odpowiednik wpisania =B2*C2+B3*C3+B4*C4... dla każdego wiersza i zsumowania tego – tylko w jednej formule, bez pomocniczej kolumny D.

PODAJ.POZYCJĘ – funkcja, która mówi "gdzie", nie "co"

PODAJ.POZYCJĘ zwraca numer pozycji wartości w zakresie, nie samą wartość:

=PODAJ.POZYCJĘ("Klawiatura";A2:A100;0)

Sama w sobie bywa mniej przydatna, ale w połączeniu z INDEKS daje elastyczną alternatywę dla WYSZUKAJ.PIONOWO, która potrafi szukać w obu kierunkach (lewo i prawo), nie tylko w prawo.

Dlaczego formuła czasem zwraca błąd, a nie liczbę

BłądNajczęstsza przyczyna
#DZIEL/0!dzielenie przez komórkę, która jest pusta albo zawiera 0
#N/D!funkcja wyszukująca nie znalazła dopasowania
#NAZWA?literówka w nazwie funkcji albo brak cudzysłowów przy tekście
#WARTOŚĆ!formuła dostała tekst, gdzie oczekiwała liczby
#ARG!zła liczba lub typ argumentów w funkcji

Zanim zaczniesz szukać błędu w logice formuły, sprawdź właśnie te podstawowe przyczyny – w praktyce odpowiadają za większość komunikatów błędów, które widzisz na co dzień.

Jak budować formuły, żeby nie trzeba było ich naprawiać za tydzień

SUMA.ILOCZYNÓW w praktyce – liczenie dni roboczych bez dodatków

Jedno z lepszych ćwiczeń na zrozumienie SUMA.ILOCZYNÓW to policzenie liczby dni roboczych między dwiema datami. Excel ma do tego wbudowaną funkcję DNI.ROBOCZE (ona sama liczy dni od poniedziałku do piątku, z pominięciem sobót i niedziel), ale SUMA.ILOCZYNÓW daje coś, czego DNI.ROBOCZE nie potrafi: elastyczność w wyborze, które dni tygodnia liczą się jako "robocze".

Załóżmy, że data początkowa jest w komórce C3, a końcowa w C4 (jako prawdziwe daty, nie tekst). Standardowe dni robocze (poniedziałek-piątek) policzysz tak:

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>7);--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>1))

Formuła działa na fakcie, że w Excelu daty są zwykłymi liczbami – WIERSZ(ADR.POŚR(C3&":"&C4)) generuje ciąg kolejnych liczb (czyli kolejnych dni) od daty początkowej do końcowej, a DZIEŃ.TYG() sprawdza, jaki dzień tygodnia odpowiada każdej z nich (gdzie 1 = niedziela, 7 = sobota). Podwójny minus (--) zamienia wartości logiczne PRAWDA/FAŁSZ na 1/0, a SUMA.ILOCZYNÓW mnoży odpowiadające sobie wartości i sumuje wynik.

Przewaga tego podejścia nad DNI.ROBOCZE widać, gdy dniami wolnymi mają być, przykładowo, środy i soboty (np. w sklepie zamkniętym w te dni), a nie klasyczny weekend. Wystarczy zmienić liczby 7 i 1 na numery odpowiadające innym dniom tygodnia (niedziela=1, poniedziałek=2, wtorek=3, środa=4, czwartek=5, piątek=6, sobota=7):

=SUMA.ILOCZYNÓW(--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>4);--(DZIEŃ.TYG(WIERSZ(ADR.POŚR(C3&":"&C4)))<>7))

Taka formuła policzy dni "robocze" z wykluczeniem środy i soboty — przykład: dla zakresu dat od 1 do 30 czerwca (przykładowy miesiąc, 30 dni) taka formuła zwróci inną liczbę dni niż standardowe DNI.ROBOCZE, bo inaczej definiuje dni wolne.

Wariant alternatywny – tablica dni do zliczenia

Istnieje też wariant tej samej formuły zapisany przez stałą tablicową, który bywa wygodniejszy, gdy chcesz liczyć konkretne dni tygodnia (np. tylko wtorki i czwartki) zamiast wykluczać dni wolne:

{=SUMA(ZAOKR.DO.CAŁK((DZIEŃ.TYG(C3-{2;3;4;5;6})+C4-C3)/7))}

W tej postaci stała {2;3;4;5;6} odpowiada dniom od poniedziałku do piątku (numeracja dni tygodnia zaczyna się od niedzieli=1). Zmieniając zawartość tej stałej, np. na {3;5;7}, policzysz tylko wtorki, czwartki i soboty w danym zakresie dat – coś, czego żadna wbudowana funkcja Excela nie zrobi jednym wywołaniem.

Funkcja WIERSZ() jako licznik w formułach tablicowych

Konstrukcja WIERSZ(ADR.POŚR(...)), użyta wyżej, wygląda nieintuicyjnie, ale ma proste wyjaśnienie: w formule tablicowej WIERSZ() działa jak licznik, generujący ciąg kolejnych liczb całkowitych. Zamiast ręcznie wpisywać listę dni czy pozycji, formuła sama "odlicza" je na podstawie zakresu wierszy. Ta sama technika stoi za wieloma formułami tablicowymi w Excelu, które muszą przejść po kolei przez cały zakres komórek bez użycia pętli VBA.

Typowy błąd przy formułach tablicowych

Formuła tablicowa (czyli taka, która operuje na całych tablicach wartości, a nie na pojedynczych komórkach) musi zostać zatwierdzona kombinacją Ctrl+Shift+Enter, nie samym Enter. Jeśli wpiszesz ją zwykłym Enterem, Excel policzy ją tylko dla pierwszego elementu tablicy i zwykle da błędny albo niepełny wynik – bez żadnego komunikatu błędu, co jest akurat najbardziej podstępne. Po poprawnym zatwierdzeniu formuła w pasku formuły pojawi się w klamrach {...} – te klamry nie wpisujesz sam, Excel dodaje je automatycznie jako znak, że formuła jest tablicowa.