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:

Poszukiwanie kolumny:
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:

Numer kolumny:

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:

Numer kolumny:

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:

 

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

Nagłówek wiersza:

Nagłówek kolumny

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. Wymagane pola są oznaczone *