Funkcje warunkowe arkusza kalkulacyjnego Excel.

Wśród wielu funkcji arkusza kalkulacyjnego Excel znajdują się funkcje warunkowe, których wynik zależy od spełnienia zadanego warunku. Na przykładach zostaną omówione trzy wybrane funkcje. Są to:

• JEŻELI

• LICZ.JEŻELI

• WYSZUKAJ.PIONOWO

1. JEŻELI

W komórce B2 znajduje się liczba. Należy określić jej znak i odpowiedni tekst umieścić w komórce D2.

Przykład rozwiązania przedstawiony został na rysunku obok.

Jak widać, zastosowano funkcję JEŻELI, której składnia jest następująca:

JEŻELI(waunek_logiczny;wartość_gdyprawda;wartość_gdy_fałsz)

Funkcja, zależnie od wartości testu logicznego, zwraca jedną z dwóch wartości:

wartość_gdy_prawda - gdy zmienna warunek_logiczny ma wartość PRAWDA.

wartość_gdy_fałsz - gdy argument warunek_logiczny ma wartość FAŁSZ.

warunek_logiczny - jest dowolną wartością lub wyrażeniem, sprawdzanym czy jest to PRAWDA czy FAŁSZ.

W naszym zadaniu sprawdzamy czy liczba testowana jest większa lub równa zero. Ponieważ nie jest to prawdą, warunek nie jest spełniony (w komórce B2 mamy -3), wynikiem działania funkcji jest wartość_gdy_fałsz, czyli tekst „Liczba ujemna”.

Jeżeli pisząc formułę opisującą działanie funkcji JEŻELI opuścimy wartość_gdy_fałsz i średnik po wartość_gdy_prawda, to zwracana jest logiczna wartość FAŁSZ, gdy warunek_logiczny równa się FAŁSZ.

Jeżeli opuścimy wartość_gdy_fałsz zostawiając średnik po wartość_gdy_prawda, to zwracana jest wartość 0, gdy warunek_logiczny równa się FAŁSZ

Wartość_jeżeli_fałsz może być inną formułą.

Uwaga

Można zagnieździć do siedmiu funkcji JEŻELI stosując je jako argumenty wartość_gdy_prawda i wartość_gdy_fałsz w celu spełnienia bardziej złożonych warunków.

2. WYSZUKAJ.PIONOWO

Uczniowie klasy IA nie byli zgodni w kwestii zajmowanych miejsc na lekcjach. Postanowiono więc rozsadzić wszystkich zgodnie z podanym przez niego wzrostem. Wyznaczono cztery strefy w klasie dla uczniów:

• niskich

• średnich

• wysokich

• i olbrzymów.

W każdej ze stref uczniowie mogli zajmować dowolne miejsce. Należy przydzielić uczniów do poszczególnych stref zgodnie z podanym przez nich wzrostem.

Do rozwiązania zadania można zastosować funkcję WYSZUKAJ.PIONOWO. Do jej prawidłowego działania konieczne jest utworzenie np. tablicy wzorzec, w której umieszczone zostaną przedziały wzrostu uczniów przyporządkowane strefom w klasie.

Funkcja

WYSZUKAJ.PIONOWO (odniesienie;tablica;nr_kolumny;kolumna)

przeszukuje skrajną lewą kolumnę tablicy (wzorzec), by znaleźć wartość szczególną i podaje wartość we wskazanej komórce (B2).

Odniesienie - jest wartością, którą należy znaleźć w pierwszej kolumnie tablicy. Odniesienie może być wartością, adresem lub łańcuchem tekstowym.

Tablica - jest tablicą z informacją, którą należy przeszukać. Należy użyć adresu zakresu lub nazwy zakresu, takiej jak Bazadanych lub Lista.

• Jeśli wartością parametru kolumna jest PRAWDA, wartości w pierwszej kolumnie tablicy powinny być umieszczone w porządku rosnącym : ..., -2, -1, 0, 1, 2, ... , A-Z, FAŁSZ, PRAWDA; w przeciwnym przypadku WYSZUKAJ.PIONOWO może nie podać poprawnej wartości. Jeśli wartością parametru kolumna jest FAŁSZ, nie ma potrzeby sortowania tablicy.

• Wartości w pierwszej kolumnie tablicy tablica mogą być tekstami, liczbami lub wartościami logicznymi.

• Teksty pisane dużymi i małymi literami są równoważne.

Nr_kolumny - jest to numer kolumny w tablicy, z którego pochodzić powinna pasująca wartość. Nr_kolumny o wartości 1 daje w wyniku wartość w pierwszej kolumnie tablicy tablica; nr_kolumny o wartości 2 daje w wyniku wartość w drugiej kolumnie tablicy tablica, i tak dalej. Jeśli indeks nr_kolumny jest mniejszy niż 1, WYSZUKAJ.PIONOWO podaje w wyniku wartość błędu #ARG!; jeśli indeks nr_kolumny jest większy niż liczba kolumn w tablicy tablica, WYSZUKAJ.PIONOWO daje w wyniku wartość błędu #ADR!.

