Як користуватися функцією Excel OFFSET -

Резюме

Функція Excel OFFSET повертає посилання на діапазон, побудований з п'яти входів: (1) початкова точка, (2) зміщення рядка, (3) зміщення стовпця, (4) висота в рядках, (5) ширина в колонки. OFFSET зручний у формулах, які вимагають динамічного діапазону.

Призначення

Створіть еталонний зсув від заданої початкової точки

Повернене значення

Посилання на клітинку.

Синтаксис

= OFFSET (посилання, рядки, стовпчики, (висота), (ширина))

Аргументи

  • посилання - початкова точка, що надається як посилання на клітинку або діапазон.
  • рядки - кількість рядків, які слід змістити нижче початкового посилання.
  • cols - кількість стовпців, зміщених праворуч від початкового посилання.
  • висота - (необов’язково) Висота в рядках повернутого посилання.
  • width - (необов’язково) Ширина у стовпцях повернутого посилання.

Версія

Excel 2003

Примітки щодо використання

Функція Excel OFFSET повертає динамічний діапазон, побудований з п'яти входів: (1) початкова точка, (2) зміщення рядка, (3) зміщення стовпця, (4) висота в рядках, (5) ширина в стовпцях.

Початковою точкою ( контрольним аргументом) може бути одна комірка або діапазон комірок. Аргументи рядків і стовпців - це кількість клітинок, які потрібно «змістити» від початкової точки. Аргументи висоти та ширини необов’язкові і визначають розмір створеного діапазону. Якщо висота та ширина пропущені, вони за замовчуванням визначають висоту та ширину посилання .

Наприклад, для посилання C5, починаючи з A1, посиланням є A1, рядки - 4, а cols - 2:

=OFFSET(A1,4,2) // returns reference to C5

До посилання C1: C5 з A1, посилання - A1, рядки - 0, cols - 2, висота - 5 і ширина - 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Примітка: ширину можна пропустити, оскільки вона буде встановлена ​​за замовчуванням на 1.

Зазвичай можна побачити OFFSET, загорнутий в іншу функцію, яка очікує діапазон. Наприклад, до SUM C1: C5, починаючи з A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Основна мета OFFSET - дозволити формулам динамічно пристосовуватися до наявних даних або до введення користувачем. Функцію OFFSET можна використовувати для побудови динамічного іменованого діапазону для діаграм або зведених таблиць, щоб забезпечити постійне оновлення вихідних даних.

Примітка: Документація Excel стверджує, що висота та ширина не можуть бути від’ємними, але негативні значення, здається, працювали нормально з початку 1990-х. Функція OFFSET в Google Sheets не допускає від’ємного значення для аргументів висоти або ширини.

Приклади

Наведені нижче приклади показують, як OFFSET можна налаштувати для повернення різних типів діапазонів. Ці екрани були зроблені з Excel 365, тому OFFSET повертає динамічний масив, коли результат має більше однієї комірки. У старих версіях Excel ви можете використовувати клавішу F9 для перевірки результатів, повернутих із OFFSET.

Приклад №1

На екрані нижче ми використовуємо OFFSET для повернення третього значення (березень) у другому стовпці (захід). Формула в H4 така:

=OFFSET(B3,3,2) // returns D6

Приклад №2

На екрані нижче ми використовуємо OFFSET, щоб повернути останнє значення (червень) у третій колонці (північ). Формула в H4 така:

=OFFSET(B3,6,3) // returns E9

Приклад №3

Нижче ми використовуємо OFFSET для повернення всіх значень у третьому стовпці (північ). Формула в H4 така:

=OFFSET(B3,1,3,6) // returns E4:E9

Приклад №4

Нижче ми використовуємо OFFSET, щоб повернути всі значення за травень (п’ятий рядок). Формула в H4 така:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Приклад №5

Нижче ми використовуємо OFFSET, щоб повернути значення квітня, травня та червня для регіону Захід. Формула в H4 така:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Приклад №6

Нижче ми використовуємо OFFSET для повернення значень квітня, травня та червня для Заходу та Півночі. Формула в H4 така:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Примітки

  • OFFSET повертає лише посилання, жодні клітинки не переміщуються.
  • Як рядки, так і стовпці можуть бути подані як від'ємні числа, щоб змінити свій нормальний напрямок зміщення - негативні колли зміщені вліво, а негативні рядки зміщені вище.
  • OFFSET - це "летюча функція" - вона перераховуватиметься при кожній зміні робочого аркуша. Нестабільні функції можуть змусити великі та складніші книги працювати повільно.
  • OFFSET відобразить #REF! значення помилки, якщо зміщення знаходиться за межами робочого аркуша.
  • Коли висота або ширина опущені, використовується висота та ширина посилання .
  • OFFSET можна використовувати з будь-якою іншою функцією, яка очікує отримання посилання.
  • Документація Excel говорить, що висота та ширина не можуть бути від’ємними, але негативні значення працюють.

Пов’язані відео

Як створити динамічний іменований діапазон за допомогою OFFSET У цьому відео ми розглянемо, як створити динамічний іменований діапазон за допомогою функції OFFSET, найпоширенішого способу створення динамічного діапазону імен за допомогою формули.

Цікаві статті...