Excel. Примеры использования функции СМЕЩ (OFFSET)

Рубрика: 7. Полезняшки Excel

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

Формально функция СМЕЩ (OFFSET) применяется для создания ссылки на прямоугольный диапазон, которая представляет собой заданное количество строк и столбцов, отстоящих от ячейки или диапазона ячеек (см. Help MS Excel). Неплохую статью для первого знакомства с функцией СМЕЩ написал Ренат Лотфуллин (рекомендую!)

1. Чтобы создать ссылку на диапазон ячеек, сначала необходимо указать начальную ячейку. Затем надо указать, на расстоянии скольких строк и столбцов от нее начинается прямоугольный диапазон. Например, с помощью функции СМЕЩ можно создать ссылку на диапазон ячеек, включающий две строки и три столбца и начинающийся на два столбцов правее и на одну строку выше текущей ячейки (рис. 1). Хитрость функции СМЕЩ в том, что она возвращает диапазон, поэтому, если просто ввести ее в ячейку, ничего не выйдет – функция СМЕЩ вернет ошибку #ЗНАЧ! Поэтому в примере использована функция СУММ, которая суммирует значения в диапазоне, возвращаемом функцией СМЕЩ. Заметим, что, если функция СМЕЩ возвращает одну ячейку, например, =СМЕЩ(A4;-1;2;1;1), то ее можно использовать напрямую, она вернет значение ячейки С3.

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

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

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

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

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

  • Ссылка (reference) — это ячейка или диапазон, от которого начинается смещение. Если указывается диапазон ячеек, они должны быть смежными.
  • Смещение по строкам (rows moved) — количество строк, отделяющее начало смещаемого диапазона от ссылки (верхняя левая ячейка в смещенном диапазоне). Если указано отрицательное число, вы перемещаетесь вверх от ссылки; если положительное — вниз. Например, если ссылка равна С5, а смещение по строкам равно –1, вы перемещаетесь к строке 4 в ячейку С4. Если смещение по строкам равно +1, вы перемещаетесь к строке 6. Если смещение по строкам равно 0, вы остаетесь на строке 5.
  • Смещение по столбцам (columns moved) — количество столбцов, отделяющее начало смещаемого диапазона от ссылки (верхняя левая ячейка смещенного диапазона). Если указано отрицательное число, вы перемещаетесь влево от ссылки; если положительное — вправо. Например, если ссылка равна С5, а смещение по столбцам равно –1, вы перемещаетесь к столбцу В. Если смещение по столбцам равно +1, вы перемещаетесь к столбцу D. Если смещение по столбцам равно 0, вы остаетесь в столбце С.
  • Высота (height) — необязательный аргумент, который задает число строк в смещенном диапазоне.
  • Ширина (width) — также необязательный аргумент, который задает число столбцов в смещенном диапазоне. Если высота или ширина опущена, функция СМЕЩ создает диапазон, для которого значения высоты и ширины будут равны высоте и ширине первого параметра функции – ссылки. Если ссылка – это одна ячейка, то функции СМЕЩ создаст диапазон из одной ячейки.

3. На рис. 2 перечислены сотрудники фирмы по продаже косметики и соответствующее каждому число продаж за неделю. Если нужно найти сотрудника, который выполнил конкретное число продаж, можно воспользоваться функцией ВПР. Однако, если нужно найти число продаж конкретного сотрудника по имени этого сотрудника, можно применить совместно функции ПОИСКПОЗ и СМЕЩ. Заметим, что с этой задачей также справится пара функций ПОИСКПОЗ и ИНДЕКС.

Рис. 2. Выборка по правому столбцу с помощью комбинации функций ПОИСКПОЗ и СМЕЩ

Введите имя сотрудника — Эшли — в ячейку А11. Затем используйте ссылку на ячейку А1 в функции СМЕЩ. Для поиска числа продаж нам требуется переместиться вниз к строке 6, где содержится имя сотрудника. Это задача функции ПОИСКПОЗ, которая находит номер строки в диапазоне В2:В8, в которой расположено имя сотрудника — Эшли. Это номер 5. После этого мы перемещаемся на вычисленное количество строк оставаясь в столбце А. Поскольку ссылка состоит из одной ячейки (аргументы высота и ширина функции СМЕЩ опущены), мы добиваемся того, что диапазон, который возвратит формула, будет состоять из одной строки и столбца (одна ячейка). Таким образом, мы находим число продаж, сделанных конкретным сотрудником. Как видите, это пример того, что функция СМЕЩ, возвращая диапазон, состоящий из одной ячейки, может вернуть число.

