Функція «зміщений» в excel.

Відео: Підрахунок проміжних підсумків функція зміщена

Розглянемо, як зробити динамічний діапазон в Excel за допомогою функцію «зміщений» в Excel. Функція дуже корисна. Про цю функцію кажуть, що вона зміщує діапазон на задану кількість рядків і стовпців.
Нічого не зрозуміти - де зміщує, для чого зміщує, тощо Скажімо простою мовою. Функція «зміщений» в Excel автоматично змінює у формулі Excel адреса діапазону комірок, щодо конкретної комірки таблиці. Не потрібно вручну постійно міняти у формулі адреса діапазону таблиці, при додаванні в таблицю рядків і стовпців.
Ця функція стане в нагоді при складанні різних випадаючих списків, графіків, тощо .. Приклад дивіться в статті "Список, що розкривається в Excel з картинками" тут.
Функція «зміщений» допоможе вважати дані з певному діапазоні. Наприклад, нам потрібно вважати дані тільки останніх семи осередків в таблиці, яка заповнюється щодня.
Спочатку розберемо аргументи (умови) функції «зміщений» в Excel.
На закладці «Формули» в розділі «Бібліотека функцій» натискаємо на кнопку «Посилання та масиви». Із списку вибираємо функцію «зміщений». З`явиться таке вікно. Заповнювати його можна повністю, можна частково. Розглянемо аргументи функції «зміщений» в Excel.
"Посилання" - тут пишемо адресу осередку, діапазону суміжних осередків, від якої буде зрушуватися діапазон (це - стартова осередок). Цей рядок заповнювати обов`язково. Адреса комірки може бути відносний, тоді діапазон буде повністю переміщатися по таблиці. Абсолютний адреса комірки - тоді верхній край діапазону зміщуватися не буде.
«Зміщений по рядках» - тут ставимо цифру, на яку кількість рядків потрібно зрушити діапазон. Якщо поставимо позитивне число (наприклад, 6), то діапазон зрушиться вниз. (На 6 рядків). Якщо поставимо негативне число (-6), то діапазон підніметься вгору (на 6 рядків). Заповнювати цей рядок обов`язково. Якщо не потрібно зрушувати рядок, то ставимо нуль (0).
«Зміщений за стовпцями» - тут ставимо цифру, на яку кількість стовпців потрібно зрушити діапазон. Якщо поставимо позитивне число (наприклад, 3), то діапазон зрушиться вправо (на 3 стовпці). Якщо поставимо негативне число (-8), то діапазон зрушиться вліво (на 8 шпальт). Заповнювати цей рядок обов`язково. Якщо не потрібно зрушувати стовпець, то ставимо нуль (0).
Увага!

Якщо поставили більшу кількість рядків або стовпців, ніж діапазон таблиці, то в осередку вийде повідомлення «# посилаючись !.». Наприклад, якщо стартова осередок В1, а ми вказали зміщення по рядках - мінус 3, то осередки вліво зрушуватися не куди. Вийде помилка. За межі таблиці діапазон не зміщувати.
«Висота» - число рядків в діапазоні, який будемо зрушувати, вважати.
«Ширина» - число стовпців в діапазоні, який будемо переміщувати.

Показники «Висота» і «Ширина» не обов`язково заповнювати. Тоді розмір діапазону буде такою, якою написали в рядку «Посилання». Але, якщо заповнюємо ці рядки (аргументи функції), то ставимо тільки позитивне число.
Після заповнення діалогового вікна, натискаємо кнопку «ОК».
Про відносні і абсолютні посилання в формулах, читайте в статті «Відносні і абсолютні посилання в Excel» тут.
Про діапазонах суміжних, несуміжних осередків, дивіться статтю «Діапазон в Excel».
Тепер розглянемо, як працює функція «зміщений» в Excel.
У нас така таблиця з даними.
Напишемо формулу, щоб відображалося значення конкретної комірки. В осередку Е1 встановлюємо функцію «зміщений». Діалогове вікно функції заповнили так.
Вийшло так.
Формула в комірці Е1 вийшла така. = Зміщений (B3-3-1)
Цією формулою ми говоримо - показати значення осередку, яка знаходиться від осередку B3 (на зображенні осередок синім кольором) нижче на три рядки і правіше на один стовпець. Це, вийшла осередок С6 (зелена осередок).
Функцією «зміщений» в Excel можна перевернути (перемістити) дані в рядку з права на ліво. Дивіться статтю «Як поміняти місцями стовпчики в Excel».
Можна перевернути дані в стовпці - знизу-вгору. Про це читайте статтю «Як перевернути таблицю в Excel».
Можна скласти дані, відняти середнє значення, тощо в певному динамічному діапазоні.
Як зробити динамічний діапазон в таблиці функцією "зміщений", дивіться в статті "Щоб розмір таблиці Excel змінювався автоматично" тут.
Як і де застосувати функцію "зміщений", як вважати останні дані таблиці, які додаються кожен день, тощо, читайте в статті "Приклади функції "зміщений" в Excel".