Remkomplekty.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Смещение vba excel

Как использовать относительные имена в Excel VBA

Многие «продвинутые» (aka: VBA) excel учебные пособия в Интернете или даже excel vba help encurage нам использовать

(точнее: объект) для выбора ячеек или получения значений. В том же месте они часто добавляют все в порядке, чтобы использовать также предопределенные имена:

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

Однако относительные имена не работают в методе Range ( «valuesabove» ), как мы привыкли к этому.

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

В объекте VBA Range() это неверно. Диапазон относится к объекту WorkSheet, по умолчанию к ActiveSheet. Но ActiveSheet объявляется его левой ячейкой A1. И это то, что Range оказывается относительно. И именно поэтому абсолютные имена ($ C $23) работают с ним, а относительные ( «один столбец слева, две строки вверх» ) этого не делают.

Итак, мой вопрос:
Как я могу использовать силу относительных имен в VBA?

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

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

В excel ссылка на ячейку может быть как относительной, абсолютной, так и смешанной. Это верно также при создании имен. Таким образом, мы можем говорить об абсолютных, относительных или смешанных именах (в терминах ссылки, конечно).

Здесь абсолютное имя используется пару раз (создается с использованием функции excel Trace Dependents):

Название «name» = $D$2

A относительное имя используется здесь несколько раз:

Назовите «upright24», пока, например. ячейка A7 выбрана = C3 (без знаков $!). Но это постоянно изменяется в зависимости от выбранной ячейки или региона. Вы можете проверить его в менеджере имен! (Ctrl + F3)

И это то, что мы можем рассматривать как смешанное имя:

Назовите «rel_serialnumber», в то время как, например, ячейка C6 выбрана = $B6 . Ряд которых (6) постоянно изменяется в соответствии с выбранной ячейкой или областью.

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

Обратите внимание, что

абсолютные имена означают динамическое смещение от ссылочной ячейки, которая является одной и единственной

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

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

Хорошо, теперь вот что. У меня есть база данных, подобная базе данных, где я обрабатываю строки, такие как записи и столбцы, как поля свойств. Пользователь использует эту вещь следующим образом: он «выбирает запись», помещая курсор в любую ячейку строки нужной записи. Затем он нажимает кнопку которая запускает мой макрос VBA. Это намеревается открыть подготовленный файл скелета и заполнить в нем некоторые определенные ячейки (которые определяются btw абсолютными именами) с некоторыми значениями (которые определены смешанными именами) из выбранной записи.

Так как Range(«name») считается подходящим для использования в VBA (см. выше), я думал, что Range(«relativename») или Range(«mixedname») будет работать так же хорошо, если автоматически полагаться на активную ячейку.
Я не могу быть хуже.
Только Range(«absolutename») работает так, как можно было бы ожидать! Объяснение см. Выше.

Итак, я выполняю функцию/метод/объект, который, возможно, удобен для использования с «relativename» или «mixedname» как Range(«absolutename») .

Я думаю, что нашел подходящее и компактное решение. Это

Не так короток, как Range(«mixedname») , но он действительно обеспечивает ожидаемые значения.

UPDATE:

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

В этом случае необходимо сохранить нефиксированную часть имени:

sourcerow = ActiveCell.Row
[. ]
‘opening a wbk, this also makes it the active one
[. ]
Names(«dest»).RefersToRange = mysheet.Cells(sourcerow, mybook.Names(«src»).RefersToRange.Column)

Однако вы могли бы сделать это как:

Просто кажется, что для этого уже существует относительно простой синтаксис.

Чтобы ссылаться на диапазон относительно другого диапазона, вы можете использовать этот синтаксис:

Примечание. Это работает только в том случае, если смещение Row и смещения столбца положительны. Например, если формула «Относится к» для именованного диапазона «= Смещение (A1, r, c)», то приведенный выше синтаксис выдает ошибку, если либо r Or c отрицательный. Но он будет работать, если оба будут положительными.