4. Можно ли создать формулу, которая всегда будет выбирать данные, касающиеся определенной страны, если в исходных данных эта страна может располагаться в разных строках?

Рис. 3 содержит данные об объемах продаж, доходе от продаж, а также о переменных издержках при продаже ПО в некоторые страны Азии и Ближнего Востока. Каждый месяц, когда мы загружаем финансовые отчеты, расположение каждой страны в электронной таблице меняется, и поэтому нам требуется, чтобы наши формулы (для конкретной страны) всегда возвращали правильное количество проданных единиц, доход и переменные издержки. С задачей справится формула =СМЕЩ($A$1;ПОИСКПОЗ($A16;$A$2:$A$12;0);D15), расположенная в ячейках В16:D16. Заметим также, что с задачей справится формула =ВПР($A17;$A$2:$D$12;СТОЛБЕЦ();ЛОЖЬ), расположенная в ячейках В17:D17.

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

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

Рис. 4 содержит данные о ежемесячных издержках, связанных с разработкой пяти лекарств. Каждое лекарство разрабатывается в три этапа. Для каждого лекарства указано количество месяцев, необходимое на завершение каждого этапа.

Рис. 4. Использование функции СМЕЩ для вычисления затрат на этапы 1–3 при разработке лекарств

Наша задача — определить для каждого лекарства издержки на каждом этапе разработки. В ячейках D4:D6 я вычисляю общие издержки на этапах 1–3 разработки лекарства 1. Для расчета затрат на этап 1 для лекарства 1 я использую ссылку на ячейку D10 и задаю смещение по строкам и смещение по столбцам равные 0. Задав высоту равной продолжительности этапа 1 в месяцах и ширину равной 1, я учту все издержки первого этапа. Я вычисляю издержки этапа 1 для лекарства 1 в ячейке D4 по формуле =СУММ(СМЕЩ(D10;0;0;D1;1)). Затем в ячейке D5 я определяю общие издержки этапа 2 для лекарства 1 по формуле =СУММ(СМЕЩ(D10;D1;0;D2;1)). Обратите внимание: я начинаю со ссылки на ячейку D10 (первый месяц затрат) и перемещаюсь вниз на число строк, равное продолжительности этапа 1. Это приводит меня к началу этапа 2. Установив высоту равной значению ячейки D2, я могу быть уверен, что учел все издержки этапа 2. Наконец в ячейке D6 я рассчитываю издержки для этапа 3 разработки лекарства 1 по формуле =СУММ(СМЕЩ(D10;D1+D2;0;D3;1)). В этой формуле я начинаю с первого месяца разработки и смещаюсь вниз на число строк, равное общей продолжительности этапов 1 и 2. Таким образом я оказываюсь в начале этапа 3 и суммирую число строк в ячейке D3, чтобы получить затраты на этапе 3. Затем, копируя формулы ячеек D4:D6 в диапазон Е4:Е6, я вычислю общие издержки для этапов 1–3 разработки лекарств 2–5. Например, вы видите, что для лекарства 2 общие затраты на этапе разработки составили $313, на этапе 2— $789 и на этапе 3 — $876.

6. Я управляю небольшим магазином видеопродукции. Мой бухгалтер занес в электронную таблицу название каждого фильма и количество его копий на складе. К сожалению, эти данные указаны в одной ячейке. Как извлечь количество копий каждого фильма в отдельную ячейку?

Рис. 5 содержит перечень фильмов на складе, а также количество копий каждого фильма на складе.

Рис. 5. Все данные о фильме в одной ячейке

Мы хотим извлечь количество копий каждого фильма в отдельную ячейку. Если бы число копий указывалось слева от названия фильма, проблема решалась бы просто. Мы могли бы воспользоваться функцией НАЙТИ для поиска первого пробела и затем с помощью функции ЛЕВСИМВ выбрать все данные слева от первого пробела. К сожалению, данный способ не работает, если число копий указано справа от названия фильма. Например, если название фильма состоит из одного слова, число копий находится справа от первого пробела, а если название состоит из четырех слов — справа от четвертого пробела.

Один из способов решить данную проблему — выбрать в меню Данные команду Текст по столбцам и поместить каждое слово названия и число копий в отдельные столбцы. Затем можно использовать функцию СЧЁТЗ, чтобы определить для каждого фильма общее количество слов в названии, считая число копий за одно слово. После этого можно с помощью функции СМЕЩ отобрать число копий.

