Instrukcje warunkowe

 

 
Instrukcja warunkowa jest konstrukcją programistyczną, która określa dalsze działanie w zależności od tego, czy dane wyrażenie jest prawdziwe, czy fałszywe. W arkuszu kalkulacyjnym przyjmuje postać wbudowanych formuł, m.in.

• JEŻELI
• LICZ.JEŻELI
• SUMA.JEŻELI
• WYSZUKAJ.PIONOWO

 

FUNKCJA JEŻELI

Funkcja logiczna JEŻELI sprawdza, czy spełniony jest warunek (np. C2>B2). Jeżeli warunek jest spełniony, to wartością funkcji jest wartość1. Jeżeli warunek nie jest spełniony, wynikiem funkcji jest wartość2.

=JEŻELI(warunek;wartośćl;wartość2)

 

Ćwiczenie 1. Badanie realizacji planu nauki

   
Przygotuj tabelę, za pomocą której zbadasz realizację planu nauki słów w języku obcym (wzór poniżej). Załóż, że każdego dnia nauczysz się co najmniej 20 słów. Uzupełnij kolumnę Realizacja planu - wpisz właściwą formułę warunkową.


Sformułuj warunek opisowy.

Jeżeli liczba słów jest większa niż założony plan dzienny (20 słów) lub równa temu planowi, napisz "plan zrealizowany", w przeciwnym razie napisz "nie udało się".

Zapisz odpowiednią formułę w komórce C2.

=JEŻELI(B2>=$F$1;"plan zrealizowany";"nie udało się")

• Operator porównania „większy niż lub równy" ma postać >=.
• Komórka z liczbą słów ma adres względny B2, a komórka z planem dziennym adres bezwzględny $F$1, aby przy kopiowaniu formuły pierwszy adres się zmieniał, a drugi nie.

 
Skopiuj formułę z komórki C2 do pozostałych komórek.

 

FUNKCJA ZAGNIEŻDŻONA

W przypadku bardziej skomplikowanych zestawień i obliczeń stosuje się funkcje zagnieżdżone, tzn. wpisuje się w miejsce np. warunku lub wartości inną funkcję. Dzięki temu nie trzeba tworzyć dodatkowych kolumn prowadzących do wyniku końcowego albo - jak w przypadku zagnieżdżenia kilku funkcji JEŻELI - można sprawdzać jednocześnie wiele kryteriów. W trakcie tworzenia tego typu formuł należy pamiętać o zamykaniu nawiasów funkcji - inaczej wystąpi błąd.

Ćwiczenie 2. Planowanie powtórzenia

Przygotuj tabelę, za pomocą której zaplanujesz powtórzenie do egzaminu z języka obcego: czytanie ze zrozumieniem co 3 dni, gramatyka co 2 dni, pisanie wypracowań co 5 dni (wzór poniżej). Uzupełnij kolumny Czytanie ze zrozumieniem, Gramatyka i Pisanie wypracowań - wpisz właściwe formuły warunkowe.
  



Sformułuj warunek opisowy.

Aby stwierdzić, jakiego typu zadania mają być realizowane danego dnia, podziel liczbę oznaczającą numer dnia przez liczbę określającą częstość realizacji danego typu zadań i ustal wartość reszty z dzielenia: jeżeli reszta wynosi 0, wpisz komunikat „TAK", w przeciwnym wypadku pozostaw pustą komórkę.

Zapisz odpowiednią formułę w komórce B3.

=JEŻELI(MOD($A3;B$2)=0;"TAK";"")

• Wynikiem funkcji matematycznej MOD(liczba1;liczba2) jest reszta z dzielenia liczba1 przez liczba2. Wyrażenie może mieć wartość dodatnią lub 0.
• Aby jedna formuła realizowała wyliczenie dla całego zestawu, komórki z liczbami oznaczającymi numer dnia i częstość realizacji danego typu zadań powinny mieć adres mieszany. Dzięki temu podczas kopiowania formuły będą odwoływać się do tej samej kolumny (A) i do tego samego wiersza (2).