Асимметрия неудачна, но, как обычно, для VBA…

Ссылка на третий столбец в строке текущего ActiveCell:

Для ссылки на смещение ячейки (например) 1 строка и 3 столбца относительно ActiveCell :

Очевидно, что вы можете использовать тот же синтаксис с объектом Selection или любым другим значением диапазона в VBA.

Private Sub Worksheet_Change(ByVal Target as Range)
If Not Intersect(Target.Address,ThisWorkbook.Sheets(‘sheetname).Range(‘RangeName)) Is Nothing Then _
‘Do whatever you want down here.
ThisWorbook.Sheets(‘sheetname).Range(‘RangeName).Offset(0,Target.Row)
End If
End Sub

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

У меня была такая же проблема, но я получил ее на работу — вроде. Я не знаю, что отличается от простого примера ниже, но он работает. Сначала я думал, что выбор имеет значение, но нет — он работает без изменения активной ячейки.

(Я все еще не могу заставить его работать в моей основной таблице.)

    Именованный диапазон: «TestName» = Sheet1! $H1

5 задач в Excel, которые решает функция СМЕЩ (OFFSET)

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

1) Сделать зависимый выпадающий список, в который можно добавлять данные;

2) Добиться того, чтобы новые строки сразу попадали в сводную таблицу при обновлении;

3) Отображать на графике только данные за последние 7 дней, причем новые данные вносятся ежедневно;

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

5) В новом столбце сослаться на каждую пятую ячейку соседнего столбца

Как видите, задачи интересные и нетривиальные. Если Вы хотите научиться их решать в два счета, то стоит уделить несколько минут изучению функции СМЕЩ.

Синтаксис и алгоритм работы функции

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

Функция имеет следующий синтаксис:

Как видите, у неё есть 5 аргументов:

1) » Ссылка «. Обязательный аргумент. Указывает стартовую ячейку, от которой будет происходить смещение. Здесь можно указать ссылку на ячейку или на диапазон ячеек. Во втором случае смещение будет считаться от левой верхней ячейки диапазона;

Читать еще:  Getpivotdata в excel

2) » Смещ_по_строкам «. Обязательный аргумент. Указывает, на сколько строк нужно сместиться от заданной ячейки. Положительные числа означают смещение вниз, отрицательные — вверх, ноль — без смещения;

3) » Смещ_по_столбцам «. Обязательный аргумент. Указывает, на сколько столбцов нужно сместиться от заданной ячейки. Положительные числа означают смещение вправо, отрицательные — влево, ноль — без смещения;

4) » Высота» . Необязательный аргумент. Высота в строках возвращаемого диапазона. Если опустить — высота будет равна высоте диапазона, указанного в аргументе «Ссылка»;

5) » Ширина» . Необязательный аргумент. Ширина в строках возвращаемого диапазона. Если опустить — ширина будет равна ширине диапазона, указанного в аргументе «Ссылка».

На рисунке ниже представлен алгоритм работы.

1) В качестве «Ссылки» задана ячейка B2.

2) От нее происходит смещение на 2 строки вниз (так как число 2 положительное). Мы оказываемся в ячейке B4.

3) Из ячейки B4 происходит смещение на 3 столбца вправо (так как число 3 положительное). В итоге мы попадаем в ячейку E4.

4) Принимая ее за верхнюю левую, выделяем диапазон из 3 строк и 4 столбцов. Результат — диапазон E4:H6.

Если смысл работы всё еще не ясен, можете попробовать «поиграть» с аргументами функции на листе «Алгоритм работы» в файле-примере, ссылка на который будет в конце статьи.

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

Если Вы сейчас подумали, что это всё какая-то ерунда, ведь ссылку на диапазон можно задать и вручную, без сложных функций, то Вы мыслите недостаточно гибко. Вся прелесть СМЕЩ в том, что аргументы можно задавать не строгими числами, а ссылками на ячейки и функциями, что делает возвращаемый диапазон динамическим ! Это будет хорошо видно в примерах использования.

Особенности функции

Функция СМЕЩ имеет пару особенностей, о которых стоит знать:

  • если итоговый диапазон находится за пределами рабочего листа, то функция вернет ошибку #ССЫЛКА;
  • функция является волатильной (летучей), то есть пересчитывается каждый раз при изменении рабочего листа. Это значит, что в некоторых случаях наличие в книге данной функции может «тормозить» работу файла

Перейдем к самому интересному — примерам работы.

1) Зависимый выпадающий список.

Сделать выпадающий список с названиями отделов компании. При выборе отдела должен появляться выпадающий список с сотрудниками этого отдела. При этом как отделы, так и сотрудники, могут добавляться в списки.

Организуйте данные на листе, так как показано на скриншоте ниже.

Обратите внимание на строку 3. В ячейку А3 введена формула =СЧЁТЗ(A5:A100). Она считает количество сотрудников отдела. Формулу нужно скопировать вправо на все отделы. Это количество пригодится нам в дальнейшем.

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

Внимательно запомните процесс создания именованного динамического диапазона. Во всех остальных примерах мы тоже будем их создавать, но пошаговое объяснение дано только тут.

Переходим по адресу: «Формулы» — «Диспетчер имен» — «Создать»

В поле » Имя » вводим «Отделы».

В поле «Область» оставляем «Книга» (чтобы имя действовало в пределах всего файла).

В поле » Диапазон » вводим формулу:

=СМЕЩ(‘Зависимый список’!$A$4;0;0;1;СЧЁТЗ(‘Зависимый список’!$4:$4))

В этой формуле в качестве стартовой указана ячейка первого отдела («Бухгалтерии»). Смещение по строкам и столбцам равно 0, так как нам никуда смещаться не нужно. Высота диапазона равна 1 строке, а вот ширина задается формулой СЧЁТЗ(‘Зависимый список’!$4:$4). Она считает все непустые ячейки в четвертой строке. Непустых у нас 4 — названия наших отделов. В итоге мы получим диапазон из 1 строки и 4 столбцов — список всех отделов. При добавлении нового отдела, СЧЁТЗ найдет уже 5 непустых ячеек и новый отдел попадет в список (учтите, что в 4 строке не должно быть ничего, кроме названий отделов, иначе СЧЁТЗ насчитает лишние ячейки).

Теперь на основе созданного диапазона «Отделы» создадим выпадающий список. Встаём в ячейку B1, кликаем » Данные » — » Проверка данных » — Тип данных: «Список» и вводим » =Отделы «.

Теперь у нас есть выпадающий список отделов. Перейдем к созданию списка для сотрудников. Создаем именованный диапазон «Сотрудники» с формулой:

=СМЕЩ(‘Зависимый список’!$A$4;1;ПОИСКПОЗ(‘Зависимый список’!$B$1;’Зависимый список’!$4:$4;0)-1;ИНДЕКС(‘Зависимый список’!$3:$3;ПОИСКПОЗ(‘Зависимый список’!$B$1;’Зависимый список’!$4:$4;0));1)

Первый аргумент — ячейка «Бухгалтерия».

Второй аргумент — 1. Так как сместиться нужно вниз на 1 строку, именно оттуда начинается список сотрудников.

Третий аргумент — смещение по столбцам. Функция ПОИСКПОЗ ищет номер столбца, в котором находится выбранный в ячейке B1 отдел.

Четвертый аргумент — функция ИНДЕКС возвращает количество сотрудников из ячейки над названием нужного отдела;

Пятый аргумент — 1, так как нам нужен столбец одного отдела.

В результате мы получили динамический диапазон со списком всех сотрудников отдела, указанного в ячейке B1. Теперь нужно в B2 добавить выпадающий список с формулой: =Сотрудники (как делали ранее для отдела) и наши списки готовы. Можно добавлять отделы и новых людей.

2) Добавление новых строк в сводную таблицу

Есть таблица с данными. На ее основе построена сводная таблица. Необходимо, чтобы при добавлении новых данных они попадали в сводную при следующем обновлении («умную таблицу» по ряду причин задействовать нельзя).

Создадим именованный диапазон «Источник», в который введем формулу:

=СМЕЩ(‘Диапазон сводной’!$A$1;0;0;СЧЁТЗ(‘Диапазон сводной’!$A:$A);СЧЁТЗ(‘Диапазон сводной’!$1:$1))

В этом случае функция стартует из ячейки «Дата». Никуда не смещаясь, она создает диапазон по следующему принципу:

  • число столбцов равно количеству непустых ячеек первой строки (то есть количеству заголовков в шапке), за подсчет которых отвечает формула СЧЁТЗ(‘Диапазон сводной’!$1:$1;
  • число строк равно количеству непустых ячеек первого столбца, за подсчет которых отвечает формула СЧЁТЗ(‘Диапазон сводной’!$A:$A).

Таким образом, если мы добавим в таблицу новый столбец или строку, эти данные попадут в динамический диапазон » Источник «. Осталось лишь указать его в качестве источника данных сводной таблицы.

Выделяем сводную — «Анализ» — «Источник данных» и в качестве диапазона указываем «=Источник».

Готово! Теперь, если в таблице появятся новые данные, они попадут в сводную при следующем обновлении.

3. Последняя неделя на графике

Есть таблица продаж товара. Нужно построить график, на котором всегда будут отображаться только последние 7 дней из таблицы.

По традиции, нам нужно создать именованный диапазон (например, » Последние7дней «, который будет забирать из таблицы только последние 7 дней. Формула в этот раз такая:

=СМЕЩ(‘Последняя неделя на графике’!$A$1;СЧЁТЗ(‘Последняя неделя на графике’!$A:$A)-7;1;7;1)

Благодаря формуле СЧЁТЗ(‘Последняя неделя на графике’!$A:$A)-7 мы всегда смещаемся на ту ячейку, с которой начинаются последние 7 дней.

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

=СМЕЩ(‘Последняя неделя на графике’!$A$1;СЧЁТЗ(‘Последняя неделя на графике’!$A:$A)-7;0;7;1)

Теперь исправим в созданной диаграмме абсолютные ссылки на именованный диапазон. Кликаем прямо на линию графика. В строке формул должна отобразиться функция РЯД(). Меняем в ней ссылки на именованные диапазоны, не стирая имя листа, как показано на рисунке ниже.

Когда закончим — жмем Enter. Вместо имени листа появится имя книги.

Теперь при добавлении новых строк будут показываться данные только за последнюю неделю.

4. Быстрый подсчет суммы в пределах периода

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

На этот раз обойдемся без именованных диапазонов.

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

Для начала нужно с помощью функции СМЕЩ создать диапазон на основе указанных месяцев «С» и «По». Формула будет такая:

Читать еще:  Функции впр в excel примеры видео

В данному случае функция ПОИСКПОЗ ищет ячейку, с которой будет начинаться диапазон (ячейка месяца «С»). Затем находится позиция второго месяца (месяц «По»). Из нее вычитается позиция первого и прибавляется единица, чтобы получить высоту диапазона (например, 5 (Май) — 2 (Февраль) + 1 = 4 (Февраль-Май)).

Осталось подсчитать сумму в найденному диапазоне. Для этого достаточно указать приведенную выше формулу в качестве аргумента функции СУММ.

Итак, в ячейке C4 будет формула:

5. Ссылка на каждую 5-ую ячейку

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

Последняя задача — самая простая из всех. В D1 нужно ввести формулу, протянув которую можно получить каждое пятое значение из списка (подкрашены зеленым). Здесь формула будет такая:

В качестве «якоря» указываем ячейку B1.

Для смещения по строкам используем формулу (СТРОКА()-1)*5. Когда мы введем ее в D2, то результат будет (2-1)*5 = 5. В следующей (третьей) строке результат будет (3-1)*5 = 10 и т.д. То есть при копировании функция СМЕЩ будет сдвигаться с шагом в 5 строк. Именно это нам и нужно.

Для лучшего понимания работы функции советуем скачать файл со всеми примерами вот по этой ссылке .

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Объекты MS Excel

Аргументы задают координаты интервала:

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

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

  • Если свойство Range применяется к объекту Range , то ссылка на интервал ячеек считается относительной и возвращается смещенный объект Range .

Например, если выделен интервал C1:D5, то запись Selection.Range(«B2») возвратит ячейку D2.

Свойство Cells

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

Синтаксис object. Cells (RowIndex,ColumnIndex)

  • object — ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
  • RowIndex — индекс строки;
  • ColumnIndex — индекс столбца.
  • В свойстве Cells индекс строки является первым аргументом, а индекс столбца — вторым аргументом, тогда как при задании адреса ячейки в стиле A1 сначала указывается столбец, а затем строка.
  • Понятие «индекс» ( Index, ColumnIndex, RowIndex ) всегда подразумевает целое число, целочисленную переменную или выражение, результат вычисления которого есть целое число или может быть преобразован в целое число.
Свойство Offset

Свойство Offset позволяет задавать ячейки или интервалы при помощи числа строк и колонок, которые отделяют нужную ячейку от исходной ячейки, т.е. указывая смещение относительно выбранной ячейки. Например, Range(«A5»).Offset(-2,1) возвращает ячейку B3.

  • object — ссылка на объект Range . Ссылка обязательна и определяет объект, относительно которого задается смещение;
  • RowOffset — смещение строки искомой ячейки относительно исходной ячейки;
  • ColumnOffset — смещение столбца искомой ячейки относительно исходной ячейки.

Необязательные аргументы RowOffset и ColumnOffset — числовые выражения. Если какой-то аргумент не задан, то соответствующее смещение равно нулю.

Например, если выделен интервал C1:D5, то запись Selection.Offset(2,1).Select выделяет интервал D3:E7.

Метод Union и свойство Areas

Метод Union используется для объединения двух и более объектов Range , заданных ссылками на непересекающиеся интервалы, в один объект Range .

Синтаксис Object. Union (arg1,arg2. )

  • object — всегда объект Application . Ссылка необязательна;
  • arg1,arg2 — интервалы ячеек. Количество аргументов произвольно. Обязательно наличие хотя бы двух аргументов.

Например, оператор Union (Range(«A1:C5»),Range(«B10:D12»)).Select выделяет несмежные интервалы A1:C5 и B10:D12.

Свойство Areas выполняет обратное действие, разделяя объединенные интервалы на несколько объектов Range .

  • object — ссылка на объект Range , состоящий из нескольких интервалов;
  • index — номер интервала в объекте. Аргумент необязателен.
Свойства Column и Row (R/O Integer)

Свойства возвращают целое число, показывающее индекс первого столбца или первой строки соответственно для заданного объекта. Синтаксис свойств

  • object — обязательная ссылка на объект Range .

Например, запись Range(«C5»).Column возвращает число 3, а запись Range(«C5»).Row возвращает число 5.

Свойства Columns и Rows

Свойство Columns (не путайте со свойством Column !) возвращает объект Range , представляющий колонку или коллекцию колонок в объекте, к которому это свойство было применено.

  • object — ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
  • index — индекс колонки в объекте.

Например, запись Columns(1) возвращает колонку A активного рабочего листа , а запись Range(«C1:D5»).Columns(1) возвращает колонку C заданного интервала, а именно, ячейки C1:C5.

  • Если не указан индекс колонки, то возвращаются все колонки объекта в виде объекта Range .
  • Индекс колонки можно указывать числом или буквой, при этом буква заключается в кавычки. Ссылки Columns(2) и Columns(«B») указывают на одну и ту же колонку B.

Свойство Rows (не путайте со свойством Row !) возвращает объект Range , представляющий строку или коллекцию строк в объекте, к которому это свойство было применено.

  • object — ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист ;
  • index — индекс строки в объекте.
  • Если не указан номер строки, то возвращаются все строки объекта в виде объекта Range .

Например, оператор nr=Selection.Rows(Selection.Rows.Count).Row позволяет получить номер последней строки в выделенном интервале ячеек.

Свойство CurrentRegion

Свойство CurrentRegion определяет объект Range , который соответствует интервалу ячеек, включающему заданную ячейку.

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

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

30 функций Excel за 30 дней: СМЕЩ (OFFSET)

Вчера в марафоне 30 функций Excel за 30 дней мы заменяли текстовые строки с помощью функции REPLACE (ЗАМЕНИТЬ) и выяснили, что вставлять символы она тоже умеет.

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

Итак, давайте изучим информацию и примеры применения функции OFFSET (СМЕЩ), а если у Вас есть дополнительные сведения и свои примеры, пожалуйста, делитесь ими в комментариях.

Функция 26: OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) возвращает ссылку, смещённую от заданной ссылки на определенное количество строк и столбцов.

Как можно использовать функцию OFFSET (СМЕЩ)?

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

  • Находить суммы продаж для выбранного месяца.
  • Суммировать данные о продажах за выбранные месяцы.
  • Создавать динамические диапазоны на основе подсчета.
  • Суммировать продажи за последние n месяцев.

Синтаксис OFFSET (СМЕЩ)

Функция OFFSET (СМЕЩ) имеет вот такой синтаксис:

OFFSET(reference,rows,cols,[height],[width])
СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

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

Ловушки OFFSET (СМЕЩ)

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

Читать еще:  Doevents vba excel

Пример 1: Находим сумму продаж для выбранного месяца

При помощи функции OFFSET (СМЕЩ) Вы можете возвращать ссылку на диапазон, отталкиваясь от стартовой ссылки. В этом примере мы хотим получить сумму продаж в ячейке G2.

  • Стартовая ссылка – это ячейка C1.
  • Количество строк для сдвига вводится в ячейке F2.
  • Суммы продаж находятся в столбце C, поэтому сдвиг по столбцам равен нулю.
  • Аргумент height (высота) равен 1 строке.
  • Аргумент width (ширина) равен 1 столбцу.

В ячейке H2 находится формула, очень похожая на предыдущую, которая возвращает имя месяца. Единственное отличие – это сдвиг по столбцам: 1 вместо 0.

=OFFSET(C1,F2, 1 ,1,1)
=СМЕЩ(C1;F2;1; 1 ;1)

Замечание: В данном примере аргументы height (высота) и width (ширина) можно не указывать, поскольку нам нужна ссылка того же размера, что и стартовая.

Пример 2: Суммируем продажи за выбранные месяцы

В этом примере функция OFFSET (СМЕЩ) возвращает ссылку на данные о продажах за выбранный месяц, а функция SUM (СУММ) подсчитывает сумму для этого диапазона. В ячейке B10 указан номер выбранного месяца 3, следовательно, результатом будет сумма продаж в марте (Mar).

  • Начальная ссылка A3:A6.
  • Сдвиг по строкам равен нулю (если ноль вообще не указать, результат будет тот же).
  • Сдвиг по столбцам соответствует значению ячейки B10.
  • Высота и ширина не указаны, поскольку итоговый диапазон должен быть того же размера, что и начальный.

Пример 3: Создаем динамический диапазон, основанный на подсчете

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

Если к списку в столбце C добавить ещё один месяц, он автоматически появится в выпадающем списке в ячейке F2, который использует имя MonthList, как источник данных.

Пример 4: Суммируем продажи за последние n месяцев

В этом заключительном примере OFFSET (СМЕЩ) работает вместе с SUM (СУММ) и COUNT (СЧЁТ), чтобы показать сумму за последние n месяцев. Как только добавляются новые значения, результат формулы будет автоматически скорректирован, чтобы включить величину продаж за последний месяц. В ячейке E2 количество месяцев равно 2, поэтому складываться будут суммы за август (Aug-10) и сентябрь (Sep-10).

  • Начальная ссылка – ячейка C2.
  • Количество строк для сдвига рассчитывается так:
    • подсчитываем количество чисел в столбце C,
    • вычитаем из результата количество месяцев, указанное в ячейке E3,
    • и прибавляем 1.
  • Продажи находятся в столбце C, поэтому сдвиг по столбцам равен нулю.
  • Высота введена в ячейке E3.
  • Ширина равна 1 столбцу.

VBA OFFSET

Excel VBA OFFSET Function

VBA Offset function is used to move or refer to a reference skipping a particular number of rows and columns, the arguments for this function in VBA is same as to the arguments in worksheet.

For example assume you have a set of data like the below one.

Now from cell A1, you want to move down 4 cells and you want to select that 5 th cell i.e. A5 cell.

Similarly, if you want to move down from A1 cell 2 rows and go 2 columns to the right and select that cell i.e. C2 cell.

In these cases, the OFFSET function is very helpful. Especially in VBA OFFSET Function is just phenomenal.

OFFSET is Used with Range Object in Excel VBA

In VBA we cannot directly enter the word OFFSET. We need to use the VBA RANGE object first and from that range object, we can use OFFSET property.

In excel range is nothing but a cell or range of the cell. Since OFFSET refers cells we need to use the object RANGE first and then we can use the OFFSET method.

Syntax of OFFSET in VBA Excel

  • Row Offset: How many rows you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).
  • Column Offset: How many columns you want to offset from the selected cell. Here selected cell is A1 i.e. Range (“A1”).

