Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel OFFSET в Excel для створення зміщення посилання від початкової комірки.
Огляд функції OFFSET
Функція OFFSET Починається з визначеного посилання на комірку і повертає посилання на комірку певну кількість рядків і стовпців, зміщених від вихідного референсу. Посилання можуть бути однією клітиною або діапазоном клітинок. Зсув також дозволяє змінювати розмір посилання на певну кількість рядків/стовпців.
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис і входи функції IFERROR:
| 1 | = OFFSET (посилання, рядки, колі, висота, ширина) |
довідковий - Початкове посилання на клітинку, від якого потрібно здійснити зсув.
рядків - Кількість рядків для зміщення.
cols - Кількість стовпців для зміщення.
висота - НЕОБОВ’ЯЗКОВО: Налаштуйте кількість рядків у посиланні.
ширина - НЕОБОВ’ЯЗКОВО: Налаштуйте кількість стовпців у посиланні.
Що таке функція OFFSET?
Функція OFFSET є однією з найпотужніших функцій електронних таблиць, оскільки може бути досить універсальною у створенні. Це дає користувачеві можливість визначати клітинку або діапазон у різних положеннях та розмірах.
УВАГА: Функція OFFSET є однією з мінливих функцій. У більшості випадків, коли ви працюєте над своєю електронною таблицею, комп’ютер перераховує формулу лише тоді, коли вхідні дані змінили свої значення. Однак мінлива функція перераховується кожен час змінити будь -яку клітинку. Слід бути обережним, щоб уникнути великого часу перерахунку через надмірне використання летких функцій або наявність багатьох клітин залежно від результату летючої функції.
Основні приклади рядків
При кожному використанні функції OFFSET вам потрібно давати вихідну точку або прив’язку. Давайте розглянемо цю таблицю, щоб зрозуміти це:
Ми будемо використовувати "Bob" у комірці B3 як нашу опорну точку. Якби ми хотіли схопити значення трохи нижче (Чарлі), ми б сказали, що ми хочемо зрушити рядок на 1. Наша формула виглядатиме так
| 1 | = ЗМІЩЕННЯ (B3, 1) |
Якби ми хотіли зрушити вгору, це було б негативним зрушенням. Ви можете думати про це, оскільки число рядка зменшується, тому нам потрібно відняти. Таким чином, щоб отримати значення вище (Адам), ми б написали
| 1 | = ЗМІЩЕННЯ (В2, -1) |
Основні приклади стовпців
Продовжуючи ідею з попереднього прикладу, ми додамо до нашої таблиці ще один стовпець.
Якби ми хотіли схопити вчителя для Боба, ми могли б скористатися формулою
| 1 | = ЗМІЩЕННЯ (B2, 0, 1) |
У цьому випадку ми сказали, що ми хочемо змістити нульові рядки (він же залишитися в одному рядку), але ми хочемо змістити 1 стовпець. Для стовпців позитивне число означає зміщення праворуч, а від’ємне - зміщення ліворуч.
OFFSET і MATCH
Припустимо, у вас є кілька стовпців даних, і ви хочете дати користувачеві можливість вибрати, з якого стовпця отримувати результати. Ви можете скористатися функцією INDEX або скористатися OFFSET. Оскільки MATCH поверне відносне положення значення, нам потрібно переконатися, що точка прив’язки знаходиться зліва від нашого першого можливого значення. Розглянемо такий макет:
У B2 ми напишемо цю формулу:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH буде виглядати "лютий" у діапазоні C1: F1 і знайти його у 2nd клітинка. Потім OFFSET змістить 1 стовпець праворуч від B2 і захопить потрібне значення 9. Зауважте, що OFFSET не має проблем із використанням тієї самої комірки, що містить формулу, як опорну точку.
ПРИМІТКА. Цей прийом можна використовувати як заміну для VLOOKUP або HLOOKUP, коли потрібно повернути значення зліва/над діапазоном пошуку. Це тому, що OFFSET може робити негативні зміщення.
OFFSET, щоб отримати діапазон
Ви можете використовувати 4го та 5го аргументи у функції OFFSET, щоб повернути діапазон, а не лише одну клітинку. Припустимо, ви хочете підсумувати 3 стовпці в цій таблиці.
| 1 | = СЕРЕДНЯ (ЗМІЩЕННЯ (A1, МАТЧ (F2, A2: A5,0), 1,1,3)) |
У F2 ми вибрали ім’я студента, для якого ми хочемо отримати його середній бал тестів. Для цього ми будемо використовувати формулу
| 1 | = СЕРЕДНЯ (ЗМІЩЕННЯ (A1, МАТЧ (F2, A2: A5,0), 1,1,3)) |
MATCH збирається шукати у стовпці A наше ім'я та повертати відносне положення, яке у нашому прикладі становить 3. Подивимось, як це оцінюватимуть. По -перше, ЗМІНШЕННЯ піде вниз 3 рядки від A1 і 1 стовпець до праворуч від А1. Це поміщає нас у клітинку В3.
| 1 | = СЕРЕДНЯ (ЗМІЩЕННЯ (A1, 3, 1, 1, 3)) |
Далі ми збираємося змінити розмір діапазону. Новий діапазон матиме B3 як верхню ліву клітинку. Це буде 1 рядок заввишки та 3 стовпці заввишки, що дає нам діапазон B4: D4.
| 1 | = СЕРЕДНЯ (ЗМІЩЕННЯ (A1,3, 1, 1, 3)) |
Зауважте, що хоча ви можете законно вводити від'ємні значення в аргументи зміщення, ви можете використовувати лише негативні значення в аргументах розміру.
Наприкінці наша функція AVERAGE бачить:
| 1 | = СЕРЕДНЯ (B4: D4) |
Таким чином, ми отримуємо наше рішення 86,67
ЗМІЩЕННЯ з динамічною сумою
Оскільки OFFSET використовується для пошуку посилання, а не для прямого вказування на клітинку, це найбільш корисно, коли ви маєте справу з даними, у яких додано або видалено рядки. Розглянемо таку таблицю з підсумком внизу
| 1 | = SUM (B2: B4) |
Якби ми використовували тут базову формулу SUM "= SUM (B2: B4)", а потім вставляли новий рядок, щоб додати запис для Білла, ми б отримали неправильну відповідь
Натомість давайте подумаємо, як це вирішити з точки зору Total. Ми дійсно хочемо захопити все - від клітини В2 до клітини трохи вище нашого загального. Як ми можемо записати це у формулі, це зробити зміщення рядка -1. Таким чином, ми використовуємо це як формулу для нашого загального числа в комірці В5:
| 1 | = SUM (B2: ЗМІСТ (B5, -1,0)) |
Ця формула робить те, що ми щойно описали: починаємо з В2 і переходимо до 1 клітинки вище нашої загальної клітини. Ви можете побачити, як після додавання даних Білла наша сума правильно оновлюється.
OFFSET, щоб отримати останні N елементів
Скажімо, ви реєструєте щомісячні продажі, але хочете мати можливість подивитися на останні 3 місяці. Замість того, щоб вручну оновлювати формули, щоб продовжувати коригувати додавання нових даних, ви можете скористатися функцією OFFSET з COUNT.
Ми вже показали, як можна використовувати OFFSET для захоплення діапазону клітинок. Щоб визначити, скільки клітинок нам потрібно змінити, ми будемо використовувати COUNT, щоб знайти скільки цифри є у стовпці B. Давайте подивимось на нашу таблицю зразків.
| 1 | = SUM (ЗМІЩЕННЯ ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Якби ми почали з B1 і змістили 4 рядки (кількість чисел у стовпці B), ми опинилися б у нижній частині нашого діапазону, B5. Однак, оскільки OFFSET не може змінювати розмір з від'ємним значенням, нам потрібно зробити деякі коригування, щоб ми опинилися в B3. Для цього буде зроблено загальне рівняння
| 1 | COUNT (…) - N + 1 |
Ми беремо підрахунок всього стовпця, віднімаємо стільки, скільки хочемо повернути (оскільки ми будемо змінювати розмір, щоб захопити їх), а потім додаємо 1 (оскільки ми по суті починаємо зсув з нульової позиції).
Тут ви можете побачити, що ми встановили діапазон, щоб отримати суму, середнє та максимальне значення за останні N місяців. У E1 ми ввели значення 3. У E2 наша формула така
| 1 | = SUM (ЗМІЩЕННЯ ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Виділений розділ - це наше загальне рівняння, яке ми щойно обговорювали. Нам не потрібно зміщувати жодні стовпці. Потім ми збираємось змінити розмір діапазону на 3 клітини заввишки (визначається значенням в E1) і 1 стовпець у ширину. Наша сума тоді бере цей діапазон і дає нам результат у 1850 доларів. Ми також показали, що можна обчислити середнє значення max цього ж діапазону, просто переключивши зовнішню функцію з SUM на все, що вимагає ситуація.
Списки динамічної перевірки OFFSET
Використовуючи методику, показану в останньому прикладі, ми також можемо створювати іменовані діапазони, які можна використовувати для перевірки даних або діаграм. Це може бути корисним, якщо ви хочете налаштувати електронну таблицю, але очікуєте, що наші списки/дані змінять розмір. Скажімо, наш магазин починає продавати фрукти, і зараз у нас є 3 варіанти.
Щоб зробити спадне меню Перевірка даних, яке ми можемо використовувати в іншому місці, ми визначимо названий діапазон MyFruit як
| 1 | = $ A $ 2: ЗМІЩЕННЯ ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Замість COUNT ми використовуємо COUNTA, оскільки маємо справу з текстовими значеннями. Однак через це наша COUNTA буде на одну вище, оскільки вона буде рахувати комірку заголовка в A1 і давати значення 4. Якщо ми змістимо на 4 рядки, ми опинимось у комірці A5, яка є порожньою. Щоб відкоригувати це, ми віднімаємо 1.
Тепер, коли у нас є налаштування іменованого діапазону, ми можемо налаштувати деяку перевірку даних у комірці С4 за допомогою типу List із джерелом:
| 1 | = MyFruit |
Зауважте, що у спадному меню відображаються лише три поточні елементи. Якщо ми потім додамо до нашого списку більше елементів і повернемося до випадаючого списку, у списку будуть відображені всі нові елементи без необхідності змінювати будь -яку формулу.
Застереження щодо використання OFFSET
Як згадувалося на початку цієї статті, OFFSET - це мінлива функція. Ви не помітите цього, якщо використовуєте його лише в кількох клітинках, але якщо ви почнете залучати його до сотень обчислень, то швидко помітите, як ваш комп’ютер витрачає помітну кількість часу на перерахунок кожного разу, коли ви вносите будь -які зміни .
Крім того, оскільки OFFSET не називає безпосередньо клітинки, на які він дивиться, іншим користувачам важче зайти пізніше та змінити формули, якщо це буде потрібно.
Замість цього, було б доцільно використовувати таблиці (представлені в Office 2007), які дозволяють посилання на структуру. Це допомогло користувачам надати єдине посилання, яке автоматично коригувалося за розміром при додаванні чи видаленні нових даних.
Інший варіант замість OFFSET - це потужна функція INDEX. INDEX дозволяє створювати всі динамічні діапазони, які ми бачили в цій статті, без проблеми нестабільності функції.
додаткові нотатки
Використовуйте функцію OFFSET, щоб повернути значення комірки (або діапазон комірок), змістивши задану кількість рядків і стовпців від початкового посилання. Коли шукають лише одну клітинку, формули OFFSET досягають тієї ж мети, що і формули INDEX, використовуючи дещо іншу техніку. Справжня сила функції OFFSET полягає в її здатності вибирати діапазон комірок для використання в іншій формулі.
При використанні функції OFFSET ви визначаєте початкову початкову клітинку або діапазон комірок. Потім ви вказуєте кількість рядків і стовпців для відступу від цієї початкової комірки. Ви також можете змінити розмір діапазону; додавання або віднімання рядків або стовпців.
Повернення до списку всіх функцій Excel
OFFSET у Google Таблицях
Функція OFFSET працює точно так само в Google Таблицях, як і в Excel:
