Lekcja 5    Temat: Funkcje w Excelu używające warunku.

W programowaniu wykorzystywaliśmy rozkaz porównaj - inaczej instrukcję warunkową.

Program Microsoft Excel oferuje również funkcje, których można używać do analizowania danych na podanych warunkach.

1.Rózne rodzaje funkcji z wykorzystaniem warunku

   a)Funkcja JEŻELI

Aby formuła zwróciła jedną z dwóch wartości na podstawie warunku, takiego jak premia za sprzedaż oparta na określonej wartości sprzedaży, czy wyprowadzenie dwóch różnych komunikatów w zależności od zawartości komórki należy użyć funkcji arkusza JEŻELI.

Składnia :

JEŻELI(test_logiczny;wartość_jeżeli_prawda;wartość_jeżeli_fałsz)

Np. chcemy wyróżnić osoby , które mają średnią powyżej  4,75   (załóżmy, że średnia umieszczona jest w komórce F8)     tworzymy funkcję

 = jeżeli( F8>4,75 ; „promocja z wyróżnieniem” ; „nie ma wyróżnienia”)

funkcja składa się z trzech elementów:  z warunku (czyli F8>4,75) , co umieścić w komórce gdy warunek jest prawdziwy ( czyli tekst „promocja z wyróżnieniem”) oraz co umieścić w komórce gdy warunek fałszywy ( czyli tekst „ nie ma wyróżnienia” ) separatorem tych trzech elementów jest średnik.

Funkcję jeżeli można zagnieżdżać tzn. umieszczać w jednej funkcji jeżeli kolejne funkcje jeżeli

b) Funkcja LICZ.JEŻELI

 Aby zliczyć komórki wewnątrz zakresu, które spełniają podane kryteria użyjemy funkcji LICZ.JEŻELI

Składnia

LICZ.JEŻELI(zakres;kryteria)

Zakres   to zakres komórek, w którym mają być zliczane komórki.

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

Wykorzystamy tę funkcję do zliczenia ile ocen bdb, db, dost, dop, ndst otrzymał dany uczeń

Czyli LICZ.JEŻELI(B2:B7; 2)  jeżeli oceny były w zakresie b2 do b7 to zliczy nam ilość ocen dopuszczających.

c) Funkcja SUMA.JEŻELI

Na przykład, aby obliczyć sumę na podstawie ciągu tekstowego lub liczby wewnątrz zakresu, należy użyć funkcji arkusza SUMA.JEŻELI.

Funkcja ta dodaje komórki określone przez podane kryteria.

Składnia

SUMA.JEŻELI(zakres;kryteria;suma_zakres)

Zakres   to zakres komórek, które należy oszacować.

Kryteria   to kryteria mające postać liczby, wyrażenia lub tekstu określającego, które komórki będą dodawane. Na przykład kryteria mogą być wyrażane jako 32, „32”, „>32”, „jabłka”.

Suma_zakres   to rzeczywiste komórki wyznaczone do zsumowania.

Np. =SUMA.JEŻELI(A2:A5;">150";B2:B5) a więc kryterium to wartość większa niż  150 sprawdzana w komórkach w kolumnie A natomiast sumowane są odpowiednie komórki z kolumny B

2. Tworzenie rozbudowanego warunku w funkcji JEŻELI – wykorzystanie funkcji logicznych

a ) Wykorzystanie alternatywy w warunku – czyli  funkcja LUB.

Funkcja zwraca wartość logiczną PRAWDA, jeśli choć jeden argument ma wartość logiczną PRAWDA; jeśli wszystkie argumenty mają wartość logiczną FAŁSZ, funkcja zwraca wartość logiczną FAŁSZ.

Składnia

LUB(logiczna1;logiczna2;...)

Logiczna1; logiczna2;...   to od 1 do 30 warunków, które można sprawdzić i które mogą przyjmować wartość albo PRAWDA, albo FAŁSZ.

b) Wykorzystanie koniunkcji w warunku – czyli funkcji ORAZ

Wynikiem funkcji jest PRAWDA, jeśli wszystkie jej argumenty mają wartość PRAWDA; wynikiem funkcji jest FAŁSZ, jeśli co najmniej jeden z argumentów ma wartość FAŁSZ.

Składnia

ORAZ(logiczny1;logiczny2;...)

Logiczny1; logiczny2;...   to od 1 do 30 warunków, które można sprawdzić i które mogą przyjmować wartość albo PRAWDA, albo FAŁSZ.

Często funkcja ORAZ służy nam do sprawdzania przedziałów liczbowych

Np. = JEŻELI( (ORAZ(A1>20;A1<100); „liczba z przedziału 20-100”; „liczba spoza przedziału20-100” ) , oczywiście w funkcji JEŻELI  oprócz wyprowadzania tekstu można operować liczbami i formułami .

ZADANIE DO WYKONANIA

Z wykonanego wcześniej arkusza  ze średnią ocen skopiować sobie zawartość do nowego pliku, który nagrywamy jako  plik zadanie006.xls

Uzupełnić arkusz tak, aby zawierał 20 uczniów i  12 przedmiotów .

Wprowadzić kolumnę, w której będziemy przyznawać wyróżnienie, jeżeli średnia ucznia jest powyżej 4,75

Wprowadzić kolumnę, w której przyznamy promocję jeżeli nie będzie wśród ocen ani jednej  oceny niedostatecznej.

Zadanie tzw. „z gwiazdką” , ale do zrobienia przez każdego - oblicz jedną  formułą średnią ocen klasy biorąc pod uwagę tylko uczniów promowanych.