Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

перевершувати 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 + зсув + вводити клавіші для отримання результатів.

Приклад 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 - допомагає виділитися з натовпу

Ви хотіли б швидко і довершити свою щоденну роботу? Kutools для Excel надає 300 потужних розширених функцій (Об’єднайте робочі книги, підсумовуйте за кольором, розділіть вміст комірок, конвертуйте дату тощо) і заощаджуйте для вас 80% часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

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

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.