Excel OFFSET Функція (Зміст)

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

OFFSET в Excel

Функція OFFSET в Excel допомагає користувачеві повернути комірку або діапазон комірок, який є вказаним "ні". рядків і стовпців із комірки. Ми можемо вказати ні. рядків і немає. стовпців, які потрібно повернути.

Нижче формула OFFSET в Excel:

Функція OFFSET в Excel складається з таких аргументів:

  • Довідка: Це аргумент, на якому ми хочемо базувати компенсацію. Це може бути посилання на клітинку або діапазон сусідніх комірок, якщо не Зсув повертає # VALUE! Значення помилки
  • Рядки: Це ні. рядків для компенсації. Якщо ми використовуємо додатне число, то воно зміщується на рядки внизу, а якщо використовується негативне число, то воно компенсує рядки вище.
  • Стовпці: Це для ні. стовпців для зміщення. Отже, це те саме поняття, що і "Рядки", якщо ми використовуємо додатне число, то воно зміщується до стовпців праворуч, а якщо використовується від'ємне число, то воно зміщується до стовпців зліва.
  • Висота: Цей аргумент необов’язковий. Це повинно бути додатне число. Це число, яке представляє кількість рядків у поверненій посилання.
  • Ширина: це також необов'язковий аргумент. Це повинно бути додатне число. Це число, яке представляє кількість стовпців у поверненій посилання.

Примітки:

  • Функція OFFSET в Excel повертає #REF! значення помилки, якщо рядки та стовпці зміщують посилання на край робочого аркуша.
  • У функції OFFSET вона повинна бути такої ж висоти чи ширини, що й опорна, якщо висота чи ширина опущені.
  • OFFSET повертає посилання, воно фактично не переміщує жодні комірки або діапазон комірок і не змінює виділення. Його можна використовувати з будь-якою функцією, що очікує опорного аргументу.

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

Функція OFFSET в Excel дуже проста і проста у використанні. Дайте зрозуміти роботу функції OFFSET в Excel на прикладі формули OFFSET.

Ви можете завантажити цей шаблон програми OFFSET Excel Шаблон тут - OFFSET Шаблон функції Excel

Приклад №1

Формула зміщення повертає посилання на комірку на основі початкової точки, рядків та стовпців, які ми вказуємо. Ми можемо бачити це в наведеному нижче прикладі:

= OFFSET (A1, 3, 1)

Формула наказує Excel розглянути клітинку A1 для початкової точки (посилання), потім перемістити 3 рядки вниз (рядки) та 1 стовпець вліво (аргумент стовпців). Після цього формула OFFSET повертає значення в комірці B4.

Знімок зліва показує, що маршрут функції та скріншот праворуч демонструє, як ми можемо використовувати формулу OFFSET за даними реального часу. Різниця між двома формулами полягає в тому, що друга (праворуч) включає в аргументі рядка посилання на клітинку (D1). Але оскільки комірка D1 містить число 1, а точно таке ж число з'являється в аргументі рядків першої формули, обидва повернуть однаковий результат - значення в B2.

Приклад №2 Використання функції OFFSET для суми

Оскільки ми знаємо, що функцію OFFSET можна використовувати і з іншою функцією Excel, отже, у цьому прикладі ми побачимо функцію SUM.

Припустімо, на наведеному вище малюнку, ми маємо знайти суму знаків, тоді ми також можемо використовувати функцію OFFSET.

Формула наведена нижче:

Отже, у наведеній вище формулі D5 - це перша клітина, з якої починаються дані, яка є посиланням як вихідна комірка. Після цього значення рядків і стовпців дорівнює 0, тому ми можемо поставити це як 0, 0. Тоді є 4, що означає 4 рядки нижче посилання, для якого Excel потрібно обчислити суму, і, нарешті, це 1, це означає, що є 1 стовпець як ширина.

Ми можемо віднести вищезгаданий малюнок, щоб співвіднести пояснений приклад.

А нижче результат:

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

Що слід пам’ятати про функцію OFFSET в Excel

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

Наприклад, якщо ми використовуємо формулу = OFFSET (A1, 3, 1, 1, 3) самостійно, вона дасть # VALUE! Помилка, оскільки діапазон повернення (1 рядок, 3 стовпці) не вміщується в комірку. Однак якщо ми поєднаємо його з функцією SUM, як показано нижче:

= SUM (OFFSET (A1, 3, 1, 1, 3))

Формула зміщення повертає суму значень у 1 рядку за допомогою діапазону 3 стовпців, який знаходиться на 3 ряди нижче та 1 стовпчик праворуч від комірки A1, тобто загальну величину значень у клітинках B4: D4.

Як ми бачимо, загальна кількість 36 + 63 + 82 виділеного Мар дорівнює 181, що вийшло в результаті G2 після застосування суми та формули зміщення. (виділено жовтим кольором)

Використання функції OFFSET в Excel

Як ми бачили, що те, що насправді функція OFFSET робить за допомогою наведеного вище прикладу, у нас можуть виникнути питання, чому Чому турбувати заспівану формулу OFFSET, яка є дещо складною, чому б просто не використати прямий посилання на зразок суми або B4: D4?

Функція OFFSET в Excel дуже хороша для нижче:

  • Отримання діапазону від вихідної комірки:

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

  • Створення динамічного діапазону:

Посилання на зразок вищенаведеного прикладу B1: C4 є статичними, це означає, що вони завжди посилаються на заданий або фіксований діапазон. Але в деяких випадках завдання легше виконувати з динамічними діапазонами. Особливо це допомагає, коли ми працюємо зі зміною даних. Наприклад, ми можемо мати електронну таблицю, де новий рядок або стовпець вставляється або додається щодня / тиждень / місяць, в цьому випадку допоможе функція OFFSET.

Обмеження та альтернативи функції OFFSET в Excel

Оскільки у кожної формули в Excel є свої обмеження та альтернативи, ми побачили, як і коли використовувати функцію OFFSET в Excel.

Нижче наведені критичні обмеження функції OFFSET в Excel:

  • Функція, що проголошує ресурс:

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

  • Складність у перегляді:

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

Альтернативи функції OFFSET в Excel:

  • Функція INDEX:

Функція INDEX в Excel може також використовуватися для створення динамічного діапазону посилань. Це не точно так само, як OFFSET. Але як і OFFSET, функція INDEX не настільки мінлива, отже, вона не сповільнить Excel, що є обмеженням для OFFSET.

  • Непряма функція:

Ми також можемо використовувати функцію INDIRECT для створення динамічного діапазону посилань з багатьох джерел, таких як значення комірок , текст та названі діапазони.

Рекомендовані статті

Це був посібник для OFFSET в Excel. Тут ми обговорюємо формулу OFFSET в Excel та як використовувати функцію OFFSET в Excel разом з практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. MS Excel: Функція LOOKUP
  2. Кращий приклад функції Excel XIRR
  3. Дивовижний посібник про НЕ функцію в Excel
  4. IPMT в Excel з прикладами

Категорія:

Поради Excel