Wyszukiwanie bliskich wartości np. nieco tańszego i droższego laptopa!

Z lekcji dowiesz się:

• W jakich sytuacjach wyszukuje się bliskie wartości?

• Jak przygotować tekst logiczny wyszukujący najbliższe dane?

• O zaletach wyszukiwania zbliżonego w statystyce!

Rozpatrywanym zadaniem jest wyszukanie najbliższej mniejszej lub większej wartości dla danej liczby w Excelu. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rys. 1.

Rys. 1 – Przykładowe dane

W pierwszej części omówimy przykład, w którym będziemy szukać najbliższej mniejszej i większej wartości w stosunku do konkretnej liczby (najbliższy tańszy i droższy produkt). Możemy to zrobić stosując test logiczny, polegający na sprawdzeniu, które ceny produktów są mniejsze lub większe od założonego budżetu klienta. Na podstawie wysokości budżetu klienta, chcemy dopasować najlepszy dla niego produkt (spełniający kryterium ceny), czyli najbliższy kwoty jaką chce wydać.

W Excelu testy logiczne są powiązane z funkcją JEŻELI. Pierwszym argumentem funkcji jest test_logiczny, polegający na sprawdzeniu które elementy z zakresu B2:B15 (z kolumny Cena) są mniejsze od komórki D2, czyli budżetu klienta. Ze względu na to, że piszemy formułę w pojedynczych komórkach, nie musimy się martwić o rodzaj odwołania (względne lub bezwzględne), dlatego nie musimy dodatkowo blokować zaznaczonego zakresu bezwzględnie.

Możemy podejrzeć wyniki testu logicznego w trybie edycji komórki za pomocą klawisza F9. Otrzymamy tablicę wartości logicznych PRAWDA lub FAŁSZ przedstawioną na rys. 2.

Rys. 2 – Wyniki testu logicznego – tablica wartości logicznych PRAWDA i FAŁSZ

Wartość logiczną FAŁSZ otrzymamy w sytuacji, kiedy cena danego produktu przekracza budżet klienta, natomiast wartość logiczną PRAWDA otrzymamy dla cen mniejszych od tego budżetu.

Z podglądu wyników testu logicznego możemy wyjść za pomocą skrótu klawiszowego Ctrl+Z.

Drugim argumentem funkcji jest wartość_jeżeli_prawda, czyli zakres z którego chcemy otrzymać wartości spełniające warunek z testu logicznego.

Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli wartości jakie chcemy uzyskać kiedy warunek z testu logicznego nie jest spełniony. W tym przypadku nie interesują nas wartości nie spełniające warunku, więc możemy pominąć trzeci argument.

Zapis funkcji powinien wyglądać następująco:

=JEŻELI(B2:B15<d2;b2:b15)< p="">

Przy takim zapisie funkcji, kiedy w trybie edycji komórki podejrzymy wyniki formuły za pomocą klawisza F9 otrzymamy wartości logiczne FAŁSZ w sytuacji, kiedy test logiczny nie został spełniony oraz konkretne wartości liczbowe w sytuacji, kiedy test logiczny został spełniony (ceny mniejsze od budżetu klienta), przedstawione na rys. 3.

Rys. 3 – Wyniki funkcji JEŻELI w podglądzie wyników formuły

Podgląd wyniku formuły można cofnąć za pomocą skrótu klawiszowego Ctrl+Z.

Ze względu na to, że szukamy najbliższej mniejszej wartości od założonego budżetu, to musimy spośród tych wartości wybrać największą liczbę. Najprostszym sposobem jest wstawienie funkcji JEŻELI do funkcji MAX. Zapis funkcji powinien wyglądać następująco:

=MAX(JEŻELI(B2:B15<d2;b2:b15))< p="">

Wynik powyższej funkcji można podejrzeć za pomocą klawisza F9 w trybie edycji komórki. W ten sposób otrzymamy jedną konkretną wartość liczbową (cenę) najbliższą założonemu budżetowi klienta przedstawioną na rys. 4.