Kolumna - jest wartością logiczną wskazującą, czy WYSZUKAJ.PIONOWO ma znaleźć dokładne czy też przybliżone dopasowanie. Jeżeli parametr ten ma wartość PRAWDA lub został pominięty, wartością wynikową będzie dopasowanie przybliżone; innymi słowy, w razie gdyby nie uzyskano dokładnego dopasowania, wynikiem będzie następna największa wartość mniejsza niż wartość odniesienie. Jeśli parametr ma wartość FAŁSZ, WYSZUKAJ.PIONOWO znajdzie dokładne dopasowanie. Jeśli nie znajdzie żadnego, wynikiem będzie wartość błędu #N/D.

Uwagi

• Jeśli WYSZUKAJ.PIONOWO nie może znaleźć odniesienia i wartością argumentu kolumna jest PRAWDA, funkcja użyje największej wartości nie większej od odniesienia.

• Jeśli wartość szukana_wartość jest mniejsza niż najmniejsza wartość w pierwszej kolumnie tablicy tablica, wynikiem funkcji WYSZUKAJ.PIONOWO będzie wartość błędu #N/D.

• Jeśli funkcja WYSZUKAJ.PIONOWO nie może znaleźć wartości odniesienie i wartością argumentu kolumna jest FAŁSZ, wynikiem funkcji WYSZUKAJ.PIONOWO będzie wartość błędu #N/D.

3. LICZ.JEŻELI

W tabeli zestawiono oceny i nieobecności uczniów klasy IA na zajęciach w pracowni komputerowej (rysunek poniżej). Ponieważ wszystkie klasy pierwsze biorą udział w konkursie na klasę o najlepszej frekwencji, należy policzyć, ile zajęć opuścili uczniowie tej klasy.

Do rozwiązania posłuży tym razem funkcja LICZ.JEŻELI, o budowie:

LICZ.JEŻELI (zakres;kryteria)

Jak widać, po podaniu zakresu, czyli obszaru, który należy objąć badaniem i kryterium, czyli wartości, która ma zostać policzona, wynikiem jest liczbę komórek wewnątrz zakresu, które spełniają podane wymagania.

Kryteria   są to kryteria w postaci liczby, wyrażenia lub tekstu określające, które komórki będą obliczane. Na przykład, kryteria można wyrazić jako 32, "32", ">32", "jabłka".

4. Zadania do wykonania

1. Uzupełnij tabelkę w arkuszu dane tak, by można było wystawić proponowane oceny roczne uczniom w pracowni specjalistycznej. (plik f_warunkowa.xls)

Kryteria wystawiania ocen są następujące:

1. Jeśli uczeń ma dwie nieobecności na zajęciach to otrzymuje ocenę niedostateczną.

2. Jeśli uczeń ma jedną nieobecność na zajęciach i ocenę niedostateczną to otrzymuje ocenę dopuszczającą.

3. Jeśli uczeń nie ma nieobecność na zajęciach to otrzymuje ocenę, która jest średnią arytmetyczną otrzymanych na zajęciach ocen.

Ponieważ oceny roczną muszą być liczbami całkowitymi, uzyskane oceny średnie zamień na całkowite tak, by spełniały poniższe kryteria:

1. Jeśli ocena średnia jest mniejsza od 1,8 uczeń otrzymuje ocenę niedostateczną

2. Jeśli ocena średnia jest większa lub równa 1,8 i mniejsza od 2,5 uczeń otrzymuje ocenę dopuszczającą

3. Jeśli ocena średnia jest większa lub równa 2,5 i mniejsza od 3,5 uczeń otrzymuje ocenę dostateczną

4. Jeśli ocena średnia jest większa lub równa 3,5 i mniejsza od 4,5 uczeń otrzymuje ocenę dobrą

5. Jeśli ocena średnia jest większa lub równa 4,5 i mniejsza od 5,3 uczeń otrzymuje ocenę bardzo dobry

6. Jeśli ocena średnia jest większa lub równa 5,3 uczeń otrzymuje ocenę celujący

2. Wykonaj w arkuszu kalkulacyjnym niżej przedstawione zadanie.

Prowadzimy ewidencję uczniów w klasie. Dobrzy uczniowie nagradzani są premiami pieniężnymi. Wysokość premii zależy od średniej ocen ucznia i klasy. Jeżeli średnia ocen ucznia przekracza 4,0, otrzymuje on premię w wysokości 100 zł. Jeżeli średnia ocen ucznia przekracza średnią klasy, to uczeń o średniej powyżej 4,0 dostaje dodatkowo 50 zł.

Nazwisko i imię	J. polski	Fizyka	Chemia	Historia	Średnia	Premia
Kowalski Jan	6	4	4	3
Nowak Edward	3	5	5	5
Spychała Ewa	3	4	4,5	4
Ząbek Bożena	5	5	3,5	2,5
Buła Joanna	4	6	5	6
			Średnia klasy

Funkcje warunkowe arkusza kalkulacyjnego 4/4

---