Перейти до основного матеріалу

перевершувати OFFSET функція

Excel Функція OFFSET повертає посилання на комірку або діапазон комірок, що зміщується від певної комірки заданою кількістю рядків і стовпців.


синтаксис

=OFFSET (reference, rows, cols, [height], [width])


Аргументи

Посилання (вимагається): Клітинка або діапазон сусідніх комірок, які ви встановите початковою точкою.
ряди (вимагається): Кількість рядків для переміщення вгору (від’ємне число) або вниз (позитивне число) від початкової точки.
нашийники (вимагається): Кількість стовпців для переміщення вліво (від’ємне число) або вправо (додатне число) від початкової точки.
висота (необов'язковий): Кількість рядків, які потрібно повернути. Висота повинна бути додатним числом.
ширина (необов'язковий): Кількість стовпців, які потрібно повернути. Ширина повинна бути додатним числом.


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

Функція OFFSET поверне зсув опорної комірки від заданої початкової точки.


Примітки до функцій

1. # ЗНАЧЕННЯ! значення помилки повернеться, коли посилання посилається на діапазон розривних комірок.
2. # REF! значення помилки повернеться, коли рядки та стовпці змістять посилання над краєм аркуша.


прикладів

Приклад 1: Основне використання функції OFFSET

Повернути посилання на клітинку з формулою нижче:
=OFFSET(B2,3,1)

У цьому випадку B2 є початковою точкою, цифри 3 і 1 означають, що потрібно перемістити 3 рядки вниз і 1 стовпець прямо з комірки B2 і повернути значення в C5 остаточно. Дивіться знімок екрана:

Повернути посилання на діапазон комірок із формулою нижче:
=OFFSET(B2,3,1,2,2)

У цьому випадку ви отримаєте результати діапазону 2 x 2, тобто 3 рядки нижче та 1 стовпець праворуч від комірки B2.

примітки: # ЗНАЧЕННЯ! Помилка трапиться, коли ви виберете лише одну комірку, щоб застосувати функцію OFFSET для повернення діапазону комірок. Потрібно вибрати діапазон 2 х 2 (наприклад, 4 порожні клітинки), ввести формулу та натиснути Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у клавіші для отримання результатів.

Приклад 2: Використовуйте функцію OFFSET для підсумовування діапазону значень

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

1. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
=SUM(OFFSET(B2,3,1,2,2)))

Приклад 3: Сума стовпців на основі певних критеріїв

Як показано на знімку екрана, як отримати загальний обсяг продажів Mango від Sun до Tue? Будь ласка, спробуйте, як показано нижче.

1. Виділіть порожню комірку, скопіюйте в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

примітки:

1. У наведеній вище формулі MATCH(G4,B3:B8) шукає Манго і повертає свою позицію в діапазоні B3: B8. Подивимось, Манго розміщує 5 рядків під коміркою В2, тому повертає число 5;
2. Оскільки результат МАТЧ є 5, а остання кома тут представляє 0, функція OFFSET тепер відображається як OFFSET(C2:E2,5,0), що означає, що зміщення починається з діапазону C2: E2, перемістіть на 5 рядків вниз і 0 стовпця вправо, щоб отримати посилання на діапазон C7: E7;
3. Потім функція SUM відображається як SUM(C7:E7)і, нарешті, повертає суму значень у діапазоні C7: E7.


Інші приклади

Як підсумувати кожні n рядків у Excel?

Як створити динамічний іменований діапазон в Excel?

Як повернути значення комірки кожні п’ятий або n-й рядок у Excel?


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

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


Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations