Функция СМЕЩ (OFFSET) в Excel и Google Sheets. Полное руководство

Кратко

Что делает:
Возвращает ссылку на диапазон, отстоящий от заданной ячейки или диапазона на указанное число строк и столбцов .

Где работает:
✅ Excel (все версии)
✅ Google Sheets — поддерживается
✅ МойОфис Таблицы

Ключевая особенность:
Не передвигает ячейки и не меняет выделение, а только возвращает ссылку, которую можно использовать в других функциях .

Сложность:
★★★☆☆ — Средняя (требует понимания работы со ссылками и смещениями)

Для чего нужна функция

СМЕЩ (OFFSET) — это универсальная функция для создания динамических диапазонов. Она позволяет «перемещаться» по листу от заданной точки на указанное количество строк и столбцов и возвращать ссылку на нужную ячейку или диапазон . В отличие от большинства других функций, она не возвращает значение, а возвращает ссылку, которую можно передать другим функциям для дальнейшей работы .

Основные сценарии использования:

• Динамические диапазоны для суммирования — создание «плавающего окна» для расчётов в зависимости от выбранных параметров .
• Создание связанных выпадающих списков — динамическое изменение источника данных в зависимости от выбора в другом списке .
• Автоматическое расширение диапазонов — включение новых данных по мере их добавления.
• Расчёт по «окну» данных — суммирование или усреднение за последние N периодов.

Совместимость

Платформа	Поддержка OFFSET	Примечание
Excel (все версии)	✅ Да	Доступна во всех версиях
Excel для Microsoft 365	✅ Да	Полная поддержка
Google Sheets	✅ Да	Поддерживается, синтаксис совпадает
МойОфис Таблицы	✅ Да	Поддерживается

Синтаксис

Логика аргументов функции одинакова в Excel и Google Sheets :

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

=OFFSET(reference; rows; cols; [height]; [width])

Разбор аргументов

Аргумент	Обязательный	Описание
ссылка (reference)	✅ Да	Исходная ячейка или диапазон. Должна быть ссылкой на ячейку или смежный диапазон
смещ_по_строкам (rows)	✅ Да	Количество строк для смещения. Положительное → вниз, отрицательное → вверх
смещ_по_столбцам (cols)	✅ Да	Количество столбцов для смещения. Положительное → вправо, отрицательное → влево
высота (height)	❌ Нет	Количество строк возвращаемого диапазона. Должно быть положительным числом
ширина (width)	❌ Нет	Количество столбцов возвращаемого диапазона. Должно быть положительным числом

Простые примеры

Пример 1. Смещение на одну ячейку

Исходные данные: В ячейке B6 находится значение 4.

Задача: Получить значение из ячейки, расположенной на 3 строки ниже и 2 столбца левее от D3.

Формула: =СМЕЩ(D3; 3; -2)

Результат: Значение из ячейки B6 (4) .

Объяснение: От точки отсчёта D3 Excel отсчитывает 3 строки вниз и 2 столбца влево и возвращает ссылку на найденную ячейку B6.

Пример 2. Смещение с указанием размера диапазона

Исходные данные: В диапазоне B6:D8 находятся числа, сумма которых равна 34.

Задача: Просуммировать диапазон из 3 строк и 3 столбцов, смещённый на 3 строки вниз и 2 столбца влево от D3.

Формула: =СУММ(СМЕЩ(D3; 3; -2; 3; 3))

Результат: Сумма значений в диапазоне B6:D8 (34) .

Объяснение: СМЕЩ возвращает ссылку на диапазон B6:D8, а СУММ суммирует его значения.

Пример 3. Смещение относительно диапазона

Задача: Отступить от диапазона A1:A10 на 1 строку вниз.

Формула: =СМЕЩ(A1:A10; 1; 0)

Результат: Ссылка на диапазон A2:A11 (сохраняет размер исходной ссылки, если высота и ширина не заданы) .

Объяснение: Исходная ссылка может быть диапазоном. Смещение считается от левого верхнего угла этого диапазона. Если нужно получить именно A11, используйте =СМЕЩ(A1; 10; 0) или =СМЕЩ(A1:A10; 10; 0; 1; 1).

Практические сценарии использования

Суммирование «плавающего окна» (динамический диапазон)

Задача: Просуммировать количество строк, заданное в ячейке C5, начиная с A5 .

Формула: =СУММ(СМЕЩ(A5; 0; 0; C5; 1))

Объяснение: Если в C5 указано 3, формула суммирует A5:A7. Если изменить C5 на 5 — суммирует A5:A9 .

Применение: Расчёт скользящего среднего, суммирование за последние N дней или месяцев.

Транспортный калькулятор: суммирование между двумя точками

Задача: В выпадающих списках пользователь выбирает станцию отправления и назначения. Необходимо просуммировать все ячейки между ними .

Формула (с ПОИСКПОЗ):

=СУММ(СМЕЩ(A1; 
       ПОИСКПОЗ(станция_отправления; A:A; 0); 
       0; 
       ПОИСКПОЗ(станция_назначения; A:A; 0) - ПОИСКПОЗ(станция_отправления; A:A; 0) + 1; 
       1))