Rys. 4 – Wynik funkcji MAX w podglądzie wyników formuły

Z podglądu wyników formuły wychodzi się za pomocą skrótu klawiszowego Ctrl+Z. Tak przygotowaną formułę tablicową zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter. Otrzymujemy wtedy najbliższą mniejszą wartość liczbową od założonego budżetu (rys. 5).

Rys. 5 – Wynik funkcji MAX (najbliższa wartość mniejsza od założonego budżetu)

Otrzymaliśmy cenę produktu, która jest najbliższą mniejszą wartością od założonego budżetu. Kolejnym celem jest otrzymanie nazwy tego produktu, a nie jego ceny. Kopiujemy formułę z komórki E2 do komórki poniżej, a następnie zajmiemy się jej edycją, aby otrzymać nazwę tego produktu. Użyjemy do tego funkcji PODAJ.POZYCJĘ.

Pierwszym argumentem funkcji PODAJ.POZYCJĘ jest szukana_wartość, czyli cena produktu zwracana przez funkcję MAX.

Drugi argument funkcji to przeszukiwana_tab, czyli zakres tabeli, w którym szukamy tej konkretnej wartości liczbowej (kolumna Cena).

Trzeci argument to typ_porównania. Na rysunku 6 przedstawione zostały trzy warianty porównywania wartości.

Rys. 6 – Typy porównania dla funkcji PODAJ.POZYCJĘ

W tym przykładzie wybieramy dokładne dopasowanie, czyli w formule wpisujemy wartość 0, ponieważ oczekiwanym wynikiem będzie jedna konkretna wartość. Zapis funkcji powinien wyglądać następująco:

=PODAJ.POZYCJĘ(MAX(JEŻELI(B2:B15<d2;b2:b15));b2:b15;0)< p="">

Powyższą formułę zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter. Otrzymamy numer pozycji w danych tej konkretnej ceny (rys. 7).

Rys. 7 – Wynik funkcji PODAJ.POZCYJĘ, czyli numer wiersza w którym znajduje się szukana cena

Nie jest znana jeszcze nazwa danego laptopa, w tym celu użyjemy funkcji INDEKS.

Pierwszym argumentem funkcji INDEKS jest tablica, czyli kolumna Produkt, z której chcemy wyciągnąć nazwę laptopa.

Drugi argument funkcji to nr_wiersza, czyli wartość zwrócona przez funkcję PODAJ.POZYCJĘ.

Trzeci argument, czyli nr_kolumny, pomijamy ponieważ poszukiwany jest konkretny wiersz. Zapis formuły powinien wyglądać następująco:

=INDEKS(A2:A15; PODAJ.POZYCJĘ(MAX(JEŻELI(B2:B15<d2;b2:b15));b2:b15;0))< p="">

Powyższą funkcję zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter. W ten sposób otrzymujemy nazwę laptopa odpowiadającego wcześniej wyznaczonej cenie – jest to najbliższy tańszy produkt od założonego budżetu (rys. 8).

Rys. 8 – Wynik funkcji INDEKS (nazwa laptopa odpowiadająca najbliższej mniejszej cenie od założonego budżetu)

Aby znaleźć produkt z ceną najbliższą droższą od założonego budżetu wystarczy odpowiednio zmodyfikować powyższą formułę. W tym celu kopiujemy w trybie edycji komórki treść formuły z komórki E3 za pomocą skrótu klawiszowego Ctrl+C. Następnie wychodzimy z trybu edycji komórki za pomocą klawisza ESC i wklejamy formułę w komórkę obok (F3).

Aby znaleźć najbliższy droższy produkt musimy odwrócić test logiczny w funkcji JEŻELI, czyli zmienić znak porównania. Sprawdzimy teraz które elementy z kolumny Cena są większe od założonego budżetu. Zapis funkcji JEŻELI będzie wyglądał następująco:

=JEŻELI(B2:B15>D2;B2:B15)

