Функція зміщений () в ms excel - сумісно з microsoft excel 2018, excel 2018

Функція зміщений () часто використовується при створенні динамічних діапазонів. Розглянемо її докладніше.

Синтаксис функції зміщений ()

Щоб було зрозуміліше, потренуємося з функцією зміщений (). використовуючи файл прикладу.

Дана вихідна таблиця з трьома стовпчиками.

Ставлячи параметри функції зміщений () підрахуємо суму значень в різних діапазонах таблиці. Для візуального спостереження діапазону, що повертається функцією зміщений (). використано Умовне форматування. Для зручності зміни параметрів функції зміщений () використані Елементи управління Лічильник.

Для підрахунку суми значень в стовпці Продажа1 запишемо формулу:
= СУММ (зміщений ($ B $ 2; 0; 0; 8; 1))
діапазон підсумовування - $ B $ 2: $ B $ 9 (лівий верхній кут - $ B $ 2. висота 8. зміщення верхнього кута немає). Результат 34.

Для підрахунку суми значень в стовпці Продажа2 запишемо формулу:
= СУММ (зміщений ($ B $ 2; 0; 1; 8; 1))
Тепер лівий верхній кут діапазону підсумовування зміщений від $ B $ 2 на один стовпець вправо, тобто діапазон підсумовування став $ C $ 2: $ C $ 9. Результат 68.

Для підрахунку суми значень в стовпцях Продажа1 і Продажа2, змінимо ширину діапазону.
= СУММ (зміщений ($ B $ 2; 0; 0; 8; 2))
вказавши ширину в 2 осередки, результат складе 102. діапазон буде модифікований в $ В $ 2: $ З $ 9.

Додавши зміщення по рядках (+1), отримаємо результат 99.
= СУММ (зміщений ($ B $ 2; 1; 0; 8; 2))
діапазон буде модифікований в $ В $ 3: $ З $ 9.

Функція зміщений () vs ІНДЕКС ()

Нехай є діапазон з числами (А2: А10) Необхідно знайти суму перших 2-х, 3-х. 9 значень. Звичайно, можна написати кілька формул = СУММ (А2: А3). = СУММ (А2: А4) і т.д. Але, записавши формулу у вигляді:

отримаємо універсальне рішення, в якому потрібно змінювати тільки останній аргумент (якщо у формулі вище замість 4 ввести 5, то буде підрахована сума перших 5-и значний). Вищевказана формула еквівалентна формулі = СУММ (A2: ІНДЕКС (A2: A10; 4)). яка, в свою чергу, еквівалентна формулі = СУММ (A2: A5)

Формула ІНДЕКС (A2: A10; 4) повертає посилання на осередок А5.