Суммирование по "окну" на листе функцией СМЕЩ (OFFSET)
Бывают ситуации, когда заранее не известно какие именно ячейки на листе нужно подсчитывать. Например, представим, что нам нужно реализовать в Excel небольшой транспортный калькулятор для расчета расстояния проезда от одной заданной станции до другой:
В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна подсчитываться сумма всех ячеек в заданном "окне" на листе. Для проезда от Останкино до Ховрино, как на рисунке, например, нужно будет просуммировать все ячейки в обведенном зеленым пунктиром диапазоне.
Как считать суммму - понятно, а вот как определить диапазон ячеек, которые нужно просуммировать? Ведь при выборе станций он будет постоянно трансформироваться?
В подобной ситуации может помочь функция СМЕЩ (OFFSET), способная выдать ссылку на "плавающее окно" - диапазон заданного размера, расположенный в определенном месте листа. Синтаксис у функции следующий:
=СМЕЩ(Точка_отсчета; Сдвиг_вниз; Свиг_вправо; Высота; Ширина)
Эта функция на выходе дает ссылку на диапазон, сдвинутый относительно некоей стартовой ячейки (Точка_отсчета) на определенное количество строк вниз и столбцов вправо. Причем размер диапазона ("окна") тоже может задаваться параметрами Высота и Ширина.
В нашем случае, если взять за точку отсчета ячейку А1, то:
- Точка отсчета = А1
- Сдвиг_вниз = 4
- Свиг_вправо = 2
- Высота = 4
- Ширина = 1
Чтобы рассчитать необходимые для СМЕЩ аргументы, давайте сначала применим функцию ПОИСКПОЗ (MATCH), которую мы уже разбирали, для вычисления позиций станций отправления и назначения:
И, наконец, используем функцию СМЕЩ, чтобы получить ссылку на нужное "окно" на листе и просуммировать все ячейки из него:
Вот и все, задача решена :)
P.S.
В отличие от большинства остальных функций Excel, СМЕЩ является волатильной (volatile) или, как еще говорят, "летучей" функцией. Обычные функции пересчитываются только в том случае, если меняются ячейки с их аргументами. Волатильные же пересчитываются каждый раз при изменении любой ячейки. Само-собой, это отрицательно сказывается на быстродействии. В больших тяжелых таблицах разница по скорости работы книги может быть очень ощутимой (в разы). Для некоторых случаев быстрее оказывается заменить медленную СМЕЩ на неволатильную ИНДЕКС или другие аналоги.
Ссылки по теме
- Поиск позиции заданного элемента в списке функцией ПОИСКПОЗ (MATCH)
- 4 способа создать выпадающий список в ячейке листа Excel
- 5 вариантов применения функции ИНДЕКС
Николай, помню у Вас была отличная статья на эту тему:
Но, кстати, да - спасибо за напоминание - дописал P.S. про это дело.
допустим, A1 = СМЕЩ()*Лист2!B2. Очевидно, что ячейка A1 зависит от ячейки Лист2!B2. Однако если вы захотите посмотреть зависимые ячейки для Лист2!B2, то эксель скажет, что таких нет)
очень нужно автоматизировать подсчет при помощи sumifs, и чтобы не прописывать колонки суммирования каждый раз, хотелось прописать offset для выбора нужной колонки
для sumif+offset получилось прописать формулу, а вот к сожалению для sumifs не получается прописать
работает - пример =SUMIF(Data!$A:$A;PL!G$5&PL!G$6&PL!G$7;OFFSET(Data!$A$1;0;MATCH($A13;Data!$B$1:$GK$1;0);60000))/1000
для sumifs почему-то не работает, подскажите, пожалуйста, что в формуле не так:
=SUMIFS(OFFSET(Data!$A$1;0;MATCH($A13;Data!$B$1:$GK$1;0);60000);Data!$A:$A;PL!G$5&PL!G$6&PL!$G7)/1000
Точка отсчета = А1
Сдвиг_вниз = 4 - как я понял, это опечатка (должно быть 3)
Свиг_вправо = 2
Высота = 4
Ширина = 1
И вопрос по последней картинке, почему ПОИСКПОЗ выдает цифры 3 и 7 на слова, находящиеся в 4 и 8 строке соответственно.