Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

Excel 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 x 2 (говорит о пустых ячейках 4), ввести формулу и нажать Ctrl + сдвиг + вводить ключи, чтобы получить результаты.

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

Как мы упоминали выше, если вы попытаетесь использовать функцию OFFSET, такую ​​как =OFFSET(B2,3,1,2,2) Сам по себе в одной ячейке он вернет # ЗНАЧЕНИЕ! Ошибка. Однако, если вы объедините функции 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, Давайте посмотрим, Mango находит строки 5 ниже ячейки B2, поэтому возвращает номер 5;
2. Поскольку результатом MATCH является 5, а последняя запятая здесь представляет 0, функция OFFSET теперь отображается как OFFSET(C2:E2,5,0), что означает, что смещение начинается с диапазона C2: E2, переместите строки 5 вниз и столбец 0 вправо, чтобы получить ссылку на диапазон C7: E7;
3. Тогда функция СУММА отображается как SUM(C7:E7)и, наконец, вернуть сумму значений в диапазоне C7: E7.


Больше примеров

Как суммировать все n строк в Excel?

Как создать динамический именованный диапазон в Excel?

Как вернуть значение ячейки каждые пять или n-я строка в Excel?


Инструменты для повышения производительности Excel

Лента Excel (с установленным Kutools для Excel)

Расширенные функции 300 помогут вам выделиться из толпы

Хотели бы вы завершить свою ежедневную работу быстро и идеально? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение рабочих книг, суммирование по цвету, разделение содержимого ячеек, конвертирование даты и т. д.) и экономию времени 80%.

  • Предназначен для рабочих сценариев 1500, помогает решать проблемы 80% Excel.
  • Ежедневно уменьшайте тысячи нажатий клавиш и клавиатуры, снимайте усталость с глаз и рук.
  • Станьте экспертом по Excel за минуты 3. Больше не нужно помнить какие-либо болезненные формулы и коды VBA.
  • 60-день неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 лет.

Снимок экрана Excel (с установленной вкладкой Office)

Приносит эффективные вкладки в офис (включая Excel), так же, как Chrome, Firefox и новый IE

  • Office Tab Повышает вашу производительность на 50% при просмотре и редактировании нескольких документов.
  • Сократите сотни щелчков мышью для вас каждый день, попрощайтесь с рукой мыши.
  • Одна секунда, чтобы переключаться между десятками открытых документов!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.