Projektowanie formuł wykorzystujących zagnieżdżone funkcje

W celu zrozumienia tego materiału potrzebna jest wiedza o podstawowych aspektach arkuszy kalkulacyjnych i ich zastosowaniach, dodatkowe informacje znajdziesz pod następującymi linkami:

• Wprowadzanie danych do arkuszaD199pZOldWprowadzanie danych do arkusza

• Formatowanie komórek arkusza kalkulacyjnegoD5KAfd3tGFormatowanie komórek arkusza kalkulacyjnego

• Projektowanie formuł i ich kopiowaniePtDHixXq7Projektowanie formuł i ich kopiowanie

• Projektowanie formuł wykorzystujących sprawdzanie warunkówP1G7oOexkProjektowanie formuł wykorzystujących sprawdzanie warunków

Nie zawsze jest możliwe ograniczenie się do podstawowych funkcji podczas tworzenia arkuszy kalkulacyjnych. Często konieczne jest opracowanie bardziej zaawansowanych formuł, zwłaszcza w przypadku bardziej złożonych zadań. Przed dodaniem dowolnej funkcji warto najpierw dokładnie zastanowić się nad problemem, który próbujemy rozwiązać. Warto spróbować rozłożyć ten problem na poszczególne przypadki, a następnie korzystać z  dostępnych funkcji w programie, aby stworzyć formułę, która obsłuży każdy z tych przypadków.

Projektując formułę zagnieżdżającą pewne funkcje, nie wystarczy zastosować pojedynczej funkcji, która sprawdzając określony warunek, daje możliwość wprowadzenia do komórki jednej z dwóch wartości. Należy wprowadzić dodatkową funkcję wewnątrz użytej funkcji, która pozwoli wprowadzić do komórki kolejne wartości. Chcąc wprowadzić więcej różnych wartości, należy wstawić wewnątrz kolejne funkcje. W formule można zagnieździć maksymalnie 64 poziomy funkcji.

Przykład 1

R11ir2zNwWto1

Zrzut przedstawia tabelę w arkuszu kalkulacyjnym, jej dwie kolumny to: Liczba oraz Znak liczby. Kolumna Znak liczby jest pusta. W kolejnych wierszach wypisane są liczby: Liczba 5, Liczba -10, Liczba 7, Liczba 0, Liczba -1.

Podana jest tabela zawierająca kolumny Liczba oraz Znak liczby. Pierwsza z nich zawiera pewne liczby dodatnie, ujemne oraz zero. W drugiej z nich ma znaleźć się:

• znak „+”, jeśli liczba w komórce po lewo jest dodatnia,

• znak „-”, jeśli liczba w komórce po lewo jest ujemna,

• znak „0”, jeśli liczba w komórce po lewo ma wartość zero.

Powyższa lista zawiera wszystkie możliwe przypadki jakie mogą wystąpić - liczba może być dodatnia, ujemna lub ma wartość zero. Chcąc wstawić odpowiedni znak, skorzystaj z funkcji JEŻELI. Sprawdź za jej pomocą jeden z przypadków, a następnie zagnieźdź kolejną funkcję JEŻELI, sprawdź za jej pomocą pozostałe dwa przypadki. Końcowa formuła wstawiona w komórce B2 będzie miała postać:

=JEŻELI(A2 > 0; "+"; JEŻELI(A2 < 0; "-"; "0"))

R1AKul1vXJUqE

Zrzut przedstawia tabelę w arkuszu kalkulacyjnym, jej dwie kolumny to: Liczba oraz Znak liczby. W kolejnych wierszach wypisane są liczby i ich znaki: Liczba 5, Znak plus, Liczba minus 10, znak minus, Liczba 7, Znak plus, Liczba 0, Znak zero, Liczba minus 1, Znak minus.

Przykład 2

RyWo6gqLGVZeX

Zrzut ekranu przedstawia tabelę w arkuszu kalkulacyjnym. Ma ona dwie kolumny: Rok, Rok przestępny. Pierwsza kolumna jest wypełniona, natomiast druga jest pusta. Zawartość kolumny Rok to: 2000, 1996, 1900, 2023, 2012, 2011, 2004, 2001.

Podana jest tabela zawierająca kolumny Rok oraz Rok przestępny. W drugiej z nich należy wypisać słowo TAK - jeżeli rok jest przestępny, NIE - jeżeli nie jest. Rok jest przestępny, jeśli zachodzi co najmniej jedno z poniższych:

• rok dzieli się przez 4 oraz nie dzieli się przez 100,

• rok dzieli się przez 400.

W tym przypadku odpowiednie funkcje będą zagnieżdżone w warunku sprawdzającym funkcji JEŻELI. W końcowej formule będą potrzebne funkcje logiczne LUB i ORAZ w celu sprawdzania dwóch powyższych warunków, jak również funkcja matematyczna MOD (reszta z dzielenia) do sprawdzania podzielności. Chcąc sprawdzić, czy liczba jest podzielna przez pewien dzielnik, należy wynik operacji modulo tej liczby i dzielnika przyrównać do zera. Końcowa formuła w komórce B2 będzie miała następującą postać:

=JEŻELI(LUB(ORAZ( MOD(A2; 4) = 0; MOD(A2; 100) <> 0 ); MOD(A2; 400) = 0); "TAK"; "NIE")

R1ArfdXlbwLkb

Zrzut ekranu przedstawia tabelę w arkuszu kalkulacyjnym. Ma ona dwie kolumny: Rok, Rok przestępny. Zawartość kolejnych wierszy dla kolumn rok, rok przestępny to: 2000 tak, 1996 tak, 1900 nie, 2023 nie, 2012 tak, 2011 nie, 2004 tak, 2001 nie.

Aby udoskonalić swoje umiejętności projektowania formuł wykorzystujących zagnieżdżone funkcje wykonaj poniższe ćwiczenia.

1

Ćwiczenie 1

Pobierz arkusz z załącznika. W kolumnie A jest zapisywana temperatura każdego dnia o godzinie 7:00. W kolumnie B jest zapisywana temperatura o godzinie 19:00. Kolumna C powinna zawierać wartość o wyższej temperaturze w danym dniu. W przypadku, gdy temperatury rano i wieczorem będą sobie równe, w kolumnie C powinna się znaleźć informacja, że były one sobie równe. Zaprojektuj w komórce C2 formułę, która porówna temperatury rano i wieczorem. W komórce C2 powinna zostać wypisana wyższa wartość temperatury lub słowo „równe”. Wykonaj samodzielnie to zadanie, wykorzystując znane ci funkcje.

RyZus5kRwJpa4

Załącznik zawiera arkusz "Obserwacje dziennych temperatur" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Obserwacje dziennych temperatur" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

R1WuJ8Jl5Bybo

Załącznik zawiera arkusz "Obserwacje dziennych temperatur" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Obserwacje dziennych temperatur" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

RgCq4BeoxvKUl

Zrzut ekrany przedstawia tabelę z arkusza "Obserwacje dziennych temperatur", Kolumna A zawiera odczyty temperatur o godzinie siódmej, Kolumna B zawiera odczyty temperatur o godzinie dziewiętnastej, Kolumna C ma zawierać informację, która z temperatur była wyższa, kolumna jest pusta.

Pokaż podpowiedź

Rozpatrz wszystkie możliwe przypadki:

• Temperatura z kolumny A jest większa niż temperatura z kolumny B.

• Temperatura z kolumny A jest mniejsza niż temperatura z kolumny B.

• Temperatura z kolumny A jest równa temperaturze z kolumny B.

Do wykonania zadania możesz pobrać gotowy plik z tabelą wypełnioną przykładowymi danymi. Aby zaprojektować arkusz, w którym zostaną porównane temperatury, zgodnie z przestawionymi założeniami, postępuj zgodnie z poniższą instrukcją.

• Utwórz tabelę i wypełnij ją danymi lub pobierz załączony plik.

• W komórce C2 zaprojektuj formułę, która określi wyższą temperaturę lub wprowadzi słowo „równe” w przypadku równych temperatur.

• Skopiuj komórkę C2 do pozostałych komórek tabeli w kolumnie C, obliczając w ten sposób wyższą temperaturę dla każdego dnia.

Pokaż odpowiedź

Zapoznaj się z filmem przedstawiającym projektowanie formuł wykorzystujących zagnieżdżone funkcje w odpowiednim programie:

Microsoft Excel (wersja 2019 lub nowszy):

RYUFCADimYCGV

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie MS Excel

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie MS Excel

Film dostępny pod adresem /preview/resource/RYUFCADimYCGV

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie MS Excel

LibreOffice Calc (wersja 7.0 lub nowszy):

RIQHRMiJnsl3X

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie LibreOffice Calc

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie LibreOffice Calc

Film dostępny pod adresem /preview/resource/RIQHRMiJnsl3X

Film przedstawia w jaki sposób zastosować formuły wykorzystujące zagnieżdżone funkcje w programie LibreOffice Calc

Przykładowa odpowiedź:

Formuła umieszczona w komórce C2:

=JEŻELI(A2 > B2; A2; JEŻELI(A2 < B2; B2; "równe"))

RVRP5baFy90ND

Zrzut arkusza z tabelą, którą można pobrać wyżej. Rozwiązanie zadania to formuła: =JEŻELI(temperatura o 7 > temperatura o 19;temperatura o 7;JEŻELI(temperatura o 7 < temperatura o 19; temperatura o 19; "równe"))

2

Ćwiczenie 2

Pobierz arkusz z załącznika. W arkuszu powinny znajdować się kolumny Przedmiot, Ocena roczna oraz Słownie. Poniżej wypisanych ocen, w komórce B14 oblicz średnią ocen ucznia. Następnie w komórce C2 zaprojektuj formułę zmieniającą wartość liczbową oceny na wartość słowną i skopiuj ją do pozostałych komórek w kolumnie Słownie.

R1AtbDVYNNAEi

Załącznik zawiera arkusz "Oceny" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Oceny" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

R15O0cBNfUh2v

Załącznik zawiera arkusz "Oceny" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Oceny" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

RIXQNCtZOVOl3

Zrzut arkusza, który przestawia tabelę. Jest to tabela składająca się z przedmiotów szkolnych, przypisanych do nich ocen słownie i numerycznie. Na końcu znajduje się komórka średnia. Kolumna ocen zapisanych słownie jest pusta oraz komórka oznaczona jako średnia jest pusta.

Pokaż podpowiedź

Projektując formułę, sprawdź po kolei każdą ocenę jaką może otrzymać uczeń, możesz również wypisać „błąd” lub „brak oceny w skali”, jeżeli pojawi się ocena wykraczająca poza dostępną skalę.

Pokaż odpowiedź

Przykładowa odpowiedź:

Formuła w komórce C2:

 =JEŻELI(B2 = 6; "celujący"; JEŻELI(B2 = 5; "bardzo dobry"; JEŻELI(B2 = 4; "dobry"; JEŻELI(B2 = 3; "dostateczny"; JEŻELI(B2 = 2; "dopuszczający"; JEŻELI(B2 = 1; "niedostateczny"; "brak oceny w skali"))))))

Formuła umieszczona w komórce B14:

=ŚREDNIA(B2:B13)

R1WzcWs4R55Bp

Zrzut arkusza, który przestawia tabelę. Jest to tabela składająca się z przedmiotów, przypisanych do nich ocen słownie i numerycznie. Na końcu jest wyliczona średnia. Przykładowe rozwiązanie zadania: formuła w komórce C2: =JEŻELI(B2=6;"celujący";JEŻELI(B2=5;"bardzo dobry";JEŻELI(B2=4;"dobry";JEŻELI(B2=3;"dostateczny";JEŻELI(B2=2;"dopuszczający";JEŻELI(B2=1;"niedostateczny";"brak oceny w skali")))))). Formuła umieszczona w komórce B14: =ŚREDNIA(B2:B13)

2

Ćwiczenie 3

Pobierz arkusz z załącznika. W tabeli prezentującej parki narodowe w Polsce uzupełnij kolumnę Wielkość, w której zostanie wypisana słownie wielkość parku: „mały” – o powierzchni większej niż 0, ale mniejszej niż 50 kmIndeks górny 2², „średni” – o powierzchni większej lub równej 50 kmIndeks górny 2² i mniejszej niż 100 kmIndeks górny 2², zaś „duży” – o powierzchni większej lub równej 100 kmIndeks górny 2². W przypadku wartości ujemnej powinien zostać wypisany „błąd”. Jeśli powierzchnia wynosi 0, powinien zostać wypisany „brak”. Zastosuj odpowiednie zagnieżdżone funkcje, aby wypełnić tabelę.

RJDhPExvm5iQm

Załącznik zawiera arkusz "Parki narodowe w Polsce" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Parki narodowe w Polsce" w formacie XLSX. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

RbTuALWFax7gy

Załącznik zawiera arkusz "Parki narodowe w Polsce" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia.

Załącznik zawiera arkusz "Parki narodowe w Polsce" w formacie ODS. Jest on potrzebny do wykonania ćwiczenia. Pobierz załącznik

R1SpY3oQsy8Kg

Zrzut arkusza z tabelą do pobrania, która przedstawia parki narodowe wraz z przypisaną datą utworzenia i powierzchnią w kilometrach kwadratowych. Tabela posiada również pustą kolumnę opisaną jako wielkość.

Pokaż podpowiedź

Rozpatrz wszystkie możliwe przypadki:

• Park ma powierzchnię mniejszą niż 0, co jest zapewne błędem.

• Park ma powierzchnię równą 0, czyli brak powierzchni.

• Park ma powierzchnię większą niż 0, ale mniejszą niż 50 kmIndeks górny 2².

• Park ma powierzchnię większą lub równą 50 kmIndeks górny 2² oraz mniejszą niż 100 kmIndeks górny 2².

• Park ma powierzchnię większą lub równą 100 kmIndeks górny 2².

Przy projektowaniu formuły, należy zwrócić uwagę na to, że w czwartym z rozpatrywanych przypadków konieczne jest sprawdzenie, czy wartość w danej komórce mieści się w określonym przedziale. Formułę można zapisać na dwa sposoby, bez lub z funkcją ORAZ w miejscu warunku funkcji JEŻELI. Funkcja ORAZ zwraca prawdę, jeśli wszystkie jej argumenty są prawdziwe.

Pokaż odpowiedź

Formuła umieszczona w komórce E2 może zostać zapisana na kilka sposobów. Poniżej wypisano dwa z nich:

=JEŻELI(D2<0;”błąd”;JEŻELI(D2=0;”brak”;JEŻELI(D2<50;”mały”;JEŻELI(D2<100;”średni”;”duży”))))

=JEŻELI(D2<0;"błąd";JEŻELI(D2=0;"brak";JEŻELI(ORAZ(D2>0;D2<50);"mały";JEŻELI(ORAZ(D2>=50;D2<100);"średni";"duży"))))

RpU9JuMrSmdXR

Zrzut tabeli, w arkuszu Parki narodowe w Polsce z wypełnioną kolumną Wielkość.

W poniższym notesie zapisuj przydatne informacje lub własne notatki.