Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

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

Вероятность: 50%
Краткий ответ

СМЕЩ возвращает ячейку или диапазон, сдвинутый от начальной точки на заданное количество строк и столбцов: =СМЕЩ(A1;3;2) вернёт ссылку на C4; в ячейке отобразится её значение. Размер диапазона меняется, если его высота или ширина вычисляются формулой.

Подробный ответ

Функция СМЕЩ (OFFSET) в Excel

СМЕЩ возвращает ссылку на диапазон, смещённый от начальной ячейки.

Синтаксис: =СМЕЩ(начало; строки; столбцы; [высота]; [ширина])

Примеры смещения ячейки

  • =СМЕЩ(A1;2;1) — сместиться на 2 строки вниз и 1 столбец вправо → B3
  • =СМЕЩ(B5;-1;0) — ячейка выше B5, то есть B4

Возврат диапазона (с высотой и шириной)

=СУММ(СМЕЩ(A1;0;0;5;3)) — сумма диапазона 5 строк × 3 столбца, начиная с A1.

Динамический диапазон через СМЕЩ

=СМЕЩ($A$1;0;0;СЧЁТЗ($A:$A);1) — диапазон от A1 высотой, равной числу непустых ячеек столбца A. Это соответствует заполненной области только при непрерывных данных от A1, без посторонних значений ниже; формулы с пустой строкой тоже учитываются СЧЁТЗ. Пустой столбец даст нулевую недопустимую высоту.

Практическое применение

  • Динамические диаграммы, которые автоматически показывают только последние N записей
  • Именованные диапазоны с переменным размером
  • Суммирование десяти строк до A100 включительно: =СУММ(СМЕЩ(A100;-9;0;10;1)) — диапазон A91:A100; высота положительная. Здесь последняя строка задана явно, а не определяется автоматически

Важно

СМЕЩ — «летучая» (volatile) функция, которая пересчитывается при каждом цикле пересчёта. Большое число таких формул может замедлять книгу. Альтернатива для динамических диапазонов — умные таблицы Excel.

Практическая деталь

СМЕЩ возвращает ссылку на диапазон и пересчитывается как волатильная функция. В большой книге по возможности используйте столбцы умной таблицы или ИНДЕКС для границы диапазона.

Сумма со смещением
AB
1 МесяцПродажи
2 Январь100
3 Февраль200
4 Март300
5 Последние два200
B5
=СУММ(СМЕЩ(B2;1;0;2;1)) → 200

СМЕЩ начинает с B2, сдвигается вниз на одну строку и возвращает две строки высотой: B3:B4.

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.