Суммирование по "окну" на листе функцией СМЕЩ (OFFSET)

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

Калькулятор проезда с функцией СМЕЩ (OFFSET)

В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна подсчитываться сумма всех ячеек в заданном "окне" на листе. Для проезда от Останкино до Ховрино, как на рисунке, например, нужно будет просуммировать все ячейки в обведенном зеленым пунктиром диапазоне.

Как считать суммму - понятно, а вот как определить диапазон ячеек, которые нужно просуммировать? Ведь при выборе станций он будет постоянно трансформироваться?

В подобной ситуации может помочь функция СМЕЩ (OFFSET), способная выдать ссылку на "плавающее окно" - диапазон заданного размера, расположенный в определенном месте листа. Синтаксис у функции следующий:

=СМЕЩ(Точка_отсчета; Сдвиг_вниз; Свиг_вправо; Высота; Ширина)

Эта функция на выходе дает ссылку на диапазон, сдвинутый относительно некоей стартовой ячейки (Точка_отсчета) на определенное количество строк вниз и столбцов вправо. Причем размер диапазона ("окна") тоже может задаваться параметрами Высота и Ширина

В нашем случае, если взять за точку отсчета ячейку А1, то:

Параметры СМЕЩ

  • Точка отсчета = А1
  • Сдвиг_вниз = 4
  • Свиг_вправо = 2
  • Высота = 4
  • Ширина = 1

Чтобы рассчитать необходимые для СМЕЩ аргументы, давайте сначала применим функцию ПОИСКПОЗ (MATCH), которую мы уже разбирали, для вычисления позиций станций отправления и назначения:

Вычисляем позиции станций

И, наконец, используем функцию СМЕЩ, чтобы получить ссылку на нужное "окно" на листе и просуммировать все ячейки из него:

Суммируем ячейки из динамического окна на листе функцией СМЕЩ

Вот и все, задача решена :)

P.S.

В отличие от большинства остальных функций Excel, СМЕЩ является волатильной (volatile) или, как еще говорят, "летучей" функцией. Обычные функции пересчитываются только в том случае, если меняются ячейки с их аргументами. Волатильные же пересчитываются каждый раз при изменении любой ячейки. Само-собой, это отрицательно сказывается на быстродействии. В больших тяжелых таблицах разница по скорости работы книги может быть очень ощутимой (в разы). Для некоторых случаев быстрее оказывается заменить медленную СМЕЩ на неволатильную ИНДЕКС или другие аналоги.

Ссылки по теме



17.03.2017 12:06:29
СМЕЩ – еще очень полезна при создании связанных выпадающих списков.
Николай, помню у Вас была отличная статья на эту тему: http://www.planetaexcel.ru/techniques/1/38/
17.03.2017 21:09:19
Зато на форуме про нее постоянно страшилки рассказывают и не рекомендуют злоупотреблять из-за её летучести.
18.03.2017 09:47:55
Ну, все зависит от ситуации :)
Но, кстати, да - спасибо за напоминание - дописал P.S. про это дело.
18.03.2017 16:05:53
еще ложка дегтя в бочку СМЕЩ:
допустим, A1 = СМЕЩ()*Лист2!B2. Очевидно, что ячейка  A1 зависит от ячейки Лист2!B2. Однако если вы захотите посмотреть зависимые ячейки для Лист2!B2, то эксель скажет, что таких нет)
18.03.2017 22:28:20
futurama3000, очевидно, что Вы глупости пишите. Такую формулу =СМЕЩ()*Лист2!B2 Эксель не даст ввести. У СМЕЩ() 5 аргументов, 3 из которых обязательны для ввода. Так что не вводите людей в заблуждение.
19.03.2017 01:20:07
Максим, спасибо за ценный комментарий. Под СМЕЩ() имеется в виду СМЕЩ со всеми аргументами на ваш вкус.
19.03.2017 11:59:40
futurama3000, в таком случае прошу прощения. Подумал, что вы так и вводите формулу.
01.04.2017 00:26:31
Привет
очень нужно автоматизировать подсчет при помощи 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
22.04.2017 11:01:08
Не видя файла - не скажу.
07.07.2017 10:39:52
Добрый день, под 2-ой картинкой описание:

Точка отсчета = А1
Сдвиг_вниз = 4 - как я понял, это опечатка (должно быть 3)
Свиг_вправо = 2
Высота = 4
Ширина = 1

И вопрос по последней картинке, почему ПОИСКПОЗ выдает цифры 3 и 7 на слова, находящиеся в 4 и 8 строке соответственно.
07.07.2017 13:22:42
РАз про Индекс речь была, вот вариантик:
СУММ(ИНДЕКС(C2:C48;G3+1):ИНДЕКС(C2:C48;G5))
 
Здравствуйте, большое Вам спасибо, за видео научился создавать выпадающие списки, динамические одноуровневые списки, делаю диагностику, для педагогов в детском саду, сам педагог, если можно помогите не могу создать: у меня есть диагностика там фамилии детей и критерии: прыжок в длину с места и внизу я ставлю минус или плюс, после перехожу на другую книгу выбираю выпадающим списком фамилию ребенка критерий вверху он отражает сам плюс или минус и если минус то выбирает цель индивидуальной работы  и в другой графе что нужно делать, я не могу чтоб из одного листа диагностики данные перешли в другой и как отразится цель и сама индивидуальная работа. Хотел отправить файл, но не смог там почти все вручную. С уважением Владимир
Наверх