Funkcje Dla Zaawansowanych

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Funkcje Dla Zaawansowanych.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Przykład 1.

(Arkusze: ‘SUMA.JEŻELI 1’, ‘SUMA.JEŻELI 2’)

SUMA.JEŻELI

Poniżej zamieszczam dwa przykłady użycia funkcji SUMA.JEŻELI.

W pierwszym przedstawionym na poniższym rysunku w komórkach L3:L5 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óra funkcja będzie sumować jeżeli w tej samej linijce Zakresu wartość komórki jest zgodna z wartością 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ć.

Przykład 2.

(Arkusze: ‘SUMA.JEŻELI 1’, ‘SUMA.JEŻELI 2’)

W drugim przykładzie przygotowano tabelę sumującą wydatki z podziałem na kategorie i miesiące jednocześnie.

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ć, zostało to zrobione w kolumnie ‘B’. Na poniższym rysunku jest to widoczne, aby łatwiej było zrozumieć działanie funkcji, w rzeczywistym raporcie kolumnę taką można by ukryć, lub zmienić kolor czcionki na biały (lub inny kolor tła).

Funkcja szuka komórek spełniających kryteria (odpowiednia kategoria i miesiąc) w kolumnie ‘$B:$B’ (wprowadzone w polu Zakres).

Proszę o dokładne zwrócenie uwagi na znaki $, dzięki którym adresy we wszystkich polach zostały zamienione na adresy bezwzględne i ‘częściowo’ bezwzględne, co umożliwiło przegranie raz wprowadzonej funkcji do wszystkich pól tabeli wynikowej bez konieczności ręcznego wprowadzania zmian.

Rzeczywiste tabele opisujące procesy biznesowe są z reguły znacznie większe i przygotowywanie formuł tylko raz jest niezbędne.

Funkcja SUMA.JEŻELI daje możliwość sumowania danych spełniających jedno kryterium, jeśli dane mają spełniać jedno z wielu kryteriów należy posłużyć się funkcjami bazy danych omówionymi na końcu tej lekcji.

Przykład 3.

SUMY.POŚREDNIE

(Arkusz: ‘SUMY.POŚREDNIE’)

W poniższym przykładzie prezentuję porównanie wykorzystania funkcji SUMY.POŚREDNIE 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 pośrednie sumy, właściwym rozwiązaniem dla tego typu problemu byłoby zsumowanie komórek sum pośrednich (odpowiednia formuła znajduje się w komórce C27).

W kolumnie D prezentuję użycie funkcji SUMY.POŚREDNIE, 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.POŚREDNIE 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.POŚREDNIE pomija jedynie inne SUMY.POŚREDNIE, sumuje natomiast zwykłą funkcję SUMA. Wynik jest błędny.

