Excel соседняя ячейка относительно предыдущей

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Изучаем функцию СМЕЩ

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

Функция СМЕЩ возвращает ссылку на диапазон с определенной отправной точкой с заданной высотой и шириной.

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

Синтаксис формулы СМЕЩ

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

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

• Ссылка: отправная точка, эта ячейка, относительно которой необходимо смещаться
• Смещение по строкам и столбцам: Указывает, на какое количество строк или столбцов необходимо сместиться от отправной точки. Может иметь положительные и отрицательные значения, а также равняться нулю.
• Высота и ширина: Это размер диапазона, который вы хотите вернуть. К примеру, 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).

Но нам необходимо, чтобы эта формула менялась ежедневно.

Используя формулу СМЕЩ мы можем один раз посчитать среднее значение и больше не возвращаться к этому вопросу.

Разберемся, как работает эта формула

• Для того, чтобы посчитать среднее значение последней недели, мы должны вытянуть последние 7 строк со всего массива данных.
• Вот откуда взялась формула СЧЁТЗ(B2:B300)-7, которая считает количество значений и затем, от полученной цифры отнимается 7.
• Формула СМЕЩ перемещается с ячейки B2 на стартовую ячейку последней недели.
• На гифке показано, как работает динамический диапазон.

Вам также могут быть интересны следующие статьи

26 комментариев

СПАСИБО!
Хоть кто-то нормально разъяснил как эта полезная функция работает.
СПАСИБО!

Огромная благодарность за очень доступное объяснение этой функции для простых людей.
Думаю, не один я «сломал» голову, разбираясь с ней на офф. сайте «мелкомягких». Так держать!

Спасибо. Только с Вами разобрался! ))

Ренат, а какой софт вы используете для записи видео с монитора и создания гиф анимации?

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.
И теперь когда пытаюсь привязать эти диапазоны к Дате с помощью ДВССЫЛ, то выпадает ошибка в источнике (как я понимаю, т.к. ДВССЫЛ и СМЕЩ не совместимы?)
Что можно еще придумать, как можно сразу в источник прописать формулу со СМЕЩ, чтобы при выборе дня месяца — сразу выбирался перечень доступных машин этого дня.
Без СМЕЩ всё получается, но тогда пустые ячейки в перечне.
Буду очень благодарен за помощь.

Источник

СМЕЩ (функция СМЕЩ)

В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции СМЕЩ описаны ниже.

Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

Смещ_по_строкам Обязательный. Количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_строкам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять строк ниже, чем указано в аргументе «ссылка». Значение аргумента «смещ_по_строкам» может быть как положительным (для ячеек ниже начальной ссылки), так и отрицательным (выше начальной ссылки).

Смещ_по_столбцам Обязательный. Количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если в качестве значения аргумента «смещ_по_столбцам» задано число 5, это означает, что левая верхняя ячейка возвращаемой ссылки должна быть на пять столбцов правее, чем указано в аргументе «ссылка». Значение «смещ_по_столбцам» может быть как положительным (для ячеек справа от начальной ссылки), так и отрицательным (слева от начальной ссылки).

Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом.

Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента «ширина» должно быть положительным числом.

Примечания

Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.

Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».

Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник

Improve Article

Save Article

Like Article

Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range of cells depending on the input. With the help of this function, we can traverse from one cell to another cell. Moreover, the user can specify the number of rows and columns to be returned.

This function is a volatile function that also works according to its name. It returns a reference of the desired element(s) that is(are) ‘offset’ of some distance from the given starting point. This can be used within some functions to make tasks easier. 

Syntax:

OFFSET(reference, rows, cols, [height], [width])

-->Here the [height] and [width] are optional.

This function takes a valid reference, rows, and columns(To know how much distance the function will traverse), and height and width(that describes the height and width of output reference) as its arguments and returns the desired reference of the cell or the range of cells. These arguments are discussed below elaborately.

Arguments:

• reference (Required): This is the reference of the starting point or the base of the OFFSET() function and this reference must be provided by the user. The reference must be valid i.e. a reference of a single cell or a reference to a range of adjacent cells.
• rows (Required): This argument contains the number of rows(up or down) that are used by the OFFSET() function to traverse up or down. Again this argument must be provided by the user. This value may be negative or positive. A negative value denotes that the function has to traverse upwards and a positive value denotes traversing downwards.
• cols (Required): This argument contains the number of columns provided by the user. With the help of this argument OFFSET(), function traverse left or right of the starting point. This value may also be negative or positive. A negative value means traversing to the left and a positive value means traversing to the right.
• [height] (Optional): This is the height of the reference to be returned by the OFFSET() function. Basically, this value denotes the number of rows of the returned reference. This value must be a positive number. But this is an optional argument. If this argument is omitted, OFFSET() returns a reference of the same height as the ‘reference’(starting point).
• [width] (Optional): This is the width of the reference to be returned by the OFFSET() function. Basically, this value denotes the number of columns of the returned reference. This value must also be a positive number. But again this is an optional argument. If this argument is omitted, OFFSET() returns a reference of the same width as the ‘reference’(starting point).

Note: The number of rows and columns must not exceed the height and width of the worksheet.

Return Value: This function naturally returns a reference to a range of cells(sometimes a single cell depending on the input arguments) of a certain height and width either same as the ‘reference’ argument or as the user has provided. 

Example:

An Excel sheet has been taken as an example and the OFFSET function has been used in several formats.

OFFSET function has been applied to the above table.

Output:

Like Article

Save Article