Formuły
Cele lekcji
W tej lekcji omówimy mechanizmy za pomocą których jest możliwe wykonywanie działań w Excelu, czyli:
" pojęcie formuły
" budowanie i edycja formuł
" podstawowe działania takie jak: dodawanie, odejmowanie, mnożenie, dzielenie
" adresowanie względne i bezwzględne
" autowypełnianie i kopiowanie formuł
" kolejność wykonywania działań w formułach
" sprawdzanie poprawności formuł (inspekcja formuł)
Pojęcie formuły
Użycie formuł pozwala na zautomatyzowanie obliczeń wykonywanych w arkuszu Excela: jak np. sumowania,
dzielenia, przeliczenia kursów walut, wyliczenia procentów, itd. Można budować proste formuły
wykonujące podstawowe działania matematyczne, traktując arkusz jak zwykły kalkulator. Jednak często
arkusz kalkulacyjny jest wykorzystywany w o wiele bardziej zaawansowanych zastosowaniach gdzie trzeba
pracować na dużej ilości danych, budować skomplikowane formuły i korzystać z funkcji kopiowania formuł
dla serii danych.
Budowanie i edycja formuł
Formuły są szczególnym typem danych wpisywanych do komórki arkusza. Formuły można wprowadzać
bezpośrednio w komórce lub za pomocą paska formuły. (Rys1)
Formuły można wprowadzać na
pasku lub w komórce
Rys. 1. Budowanie formuł
Formuła wpisana w komórce będzie się wyświetlała również w pasku formuły.
Budowa formuł wymusza na użytkownikach stosowanie się do pewnych zasad tworzenia formuł. Należy
pamiętać, że każda formuła musi się zaczynać od znaku = (znak równości), tylko w takim przypadku
możemy być pewni, że działanie zostanie wykonane. Jeżeli z jakichś powodów zapomnimy użyć znaku  =
przed formułą, Excel potraktuje wprowadzone działanie jako wartość tekstową.
Uwaga: Wartości tekstowe w Excelu po wprowadzeniu zawsze przylegają do lewej strony komórki, wartości
liczbowe natomiast do prawej strony.
Formuły można budować z liczb lub adresów komórek w których znajdują się liczby.
Przykład:
Wprowadzając do komórki A1 formułę postaci =2+5 i zatwierdzeniu jej przyciskiem [Enter], otrzymamy
wynik działania czyli cyfrę 7.
Rys. 2. Formuła składająca się z samych liczb
To samo działanie można wykonać wprowadzając do formuły adresy komórek zamiast liczb. Wprowadzamy
do komórek A2 i A3 odpowiednio wartości 2 i 5. następnie wprowadzając do komórki A1 formułę postaci
=A2+A3 otrzymamy również wynik 7.
Rys. 3. Formuła składająca się z odwołań do wartości w innych komórkach
Uwaga: Aby wprowadzić do formuły adres komórki np. A2 nie trzeba wpisywać adresu za pomocą
klawiatury, wystarczy po wpisaniu znaku  = , używając klawiszy ze strzałkami na klawiaturze wskazać
wybraną komórkę lub też kliknąć lewym klawiszem myszy na daną komórkę w celu wprowadzenia jej
adresu do formuły.
Podstawowe działania i operatory matematyczne
Operatory matematyczne odpowiadają typom działań, które chcemy wykonać w komórkach arkusza
Tabela 1. Operatory matematyczne w formułach
Operator Działanie Przykład liczby Przykład adresy
+ dodawanie =7+7 =A1+A2
 odejmowanie =5 9 =A1 A2
