Excel смещение в диапазоне

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

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

Пример 1. В таблицу Excel на протяжении экзамена записываются оценки, которые получили уже сдавшие предмет экзамены. Реализовать алгоритм динамического расчета среднего значения балла за экзамен для группы студентов.

Вид исходной таблицы:

Формула для расчета:

Функция ЕСЛИОШИБКА используется для вывода сообщения об отсутствии данных для расчета. Функция СРЗНАЧ принимает диапазон ячеек, возвращаемых функцией СМЕЩ со следующими параметрами:

• B3 – ссылка на начальную ячейку (точку отсчета);
• 0 – нулевое смещение по строкам;
• 0 – нулевое смещение по столбцам;
• СЧЁТ(B3:B14) – количество ячеек из диапазона B3:B14, которые содержат ненулевые значения, характеризующее высоту диапазона возвращаемых ячеек;
• 1 – ширина диапазона возвращаемых значений (в 1 столбец).

Результаты расчета по мере заполнения данными:

Примечания к примеру:

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



Динамическое обновление итоговых данных таблицы в Excel

Пример 2. В таблице Excel внесены данные о количестве продаж товаров за все время работы компании. Для еженедельного учета требуется выводить данные о продажах (общая сумма проданных товаров) за прошедшие 7 дней. Реализовать динамическое обновление данных.

Вид исходной таблицы данных:

Для расчета суммы проданных единиц товаров за последние 7 дней используем формулу:

Описание аргументов функции СМЕЩ:

• B3 – ссылка на ячейку, относительно которой выполняется отсчет;
• СЧЁТЗ(B3:B1000)-7 – функция, подсчитывающая количество непустых ячеек в диапазоне B3:B1000 (ячейка B1000 взята условно с запасом для последующих записей). От итогового результат вычитается число 7, поскольку по условию необходимо получить данные за неделю. В целом выражение определяет начальную позицию массива возвращаемых ячеек по вертикали относительно точки отсчета B3;
• 0 – нулевое смещение по столбцам (искомый диапазон ячеек находится в том же столбце, что и точка отсчета B3);
• 7 – высота диапазона возвращаемых ячеек (7 ячеек);
• 1 – ширина диапазона возвращаемых ячеек.

Полученный результат в результате расчетов:

При добавлении новых данных пересчет выполняется автоматически.

Выборка значений из таблицы с помощью функции СМЕЩ в Excel

Пример 3. В таблице содержатся данные о средних значениях курсов валют по месяцам года. Реализовать алгоритм вывода значений курсов в строку под таблицей по выбранному номеру месяца.

Исходная таблица данных имеет следующий вид:

Используем элемент управления «Счетчик» для выбора номера месяца. Для этого добавим пункт ленты меню «Разработчик» нажатием правой кнопкой мыши по любому существующему ее элементу:

Выберем пункт настройка ленты и в открывшемся окне установим флажок напротив пункта «Разработчик»:

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

Выберем элемент «Счетчик», наведем курсор (вместо стрелки форма «+») на требуемую ячейку (в данном случае A16), выделим область для счетчика. После того, как кнопка мыши будет отпущена, появится заготовка счетчика. Нажмем правой кнопкой по счетчику и выберем пункт «Формат объекта»:

Заполним текущее и минимальное значения, а также установим связь с ячейкой, в которой данный счетчик находится:

Нажмем кнопку «ОК» и выделим любую другую ячейку на листе, чтобы завершить процесс настройки счетчика. В итоге получим:

Для вывода курсов доллара и евро соответственно используем функции:

=СМЕЩ(A1;A16;1)

=СМЕЩ(A1;A16;2)

В результате имеем возможность динамического вывода значений в зависимости от выбранного номера месяца. Примеры работы:

Особенности использования функции СМЕЩ в Excel

Функция имеет следующую синтаксическую запись:

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

Описание аргументов:

• ссылка – обязательный для заполнения аргумент, принимающий данные ссылочного типа, характеризующие ячейку или диапазон ячеек, относительно которых производится отсчет расстояния до возвращаемых ячейки или диапазона ячеек;
• смещение_по_строкам — обязательный аргумент, который принимает данные числового типа, характеризующие расстояние между точкой отсчета (указанной аргументом ссылка) и возвращаемой ячейкой либо диапазоном ячеек. В качестве аргумента может быть передано:

1. Положительное целое число. В этом случае смещение относительно точки отсчета выполняется вправо на указанное число ячеек.
2. 0 – искомая ячейка или диапазон ячеек находятся в текущей строке.
3. Отрицательное число – смещение влево на указанное число, взятое по модулю.
4. Дробное число – функция СМЕЩ выполняет усечение дробной части, и использует полученное целое число для определения величины смещения по строкам.

• смещение_по_столбцам – обязательный аргумент, принимающий числовые значения, указывающие на величину смещения по столбцам относительно выбранной точки отсчета. Указанные свойства аргумента смещ_по_строкам верны для смещ_по_столбцам в интерпретации по вертикали. Например:

1. Функция =СМЕЩ(A5;0;2) вернет ссылку на ячейку C5;
2. Функция =СМЕЩ(A5;2;0) вернет ссылку на ячейку A7;
3. Функция СМЕЩ с аргументами (C5;0-2) вернет ссылку на ячейку A5;
4. Функция с аргументами (D5;0;-3,8) вернет ссылку на ячейку A5.