Для начала проверим, что справа от данных достаточно столбцов, чтобы можно было извлечь каждое слово названия фильма и количество копий в отдельный столбец. Затем я выделил диапазон ячеек С2:С12 (рис. 6) и выбрал в меню Данные команду Текст по столбцам. В Мастере текстов я установил переключатель в положение с разделителями и на следующем шаге выбрал в качестве символа-разделителя пробел (рис. 7). На третьем этапе я выбрал в качестве конечной ячейки D2.

Рис. 6. Мастер распределения текста по столбцам, шаг 1

Рис. 7. Мастер распределения текста по столбцам, шаги 2 и 3

Теперь мы, копируя формулу =СЧЁТЗ(D2:I2) из ячейки А2 в диапазон АЗ:А12, найдем для каждого фильма количество слов в названии, считая количество копий за одно слово. Результаты показаны на рис. 8. Наконец, скопировав формулу =СМЕЩ(C2;0;A2) из ячейки В2 в диапазон ВЗ:В12, я получу количество копий каждого фильма на складе. Эта формула начинается со ссылки на ячейку, содержащую название фильма, и перемещается вправо на количество столбцов, равное числу «слов» в ячейке Число слов. Поскольку ссылка состоит только из одной ячейки, мы можем опустить два последних аргумента функции СМЕЩ, чтобы функция выбирала только одну ячейку, содержащую последнее «слово» в заголовке (то есть число копий).

Рис. 8. Итоговые результаты примера с фильмами

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

Рис. 9 содержит данные об объемах продаж товара нашей компании в единицах. Как вы видите, график продаж — это точечная диаграмма со значениями, соединенными сглаживающими линиями. Начиная со строки 19, мы будем загружать новые данные о продажах. Есть ли простой способ гарантировать, что график будет автоматически отражать новые данные?

Рис. 9. Функция СМЕЩ позволяет автоматически обновлять данный график

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

Чтобы реализовать все описанные возможности, сначала выберите в меню Формулы команду Диспетчер имен; в открывшемся окне кликните Создать, и в новом окне дайте имя новому диапазону и определите сам диапазон с помощью формулы СМЕЩ, как показано на рис. 10. Учтите, что в имени нельзя использовать пробелы. Формула =СМЕЩ(‘Рис. 9’!$C$3;0;0;СЧЁТ(‘Рис. 9’!$C:$C);1) создает диапазон в один столбец шириной, начинающийся в ячейке СЗ, содержащей первое значение количества проданных единиц. Диапазон включает столько чисел, сколько окажется в столбце С, что определяется областью СЧЁТ(‘Рис. 9’!$C:$C). При вводе новых данных в столбец С они будут автоматически включены в диапазон Количество_проданных_единиц.

Рис. 10. Создание динамического диапазона Количество_проданных_единиц

Затем аналогичным образом создайте динамический диапазон Месяцы для месяцев, введенных в столбце В. Для этого введите в поле Формула =СМЕЩ(‘Рис. 9’!$B$3;0;0;СЧЁТ(‘Рис. 9’!$B:$B);1).

Переходим к нашему графику и правой кнопкой мыши щелкаем любую точку на нем. В контекстном меню щелкаем Выбрать данные. В окне Выбор источника данных, щелкаем на ряд Количество проданных единиц и на кнопку Изменить (рис. 11).

Рис. 11. Выбор источника данных

В открывшемся окне Изменение ряда заменяем диапазоны значений Х и Y на именованные динамические диапазоны (рис. 12). Слева – до изменения, справа – после.

Рис. 12. Вместо ссылок на диапазоны в качестве источника данных выбираем имена диапазонов

Введите какие-нибудь новые данные, и вы увидите, что они появятся на графике.

Контрольные задания (с ответами)

1. В Excel-файле на листе Задание_1 хранятся данные о количестве проданных единиц для 11 товаров за 1999—2003 гг. Напишите формулу, используя функции ПОИСКПОЗ и СМЕЩ, чтобы отобрать объемы продаж конкретного товара за определенный год. Можете ли вы решить эту задачу, не используя функции ПОИСКПОЗ и СМЕЩ?

2. Ниже представлены данные о количестве рекламных объявлений в журналах, размещенных Центром вербовки в армию за 60 последовательных месяцев (см. также Excel-файле, лист Задание_2). Для каждого месяца мы задаем число объявлений, запаздывающее на k месяцев, равным числу рекламных объявлений, размещенных k месяцев назад. Для 7—60 месяцев нам требуется вычислить, число объявлений, запаздывающих на 1 месяц, 2 месяца … 6 месяцев. Воспользуйтесь функцией СМЕЩ, чтобы вычислить эти запаздывающие значения.

