Funkcje Podstawy
Przykłady opisane w tej lekcji dostępne są w
arkuszu Excela: Funkcje
Podstawy.xlsx tylko ich samodzielne przerobienie daje gwarancję
zapamiętania tej lekcji
Przykłady dla wszystkich lekcji szkolenia
Excel 2013: ExcelSzkolenie.pl
Cwiczenia Excel 2013.zip
Ta lekcja może być
obejrzana lub przeczytana poniżej.
Film wygląda
najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można
zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika
która pojawi się w prawym dolnym
rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.
Funkcje w Excelu
uruchamiamy klikając na ikonę fx.
Istnieje kilka
sposobów, aby znaleźć interesującą nas funkcje, w oknie ‘Wyszukaj funkcję’
możemy wpisać nazwę funkcji (jeśli ją znamy) lub wpisać najlepiej jednym
słowem, to co funkcja powinna robić.
Możemy też wybrać
kategorię, w której może znajdować się interesująca nas funkcja, np. Finansowe
lub Matematyczne.
Klikając na nazwę
funkcji w oknie ‘Wybierz funkcję:’ pod listą nazw widzimy opis funkcji.
W wielu przypadkach
opis ten w pełni pozwala zrozumieć, do czego służy dana funkcja.
Po wybraniu funkcji,
(w poniższym przykładzie SUMA), wyświetlane jest okno ‘Argumenty funkcji’, po
wprowadzeniu adresów komórek do pierwszego z okienek, poniżej zostanie
wyświetlony aktualny wynik funkcji (o ile jest możliwe jego obliczenie), pod
nim znany już nam opis funkcji, a jeszcze niżej opis argumentu, który można
wprowadzić do pola ‘Liczba1’ i tego co funkcja z nimi zrobi.
Opisy najczęściej używanych funkcji
Poniżej przedstawiam
opisy często używanych funkcji z przykładami użycia zaczerpniętymi z
doświadczeń w korporacjach.
Lista opisanych funkcji
oczywiście nie wyczerpuje wszystkich możliwości Excela, ale tak jak wspominałem
wcześniej, dla większości funkcji sposób ich działania można zrozumieć z opisów
zawartych w Excelu. Funkcje bardziej skomplikowane zostały omówione w lekcjach:
‘Funkcje dla Zaawansowanych’, ‘Funkcje dla Ekspertów’ i ‘Funkcje Tablicowe’.
SUMA
(Arkusz: ‘SUMA’)
Prawdopodobnie
najczęściej używana funkcja Excela, oraz najprostsza w użyciu.
Można jej używać na
kilka sposobów, najszybszym jest ustawienie aktywnej komórki pod zakresem,
który ma być zsumowany i wybranie kombinacji lewy Alt i =.
Excel sam spróbuje
domyśleć się z jakiego zakresu ma zsumować dane.
Jeżeli zakres jest
niepoprawny należy go zmienić przeciągając za róg sugerowanego zakresu.
Dokładny opis tego procesu można znaleźć w lekcji ‘Wprowadzanie Formuł’.
Wiele osób będzie
jednak wolało zaznaczyć obszar, który ma być zsumowany i kliknąć ikonę sumy z
karty ‘NARZĘDZIA GŁÓWNE.
Ikona sumy jest też
dostępna na karcie ‘FORMUŁY’, to jedna i ta sama ikona dostępna w dwóch
miejscach.
UWAGA:
Nawet w przypadku tak
prostej funkcji wielu użytkownikom Excela zdarzają się błędy, najpopularniejsze
z nich polegają na ustawieniu aktywnej komórki na końcu sumowanych liczb i
wciśnięciu ikony ‘Autosumowania’ (lub klawisza skrótu
Alt + =), po zasugerowaniu przez Excela zakresu, użytkownicy, bez spojrzenia co
zostanie zsumowane, wciskają Enter i zakres sumy jest niewłaściwy.
Dzieje się tak
najczęściej w 2 przypadkach:
- kiedy brak jest
jednej z danych Excel zatrzyma się przy pierwszej pustej komórce.
- gdy w nagłówku
kolumny znajduje się liczba np. rok, także on zostanie zsumowany.
Znacznie więcej
możliwości daje funkcja SUMY.CZĘŚCIOWE,
opisana w dalszej części tej lekcji.
ŚREDNIA
(Arkusz: ‘ŚREDNIA’)
Funkcja oblicza średnią
dla wprowadzonych liczb, bądź zakresów.
Wszystkie funkcję
można też wprowadzać wpisując w komórkę znak = i rozpoczynając pisanie nazwy
funkcji.
Już po pierwszej
literze Excel proponuje funkcje zaczynające się na taką literę, aby wybrać
jedną z nich dwukrotnie klikamy lewym klawiszem na jej nazwie.
Po zaznaczeniu myszką
zakresu, którego funkcja ma dotyczyć, nie musimy wpisywać zamykającego go
nawiasu, od razu możemy potwierdzić całość Enterem.
UWAGA:
Funkcja ta podobnie
jak wszystkie inne funkcje Excela inaczej traktuje pustą komórkę lub tekst a inaczej
liczbę zero, co dobrze ilustruje poniższy przykład w którym średnia wyliczana
jest trzy razy dla tych samych danych i podaje różne wyniki.
W pierwszej z tabel
średnia liczona jest tylko dla 6 elementów tabeli. Gdybyśmy chcieli obliczyć
ile wynosi średnia sprzedaż na produkt dla poniższych 7 produktów, w pustą
komórkę należy wprowadzić zero. Tekst tak samo jak puste komórki jest pomijany
w kalkulacjach.
Zaletą tej funkcji
jest to, że można do niej wprowadzać adresy pojedynczych komórek i jednocześnie
całe zakresy.
Średnia dostępna jest
też w pasku stanu. Więcej informacji na ten temat w lekcji: Pasek Stanu.
Do obliczania
średniej ważonej najprościej wykorzystywać funkcję SUMA.ILOCZYNÓW, a do
obliczania średniej geometrycznej funkcję ŚREDNIA.GEOMETRYCZNA
Funkcja JEŻELI
(Arkusz: ‘JEŻELI’)
W poniższym
przykładzie w kolumnie E chcielibyśmy uzyskać wzrost sprzedaży dla długiej listy
produktów. Ponieważ część produktów nie była sprzedawana w 2009 roku zwykła
formuła dzielenia dałaby w ich przypadku wynik ‘#DZIEL/0!’, który nie wygląda
zbyt profesjonalnie, dlatego wolelibyśmy mieć w takich przypadkach wprowadzony
znak ‘-----‘.
Proponuje wykasować
tą formułę i ustawić jako aktywną komórkę E4, po czym wybrać ikonę funkcji.
W okienku ‘Wyszukaj
funkcję’ wpisujemy ‘jeżeli’ i
zatwierdzamy enterem.
Po wybraniu funkcji
JEŻELI i kliknięciu OK na powyżej przedstawionym oknie ‘Wstawianie funkcji’,
zostanie wyświetlone poniżej pokazane okno ‘Argumenty funkcji’.
W okienku ‘Test_logiczny’ wprowadzamy C4>0, czyli jeżeli sprzedaż w
2013 była większa od zera to…
W okienku ‘Wartość_jeżeli_prawda’ wprowadzamy formułę na wzrost
procentowy, który można wyliczyć TYLKO wtedy gdy sprzedaż w 2013 była większa
od zera.
W okienku ‘Wartość_jeżeli_fałsz’ wprowadzamy -----, symbol ten ma być
wyświetlany gdy nie jest możliwe obliczenie procentowego wzrostu.
Formułę zatwierdzamy
kliknięciem w przycisk OK, po czym kopiujemy ją z komórki E4 do poniższych
komórek tabeli.
Po skopiowaniu
formuły do całej kolumny powinniśmy uzyskać poniższy wygląd tabeli.
ZAOKR
(Arkusz: ‘ZAOKR’)
W Excelu jest wiele
funkcji służących do zaokrąglania, funkcja ZAOKR() jest najczęściej używaną z
nich.
Ma 2 argumenty:
- liczbę, którą będziemy
zaokrąglać (lub adres komórki w której ta liczba się znajduje)
- ilość cyfr która ma
być pokazana po przecinku (lub adres komórki w której ilość ta jest wpisana)
W poniższym
przykładzie w komórce C5 liczba 1,23456789 została zaokrąglona do 2 miejsca po
przecinku czyli do liczby 1,23.
Cyfry od 1 do 4 są
zaokrąglane w dół, od 5 do 9 w górę, co łatwo zauważyć przeglądając wyniki
powyższego przykładu.
MAX, MIN
(Arkusz: ‘MAX MIN’)
Funkcje wybierają
najmniejszą (MIN) lub największą liczbę (MAX) z podanego zakresu bądź zakresów.
Tekst i puste komórki
nie są brane pod uwagę.
W poniższym
przykładzie komórka C9 jest pusta, czyli jej wartość to 0, jednak jako
najmniejsza wartość wybrana została komórka C13.
ILE.NIEPUSTYCH
Poniżej prezentuje
zaczerpnięty z praktyki biznesowej przykład użycia funkcji =ILE.NIEPUSTYCH. W
wierszu ‘15’ znajdują się formuły wykorzystujące tą funkcję i zliczające ile
było klientów którym przyznano rabat w każdym z miesięcy, a w kolumnie ‘J’ w
ilu miesiącach przyznano rabat danemu klientowi.
W komórce J1 musi być
suma aby pokazać ilość rabatów dla wszystkich klientów we wszystkich
miesiącach.
DZIŚ()
Funkcja zwraca dzisiejszą dane, jest używana
głównie w różnego rodzaju formularzach, oraz w logistyce np. do obliczania ile
dni zostało od daty dostawy/transportu do dziś.
Funkcja ta, jak mało która, nie posiada
argumentów.
Jej poprawny wynik zależy od prawidłowo
ustawionej daty (i godziny) w systemie Windows.
Funkcje Logiczne:
LUB, ORAZ
(Arkusz: ‘ORAZ LUB’)
Funkcja logiczne rzadko
są używane samodzielnie, znacznie częściej w połączeniu z innymi funkcjami, o
łączeniu funkcji możesz przeczytać w dalszej części tej lekcji.
ORAZ
Funkcja zwraca
wartości logiczne ‘PRAWDA’/‘FAŁSZ’, wartość ‘PRAWDA’, jeżeli wszystkie warunki zostały
spełnione, w przeciwnym razie ‘FAŁSZ’.
W poniższym
przykładzie w kolumnie F uzyskaliśmy wartość prawda dla miesięcy, w których
sprzedaż we wszystkich oddziałach była poniżej 50.
LUB
W poniższym
przykładzie w kolumnie F przy użyciu funkcji LUB sprawdzamy czy w danym
miesiącu sprzedaż w którymkolwiek z oddziałów przekroczyła wartość 100. Funkcja
zwraca wartości logiczne: ‘PRAWDA’/’FAŁSZ’, ‘PRAWDĘ’ jeśli choć jeden z warunków jest spełniony, gdy żaden z nich nie
jest spełniony ‘FAŁSZ’.
ZAGNIEŻDŻANIE FUNKCJI
(Arkusz: ‘Zagnieżdżanie funkcji’)
Dość często występuje
potrzeba użycia kilku funkcji na raz, co jest nazywane także zagnieżdżaniem
funkcji.
W poniższym
przykładzie chcielibyśmy na podstawie dużej ilości imion ustalić płeć osób
noszących te imiona. Zdecydowana większość imion kobiet używanych w Polsce
kończy się na literę „a”, litera ta nie występuje natomiast na końcu imion
męskich.
Poniżej rozwiązanie
tego problemu przy użyciu funkcji ‘JEŻELI’ i funkcji tekstowej ‘PRAWY’.
Funkcję JEŻELI,
poznaliśmy już w tej lekcji, zacznijmy więc od funkcji PRAWY, wycina ona
określoną ilość liter od prawej strony tekstu.
W poniższym
przykładzie wycina 1 literę z prawej strony tekstu znajdującego się w komórce
B3. (Ostatnia litera z tekstu ‘Agata’ to „a”.)
W komórce D3 zgodnie
z poniższym rysunkiem wprowadzamy funkcję JEŻELI, która gdy w komórce C3
znajduje się litera „a” daje rezultat: „Kobieta”, a przeciwnym wypadku „Facet”.
Rozwiązaliśmy już
dane nam zadanie, ale postarajmy się to zrobić przy użyciu jednej a nie dwóch
komórek.
Wchodzimy w komórkę
C3 i zaznaczamy znajdującą się w niej formułę pomijając znak równości.
Kopiujemy zaznaczoną
formułę (wciskając Ctrl+C), po czym wychodzimy z
komórki wciskając klawisz Esc.
Wchodzimy do komórki D3,
gdzie zaznaczamy adres C3 i wklejamy skopiowaną wcześniej formułę w jego
miejsce (adres zostanie automatycznie usunięty).
W ten sposób
uzyskaliśmy funkcję zagnieżdżoną. Kolumna C nie jest już dłużej nam potrzebna.
Po skopiowaniu
formuły do poniższych wierszy i skasowaniu kolumny C z ostatnimi literami
powinniśmy uzyskać widok taki jak poniżej.
Zaawansowani
użytkownicy Excela od razu wprowadzają wiele zagnieżdżonych funkcji,
zaprezentowany powyżej sposób jest najbardziej użyteczny dla początkujących i
pozwala zrozumieć logikę łączenia funkcji.
SUMA.ILOCZYNÓW
(Arkusz ‘SUMA.ILOCZYNÓW’)
W poniższym przykładzie mamy dane wartości sprzedaży i marży procentowej
dla 4 krajów, obliczyliśmy już sumę sprzedaży, teraz chcemy obliczyć marżę procentową
dla sumy tych krajów. Oczywiście użycie funkcji ŚREDNIA nie dałoby właściwego
rozwiązania ze względu na to że sprzedaż w krajach ma różne wielkości i kraje
te w rożny sposób wpływają na marżę całościową.
W tym przykładzie
moglibyśmy mozolnie wprowadzić formułę przemnażającą sprzedaż z każdego kraju
przez marżę w kraju tym uzyskaną i podzielić to przez sumę sprzedaży, formuła
taka jest pokazana w komórce I4.
Dzięki sumie
iloczynów formuła ta zostanie wprowadzona szybciej, ograniczymy ryzyko błędu, a
w przypadku dodania kolejnego kraju nie będzie konieczności dokonywania zmian w
formule.
Stajemy w komórce G4,
wybieramy funkcję SUMA.ILOCZYNÓW i wprowadzamy adresy tak jak na poniższym
rysunku.
Aby uzyskać średnią ważoną
wystarczy na końcu formuły dopisać dzielenie przez sumę sprzedaży znajdującą
się w komórce G5.
Właściwy wynik to
32%, taki sam jak uzyskaliśmy korzystając z formuły.
Na koniec dodajmy
jeszcze kolumnę pomiędzy Polską a Rosją wprowadzając do niej nowe dane.
Średnia ważona marża
w H4 aktualizowana jest na bieżąco. Formuła wpisana ręcznie wymaga poprawienia.
SZUKAJ.TEKST
FRAGMENT.TEKSTU
(Arkusz ‘Funkcje Tekstowe’)
W poniższym
przykładzie przedstawiam fragment Price Survey (lista z cenami konkurencji), jaki pewna firma
otrzymała od agencji badawczej. Niestety pojemności produktów były częścią
nazwy, co znacznie utrudniło porównywanie cen produktów. Ponieważ produktów objętych
badaniem było kilka tysięcy zdecydowano się użyć funkcji tekstowych, aby
wyodrębnić pojemność.
Użyto dwóch funkcji
tekstowych najpierw użyto funkcję SZUKAJ.TEKST() - wyszukiwany jest tekst ml,
który następuje po pojemności danego produktu, wynikiem działania na funkcję
SZUKAJ.TEKST(), nałożona jest funkcja FRAGMENT.TEKSTU(), która wycina 3 znaki z
nazwy produktu znajdujące się o 3 w lewo od miejsca w którym został znaleziony
tekst ‘ml’.
Wynikiem działania
powyżej opisanych funkcji jest nie liczba lecz tekst, który w przypadku
produktów o pojemności poniżej 100ml zaczyna się od spacji.
Aby móc dokonywać operacji
matematycznych na pojemności, należy całą kolumnę C zamienić na wartości
(skopiować i wkleić w to samo miejsce wartości), po czym korzystając z operacji
zamień (Ctrl+H) zamienić znak spacja na nic (niczego
nie wpisując w okienko „Zamień na:”). Aby uzyskane tak cyfry miały format
liczbowy ostatnią operacją będzie przemnożenie ich przez 1, używając opcji ‘przemnóż’
polecenia ‘Wklej specjalnie’.
CZY.BŁĄD
(Arkusz ‘CZY.BŁĄD’)
Funkcja ta jest z
reguły używana jako funkcja ‘wewnętrzna’ innych funkcji tak, aby gdy jeden z
argumentów funkcji głównej będzie miał niewłaściwy format lub okaże się błędem,
funkcja ta pomijała go zamiast wskazywać błąd.
Najprostszym
przykładem będzie użycie ten funkcji w dodawaniu komórek i w połączeniu z
funkcją JEŻELI.
SUMY.CZĘŚCIOWE
(Arkusz: ‘SUMY.CZĘŚCIOWE)
W poniższym
przykładzie prezentuje porównanie wykorzystania funkcji SUMY.CZĘŚCIOWE i funkcji SUMA oraz ich
połączenia.
W kolumnie C używam
funkcji SUMA dla regionów, a także dla sumy końcowej opisanej jako TOTAL. W tym
przypadku obliczany jest błędny wynik ponieważ oprócz sprzedaży dla klientów sumowane
są także sumy dla regionów, właściwym rozwiązaniem dla tego typu problemu
byłoby zsumowanie komórek sum dla regionów (odpowiednia formuła znajduje się w
komórce C27).
W kolumnie D
prezentuję użycie funkcji SUMY.CZĘŚCIOWE,
zarówno w sumach dla regionów, jak i Total. Wykorzystanie tej funkcji da nam
pewność że sumy dla regionów (sumy wewnętrzne) będą pomijane. Wynik jest
poprawny.
W kolumnie E używam
funkcji SUMY.CZĘŚCIOWE w wariancie,
który pomija ukryte wiersze. Ponieważ wiersz 4 jest ukryty, wynik nie będzie
poprawny, chyba że zależało nam właśnie na pominięciu wartości w ukrytych
komórkach.
W ostatnim przykładzie pokazuję, że funkcja SUMY.CZĘŚCIOWE pomija jedynie
inne SUMY.CZĘŚCIOWE, sumuje natomiast zwykłą funkcję SUMA. Wynik jest błędny.
=SUMY.CZĘŚCIOWE(9;D3:D24)
W składni funkcji pierwsza cyfra jest oznaczeniem funkcji jaka ma być
wykorzystana, dla cyfry ‘9’ lub ‘109’ będzie to suma, ale są także inne
możliwości, przedstawiam je w tabeli poniżej.
Drugim argumentem jest obszar który ma być sumowany.
|
Funkcja
nr |
Funkcja_nr |
Funkcja |
|
1 |
101 |
ŚREDNIA |
|
2 |
102 |
ILE.LICZB |
|
3 |
103 |
ILE.NIEPUSTYCH |
|
4 |
104 |
MAKSIMUM |
|
5 |
105 |
MINIMUM |
|
6 |
106 |
ILCZYN |
|
7 |
107 |
ODCH.STANDARDOWE |
|
8 |
108 |
ODCH.STANDARD.POPUL |
|
9 |
109 |
SUMA |
|
10 |
110 |
WARIANCJA |
|
11 |
111 |
WARIANCJA.POPUL |
MODUŁ.LICZBY
(Arkusz ‘MODUŁ.LICZBY’)
Moduł liczby to funkcja
używana w matematyce, nazywana jest także wartością bezwzględną. Funkcja ta ma
jeden argument, który może być tylko liczbą (lub adresem, lub inną funkcją
dającą w wyniku liczbę). Funkcja ta dla liczb dodatnich nie zmienia ich
wartości a dla liczb ujemnych zmienia znak na dodatni.
Przykład:
MODUŁ.LICZBY(-5)=5
Jest to jedna z wielu
funkcji w Excelu, które można łatwo zastąpić używając prostych symboli
matematycznych. W tym przypadku potęgowania. Najpierw należy podnieść liczbę do
potęgi 2 (lub innej parzystej) a potem wynik pierwiastkować pierwiastkiem 2
stopnia (lub innego takiego samego jak potęga).
(-5)^2^(1/2)=5
LOS()
Funkcja losuje wartość z przedziału od 0 do
1. Ponieważ losowanie odbywa się od nowa przy każdym przeliczeniu arkusza,
najlepiej jest od razu zamienić ją na wartości, w przeciwnym razie losowanie
nowych wartości przy każdej zmianie danych w pliku, może znacznie spowolnić
działanie komputera. Często wynik tej funkcji przemnaża się je lub dzieli aby
uzyskać rząd wielkości liczb który nas interesuje np. =los()*1000 lub
=0,5+los()/10
Funkcja bywa używana najczęściej do tworzenia
różnorodnych danych, które wyglądają realistycznie, większość danych w tym
szkoleniu powstała przy użyciu tej funkcji. Często też w dyskusjach w wielkich
korporacjach niektóre osoby sugerują tą funkcję jako możliwe źródło danych,
które otrzymały od innych działów, krajów lub partnerów biznesowych.
Więcej o funkcjach Excela w lekcjach:
‘Funkcje dla Zaawansowanych’ i ‘Funkcje dla Ekspertów’.