зміст
Вчора на марафоні 30 функцій Excel за 30 днів ми знайшли елементи масиву за допомогою функції MATCH (ПОШУК) і виявив, що він чудово працює в команді з іншими функціями, такими як ВЛООКУП (VLOOKUP) і ІНДЕКС (ІНДЕКС).
20-й день нашого марафону ми присвятимо вивченню функції АДРЕСА (АДРЕСА). Він повертає адресу комірки в текстовому форматі з використанням номера рядка та стовпця. Нам потрібна ця адреса? Чи можна те саме зробити з іншими функціями?
Давайте розглянемо деталі функції АДРЕСА (АДРЕСА) та вивчити приклади роботи з ним. Якщо у вас є додаткова інформація чи приклади, поділіться ними в коментарях.
Функція 20: АДРЕСА
функція АДРЕСА (ADDRESS) повертає посилання на клітинку як текст на основі номера рядка та стовпця. Він може повертати абсолютну або відносну адресу у стилі посилання. A1 or R1C1. Крім того, назву аркуша можна включити в результат.
Як можна використовувати функцію АДРЕСА?
функція АДРЕСА (ADDRESS) може повертати адресу комірки або працювати в поєднанні з іншими функціями, щоб:
- Отримати адресу комірки за номером рядка та стовпця.
- Знайдіть значення клітинки, знаючи номер рядка та стовпця.
- Повертає адресу комірки з найбільшим значенням.
Синтаксис ADDRESS (АДРЕСА)
функція АДРЕСА (ADDRESS) має такий синтаксис:
ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (link_type) – якщо дорівнює 1 або не вказано взагалі, функція поверне абсолютну адресу ($A$1). Щоб отримати відносну адресу (A1), використовуйте значення 4. Інші варіанти: 2=1 австралійський долар, 3=$A1.
- a1 – якщо TRUE (ІСТИНА) або не вказано взагалі, функція повертає посилання в стилі A1, якщо FALSE (ЛОЖЬ), то в стилі R1C1.
- лист_текст (ім’я_аркуша) – ім’я аркуша можна вказати, якщо ви хочете побачити його в результатах, які повертає функція.
Пастки АДРЕСА
функція АДРЕСА (ADDRESS) повертає лише адресу комірки у вигляді текстового рядка. Якщо вам потрібно значення клітинки, використовуйте його як аргумент функції НЕПРАВИЛЬНО (НЕПРЯМО) або скористайтеся однією з альтернативних формул, наведених у прикладі 2.
Приклад 1: отримати адресу комірки за номером рядка та стовпця
Використання функцій АДРЕСА (АДРЕСА) Ви можете отримати адресу комірки як текст, використовуючи номер рядка та стовпця. Якщо ви введете лише ці два аргументи, результатом буде абсолютна адреса, написана у стилі посилання A1.
=ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Абсолютна чи відносна
Якщо не вказати значення аргументу abs_num (тип_посилання) у формулі, результатом є абсолютне посилання.
Щоб побачити адресу як відносне посилання, ви можете замінити її як аргумент abs_num (reference_type) значення 4.
=ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 або R1C1
Стилізувати посилання R1C1, замість стилю за замовчуванням A1, Ви повинні вказати FALSE для аргументу a1.
=ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Назва аркуша
Останній аргумент - ім'я аркуша. Якщо вам потрібна ця назва в результаті, вкажіть її як аргумент текст_аркуша (назва_аркуша).
=ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Приклад 2: Знайдіть значення клітинки за номером рядка та стовпця
функція АДРЕСА (ADDRESS) повертає адресу комірки як текст, а не як дійсне посилання. Якщо вам потрібно отримати значення клітинки, ви можете скористатися результатом, який повертає функція АДРЕСА (АДРЕСА), як аргумент за НЕПРАВИЛЬНО (НЕПРЯМИЙ). Будемо вивчати функцію НЕПРАВИЛЬНО (НЕПРЯМО) пізніше в марафоні 30 функцій Excel за 30 днів.
=INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
функція НЕПРАВИЛЬНО (INDIRECT) може працювати без функції АДРЕСА (АДРЕСА). Ось як це можна зробити, використовуючи оператор конкатенації “&“, зліпити потрібну адресу в стилі R1C1 і в результаті отримати значення комірки:
=INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
функція ІНДЕКС (INDEX) також може повертати значення комірки, якщо вказано номер рядка та стовпця:
=INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 це перші 5000 рядків аркуша Excel.
Приклад 3: повертає адресу комірки з максимальним значенням
У цьому прикладі ми знайдемо клітинку з максимальним значенням і використаємо функцію АДРЕСА (АДРЕСА), щоб отримати її адресу.
функція MAX (MAX) знаходить максимальне число в стовпці C.
=MAX(C3:C8)
=МАКС(C3:C8)
Далі йде функція АДРЕСА (АДРЕСА) сум MATCH (MATCH), який знаходить номер рядка, і КОЛОНКА (COLUMN), який визначає номер стовпця.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))