Функция СМЕЩ часто пугает неискушенного пользователя наличием аж пяти аргументов и непонятным назначением. Но в Excel часто встречаются задачи, в которых она проявит себя с самой лучшей стороны и станет незаменимым помощником. Например, когда Вам нужно:
1) Сделать зависимый выпадающий список, в который можно добавлять данные;
2) Добиться того, чтобы новые строки сразу попадали в сводную таблицу при обновлении;
3) Отображать на графике только данные за последние 7 дней, причем новые данные вносятся ежедневно;
4) В списке помесячных продаж за год мгновенно подсчитать сумму с января по март, а потом с февраля по апрель, а потом с сентября по декабрь...
5) В новом столбце сослаться на каждую пятую ячейку соседнего столбца
Как видите, задачи интересные и нетривиальные. Если Вы хотите научиться их решать в два счета, то стоит уделить несколько минут изучению функции СМЕЩ.
Синтаксис и алгоритм работы функции
Функция СМЕЩ умеет возвращать ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.
Функция имеет следующий синтаксис:
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])
Как видите, у неё есть 5 аргументов:
1) "Ссылка". Обязательный аргумент. Указывает стартовую ячейку, от которой будет происходить смещение. Здесь можно указать ссылку на ячейку или на диапазон ячеек. Во втором случае смещение будет считаться от левой верхней ячейки диапазона;
2) "Смещ_по_строкам". Обязательный аргумент. Указывает, на сколько строк нужно сместиться от заданной ячейки. Положительные числа означают смещение вниз, отрицательные - вверх, ноль - без смещения;
3) "Смещ_по_столбцам". Обязательный аргумент. Указывает, на сколько столбцов нужно сместиться от заданной ячейки. Положительные числа означают смещение вправо, отрицательные - влево, ноль - без смещения;
4) "Высота". Необязательный аргумент. Высота в строках возвращаемого диапазона. Если опустить - высота будет равна высоте диапазона, указанного в аргументе "Ссылка";
5) "Ширина". Необязательный аргумент. Ширина в строках возвращаемого диапазона. Если опустить - ширина будет равна ширине диапазона, указанного в аргументе "Ссылка".
На рисунке ниже представлен алгоритм работы.
1) В качестве "Ссылки" задана ячейка B2.
2) От нее происходит смещение на 2 строки вниз (так как число 2 положительное). Мы оказываемся в ячейке B4.
3) Из ячейки B4 происходит смещение на 3 столбца вправо (так как число 3 положительное). В итоге мы попадаем в ячейку E4.
4) Принимая ее за верхнюю левую, выделяем диапазон из 3 строк и 4 столбцов. Результат - диапазон E4:H6.
Если смысл работы всё еще не ясен, можете попробовать "поиграть" с аргументами функции на листе "Алгоритм работы" в файле-примере, ссылка на который будет в конце статьи.
Можете задавать смещения, размеры исходного диапазона и адреса стартового, а результат будет подсвечиваться на листе.
Если Вы сейчас подумали, что это всё какая-то ерунда, ведь ссылку на диапазон можно задать и вручную, без сложных функций, то Вы мыслите недостаточно гибко. Вся прелесть СМЕЩ в том, что аргументы можно задавать не строгими числами, а ссылками на ячейки и функциями, что делает возвращаемый диапазон динамическим! Это будет хорошо видно в примерах использования.
Особенности функции
Функция СМЕЩ имеет пару особенностей, о которых стоит знать:
- если итоговый диапазон находится за пределами рабочего листа, то функция вернет ошибку #ССЫЛКА;
- функция является волатильной (летучей), то есть пересчитывается каждый раз при изменении рабочего листа. Это значит, что в некоторых случаях наличие в книге данной функции может "тормозить" работу файла
Перейдем к самому интересному - примерам работы.
1) Зависимый выпадающий список.
Задача
Сделать выпадающий список с названиями отделов компании. При выборе отдела должен появляться выпадающий список с сотрудниками этого отдела. При этом как отделы, так и сотрудники, могут добавляться в списки.
Решение
Организуйте данные на листе, так как показано на скриншоте ниже.
Обратите внимание на строку 3. В ячейку А3 введена формула =СЧЁТЗ(A5:A100). Она считает количество сотрудников отдела. Формулу нужно скопировать вправо на все отделы. Это количество пригодится нам в дальнейшем.
Сначала создадим выпадающий список всех отделов с учетом того, что справа могут добавляться новые отделы. Удобно будет создать именованный динамический диапазон.
Внимательно запомните процесс создания именованного динамического диапазона. Во всех остальных примерах мы тоже будем их создавать, но пошаговое объяснение дано только тут.
Переходим по адресу: "Формулы" - "Диспетчер имен" - "Создать"
В поле "Имя" вводим "Отделы".
В поле "Область" оставляем "Книга" (чтобы имя действовало в пределах всего файла).
В поле "Диапазон" вводим формулу:
=СМЕЩ('Зависимый список'!$A$4;0;0;1;СЧЁТЗ('Зависимый список'!$4:$4))
В этой формуле в качестве стартовой указана ячейка первого отдела ("Бухгалтерии"). Смещение по строкам и столбцам равно 0, так как нам никуда смещаться не нужно. Высота диапазона равна 1 строке, а вот ширина задается формулой СЧЁТЗ('Зависимый список'!$4:$4). Она считает все непустые ячейки в четвертой строке. Непустых у нас 4 - названия наших отделов. В итоге мы получим диапазон из 1 строки и 4 столбцов - список всех отделов. При добавлении нового отдела, СЧЁТЗ найдет уже 5 непустых ячеек и новый отдел попадет в список (учтите, что в 4 строке не должно быть ничего, кроме названий отделов, иначе СЧЁТЗ насчитает лишние ячейки).
Теперь на основе созданного диапазона "Отделы" создадим выпадающий список. Встаём в ячейку B1, кликаем "Данные" - "Проверка данных" - Тип данных: "Список" и вводим "=Отделы".
Теперь у нас есть выпадающий список отделов. Перейдем к созданию списка для сотрудников. Создаем именованный диапазон "Сотрудники" с формулой:
=СМЕЩ('Зависимый список'!$A$4;1;ПОИСКПОЗ('Зависимый список'!$B$1;'Зависимый список'!$4:$4;0)-1;ИНДЕКС('Зависимый список'!$3:$3;ПОИСКПОЗ('Зависимый список'!$B$1;'Зависимый список'!$4:$4;0));1)
Первый аргумент - ячейка "Бухгалтерия".
Второй аргумент - 1. Так как сместиться нужно вниз на 1 строку, именно оттуда начинается список сотрудников.
Третий аргумент - смещение по столбцам. Функция ПОИСКПОЗ ищет номер столбца, в котором находится выбранный в ячейке B1 отдел.
Четвертый аргумент - функция ИНДЕКС возвращает количество сотрудников из ячейки над названием нужного отдела;
Пятый аргумент - 1, так как нам нужен столбец одного отдела.
В результате мы получили динамический диапазон со списком всех сотрудников отдела, указанного в ячейке B1. Теперь нужно в B2 добавить выпадающий список с формулой: =Сотрудники (как делали ранее для отдела) и наши списки готовы. Можно добавлять отделы и новых людей.
2) Добавление новых строк в сводную таблицу
Задача
Есть таблица с данными. На ее основе построена сводная таблица. Необходимо, чтобы при добавлении новых данных они попадали в сводную при следующем обновлении ("умную таблицу" по ряду причин задействовать нельзя).
Решение
Создадим именованный диапазон "Источник", в который введем формулу:
=СМЕЩ('Диапазон сводной'!$A$1;0;0;СЧЁТЗ('Диапазон сводной'!$A:$A);СЧЁТЗ('Диапазон сводной'!$1:$1))
В этом случае функция стартует из ячейки "Дата". Никуда не смещаясь, она создает диапазон по следующему принципу:
- число столбцов равно количеству непустых ячеек первой строки (то есть количеству заголовков в шапке), за подсчет которых отвечает формула СЧЁТЗ('Диапазон сводной'!$1:$1;
- число строк равно количеству непустых ячеек первого столбца, за подсчет которых отвечает формула СЧЁТЗ('Диапазон сводной'!$A:$A).
Таким образом, если мы добавим в таблицу новый столбец или строку, эти данные попадут в динамический диапазон "Источник". Осталось лишь указать его в качестве источника данных сводной таблицы.
Выделяем сводную - "Анализ" - "Источник данных" и в качестве диапазона указываем "=Источник".
Готово! Теперь, если в таблице появятся новые данные, они попадут в сводную при следующем обновлении.
3. Последняя неделя на графике
Задача
Есть таблица продаж товара. Нужно построить график, на котором всегда будут отображаться только последние 7 дней из таблицы.
Решение
По традиции, нам нужно создать именованный диапазон (например, "Последние7дней", который будет забирать из таблицы только последние 7 дней. Формула в этот раз такая:
=СМЕЩ('Последняя неделя на графике'!$A$1;СЧЁТЗ('Последняя неделя на графике'!$A:$A)-7;1;7;1)
Благодаря формуле СЧЁТЗ('Последняя неделя на графике'!$A:$A)-7 мы всегда смещаемся на ту ячейку, с которой начинаются последние 7 дней.
Кроме того, хотелось бы, чтобы динамические изменялись и подписи. Для них создадим диапазон "Подписи", но формула будет отличаться: третий аргумент будет равен 0, чтобы мы остались в столбце А.
=СМЕЩ('Последняя неделя на графике'!$A$1;СЧЁТЗ('Последняя неделя на графике'!$A:$A)-7;0;7;1)
Теперь исправим в созданной диаграмме абсолютные ссылки на именованный диапазон. Кликаем прямо на линию графика. В строке формул должна отобразиться функция РЯД(). Меняем в ней ссылки на именованные диапазоны, не стирая имя листа, как показано на рисунке ниже.
Когда закончим - жмем Enter. Вместо имени листа появится имя книги.
Теперь при добавлении новых строк будут показываться данные только за последнюю неделю.
4. Быстрый подсчет суммы в пределах периода
Задача
Есть таблица, в которой указаны суммы за каждый месяц. Нужно создать формулу, которая будет пересчитывать общую сумму за указанный период (с месяца по месяц).
Решение
На этот раз обойдемся без именованных диапазонов.
В ячейках A4 и B4 будут указываться месяцы, означающие период, за который нужно подсчитать сумму. В ячейку C2 нужно ввести формулу, которая будет давать итоговый результат.
Для начала нужно с помощью функции СМЕЩ создать диапазон на основе указанных месяцев "С" и "По". Формула будет такая:
=СМЕЩ($A$6;ПОИСКПОЗ(A4;$A$7:$A$18;0);1;ПОИСКПОЗ(B4;$A$7:$A$18;0)-ПОИСКПОЗ(A4;$A$7:$A$18;0)+1;1)
В данному случае функция ПОИСКПОЗ ищет ячейку, с которой будет начинаться диапазон (ячейка месяца "С"). Затем находится позиция второго месяца (месяц "По"). Из нее вычитается позиция первого и прибавляется единица, чтобы получить высоту диапазона (например, 5 (Май) - 2 (Февраль) + 1 = 4 (Февраль-Май)).
Осталось подсчитать сумму в найденному диапазоне. Для этого достаточно указать приведенную выше формулу в качестве аргумента функции СУММ.
Итак, в ячейке C4 будет формула:
=СУММ(СМЕЩ($A$6;ПОИСКПОЗ(A4;$A$7:$A$18;0);1;ПОИСКПОЗ(B4;$A$7:$A$18;0)-ПОИСКПОЗ(A4;$A$7:$A$18;0)+1;1))
Пример работы:
5. Ссылка на каждую 5-ую ячейку
Задача
Есть таблица с данными. В соседней нужно вывести подряд значения каждой пятой ячейки первой таблицы.
Решение
Последняя задача - самая простая из всех. В D1 нужно ввести формулу, протянув которую можно получить каждое пятое значение из списка (подкрашены зеленым). Здесь формула будет такая:
=СМЕЩ($B$1;(СТРОКА()-1)*5;0;1;1)
В качестве "якоря" указываем ячейку B1.
Для смещения по строкам используем формулу (СТРОКА()-1)*5. Когда мы введем ее в D2, то результат будет (2-1)*5 = 5. В следующей (третьей) строке результат будет (3-1)*5 = 10 и т.д. То есть при копировании функция СМЕЩ будет сдвигаться с шагом в 5 строк. Именно это нам и нужно.
Для лучшего понимания работы функции советуем скачать файл со всеми примерами вот по этой ссылке.
Поддержать наш проект и его дальнейшее развитие можно вот здесь.
Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
С уважением, команда tDots.ru
