Как использовать функцию СМЕЩЕНИЕ в Excel?

Функция Excel OFFSET возвращает ссылку на ячейку или диапазон ячеек, которые смещены от определенной ячейки на заданное число. строк и столбцов.


Синтаксис


Аргументы

Ссылка ( требуется ): Ячейка или диапазон соседних ячеек, которые вы установите в качестве начальной точки.
Строки ( обязательно ): количество строк для перемещения вверх (отрицательное число) или вниз (положительное число) от начальной точки.
Столбцы ( обязательно ): количество столбцов, которые нужно переместить влево (отрицательное число) или вправо (положительное число) от начальной точки.
Высота ( необязательно ): количество возвращаемых строк. Высота должна быть положительным числом.
Ширина ( необязательно ): количество столбцов, которые вы хотите вернуть. Ширина должна быть положительным числом.


Возвращаемое значение

Функция OFFSET вернет смещение ссылки на ячейку от заданного начального значения. точка.


Примечания к функциям

1. #ЗНАЧЕНИЕ! значение ошибки будет возвращено, если ссылка относится к диапазону прерывистых ячеек.
2. #REF! значение ошибки будет возвращено, когда ссылка на строки и столбцы смещается за край листа.


Примеры

Пример 1: Базовое использование для СМЕЩЕНИЯ функция

Возвращает ссылку на ячейку с приведенной ниже формулой:

В этом случае B2 — начальная точка, числа 3 и 1, означают, что нужно переместить 3 строки вниз и 1 столбец вправо из ячейки B2 и, наконец, вернуть значение в C5. См. Снимок экрана:

Вернуть ссылку на диапазон ячеек по следующей формуле:

В этом случае вы получите результаты для диапазона 2 x 2, который находится на 3 строки ниже и 1 столбец справа от ячейки B2.

Примечание : #VALUE! Ошибка произойдет, если вы выберете только одну ячейку для применения функции СМЕЩЕНИЕ для возврата диапазона ячеек. Вам нужно выбрать диапазон 2 x 2 (говорит 4 пустые ячейки), ввести формулу и нажать Ctrl + Shift + Enter. , чтобы получить результаты.

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

Как мы упоминали выше, если вы попытаетесь используйте функцию СМЕЩЕНИЕ, такую ​​как , в отдельной ячейке, она вернет #VALUE! Ошибка. Однако, если вы объедините функции СУММ и СМЕЩЕНИЕ, как показано на скриншоте ниже, она вернет сумму значений в диапазоне C5: D6 напрямую.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите клавишу Enter , чтобы получить результат..

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

Как показано на скриншоте ниже, как получить общие продажи манго с воскресенья по вторник? Попробуйте, как показано ниже.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите клавишу Enter , чтобы получить результат.

Примечания :

1. В приведенной выше формуле ищет манго и возвращает его позицию в диапазоне B3: B8 . Давайте посмотрим, Mango размещает 5 строк под ячейкой B2, поэтому возвращает число 5;
2. Поскольку результат MATCH равен 5, а последняя запятая здесь представляет 0, функция OFFSET теперь отображается как , что означает, что смещение начинается с диапазона C2: E2, переместите 5 строк вниз и 0 столбцов вправо, чтобы получить ссылку на диапазон C7: E7;
3. Затем функция СУММ отображается как и, наконец, вернуть сумму значений в диапазоне C7: E7.


Дополнительные примеры

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

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

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


-> Лучшие инструменты для повышения производительности офиса


Оцените статью
Frestage.ru
Добавить комментарий