Examples

Example #1

Consider the below data for example demonstration.

Now I want to select the cell A6 from the cell A1. Start the macro and reference cell using the Range object.

Code:

Now I want to select the cell A6 i.e. I want to do down 5 cells. So enter 5 as the parameter for Row Offset.

Code:

Since I am selecting in the same column I leave out the column part. Close the bracket and put a dot (.) and type the method “Select”.

Code:

Now run this code using the F5 key or you can run manually to select the cell A6 as shown below.

Output:

Example #2

Now take the same data but here will see how to use column offset argument as well. Now I want to select the cell C5.

Since I want to select the cell C5 firstly I want to move down 4 cells and take the right 2 columns to reach the cell C5. The Below code would do the job for me.

Code:

I run this code manually or using the F5 key then, it will select the cell C5 as shown in the below screenshot.

Output:

Example #3

We have seen how to offset rows and columns. We can also select the above cells from the specified cells as well. For example, if you are in the cell A10 and you want to select A1 cell, how do you select?

In case of moving down the cell, we can enter a positive number, so here in case of moving up, we need to enter negative numbers.

From A9 cell we need to move up by 8 rows i.e. -8.

Code:

If you run this code using the F5 key or you can manually run this code then, it will select the cell A1 from A9 cell.

Output:

Example #4

Assume you are in the cell C8. From this cell, you want to select the cell A10.

From the active cell i.e. C8 cell we need to first move down 2 rows and we need to move to left by 2 columns to select the cell A10.

In case of moving left to select the column, we need to specify the number is negative. So here we need to come back by -2 columns.

Code:

Now run this code using F5 key or run manually, it will select A10 cell as shown below:

Output:

Things to Remember

  • In case of moving up of rows, we need to specify the number in negatives.
  • In case of moving left to select the column, the number should be negative.
  • A1 cell is the first row and first column.
  • Active Cell means presently selected cells.
  • If you want to select the cell using OFFSET you need mention “.Select”.
  • If you want to copy the cell using OFFSET you need mention “.Copy”.

Recommended Articles

This has been a guide to VBA OFFSET. Here we learn how to use VBA OFFSET Property to navigate in Excel along with practical examples and a downloadable template. Below are some useful excel articles related to VBA –

Ссылка на основную публикацию
Adblock
detector