Познакомимся с редко используемой, но чрезвычайно полезной функцией Excel – СМЕЩ.

Зачем использовать функцию СМЕЩ?

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

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

Синтаксис функции СМЕЩ

Функция СМЕЩ в общем виде выглядит следующим образом:

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

Например,

=СМЕЩ(A1;5;6)
=СМЕЩ(A1;5;6;7;8)

  • Базовая ячейка – ячейка или диапазон ячеек, относительно которого производится смещение
  • Смещение по строкам и столбцам – количество ячеек, на которое производится смещение относительно базовой ячейки. Значения могут принимать как положительные (смещение вправо и вниз), так и отрицательные (смещение влево и вниз) или нулевые значения
  • Высота и ширина – размер итогового (возвращаемого) диапазона ячеек. Если параметры не указаны, используется размер базовой ячейки или диапазона ячеек. Например, размер 4х3 возвращает диапазон высотой 4 и шириной 3 ячейки

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

=СМЕЩ(A1;D1;D2;D3;D4)

Результатом будет диапазон ячеек высотой D3 и шириной D4, смещенный относительно ячейки A1 на D1 строк и D2 столбцов.

Пример использования функции СМЕЩ

Функция СМЕЩ - пример работы

Для чего использовать функцию СМЕЩ?

Зачем использовать СМЕЩ вместо того, чтобы указать диапазон ячеек напрямую, например, "A1:D5"?

На это есть две основные причины:

  • Динамический диапазон. Нам необходим диапазон, меняющийся в зависимости от изменений, вносимых в таблицу. Введя фиксированный (статичный) диапазон «A1:D5» мы обрекаем себя на обновление данной ссылки каждый раз, когда этот диапазон необходимо расширить или сузить. Например, каждый день добавляется новая строка с курсом валюты или столбец с новым продуктом
  • Точная ссылка неизвестна. Иногда мы не знаем точную ссылку на диапазон, нам лишь известно, что он начинается с определенной ячейки.

Интерактивное обучение функции СМЕЩ

Функция СМЕЩ достаточно сложна для понимания и ее правильного использования – это дело тренировки. Для облегчения этого процесса я сделал интерактивную таблицу, в которой вы можете ввести все 5 используемых параметров функции и увидеть результат ее использования: базовая ячейка и итоговый диапазон будут автоматически подсвечены.

Функция СМЕЩ - Интерактивная таблица

Скачать интерактивную таблицу

Практическое использование функции СМЕЩ

Допустим, мы каждый день анализируем курс иностранной валюты по отношению к рублю.

Функция СМЕЩ - Курс иностранной валюты

Как нам рассчитать среднее значение курса валюты за последние 7 дней?

В общем случае мы бы ввели такую формулу:

=СРЗНАЧ(B25:B31), где в диапазон ячеек B25:B31 введены курсы валюты за последние 7 дней

Введенная формула не обладает динамичностью и каждый день нам придется не только вводить новые данные, но и обновлять формулу.

Теперь воспользуемся функцией СМЕЩ для нахождения того же среднего значения:

=СРЗНАЧ(СМЕЩ(B2;СЧЁТ(B:B)-7;0;7;1))

Разберем по порядку:

  • СЧЁТ(B:B)-7 – подсчитываем общее количество ячеек в столбце C, содержащих числа, и вычитаем 7 (последние 7 дней, для которых мы будем подсчитывать среднее значение)
  • СМЕЩ(B2;СЧЁТ(…)-7;0;7;1) – определяем диапазон размером 7х1 ячеек, отстоящий от ячейки B2 на количество заполненных числами ячеек столбца B за вычетом 7 ячеек
  • СРЗНАЧ(СМЕЩ(…)) – вычисляем среднее значение диапазона, содержащего последние 7 введенных значений

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

Имейте ввиду

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

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

Альтернативы функции СМЕЩ

Существует 2 альтернативы использованию функции СМЕЩ:

  • Используйте функционал Таблиц в версиях Excel 2007 и выше. Он позволяет придать ссылающимся на такие Таблицы формулам динамичность без потери производительности.
  • При наличии возможности используйте функцию ИНДЕКС. Данная функция также позволяет создавать динамические ссылки на диапазоны, но не так сильно загружает Excel расчетами из-за того, что в отличие от СМЕЩ не пересчитывается при каждом изменении в книге.

Добавить комментарий


Защитный код
Обновить

Вверх