Использование B2:ИНДЕКС() как нелетучий аналог СМЕЩ / OFFSET в Excel
В этой статье мы разберем, как создать динамический диапазон в Excel без использования летучих функций, что является ключевым аспектом для оптимизации производительности больших таблиц.
Полная формула: =СРЗНАЧ(B2:ИНДЕКС(B2:E2;1;$H$1)) / =AVERAGE(B2:INDEX(B2:E2,1,$H$1)). При H1=3 правой границей станет D2 и усредняется B2:D2. ИНДЕКС получает номер строки 1 и номер столбца H1; двоеточие формирует ссылку на весь диапазон. Для H1 задайте проверку ввода целого числа от 1 до 4.
Приём работает в Excel 2016/2019/2021/2024, Microsoft 365 для Windows/Mac и Excel для Интернета, без динамических массивов. B2:E2 — четыре квартальные суммы, H1 — целое число от 1 до 4.
Эта статья особенно полезна тем, кто ищет формулы в Excel, хочет быстрее разобраться в функциях и применять рабочие примеры без путаницы между русской и английской версиями Excel.
Функция СМЕЩ / OFFSET, несмотря на свою гибкость, заставляет Excel пересчитывать множество ячеек при каждом цикле пересчёта; влияние на скорость зависит от числа зависимых формул.
Вместо нее можно использовать более эффективную конструкцию с функцией ИНДЕКС / INDEX.
- Основная проблема СМЕЩ / OFFSET — его летучесть. Формулы с СМЕЩ и их зависимые формулы пересчитываются в каждом цикле расчёта, даже если их аргументы не изменились. В ручном режиме расчёт нужно запустить.
- Решение — замена на
B2:ИНДЕКС(B2:E2;1;$H$1). Двоеточие перед ИНДЕКС / INDEX заставляет его использовать ссылку на ячейку (например, D2) как правую границу, а не текстовый адрес, формируя диапазон.
Функция ИНДЕКС / INDEX не является летучей. В этом примере она позволяет выбрать правую границу диапазона без летучей функции. Пересчёт всё равно выполняется при изменении зависимостей; это не полная замена всех сценариев СМЕЩ.
Замена летучих формул может ускорить большую книгу, но фактическое время нужно измерять: результат зависит от структуры зависимостей и объёма вычислений.