Функция 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?
-> Лучшие инструменты для повышения производительности офиса
Источник: