Не правда ли, было бы замечательно, если бы Excel мог изменять диапазон данных, например, для графиков или диаграмм, при добавлении новых строк или столбцов. На самом деле, возможность создания динамических диапазонов существует, и она реализована с помощью функции СМЕЩ.
Функция СМЕЩ возвращает ссылку на диапазон с определенной отправной точкой с заданной высотой и шириной.
Что это значит? К примеру, у вас есть диапазон данных, который используется для построения диаграммы, но количество строк настолько велико, что диаграмма становиться нечитабельной для пользователей. Выходом из ситуации станет отображение только определенной части данных. Например, пользователям необходимо будет указать последний квартал или год и функция СМЕЩ отправит новый диапазон данных на построение диаграммы.
Синтаксис формулы СМЕЩ
Перевод статьи с сайта Chandoo
Формула СМЕЩ выглядит следующим образом:
=СМЕЩ(ссылка; смещение по строкам; смещение по столбцам; высота; ширина)
- Ссылка: отправная точка, эта ячейка, относительно которой необходимо смещаться
- Смещение по строкам и столбцам: Указывает, на какое количество строк или столбцов необходимо сместиться от отправной точки. Может иметь положительные и отрицательные значения, а также равняться нулю.
- Высота и ширина: Это размер диапазона, который вы хотите вернуть. К примеру, 4X3 вернет диапазон содержащий 4 строки в высоту и 3 столбца в ширину.
И конечно, все аргументы могут ссылаться на другие ячейки. Это означает, что вы можете написать формулу =СМЕЩ(A1;D1;D2;D3; D4), которая вернет диапазон
- Смещенный от ячейки A1
- На D1 строк и D2 столбцов
- Размером D3 строчки и D4 столбца
На рисунке изображен пример работы формулы СМЕЩ для лучшего понимания.
Зачем использовать формулу СМЕЩ?
Почему просто нельзя явно указать название диапазона, например, A1:C4?
Вот пара причин почему:
- Динамические диапазоны: Ссылка типа A1:C4 всегда будет возвращать диапазон A1:C4, т.е. она статична. Но иногда нам необходимо, чтобы диапазон был динамичным. Это требуется потому что данные могут меняться (с каждым месяцем добавляются новые строчки, запускаются новые продукты – добавляются колонки)
- Мы не знаем точного адреса: Иногда мы не можем знать фактического адреса. Чаще всего нам известна только стартовая ячейка. В такой ситуации нам поможет формула СМЕЩ.
Интерактивное описание формулы СМЕЩ
Формула СМЕЩ довольно непростая для понимания, поэтому была создана интерактивная таблица, чтобы вы могли лучше понять, как она работает. Изменяя 5 аргументов формулы, Excel будет выделять диапазон, который вы выбрали. После нескольких минут баловства, вы поймете формулу лучше.
Скачать книгу с интерактивной таблицей формулы СМЕЩ.
Практикуемся с формулой СМЕЩ – среднее значение последней недели
Предположим, что мы работаем в службе качества фабрики по производству шоколадных батончиков. Одним из показателей эффективности (KPI), который мы должны отслеживать является бракованные батончики. Лист сбора данных будет выглядеть примерно следующим образом.
Так как же мы будем считать средний показатель брака за неделю?
Если предположить, что данные находятся в диапазоне B2:B23, тогда необходимо записать =СРЗНАЧ(B2:B23).
Но нам необходимо, чтобы эта формула менялась ежедневно!!!
Используя формулу СМЕЩ мы можем один раз посчитать среднее значение и больше не возвращаться к этому вопросу.
=СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(B2:B300)-7;0;7;1))
Разберемся, как работает эта формула
- Для того, чтобы посчитать среднее значение последней недели, мы должны вытянуть последние 7 строк со всего массива данных.
- Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество значений и затем, от полученной цифры отнимается 7.
- Формула СМЕЩ перемещается с ячейки B2 на стартовую ячейку последней недели.
- На гифке показано, как работает динамический диапазон.
СПАСИБО!
Хоть кто-то нормально разъяснил как эта полезная функция работает.
СПАСИБО!
Огромная благодарность за очень доступное объяснение этой функции для простых людей.
Думаю, не один я «сломал» голову, разбираясь с ней на офф. сайте «мелкомягких». Так держать!
Спасибо. Только с Вами разобрался! ))
Ренат, а какой софт вы используете для записи видео с монитора и создания гиф анимации?
Camtasia Studio 7
Спасибо, большое.
Частенько вставала проблема динамических диапазонов. Теперь есть простое решение. Сайт Ваш очень понравился. Побольше бы статей в нем еще, было бы вообще прекрасно.
Ренат, а вот если нам необходимо посчитать не среднюю сумму за последнюю неделю, а сумму по первому и последнему дню, не поможете решить такую вот задачку?
Если в рамках последнего примера, то вот что пришло первое на ум:
=СУММ(СМЕЩ(B2;СЧЁТЗ(B2:B300)-7;0);СМЕЩ(B2;СЧЁТЗ(B2:B300)-1;0))
В данном случае будет основное ограничение в том, что дни должны идти по порядку один за другим.
Спасибо. Вы гений!))
Ренат добрый день,
столкнулся с задачей, задать в Excel следующее условие:
если в ячейке A1 стоит «да», то в ячейке А2 должно стоять «нет», а если в ячейке А1 стоит «нет» то в ячейке А2 должен появиться выбор из списка «да» или «нет».
Есть ли решение без программирования в VBA ?
Добрый день, Родион. Без VBA на ум приходит только вариант с наложением двойного условия на ячейку A2 и на диапазон выпадающего списка. Чтобы список менял свое содержимое, в зависимости от значения ячейки A1. Большой недостаток, данного метода, что после использования выпадающего списка в ячейке A2, формулы слетают. Пример в прикрепленном файле с условиями на выпадающий список
Ребята, привет!
Может поможете — подскажете:)
Есть данные продаж — по строкам список магазинов, по столбцам — название товара, внутри соответственно количество проданного товара.
Необходимо данные предоставить по строкам — Магазин, товар, кол-во.
Коллеги показывают использование функции СМЕЩ.
Как на ваш взгляд — это наиболее рациональный подход?
Заранее спасибо.
Добрый день,
Думаю, использование функции СМЕЩ — хороший вариант, но в вашем случае, я бы использовал функцию ИНДЕКС
Добрый день.
мне необходимо в отдельную таблицу добавлять данные из другой таблицы. Все данные как бы располагаются по горизонтали. Строка остается прежняя, а столбец смещается на 2 вперед. я задала функцию смещ. но она работает только в первой ячейке. А дальше только если я копирую формулу по горизонтали. Но итоговая таблица по вертикали. Как сделать формулу, чтобы при копировании по вертикали, исходная строка не смещалась? Но при этом менялся столбец? Я сделала так OFFSET(BB$274,0,2).
спасибо
спасибо!!!
Пришлите, пожалуйста, файл с примером, посмотрим. Судя по тому, что вы написали, все должно работать
Ренат, спасибо большое за описание функции!
Не могли бы вы также пояснить метод, который был использован для создания интерактивной таблицы. В частности, меня интересует часть «=ЧСТРОК(диап_смещ I12)», которая возвращает «пусто», если ячейка вне диапазона, и «1», если внутри. Я первый раз вижу использование названия массива через пробел со ссылкой на ячейку, хотел бы понять, какой механизм за этим стоит. Спасибо большое.
Ренат, это одна из хитростей Excel. Функция ЧСТРОК позволяет сказать количество строк, которые находятся на пересечении двух массивов (в нашем случае массива и ячейки). Пробел указывает на то, что нам необходимо именно пересечение
Здравствуйте, Ренат!
С большим интересом прочитал вашу статью о формуле СМЕЩ.
Очень интересно и познавательно! Особенно понравилось
применение СМЕЩ для вычисления среднего значения последней недели.
Просто великолепно! Давно искал что-то подобное, простое
и изящное. Благодарю за ваш труд.
Скажите, а нельзя ли как-то переписать вашу формулу таким образом,
чтобы она при вычислениях учитывала (игнорировала-?) пустые ячейки?
Я попробовал применить вашу формулу к диапазону чисел, в котором
есть пустые ячейки.Применительно к примеру в вашей статье — это как если бы
отсутствовало, например, число 98 в ячейке В20. Получилась какая-то ерунда, формула
вроде бы посчитала, но выдала неверное значение. Как можно это исправить,
чтобы формула работала с диапазоном чисел, в котором есть пустые ячейки?
Мне лично ничего в голову не приходит, пробовал и так и сяк, ничего
не получается. Может быть, поможете решить эту проблему?
Добрый день, чтобы определить положение последнего элемента в диапазоне, необходим непрерывный массив (без пустых ячеек). Если все таки в диапазоне имеются пробелы, роль такого массива может сыграть столбец с датами. Тогда формула примет вид: =СРЗНАЧ(СМЕЩ(B2;СЧЁТЗ(A2:A300)-7;0;7;1))
Здравствуйте, Ренат! Спасибо за помощь, новая формула прекрасно работает,
все правильно считает, в том числе и при наличии пустых ячеек. Как раз то, что мне
и было нужно. Ещё раз благодарю за вашу помощь.
Здравствуйте, Ренат!
Пользуюсь вашей формулой уже 2 дня, прекрасно работает, ещё раз благодарю
за помощь.
В процессе работы с формулой возникла одна проблемка, связанная с особенностью
тех данных, на основании которых я строю таблицу. Я как-то упустил её из вида, когда
в первый раз обращался к вам за помощью. Может быть, поможете решить её сейчас?
Вы пишете:»…Для того, чтобы посчитать среднее значение последней недели,
мы должны вытянуть последние 7 строк со всего массива данных.
Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество
значений и затем, от полученной цифры отнимается 7.» Т.е. формула в обоих
вариантах задает смещение на определенное количество строк, 7 в данном случае,
и уже для них рассчитывают СРЗНАЧ. В моем случае, при наличии пустых ячеек в диапазоне,
может возникнуть ситуация, когда все эти ячейки окажутся пустыми, т.к.
промежутки между данными, как выяснилось, могут быть в одних случаях 1 ячейка, а
в других 5,10, и даже 24 ячейки(!) могут оказаться пустыми. Столкнулся сегодня
с таким вариантом, сам не ожидал ничего подобного. Причем, все это постоянно меняется.
В таких условиях вести расчет в диапазоне, состоящем из определенного количества
строк, очень неудобно. Приходится все равно перелопачивать весь столбец вручную.
В этом случае гораздо лучше было бы считать диапазон, состоящий
не из определенного количества ячеек(7), как это сделано у вас, а диапазон, состоящий из
из определенного количества только лишь ЗАПОЛНЕННЫХ ячеек(7). Которые, с учетом пустых
ячеек между ними, могут занимать 7,10,20… любое количество ячеек, если считать их
подряд, от последней заполненной до седьмой, например, лежащей внутри диапазона В. Именно
такая формула мне необходима. Может быть, на базе вашей формулы, вы смогли бы создать
нечто подобное? Я был бы вам очень благодарен.
Применительно к примеру в вашей статье и с учетом решения для диапазона с пустыми
ячейками, которое вы мне уже подсказали, алгоритм действий мог бы выглядеть следующим
образом. СЧЕТЗ ведем по столбцу А с датами, поскольку он без пробелов. Затем из этого
столбца выбираем только те даты, для которых в столбце В есть заполненные ячейки. Выбираем
определенное количество таких ячеек из столбца В, например 7 последних, и уже для них рассчитываем
СРЗНАЧ. При это вся эта конструкция, естественно, должна работать так же, как и ваша формула,
если в ячейки А и В будут добавлены новые значения. Как-то так.
Но как именно все это выбрать и соотнести? При помощи ВПР, ЕСЛИ, работы с базами
данных, если столбец В обозначить как базу данных, или ещё каких-то функций и приемов?
Тут моих знаний не хватает, очень надеюсь на вашу помощь.
Здравствуйте, Ренат.
Помогите решить следующую проблему по использованию функции СМЕЩ. Есть таблица с двумя страницами. На первой с названием «расчет 2015» в диапазоне F5:F16 находятся данные по расходу эл.энергии в квт.час. за календарные промежутки времени, которые вносятся в диапазон E2:E16. На второй странице с названием «эл.тариф» идет расчет стоимости по сложным тарифам. В ячейку G3 вносится текущее значение =МАКС(‘расчет 2015’!F5:F16), а в ячейку G2 надо внести значение, которое является предыдущим к максимальному (т.е. за предыдущий период). Я составил формулу = =СМЕЩ(МАКС(‘расчет 2015’!F5:F16);-1;0;1;1)), но к сожалению результата нет. Программа говорит «ошибка в формуле». Сам расчет стоимости простой, но не удается перенести нужные данные по квт.часам.
Буду очень признателен, если сможете помочь.
Спасибо.
Владислав, если вам нужно найти следующую после максимальной величину, воспользуйтесь функцией НАИБОЛЬШИЙ, которая возвращает требуемый наибольший элемент в массиве. Подробнее про эту функцию можно прочитать в статье про функцию НАИБОЛЬШИЙ/ НАИМЕНЬШИЙ
Здравствуйте. Помогите с проблемой.
Необходимо, чтобы когда менеджер выбирает день месяца — в соседнем столбце в выпадающем списке были доступны только те машины, которые отметили как «доступные» в этот день (и еще надо, чтобы пустых строк не было в выпадающем перечне)
Для этого сделал перечень авто и возможность, чтобы человек вручную по дням отмечал, какие из них будут доступны (т.к. надо чтобы можно было на сегодня и например на 3 дня вперед отметить доступные авто). После этого сделал еще одну таблицу в разрезе дней, куда переносятся доступные авто без пробелом и для каждого дня создал именованный диапазон (с помощью СМЕЩ, чтобы в конце не было пустых ячеек выпадающем списке).
Диапазоны каждого дня назвал д1, д2 … д31.
И теперь когда пытаюсь привязать эти диапазоны к Дате с помощью ДВССЫЛ, то выпадает ошибка в источнике (как я понимаю, т.к. ДВССЫЛ и СМЕЩ не совместимы?)
Что можно еще придумать, как можно сразу в источник прописать формулу со СМЕЩ, чтобы при выборе дня месяца — сразу выбирался перечень доступных машин этого дня.
Без СМЕЩ всё получается, но тогда пустые ячейки в перечне.
Буду очень благодарен за помощь.
Файл: .xlsx
Спасибо за полезный материал
Здравствуйте! Пожалуйста подскажите, функция находится в одной книге, а аргументы взяты из других файлов. Когда все файлы открыты все прекрасно работает. Когда книги с аргументами закрыты функция выдает #ЗНАЧ! Возможно ли получить правильный результат при закрытых таблицах? Как это сделать? Спасибо!
Здравствуйте! подскажите пожалуйста. нужно написать формулу выбора автомобиля из пронумерованного списка по случайно выбранному числу. Т.е в одну ячейку пишу случайное число от 1 до 24 и, в другой ячейке, по формуле, выбирается автомобиль в соответствии с выбранным числом.