Функция СМЕЩ() в MS EXCEL

Функция СМЕЩ(), английский вариант OFFSET(), возвращает ссылку на диапазон ячеек. Размер диапазона и его положение задается в параметрах этой функции.

Функция СМЕЩ() часто используется при создании динамических диапазонов. Рассмотрим ее подробнее.

Синтаксис функции СМЕЩ()

СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;высота;ширина), где

  • ссылка - ссылка, от которой вычисляется смещение;
  • смещ_по_строкам - количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =0);
  • смещ_по_столбцам – аналогично смещ_по_строкам, только смещение отсчитывается по столбцам (по умолчанию =0);
  • высота - число строк возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом;
  • ширина - число столбцов возвращаемой ссылки (по умолчанию =1).

Чтобы было понятнее, потренируемся с функцией СМЕЩ(), используя файл примера.

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ(), использовано Условное форматирование. Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик.

Для подсчета суммы значений в столбце Продажа1 запишем формулу:
=СУММ(СМЕЩ($B$2;0;0;8;1))
диапазон суммирования - $B$2:$B$9 (левый верхний угол - $B$2, высота 8, смещения верхнего угла нет). Результат 34.

Для подсчета суммы значений в столбце Продажа2 запишем формулу:
=СУММ(СМЕЩ($B$2;0;1;8;1))
Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9. Результат 68.

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона.
=СУММ(СМЕЩ($B$2;0;0;8;2))
указав ширину в 2 ячейки, результат составит 102, диапазон будет модифицирован в $В$2:$С$9.

Добавив смещение по строкам (+1), получим результат 99:
=СУММ(СМЕЩ($B$2;1;0;8;2))

диапазон будет модифицирован в $В$3:$С$9.

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами (А2:А10) Необходимо найти сумму первых 2-х, 3-х, ...9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3)=СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

=СУММ(СМЕЩ(A2;;;4))

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)), которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.9 (11 оценок)