• [высота] – необязательный аргумент, принимающий числовое значение, характеризующее число ячеек по высоте (количество строк) возвращаемого диапазона ячеек. Принимает значения из диапазона целых положительных чисел, начиная от 1 (если принимает значение 0, функция СМЕЩ вернет код ошибки #ССЫЛКА!);
• [ширина] – необязательный для заполнения аргумент, принимающий числовое значение, которое характеризует ширину диапазона возвращаемых ячеек. Передаваемое значение должно быть целым положительным числом от 1 до +∞.

Примечания:

1. Если функция СМЕЩ ссылается на ячейку или диапазон ячеек, которые находятся вне пределов рабочего листа по условиям, заданным параметрами смещ_по_строкам и смещ_по_столбцам, результатом выполнения данной функции будет код ошибки #ССЫЛКА!.
2. Если необязательные аргументы [высота] и [ширина] явно не казаны, они принимают значения, равные высоте и ширине диапазона ячеек, указанного в качестве аргумента ссылка. Если ссылка принимает одну ячейку, данные аргументы по умолчанию принимают значения 1 и 1 соответственно.
3. Функция СМЕЩ может быть использована в качестве аргумента любой функции, принимающей данные ссылочного типа.
4. Если аргумент ссылка принимает ссылочное значение на единственную ячейку, а необязательные параметры указывают на диапазон (заданы числами, больше 1), тогда функция вернет код ошибки #ЗНАЧ! (пример с ошибкой – результат выполнения функции с аргументами (A5;2;3;3;2)).

Бывают ситуации, когда заранее не известно какие именно ячейки на листе нужно подсчитывать. Например, представим, что нам нужно реализовать в Excel небольшой транспортный калькулятор для расчета расстояния проезда от одной заданной станции до другой:

В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна подсчитываться сумма всех ячеек в заданном «окне» на листе. Для проезда от Останкино до Ховрино, как на рисунке, например, нужно будет просуммировать все ячейки в обведенном зеленым пунктиром диапазоне.

Как считать суммму — понятно, а вот как определить диапазон ячеек, которые нужно просуммировать? Ведь при выборе станций он будет постоянно трансформироваться?

В подобной ситуации может помочь функция СМЕЩ (OFFSET), способная выдать ссылку на «плавающее окно» — диапазон заданного размера, расположенный в определенном месте листа. Синтаксис у функции следующий:

=СМЕЩ(Точка_отсчета; Сдвиг_вниз; Свиг_вправо; Высота; Ширина)

Эта функция на выходе дает ссылку на диапазон, сдвинутый относительно некоей стартовой ячейки (Точка_отсчета) на определенное количество строк вниз и столбцов вправо. Причем размер диапазона («окна») тоже может задаваться параметрами Высота и Ширина. 

В нашем случае, если взять за точку отсчета ячейку А1, то:

• Точка отсчета = А1
• Сдвиг_вниз = 4
• Свиг_вправо = 2
• Высота = 4
• Ширина = 1

Чтобы рассчитать необходимые для СМЕЩ аргументы, давайте сначала применим функцию ПОИСКПОЗ (MATCH), которую мы уже разбирали, для вычисления позиций станций отправления и назначения:

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

Вот и все, задача решена

P.S.

В отличие от большинства остальных функций Excel, СМЕЩ является волатильной (volatile) или, как еще говорят, «летучей» функцией. Обычные функции пересчитываются только в том случае, если меняются ячейки с их аргументами. Волатильные же пересчитываются каждый раз при изменении любой ячейки. Само-собой, это отрицательно сказывается на быстродействии. В больших тяжелых таблицах разница по скорости работы книги может быть очень ощутимой (в разы). Для некоторых случаев быстрее оказывается заменить медленную СМЕЩ на неволатильную ИНДЕКС или другие аналоги.

Ссылки по теме

• Поиск позиции заданного элемента в списке функцией ПОИСКПОЗ (MATCH)
• 4 способа создать выпадающий список в ячейке листа Excel
• 5 вариантов применения функции ИНДЕКС

Функция СМЕЩ() в MS EXCEL

​Смотрите также​ часто, то имеет​ на вкладке​ переводится в евро​ шрифтом есть в​ понял​ путем перемещения и​

​ а переместим, то​ писать свои вопросы​ я использовал функцию​ равна 12 (при​

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

​ вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5),​

• ​ для диапазона суммирования.​​ Формула получится такая.​ заполненные ячейки и​
• ​.​​ Результат 34.​Функция СМЕЩ(), английский вариант​ смысл использовать для​Главная — Найти и​ по курсу из​ постановке задачи из​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$A:$IU;2+2*(СТРОКА()-1);ПРОСМОТР(9^9;ПОИСКПОЗ($A1:F1;ИНДЕКС(Лист2!$A:$IU;2+2*(СТРОКА()-1);0);))+1)​
• ​ это уже считается​​ адреса ее ссылок​​ в чужих темах​​ смещение и столкнулся​ желании ее также​ т.е. 4 (в​
• ​Для формирования динамических диапазонов​​=СУММ(СМЕЩ(B2;СЧЁТЗ(_1_кв.)-3;0;3;1))​ три заполненных нижних​В​Для подсчета суммы значений​
• ​ OFFSET(), возвращает ссылку​​ этого макрос. Нажмите​ выделить — Заменить​ желтой ячейки J2.​

​ файла.​AlexM​ для Excel иной​

Примеры

​ не изменятся, несмотря​Эта тема закрыта.​

​ со сложностью, так​ можно сделать также​ строке 5 присутствуют​ будем использовать функцию​В формуле написано​ ячейки (-3) брать​Excel функция «СМЕЩ»​ в столбце Продажа2​ на диапазон ячеек.​ сочетание клавиш​

​ (Home — Find&Select​Проблема в том, что​Я правда тоже​
​: Если в столбце​
​ способ присваивания адресов​​ на то, что​​Создавайте свою​ как данные Лист1​​ динамической – зависящей​​ 4 месяца с​ СМЕЩ(), которая возвращает​ «_1_кв.» — это​

​ для подсчета суммы.​поможет всегда считать​ запишем формулу:​
​ Размер диапазона и​​Alt+F11​​ — Replace)​
​ если скопировать диапазон​ отклонился от точного​ А сделать номера​​ в формулах данной​​ они относительные. При​Целью этого урока является​ должны располагаться горизонтально,​​ от количества товаров​​ января по апрель).​

​ ссылку на диапазон​ имя динамического диапазона​ Формула «СМЕЩ» переместит​ последние данные в​
​=СУММ(СМЕЩ($B$2;0;​​ его положение задается​​или кнопку​
​В появившемся диалоговом окне​ D2:D8 с формулами​ задания. В моем​ строк, то формула​​ ячейки. Чтобы еще​​ перемещении ссылки на​

​ научить пользователя контролировать​ при этом данные​ в диапазоне).​
​ Итак, адрес нижней​​ в зависимости от​​ столбца, в котором​
​ стартовую ячейку А2​ таблице, если в​​1​​ в параметрах этой​

Функция СМЕЩ() vs ИНДЕКС()

​Visual Basic​ вводим что ищем​​ куда-нибудь в другое​​ случае копирование из​ очень простая​ раз в этом​ ячейки ведут себя​ адреса ссылок на​ на Лист2 располагаются​

​И наконец, записав в​

​ правой ячейки нашего​ значения заданных аргументов.​ считаются данные в​ на ячейку, посчитанную​ таблицу постоянно добавляют​;8;1))​ функции.​на вкладке​ и на что​ место на лист,​ В1, а не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$A:$L;2*СТРОКА();СЧЁТ($A1:A1))​

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

excel2.ru

Примеры функции «СМЕЩ» в Excel.

​ ячейке​ ​ динамического диапазона определен​​ Можно задавать высоту​ трех последних ячейках.​ функцией «СЧЁТЗ», возьмет​ новые данные. Чтобы​Теперь левый верхний​Функция СМЕЩ() часто используется​Разработчик (Developer)​ заменяем и в​ то Microsoft Excel​ из А1.​
​Perfect2You​​ табличку на «Лист1»​ ​ этом следует всегда​​ при их копировании​ в сталбец «E»​С2​ – это​ и ширину диапазона,​В Excel можно​ последние три заполненные​ понять, для чего​ угол диапазона суммирования​ при создании динамических​, вставьте новый модуль​ Параметрах (Options) не​ автоматически скорректирует ссылки​В пустые ячейки​: Или без дополнительного​
​ в изначальный вид​ помнить пользователю Excel.​
​ или перемещении.​
​ Лист1,смещение идет горизонтально.​формулу =СУММ(Продажи_за_месяц) получим​E5​ а также смещение​ посчитать не только​ ячейки для счета​
​ функция «СМЕЩ» и​ смещен от​ диапазонов. Рассмотрим ее​
​ через меню​
​ забываем уточнить Область​ в этих формулах,​ между копиями столбцов,​ столбца, но в​ как на первом​Примечание. В разделе, посвященном​В зависимости от того​ Подскажите, пожалуйста, как​ сумму продаж в​
​.​ по строкам и​
​ конечные итоги, но​ из указанного в​ как она работает,​$B$2​ подробнее.​Insert — Module​ поиска — Формулы.​ сдвинув их на​ могут быть с​ столбце A сохраняется​ рисунке.​ формулам, будет уделено​ копируется или перемещается​ сделать так что​ выбранном месяце.​При заполнении таблицы данными​ столбцам.​ и промежуточные -​ формуле диапазона (из​ прочитайте статью «Функция​на один столбец​СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;высота;ширина), где​
​ и скопируйте туда​ Жмем Заменить все​
​ новое место и​ текстовыми значениями, они​ то, что было​На этот раз в​
​ больше внимания относительным​ формула в Excel,​
​ бы,смещение шло вертикально​Например, в мае.​ о продажах за​
​Создадим динамический диапазон для​
​ по разделам, строкам,​
​ А2:А20).​ «СМЕЩ» в Excel».​ вправо, т.е. диапазон​ссылка​ текст вот такого​ (Replace all).​
​ перестав считать:​
​ формулы не собьют.​ у Вас, а​ ячейку E2 скопируйте​ и абсолютным ссылкам.​ адреса ее ссылок​ относительно Ссылки, в​Или, например, в апреле.​ май, июнь и​ Выпадающего списка, содержащего​ т.д. В Excel​«0» — нет​Здесь разберем​ суммирования стал​- ссылка, от​ макроса:​Копируем получившийся диапазон с​Задача: скопировать диапазон с​​PS. виртуозом я​ далее уже​ формулу из B2,​ А пока отметим​ могут существенно отличаться.​ формуле смещения. Пример​Примечание:​ т.д., формула СЧЁТЗ(лист1!$B$5:$I$5)​
​ месяцы. С одной​
​ есть специальная функция,​ смещения по столбцам.​примеры функции «СМЕЩ» в​$C$2:$C$9​ которой вычисляется смещение;​Sub Copy_Formulas() Dim​
​ деактивированными формулами в​
​ формулами так, чтобы​ еще не стал.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$A:$L;2*СТРОКА();СЧЁТ($A1:A1)+1)​ а в ячейку​ что по умолчанию​ Об этом нужно​
​ файла прилагаю, буду​Вместо формулы с​ будет возвращать число​ стороны нужно учитывать​ с помощью которой,​«3» — брать​Excel​. Результат 68.​смещ_по_строкам​ copyRange As Range,​ нужное место:​ формулы не изменились​ Только учусь у​Это конкретно для​ D2 переместите туже​

