При використанні формул пошуку в Excel (таких як VLOOKUP, XLOOKUP або INDEX/MATCH), мета полягає в тому, щоб знайти відповідне значення і отримати це значення (або відповідне значення в тому ж рядку/стовпці) як результат.
Але в деяких випадках замість отримання значення може знадобитися формула повернути адресу комірки значення.
Це може бути особливо корисним, якщо у вас великий набір даних і ви хочете дізнатися точну позицію результату формули пошуку.
У Excel є деякі функції, призначені саме для цього.
У цьому уроці я покажу вам, як ви можете знайти та повернути адресу комірки замість значення в Excel за допомогою простих формул.
Пошук та повернення адреси клітинки за допомогою функції АДРЕС
Функція ADDRESS в Excel призначена саме для цього.
Він бере рядок і номер стовпця і дає вам адресу комірки цієї конкретної комірки.
Нижче наведено синтаксис функції ADDRESS:
= АДРЕСА (номер рядка, номер стовпця, [абс. Число], [a1], [текст аркуша])
де:
- row_num: номер рядка клітинки, для якої потрібна адреса комірки
- column_num: Номер стовпця клітинки, для якої потрібна адреса
- [abs_num]: Необов’язковий аргумент, де можна вказати, чи потрібно посилання на клітинку бути абсолютним, відносним чи змішаним.
- [a1]: Необов’язковий аргумент, де можна вказати, чи потрібно посилання у стилі R1C1 або A1
- [text_text]: Необов’язковий аргумент, де можна вказати, чи потрібно додати назву аркуша разом з адресою комірки чи ні
Тепер візьмемо приклад і подивимося, як це працює.
Припустимо, є набір даних, як показано нижче, де я маю ідентифікатор працівника, його ім’я та відділ, і я хочу швидко дізнатися адресу комірки, яка містить відділ для ідентифікатора співробітника KR256.
Нижче наведена формула, яка дозволить це зробити:
= АДРЕСА (МАТЧ ("KR256", A1: A20,0), 3)
У наведеній вище формулі я використовував функцію MATCH, щоб дізнатися номер рядка, що містить даний ідентифікатор працівника.
А оскільки відділ знаходиться у стовпці С, я використав 3 як другий аргумент.
Ця формула чудово працює, але у неї є один недолік - вона не працюватиме, якщо додати рядок над набором даних або стовпець зліва від набору даних.
Це тому, що коли я вказую другий аргумент (номер стовпця) як 3, він жорстко кодується і не змінюється.
У разі, якщо я додаю будь -який стовпець зліва від набору даних, формула рахуватиме 3 стовпці від початку аркуша, а не від початку набору даних.
Отже, якщо у вас є фіксований набір даних і вам потрібна проста формула, це буде добре.
Але якщо вам потрібно, щоб це було більш надійним, скористайтеся тим, що описано в наступному розділі.
Пошук та повернення адреси клітини за допомогою функції CELL
Хоча функція ADDRESS була створена спеціально для того, щоб дати вам посилання на клітинку із зазначеним номером рядка та стовпця, існує ще одна функція, яка також робить це.
Це називається функція CELL (і вона може дати вам набагато більше інформації про клітинку, ніж функція ADDRESS).
Нижче наведено синтаксис функції CELL:
= CELL (info_type, [посилання])
де:
- info_type: інформація про потрібну клітинку. Це може бути адреса, номер стовпця, назва файлу тощо.
- [посилання]: Необов’язковий аргумент, де можна вказати посилання на клітинку, для якої вам потрібна інформація про клітинку.
Тепер давайте розглянемо приклад, де ви можете скористатися цією функцією для пошуку та отримання посилання на клітинку.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете швидко дізнатися адресу комірки, яка містить відділ для ідентифікатора співробітника KR256.
Нижче наведена формула, яка дозволить це зробити:
= CELL ("адреса", ІНДЕКС ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
Наведена вище формула досить проста.
Я використав формулу INDEX як другий аргумент, щоб отримати відділ для ідентифікатора співробітника KR256.
А потім просто загорнув його в функцію CELL і попросив повернути адресу комірки з таким значенням, яке я отримую з формули INDEX.
Тепер ось секрет, чому це працює - формула INDEX повертає значення пошуку, коли ви надаєте йому всі необхідні аргументи. Але в той же час він також повертає посилання на клітинку цієї одержуваної комірки.
У нашому прикладі формула INDEX повертає “Продажі” як отримане значення, але водночас ви також можете використовувати її, щоб дати вам посилання на клітинку цього значення замість самого значення.
Зазвичай, коли ви вводите формулу ІНДЕКС у клітинку, вона повертає значення, тому що саме цього і очікується. Але в сценаріях, де потрібне посилання на клітинку, формула INDEX дасть вам посилання на клітинку.
У цьому прикладі це саме те, що він робить.
І найкраща частина використання цієї формули полягає в тому, що вона не прив’язана до першої клітинки на аркуші. Це означає, що ви можете вибрати будь -який набір даних (який може бути будь -де на аркуші), використовувати формулу INDEX для регулярного пошуку, і він все одно дасть вам правильну адресу.
І якщо ви вставите додатковий рядок або стовпець, формула відповідно зміниться, щоб дати правильну адресу комірки.
Отже, це дві прості формули, за допомогою яких можна шукати і шукати знайти та повернути адресу комірки замість значення в Excel.
Сподіваюся, цей підручник був вам корисним.
