Funkcje dla Zaawansowanych
Przykłady opisane w tej lekcji dostępne są w
arkuszu Excela: Funkcje dla
Zaawansowanych.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ę.
Przykład 1.
Funkcje Tekstowe
(Arkusz
‘Funkcje Tekstowe’)
PRAWY LEWY
Dość
często spotykanym problemem jest zły format daty, którego Excel nie potrafi
zrozumieć jako daty nawet jeśli sformatujemy komórki jako datę.
Jeśli
data zaczyna się od dnia a nie roku, jedynym sposobem automatycznej zamiany jej
do postaci zrozumiałej dla Excela jest użycie funkcji tekstowych.
Za
pomocą funkcji PRAWY wycinamy rok. W tym przypadku funkcja wycina 4 znaki od
prawej strony.
Do wycięcia dnia użyjemy funkcji LEWY.
A do wycięcia miesiąca funkcji FRAGMENT.TEKSTU
(opisanej w lekcji ‘Funkcje Podstawy’).
Po rozdzieleniu daty na jej elementy składowe,
możemy je złączyć używając znaku & i dodatkowo między elementami wstawiając
myślniki.
Aby sprawdzić czy Excel na pewno traktuje daną
jako datę można do niej dodać dowolną liczbę i sprawdzić czy data zmienia się
poprawnie.
Łącząc funkcje z kolumn od C do F, uzyskamy ten
sam wynik używając tylko jednej formuły.
(Prosty sposób łączenia funkcji został opisany
w lekcji ‘Funkcje Podstawy’.)
Po skopiowaniu funkcji do pozostałych komórek
powinniśmy uzyskać wynik taki jak poniżej.
SZUKAJ.TEKST
DŁ
Innym często spotykanym problemem jest podział komórek
zawierających Imię i Nazwisko na dwie komórki w których dane te będą osobno. Za
pomocą funkcji SZUKAJ.TEKST możemy określić na którym miejscu jest spacja
Używając funkcji DŁ, określimy ile jest znaków
w komórce B18.
Korzystając z funkcji LEWY wycinamy imię.
Korzystając z funkcji PRAWY wycinamy nazwisko.
Alternatywnym rozwiązaniem będzie skorzystanie
z funkcji FRAGMENT.TEKSTU
Wycinając nazwisko możemy podać dokładną liczbę
znaków nazwiska lub podać dowolną dużą liczbę, co zostanie odczytane jako
wytnij do końca tekstu.
Poniżej wyniki jakie powinniśmy uzyskać.
Ten sam efekt możemy uzyskać szybciej nie
używając funkcji tekstowych ale ‘kreatora konwersji tekstu na kolumny’.
Zaczniemy od zaznaczenia danych które chcemy
rozdzielić i wybrania z Menu ‘Dane’ polecenia ‘Tekst jako kolumny…’.
Na ekranie który zostanie wyświetlony wybieramy
‘Dalej >’.
Na kolejnym ekranie wybieramy Spację i klikamy
‘Dalej >’.
W ostatnim kroku kreatora wybieramy miejsce
docelowe gdzie mają pojawić się przekonwertowane dane i klikamy ‘Zakończ’.
Podział przeprowadzony jest poprawnie.
Kreator konwersji nie poradzi sobie natomiast z
nieco bardziej skomplikowanym przypadkiem, gdy niektóre osoby mają wpisane
także drugie imię.
Przy pomocy funkcji tekstowych rozwiążemy ten
problem dość szybko.
Zacznijmy od wyznaczenia tego na którym miejscu
znajduje się pierwsza i druga spacja.
Posłużymy się funkcja SZUKAJ.TEKST, dla
pierwszej spacji formuła będzie taka sama jak używana w poprzednim przykładzie.
Dla wyznaczenia miejsca drugiej spacji dodatkowo wprowadzimy ‘Liczbę_początkową’, która będzie pozycją pierwszej spacji
plus jeden.
Dla osób, które nie mają drugiego imienia wynik
formuły jest błędem.
W kolejnej kolumnie używając funkcji CZY.BŁĄD
(lub CZY.LICZBA) ustalimy które osoby mają tylko 1 imię.
Pierwsze imię ustalimy tak samo jak w
poprzednim przykładzie.
Drugie przy pomocy funkcji JEŻELI, która
skorzysta z informacji o ilości imion z kolumny E, jeśli jest tylko jedno
komórka będzie pusta (wprowadzenie podwójnej spacji ””). Jeśli natomiast drugie
imię istnieje, zostanie wycięte przy pomocy funkcji FRAGMENT.TEKSTU, jego
początek to pozycja 1 spacji +
Błędem byłoby wycięcie któregokolwiek imienia
bądź też nazwiska wraz ze znakiem spacji.
Nazwisko wycinamy ponownie posługując się
funkcją FRAGMENT.TEKSTU, do ustalenia na której pozycji znajduje się pierwsza
litera nazwiska posłużymy się funkcją JEŻELI.
Wynikiem wszystkich operacji będzie tabela taka
jak przedstawiona poniżej.
Formuły w kolumnach od C do E możemy na koniec włączyć
w formuły znajdujące się w F do H a kolumny robocze skasować.
Istnieje wiele innych sposobów rozwiązania
powyżej przedstawionych problemów, wybrałem akurat takie ze względu na to, że
dają możliwość dobrego przećwiczenia użycia funkcji tekstowych.
Przykład
2.
POZYCJA()
(Arkusz: ‘POZYCJA’)
W poniższej tabeli chcielibyśmy szybko ustalić
którzy przedstawiciele osiągnęli najwyższą sprzedaż a którzy najniższą.
Pracownicy w tabeli są ustawieni wg miast i kolejności tej nie można zmieniać,
nie możemy więc ich posortować wg sprzedaży.
Posłużymy się funkcją POZYCJA, która ustala
pozycję danej wartości na wskazanej liście.
W adresach listy konieczne jest dodanie znaków
$ aby lista nie przesuwała się podczas kopiowania.
Po skopiowaniu formuły do poniższych komórek i
dodaniu formatowania warunkowe najlepsi sprzedawcy są od razu wyraźnie
widoczni.
Przykład 3.
SUMA.JEŻELI
(Arkusz z danymi: ‘SUMA.JEŻELI’)
(Arkusz z rozwiązaniem: ‘SUMA.JEŻELI 2’)
W przykładzie
przedstawionym na poniższym rysunku w komórkach L3:L6 sumujemy wydatki w
podziale na kategorie.
Argumenty funkcji
SUMA.JEŻELI to:
‘Zakres’ – czyli co
ma być porównywane z kryterium sumowania, inaczej mówiąc gdzie funkcja ma
szukać.
‘Kryteria’ – czyli
czego funkcja ma szukać
‘Suma_zakres’
– dane które funkcja będzie sumować jeżeli w tej samej linijce ‘Zakresu’
zawartość komórki jest taka sama jak zawartość komórki ‘Kryteria’.
W pola Zakres i Suma_zakres często wygodnie jest wprowadzać całe kolumny
gdyż łatwiej wtedy będzie kopiować tą formułę do komórek niżej.
Należy tego unikać
jeżeli plik jest bardzo duży i wolno się przelicza, ponieważ może go to
dodatkowo spowolnić.
W poniższym
przykładzie dodatkowo dodano odpowiednie adresowanie absolutne, aby formułę
można było skopiować do całej tabeli z sumami dla krajów i kategorii.
Po skopiowaniu
powinniśmy uzyskać dane takie jak przedstawione poniżej.
Przykład 4.
SUMA.JEŻELI
(Arkusz z danymi: ‘SUMA.JEŻELI’)
(Arkusz z rozwiązaniem: ‘SUMA.JEŻELI 2’)
Na podstawie danych
używanych w poprzednim przykładzie, wypełnimy tabelę sumującą wydatki z podziałem
na kategorie i miesiące jednocześnie.
Ponieważ zarówno
miesiące jak i kategorie znajdują się w kolumnach, te dwa kryteria połączymy.
Aby sumowanie przy
użyciu funkcji Suma.Jeżeli mogło działać dla 2 lub
większej ilości kryteriów należy je połączyć w jedno używając znaku &.
Kryterium zawierające
miesiąc i kategorię umieszczono w kolumnie B.
Funkcja SUMA.JEŻELI
wprowadzona w komórce L9, szuka komórek spełniających kryterium w kolumnie
‘$B:$B’ (wprowadzone w polu Zakres).
Kryteria zostały
zapisane jako połączenie nazwy miesiąca z nazwą kategorii, tu także użyto znaku
&.
Suma_zakres to kolumna I, dane z tej kolumny będą sumowane
jeśli zgadzać się będzie zarówno miesiąc jak i kategoria.
Proszę o zwrócenie
uwagi na znaki $, dzięki którym adresy we wszystkich polach zostały zamienione
na adresy bezwzględne, co umożliwiło skopiowanie raz wprowadzonej funkcji do
wszystkich pól tabeli wynikowej bez konieczności ręcznego wprowadzania zmian.
Po skopiowaniu
powyżej pokazanej formuły do pozostałych komórek powinniśmy uzyskać dane takie
jak przedstawione poniżej.
Rzeczywiste tabele
opisujące procesy biznesowe są z reguły znacznie większe i przygotowanie
formuły tak aby nie trzeba było wprowadzać zmian ręcznie jest niezbędne.
W skomplikowanych
przypadkach dobrą alternatywą dla funkcji SUMA.JEŻELI jest funkcja
SUMA.WARUNKÓW, która sumuje dane spełniające wiele warunków dla danych znajdujących
się we wskazanych kolumnach.
Funkcja została
opisana w lekcji ‘Funkcje dla Ekspertów’.
Przykład 5.
Funkcje Finansowe NPV,
IRR, CAGR
(Arkusz: ‘Funkcje Finansowe’)
NPV
Funkcja NPV służy do obliczania Wartości
Bieżącej Netto inwestycji czyli mówiąc wprost: ile da się na tym zarobić.
W poniższym przykładzie na przestrzeni lat
2010-2012 dokonujemy inwestycji, która będzie nam przynosić przychody od 2012
do 2030.
Stopa dyskontowa w całym okresie wynosi 5%.
Zakładamy że wszystkie wydatki i przypływy zachodzą na koniec okresu.
Gdybyśmy chcieli wyliczyć wartość na dzień
dzisiejszy (zakładamy 1 stycznia 2010) wydatków moglibyśmy skorzystać z
poniższego wzoru:
=F7/(1+C5)+G7/(1+C5)^2+H7/(1+C5)^3
(Symbol ^ oznacza podniesienie do potęgi i
można go wpisać z klawiatury przytrzymując Shift i
wciskając cyfrę 6.)
Każdy z wydatków dzielimy przez 1+stopa
dyskontowa aby wyliczyć jego wartość na dziś (wartość pieniądza w czasie jest
zmienna). Wydatek z 2011 roku dzielimy dwukrotnie stąd potęga ^2, a wartość z
2012 trzykrotnie (trzecia potęga czyli ^3).
Powyżej opisany wzór został użyty w komórce C7.
W komórce C8 wykorzystano funkcję NPV, dzięki
której uzyskano dokładnie ten sam wynik.
W przypadku obliczania bieżącej wartości wydatków
możliwe było wprowadzenie wzoru na NPV ponieważ wydatki są ponoszone tylko w 3
okresach i wzór nie był zbyt długi. Gdybyśmy chcieli tak samo wyliczyć bieżącą
wartość przypływów musielibyśmy wykazać się dużą cierpliwością, a ryzyko błędu
byłoby bardzo duże.
W komórce C10 użyto funkcji NPV do obliczenia
bieżącej wartości przypływów. Warto podkreślić, że w komórkach dla których
przypływy nie występują (F8 i G8) muszą być wpisane zera, w przeciwnym razie
funkcja pominie te komórki i wynik będzie niepoprawny.
Aby obliczyć bieżącą wartość przewidywanego
zysk na tym projekcie wystarczy odjąć od bieżącej wartości przypływów (C10)
bieżącą wartość wydatków (C8). Taka formuła zapisana jest w komórce C12.
Zysk możemy także wyliczyć bezpośrednio
korzystając z danych znajdujących się w tabeli w wierszu 9, gdzie od przypływów
odjęto wydatki.
Funkcja ta została zapisana w komórce C13.
IRR
Funkcja IRR oblicza wewnętrzną stopę zwrotu,
która może być rozumiana jako graniczna wielkość oprocentowania przy której
zysk na danym projekcie wynosi 0.
Jeżeli stopa dyskontowa jest niższa niż IRR
zysk jest dodatni, jeżeli wyższa projekt przyniesie stratę. Można ją także
rozumieć jako najwyższe oprocentowanie kredytu jakie możemy zaakceptować aby
projekt nie przyniósł strat.
Funkcja IRR dla tego przykładu wykorzystana
jest w komórce C18.
Dla sprawdzenia poprawności obliczeń możemy
wynik funkcji IRR wpisać w komórce C5 (wprowadzamy z klawiatury 8,886%), zysk
dla tego projektu będzie wynosił zero (komórki C12 i C13).
Funkcje finansowe PV FV zostały opisane w lekcji Funkcje dla
Ekspertów.
CAGR
CAGR to średni roczny wzrost/spadek obliczany
dla okresu określonej liczby lat. W Excelu nie ma funkcji która by go
obliczała, warto więc zapamiętać formułę dzięki której możemy to zrobić.
W poniższym przykładzie sprzedaż w 2010 roku
wynosiła 10,0 a w 2019 przewidywana jest sprzedaż wynosząca 23,6, chcielibyśmy policzyć
ile wynosi średni roczny wzrost który pozwoliłby uzyskać taką sprzedaż.
Skorzystamy z następującego wzoru:
Średni wzrost = (Wartość Końcowa / Wartość
Początkowa) ^ (1/(ilość okresów -1))-1
W poniższym przykładzie:
Wartość Końcowa
=23,6
(wartość w roku 2019)
Wartość Początkowa
= 10 (wartość w roku 2010)
ilość okresów
= 10
(w tabeli pokazane są
dane dla 10 lat )
W poniższym przykładzie formuła będzie wyglądać
następująco:
Średni wzrost =(O27/F27)^(1/9)-1
Przykład 6.
WYBIERZ
(Arkusz: ‘WYBIERZ)
Działanie funkcji WYBIERZ jest bardzo proste, spośród
listy wartości, które należy wprowadzić oddzielnie, wybiera tą, której numer
pojawi się w pozycji Nr_arg.
W poniższym przykładzie wprowadzono 5 wysokości
rabatów, które zależą od ‘klasy klienta’ przyjmującej wartości od 1 do 5.
Po wprowadzeniu cyfry do komórki B4 rabat
zostaje wyszukany i podany w komórce C4.
Funkcja ta jest podobna w swym działaniu do
funkcji WYSZUKAJ.PIONOWO, choć oferuje mniej możliwości.
Przykład 7.
WYST.NAJCZĘŚCIEJ.WART czyli DOMINANTA
MEDIANA
(Arkusz: ‘Mediana Dominanta’)
Tak powszechnie używana średnia ma wielu przeciwników, którzy twierdzą że
w rzeczywistości niewiele mówi o zbiorze lub też zafałszowuje informacje. A
mówiąc bardziej obrazowo, to jeśli pójdę z psem na spacer to średnio mamy po 3
nogi.
Dlatego w statystyce powstało kilka innych miar opisujących zbiory liczb,
te najczęściej spotykane to mediana i dominanta.
Mediana to liczba, która w szeregu uporządkowanym jest w połowie szeregu w
wypadku nieparzystej liczby elementów. Dla parzystej liczby elementów – średnia
dwóch środkowych liczb.
Dominanta to wartość najczęściej występująca w próbie.
Medianę wyliczamy funkcją o tej samej nazwie.
Dominantę obliczamy funkcją =WYST.NAJCZĘŚCIEJ.WART.
Funkcja ta w przypadku gdy kilka elementów występuje po tyle samo razy
podaje pierwszy z nich.
W poniższym przykładzie mamy dwie dwójki i dwie szóstki, dominanta
przyjmuje wartość 2.
