Excel7 мин·
СМЕЩ (OFFSET) — динамические диапазоны в Excel
Функция СМЕЩ: создание диапазонов, которые автоматически расширяются. Динамические графики, выпадающие списки, подсчёт последних N значений.
Что делает СМЕЩ
СМЕЩ (OFFSET) возвращает ссылку на диапазон, сдвинутый от начальной ячейки на заданное количество строк и столбцов. Звучит абстрактно, но на практике это основной инструмент для диапазонов, которые растут вместе с данными.
Синтаксис
=СМЕЩ(начальная_ячейка; строки_сдвига; столбцы_сдвига; [высота]; [ширина])
Ссылка на ячейку, которая на 3 строки ниже и 2 столбца правее от A1:
=СМЕЩ(A1;3;2)
💡 Совет: Без высоты и ширины СМЕЩ возвращает одну ячейку. С ними — диапазон.
Динамический диапазон для СУММ
Сумма всех заполненных ячеек в столбце B (от B2 до последней):
=СУММ(СМЕЩ(B2;0;0;СЧЁТЗ(B:B)-1;1))
💡 Совет: СЧЁТЗ(B:B)-1 = количество заполненных ячеек минус заголовок. Это и есть высота диапазона.
Последние N значений
Среднее последних 7 значений в столбце B:
=СРЗНАЧ(СМЕЩ(B1;СЧЁТЗ(B:B)-7;0;7;1))
Динамический график
Создайте именованный диапазон «ДинДанные»:
=СМЕЩ(Лист1!$B$2;0;0;СЧЁТЗ(Лист1!$B:$B)-1;1)
Используйте это имя как источник данных для графика. При добавлении строк график обновится автоматически — не нужно вручную менять диапазон.
СМЕЩ для выпадающего списка
Именованный диапазон для списка, который растёт:
=СМЕЩ(Справочник!$A$2;0;0;СЧЁТЗ(Справочник!$A:$A)-1;1)
В проверке данных: Источник = имя этого диапазона. Добавили город в справочник — он появится в выпадающем списке.
СМЕЩ vs умные таблицы (Ctrl+T)
В Excel 365 умные таблицы (Ctrl+T) расширяются автоматически. СМЕЩ для этого не нужна. Но СМЕЩ по-прежнему полезна:
- Когда нужны последние N значений
- Когда нельзя преобразовать данные в таблицу
- В старых версиях Excel
- Для сложных сдвигов (не только вниз, но и вправо)
Заключение
СМЕЩ — мощная, но «тяжёлая» функция (пересчитывается при каждом изменении). Используйте для динамических диапазонов, последних N значений, автоматически обновляемых графиков. Где можно — предпочитайте умные таблицы. Fast Formula подберёт формулу с СМЕЩ для вашей задачи.
🤖
Не хотите запоминать формулы?
Опишите задачу на русском — AI сгенерирует формулу с правильными ссылками на вашу таблицу. 5 запросов бесплатно.
Попробовать бесплатно →Читайте также
ТОП-20 формул Excel, которые должен знать каждый бухгалтер
Подборка самых нужных формул Excel для бухгалтерии: СУММЕСЛИ, ВПР, ЕСЛИ, СЧЁТЕСЛИ и другие. С примерами и объяснениями на русском.
Как сделать ВПР (VLOOKUP) в Google Таблицах — полное руководство
Подробное руководство по VLOOKUP в Google Sheets: синтаксис, примеры, ошибки, альтернативы (INDEX/MATCH, XLOOKUP). С картинками и объяснениями.