Kolejna zmiana w formule dotyczy maksymalnej wartości, tym razem szukamy minimalnej wartości ze zwróconych przez funkcję JEŻELI. Zatem zamieniamy funkcję MAX na MIN. Zapis funkcji będzie wyglądał następująco:

=MIN(JEŻELI(B2:B15>D2;B2:B15))

Powyższe zmiany wystarczą, aby uzyskać nazwę laptopa o cenie najbliższej większej od założonego budżetu. Zapis całej formuły będzie wyglądał następująco:

=INDEKS(A2:A15; PODAJ.POZYCJĘ(MIN(JEŻELI(B2:B15>D2;B2:B15)))B2:B15;0)

Tak przygotowaną formułę zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter, gdyż jest to formuła tablicowa.

Kiedy podejrzymy sobie wyniki funkcji JEŻELI w trybie edycji komórki otrzymamy wartości logiczne FAŁSZ oraz wszystkie ceny większe od założonego budżetu klienta przedstawione na rys. 9.

Rys. 9 – Podgląd wyników funkcji JEŻELI (ceny wyższe od założonego budżetu)

Z podglądu formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z.

Z funkcji INDEKS otrzymamy nazwę laptopa o najniższej cenie przekraczającej budżet klienta (rys. 10).

Rys. 10 - Wynik funkcji INDEKS (nazwa laptopa odpowiadająca najbliższej większej cenie od założonego budżetu)

Cześć powyższej formuły – funkcję MIN możemy skopiować w trybie edycji komórki za pomocą skrótu klawiszowego Ctrl+C, a następnie wkleić ją do komórki F2 za pomocą skrótu klawiszowego Ctrl+V, aby otrzymać najmniejszą wartość wyższą od założonego budżetu klienta. Zapis funkcji powinien wyglądać następująco:

=MIN(JEŻELI(B2:B15>D2;B2:B15))

Powyższą formułę zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter. Otrzymamy wtedy szukaną cenę Najbliższego droższego produktu przedstawioną na rys. 11.

Rys. 11 – Wynik funkcji MIN (najbliższa wartość większa od założonego budżetu)

W pracy z danymi zdarza się, że szukamy wartości najbliższej do konkretnej liczby. Zadanie to wykonamy na drugim arkuszu o nazwie Stan wody. Przykładowe dane do tego zadania zostały przedstawione na rys. 12.

Rys. 12 – Przykładowe dane do obliczenia wartości najbliższej w stosunku do konkretnej liczby

Zadanie polega na ustaleniu jaki był stan wody w konkretnym dniu o konkretnej godzinie (2019-09-03 godz. 18.00). Nie można tego określić dokładnie ponieważ pomiary wody są okresowe, w różnych dniach o różnych godzinach. Stan wody nie jest też parametrem, dla którego można wyznaczyć linię trendu.

Należy więc znaleźć datę i czas najbliższy podanej dacie w kolumnie Data (komórka D2) i na tej podstawie określić stan wody. W przykładowych danych mamy podany stan wody z 2019-09-03 o godz. 18.17, a nas interesuje stan wody z godziny 18.00.

Pierwszą czynnością, jaką wykonamy będzie odejmowanie – odejmiemy od wartości w kolumnie A (Czas pomiaru), Czas jaki nas interesuje. Zapis formuły powinien wyglądać następująco:

=A2:A121-D2

W wyniku tego odejmowania otrzymamy całą tablicę wartości. Wartości te mogą być również ujemne, dlatego musimy wyciągnąć moduł liczby, aby otrzymać tylko wartości dodatnie. Użyjemy do tego funkcji MODUŁ.LICZBY. Zapis formuły będzie wyglądał następująco:

=MODUŁ.LICZBY(A2:A121-D2)

W kolejnym kroku chcemy znaleźć wartość minimalną z tych wartości, ponieważ wtedy otrzymamy najmniejszą różnicę w dacie pomiaru oraz naszej konkretnej dacie dla której chcemy ustalić stan wody. Użyjemy tutaj funkcji MIN. Zapis formuły będzie wyglądał następująco:

