Підсумовування по - вікну - на аркуші функцією ЗМІЩ (OFFSET)

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

аркуші

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

Як рахувати суму - зрозуміло, а ось як визначити діапазон осередків, які потрібно підсумувати? Адже при виборі станцій він постійно трансформуватиметься?

У подібній ситуації може допомогти функціяЗМІЩ (OFFSET), здатна видати посилання на "плаваюче вікно" - діапазон заданого розміру, розташований у певному місці аркуша. Синтаксис у функції наступний:

=ЗМІЩ( Точка_відліку ; Зсув_вниз ; Свіг_вправо ; Висота ; Ширина )

Ця функція на виході дає посилання на діапазон, зрушений щодо якоїсь стартової комірки (Точка_відліку) на певну кількість рядків вниз і стовпців праворуч. Причому розмір діапазону ("вікна") також може задаватися параметрамиВисотаіШирина.

У нашому випадку, якщо взяти за точку відліку комірку А1, то:

вікну

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

Щоб розрахувати необхідні дляЗМІЩаргументи, давайте спочатку застосуємо функціюПОШУКПОЗ (MATCH), яку ми вже розбирали, дляобчислення позицій станцій відправлення та призначення:

аркуші

І, нарешті, використовуємо функціюЗМІЩ, щоб отримати посилання на потрібне "вікно" на аркуші і підсумувати всі комірки з нього:

аркуші

Ось і все, завдання вирішене :)

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