* mnożenie =5*6 =A1*A3
/ dzielenie =6/2 =A1/A7
^ Potęgowanie =2^3 =A1^A2
Adresowanie komórek
W Excelu wykorzystywane są dwa sposoby adresowania komórek:
" Względny, czyli w odniesieniu do komórki znajdującej się w pewnej pozycji względem  Adresu
komórki wynikowej zawierającej formułę.
" Bezwzględny, czyli w odniesieniu do konkretnej  stałej komórki arkusza.
Adresy względne to np. B4, C7, A15, D22. Są one interpretowane jako opis  drogi od komórki aktywnej
(w której wpisywana jest formuła): komórka pierwsza z lewej, druga z góry, itd. Używa się ich we wzorach,
które uwzględniają różne wartości dla różnych pozycji obliczeń np. różna cena towaru dla różnych pozycji
cennika walutowego.
Adresy bezwzględne to np. $B$4, $C$7, $A$15, $D$22. Oznaczają one konkretną komórkę, niezależnie od
tego, jaka jest aktualna komórka. Używa się ich we wzorach, które uwzględniają stałą wartość dla różnych
pozycji obliczeń np. stały kurs dolara dla wszystkich pozycji cennika walutowego, stawka godzinowa, kwota
zasiłku rodzinnego itd.
Rozróżnienie adresowania względnego i bezwzględnego ma znaczenie przy pracy z seriami danych, kiedy
zachodzi potrzeba skopiowania formuły do kolejnego wiersza lub kolumny.
Aby skopiować formułę =A1+A2 wpisaną np. w komórce C3 do komórek C4, C5, C6, C7,C8,C9,C10 (czyli do
kilku wierszy poniżej) nie trzeba siedmiokrotnie używać opcji kopiuj/wklej. Wystarczy zaznaczyć komórkę
C3 i  złapać lewym przyciskiem myszy w prawym dolnym rogu aktywnej (jest to tzw. uchwyt wypełnienia),
i cały czas trzymając wciśnięty przycisk myszy przeciągnąć obramowanie na komórki poniżej. W taki sposób
zostanie przekopiowana formuła do nowych komórek.
Chwytamy  uchwyt wypełnienia
i przeciągamy w dół cały czas
trzymając wciśnięty lewy
przycisk myszy
Rys. 4. Kopiowanie poprzez przeciąganie
Została skopiowana formuła, a nie jej wynik. Ponieważ w tym przypadku było wykorzystane adresowanie
względne formuły w kolejnych wierszach zostały przeliczone i tak w komórce C4 będzie się znajdowała
formuła =A2+A3 i wartość 2. A jaka formuła będzie się znajdowała w komórce C10? A jaka wartość
i dlaczego taka?
Uwaga: Aby podejrzeć formułę znajdującą się w danej komórce wystarczy taką komórkę wskazać. W tym
momencie na pasku formuły zostanie wyświetlona formuła. Jeżeli chcemy edytować formułę (zawartość
komórki) wygodnie jest to zrobić naciskając przycisk F2 na klawiaturze. Spróbuj. Jest to najszybszy
i najwygodniejszy sposób edycji zawartości komórki. Polecamy!!!
Adresowanie względne
Pojęcie adresu względnego (względnego odwołania się do komórki) zostanie zaprezentowane w na
przykładzie skoroszytu Lista płac (znajduje się on w materiałach). Aby lepiej zrozumieć omawiane działania
zachęcamy do samodzielnego prześledzenia całej procedury.
1. Uaktywniamy komórkę H3 i wprowadzamy do niej wyrażenie =C3*E3, służące do obliczenia wypłaty
łącznie z premią.
2. Wskaz
nik komórki przesuwamy ponownie do H3 (jeżeli w czasie wprowadzania formuły został
przesunięty) i kopiujemy zawartość tej komórki do komórek od H3 do H10.
3. Uaktywniamy komórkę H10 i odczytujemy formułę z tej komórki na pasku formuły.
Rys. 5. Adresowanie względne
Formuła z
ródłowa w komórce H3 miała postać =C3*E3. Po jej skopiowaniu do komórek od H4 do H10
zmieniły się odwołania do komórek zawarte w tej formule; w komórce H4 przybrała ona postać =C4*E4,
w komórce H5 odpowiednio =C5*E5, itd. Jaka będzie formuła w komórce H10? Sprawdz
.
Dzieje się tak, ponieważ w pobranej z H3 formule =C3*E3 były użyte adresy względne. To oznacza, że jeżeli
na przykład kopiujemy formułę do komórki położoną o jeden wiersz niżej, czyli z H3 do H4 to Excel
automatycznie wie, że w komórce H4 względem komórki H3 musi do adresów komórek, do części adresu
komórek odpowiadającej numerowi wiersza dodać wartość 1. Stąd Z C3 zrobiło się C4, a z E3 zrobiło się E4.
Gdybyśmy chcieli skopiować zawartość komórki H3 (w której znajduje się formuła =C3*E3) w poziomie do
innej komórki, np. do J3, formuła ta w komórce J3 przybrałaby postać =E3*G3  czyli względem komórki H3
zmieniłyby się nazwy kolumn: C3 na E3, a E3 na G3. Czy wiesz dlaczego? Jak będzie wyglądała formuła w
komórce J6 jeżeli do tej komórki skopiowaliśmy formułę z komórki H3?
Adresowanie bezwzględne
Załóżmy, ze wszyscy pracownicy muszą zapłacić  Podatek stały którego wysokość dla wszystkich jest taka
sama i znajduje się w komórce B16 (Na potrzeby tego ćwiczenia wprowadziliśmy do komórki A16 tekst
 podatek , a do komórki B16 wartość 15%). Stworzymy teraz formułę w kolumnie J wyliczającą wartość
