зміст
Щиро вітаю! Ви дійшли до останнього дня марафону 30 функцій Excel за 30 днів. Це була довга та цікава подорож, під час якої ви дізналися багато корисного про функції Excel.
30-й день марафону ми присвятимо вивченню функції НЕПРАВИЛЬНО (INDIRECT), який повертає посилання, визначене текстовим рядком. За допомогою цієї функції ви можете створювати залежні розкривні списки. Наприклад, вибір країни зі спадного списку визначає, які параметри відображатимуться у спадному списку міст.
Отже, давайте детальніше розглянемо теоретичну частину функції НЕПРАВИЛЬНО (НЕПРЯМИЙ) і дослідити практичні приклади його застосування. Якщо у вас є додаткова інформація чи приклади, поділіться ними в коментарях.
Функція 30: НЕПРЯМИЙ
функція НЕПРАВИЛЬНО (INDIRECT) повертає посилання, визначене текстовим рядком.
Як можна використовувати функцію INDIRECT?
Оскільки функція НЕПРАВИЛЬНО (INDIRECT) повертає посилання, задане текстовим рядком, ви можете використовувати його, щоб:
- Створіть початкове посилання без зсуву.
- Створіть посилання на статичний іменований діапазон.
- Створіть посилання, використовуючи інформацію аркуша, рядка та стовпця.
- Створіть незсувний масив чисел.
Синтаксис INDIRECT (НЕПРЯМИЙ)
функція НЕПРАВИЛЬНО (INDIRECT) має такий синтаксис:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) — це текст посилання.
- a1 – якщо дорівнює TRUE (ІСТИНА) або не вказано, то буде використовуватися стиль посилання A1; а якщо FALSE (ЛОЖЬ), то стиль R1C1.
Пастки INDIRECT (НЕПРЯМІ)
- функція НЕПРАВИЛЬНО (INDIRECT) перераховується щоразу, коли змінюються значення на аркуші Excel. Це може значно сповільнити вашу книгу, якщо функція використовується в багатьох формулах.
- Якщо функція НЕПРАВИЛЬНО (НЕПРЯМО) створює посилання на іншу книгу Excel, ця книга має бути відкритою, інакше формула повідомить про помилку #REF! (#ПОСИЛАННЯ!).
- Якщо функція НЕПРАВИЛЬНО (INDIRECT) посилається на діапазон, який перевищує обмеження рядків і стовпців, формула повідомить про помилку #REF! (#ПОСИЛАННЯ!).
- функція НЕПРАВИЛЬНО (INDIRECT) не може посилатися на динамічний іменований діапазон.
Приклад 1: Створіть початкове посилання без зміщення
У першому прикладі стовпці C і E містять однакові числа, їх суми обчислено за допомогою функції SUM (SUM) також однакові. Однак формули дещо відрізняються. У клітинці C8 формула така:
=SUM(C2:C7)
=СУММ(C2:C7)
У комірці E8 функція НЕПРАВИЛЬНО (INDIRECT) створює посилання на початкову клітинку E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Якщо вставити рядок у верхній частині аркуша та додати значення за січень (січень), то сума в стовпці C не зміниться. Формула зміниться, реагуючи на додавання рядка:
=SUM(C3:C8)
=СУММ(C3:C8)
Проте функція НЕПРАВИЛЬНО (INDIRECT) фіксує E2 як початкову комірку, тому січень автоматично включається в обчислення підсумків стовпця E. Кінцева клітинка змінилася, але початкова клітинка не вплинула.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Приклад 2: посилання на статичний іменований діапазон
функція НЕПРАВИЛЬНО (INDIRECT) може створити посилання на іменований діапазон. У цьому прикладі сині клітинки складають діапазон NumList. Крім того, динамічний діапазон також створюється зі значень у стовпці B NumListDyn, залежно від кількості чисел у цьому стовпчику.
Суму для обох діапазонів можна обчислити, просто вказавши його назву як аргумент функції SUM (SUM), як ви можете бачити в клітинках E3 і E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Замість того, щоб вводити назву діапазону у функцію SUM (SUM), Ви можете звернутися до імені, написаного в одній із клітинок аркуша. Наприклад, якщо ім NumList записана в клітинку D7, то формула в клітинці E7 буде такою:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
На жаль функція НЕПРАВИЛЬНО (INDIRECT) не може створити посилання на динамічний діапазон, тому під час копіювання цієї формули в клітинку E8 ви отримаєте повідомлення про помилку #REF! (#ПОСИЛАННЯ!).
Приклад 3. Створіть посилання, використовуючи інформацію про аркуш, рядок і стовпець
Ви можете легко створити посилання на основі номерів рядків і стовпців, а також використовуючи значення FALSE (ЛОЖЬ) для другого аргументу функції НЕПРАВИЛЬНО (НЕПРЯМИЙ). Так створюється стильове посилання R1C1. У цьому прикладі ми додатково додали назву аркуша до посилання – «MyLinks»!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Приклад 4: Створення масиву чисел без зміщення
Іноді вам потрібно використовувати масив чисел у формулах Excel. У наступному прикладі ми хочемо усереднити 3 найбільші числа в стовпці B. Числа можна ввести у формулу, як це робиться в комірці D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Якщо вам потрібен більший масив, то ви навряд чи захочете ввести всі числа у формулу. Другий варіант - використання функції ROW (РЯДОК), як у формулі масиву, введеній у клітинку D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Третій варіант - використання функції ROW (STRING) разом із НЕПРАВИЛЬНО (НЕПРЯМО), як це зроблено з формулою масиву в комірці D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Результат для всіх 3 формул буде однаковий:
Однак якщо рядки вставляються у верхній частині аркуша, друга формула поверне неправильний результат через те, що посилання у формулі зміняться разом зі зміщенням рядків. Тепер замість середнього трьох найбільших чисел формула повертає середнє 3-го, 4-го та 5-го найбільших чисел.
Використання функцій НЕПРАВИЛЬНО (НЕПРЯМО), третя формула зберігає правильні посилання на рядок і продовжує показувати правильний результат.