Podczas korzystania z formuł wyszukiwania w programie Excel (takich jak WYSZUKAJ.PIONOWO, WYSZUKAJ.X lub INDEKS/PODSTAW) celem jest znalezienie pasującej wartości i uzyskanie tej wartości (lub odpowiadającej wartości w tym samym wierszu/kolumnie) jako wyniku.
Ale w niektórych przypadkach zamiast pobierania wartości możesz chcieć, aby formuła zwracała adres komórki wartości.
Może to być szczególnie przydatne, jeśli masz duży zestaw danych i chcesz poznać dokładną pozycję wyniku formuły wyszukiwania.
W programie Excel jest kilka funkcji, które mają dokładnie to zrobić.
W tym samouczku pokażę Ci, jak możesz znajdź i zwróć adres komórki zamiast wartości w Excelu za pomocą prostych formuł.
Wyszukaj i zwróć adres komórki za pomocą funkcji ADRES
Do tego właśnie służy funkcja ADRES w programie Excel.
Pobiera numer wiersza i kolumny i podaje adres komórki tej konkretnej komórki.
Poniżej znajduje się składnia funkcji ADRES:
=ADRES(numer_wiersza;numer_kolumny;[numer_abs];[a1];[tekst_arkusza])
gdzie:
- row_num: Numer wiersza komórki, dla której chcesz uzyskać adres komórki
- column_num: Numer kolumny komórki, dla której chcesz uzyskać adres
- [numer_abs]: opcjonalny argument, w którym można określić, czy odwołanie do komórki ma być bezwzględne, względne czy mieszane.
- [a1]: opcjonalny argument, za pomocą którego można określić, czy odwołanie ma być w stylu W1K1, czy w stylu A1
- [tekst_arkusza]: opcjonalny argument, w którym możesz określić, czy chcesz dodać nazwę arkusza wraz z adresem komórki, czy nie
Teraz weźmy przykład i zobaczmy, jak to działa.
Załóżmy, że istnieje zestaw danych, jak pokazano poniżej, w którym mam identyfikator pracownika, jego imię i nazwisko oraz dział, i chcę szybko poznać adres komórki, który zawiera dział dla identyfikatora pracownika KR256.
Poniżej znajduje się formuła, która to zrobi:
=ADRES(DOPASUJ("KR256";A1:A20,0);3)
W powyższym wzorze użyłem funkcji PODAJ.POZYCJĘ, aby znaleźć numer wiersza, który zawiera podany identyfikator pracownika.
A ponieważ dział jest w kolumnie C, użyłem 3 jako drugiego argumentu.
Ta formuła działa świetnie, ale ma jedną wadę - nie zadziała, jeśli dodasz wiersz powyżej zestawu danych lub kolumnę po lewej stronie zestawu danych.
Dzieje się tak, ponieważ kiedy określam drugi argument (numer kolumny) jako 3, jest on zakodowany na stałe i się nie zmienia.
W przypadku dodania dowolnej kolumny po lewej stronie zestawu danych, formuła policzy 3 kolumny od początku arkusza roboczego, a nie od początku zestawu danych.
Tak więc, jeśli masz stały zestaw danych i potrzebujesz prostej formuły, to zadziała dobrze.
Ale jeśli potrzebujesz tego, aby być bardziej niezawodnym, użyj tego omówionego w następnej sekcji.
Wyszukaj i zwróć adres komórki za pomocą funkcji CELL
Chociaż funkcja ADRES została stworzona specjalnie, aby dać odwołanie do komórki o określonym numerze wiersza i kolumny, istnieje inna funkcja, która również to robi.
Nazywa się to funkcją KOMÓRKA (i może dać o wiele więcej informacji o komórce niż funkcja ADRES).
Poniżej znajduje się składnia funkcji KOMÓRKA:
= KOMÓRKA(typ_informacji; [odniesienie])
gdzie:
- info_type: informacje o wybranej komórce. Może to być adres, numer kolumny, nazwa pliku itp.
- [odniesienie]: Argument opcjonalny, w którym można określić odwołanie do komórki, dla której potrzebujesz informacji o komórce.
Zobaczmy teraz przykład, w którym możesz użyć tej funkcji, aby wyszukać i uzyskać odwołanie do komórki.
Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz szybko poznać adres komórki, który zawiera dział dla identyfikatora pracownika KR256.
Poniżej znajduje się formuła, która to zrobi:
= KOMÓRKA("adres"; INDEKS($A$1:$D$20;MATCH("KR256";$A$1:$A$20;3))
Powyższy wzór jest dość prosty.
Użyłem formuły INDEX jako drugiego argumentu, aby uzyskać dział dla identyfikatora pracownika KR256.
A potem po prostu zawinąłem go w funkcję KOMÓRKA i poprosiłem o zwrócenie adresu komórki o tej wartości, którą otrzymałem z formuły INDEKS.
Teraz tutaj jest tajemnica, dlaczego to działa - formuła INDEKS zwraca wartość wyszukiwania po podaniu wszystkich niezbędnych argumentów. Ale jednocześnie zwróciłby również odwołanie do komórki wynikowej.
W naszym przykładzie formuła INDEKS zwraca „Sprzedaż” jako wartość wynikową, ale jednocześnie możesz jej użyć, aby podać odwołanie do komórki tej wartości zamiast samej wartości.
Zwykle po wprowadzeniu formuły INDEKS w komórce zwraca ona wartość, ponieważ właśnie tego oczekuje. Ale w scenariuszach, w których wymagane jest odwołanie do komórki, formuła INDEKS daje odwołanie do komórki.
W tym przykładzie właśnie to robi.
A najlepsze w używaniu tej formuły jest to, że nie jest ona powiązana z pierwszą komórką w arkuszu. Oznacza to, że możesz wybrać dowolny zestaw danych (który może znajdować się w dowolnym miejscu w arkuszu), użyć formuły INDEKS do regularnego wyszukiwania i nadal poda poprawny adres.
A jeśli wstawisz dodatkowy wiersz lub kolumnę, formuła dostosuje się odpowiednio, aby uzyskać poprawny adres komórki.
Są to więc dwie proste formuły, których możesz użyć, aby wyszukać i znajdź i zwróć adres komórki zamiast wartości w Excelu.
Mam nadzieję, że ten samouczek okazał się przydatny.
