Функція Excel OFFSET (Приклад + відео)
Коли використовувати функцію Excel OFFSET
Функцію Excel OFFSET можна використовувати, коли потрібно отримати посилання, яке зсуває зазначену кількість рядків і стовпців від початкової точки.
Що повертає
Він повертає посилання, на яке вказує функція OFFSET.
Синтаксис
= OFFSET (посилання, рядки, стовпці, [висота], [ширина])
Аргументи введення
- довідка - Посилання, від якого потрібно компенсувати. Це може бути посилання на клітинку або діапазон сусідніх клітинок.
- рядки - кількість рядків для зміщення. Якщо ви використовуєте позитивне число, воно зсувається до рядків нижче, а якщо використовується від’ємне число, то воно зсувається до рядків вище.
- cols - кількість стовпців для зміщення. Якщо ви використовуєте позитивне число, воно зсувається до стовпців праворуч, а якщо використовується від’ємне число, то воно зсувається до стовпців зліва.
- [висота] - це число, яке представляє кількість рядків у поверненому посиланні.
- [ширина] - це число, яке представляє кількість стовпців у поверненому посиланні.
Розуміння основ функцій Excel OFFSET
Функція Excel OFFSET, можливо, одна з найбільш заплутаних функцій у Excel.
Візьмемо простий приклад гри в шахи. У шахах є фігурка під назвою «Ладья» (також відома як вежа, маркіз або ректор).
[Зображення надано: Чудова Вікіпедія]
Тепер, як і всі інші шахові фігури, у грака є фіксований шлях, по якому він може переміщатися по дошці. Він може рухатися прямо (вправо/вліво або вгору/вниз по дошці), але не може йти по діагоналі.
Наприклад, припустимо, що у нас є шахова дошка в Excel (як показано нижче), у даному полі (у цьому випадку D5), Ладья може рухатися лише у чотирьох виділених напрямках.
Тепер, якщо я попрошу вас перевести Ладью з її поточної позиції на позицію, виділену жовтим, що ви скажете ладьї?
Ви попросите його зробити два кроки вниз і два кроки вправо … чи не так?
І це наближене до того, як працює функція OFFSET.
Тепер подивимося, що це означає в Excel. Я хочу почати з комірки D5 (де знаходиться Ладья), а потім перейти на два рядки вниз і два стовпці праворуч і отримати значення з комірки там.
Формула буде такою:
= OFFSET (звідки почати, скільки рядків вниз, скільки стовпців праворуч)
Як бачите, формула у наведеному вище прикладі у клітинці J1 є = OFFSET (D5,2,2).
Він розпочався з D5, а потім пішов на два рядки вниз і два стовпці праворуч і дійшов до комірки F7. Потім він повернув значення в комірку F7.
У наведеному вище прикладі ми розглянули функцію OFFSET з 3 аргументами. Але є ще два необов’язкові аргументи, які можна використати.
Давайте розглянемо простий приклад тут:
Припустимо, ви хочете використовувати посилання на клітинку A1 (жовтим кольором) і хочете посилатися на весь діапазон, виділений синім кольором (C2: E4) у формулі.
Як би ви це зробили за допомогою клавіатури? Спочатку перейдіть до комірки C2, а потім виділіть усі клітинки у C2: E4.
Тепер давайте подивимося, як це зробити за формулою OFFSET:
= ЗМІЩЕННЯ (A1,1,2,3,3)
Якщо ви використовуєте цю формулу в комірці, вона поверне #VALUE! помилка, але якщо ви перейдете в режим редагування, виберіть формулу і натисніть F9, ви побачите, що вона повертає всі значення, виділені синім кольором.
Тепер подивимося, як працює ця формула:
= ЗМІЩЕННЯ (A1,1,2,3,3)
- Перший аргумент - це клітинка, з якої вона повинна починатися.
- Другий аргумент - 1, який вказує Excel повернути посилання, зміщене на 1 рядок.
- Третій аргумент - 2, який вказує Excel повернути посилання, зміщене на 2 стовпці.
- Четвертий аргумент - 3. Це вказує, що посилання має охоплювати 3 рядки. Це називається аргументом висоти.
- П’ятий аргумент - 3. Це вказує, що посилання має охоплювати 3 стовпці. Це називається аргументом ширини.
Тепер, коли у вас є посилання на діапазон клітинок (C2: E4), ви можете використовувати його в інших функціях (таких як SUM, COUNT, MAX, AVERAGE).
Сподіваємось, ви добре розумієте використання функції Excel OFFSET. Тепер давайте подивимося на деякі практичні приклади використання функції OFFSET.
Функція Excel OFFSET - Приклади
Ось два приклади використання функції Excel OFFSET.
Приклад 1 - Пошук останньої заповненої клітинки у стовпці
Припустимо, у вас є деякі дані у стовпці, як показано нижче:
Щоб знайти останню клітинку у стовпці, скористайтеся такою формулою:
= OFFSET (A1, COUNT (A: A) -1,0)
Ця формула передбачає, що немає інших значень, окрім показаних, і ці клітинки не мають порожньої клітинки. Він працює шляхом підрахунку загальної кількості заповнених клітинок і відповідно зсуває клітинку A1.
Наприклад, у цьому випадку є 8 значень, тому COUNT (A: A) повертає 8. Ми зміщуємо клітинку A1 на 7, щоб отримати останнє значення.
Приклад 2 - Створення динамічного випадаючого меню
Ви можете розширити концепт -шоу в Прикладі 1, щоб створити динамічні іменовані діапазони. Вони можуть бути корисними, якщо ви використовуєте іменовані діапазони у формулах або створюєте випадаючі списки, і ви, ймовірно, додаєте/видаляєте дані з посилання, що робить іменований діапазон.
Ось приклад:
Зауважте, що випадаюче меню налаштовується автоматично, коли рік додається або видаляється.
Це відбувається, оскільки формула, яка використовується для створення випадаючого списку, є динамічною і визначає будь -яке додавання чи видалення та відповідно змінює діапазон.
Ось як це зробити:
- Виберіть клітинку, куди потрібно вставити спадне меню.
- Перейдіть до Дані -> Інструменти даних -> Перевірка даних.
- У діалоговому вікні Перевірка даних на вкладці Налаштування виберіть Список зі спадного меню.
- У джерелі введіть таку формулу: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Натисніть OK.
Давайте подивимося, як працює ця формула:
- Перші три аргументи функції OFFSET - це A1, 0 і 0. Це, по суті, означає, що вона повертає ту саму опорну комірку (яка є A1).
- Четвертий аргумент - для висоти, і тут функція COUNT повертає загальну кількість елементів у списку. Він передбачає, що у списку немає пробілів.
- П'ятий аргумент - 1, який вказує на те, що ширина стовпця повинна бути одиницею.
Додаткові нотатки:
- OFFSET - мінлива функція (використовуйте з обережністю).
- Він перераховує щоразу, коли відкрита книга Excel або коли на робочому аркуші запускається обчислення. Це може збільшити час обробки та уповільнити вашу книгу.
- Якщо значення висоти або ширини опущено, воно приймається як значення посилання.
- Якщо рядків та cols є від'ємними числами, тоді напрямок зміщення змінюється.
Альтернативи функцій Excel OFFSET
Через деякі обмеження функції Excel OFFSET багато хто хоче розглянути альтернативи їй:
- Функція INDEX: Функцію INDEX також можна використовувати для повернення посилання на клітинку. Натисніть тут, щоб побачити приклад створення динамічного іменованого діапазону за допомогою функції INDEX.
- Таблиця Excel: Якщо ви використовуєте структуровані посилання в таблиці Excel, вам не потрібно турбуватися про додавання нових даних та необхідність коригування формул.
Функція Excel OFFSET - Відеопосібник
- Функція Excel VLOOKUP.
- Функція Excel HLOOKUP.
- Функція Excel INDEX.
- Непряма функція Excel.
- Функція Excel MATCH.
