✅ OFFSET Формула в Excel - Як використовувати функцію OFFSET в Excel?

Що таке формула OFFSET в Excel?

Excel OFFSET Formula (Зміст)

Що таке формула OFFSET в Excel?
Як використовувати формулу OFFSET в Excel?

Що таке формула OFFSET в Excel?

Формула OFFSET в Excel дає клітинку або діапазон динамічних прямокутних комірок як вихід, який є підмножиною загального заданого діапазону з заданою кількістю рядків, стовпців, висоти та ширини.

Синтаксис формули OFFSET в Excel

Пояснення функції OFFSET в Excel:

Довідка: Відправну точку для OFFSET або, можна сказати, діапазон комірок, який можна вважати базовим для OFFSET.
рядки : кількість рядків для OFFSET нижче або над базовою посиланням.
cols: кількість стовпців для OFFSET праворуч або ліворуч від базової бази.
висота: кількість рядків у поверненому посиланні.
ширина: кількість стовпців у поверненій посилання.

Висота і ширина - необов'язкові параметри для цієї функції і можуть використовуватися для визначення розміру створеної посилання.

Як використовувати формулу OFFSET в Excel?

Зараз ми дізнаємось, як записати формулу зміщення, щоб отримати значення пошуку. Розберемо формулу зміщення з деякими прикладами в Excel.

Ви можете завантажити шаблон OFFSET Formula Excel тут - OFFSET Шаблон формули Excel

Формула Excel OFFSET - приклад №1

Припустимо, у нас є дані, як наведено нижче.

У комірку E2 введіть таку формулу:

= OFFSET (A1, 11, 1)

Після закінчення формули натисніть клавішу Enter і подивіться вихід.

Ця формула зробила це те, що вона посилається на клітинку A1 (Рік) і переміщується вниз на 11 рядків після A1 (тобто рядка № 12), а потім отримує значення, яке є координатою 11- ^го ряду після A1 та першого стовпця ( стовпець A = 0 у цьому випадку стовпчик B = 1. Як ми знаємо, ми можемо йти і вправо, і вліво). Що становить 141, 27 млн дол.

Формула Excel OFFSET - приклад №2

Зараз ми побачимо ще один приклад контрольної комірки на конкретний рік. Помістіть таку формулу в комірку E3:

= OFFSET (A1, 11, 0)

Після закінчення формули натисніть клавішу Enter і подивіться вихід.

Якщо ви бачили формулу, вона така ж, як і в попередній, єдина зміна - у посиланні на стовпчик. Посилання стовпця 0 (нульове) означає, що значення буде зафіксовано з стовпця А для 11- ^го ряду після A1 (що є 2010).

Формула Excel OFFSET - приклад №3

У цьому прикладі ми обчислимо суму останніх N рядків за допомогою функції Excel OFFSET. Загальна формула підсумовування останніх N чисел:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Для підведення підсумків останніх N спостережень за вашими даними, ви можете використовувати функцію SUM разом із функціями OFFSET та COUNT разом. У цьому прикладі ми підведемо підсумки останніх 5 даних даних. Зверніть увагу, що коли ми говоримо N, це узагальнення будь-якого цілого значення. Отже, ви можете розрахувати останні 3, останні 4, останні 5 тощо, залежно від вашого набору даних, а також ваших вимог.

Давайте зробимо це для нашого набору даних:

Введіть таку формулу в комірку E2 файлу excel.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

Клацніть Enter, щоб побачити вихід.

Як ми вже бачили, функція Excel OFFSET видає прямокутні динамічні діапазони, коли дається вихідна посилання.

У цьому випадку початкова посилання надається з точки зору B1 (як перший аргумент функції OFFSET).
Ми використовували функцію COUNT, оскільки нам потрібна сума спостережень від останнього значення. Функція COUNT допомагає OFFSET визначити, скільки спостережень (рядків) є у стовпці B. Він підраховує всі числові значення у стовпці B (19 - кількість).
Якщо вважати 19, формула OFFSET стає:

= OFFSET (B1, 19, 0, -5)

Ця формула починається зсувом на B1 з 19 рядків і тієї ж колонки (0). Тоді параметр висоти -5 допомагає цій функції розширити діапазон у напрямку назад на 5 рядків (див. Негативний знак, пов'язаний з 5).
Динамічний діапазон останніх п’яти спостережень задається як вхід до функції суми. Який вихід у підсумовуванні даних із стовпця B.

Формула Excel OFFSET - приклад №4

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

Помістіть у комірку E2 таку формулу:

= МЕДІАН (ВИМКНЕННЯ (B1, COUNT (B: B), 0, -3))

Натисніть Enter, щоб побачити вихід.

Ця формула працює на рядках, аналогічних попередній.

OFFSET Створює динамічний діапазон останніх трьох спостережень із стовпця B за допомогою функції COUNT (= OFFSET (A1, 19, 0, -3)).
Це може бути подано як вихід до функції MEDIAN. Потім сортує спостереження у порядку зростання чи зменшення, а потім вибирає середнє значення, як ви бачите у випуску ($ 212, 07).

Формула Excel OFFSET - приклад №5

Тут ми обчислимо медіану року за допомогою функції MEDIAN та OFFSET.

Використовуйте таку формулу для тієї ж у комірці E3:

= МЕДІАН (ВИМКНЕННЯ (A1, COUNT (A: A), 0, -3))

Клацніть Enter, щоб побачити вихід.

Тут відбулося те саме. Формула займала динамічний діапазон, створений функцією OFFSET за допомогою COUNT. І використовував це як аргумент функції MEDIAN, яка потім сортувала значення року у порядку зростання чи зменшення, а потім давала вихід у 2017 році.

Використання формули OFFSET полягає у створенні динамічних діапазонів. Завжди краще працювати на динамічних діапазонах, а не на статичних. Оскільки ви, можливо, матимете дані, які додаються раз у раз. Наприклад, Майстер працівника. Основні дані Вашого працівника завжди будуть збільшуватися раз у раз, тому що повинні бути приєднані нові співробітники. Кожен раз, коли використовувати одну і ту ж формулу, немає сенсу. Замість цього створіть діапазон, який є динамічним, використовуючи функцію OFFSET і побачте магію. Це, безумовно, заощадить ваш час.

Що потрібно пам’ятати

Функція OFFSET повертає лише посилання на певний діапазон комірок у Excel. Він не переміщує осередки з початкового положення.
За замовчуванням ця функція працює у верхній частині (у рядках) та праворуч (у стовпцях). Однак це може бути забезпечено негативними аргументами для зміни функцій за замовчуванням. Як -негативний аргумент рядка допомагає компенсувати дані знизу вгору, а негативний аргумент col допомагає компенсувати дані зліва.
Формула OFFSET може уповільнити функціональність Excel, оскільки це формула, яка перераховується щоразу, коли відбувається зміна робочого аркуша.
Помилка #REF буде видалена, якщо ваш діапазон виходить за рамки вашої робочої книги.
Будь-яка інша функція, яка очікує, що посилання може використовувати функцію OFFSET всередині неї.

OFFSET Формула в Excel - Як використовувати функцію OFFSET в Excel?

Що таке формула OFFSET в Excel?