Wyznaczenie adresu komórki z wartością skrajną

Wyszukiwanie wartości maksymalnej jest bardzo częstym działaniem wykonywanym podczas analizy danych. Niekiedy jednak potrzebna jest wiedza nie tyle o wielkości wartości maksymalnej, ile jej lokalizacja w tabeli (czyli adres). Funkcja MAX() nie pozwoli ci wyznaczyć adresu komórki przechowującej poszukiwane ekstremum, ale z wykorzystaniem formuły o przedstawionej konstrukcji wykonasz to błyskawicznie:

=KOMÓRKA("adres";(INDEKS(zakres; PODAJ.POZYCJĘ(MAX(zakres);zakres;0))))

Formuła wykorzystuje trzykrotnie ten sam zakres, w którym zgromadzone są dane. Spełniony musi być jednak podstawowy warunek: zakres ma być jednowymiarowy. Rozpatrywać możesz zatem komórki w jednej kolumnie lub w jednym wierszu.

Jak widać na filmie umieszczonym poniżej, formuła zwróciła w wyniku adres bezwzględny poszukiwanej komórki.

Zastosowano formułę analizującą zakres B4:B15:

=KOMÓRKA("adres";(INDEKS(B4:B15;PODAJ. POZYCJĘ(MAX(B4:B15);B4:B15;0))))

Wynik zwrócony w postaci adresu $B$5 mówi jedno: w komórce o tym adresie znajdziesz wartość maksymalną. W przykładzie jest to liczba 253.

Wyjaśnienie działania formuły:

Formuła wykorzystuje cztery funkcje arkusza działające w następujący sposób:

Funkcja MAX() odszukuje największą wartość w podanym zakresie komórek.

Funkcja PODAJ.POZYCJĘ() zwraca względną lokalizację (pozycję) wartości wyznaczonej funkcją MAX(), wewnątrz analizowanego zakresu danych.

Ostatni parametr funkcji PODAJ.POZYCJĘ() określa sposób wyszukiwania żądanej wartości w podanym zakresie. W naszym przypadku podany parametr 0 oznacza, że funkcja zwróci pierwszą wartość z zakresu, która będzie równa wartości wyznaczonej funkcją MAX().

Za pomocą funkcji INDEKS() przekażesz do zewnętrznej funkcji odwołanie do odszukanej komórki, znajdującej się na przecięciu kolumny wyznaczonej podanym zakresem oraz wiersza wyznaczonego funkcją PODAJ.POZYCJĘ().

Funkcja informacyjna KOMÓRKA() dzięki podaniu jej argumentu tekstowego adres zwraca w wyniku adres (lokalizację) poszukiwanej wartości maksymalnej.

Poprzez zastąpienie w podanej formule funkcji MAX() funkcją MIN() w prosty sposób wyznaczysz adres komórki przechowującej najmniejszą wartość w rozpatrywanym zakresie. Oczywiście zasada działania formuły nie ulegnie zmianie.

R1TNAfcDtlzvs

Nagranie filmowe dotyczące wyznaczania adresu komórki z wartością skrajną.

Nagranie filmowe dotyczące wyznaczania adresu komórki z wartością skrajną.

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

Nagranie filmowe dotyczące wyznaczania adresu komórki z wartością skrajną.

Pobierz plik z danymi:

Polecenie 1

Przygotuj arkusz, w którym w kolumnie A zapiszesz daty, a w kolumnach B i C dokładne godziny, o których się budzisz i zasypiasz w ciągu kolejnych dni. Wykorzystując poznane funkcje, znajdź najmniejszą i największą wartość z kolumny B. Wskaż je oraz daty, w których wystąpiły.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Rb7dZTEPVxveT

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B i C wprowadzono dane dotyczące godzin pobudki oraz pójścia spać w kolejnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Pobudka, w komórce C1 Pójście spać. W kolumnie A, w komórkach od A2 do A9 wpisano daty. W kolumnie B, w komórkach od B2 do B9 wpisano godziny pobudek. W kolumnie C, w komórkach od C2 do C9 wpisano godziny pójścia spać. Obok danych komórkę D4 zatytułowano najpóźniejsza pobudka, w komórce D5 wpisano datę. Dodatkowo zaprezentowano sposób obliczania najwcześniejszej pobudki. Formułę obliczania należy wpisać w komórce D2. Brzmi ona następująco: =KOMÓRKA("zawartość";(INDEKS(A2:A9;PODAJ.POZYCJĘ(MIN(B2:B9);B2:B9;0)))).

RbPksD5x6tZlV

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B i C wprowadzono dane dotyczące godzin pobudki oraz pójścia spać w kolejnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Pobudka, w komórce C1 Pójście spać. W kolumnie A, w komórkach od A2 do A9 wpisano daty. W kolumnie B, w komórkach od B2 do B9 wpisano godziny pobudek. W kolumnie C, w komórkach od C2 do C9 wpisano godziny pójścia spać. Obok danych komórkę D1 zatytułowano najwcześniejsza pobudka, w komórce D2 wpisano datę. Dodatkowo zaprezentowano sposób obliczania najpóźniejszej pobudki. Formułę obliczania należy wpisać w komórce D5. Brzmi ona następująco: =KOMÓRKA("zawartość";(INDEKS(A2:A9;PODAJ.POZYCJĘ(MAx(B2:B9);B2:B9;0)))).

Polecenie 2

Zmodyfikuj przygotowany arkusz tak, aby obliczył długość snu dla każdego dnia. Policz średnią arytmetyczną z tych danych i odnajdź noce, podczas których sen trwał najkrócej i najdłużej.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Ry7qqgBVziu9U

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B i C wprowadzono dane dotyczące godzin pobudki, pójścia spać oraz długości snu w kolejnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 data, w komórce B1 pobudka, w komórce C1 pójście spać, w komórce D1 długość snu. W kolumnie A, w komórkach od A2 do A10 wpisano daty. W kolumnie B, w komórkach od B3 do B10 wpisano godziny pobudek. W kolumnie C, w komórkach od C2 do C10 wpisano godziny pójścia spać. W kolumnie D, w komórkach od D3 do D9 wpisano długość snu w godzinach. Dodatkowo komórkę E4 zatytułowano Kiedy sen trwał najdłużej?, w komórce E5 wpisano datę. Dodatkowo zaprezentowano sposób obliczania dnia, w którym sen trwał najkrócej. Formułę obliczania należy wpisać w komórce E2. Brzmi ona następująco: =KOMÓRKA("zawartość";(INDEKS(A2:A9;PODAJ.POZYCJĘ(MIN(D3:D9);D3:D9;0)))).

R1LPwgnEhvGJJ

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B i C wprowadzono dane dotyczące godzin pobudki, pójścia spać oraz długości snu w kolejnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 data, w komórce B1 pobudka, w komórce C1 pójście spać, w komórce D1 długość snu. W kolumnie A, w komórkach od A2 do A10 wpisano daty. W kolumnie B, w komórkach od B3 do B10 wpisano godziny pobudek. W kolumnie C, w komórkach od C2 do C10 wpisano godziny pójścia spać. W kolumnie D, w komórkach od D3 do D9 wpisano długość snu w godzinach. Dodatkowo komórkę E1 zatytułowano Kiedy sen trwał najkrócej?, w komórce E2 wpisano datę. Dodatkowo zaprezentowano sposób obliczania dnia, w którym sen trwał najdłużej. Formułę obliczania należy wpisać w komórce E5. Brzmi ona następująco: =KOMÓRKA("zawartość";(INDEKS(A2:A9;PODAJ.POZYCJĘ(MAX(D3:D9);D3:D9;0)))).