Skopiuj formułę z komórki B3 do pozostałych komórek - przeciągnij ją w dół i w prawo.

 

FUNKCJA LICZ.JEŻELI

 

Funkcja statystyczna LICZ.JEŻELI umożliwia zliczenie tych komórek z danego obszaru, które spełniają dane kryterium. Przyjmuje dwa argumenty: zakres (przeszukiwane dane, np. A1:A25) oraz kryterium (określony warunek, np. ">100").

 = LICZ.JEŻELI(zakres;kryterium)

Aby sprawdzić np., ile wymienionych w tabeli osób:

• zdobyło najwyżej 20 punktów, należy zapisać kryterium w postaci "<=20";
• zaliczyło test w pierwszym kwartale 2018 roku, należy zapisać kryterium w postaci "<=2018-03-31";
• otrzymało ocenę różną od ndst, należy zapisać kryterium w postaci "<>ndst", gdzie <> oznacza „różne od".

 

Ćwiczenie 3. Odnotowywanie postępów w nauce

Przygotuj tabelę, za pomocą której odnotujesz wyniki testów próbnych i postępy w nauce (wzór poniżej). Zastosuj odpowiednie funkcje warunkowe, aby uzupełnić:

1. kolumnę Ocena, zawierającą ocenę postępów w nauce w odniesieniu do poprzedniego dnia,
2. kolumnę Ile razy, zliczającą, ile razy było lepiej, gorzej, bez zmian.

Sformułuj warunek opisowy dotyczący kolumny Ocena.

Jeżeli wartość w danej komórce w kolumnie B jest większa niż wartość w komórce poprzedniej, napisz "lepiej", w przeciwnym wypadku sprawdź drugi warunek: jeżeli wartość w danej komórce jest mniejsza niż wartość w komórce poprzedniej, napisz „gorzej"; jeśli obie wartości są równe, napisz "bez zmian".

Zapisz odpowiednią formułę w komórce C3.

=JEŻELI(B3>B2;"lepiej"; JEŻELI(B3<B2;"gorzej";"bez zmian"))

Aby otrzymać rozwiązanie punktu 1, skopiuj formułę z komórki C3 do pozostałych komórek w tej kolumnie.




Sformułuj warunek opisowy dotyczący kolumny Ile razy.

Jeżeli w kolumnie Ocena znajdują się komórki spełniające dane kryterium ("lepiej", "gorzej", "bez zmian"), oblicz liczbę takich komórek.

Wprowadź odpowiednią formułę w komórce F2.

=LICZ.JEŻELI(C:C;E2)

     Zapis C:C oznacza całą kolumnę.

Aby otrzymać rozwiązanie punktu 2, skopiuj formułę z komórki F2 do pozostałych komórek w tej kolumnie.




 

FUNKCJA SUMA.JEŻELI

Funkcja matematyczna SUMA.JEŻELI umożliwia zbadanie, czy komórki z danego zakresu spełniają kryterium, i zsumowanie na tej podstawie wartości należących do innego zakresu. Funkcja ta przyjmuje trzy argumenty:

• zakres1 (przeszukiwane dane, np. B:B),
• kryterium (określony warunek) 
• zakres2 (zakres danych do zsumowania, odpowiadający argumentowi zakres1), przy czym zakres2 to argument opcjonalny — jeśli zostanie pominięty, program zsumuje komórki wyznaczone przez argument zakres1.

=SUMA.JEŻELI(zakres1;kryterium;zakres2)

 

Ćwiczenie 4. Zestawianie wyników egzaminu próbnego

Przygotuj tabelę, za pomocą której zestawisz wyniki egzaminu (wzór poniżej). Zastosuj odpowiednie formuły, aby uzupełnić:

1. kolumnę Suma prezentującą łączną liczbę punktów zdobytych podczas egzaminu,
2. kolumnę % prezentującą wynik procentowy egzaminu,
3. kolumnę Zal/Nzal prezentującą informację, czy egzamin został zaliczony, czy też nie,
4. średnią liczbę punktów dla egzaminów zaliczonych i niezaliczonych.