Объяснение: ПОИСКПОЗ находит позиции станций, СМЕЩ возвращает диапазон между ними, СУММ суммирует значения .

Важно: Формула рассчитана на случай, когда станция отправления расположена выше станции назначения в исходном списке.

Связанные выпадающие списки

Задача: При выборе региона в первом списке, во втором появляются только города этого региона .

Формула для источника второго списка:

=СМЕЩ(начало_списка_городов; ПОИСКПОЗ(выбранный_регион; список_регионов; 0); 0; СЧЁТЕСЛИ(список_регионов; выбранный_регион); 1)

Объяснение: СМЕЩ динамически определяет, с какой строки и сколько строк брать для второго выпадающего списка .

Важно: Такой подход подходит, если записи каждого региона расположены последовательно в исходном списке.

Расчёт скользящего среднего

Задача: Рассчитать среднее за последние 12 месяцев (период задаётся в ячейке E1).

Формула: =СРЗНАЧ(СМЕЩ(B2; СЧЁТЗ(B:B)-E1; 0; E1; 1))

Объяснение: СЧЁТЗ определяет количество записей, СМЕЩ возвращает диапазон последних E1 значений.

Важное предупреждение: волатильность функции

Ключевое ограничение: Функция СМЕЩ является волатильной (volatile) .

Это означает, что она пересчитывается при любом изменении на листе, даже если изменённые ячейки никак не связаны с аргументами СМЕЩ . В больших таблицах это может значительно замедлить работу .

Что делать:

• В современных версиях Excel для замены СМЕЩ часто используют ИНДЕКС (INDEX) — она неволатильна.
• Например, вместо =СУММ(СМЕЩ(A5;0;0;C5;1)) можно использовать =СУММ(ИНДЕКС(A:A;5):ИНДЕКС(A:A;5+C5-1)) .

Частые ошибки и их решение

Ошибка #ССЫЛ! (#REF!)

Причина: Смещение выводит ссылку за пределы рабочего листа .

Решение: Проверьте, что результат смещения попадает в существующие границы листа.

Ошибка #ЗНАЧ! (#VALUE!)

Причина: Аргумент ссылка не является ссылкой на ячейку или диапазон .

Решение: Убедитесь, что первый аргумент — корректная ссылка.

СМЕЩ возвращает не ту ячейку

Причина: Неверное направление смещения: положительные числа → вниз/вправо; отрицательные → вверх/влево.

Решение: Проверьте знаки в аргументах смещ_по_строкам и смещ_по_столбцам.

Замедление работы таблицы

Причина: Слишком много волатильных функций СМЕЩ в больших таблицах .

Решение: По возможности заменяйте СМЕЩ на ИНДЕКС (INDEX) — она неволатильна и работает быстрее на больших данных .

Сравнение СМЕЩ с альтернативами

Критерий	СМЕЩ (OFFSET)	ИНДЕКС (INDEX)
Основное назначение	Создание смещённых ссылок	Получение значения или построение ссылок
Волатильность	✅ Волатильна	❌ Неволатильна
Динамические диапазоны	✅ Да	✅ Да
Производительность	Может снижаться при большом количестве формул	Обычно предпочтительнее для больших моделей

Часто задаваемые вопросы

Вопрос: В каких версиях Excel работает СМЕЩ?
Ответ: Во всех версиях Excel, начиная с самых старых .

Вопрос: Почему СМЕЩ считается «опасной» функцией?
Ответ: Она волатильна — пересчитывается при любом изменении на листе, что может замедлять работу в больших таблицах .

Вопрос: Можно ли использовать СМЕЩ в Google Sheets?
Ответ: Да, функция поддерживается с тем же синтаксисом .

Вопрос: Чем заменить СМЕЩ для создания динамического диапазона?
Ответ: Используйте ИНДЕКС (INDEX) — она неволатильна и работает быстрее на больших данных .

Заключение

СМЕЩ (OFFSET) — это мощный инструмент для создания динамических диапазонов и «плавающих окон», доступный во всех версиях Excel и Google Sheets. Она позволяет гибко управлять ссылками на ячейки, используя смещения и изменяемые размеры диапазонов.

Ключевые правила:

1. СМЕЩ возвращает ссылку, которую необходимо использовать в других функциях .
2. Положительные значения в смещ_по_строкам и смещ_по_столбцам → вниз/вправо; отрицательные → вверх/влево .
3. СМЕЩ — волатильная функция, поэтому Excel может пересчитывать её при изменениях книги, даже если изменение напрямую не связано с аргументами функции.
4. Если смещение выходит за границы листа, возвращается ошибка #ССЫЛ! .
5. Для больших таблиц и высоких требований к производительности рассмотрите альтернативу — ИНДЕКС (INDEX) .

Освойте СМЕЩ — и вы сможете создавать динамические отчёты, которые автоматически подстраиваются под изменения данных. Но помните о волатильности: в больших таблицах предпочтительнее использовать неволатильные альтернативы.