Wyszukiwanie wartości w tabeli, zakresie

Jakiś czas temu napisałem wpis o wyszukiwaniu w tablicy dwu wymiarowej. Szukałem tam jaka była wartości sprzedaży Janka, produktów w kolorze czerwonym (na przykład).

Jeden z czytelników, zwrócił mi uwagę iż trafił na ten wpis szukając rozwiązania innego problemu. Może się zdarzyć, że będziemy chcieli znaleźć w tabeli adres pozycji o określonej wartości. Czyli gdzie znajduje się komórka o wartości, powiedzmy 358,82 (Janek, Czerwony). Na końcu wpisu znajduje się link do przykładowego pliku.

Poniżej posłużę się powyższą tabelą.

Zaczniemy prosto, stworzymy dwie formuły, do wyciągania wiersza i kolumny w której znajduje się poszukiwana wartość. Szukana wartość, będzie się znajdować w komórce A9.

Poszukiwanie wiersza:

=SUMA.ILOCZYNÓW((B2:F6=A9)*WIERSZ(B2:F6))

Poszukiwanie kolumny:

=SUMA.ILOCZYNÓW((B2:F6=A9)*NR.KOLUMNY(B2:F6))

Uzyskany efekt:

Należy pamiętać, że formuła zwraca absolutny numer wiersza. Jeżeli nasza tabela będzie przesunięta, to numery nie będą się zgadzać z względnymi kolumnami i wierszami tabeli. Poniżej okaże się, że nie jest to problem.

Problem jest natomiast inny, w tej chwili poszukujemy unikalnej wartości, takiej, która w tabeli występuje tylko raz. Jeżeli będę chciał w znaleźć wartość 649,74 to formuły zwrócą mi poniższe wyniki:

Są to sumy wszystkich znalezionych wierszy i kolumn. Spróbujmy więc rozwiązać ten problem i znaleźć pierwszą lub ostatnią wartość. Zaczniemy od prostszego przypadku, ostatnia wartość.

Numer wiersza:

=SUMA.ILOCZYNÓW(MAX((B2:F6=A9)*WIERSZ(B2:F6)))

Numer kolumny:

=SUMA.ILOCZYNÓW(MAX((B2:F6=A9)*NR.KOLUMNY(B2:F6)))

Formuły zwracają nam najwyższą wartość kolumny i wiersza.

Jak znaleźć pierwszą wartość? Na pierwszy rzut oka, można by się posłużyć funkcją MIN, należy jednak pamiętać, że w poszukiwanej tabeli mamy dużo zer i MIN zwróci na po prostu zero. Posłużę, się więc funkcją MIN.K, która zwróci mi n-tą najmniejszą wartość. Skąd wiem którą? Pierwsza, druga, trzecia itd. wartość to zera. Muszę więc policzyć ile wartości w tabeli nie równa się poszukiwanej liczbie i dodać 1.

Numer wiersza:

=SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*WIERSZ(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1))

Numer kolumny:

=SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*NR.KOLUMNY(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1))

Uzyskujemy poniższy efekt:

Mamy już prawidłowo wyznaczoną kolumnę i wiersz. Spróbujmy scalić wynik do jednej komórki i uzyskać adres. Posłużę się funkcją ADRES:

=ADRES(Numer wiersza, numer kolumny)

 

=ADRES(SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*WIERSZ(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1));SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*NR.KOLUMNY(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1)))

Na koniec formuły które zwracają nam nagłówki tabeli:

Nagłówek wiersza:

=INDEKS(cała kolumna z nagłówkami wiersza, numer wiersza)
=INDEX(A:A;SUMPRODUCT(SMALL((B2:F6=A9)*ROW(B2:F6);COUNTIFS(B2:F6;"<>"&A9)+1)))

Nagłówek kolumny

=INDEX(cały wiersz z nagłówkami kolumn, 1, numer kolumny)
=INDEX(1:1;1;SUMPRODUCT(SMALL((B2:F6=A9)*COLUMN(B2:F6);COUNTIFS(B2:F6;"<>"&A9)+1)))

Ostatecznie nasze formuły działają tak:

 

Tutaj można znaleźć plik użyty powyżej.

Powyższe formuły można by poprawić na wypadek błędów, jeżeli poszukiwana wartość nie występuje w tabeli, przy pomocy funkcji JEŻELI.BŁĄD.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.