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 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

Przykład 1 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 bezwzględne, 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 2 SUMA.JEŻELI, ZŁĄCZ.TEKSTY

(Arkusz: ‘SUMA.JEŻELI’)

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 funkcji ZŁĄCZ.TEKSTY.

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 funkcji ZŁĄCZ.TEKSTY.

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ć data 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.

Przykład 3. ADRES, ADR.POŚR

(Arkusz z danymi: ‘Adres’)

(Arkusz z rozwiązaniem: ‘Adres 2’)

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 przykładu 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 poniżej 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 pochodzących 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, w tym miejscu wykorzystamy cyfry znajdujące się pod tabelą pod pierwszą z komórek tabeli jest cyfra 1 i odniesienie do niej wprowadzimy w tej formule ale musimy do niej dodać 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 w cudzysłowie nazwę arkusza w którym znajdują się dane do których się odwołujemy.

Tak zapisana formuła będzie pokazywać adresy komórek z 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 danych dla Polski.

Aby formuła ta pokazywała odpowiednie dane w zależności także od kraju należy w ‘Nr_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. Najwygodniej będzie dopisać ją ręcznie.

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

Zmienioną formułę kopiujemy do pozostałych pól.

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 funkcję WYSZUKAJ.PIONOWO, która na podstawie cyfry z komórki E2 wyszukuje jaki kraj został wybrany.

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łowie oraz funkcję WYSZUKAJ.PIONOWO, która już wcześniej była w tej komórce.

Kolejnym krokiem będzie skopiowanie całej formuły z komórki B4 w miejsce litery „a”. (pomijamy znak =)

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

=ZŁĄCZ.TEKSTY(WYSZUKAJ.PIONOWO(E2;B38:C40;2;0);" ";WYSZUKAJ.PIONOWO(H2;D38:E40;2;0))

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

UWAGA:

Nazwy arkuszy zapisane w funkcjach ADRES i ADR.POŚR, nie zmieniają się automatycznie wraz ze zmianą nazwy arkusza. Po każdej zmianie nazwy arkusza musimy ręcznie zmienić odwołania we wszystkich funkcjach, w przeciwnym razie wyświetlone zostaną komunikaty o błędzie: #ADR!.