Как добиться нужного значения с помощью функции INDIRECT в Excel: пошаговое руководство

Использование функции INDIRECT в Excel для суммирования данных из нескольких листов компаний

В современном бизнес-анализе часто требуется объединять данные из нескольких источников в одном файле Excel. Например, у вас есть файл Excel с несколькими листами, где каждый лист представляет данные о ценах на медикаменты различных компаний. На отдельном листе вы хотите отображать сводные данные по всем компаниям. В такой ситуации функция INDIRECT станет отличным помощником для динамического обращения к данным разных листов.

Пример задачи: сводный лист с ценами разных компаний

Представим, что у вас есть Excel-файл с несколькими листами — каждый с названием компании (например, "Company-X", "Company-Y" и т.д.) и таблицей с ценами на лекарства. Кроме того, есть один лист-резюме, где в строках или столбцах указаны имена этих компаний. Цель: не ссылаться напрямую на ячейки каждого листа, а использовать динамическую ссылку с помощью функции INDIRECT.

Что такое функция INDIRECT и зачем она нужна?

Функция INDIRECT в Excel преобразует текстовую строку в ссылку на ячейку или диапазон. Это значит, что можно формировать адрес ячейки динамически, используя значения из других ячеек.

Например, если в ячейке А1 написано "Company-X", то формула:
=INDIRECT("'" & A1 & "'!B6")
будет возвращать значение ячейки B6 с листа "Company-X".

Это удобно, если нужно быстро менять ссылки на разные листы или адреса, без редактирования формул вручную.

Как правильно использовать функцию INDIRECT в вашем сводном листе?

1. Ссылка на листы: имена компаний должны строго соответствовать названиям листов.

2. Удалите кавычки вокруг ссылок, так как INDIRECT не принимает адреса в кавычках, а работает с текстовыми строками.

3. Используйте функцию CELL для ссылки на текущую ячейку. Например, формула:
   =INDIRECT("'" & B4 & "'!" & CELL("address"))
   — обращается к ячейке с таким же адресом, как у текущей.

4. Динамическое расширение при копировании формул. Для правильной работы формулы при копировании вниз и вправо можно использовать функцию ADDRESS, которая позволяет генерировать адреса по номерам строк и столбцов:

excel
=INDIRECT("’" & IF(B$4="", A$4, B$4) & "’!" & ADDRESS(ROW(), MOD((COLUMN(B6)-2), 2)+2))

Эта формула подстраивается под расположение ячейки, учитывая, что данные расположены в двух колонках на каждом листе — пример того, как адаптировать ссылку под специфическую структуру данных.

Решение с использованием Excel 365 и динамических массивов

В версиях Excel с поддержкой динамических массивов (Microsoft 365) можно значительно упростить задачу с помощью функций LET, SEQUENCE, SCAN и подобных. Например:

excel
=LET(
_a, B4:G4,
_b, SCAN(, _a, LAMBDA(x,y, IF(y="", x, y))),
_c, SEQUENCE(ROWS(A6:A23), , 6),
_d, MOD(SEQUENCE(, COLUMNS(_a))-1, 2)+2,
_e, "’"&_b&"’!"&ADDRESS(_c, _d),
_f, DROP(REDUCE("", _e, LAMBDA(m, n, VSTACK(m, INDIRECT(n)))), 1),
WRAPROWS(_f, 6))

• Функция LET позволяет сохранять промежуточные вычисления для удобства и оптимизации.
• Динамические массивы позволяют формуле “растекаться” по диапазону автоматически, без ручного копирования.

Такой подход уменьшает количество ошибок и облегчает поддержку большого количества компаний и позиций.

Важные моменты и возможные ошибки

• Объединённые ячейки: Если в шапке с названиями листов (например, B4:G4) есть объединённые ячейки, это может вызвать сбои при вычислении формул. Рекомендуется избегать слияния там, где нужна точная адресация.

• Структура данных: Формулы выше предполагают, что данные на всех листах расположены одинаково (например, цены всегда в столбцах B и C, начиная с 6 строки). Если структура разная, формулы нужно адаптировать.

• Волатильность INDIRECT: Функция INDIRECT считается волатильной — каждый пересчёт вызывает обновление формулы, что замедляет работу книг с большим количеством данных. В Excel 365 можно использовать альтернативы, минимизирующие эту проблему.

Альтернативное решение без INDIRECT в Excel 365

Если вы хотите избежать недостатков INDIRECT, можно использовать формулы с функциями TOCOL, HSTACK, XMATCH и другими, которые работают с массивами и позволяют обходить ограничение INDIRECT. Пример:

excel
=LET(mrp,B$5="MRP",
INDEX(IF(mrp, HSTACK(‘Company-X:Company-Z’!$C6), HSTACK(‘Company-X:Company-Z’!$B6)),
XMATCH(IF(mrp, A$4, B$4), TOROW($B$4:$G$4,1))))

Такой подход более устойчив и эффективен при больших объёмах данных. Он также позволяет обращаться к диапазонам и анализировать данные без необходимости вручную указывать листы и адреса ячеек.

Выводы

• Функция INDIRECT отлично подходит для динамического обращения к ссылкам в разных листах на основе названия компаний.
• Для удобства и масштабируемости формул используйте ADDRESS и CELL для создания динамических адресов.
• В новых версиях Excel (365) рекомендуются использовать динамические массивы совместно с LET и другими функциями для повышения производительности и удобства.
• Избегайте слияния ячеек в диапазонах с именами листов и обращайтесь к структурированным таблицам.
• Если INDIRECT вызывает проблемы с производительностью, применяйте альтернативные методы на основе динамических массивов.

Таким образом, использование INDIRECT с правильно построенными формулами позволит вам создать удобный сводный отчет по ценам разных компаний, автоматически обновляющийся при изменении исходных данных.

Если нужна помощь по конкретным формулам или настройке вашего файла, всегда можно обратиться к примерам с вашим образцом Excel или задать вопрос на профильных форумах.

Источник