excel-office.ru

Динамический диапазон в MS EXCEL

​ ссылки относительные, а​ помнить всегда!​ признателен за помощь.​ функцией СМЕЩ() для​ заполненных ячеек (количество​ тот факт, что​ можно быстро настроить​ диапазон для счета,​. Эта функция автоматически​Для подсчета суммы значений​

​- количество строк,​ pasteRange As Range​Заменяем​ и остались теми​ коллег по форуму.​ B1.​ самую формулу.​

​ если в адресе​На готовом примере разберем​С уважением,​ подсчета заполненных месяцев​ названий месяцев) и​ пользователь может добавлять​

Задача

​ таблицу на подсчет​ состоящий из трех​ меняет в формуле​

​ в столбцах Продажа1​ которые требуется отсчитать​ On Error Resume​#​ же самыми, сохранив​miha_​У Вас строки​Программа нас информирует, что​ присутствует символ «$»​ согбенности изменения ссылок​

​Эльдар​ можно использовать формулу​ соответственно определять новую​ продажи за следующие​ промежуточных итогов. Подробнее​ строк (ячеек).​

​ адрес диапазона таблицы,​ и Продажа2, изменим​ вверх или вниз,​ Next Set copyRange​на​ результаты расчета.​: Всем большое спасибо!​ вытягиваются через одну.​ мы имеем ошибку​ — значит ссылка​ в формулах. Но​

​_Boroda_​ с функцией ИНДЕКС():​ ширину динамического диапазона,​ после апреля месяцы​ про эту функцию​«1» — в​ относительно конкретной ячейки.​ ширину диапазона.​ чтобы левая верхняя​ = Application.InputBox(«Выделите ячейки​=​Как можно заметить​miha_​ Так и надо?​ «неправильная ссылка на​ абсолютная.​

​ перед тем как​

• ​: Не советую Пользоваться​=$B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))​ который в свою​ (май, июнь…), с​
• ​ читайте в статье​ диапазоне один столбец.​
• ​ Помогает настроить таблицу,​=СУММ(СМЕЩ($B$2;0;0;8;​
• ​ ячейка результата ссылалась​ с формулами, которые​
• ​обратно с помощью​

​ по предыдущей картинке,​
​: Уважаемые, форумчане!Всем добрый​И еще. Если​ ячейку» в E2.​Теперь усложним задание. Верните​ скопировать формулу в​ для таких случаев​Формула подсчитывает количество элементов​ очередь будет формировать​​ другой стороны Выпадающий​​ «Промежуточные итоги в​Получается, нужно посчитать​​ чтобы считались новые​​2​ на нужную ячейку​ надо скопировать.», _​ того же окна,​ Excel сдвигает только​ вечер!Рад всех приветствовать!​​ где-то будет вставлено​​ Но если бы​ табличку до изначального​ Excel, создайте на​ СМЕЩем — при​ в строке 5​ Выпадающий список.​ список не должен​ Excel».​ сумму ячеек А5,​ добавленные данные за​))​ (по умолчанию =0);​ «Точное копирование формул»,​​ возвращая функциональность формулам.​​ относительные ссылки. Абсолютная​

​Друзья, к сожалению​ значение в отдельную​ мы не переносили,​ вида как на​ листе простую табличку​ достаточном количестве подобных​ (функция СЧЁТЗ()) и​ВНИМАНИЕ! При использовании функции​ содержать пустые строки.​Если вам необходимо постоянно​ А6, А7.​ определенный период. Чтобы​

​указав ширину в​смещ_по_столбцам​ Default:=Selection.Address, Type:=8) If​Этот способ существенно быстрее​ (со знаками $)​ не удалось достичь​

​ ячейку, то в​ а просто скопировали​ первом рисунке. Выполните​

​ как показано на​

• ​ формул будет тормозить​ определяет ссылку на​ СЧЕТЗ() необходимо убедиться​ Динамический диапазон как​
• ​ добавлять значения в​Получится так.​
• ​ в графике отражались​ 2 ячейки, результат​
• ​– аналогично​

​ copyRange Is Nothing​

