Excel вывести значение ячейки со сдвигом

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

history 9 апреля 2013 г.

Функция СМЕЩ() часто используется при создании динамических диапазонов . Рассмотрим ее подробнее.

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

• ссылка — ссылка, от которой вычисляется смещение;
• смещ_по_строкам — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку (по умолчанию =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) и т.д. Но, записав формулу ввиде:

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5 .

Источник

data_client

Альтернатива ВПР в Excel. Функции СМЕЩ и ПОИСКПОЗ

При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.

Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:

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

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

Ссылка — ячейка, от которой рассчитывается смещение по строкам и столбцам.

Смещение по строкам — указываем число, на какое количество строк надо сдвинуться. Положительное число — количество строк вниз, отрицательное число — количество строк вверх.

Смещение по столбцам — указываем число, на какое количество столбцов надо сдвинуться. Положительное число — количество столбцов вправо, отрицательное число — количество столбцов влево.

К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).

ПОИСКПОЗ(искомое значение; просматриваемый массив; тип сопоставления)

Искомое значение — это то значение, что мы ищем в таблице

Просматриваемый массив — таблица, где мы ищем искомое значение

Тип сопоставления — как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.

К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ(«Петров»;B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.

Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.

Итак, для нашего примера формула будет следующая:

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

Надеюсь, статья была для вас полезной. Спасибо за внимание.

Источник

Смещение в эксель

Функция СМЕЩ() в 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))​

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

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

Динамический диапазон в 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. Временная деактивация формул

​ или на пару​ № 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​

Источник