Как работает функция смещение в excel

Функция СМЕЩ в Excel, которая может заменить СУММ и сэкономить время

Программу Excel любят за возможность быстро и автоматически считать сложные формулы. Достаточно освоить элементарные команды, чтобы экономить значительное количество времени.

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

СМЕЩ и ее синтаксис

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

1. Ссылка: это отправная ячейка. Относительно нее и происходит смещение.
2. Смещение по строкам: количество строк, на которые нужно спуститься или подняться от отправной ячейки. Если идем вниз – значение будет положительным (от 1 и далее); если вверх – отрицательным (от -1 и далее).
3. Смещение по столбцам: количество столбцов, на которые нужно уйти вправо или влево от отправной точки. Если вправо – положительное значение; влево – отрицательное.
4. Высота и ширина: параметры диапазона, который будет возвращен функцией СМЕЩ. Если нам нужна одна ячейка, мы ставим цифры 1 и 1. Если нужна таблица, задаем иные значения, например 3 и 4, 6 и 8 и т.д.

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

Пример функции СМЕЩ

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

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

Но как только склад предлагает новую марку зубной пасты (назовем ее M), предприятию приходится добавлять дополнительные строки. Если сделать это и ввести новую цифру, то СУММ никак не отреагирует на новую строку (об этом говорит появление зеленого треугольника в верхнем левом углу ячейки и восклицательного знака рядом), потому что команда рассчитана для определенного диапазона. И мы действительно видим, что сумма осталась прежней = 554.

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

Первым числом в СУММ будет ссылка на первоначальную ячейку, с которой пойдет подсчет. Это D5. После нее записываем команду СМЕЩ, которая будет находиться внутри СУММ.

Начинаем заполнять синтаксис функции СМЕЩ. Первое – это ссылка. Ссылаться будем на саму ячейку, в которой у нас стоит формула, т.е. на D18.

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

Смещение по столбцам нам не нужно, поэтому ставим либо 0, либо просто оставляем пустое место. Не забываем разделять элементы формулы точкой с запятой.

Высоту и ширину не указываем. По умолчанию она будет равна одной ячейке.

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

И видим, что сумма увеличилась на 51 (получилось 650), в то время как команда СУММ осталась прежней (554).

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

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

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

Суммирование по "окну" на листе функцией СМЕЩ (OFFSET)

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

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

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

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

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

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

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

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

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

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

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

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

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

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

Что возвращает функция

Возвращает ссылку, которая смещается на заданное количество ячеек.

Синтаксис

=OFFSET(reference, rows, cols, [height], [width]) — английская версия

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

Аргументы функции

• reference (ссылка) — ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
• rows (смещ_по_строкам) — количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное — выше;
• cols (смещ_по_столбцам) — количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
• [height] ([высота]) — количество строк в указанном диапазоне функции;
• [width] ([ширина]) — количество колонок в указанном диапазоне функции.

Основной принцип работы функции

Функция СМЕЩ , пожалуй, самая запутанная функция в Excel.

Давайте разберем ее работу на простом примере игры в шахматы. В шахматах есть фигура Ладья.

Источник фото: Wikipedia

По правилам игры в шахматы, Ладья может ходить только вправо, влево, вниз и вверх. Фигура не может передвигаться по диагонали.

Теперь давайте представим, что нашей Ладье нужно переместиться не строго влево или вправо, а на ячейку, находящуюся по диагонали от изначальной позиции. Что мы будем делать этом случае?

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

Рассмотрим перемещение Ладьи на примере в Excel. Мы хотим начать с ячейки D5 (где находится ладья), а затем перейти на две строки вниз и два столбца вправо и извлечь значение из ячейки. Для этого будем использовать формулу:

=OFFSET(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) — английская версия

=СМЕЩ(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) — русская версия

Как вы видите формула по нашему примеру выглядит так:

=OFFSET(D5,2,2) — английская версия

=СМЕЩ(D5;2;2) — русская версия

Функции задан аргумент старта отсчета с ячейки «D5», затем смещение на две строки вниз, после этого на две колонки вправо. Так мы переместимся с ячейки «D5» на ячейку «F7». По завершении перемещения функция выдает значение ячейки «F7».

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

Давайте рассмотрим простой пример:

Предположим, вы хотите использовать ссылку на ячейку «A1» (желтую), и хотите сослаться на весь диапазон, выделенный синим (C2:E4) в формуле.

Как бы вы это сделали с помощью клавиатуры? Сначала нужно перейти к ячейке C2, а затем выбрать все ячейки в диапазоне «C2:E4».

Теперь посмотрим, как это сделать, используя формулу OFFSET (СМЕЩ) :

=OFFSET(A1,1,2,3,3) — английская версия

=СМЕЩ(A1;1;2;3;3) — русская версия

Если вы используете эту формулу в ячейке, она вернет #VALUE! Но если вы перейдете в режим редактирования, выберете формулу и нажмите клавишу «F9», вы увидите, что она возвращает все значения, выделенные синим цветом.

Надеюсь, теперь у вас есть базовое понимание использования функции OFFSET (СМЕЩ) в Excel.

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

Пример 1. Ищем последнюю заполненную ячейку в колонке

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

=OFFSET(A1,COUNT(A:A)-1,0) — английская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) — русская версия

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку «A1».

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку «A1» на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

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

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

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

Как сделать такой список:

• Выберите ячейку, в которой вы хотите создать выпадающий список;
• Нажмите на вкладку Data => Data Tools => Data Validation;
• В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
• В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
• Нажмите ОК

Как эта формула работает:

Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке «A1», которое не смещается ни по строкам и по колонкам (0, 0);
Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие — отсутствие пустых ячеек в диапазоне.
Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.

Дополнительная информация

• Функция OFFSET (СМЕЩ) — волатильная функция. Она пересчитывается каждый раз, как только вы открываете Excel файл. Работа этой функции может сильно сказываться на скорости работы всего файла.
• Если значения высоты и ширины не указаны, функция учитывает только первые три аргумента;
• Если значения аргументов rows (смещ_по_строкам) и cols (смещ_по_столбцам) отрицательны, то смещение будет происходить в обратную сторону.

Альтернативы функции OFFSET (СМЕЩ) в 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))​

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

Примеры функции «СМЕЩ» в 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 переместите туже​

Динамический диапазон в 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​но если очень​ строке (функция ИНДЕКС()),​ ячеек! Т.е. нужно​

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

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

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

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

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

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

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

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

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

​китин​​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 &​

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

​ проверки формул -​ в строке формул​: Объединение ячеек убрал.​Код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-х,​Задавая параметры функции СМЕЩ()​

смещение столбца по заданному интервалу (Формулы/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​ не встречающийся обычно​Цитата​

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

Проблема

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

​ что считать.​ (если в формуле​ Элементы управления Счетчик.​на вкладке​ в обычный режим.​ в формулах, например​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, смещения​ автоматически:​ вам приходится делать​