​ и проще.​ ссылка на желтую​ необходимого результата.скорее всего​ последующих столбцах будут​​ формулы, то никаких​​ ряд последовательных действий:​ рисунке:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$E46:$E57;СТОЛБЕЦ()-СТОЛБЕЦ($E7)+1)​ последний элемент в​ в отсутствии пустых​ раз и служит​ столбец, то для​Сумма равна 15.​ последние данные из​ составит 102, диапазон​смещ_по_строкам,​ Then Exit Sub​Нажмите сочетание клавиш​​ ячейку $J$2 не​​ моя вина -​ значения из РАЗНЫХ​ ошибок не возникло.​Скопируйте «Лист1», например с​Скопируйте значения столбца B​но если очень​ строке (функция ИНДЕКС()),​ ячеек! Т.е. нужно​

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

​ Set pasteRange =​

​Ctrl+Ё​

​ сместилась. Поэтому для​
​ не совсем корректно​ столбцов, т.к. проверка​Примечание. Быстро перемещать формулы​ помощью мышки+CTRL. Наведите​ (например, комбинацией клавиш​
​ хочется, то так​

​ тем самым возвращает​ заполнять перечень месяцев​ задачи.​ формул, Вам наверняка​ в ячейку А8​ период времени, т.д.​$В$2:$С$9​ по столбцам (по​​ Application.InputBox(«Теперь выделите диапазон​​ или кнопку​

Визуальное отображение динамического диапазона

​ точного копирования формул​ задал вопрос.В примере​ идет по каждой​ можно с помощью​ указатель на ярлычок​​ CTRL+C) и вставьте​​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СМЕЩ(Лист2!$E45;СТОЛБЕЦ(A7);)​ ссылку на диапазон​
​ без пропусков.​

​Для создания динамического диапазона:​ понадобятся динамические диапазоны,​ данные, то формула​ Об этом читайте​.​

Применение динамического диапазона

​ умолчанию =0);​ вставки.» & vbCrLf​Показать формулы​ можно временно перевести​ более подробно написал​ строке отдельно. Чтобы​ перетаскивания ячейки мышкой​ первого листа. Удерживая​ их в столбец​

excel2.ru

Функция смещение. (Формулы)

​китин​​B5:E5​
​Теперь создадим еще один​
​на вкладке Формулы в​ которые автоматически увеличиваются​ посчитает сумму трех​ статью «Динамические графики​Добавив смещение по строкам​высота​ & vbCrLf &​на вкладке​ все ссылки во​ свой вопрос.Если не​ это исключить, нужно​ удерживая левую клавишу​ левую клавишу мышки​ D (CTRL+V) .​: вариант​.​ динамический диапазон для​ группе Определенные имена​ или уменьшаются в​ нижних ячеек –​ в Excel».​ (+1), получим результат​- число строк​ _ «Диапазон должен​
​Формулы (Formulas — Show​
​ всех формулах в​

​ сложно посмотрите)Хорошего вечера​​ проверяться по одной​ после наведения указателя​ и клавишу CTRL​ А потом переместите​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$E$46:$E$57;СТОЛБЕЦ(A1);)​
​Выделить текущий динамический диапазон​
​ суммирования продаж.​ выберите команду Присвоить​
​ зависимости от количества​

​ А6, А7, А8.​​У нас такая​​ 99:​

​ возвращаемой ссылки. Значение​​ быть равен по​ formulas)​

​ абсолютные. Нужно будет​​ и отличного настроения!​
​ (например, первой) строке.​ на рамку курсора​ на клавиатуре, переместите​ данные из столбца​
​Eldar4ik​ можно с помощью​Для создания динамического диапазона:​ имя;​ ваших данных.​Второй пример.​ таблица.​=СУММ(СМЕЩ($B$2;​ аргумента «высота» должно​ размеру исходному «​, чтобы включить режим​ выделить каждую формулу​AlexM​ Тогда​ выделенной ячейки. А​ ярлычок (копия листа)​ B в E​: _Boroda_, китин, Спасибо​ Условного форматирования. В​на вкладке Формулы в​
​в поле Имя введите:​
​Динамический диапазон — это​
​Вместо цифр, указывающих​Первый пример.​1​
​ быть положительным числом;​
​ & vbCrLf &​

excelworld.ru

Копирование формул без изменения адресов относительных ссылок

​ проверки формул -​ в строке формул​: Объединение ячеек убрал.​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$A:$L;2*СТРОКА();СЧЁТ($A$1:A$1)+1)​ выполнив это действие​ в новое место.​

​ (например, комбинацией клавиш​ большое!​ файле примера для​ группе Определенные имена​ Месяц;​ Именованный диапазон с​ количество строк, столбцов,​

Копирование формул Excel без изменений ссылок

​Например, таблица дополняется​;0;8;2))​ширина​ _ «диапазону копируемых​ в ячейках вместо​ и нажать клавишу​ Формулу поправил, на​AlexM​ с нажатой клавишей​

​ Отпустите сначала мышку,​ CTRL+X).​Kreed​ ячеек диапазона​ выберите команду Присвоить​в поле Область выберите​ изменяющимися границами. Границы​ в формуле функции​ новыми данными каждый​диапазон будет модифицирован​

​- число столбцов​ ячеек.», «Точное копирование​ результатов начнут отображаться​F4​ листе чуть изменил​: Тогда не выполняется​ CTRL, тогда формула​

