Podział na kategorie jest bardzo często spotykany i pozwala na szybką wstępną ocenę rozpatrywanego elementu. Bardzo często stosuje następujący podział:
kategoria A – bardzo dobry, kategoria B – średni/przeciętny, kategoria C – słaby/zły. Dzięki zastosowaniu podziału na kategorie błyskawicznie otrzymasz wstępny czytelny opis hierarchii twoich danych. Wystarczy, że określisz granice przedziałów poszczególnych kategorii, a samo ich przyporządkowanie zrobi za ciebie odpowiednia formuła.
Film przedstawia przykładową tabelę z danymi o średniej ocen osiągniętej przez poszczególnych uczniów w twojej klasie. W dolnej części umieszczamy informację o wartościach granicznych poszczególnych kategorii. Zakładamy, że:
kategoria A dotyczy wartości większych niż średnia 4,8, kategoria B opisuje wartości pomiędzy 4,0 a 4,8 wraz z liczbami granicznymi, kategoria C dotyczy wszystkich wartości poniżej 4,0. Aby przyporządkować wartościom odpowiednie kategorie:
W komórce C4 zastosuj następującą formułę:
Linia 1. znak równości JEŻELI otwórz nawias okrągły B4 otwórz nawias ostrokątny $B$13 średnik cudzysłów C cudzysłów średnik JEŻELI otwórz nawias okrągły ORAZ otwórz nawias okrągły B4 zamknij nawias ostrokątny znak równości $B$13 średnik B4 otwórz nawias ostrokątny znak równości $B$14 zamknij nawias okrągły średnik cudzysłów B cudzysłów średnik cudzysłów A cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
Inna formuła realizująca te same obliczenia wygląda tak:
Linia 1. znak równości JEŻELI otwórz nawias okrągły B4 otwórz nawias ostrokątny $B$13 średnik cudzysłów C cudzysłów średnik JEŻELI otwórz nawias okrągły B4 zamknij nawias ostrokątny $B$14 średnik cudzysłów A cudzysłów średnik cudzysłów B cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
=JEŻELI(B4<$B$13;"C";JEŻELI(B4>$B$14;"A";"B"))
Skopiuj ją następnie na cały zakres C4:C11. W efekcie otrzymasz właściwy podział na kategorie na bazie przyjętych granic.
Wyjaśnienie działania formuły:
Pierwsza funkcja JEŻELI() sprawdza, czy wartość z komórki B4 jest mniejsza od dolnej wartości granicznej, podanej w komórce B13. Jeśli taka sytuacja zaistniała, wartość należy do kategorii C i taki tekst zostanie wpisany w wyniku działania formuły. Dla liczb większych od pierwszego przedziału zastosowanie ma druga funkcja JEŻELI(), wewnątrz której sprawdzane jest jednoczesne wystąpienie dwóch zależności: większe od dolnej i mniejsze od górnej wartości granicznej kategorii. Jeśli występują powyższe zależności, wartość znajduje się w przedziale B, który zostanie wyświetlony. Jeżeli żaden ze sprawdzanych wcześniej warunków nie został spełniony, analizowana wartość musi być większa od górnego przedziału, stąd też bez dodatkowego sprawdzania wyświetlona zostanie kategoria A.
Ciekawostka
Ten sam problem możesz rozwiązać przy użyciu innej funkcji arkusza kalkulacyjnego. Mowa o funkcji WYSZUKAJ.PIONOWO(). Może ona zostać użyta do odnalezienia w jakim przedziale mieści się rozpatrywana wartość. Przykład jej zastosowania przedstawia zrzut ekranu:
R182R9R9tkimu
Na zrzucie ekranu widoczny jest fragment arkusza Excel, w kolumnach A, B i C wprowadzono dane. W kolumnie A opisano komórkę A1 jako Kategorie. Komórkę A3 opisano jako Uczeń. Komórkę B3 jako Średnia. Komórkę C3 Kategoria. W kolumnie A, w komórkach wpisano nazwiska uczniów. W kolumnie B wpisano wartości średniej. W kolumnie C kategorie A, B lub C. Wartości wierszami przedstawiają się następująco. Wiersz 4. Uczeń Kowalski. Średnia 4,9. W komórce C4 wpisano formułę =WYSZUKAJ kropka PIONOWO otwórz nawias B4 średnik dolar B dolar 13 dwukropek dolar C dolar 15 średnik 2 średnik PRAWDA zamknąć nawias. Wiersz 5. Uczeń Miklas. Średnia 4,7. Kategoria B. Wiersz 6. Uczeń Rak. Średnia 3,4. Kategoria C. Wiersz 7. Uczeń Jasna. Średnia 3,2. Kategoria C. Wiersz 8. Uczeń Wielicki. Średnia 4,8. Kategoria A. Wiersz 9. Uczeń Stoch. Średnia 4. Kategoria B. Wiersz 10. Uczeń Bytomski. Średnia 5. Kategoria A. Wiersz 11. Uczeń Obara. Średnia 2. Kategoria C. Wiersz 12 jest pusty. Wiersz 13 opisano jako Granica 1. Średnia 0. Kategoria C. Wiersz 14 opisano jako Granica 2. Średnia 4. Kategoria B. Wiersz 15 opisano jako Granica 3. Średnia 4,8. Kategoria A. Komórki zawierające średnią i kategorię w wierszach od 13 do 15 zaznaczone są kolorem czerwonym.
Do prawidłowego działania funkcji WYSZUKAJ.PIONOWO() należy utworzyć tablicę z dolnymi granicami przedziałów (na zrzucie ekranu zakres B13:C15). Przedziały muszą być podane w kolejności rosnącej. W przeciwnym razie funkcja zwróci błąd #N/D.
Zwróć uwagę na komórkę C8. Funkcja rozpatrzyła wartość 4,8 jako należącą do kategorii A. Zgodnie z treścią polecenia chcemy, aby to kategoria B opisywała wartości pomiędzy 4,0 a 4,8 wraz z liczbami granicznymi. Aby to uczynić, należałoby zmienić dolną granicę kategorii A na np. 4,81. Niestety, takie rozwiązanie niesie za sobą konsekwencje. Otóż wartości między 4,80 a 4,81 będą należały to kategorii B (a nie powinny). Dlatego w tym konkretnym przypadku wykorzystanie rozbudowanej funkcji JEŻELI() okaże się lepszym rozwiązaniem.
R6XRYmFPNrTFO
Nagranie filmowe dotyczące przypisania wartości do kategorii.
Nagranie filmowe dotyczące przypisania wartości do kategorii.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-ND 3.0.
Zapytaj koleżanek i kolegów z klasy, ile czasu dziennie spędzają na przeglądaniu mediów społecznościowych. Otrzymane dane umieść w tabeli, a następnie podziel je na kilka kategorii w zależności od ilości spędzonego czasu w sieci.
Przykładowe rozwiązanie zadania:
R1JOM74p7nrN7
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące czasu spędzanego codziennie na przeglądaniu mediów społecznościowych przez dane osoby. W arkuszu kolejno dodano opisy: w komórce A1 osoba, w komórce B2 ile czasu. W kolumnie A, w komórkach od A2 do A11 wpisano imiona osób. W kolumnie B, w komórkach od B2 do B11 wpisano wartości czasu. Dodatkowo zaprezentowano sposób obliczania kategorii. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórkach od C2 do C11. W komórce C2 brzmi ona następująco: =JEŻELI(B2>5;"Za dużo";JEŻELI(B2>3;"Trochę za dużo";"Mało")). W komórce C3 brzmi ona następująco: =JEŻELI(B3>5;"Za dużo";JEŻELI(B3>3;"Trochę za dużo";"Mało")). W komórce C4 brzmi ona następująco: =JEŻELI(B4>5;"Za dużo";JEŻELI(B4>3;"Trochę za dużo";"Mało")). W komórce C5 brzmi ona następująco: =JEŻELI(B5>5;"Za dużo";JEŻELI(B5>3;"Trochę za dużo";"Mało")). W komórce C6 brzmi ona następująco: =JEŻELI(B6>5;"Za dużo";JEŻELI(B6>3;"Trochę za dużo";"Mało")). W komórce C7 brzmi ona następująco: =JEŻELI(B7>5;"Za dużo";JEŻELI(B7>3;"Trochę za dużo";"Mało")). W komórce C8 brzmi ona następująco: =JEŻELI(B8>5;"Za dużo";JEŻELI(B8>3;"Trochę za dużo";"Mało")). W komórce C9 brzmi ona następująco: =JEŻELI(B9>5;"Za dużo";JEŻELI(B9>3;"Trochę za dużo";"Mało")). W komórce C11 brzmi ona następująco: =JEŻELI(B11>5;"Za dużo";JEŻELI(B11>3;"Trochę za dużo";"Mało")).
3
Ćwiczenie 2
Planujesz zakup nowego telefonu. Masz już kilka wybranych modeli, jednak trudno ci podjąć decyzję, który z nich opłaca się kupić najbardziej. Wypisz kilka specyfikacji tych urządzeń (np. liczba pikseli w aparacie, pojemność akumulatora, przekątną ekranu itp.), a następnie podziel je na kategorię od najlepszego do najgorszego. Znajdź najbardziej opłacalną dla ciebie opcję.
Przykładowe rozwiązanie zadania:
RiX9OUTRfjXEe
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, C, D i E wprowadzono dane dotyczące specyfikacji konkretnych marek aparatów. W arkuszu kolejno dodano opisy: w komórce A1 marka, w komórce B1 ilość megapikseli w aparacie, w komórce C1 przekątną ekranu (cale), w komórce D1 cena, w komórce E1 akumulator (miliamperogodzina). W kolumnie A, w komórkach od A2 do A4 wpisano marki aparatów. W kolumnie B w komórkach od B2 do B4 wpisano wartości megapikseli. W kolumnie C, w komórkach od C2 do C4 wpisano wartości przekątnej ekranu. W kolumnie D, w komórkach od D2 do D4 wpisano wartości ceny. W kolumnie E, w komórkach od E2 do E4 wpisano wartości miliamperogodzin. Dodatkowo zaprezentowano sposób obliczania ilości pikseli, przekątnej ekranu, ceny i akumulatora danej marki aparatu. Formułę obliczania ilości pikseli należy wpisać w odpowiednich komórkach. W tym przypadku – dla każdej kolejnej marki - w komórkach od H3 do H5. W komórce H3 brzmi ona następująco: =JEŻELI(ORAZ(B$2>=B$3;B$2>=B$4);”1”;JEŻELI(LUB(B$2>=B$3;B$2>=B$4);”2”;”3”)). W komórce H4 brzmi ona następująco: =JEŻELI(ORAZ(B$3>=B$2;B$3>=B$4);”1”;JEŻELI(LUB(B$3>=B$2;B$3>=B$4);”2”;”3”)). W komórce H5 brzmi ona następująco: =JEŻELI(ORAZ(B$4>=B$3;B$4>=B$2);”1”;JEŻELI(LUB(B$4>=B$3;B$4>=B$2);”2”;”3”)). Formułę obliczania przekątnej ekranu należy wpisać w odpowiednich komórkach. W tym przypadku – dla każdej kolejnej marki - w komórkach od H8 do H10. W komórce H8 brzmi ona następująco: =JEŻELI(ORAZ(C$2>=C$3;C$2>=C$4);”1”;JEŻELI(LUB(C$2>=C$3;C$2>=C$4);”2”;”3”)). W komórce H9 brzmi ona następująco: =JEŻELI(ORAZ(C$3>=C$2;C$3>=C$4);”1”;JEŻELI(LUB(C$3>=C$2;C$3>=C$4);”2”;”3”)). W komórce H10 brzmi ona następująco: =JEŻELI(ORAZ(C$4>=C$3;C$4>=C$2);”1”;JEŻELI(LUB(C$4>=C$3;C$4>=C$2);”2”;”3”)). Formułę obliczania ceny należy wpisać w odpowiednich komórkach. W tym przypadku – dla każdej kolejnej marki - w komórkach od H13 do H15. W komórce H13 brzmi ona następująco: =JEŻELI(ORAZ(D$2<=D$3;D$2<=D$4);”1”;JEŻELI(LUB(D$2<=D$3;D$2<=D$4);”2”;”3”)). W komórce H14 brzmi ona następująco: =JEŻELI(ORAZ(D$3<=D$2;D$3<=D$4);”1”;JEŻELI(LUB(D$3<=D$2;D$3<=D$4);”2”;”3”)). W komórce H15 brzmi ona następująco: =JEŻELI(ORAZ(D$4<=D$3;D$4<=D$2);”1”;JEŻELI(LUB(D$4<=D$3;D$4<=D$2);”2”;”3”)). Formułę obliczania akumulatora należy wpisać w odpowiednich komórkach. W tym przypadku – dla każdej kolejnej marki - w komórkach od H18 do H20. W komórce H18 brzmi ona następująco: =JEŻELI(ORAZ(E$2>=E$3;E$2>=E$4);”1”;JEŻELI(LUB(E$2>=E$3;E$2>=E$4);”2”;”3”)). W komórce H19 brzmi ona następująco: =JEŻELI(ORAZ(E$3>=E$2;E$3>=E$4);”1”;JEŻELI(LUB(E$3>=E$2;E$3>=E$4);”2”;”3”)). W komórce H20 brzmi ona następująco: =JEŻELI(ORAZ(E$4>=E$3;E$4>=E$2);”1”;JEŻELI(LUB(E$4>=E$3;E$4>=E$2);”2”;”3”)).
