Подробный ответ
Функция СМЕЩ (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) — диапазон в столбце A от A1 до последней заполненной строки. При добавлении строк диапазон расширяется автоматически.
Практическое применение
- Динамические диаграммы, которые автоматически показывают только последние N записей
- Именованные диапазоны с переменным размером
- Суммирование «последних N значений»:
=СУММ(СМЕЩ(A100;0;0;-10;1))
Важно
СМЕЩ — «летучая» (volatile) функция, которая пересчитывается при каждом изменении на листе. На больших данных это замедляет работу. Альтернатива для динамических диапазонов — умные таблицы Excel.