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:
1 |
=SUMA.ILOCZYNÓW((B2:F6=A9)*WIERSZ(B2:F6)) |
1 |
=SUMA.ILOCZYNÓW((B2:F6=A9)*NR.KOLUMNY(B2:F6)) |
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:
1 |
=SUMA.ILOCZYNÓW(MAX((B2:F6=A9)*WIERSZ(B2:F6))) |
Numer kolumny:
1 |
=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:
1 |
=SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*WIERSZ(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1)) |
Numer kolumny:
1 |
=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:
1 |
=ADRES(Numer wiersza, numer kolumny) |
1 |
=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:
1 |
=INDEKS(cała kolumna z nagłówkami wiersza, numer wiersza) |
1 |
=INDEX(A:A;SUMPRODUCT(SMALL((B2:F6=A9)*ROW(B2:F6);COUNTIFS(B2:F6;"<>"&A9)+1))) |
Nagłówek kolumny
1 |
=INDEX(cały wiersz z nagłówkami kolumn, 1, numer kolumny) |
1 |
=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.