Wyznacz łączną liczbę uzyskanych punktów w komórce H3.

=SUMA(C3:G3)

Aby otrzymać rozwiązanie punktu 1, skopiuj formułę z komórki H3 do pozostałych komórek w tej kolumnie.

Wyznacz w komórce I3, ile procent stanowią uzyskane punkty. Pamiętaj o odpowiednim formacie liczb.

=H3/$H$2

Aby otrzymać rozwiązanie punktu 2, skopiuj formułę z komórki I3 do pozostałych komórek w tej kolumnie. (Jeśli po skopiowaniu formuły w komórce 14 uzyskasz wynik 49,5%, ustaw wyświetlanie wartości z zaokrągleniem do części całkowitych).

Sformułuj warunek opisowy dotyczący kolumny Zal/Nzal i wprowadź odpowiednią formułę w komórce J3.

=JEŻELI(I3>60%;"zal";"nzal")

Aby otrzymać rozwiązanie punktu 3, skopiuj formułę z komórki J3 do pozostałych komórek w tej kolumnie.

Wyznacz średnią liczbę uzyskanych punktów dla egzaminów zaliczonych i niezaliczonych.

  Oblicz łączną liczbę uzyskanych punktów za pomocą funkcji SUMA.JEŻELI.

=SUMA.JEŻELI(J:J;"zal";H:H)

=SUMA.JEŻELI(J:J;"nzal";H:H)

  Oblicz liczbę osób, które zaliczyły egzamin i które go nie zaliczyły, za pomocą funkcji LICZ.JEŻELI.

=LICZ.JEŻELI(J:J;"zal")

=LICZ.JEŻELI(J:J;"nzal")

  Aby otrzymać rozwiązanie punktu d, podziel wyniki funkcji SUMA.JEŻELI przez wyniki funkcji LICZ.JEŻELI.

 

FUNKCJA WYSZUKAJ.PIONOWO

Funkcja wyszukiwania i odwołań WYSZUKAJ.PIONOWO umożliwia znajdowanie danych w podanym zakresie według wierszy i przyjmuje trzy argumenty:

• wartość (szukaną wartość, np. liczbę uzyskanych punktów)
• zakres (przeszukiwane dane)
• indeks (numer kolumny w zakresie zawierającym wartość będącą wynikiem funkcji)

=WYSZUKAJ.PIONOWO(wartość; zakres; indeks)

Za pomocą tej funkcji można również kategoryzować dane, nadawać im pewne atrybuty (np. egzamin próbny, poprawkowy), przenosić je między arkuszami.
Podczas opracowywania danych trzeba pamiętać, że drugi argument składa się z dwóch kolumn. W pierwszej przeszukiwane są wartości lub zakresy, w drugiej — odpowiadające im wartości. W przypadku wyszukiwania wartości w zadanych przedziałach lista musi być posortowana rosnąco, w przeciwnym razie wynik będzie błędny.

 

Ćwiczenie 5.  Analizowanie wyników egzaminu końcowego

Przygotuj tabelę, za pomocą której zestawisz liczbę punktów z egzaminu końcowego (wzór poniżej). Zastosuj odpowiednie formuły, aby:

1. wyznaczyć łączną liczbę punktów uzyskanych przez każdego ucznia,
2. przyporządkować każdej łącznej liczbie punktów ocenę według podanej skali.

Wyznacz sumę uzyskanych punktów w komórce G3.

=SUMA(B3:F3)

Aby otrzymać rozwiązanie punktu 1, skopiuj formułę z komórki G3 do pozostałych komórek w tej kolumnie.

Przyporządkuj łącznej liczbie punktów w komórce H3 odpowiednią ocenę.

=WYSZUKAJ.PIONOWO(G3;$J$3:$K$8;2)

Aby otrzymać rozwiązanie punktu 2, skopiuj formułę z komórki H3 do pozostałych komórek.