Wyszukiwanie tekstu na podstawie wartości liczbowej
Wbudowane narzędzia i funkcje arkusza kalkulacyjnego pozwalają przeprowadzić różne wyszukiwania: możemy szukać konkretnej wartości, a także wartości skrajnych (minimum lub maksimum). Jednak co robić w sytuacji, gdy potrzebujemy wyszukać opis wartości, która jest najbliższa poszukiwanej? Pobierz plik z przykładowymi danymi umieszczony poniżej filmu.
Skorzystajmy z formuły tablicowej:
Linia 1. znak równości INDEKS otwórz nawias okrągły A5 dwukropek A16 średnik PODAJ kropka POZYCJĘ otwórz nawias okrągły MIN otwórz nawias okrągły MODUŁ kropka LICZBY otwórz nawias okrągły E4 minus B5 dwukropek B16 zamknij nawias okrągły zamknij nawias okrągły średnik MODUŁ kropka LICZBY otwórz nawias okrągły E4 minus B5 dwukropek B16 zamknij nawias okrągły średnik 0 zamknij nawias okrągły zamknij nawias okrągły.
Działanie formuły w przykładowym arkuszu przedstawiliśmy na filmie.
Formuła wymaga podania trzech argumentów: najpierw zakresu, z którego zwrócony będzie wynik (w przykładzie A5:A16), potem poszukiwanej wartości lub wartości jej najbliższej (w przykładzie E4) i wreszcie przeszukiwanego zakresu, czyli zakres komórek, w którym szukana będzie wartość poszukiwana (w przykładzie B5:B16).
Nawiasy klamrowe wskazują, że jest to formuła tablicowa, a więc zatwierdzamy ją kombinacją klawiszy [Ctrl]+[Shift]+[Enter].
Ta formuła tablicowa wykorzystuje cztery funkcje arkusza, współpracujące ze sobą w następujący sposób:
funkcje MODUŁ.LICZBY obliczają wartość bezwzględną różnicy pomiędzy wartością poszukiwaną a każdą napotkaną wartością w przeszukiwanym zakresie;
za pomocą funkcji MIN wyznaczysz najmniejszą wartość obliczanej różnicy, a więc wartość najbliższą szukanej;
dzięki funkcji PODAJ.POZYCJĘ ustalisz, w którym wierszu wewnątrz rozpatrywanej tabeli znajduje się wartość zwrócona przez funkcję MIN;
na podstawie informacji przekazanej z funkcji PODAJ.POZYCJĘ funkcja INDEKS zwróci w wyniku wartość, której lokalizacja określona została pozycją w tabeli i zakresem (kolumną) podaną w pierwszym argumencie.
A teraz zobaczmy działanie formuły na przykładzie. Najmniejszą różnicę względem poszukiwanej liczby 80 daje liczba 80, zlokalizowana w 9. wierszu tabeli, a 13. wierszu arkusza. Jest to więc liczba najbliższa poszukiwanej. Za pomocą funkcji INDEKS zwrócona zostaje w wyniku wartość Wrzesień, odpowiadająca w tabeli pozycji znalezionej liczby 80. Jeśli kilka liczb wykazywało taką samą różnicę względem wartości poszukiwanej, w wyniku zwrócona zostanie pierwsza z napotkanych wartości.
Ro3nd5XXfVxyF
Film nawiązujący do treści wyszukiwania wartości najbliższej.
Film nawiązujący do treści wyszukiwania wartości najbliższej.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Twoja wychowawczyni w arkuszu kalkulacyjnym prowadzi spis rocznych średnich ocen uczniów uczęszczających razem z tobą do klasy. Znajdź osobę, która w tym roku uzyskała najwyższą średnią.
Przykładowe rozwiązanie zadania:
R2XNG7USRkWnO
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 umieszczono tytuł Uczniowie. W komórkach od A2 do A17 wpisano imiona i nazwiska. W komórce B1 umieszczono tytuł Średnia. W komórkach od B2 do B17 wpisane są średnie z dokładnością do 2 miesc po przecinku. W komórce D5 wpisano tytuł Najwyższa średnia. W komórce E5 wpisano formułę, która brzmi następująco: =MAX(B2:B22) W komórce D6 wpisano tytuł Osoba z najwyższą średnią. W komórce E6 wpisano nazwisko uczennicy z najwyższą średnią, jest to Idalia Kwiatkowska.
RL5i9TQ4sRGeC
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 wpisano tytuł Uczniowie. W komórce B1 wpisano tytuł Średnia. W komórkach kolumn A i B wprowadzano dane imion uczniów oraz wartości osiąganej przez nich średniej ocen. W komórce D5 wpisano tytuł Najwyższa średnia. W komórce E5 wpisano wartość 5,73. W komórce D6 wpisano tytuł Osoba z najwyższą średnią. W komórce E6 wpisano formułę, która brzmi następująco =INDEKS(A2:A21;PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY(E5-B2:B22));MODUŁ.LICZBY(E5-B2:B22);0))
Polecenie 2
Dyrektor twojej szkoły zorganizował konkurs ekologiczny. Każda z klas miała za zadanie zebrać jak najwięcej kilogramów makulatury w 2 tygodnie. Nagrodą w konkursie była wycieczka do jednego z polskich miast. Przygotuj arkusz zawierający podsumowanie konkursu (ilość kg zebranej makulatury przez poszczególne klasy). Znajdź klasę, która wygrała nagrodę.
Przykładowe rozwiązanie zadania:
RFz19x6Ojd9ui
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono różne dane dotyczące zebranej przez klasy szkolne makulatury. W komórce A1 wpisano tytuł Klasa. W komórce B1 wpisano tytuł Makulatura jednostka kilogramy. W komórkach kolumn A i B do wiersza 14 wprowadzano symbole klas oraz ilości kilogramów zebranej makulatury. W komórce D3 wpisano tytuł Największa ilość. W komórce E3 wpisano formułę, która brzmi następująco =MAX(B2:B14) W komórce D4 wpisano tytuł Zwycięska klasa. W komórce E4 wpisano symbol zwycięskiej klasy jest to I b.
RsNX4RyUWrxxt
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 wpisano tytuł Klasa. W komórce B1 wpisano tytuł Makulatura jednostka kilogramy. W komórkach kolumn A i B do wiersza 14 wprowadzano symbole klas oraz ilości kilogramów zebranej makulatury. W komórce D3 wpisano tytuł Największa ilość. W komórce E3 wpisano wartość 360. W komórce D4 wpisano tytuł Zwycięska klasa. W komórce E4 wpisano formułę, która brzmi następująco =INDEKS(A2:A14;PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY(E3-B2:B14));MODUŁ.LICZBY(E3-B2:B14);0))
