Obliczanie rzeczywistej k‑tej najmniejszej wartości w tabeli

Za pomocą funkcji MIN.K() możesz szybko i w prosty sposób wyznaczyć k‑tą najmniejszą wartość w analizowanej tabeli. Niestety łatwo popełnić istotny błąd przy stosowaniu tej funkcji: jeśli w rozpatrywanym zakresie komórek wartość najmniejsza występuje wielokrotnie, otrzymany wynik może być niezgodny z oczekiwaniami. Przykładowo: gdy wartość minimalna występuje w zestawieniu trzykrotnie, to zarówno pierwsza, jak i druga oraz trzecia wartość wyznaczona funkcją MIN.K() będzie taka sama.

Aby wyznaczyć rzeczywistą k‑tą najmniejszą wartość w tabeli, zastosuj formułę wykorzystującą funkcje MIN.K(), JEŻELI() oraz CZĘSTOŚĆ(). Konstrukcja omawianej formuły jest prosta i wygląda następująco:

=MIN.K(JEŻELI((CZĘSTOŚĆ(zakres;zakres)>0);zakres;"");k)

Formuła wymaga podania dwóch parametrów: zakres (jednowymiarowy, podany trzykrotnie) oraz parametr k do określenia, którą najmniejszą wartość chcesz wyszukać.

Działanie formuły

=MIN.K(JEŻELI((CZĘSTOŚĆ(C3:C8;C3:C8)>0);C3:C8;"");C10)

możesz zobaczyć w arkuszu na filmie.

Wartość najmniejsza (10) występuje w każdej z kolumn trzykrotnie. Funkcja MIN.K() zwróciłaby więc wartość 10 dla parametru k równego 1, 2 i 3. Podana formuła zwraca rzeczywiste kolejne wartości najmniejsze.

Wyjaśnienie działania formuły:

Funkcja CZĘSTOŚĆ() sprawdza, ilokrotnie w badanym zakresie występuje każda z wartości. Wynikiem działania tej funkcji jest tablica danych prezentująca częstotliwość występowania każdej wartości. W zwracanej tablicy każdej wartości odpowiada tylko wtedy częstość większa od zera, gdy opisywana jest pierwszy raz.

Funkcja JEŻELI(), dzięki zastosowaniu warunku (>0) przekazuje do funkcji MIN.K() tylko dane z jednokrotnie występującą każdą wartością. Spośród otrzymanego zbioru danych funkcja MIN.K() wyznacza k‑tą wartość, zgodnie z parametrem k podanym jako odniesienie do komórki.

Podstawiając zamiast MIN.K() funkcję MAX.K(), błyskawicznie przeanalizujesz zakresy danych pod kątem występowania k‑tej największej wartości.

RMr9TGQ7Sx8iD

Nagranie filmowe dotyczące obliczania rzeczywistej k‑tej najmniejszej wartości w tabeli.

Nagranie filmowe dotyczące obliczania rzeczywistej k‑tej najmniejszej wartości w tabeli.

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

Nagranie filmowe dotyczące obliczania rzeczywistej k‑tej najmniejszej wartości w tabeli.

Pobierz plik z danymi:

Polecenie 1

Przygotuj arkusz w którym zestawisz liczbę państw graniczących z Polską, oraz liczbę państw sąsiadujących z Rosją, Litwą, Białorusią, Ukrainą, Słowacją, Czechami oraz Niemcami. Używając poznanych funkcji odszukaj po trzy rzeczywiste największe i najmniejsze liczby sąsiadów.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R1dFeZkZxoVP7

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące państw wraz z liczbą sąsiadujących z nimi krajów. W arkuszu kolejno dodano opisy: w komórce A1 Państwo, w komórce B1 Liczba sąsiadujących krajów. W kolumnie A, w komórkach od A2 do A9 wpisano nazwy państw. W kolumnie B, w komórkach od B2 do B9 wpisano liczbę krajów. Dodatkowo zaprezentowano sposób obliczania krajów z największą ilością sąsiadów. Formułę obliczania należy wpisać w komórce E2. Brzmi ona następująco: =MAX.K(JEŻELI((CZĘSTOŚĆ(B2:B9;B2:B9)>0);B2:B9;"");D2).

Polecenie 2

Zmodyfikuj poprzedni arkusz tak, aby oprócz odnalezionych wartości zaprezentowana została nazwa kraju lub nazwy jeśli kilka krajów ma taką samą liczbę sąsiadów.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RCrLcntod0pNx

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące państw wraz z liczbą sąsiadujących z nimi krajów. W arkuszu kolejno dodano opisy: w komórce A1 Państwo, w komórce B1 Liczba sąsiadujących krajów. W kolumnie A, w komórkach od A2 do A9 wpisano nazwy państw. W kolumnie B, w komórkach od B2 do B9 wpisano liczbę krajów. Dodatkowo zaprezentowano sposób obliczania łączenia krajów mających taką samą liczbę sąsiadów. Formułę obliczania należy wpisać w komórce F2. Brzmi ona następująco: =ZŁĄCZ.TEKST(JEŻELI$B$2:$B$9=E2;$A$2:$A$9&" ";"");" ").