=MIN(MODUŁ.LICZBY(A2:A121-D2))

Powyższą formułę zatwierdzamy skrótem klawiszowym Ctrl+Shift+Enter, ponieważ jest to formuła tablicowa. Otrzymamy najbliższą różnicę między czasami pomiaru a czasem dla którego chcemy ustalić stan wody (rys. 13).

Rys. 13 – Najbliższa różnica między czasami pomiaru a czasem, dla którego chcemy wyznaczyć stan wody

W kolejnym kroku musimy znaleźć pozycję tego konkretnego wyniku. Użyjemy do tego funkcji PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli wynik z funkcji MIN.

Drugi argument to przeszukiwana_tab, czyli zakres, gdzie chcemy szukać naszej wartości. Wartości tej będziemy szukać w wynikach z funkcji MODUŁ.LICZBY, więc kopiujemy część formuły w trybie edycji komórki z tą funkcją za pomocą skrótu klawiszowego Ctrl+C, a następnie wklejamy ją w miejsce drugiego argumentu funkcji PODAJ.POZYCJĘ za pomocą skrótu klawiszowego Ctrl+V.

Podsumowując - wartości minimalnej szukamy w całej tablicy z wartościami uzyskanymi z odejmowania.

Trzeci argument to typ_porównania, wybieramy Dokładne dopasowanie, czyli w formule wpisujemy wartość 0. Zapis funkcji powinien wyglądać następująco:

=PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY(A2:A121-D2)); MODUŁ.LICZBY(A2:A121-D2);0)

Możemy podejrzeć sobie wynik tej funkcji w trybie edycji komórki za pomocą klawisza F9. Otrzymamy pozycję wiersza z poszukiwanym wynikiem (rys. 14).

Rys. 14 – pozycja wiersza z szukaną wartością

Z podglądu formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z.

Kolejnym etapem jest znalezienie stanu wody z tego konkretnego wiersza w danych. Do tego celu użyjemy funkcji INDEKS. Pierwszym argumentem funkcji jest tablica, czyli zakres, z którego funkcja ma nam zwrócić wynik. Zaznaczamy zakres całej kolumny B (bez nagłówka) za pomocą skrótu klawiszowego Ctrl+Shift+Strzałka w dół, następnie skrótem klawiszowym Ctrl+Backspace można wrócić do formuły.

Drugim argumentem funkcji jest nr_wiersza, który został wygenerowany z funkcji PODAJ.POZYCJĘ. Zapis funkcji powinien wyglądać następująco:

=INDEKS(B2:B121; PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY(A2:A121-D2)); MODUŁ.LICZBY(A2:A121-D2);0))

Należy pamiętać o zatwierdzeniu powyższej formuły skrótem klawiszowym Ctrl+Shift+Enter, aby otrzymać prawidłowy wynik (rys. 15).

Rys. 15 – Wynik funkcji INDEKS

Otrzymany wynik stanu wody jest prawidłowy, natomiast występuje w złym formatowaniu. Należy zmienić formatowanie na ogólne za pomocą skrótu klawiszowego Ctrl+Shift+~ (tylda). Otrzymany wynik przedstawia rys. 16.

Rys. 16 – Wynik funkcji INDEKS w formatowaniu ogólnym

Użyte formuły działają dynamicznie, tzn. jeśli zostanie zmieniona godzina w szukanym czasie, to automatycznie zmieni się wynik (rys. 17).

Rys. 17 – Wynik funkcji INDEKS dla zmienionej szukanej daty

Dla daty 2019-09-03 godz. 15.00 najbliższym czasem będzie 2019-09-03 godz. 12.02 i właśnie dla tego czasu funkcja INDEKS zwróci wynik stanu wody.

Polecamy także:

• Jak działa X.WYSZUKAJ – najnowsza funkcja Excela!

• Jak obliczyć wartość: średnią, medianę i dominantę?