3. Допустим, вы постоянно вводите ежемесячные данные о продажах в столбец С:

Продажи первого месяца введены в ячейку С2. Предполагается, что при вводите данные последовательно в каждую следующую ячейку столбца С. Таким образом, если данные о продаже последнего месяца находятся в ячейке С8, данные о продаже следующего месяца будут введены в ячейку С9 и так далее. Напишите формулу, которая всегда будет возвращать данные о продажах за последний месяц.

Ответы

1. =СМЕЩ(B2;ПОИСКПОЗ(B16;B3:B13;0);ПОИСКПОЗ(C15;C2:G2;0)). См. лист Ответ_1. Обратите внимание, что в функции ПОИСКПОЗ необходимо использовать третий (необязательный) аргумент. Этому аргументу нужно присвоить значение 0. В этом случае Excel вернет номер первого встреченного элемента в точности равного искомому. По умолчанию Excel почему-то присваивает значение третьему аргументу 1. В этом случае Excel найдет максимальное значение не превышающее искомое. В нашем случае это будет Товар 1.

Можно также использовать:

  • функцию ИНДЕКС в сочетании с функцией ПОИСКПОЗ…
  • или функцию ВПР.

2. =СМЕЩ($C10;-ЛЕВСИМВ(D$3);0). Функция ЛЕВСИМВ возвращает число месяцев, на которые нужно сместить значение в столбце С. А знак «–» говорит о том, что нужно смещаться вверх.

3. =СМЕЩ(C1;СЧЁТ(C:C);0). Функция СЧЁТ() возвращает количество ячеек в столбце С, содержащих числа. Функция СМЕЩ смещается по столбцу С от ячейки 1 на число ячеек с числами; в нашем случае на 7, попадая на ячейку С8, смещения по столбцам нет, выбирается одна ячейка, так как 4-й и 5-й аргументы функции СМЕЩ опущены (по умолчанию они равны единице).


[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel. Анализ данных и построение бизнес-моделей, глава 21.

Комментарии: 13 комментариев

Спасибо !!! Грамотно и, главное, доступно.

Спасибо огромное! Приятно читать и учиться на Вашем сайте!

Спасибо!!
Очень доступно, полезно и профессионально!!

Добрый день,
Подскажите пожалуйста, а если в ячейках стоят формулы и значения за октябрь-март17 пока пустые (0). Динамический график заваливает данные на 0 с октября по октябрь (формулы с IF тоже не помогают, приходится удалять формулы в ячейках (с октября по март) чтобы график был красивым. Можно ли как-то избежать эту проблему или удалять формулы и потом вновь их протягивать чтобы обновить месячные данные?
Спасибо большое
Мария

Мария, почитайте заметку Отражение пропущенных данных на графиках Excel. Возможно, найдете в ней решение вашей проблемы.

Если есть ряд данных на горизонте 10 кварталов и надо внизу во второй строке разместить этот же ряд, но со смещением на 2 квартала, то как это сделать через эту функцию?

Семен, например так: =СМЕЩ(A1;1;0)

См. также Excel-файл

Спасибо, но не совсем то. Задача: отразить смещение графика инвестиций на несколько кварталов в будущее. У Вас 184 тыс в обоих рядах приходятся на 1кв2015 года, а надо, чтобы эта сумма была уже, например, в 3кв2015.

Понял: в формуле вместо «0» надо ставить ссылку на ячейку с количеством кварталов смещения.
Например =OFFSET(A1;1;3), где «3» — сдвиг на 3 квартала (вернее 4 получается).

Очень полезная статья!
Спасибо!

Здравствуйте ! Подскажите есть ли формула которая по признаку Доллар США $ выберет ячейку с датой 24.05.17, т.е. на строку выше. Это часть большой таблицы которая грузится с сайта ЦБ. Проблема в том что в строках с датой пусто, если бы там что то стояло то я бы использовал ВПР без проблем.

23.05.2017 24.05.2017
Доллар США $ руб. 56,4988 руб. ↑56,5552

Роман, пришлите Excel-файл на s_bag@mail.ru

Роман, воспользуйтесь парой функций: ИНДЕКС и ПОИСКПОЗ. Эта пара отлично заменяет ВПР, когда последняя не справляется, и требуется больше гибкости. См., например, Excel. Функции ИНДЕКС и ПОИСПОЗ


Прокомментировать

Все статьи2