=SUMY.POŚREDNIE(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 (z wartościami ukrytymi)	Funkcja_nr (bez wartości ukrytych)	Funkcja
1	101	ŚREDNIA
2	102	ILE.LICZB
3	103	ILE.NIEPUSTYCH
4	104	MAKSIMUM
5	105	MINIMUM
6	106	ILOCZYN
7	107	ODCH.STANDARDOWE
8	108	ODCH.STANDARD.POPUL
9	109	SUMA
10	110	WARIANCJA
11	111	WARIANCJA.POPUL

Przykład 4.

ADRES, ADR.POŚR

(Arkusze: ‘Adres 1’, ‘Adres2’)

Sposób używania pola kombi wykorzystanego w poniższym przykładzie został opisany w lekcji ‘Profesjonalne raporty podstawy’, funkcja WYSZUKAJ.PIONOWO została opisana w lekcji ‘Funkcja wyszukaj’ przed przerobieniem tego ćwiczenia sugeruję zapoznanie się z tymi lekcjami.

Chcielibyśmy utworzyć wykres, który będzie przedstawiał dane w zależności od kraju i rodzaju danych wybranych przez użytkownika naszego raportu za pomocą dwóch pól kombi.

Poniżej tabela z zielonymi nagłówkami w której mają pojawiać się wybrane dane, oraz dane źródłowe z których będziemy korzystać.

Rozpoczniemy od przygotowania pól kombi, które pozwolą dokonywać wyboru.

Zaczynamy od pola pozwalającego na wybór kraju.

To co może zostać wybrane wprowadzamy pod tabelą z danymi. Łącze komórki ustawiamy przy polu kombi.

Analogicznie tworzymy pole wyboru rodzaju danych.

W komórce B6 korzystając funkcji Wyszukaj.Pionowo wprowadzamy formułę która będzie wyszukiwać jaki rodzaj danych wybrano.

Komórka ta zostanie użyta w późniejszym etapie jako tytuł wykresu, który będzie zmieniał się w zależności od rodzaju wybranych danych.

Pod tabelą wprowadzamy liczby od 1 do 12, dzięki którym będziemy mogli użyć tej samej formuły dla wszystkich miesięcy.

Wyszukiwanie danych rozpoczniemy od wprowadzenia funkcji ADRES, która tworzy adres komórki na podstawie danych, które mogą pochodzić z wielu źródeł.

Nr_wiersza Jeżeli wybrano Sprzedaż w komórce H3 pojawi się cyfra 1, sprzedaż jest w komórce C28, więc dodajemy jeszcze 27 aby trafić do tej komórki

Nr_kolumny pierwsza kolumna w której są interesujące nas dane to C, dlatego więc 1 (bo jest pierwsza) i +2 aby była to kolumna ‘C’ (która jest 3 w arkuszu 2+1=3)

Typ_adresu wprowadzamy 1

A1 wprowadzamy 1

Tekst_arkusz wprowadzamy nazwę arkusza w którym znajdują się dane do których się odwołujemy.

Tak zapisana formuła będzie w pokazywać adresy komórek z zaznaczonego na czerwono obszaru (C28:C30) w zależności od tego jaki rodzaj danych wybrano za pomocą pola kombi.

Formuła ta nie bierze jeszcze pod uwagę kraju który został wybrany i zawsze pokazuje adresy dla Polski.

Aby formuła ta pokazywała odpowiednie dane w zależności także od kraju należy w Mr_wirsza wprowadzić także odwołanie do komórki E2. Ponieważ dane dla każdego kraju zajmują 3 linie, cyfra znajdująca się w E2 jest przemnażana przez 3.

Ponieważ już dla Polski która jest pierwsza ta nowa dana przesuwa adres o 3 w dół, liczba 27 została zmniejszona do 24.

Pozostałe argumenty funkcji nie zmieniają się.

Po przeciągnięciu funkcji na całą tabelę uzyskujemy adresy danych właściwych dla kraju i rodzaju danych jakie zostaną wybrane za pomocą pół kombi.

Dla upewnienia się czy funkcja została dobrze wprowadzona sugeruję sprawdzić kilka kombinacji wyborów przed przejściem dalej.

Na funkcję ADRES nakładamy funkcje ADR.POŚR aby w miejsce adresu komórki pojawiła się dana która się w niej znajduje.

=ADR.POŚR(ADRES($E$2*3+$H$2+24;C7+2;1;1;"Adres 1"))

Pozostało już tylko dodanie wykresu (np. liniowego), który korzysta z danych zgromadzonych w tabeli.

Łącza pól kombi, cyfry znajdujące się pod tabelą oraz dane źródłowe ukrywamy na przykład formatując ich czcionkę na kolor biały.

Nasz wykres jest już godowy.

Możemy udoskonalić wykres wprowadzając do tytułu oprócz rodzaju pokazywanych danych także nazwę kraju.

W dowolnej komórce (w tym przykładzie jest to B4) wprowadzamy funcję WYSZUKAJ.PIONOWO, która na podstawie cyfry z komórki E2 wyszukuje jaki kraj został wybrany.

Aby mogła działać do listy krajów znajdującej się pod tabelą z danymi dopisujemy cyfry im odpowiadające.

W komórce B6 wprowadzamy funkcję ZŁĄCZ.TEKSTY. Na początek wprowadźmy funkcję łączącą 3 teksty literę „a” i spację oba znaki w cudzysłowiu oraz funkcję WYSZUKAJ.PIONOWO, która już wcześniej się tam znajdowała.

Kolejnym krokiem będzie skopiowanie funkcji WYSZUKAJ.PIONOWO z komórki B4 w miejsce litery „a”.

Na skutek czego otrzymamy następującą formułę:

=ZŁĄCZ.TEKSTY(WYSZUKAJ.PIONOWO(E2;B39:C41;2;0);" ";WYSZUKAJ.PIONOWO(H2;D39:E41;2;0))

W tytule wykresu będzie pojawiać się automatycznie informacja o kraju i rodzaju danych przedstawionych na wykresie.

Przykład 5.

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.

Po skopiowaniu formuły do poniższych komórek i dodaniu formatowania warunkowe najlepsi sprzedawcy są od razu wyraźnie widoczni.

Przykład 6.

Funkcje Bazy Danych

(Arkusz: ‘Funkcje BD’)

Funkcje bazy danych są używane do skomplikowanych operacji na danych.

Poniżej prezentuję funkcję BD.SUMA, która sumuje sprzedaż z bazy danych dla produktów wymienionych w liście warunki.

Podając zakres zarówno bazy danych, jak i warunków, podajemy je wraz z nagłówkami kolumn.

Analogicznie działają funkcje BD.ŚREDNIA, BD. MAX, BD.MIN i BD.ILE.REKORDÓW. Poniżej prezentuję ostatnią z nich.

Warunki można także zapisywać używając znaków <, >, <=, =>, <>.

Poniżej prezentuję działanie funkcji BD.SUMA dla 2 warunków: sprzedaż większa od 100 i jednocześnie mniejsza od 350.

Szkolenie Excel 2003 Szkolenie Excel 2007