Подробный ответ
Функция СМЕЩ (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.
Практическая деталь
СМЕЩ возвращает ссылку на диапазон и пересчитывается как волатильная функция. В большой книге по возможности используйте столбцы умной таблицы или ИНДЕКС для границы диапазона.
| A | B | |
|---|---|---|
| 1 | Месяц | Продажи |
| 2 | Январь | 100 |
| 3 | Февраль | 200 |
| 4 | Март | 300 |
| 5 | Последние два | 200 |
- B5
=СУММ(СМЕЩ(B2;1;0;2;1))→ 200
СМЕЩ начинает с B2, сдвигается вниз на одну строку и возвращает две строки высотой: B3:B4.