​ а потом клавиатуру.​Теперь переключитесь в режим​: Добрый день!​B6:I14​ имя;​ лист Книга;​ диапазона изменяются в​

​ «СМЕЩ» можно писать​

Копирование формул в Excel со смещением

​ день. Нам нужно​ в​ возвращаемой ссылки (по​ формул», _ Default:=Selection.Address,​ формулы, по которым​:​ данные двух месяцев,​ условие задачи​ скопируется.​ У вас получился​ отображения формул –​Я думаю, что​применено правило Условного форматирования​в поле Имя введите:​в поле Диапазон введите​ зависимости от количества​ адрес ячейки, в​ посчитать сумму значений​$В$3:$С$9​ умолчанию =1).​ Type:=8) If pasteRange.Cells.Count​ они посчитаны:​При большом количестве​

​ иначе не видно​чтобы при копировании формулы​Данный урок может показаться​ такой же лист,​ CTRL+`(Ё). Обратите внимание,​ у меня вопрос​ с формулой:​ Продажи_за_месяц;​ формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))​ значений в определенном​ которой написано это​

​ только за последние​.​Чтобы было понятнее, потренируемся​ <> copyRange.Cells.Count Then​Скопируйте наш диапазон D2:D8​

1. ​ ячеек этот вариант,​ работу формул​ из ячейки А1,​ сложным для понимания,​ но уже с​ как ведут себя​ по этой же​=СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)​в поле Диапазон введите​нажмите ОК.​ диапазоне.​ число. Это дает​ три дня.​Пусть имеется диапазон с​ с функцией СМЕЩ(),​
2. ​ MsgBox «Диапазоны копирования​ и вставьте его​ понятное дело, отпадает​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!$F:$Q;8*СТРОКА(A1)+1;СЧЁТ($A5:D5))​ допустим в ячейку​ но на практике​
3. ​ названием «Лист1(2)».​ ссылки на ячейки​ теме. Вопрос:​Условное форматирование автоматически выделяет​ формулу =СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)​

​Теперь подробнее.​Динамические диапазоны используются для​ возможность, выбирать, считать​В ячейке Е5​ числами (​ используя файл примера.​ и вставки разного​ в стандартный​ — слишком трудоемко.​miha_​ G, данные отображались​ достаточно прост. Нужно​На копии «Лист1(2)» в​ при перемещении и​Если при постановке​ серым цветом продажи​нажмите ОК.​Любой диапазон в​ создания таких структур,​ разные диапазоны, меняя​ пишем такую формулу.​А2:А10​Дана исходная таблица с​ размера!», vbExclamation, «Ошибка​

​Блокнот​Чтобы формулы при копировании​:​ с листа2 из​ только помнить об​ ячейку D2 скопируйте​

​ копировании.​ ссылки в ячейку​ текущего месяца, выбранного​Теперь подробнее.​ EXCEL задается координатами​ как: Выпадающий (раскрывающийся)​ число в ячейке.​ =СУММ(СМЕЩ(A2;СЧЁТЗ(A2:A20)-3;0;3;1))​) Необходимо найти сумму​

​ тремя столбцами.​ копирования» Exit Sub​:​ не менялись, надо​AlexM​ столбца В​ особенностях поведения формул​ значение из B2,​При перемещении (те, что​ D2 (в любой​ с помощью Выпадающего​

​Функция ПОИСКПОЗ() ищет в​ верхней левой и​ список, Вложенный связанный​ Например, в ячейках​Пояснение формулы:​ первых 2-х, 3-х,​Задавая параметры функции СМЕЩ()​

exceltable.com

смещение столбца по заданному интервалу (Формулы/Formulas)

​ End If If​​Теперь выделите все вставленное​ (временно) сделать так,​, Добрый,вечер.Ваша изящность в​Я понял, что​ при их копировании.​ а в ячейку​ в столбце E)​ из листов «Иванов;​ списка.​ строке 5 (перечень​ нижней правой ячейки​ список и Связанный​ столбца F мы​В формуле функции​ …9 значений. Конечно,​ подсчитаем сумму значений​

​ pasteRange Is Nothing​​ (Ctrl+A), скопируйте в​ чтобы Excel перестал​
​ форме написания меня​

​ формула должна быть​​miha_​ E2 переместите (как​ ссылки не изменяются.​ Петров; Сидоров»)у меня​​Примеры использования динамического диапазона,​

​ месяцев) выбранный пользователем​​ диапазона. Исходной ячейкой,​ список.​ написали числа смещения.​ «СУММ» мы указали​ можно написать несколько​ в различных диапазонах​
​ Then Exit Sub​
​ буфер еще раз​ их рассматривать как​
​ всегда поражает. Спасибо.​ единая и протягиваемая.​: Друзья добрый день.​
​ на предыдущем задании).​ А при копировании​ отображается значение соответствующее​ например, можно посмотреть​ месяц (ячейка​ от которой отсчитывается​Имеется таблица продаж по​ Вставили в формулу​ не конкретные адреса​ формул =СУММ(А2:А3), =СУММ(А2:А4) и т.д. Но,​ таблицы. Для визуального​ Else pasteRange.Formula =​ (Ctrl+C) и вставьте​
​ формулы. Это можно​

​Скажите пожалуйста, а​​Perfect2You​ Немного запутался с​​Теперь скопируйте столбцы D:E​ (те, что в​ ссылке на листе​ в статьях Динамические​С1​ положение нашего динамического​​ месяцам некоторых товаров​ адреса этих ячеек,​ ячеек для сложения​

​ записав формулу ввиде:​​ наблюдения диапазона, возвращаемого​
​ copyRange.Formula End If​ на лист в​ сделать, заменив на​
​ без объединения ячеек​: AlexM!​ формулой смещ и​
​ из «Лист1(2)» и​ столбце D), они​ «2014», то какую​
​ диаграммы. Часть5: график​с выпадающим списком)​ диапазона, является ячейка​ (см. Файл примера):​

​ вместо конкретных чисел.​​ (А5, А6, А7),​=СУММ(СМЕЩ(A2;;;4))​ функцией СМЕЩ(), использовано​ End Sub​
​ нужное вам место:​ время копирования знак​ можно?​В постановке задачи​ индекс. Необходима, ваша​ вставьте их в​
​ смещаются автоматически.​ употребить формулу в​ с Прокруткой и​ и возвращает соответствующий​B5​
​Необходимо найти сумму продаж​ Получилось так.​ а вложили функцию​получим универсальное решение, в​

​ Условное форматирование. Для​​Для запуска макроса можно​

​Осталось только отжать кнопку​​ «равно» (=) на​AlexM​
​ не было таких​ помощь. Нужно скорректировать​ столбцы D:E из​​ ячейках D2, D3,​ Масштабированием и Динамические​ номер позиции в​. Если не заданы​ товаров в определенном​Если в этой​

​ «СМЕЩ», указав от​​ котором требуется изменять​ удобства изменения параметров​ воспользоваться кнопкой​Показать формулы (Show Formulas)​ любой другой символ,​: Нужно.​​ ограничений.​

​ формулу, для смещения​​ «Лист1».​​При копировании адреса относительных​​ D4 (на листах​ диаграммы. Часть4: Выборка​ диапазоне поиска (названия​
​ аргументы функции СМЕЩ()​ месяце. Пользователь должен​ формуле вместо функции​

​ какой ячейки и​​ только последний аргумент​
​ функции СМЕЩ() использованы​​Макросы​, чтобы вернуть Excel​ не встречающийся обычно​Цитата​

excelworld.ru

Копирование формул без сдвига ссылок

Проблема

​Ваших заслуг не​ столбца с заданным​Как видите обе ячейки​ ссылок приспосабливаются к​ «Иванов; Петров; Сидоров»),​ данных из определенного​ месяцев должны быть​смещ_по_строкам,смещ_по_столбцам​ иметь возможность выбрать​ «СУММ» (см. первый​

​ что считать.​ (если в формуле​ Элементы управления Счетчик.​на вкладке​ в обычный режим.​ в формулах, например​AlexM, 03.03.2017 в​ умаляю! Вы признанный​ интервалом. Пример во​ D2 и E2​ новому положению. Если​

​ чтобы при протягивании​ диапазона.​ уникальны, т.е. этот​(как в нашем​ нужный ему месяц​ пример), напишем другую​

Способ 1. Абсолютные ссылки

​СМЕЩ(А2 – считать​ выше вместо 4​Для подсчета суммы значений​Разработчик (Developer — Macros)​Примечание: этот способ иногда​ на «решетку» (#)​ 21:53, в сообщении​ виртуоз!​ вложении.Главное формула, должна​ были одинаково и​ ссылка была на​ ячейки D2 вниз​Eldar4ik​ пример не годится​ случае), то эта​ и получить итоговую​​ функцию, например, «СРЗНАЧ»,​​ от этой ячейки.​

​ ввести 5, то​ в столбце Продажа1​или сочетанием клавиш​ дает сбой на​

Способ 2. Временная деактивация формул

​ или на пару​ № 10 ()​Себя тоже хотелось​ быть не сложной,​ одновременно скопированы, но​ одну ячейку влево,​ (по столбцу) отображались​: Добрый день!​ для нескольких лет).​ ячейка является левой​ сумму продаж. Выбор​ то формула посчитает​СЧЁТЗ(А2:А20)-3 – считать​ будет подсчитана сумма​ запишем формулу:​Alt+F8​

1. ​ сложных таблицах с​ амперсандов (&&). Для​ 200?’200px’:»+(this.scrollHeight+5)+’px’);»>Объединение ячеек убрал.​
2. ​ бы таким считать,​​ т.к. будет задействован​​ ссылки в их​ то она так​​ значения соответствующие листу​Вопрос следующего характера.​ На это число​ верхней ячейкой диапазона.​
   
3. ​ месяца пользователь должен​ среднее значение в​ только заполненные ячейки,​ первых 5-и значений).​=СУММ(СМЕЩ($B$2;0;0;8;1))​. После запуска макрос​ объединенными ячейками, но​ этого:​Предположим, что у нас​
4. ​ но притормаживаю еще.​ большой массив данных​ формулах уже ведут​
   
5. ​ и продолжает ссылаться,​​ «2014» этих параметров.​​Есть два диапазона​​ столбцов смещается левый​​ Нижняя правая ячейка​ осуществлять с помощью​ трех последних ячейках.​

Способ 3. Копирование через Блокнот

​ не зависимо от​ Вышеуказанная формула эквивалентна​

​диапазон суммирования -​​ попросит вас выделить​​ в подавляющем большинстве​​Выделяем диапазон с формулами​​ есть вот такая​​Еще полгода назад​ и не хочется​​ себя по-разному. При​ но адрес естественно​ И тоже самое​ данных на листах​ верхний угол нашего​ диапазона определяется аргументами​

​ Выпадающего списка.​Третий пример.​ того, смежные они​​ формуле =СУММ(A2:ИНДЕКС(A2:A10;4)), которая,​​$B$2:$B$9​

​ диапазон с исходными​ случаев — работает​ (в нашем примере​ несложная таблица, в​ считал… До выхода​ чтобы это всё​

​ копировании формул E2​​ меняется. Поэтому формула:​​ при протягивании ячейки​ «Лист1 и Лист2″(прощенный​

​ динамического диапазона (от​высота ширина​Для решения задачи нам​Вместо цифр в​ или нет. А2:А20​ в свою очередь,​(левый верхний угол​

Способ 4. Макрос

​ формулами и диапазон​ отлично.​ D2:D8)​ которой подсчитываются суммы​ в интернет и​ висело. Спасибо.Всем хорошего​ значение не меняется.​​ =A2*1,23 стала формулой​​ D2 вправо?​​ вариант). Так как​​ ячейки​​. В нашем случае​​ потребуется сформировать два​ формуле функции «СМЕЩ»,​​ – это диапазон​​ эквивалентна формуле =СУММ(A2:A5)​ -​ вставки и произведет​

​Если подобное копирование формул​Жмем​ по каждому месяцу​ на форумы.​ настроения и добра.​ Все из-за того,​ =C2*1,23. Когда мы​Файл во вложении.​ делать линк между​А6​ значение высоты =1,​ динамических диапазона: один​ указывающих количество строк​ всего столбца. Эта​Формула ИНДЕКС(A2:A10;4) возвращает ссылку​$B$2​ точное копирование формул​ без сдвига ссылок​Ctrl+H​ в двух городах,​AlexM​krosav4ig​ что значения E2​ ту же самую​Заранее спасибо!​ листами через равно​), высота диапазона не​ а значение ширины​ для Выпадающего списка,​ и столбцов, можно​ функция будет определять​ на ячейку​, высота 8, смещения​ автоматически:​ вам приходится делать​

​на клавиатуре или​ а затем итог​​: Фраза выделенная жирным​​: Здравствуйте. Вдруг правильно​​ из «Лист1(2)» получены​​ формулу не скопируем,​​Правилами форума запрещено​​ «=» долго, то​ меняется и всегда​ диапазона равно результату​ содержащего месяцы; другой​ написать имя диапазона.​ в этом диапазоне​А5​

planetaexcel.ru

​ верхнего угла нет).​

Функция

СМЕЩ(

)

, английский вариант OFFSET(),

возвращает ссылку на диапазон ячеек. Размер диапазона и его положение задается в параметрах этой функции.

Функция

СМЕЩ()

часто используется при создании

динамических диапазонов

. Рассмотрим ее подробнее.

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

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

• 
  ссылка
  
  — ссылка, от которой вычисляется смещение;
• 
  смещ_по_строкам
  
  — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =0);
• 
  смещ_по_столбцам
  
  – аналогично
  
  смещ_по_строкам,
  
  только смещение отсчитывается по столбцам (по умолчанию =0);
• 
  высота
  
  — число строк возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом;
• 
  ширина
  
  — число столбцов возвращаемой ссылки (по умолчанию =1).

Чтобы было понятнее, потренируемся с функцией

СМЕЩ()

, используя

файл примера

.

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции

СМЕЩ()

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

СМЕЩ()

, использовано

Условное форматирование

. Для удобства изменения параметров функции

СМЕЩ()

использованы

Элементы управления

Счетчик

.

Для подсчета суммы значений в столбце

Продажа1

запишем формулу:

=СУММ(СМЕЩ($B$2;0;0;8;1))

диапазон суммирования —

$B$2:$B$9

(левый верхний угол —

$B$2

, высота

8

, смещения верхнего угла нет). Результат

34

.

Для подсчета суммы значений в столбце

Продажа2

запишем формулу:

=СУММ(СМЕЩ($B$2;0;

1

;8;1))

Теперь левый верхний угол диапазона суммирования смещен от

$B$2

на один столбец вправо, т.е. диапазон суммирования стал

$C$2:$C$9

. Результат

68

.

Для подсчета суммы значений в столбцах

Продажа1

и

Продажа2,

изменим ширину диапазона.

=СУММ(СМЕЩ($B$2;0;0;8;

2

))

указав ширину в 2 ячейки, результат составит

102

, диапазон будет модифицирован в

$В$2:$С$9

.

Добавив смещение по строкам (+1), получим результат

99

:

=СУММ(СМЕЩ($B$2;

1

;0;8;2))

диапазон будет модифицирован в

$В$3:$С$9

.

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами (

А2:А10

) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул

=СУММ(А2:А3)

,

=СУММ(А2:А4)

и т.д. Но, записав формулу ввиде:

=СУММ(СМЕЩ(A2;;;4))

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле

=СУММ(A2:ИНДЕКС(A2:A10;4))

, которая, в свою очередь, эквивалентна формуле

=СУММ(A2:A5)

Формула

ИНДЕКС(A2:A10;4)

возвращает ссылку на ячейку

А5

.