tego podatku dla każdego pracownika. Tworzymy formułę. Istnieje kilka rozwiązań:
1. Pierwszym rozwiązaniem jest wpisanie tej stawki bezpośrednio do odpowiednio zmodyfikowanej
formuły w komórce J3 i skopiowanie zawartości tej komórki do komórek położonych poniżej.
Formuła umieszczona w J3 wyglądałaby wówczas następująco: =C3*15%. Wadą tego rozwiązania
jest to, że w razie zmiany stawki trzeba będzie zmienić liczbę 15% we wszystkich formułach, we
wszystkich komórkach ją zawierających. Odradzamy stosowanie tego rozwiązania.
2. Spróbujmy stawkę za nadgodziny umieścić w oddzielnej komórce np. B16  gdy zmieni się stawka,
wystarczy wpisać inną liczbę do tej komórki. Excel sam automatycznie zaktualizuje wyniki. Formuła
umieszczona w J3 wyglądałaby wówczas następująco: =C3*B16. Po jej skopiowaniu do komórek od
J4 do J10 okazuje się jednak, że jest to rozumowanie błędne, gdyż przy kopiowaniu w dół wzrastają
numery wierszy we wszystkich adresach (w J3 będzie formuła: =C3*B16, w J4 pojawi się =C4*B17.
Dzieje się tak, ponieważ adresy względne zmieniają się przy kopiowaniu formuły. Zalecamy
dokładne sprawdzanie wyników po kopiowaniu formuł. Jeżeli w formule występuje odwołanie do
komórki, które pomimo kopiowania formuły powinno do niej pozostać trzeba koniecznie przed
kopiowaniem formuł zmodyfikować adres odwołania z względnego na bezwzględny. Pokażemy to
w punkcie poniżej.
3. Jedynym rozwiązaniem jest użycie adresów bezwzględnych (absolutnych). Usuń formuły z komórek
od J3 do J10. (zaznacz zakres komórek J3:J10 i naciśnij klawisz [Del]). Do komórki J3 wpisz formułę =
C2*$B$16 i naciśnij [Enter]. Skopiuj formułę do komórek od J4 do J10 posługując się uchwytem
wypełniania. Teraz w ramach testów do komórki B16 wpisz inną stawkę np. 40%  podatki
wszystkich pracowników zostaną automatycznie przeliczone. I o to właśnie chodziło. Dodanie
znaków  $ w adresie komórki B16 w formule (teraz wygląda tak $B$16) spowodowało, że od tej
chwili kopiowana formuła zawierająca taki zapis zachowa odwołanie do komórki B16 bez względu
na to czy będzie kopiowana w poziomie czy w pionie. Adres $B$16 stał się adresem bezwzględnym,
który nie zmienia się przy operacjach kopiowania i przesuwania.
Uwaga: Znaki $ przy adresowaniu można wstawiać za pomocą kombinacji klawiszy: [Shift] +[4] , lub
podczas wprowadzania formuły mając w formule wprowadzony adres komórki, który ma się stać adresem
bezwzględnym nacisnąć przycisk [F4].
Rys. 6. Adresowanie bezwzględne
Aby przećwiczyć kopiowanie komórek i lepiej zrozumieć wykonywanie działań w Excelu powtórz to
ćwiczenie samodzielnie w dokumencie Lista płac znajdującym się w udostępnionych materiałach.
Kolejność wykonywania działań w formułach
Ponieważ formuły mogą być bardzo rozbudowane, w Excel obowiązują zasady następujące zasady
wykonywania kolejności działań:
" działania wykonywane w nawiasach
" mnożenie dzielenie, potęgowanie
" dodawanie i odejmowanie
Przykłady działań:
=3+3*3+3 da wynik 15
=(3+3)*3+3 da wynik 21
=(3+3)*(3+3) da wynik 36
Sprawdzanie poprawności formuł (inspekcja formuł)
Excel posiada doskonałe narzędzia do sprawdzania odwołań w formułach. Narzędzia te znajdują się na
zakładce Formuły w sekcji Inspekcja formuł.
Rys. 7. Narzędzia do sprawdzania poprawności formuł
Narzędzia, które są najczęściej wykorzystywane to:
" Śledz
poprzedniki  umożliwia wskazanie strzałek, wskazujących komórki, które mają wpływ na
wartość aktualnie zaznaczonej komórki.
" Śledz
zależności  umożliwia pokazanie strzałek wskazujących komórki, na które ma wpływ wartość
aktualnie zaznaczonej komórki.
" Usuń strzałki  umożliwia usunięcie strzałek narysowanych przez funkcję Śledz
poprzedniki i Śledz

zależności.
" Pokaż formuły ([Ctrl] + [ ])  umożliwia wyświetlanie w każdej komórce formuły zamiast wartości
wynikowej.
Obejrzyj na poniższych rysunkach działanie narzędzi Śledz
poprzedniki, Śledz
zależności.
Rys. 8. Działanie narzędzia Śledz
poprzedniki
Rys. 9. Działanie narzędzia Śledz
zależności
Podsumowanie
W tej zapoznałeś się z tematyką związaną z pojęciem formuł, ich budowaniem i edycją, sposobami
adresowania (względne i bezwzględne). Wiesz również, jak sprawdzić poprawność formuł.