Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція адреси Excel в Excel, щоб повернути адресу комірки у вигляді тексту.
Огляд функції АДРЕСА
Функція ADDRESS Повертає адресу комірки у вигляді тексту.
Щоб скористатися функцією ADDRESS Excel Worksheet, виберіть клітинку та введіть:
(Зверніть увагу, як виглядають вхідні дані формул)
Функція ADDRESS Синтаксис та входи:
| 1 | = Адреса |
row_num - Номер рядка для посилання. Приклад: 5 для рядка 5.
col_num - Номер стовпця для довідки. Приклад: 5 для стовпця E. Ви не можете ввести “E” для стовпця E
абс - [необов’язково] Число, що означає, чи посилання має містити абсолютні чи відносні посилання на рядок/стовпець. 1 для абсолютного. 2 для абсолютного рядка/відносного стовпця. 3 для відносного рядка/абсолютного стовпця. 4 для родичів.
a1 - [необов’язково]. Число, яке вказує, чи використовувати стандартний формат посилання на клітинку (A1) або формат R1C1. 1/TRUE для стандарту (за замовчуванням). 0/НЕВІРНО для R1C1.
аркуш_тексту - [необов’язково] Назва робочого аркуша для використання. За замовчуванням для поточного аркуша.
Що таке функція АДРЕСА?
Функція ADDRESS є трохи унікальною функцією. Велику частину часу в електронній таблиці ми повідомляємо комп’ютеру посилання на клітинку, і вона дає нам значення з цієї комірки. За допомогою ADDRESS ми збираємось замість цього створити назву клітинки. Ця адреса може бути відносною або абсолютною, у стилі A1 або R1C1, і може включати або не включати назву аркуша. Здебільшого ця функція не має багато практичного застосування, але дізнатися про неї може бути цікаво. Коли він використовується, він зазвичай поєднується з іншими функціями, як ми побачимо нижче.
Основний приклад
Скажімо, ми хочемо створити посилання на клітинку в 4го стовпець і 1вул ряд, він же клітина D1. Ми можемо використовувати макет, зображений тут:
Наша формула в A3 проста
| 1 | = АДРЕСА (B1, B2) |
Зауважте, що, не вказуючи конкретного аргументу відносного/абсолютного, ми отримали все абсолютне. Ми також отримали стандартний стиль посилання типу А1 без назви аркуша.
У поєднанні з INDIRECT
Як ми вже говорили, функція ADDRESS сама по собі насправді не дала нам нічого корисного. Ми могли б поєднати його з функцією INDIRECT, щоб отримати певну функціональність. Розглянемо цей макет, де у нас є список елементів у стовпці D. Якщо ми зберегли ту саму формулу, що і раніше, ми створимо посилання на D1 так
| 12 | = АДРЕСА (B1, B2)= $ D $ 1 |
Розмістивши функцію адреси всередині функції INDIRECT, ми зможемо використовувати згенеровану посилання на клітинку та використовувати її практично. INDIRECT візьме посилання на "$ D $ 1" і використовуватиме його для отримання значення з цієї комірки.
| 123 | = НЕПРЯМИЙ (АДРЕСА (B1, B2)= НЕПРЯМО ($ D $ 1)= "Яблуко" |
Примітка: Хоча вищесказане дає хороший приклад того, як зробити функцію ADDRESS корисною, це не найкраща формула для нормального використання. Він вимагав двох функцій, і через НЕПРОКИЙ він буде мінливим за своєю природою. Кращою альтернативою було б використання INDEX таким чином: = INDEX (1: 1048576, B1, B2)
Адреса конкретного значення
Іноді, коли у вас є великий список елементів, вам потрібно знати, де в списку є елемент. Розгляньте цю таблицю балів від учнів. Ми продовжили розрахунок мінімальних, середніх та максимальних значень цих показників у клітинках E2: G2.
Ми могли б відфільтрувати нашу таблицю для кожного з цих елементів, щоб знайти, де він знаходиться (знову ж таки, уявіть, що це набагато більший список), або ми могли б навіть застосувати умовне форматування, щоб воно візуально з'явилося для користувача. Але, якщо список складає тисячі рядків, ми не хочемо прокручувати так далеко, щоб побачити, чого ми хочемо. Замість цього ми будемо використовувати значення в E2: G2 для визначення адреси клітинок, що містять наші значення. Для цього ми будемо використовувати функцію MATCH з ADDRESS. Пам’ятайте, що MATCH поверне відносне положення значення в межах діапазону.
Тоді наша формула в E3 така:
| 1 | = АДРЕСА (МАТЧ (E2, $ B: $ B, 0), 2) |
Ми можемо скопіювати цю саму формулу в G3, і зміниться лише посилання E2, оскільки це єдине відносне посилання. Озираючись на E3, функція MATCH змогла знайти значення 98 у 5го у рядку стовпця B. Наша функція ADDRESS тоді використовувала це для створення повної адреси “$ B $ 5”.
Перекладіть літери стовпців з цифр
До цього моменту всі наші приклади дозволяли функції ADDRESS повертати абсолютну посилання. Наступний приклад поверне відносне посилання. У цій таблиці ми хочемо ввести номер у стовпці А та повернути назву відповідної літери стовпця.
Для досягнення нашої мети функція ADDRESS поверне посилання на рядок 1 у відносному форматі, а потім видалимо “1” з текстового рядка, щоб у нас залишилася буква (и). Розглянемо в нашій таблиці рядок 3, де наш вхід 13. Наша формула в B3 така
| 1 | = ЗАМІНА (АДРЕСА (1, A3, 4), "1", "") |
Зверніть увагу, що ми дали 3rd аргумент у функції ADDRESS, яка керує відносним або абсолютним посиланням. Функція ADDRESS видасть “M1”, а потім функція SUBSTITUTE видалить “1”, так що нам залишиться лише “M”.
Знайдіть адресу Іменованих діапазонів
В Excel ви можете називати діапазон або діапазони клітинок, дозволяючи просто посилатися на іменований діапазон замість посилання на клітинку.
Більшість іменованих діапазонів є статичними, тобто вони завжди відносяться до одного і того ж діапазону. Тим не менш, ви також можете створити динамічні іменовані діапазони, які змінюються за розміром на основі деяких формул (ів).
За допомогою динамічного іменованого діапазону вам може знадобитися знати точну адресу, на яку вказує ваш іменований діапазон. Ми можемо це зробити за допомогою функції ADDRESS.
У цьому прикладі ми розглянемо, як визначити адресу для нашого іменованого діапазону під назвою «Оцінки».
Повернемо наш стіл від раніше:
Щоб отримати адресу діапазону, потрібно знати верхню ліву клітинку та нижню ліву клітинку. Першу частину досить легко виконати за допомогою функцій ROW і COLUMN. Наша формула в E1 може бути такою
| 1 | = АДРЕСА (ROW (оцінки), COLUMN (оцінки)) |
Функція ROW поверне рядок першої клітинки в нашому діапазоні (який буде 1), а COLUMN зробить те ж саме для стовпця (також 1).
Щоб отримати нижню праву клітинку, ми будемо використовувати функції РЯДКИ та СТОЛКИ. Оскільки ми можемо визначити початкову точку нашого діапазону, якщо обчислити, наскільки високий діапазон, і відняти нашу вихідну точку, ми отримаємо правильну кінцеву точку. Формула цього виглядає так
| 1 | = АДРЕСА (РЯДИ (Оцінки) -РОК (Оцінки) +1, СТОЛКИ (Оцінки) -Стовпчик (Оцінки) +1) |
Нарешті, щоб об’єднати все це в один рядок, ми можемо просто об’єднати значення разом із двокрапкою посередині. Формула в Е3 може бути
| 1 | = E1 & ":" & E2 |
Примітка: Хоча нам вдалося визначити адресу, діапазон, наша функція ADDRESS визначала, чи варто посилати посилання як відносні чи абсолютні. Ваші динамічні діапазони матимуть відносні посилання, які ця техніка не сприймає.
2nd Примітка: Ця техніка працює лише на безперервному діапазоні імен. Якби у вас був іменований діапазон, який визначався як щось на зразок цієї формули
| 1 | = A1: B2, A5: B6 |
тоді вищенаведена техніка призведе до помилок.
додаткові нотатки
Використовуйте функцію ADDRESS для створення адреси з заданого номера рядка та стовпця. Важливо: Ви повинні ввести стовпець номер. Введення літери стовпця призведе до помилки. При необхідності можна скористатися функцією стовпця для обчислення номера стовпця для посилання на клітинку.
Abs_num дозволяє перемикати абсолютні та відносні посилання на клітинки.
1,2,3,4 a1, $ a $ 2… відносне/абсолютне тощо…
Потім вкажіть, чи використовувати a1 або R1C1. Режим a1 - це стандартний режим, де на клітинки посилаються літерою стовпця та номером рядка (наприклад, a4). У режимі R1C1 клітинки посилаються за номерами рядків і стовпців (наприклад, R4C1). a1 - це режим за замовчуванням. Використовуйте це, якщо у вас немає вагомих причин не робити цього.
В останньому аргументі ви можете ввести назву аркуша, якщо посилання на клітинку буде на іншому аркуші. Введіть назву аркуша в оточенні паратез (наприклад, «аркуш3»).
Повернення до списку всіх функцій Excel
АДРЕСА функцію в Google Таблицях
Функція ADDRESS працює точно так само в Google Таблицях, як і в Excel.
