26 października 2012
Kategoria: Źródło: [http://www.gohha.com/stronka/tutoriale/excel/function_if_zagn/function_if_zagn.html]
Najprostsze działanie funkcji Jeżeli polega na sprawdzeniu wartości podanej w komórkach arkusza ze wzorcem wprowadzonym do okna funkcji w pole Test logiczny. Następnie funkcja zwraca do komórki odpowiedni komunikat, który może być tekstem, liczbą lub spacją. Aby zrozumieć sens i sposób działania zagnieżdżania funkcji Jeżeli konieczne jest zapoznanie się i przećwiczenie jej działania na podstawowych prostych przykładach. Tutaj znajduje się tutorial, w którym można przeczytać o podstawach pracy z tą funkcją.
Kiedy stosować zagnieżdżanie funkcji Jeżeli?
Zagnieżdżanie polega na wywołaniu funkcji w funkcji. Bez zagnieżdżania funkcja Jeżeli jest w stanie wyprowadzić jedyniedwa komunikaty (jeden w przypadku, gdy test jest prawdziwy, drugi – gdy test okaże się fałszywy, czyli niezgodny z założeniem). Przykłady takich komunikatów to: TAK – NIE, Ubezpieczony – Nieubezpieczony, Palący – Niepalący, 0% – 100%. Takie odpowiedzi można mnożyć w nieskończoność.
Co można zrobić, gdy będą potrzebne nam trzy odpowiedzi (TAK – NIE – BRAK), lub więcej? Wtedy własnie powinno wkroczyć zagnieżdżenie. Jeśli wywołamy jedną funkcję Jeżeli w drugiej to w konsekwencji zwiększymy jednocześnie ilość możliwych przypadków oraz komunikatów, jakie będą wyprowadzane do komórek ze standardowych dwóch do trzech. I konsekwetnie – trzykrotne wywołanie funkcji w funkcji – zwiększa ilość komunikatów do czterech, czterokrotne – do pięciu itd.
Możliwości zagnieżdżania są na szczęście ograniczone (ze względu na nakład pracy i wzrastające ryzyko pomyłki). Korzystając z zagnieżdżania trzeba pamietać, że mamy jedynie 7 poziomów, czyli można siedem razy wywołać jedną funkcję w drugiej. W przypadku, gdy potrzebna będzie większa liczba komunikatów konieczne będzie skorzystanie z funkcji wyszukaj.pionowo.
Czas na prosty przykład
Załóżmy, że niektóre towary w sklepie mają czasową promocję. Dla przykładu niech to będą produkty Motoroli oraz Samsunga. Ceny pozostałych towarów pozostają bez zmian. Przy każdej nazwie produktu funkcja powinna wypisać wysokość obowiązującego rabatu. Potrzebne będą tu trzy komunikaty: 7%, 10% oraz 0% dla towarów, których cena nie podlega obniżeniu.
Zakładając, że tabela jest przygotowana identycznie jak ta na zrzucie, pierwsze wywołanie funkcji Jeżeli powinno nastąpić w komórce B2. W oknie funkcji trzeba podać pierwszy warunek (test logiczny), aby funkcja sprawdziła, czy w komórce A2 znajduje się tekst „Motorola”. Cudzysłów, w jakim umieszczony jest ten tekst jest obowiązkowy. Jeśli funkcja znajdzie taki napis zwróci komunikat 7%.
Pozostają nam jeszcze dwa przypadki: rabat 10% w przypadku marki Samsung oraz komunikat w przypadku, gdy funkcja nie znajdzie napisu Motorola lub Samsung.
Trzeba to rozwiązać za pomocą zagnieżdżenia. Aby wywołać funkcję w funkcji należy po wpisaniu komunikatu w poleWartość_jeżeli_prawda przestawić kursor w pole Wartość_jeżeli_falsz. Następnie kliknąć Pole nazwy (miejsce to wskazuje niebieska górna strzałka).
W tym momencie poprzednie okno funkcji powinno zniknąć, a na jego miejscu pojawić się powinno nowe puste okno funkcjiJeżeli.
Tak jak poprzednio najpierw należy wpisać drugi test logiczny oraz komunikat, który wyświetli się, gdy test ten będzie prawdziwy. Pozostaje nam ostatnie pole: Wartość_jeżeli_fałsz. Ten komunikat funkcja powinna wyświetlić wszędzie tam, gdzie znajdzie nazwę inną niż Motorola, czy Samsung. W moich wstępnych założeniach towary te nie podlegają promocji, więc funkcja powinna wyświetlać wartość zero.
Po wypełnieniu wszystkich pól klikamy w OK i przeciągamy funkcję do kolejnych komórek arkusza. Wyniki powinny być podobne do prezentowanych poniżej. Kolorowe elementy formatowania zostały wprowadzone wyłącznie dla podkreslenia wyników, więc nie należy się tym sugerować przy sprawdzaniu.
A teraz będzie trudniej
Trudniejszym przypadkiem, w którym można wykorzystać zagnieżdżoną funkcję Jeżeli są zakresy, np.: skala punktowa z przełożeniem na konkretną ocenę. Ja jednak skorzystam z nieco innego przykładu, którego założenia są oczywiście fikcyjne i wymyślone wyłącznie na potrzeby ćwiczenia.
Załóżmy, że klientowi korzystającemu z telefonu komórkowego trzeba zaproponować korzystniejszy pakiet w oparciu o średnią ilość minut, jakie faktycznie wykorzystał w ciągu ostatnich 3 miesiecy.
Przykład jest trudniejszy z kilku powodów. Po pierwsze konieczne będzie zastosowanie trzech funkcji Jeżeli. Po drugie mamy tu do czynienia z zakresami od-do, a nie z wartoscią, którą wystarczy porównać ze wzorcem. Trzeci powód może okazać się najtrudniejszy. W przypadku zakresów nie można zastosować dowolnej kolejności porównywania wartości z zakresem, w którym leży sprawdzana wartość. W poprzednim prostym zdaniu nie miało znaczenia, czy najpierw sprawdzimy wyraz Motorola, czy Samsung. W tym przypadku zbytniej dowolności nie ma.
Aby funkcja zadziałała prawidłowo i wyświetliła adekwatne do liczby komunikaty, należy testy logiczne układać tak, aby porównywać wartość z górną granicą przedziału. Nie ma znaczenia natomiast, czy sprawdzanie zaczniemy od największego, czy najmniejszego zakresu. Tak więc w pierwszym teście porównujemy, czy wartość z tabeli jest mniejsza bądź równa 25 (górna granica przedziału). Jeśli tak, funkcja wyprowadzi komunikat „oszczędny” (okno Pierwsza funkcja). W przeciwnym wypadku funkcja musi dalej szukać prawidłowego przedziału, czyli analogicznie do przykładu pierwszego kursor należy ustawić w ostatnim polu (Wartość_jeżeli_fałsz) i wywołać kolejne okno funkcji.
Tu układamy test logiczny, który sprawdzi liczbę z kolejnym przedziałem, wpisujemy komunikat „ekonomiczny” w pole Wartość_jeżeli_prawda (okno Druga funkcja) i powtarzamy ten krok wywołując jeszcze raz funkcję. W tym przypadku to ostatni etap. Wartosć z tabeli trzeba porównać za pomocą trzeciego testu logicznego z przedziałem „mniejsze lub równe 75”. Jeśli test wypadnie pomyślnie funkcja wypisze komunikat „gadatliwy”.
Zostaje jeszcze jedna możliwość – liczba z tabeli może być większa od poprzednio sprawdzanych przedziałów. Zgodnie z moim założeniem, gdy średnia liczba minut przekroczy 75 minut klientowi należy zaproponować pakiet „biznesowy”. Ten komunikat należy wpisać w ostatnie okno funkcji w polu Wartość_jeżeli_falsz (Okno Trzecia funkcja). Na tym kończymy przygotowywanie funkcji i kopiujemy ją do pozostałych komórek.
Powyżej na zrzucie widać, że wyniki funkcji odpowiadają założonym przedziałom. Oczywiście w momencie, gdy zmianie ulegnie średnia liczba minut funkcja sprawdzi, w jakim przedziale mieści się nowa liczba i wyprowadzi odpowiedni komunikat.
Jeszcze kilka słów wyjaśnienia powodu, dla którego konieczne jest sprawdzanie przedziałów w poprawny sposób. Gdyby ułożyć niepoprawnie testy sprawdzające, Jeżeli w części przypadków nie zadziała poprawnie i nie dopasuje dokładnie przedziału do sprawdzanej wartości, a w konsekwencji wyświetli zły komunikat.
Dwa poniższe zapisy funkcji będą dzialać prawidłowo:
=JEŻELI(B2<=25;”oszczędny”;JEŻELI(B2<=50;”ekonomiczny”;JEŻELI(B2<=75;”gadatliwy”;”biznesowy”)))
=JEŻELI(B2>75;”Biznesowy”;JEŻELI(B2>50;”Gadatliwy”;JEŻELI(B2>25;”Ekonomiczny”;”Oszczędny”)))
Jak widać w zapisie funkcji test porównawczy jest ułożony w taki sposób, że sprawdzanie zaczyna się od górnej granicy każdego przedziału.
Poniższy sposób natomiast nie jest prawidłowy, ponieważ test jest ułożony źle – sprawdzanie jest rozpoczęte od dolnej granicy najniższego przedziału:
=JEŻELI(B2>=0;”Oszczędny”;JEŻELI(B2>=26;”Ekonomiczny”;JEŻELI(B2>=51;”Gadatliwy”;”Biznesowy”)))
Wynikiem takiego zapisu funkcji będzie komunikat „Oszczędny” w każdym przypadku. Stanie się tak, ponieważ funkcja po sprawdzeniu pierwszego testu uzna, że jest on prawdziwy, ponieważ sprawdzana wartość jest większa od zera i zakończy działanie wypisaniem komunikatu.
Opis pochodzi ze strony:
http://www.gohha.com/stronka/tutoriale/excel/function_if/function_if.html