30 функцій Excel за 30 днів ЗМІЩ (OFFSET) - Microsoft Excel для початківців

Вчора в марафоні30 функцій Excel за 30 днів ми заміняли текстові рядки за допомогою функціїREPLACE (ЗАМІНИТИ) і з'ясували, що вставляти символи вона теж вміє.

У 26-й день марафону ми вивчатимемо функціюOFFSET (ЗМІЩ). Вона повертає посилання заданого розміру, що віддаляється від стартового посилання на вказану кількість рядків та стовпців.

Функція 26: OFFSET (ЗМІЩ)

ФункціяOFFSET (ЗМІЩ) повертає посилання, зміщене від заданого посилання на певну кількість рядків та стовпців.

Як можна використовувати функцію OFFSET (ЗМІЩ)?

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

Знаходити суми продажів для обраного місяця.
Підсумовувати дані про продаж за вибрані місяці.
Створювати динамічні діапазони з урахуванням підрахунку.
Підсумовувати продажі за останніn місяців.

Синтаксис OFFSET (ЗМІЩ)

ФункціяOFFSET (ЗМІЩ) має такий синтаксис:

Пастки OFFSET (ЗМІЩ)

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

Приклад 1: Знаходимо суму продажів для обраного місяця

За допомогою функціїOFFSET (ЗМІЩ) Ви можете повертати посилання на діапазон, відштовхуючись від стартового посилання. У цьому прикладі ми хочемо отримати суму продажу в осередку G2.

У осередку H2 знаходиться формула, дуже схожа на попередню, яка повертає ім'я місяця. Єдина відмінність - це зрушення по стовпцях: 1 замість 0.

=OFFSET(C1,F2, 1 ,1,1) =ЗМІЩ(C1;F2;1; 1 ;1)

Приклад 2: Підсумовуємо продажі за вибрані місяці

Приклад 3: Створюємо динамічний діапазон на основі підрахунку

Для створення динамічного діапазону можна використовуватиOFFSET (ЗМІЩ). У цьому прикладі ми створили іменований діапазонMonthList з такою формулою:

Якщо до списку в стовпці C додати ще один місяць, він автоматично з'явиться у списку, що випадає, в комірці F2, який використовує ім'яMonthList, як джерело даних.