Excel vba пересечение диапазонов
Представляет ячейку, строку, столбец или группу ячеек, содержащую один или несколько смежных блоков ячеек или объемный диапазон.
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Примечания
Элемент по умолчанию объекта Range направляет вызовы без параметров в свойство Value, а вызовы с параметрами — в элемент Item. Таким образом, someRange = someOtherRange соответствует someRange.Value = someOtherRange.Value , someRange(1) соответствует someRange.Item(1) и someRange(1,1) соответствует someRange.Item(1,1) .
В разделе Пример описаны следующие свойства и методы для возврата объекта Range:
Пример
Чтобы вернуть объект Range, представляющий одну ячейку или диапазон ячеек, используйте синтаксис Range ( arg ), где arg обозначает диапазон. В следующем примере значение ячейки A1 помещается в ячейку A5.
В следующем примере диапазон A1:H8 заполняется случайными числами путем задания формулы для каждой ячейки в диапазоне. При использовании без квалификатора объекта (объекта слева от точки) свойство Range возвращает диапазон на активном листе. Если активное окно не является листом, метод завершается с ошибкой.
Используйте метод Activate объекта Worksheet, чтобы активировать лист перед использованием свойства Range без явного квалификатора объекта.
В следующем примере очищается содержимое диапазона Criteria.
Если используется текстовый аргумент для адреса диапазона, необходимо указать адрес в нотации стиля A1 (нельзя использовать нотацию в стиле R1C1).
Чтобы получить диапазон, содержащий все отдельные ячейки листа, используйте свойство Cells на листе. Вы можете обращаться к отдельным ячейкам, используя синтаксис Item(строка, столбец), где строка — индекс строки, а столбец — индекс столбца. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги ячейке A1 присваивается значение 24, а в ячейке B1 — значение 42.
В следующем примере задается формула для ячейки A2.
Хотя также можно использовать Range("A1") , чтобы вернуть значение ячейки A1, иногда свойство Cells может быть удобнее, так как позволяет использовать переменную для строки или столбца. В следующем примере создаются заголовки столбцов и строк на листе Sheet1. Обратите внимание, что после активации листа можно использовать свойство Cells без явного объявления листа (оно возвращает ячейку на активном листе).
Хотя для изменения ссылок в стиле A1 можно использовать строковые функции Visual Basic, проще (и лучше при программировании) использовать нотацию Cells(1, 1) .
Используйте синтаксис_выражение_.Cells, где выражение возвращает объект Range, чтобы получить диапазон с тем же адресом, состоящий из отдельных ячеек. В таком диапазоне отдельные ячейки доступны с помощью синтаксиса Item(строка, столбец) относительно левого верхнего угла первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range. В следующем примере на первом листе активной книги в ячейках C5 и D5 указывается формула.
Чтобы вернуть объект Range, используйте синтаксис Range ( ячейка1, ячейка2 ), где ячейка1 и ячейка2 — это объекты Range, указывающие начальную и конечную ячейки. В следующем примере устанавливается тип линии границы для ячеек A1:J10.
Имейте в виду, что точка перед каждым появлением свойства Cells является обязательной, если результат предыдущего оператора With нужно применять к свойству Cells. В данном случае указано, что ячейки расположены на листе один (без точки свойство Cells будет возвращать ячейки активного листа).
Чтобы получить диапазон, содержащий все строки листа, используйте свойство Rows на листе. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это индекс строки. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются строки 4 и 10 первого листа активной книги.
Чтобы получить диапазон, содержащий все столбцы листа, используйте свойство Columns на листе. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это индекс столбца в виде числа или адреса столбца в формате А1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются столбцы B, C, E и J первого листа активной книги.
Используйте синтаксис_выражение_.Rows, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из строк первой области диапазона. Вы можете обращаться к отдельным строкам с помощью синтаксиса Item(строка), где строка — это относительный индекс строки от верхнего края первой области диапазона. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из строк. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются диапазоны C8:D8 и C6:D6 первого листа активной книги.
Используйте синтаксис_выражение_.Columns, где выражение возвращает объект Range, чтобы получить диапазон, состоящий из столбцов первой области диапазона. Вы можете обращаться к отдельным столбцам с помощью синтаксиса Item(строка) [sic], где строка — это относительный индекс столбца от левого края первой области диапазона, указанный в виде числа или адреса столбца в формате A1. Свойство Item можно пропустить, так как вызов направляется к нему с помощью элемента по умолчанию объекта Range.
Недопустимо указывать второй параметр свойства Item для диапазонов, состоящих из столбцов. Сначала нужно преобразовать их в отдельные ячейки, используя свойство Cells.
В следующем примере удаляются диапазоны L2:L10, G2:G10, F2:F10 и D2:D10 первого листа активной книги.
Чтобы вернуть диапазон с указанным смещением относительно другого диапазона, используйте синтаксис Offset ( строка, столбец ), где строка и столбец — это смещения строк и столбцов. В следующем примере выделяются ячейки, расположенные на три строки вниз и на один столбец вправо от ячейки в левом верхнем углу текущего выделенного фрагмента. Нельзя выбрать ячейку, которая находится не на активном листе, поэтому сначала необходимо активировать лист.
Используйте синтаксис Union ( диапазон1, диапазон2, . ) для возврата диапазонов из нескольких областей, то есть диапазонов, состоящих из двух или более смежных блоков ячеек. В следующем примере создается объект, определенный как объединение диапазонов A1:B2 и C3:D4, а затем выбирается определенный диапазон.
При работе с выделенными фрагментами, содержащими несколько областей, удобно применять свойство Areas. Оно разделяет выделенный фрагмент с несколькими областями на отдельные объекты Range, а затем возвращает объекты в виде коллекции. Вы можете использовать свойство Count в возвращенной коллекции, чтобы убедиться, что выделение содержит более одной области, как показано в следующем примере.
В этом примере используется метод AdvancedFilter объекта Range для создания списка уникальных значений, а также количества появлений этих уникальных значений в диапазоне столбца A.
Методы
Свойства
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:
Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.
Присваивается переменной диапазон ячеек с помощью оператора Set:
В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.
Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.
Адресация ячеек в диапазоне
К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.
Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 |
Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:
VBA Пересечение в математике или в геометрии означает, когда две или более линий или области пересекаются друг с другом. Общая точка или область, созданная после этого, называется точкой пересечения или областью. В Excel также мы можем выделить и измерить площадь пересечения.
Синтаксис функции пересечения в Excel VBA
Функция пересечения имеет следующий синтаксис в Excel VBA:
Как мы видим, Arg1 и Arg2 упоминаются, Range. А остальные аргументы в скобках. Это означает, что первые два аргумента должны быть выбраны как Range. Или, другими словами, минимум 2 области должны быть включены для нахождения пересечения. Остальные аргументы могут быть выбраны как Range или могут включать в себя некоторые другие вещи или параметры, а также по мере необходимости. Этот синтаксис может содержать максимум 30 аргументов.
Как использовать функцию пересечения Excel VBA?
Мы научимся использовать функцию пересечения VBA с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA Intersect Excel здесь - Шаблон VBA Intersect Excel
VBA Intersect - Пример № 1
В первом примере мы выделим и создадим область пересечения, когда у нас будет некоторый набор данных. Для этого у нас есть примеры данных, которые имеют 3 столбца, заполненных числами, как показано ниже.
Теперь нам нужно найти область пересечения вышеуказанной таблицы данных, используя VBA Intersect. Для этого выполните следующие шаги:
Шаг 1. Откройте окно VBA и откройте модуль из меню «Вставка», как показано ниже.
Мы получим пустое окно модуля.
Шаг 2: Теперь напишите Подкатегория VBA Intersect или под любым другим именем по вашему выбору.
Код:
Шаг 3: Теперь непосредственно вставьте команду Пересечь, как показано ниже.
Код:
Как мы уже объясняли подробный синтаксис Intersect, мы добавим область пересечения. Мы можем выбрать N диапазонов, но должно быть минимум два диапазона.
Рассмотрим ниже область пересечения, где первая область от А1 до В8, вторая область от В3 до С12 и третья область от А7 до С10. Мы можем рассмотреть и выбрать любую комбинацию шаблона пересечений.
Теперь давайте посмотрим, в какой точке эти области встречаются и пересекаются друг с другом. Общая область, созданная всеми вышеупомянутыми областями, будет нашей областью пересечения.
Шаг 4: Теперь в модуле пересечения VBA выберите первый диапазон области, как показано ниже.
Код:
Мы добавили первый диапазон, но наш синтаксис все еще не завершен.
Шаг 5: Теперь дополнительно вставьте остальные две области, которые мы обсуждали выше, через запятую.
Код:
Шаг 6: Теперь задайте условие как «True».
Код:
Это завершает наш код.
Шаг 7: Теперь скомпилируйте код и запустите, нажав на кнопку Play, которая находится под строкой меню, как показано ниже.
Мы получим общую область или область пересечения, которая имеет значение ИСТИНА, как показано выше. Хотя мы получили область пересечения, эта ИСТИНА заменила данные, которые были в этой области.
Шаг 8: Теперь, чтобы не потерять это, мы можем изменить цвет фона, эти общие ячейки на любой цвет по нашему выбору. Для этого после синтаксиса Intersect используйте функцию Interior вместе с Color, как показано ниже.
Код:
Шаг 9: Теперь в VBA мы не можем использовать название цвета, который мы хотим использовать напрямую. Для этого нам нужно добавить « vb », который используется для активации цветов, доступных в VBA. Теперь используйте его и добавьте любое название цвета по вашему выбору. Мы выбираем зеленый здесь, как показано ниже.
Код:
Шаг 10: Теперь снова скомпилируйте написанный код за один раз, так как код довольно маленький, и запустите его.
Мы увидим, что цвет пересекаемой области меняется на Зеленый и общая область, которая создается пересечением трех разных областей от B7 до B8.
VBA Intersect - пример № 2
Существует другой, но совершенно другой способ использования VBA Intersect. На этот раз мы используем пересечение в конкретном листе. На Листе 2 мы отметили область от B4 до E8, как показано ниже.
Выполните следующие шаги:
Шаг 1: В VBA перейдите к Листу 2 текущей Рабочей книги, как показано ниже.
Шаг 2: Теперь выберите « Рабочий лист» в первом раскрывающемся списке. Это позволит использовать код только в этом текущем листе.
Шаг 3: И во втором раскрывающемся списке выберите параметр « Изменить», как показано ниже. Это используется для определения изменений, сделанных в выбранном диапазоне.
Шаг 4: Мы напишем наш код только в первой подкатегории.
Код:
Шаг 5: Мы будем использовать цикл If-Else для формирования условия для функции пересечения.
Код:
Шаг 6: Сначала выберите целевой диапазон от B4 до E8, как показано ниже. Это будет нацелено на пересечение области, покрытой от B4 до E8 в основном.
Код:
Шаг 7: И если в целевой области ничего нет, нам нужно написать оператор, который будет перенаправлять код вперед.
Код:
Код:
Код:
Шаг 10: Теперь скомпилируйте каждый шаг написанного кода и закройте рабочий лист VBA. Поскольку мы написали код, специфичный для листа, он будет работать так же.
Шаг 11: Теперь напишите что-нибудь внутри коробки.
Это еще один способ использования Intersect в Excel VBA.
Плюсы Excel VBA Intersect
- Очень легко по крайней мере выделить область, которая пересекается в процессе примера 1.
- Это очень полезно, когда нам нужно отфильтровать или обработать данные такого типа, которые пересекаются с другой областью, такой как даты, владелец и т. Д.
То, что нужно запомнить
- Не забудьте сохранить файл в формате Macro Enable Excel, чтобы код работал при каждом использовании.
- Записав код на листе вместо модуля, как показано в примере 2, сделайте код применимым только для этого листа. Этот код не будет работать на любом другом листе.
- Использование Target Range, как показано в примере 2, полезно при указании области для удара.
Рекомендуемые статьи
Это руководство по VBA Intersect. Здесь мы обсуждаем, как использовать функцию пересечения Excel VBA вместе с некоторыми практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
В Excel мы узнали, как назвать диапазон и использовать его. Теперь мы узнаем, как использовать этот диапазон имен в VBA, и в дополнение к этому мы также узнаем, как назвать диапазон из VBA. Диапазон является важной частью рабочей таблицы, поскольку он содержит данные для нас. Конкретное присвоение имен диапазонам помогает нам определить, какая часть диапазона содержит какие данные.
Давайте начнем с того, как мы можем назвать диапазон в Excel, а затем использовать его в VBA. Чтобы назвать группу диапазонов в Excel, мы просто выбираем диапазоны следующим образом:
Теперь есть поле, где оно упоминается как A1, мы нажимаем на него и даем ему имя.
Коробка называется коробкой имени, и мы используем ее, чтобы дать имя нашему диапазону. Для текущего примера я дал его в качестве именованного диапазона. Теперь, когда мы дали имя диапазона, как мы используем его в VBA. Предположим, что мы должны были изменить цвет интерьера вышеупомянутого диапазона ранее в VBA, что мы сделали в VBA, так как мы ссылались на диапазон следующим образом:
Теперь, когда мы дали диапазону имя, мы можем сделать следующее, чтобы изменить цвет диапазона следующим образом:
NEW - это имя диапазона, которое мы дали для выбора ячейки A1: B5. Вот как мы используем диапазоны имен в VBA, как только мы назвали их.
Теперь мы можем также назвать диапазоны, используя VBA, слишком хорошо, это немного более длительный процесс, чем выше, но давайте пройдемся по нему.
Чтобы присвоить VBA диапазон в рабочей таблице, мы используем следующий код:
Как использовать именованный диапазон в Excel VBA?
Существует два метода использования именованного диапазона в Excel VBA:
- Во-первых, мы называем диапазон в Excel, а затем используем его в VBA.
- Второй метод заключается в создании именованного диапазона в самом VBA и использовании его свойств.
Ниже приведены различные примеры использования именованного диапазона в Excel:
Вы можете скачать этот шаблон Excel с именованным диапазоном VBA здесь - Шаблон Excel с именованным диапазоном VBA
Пример № 1
Для первого примера давайте используем диапазон имен, который мы использовали выше, превратив его в таблицу Excel. Выполните следующие шаги:
Шаг 1: Выберите диапазон, который мы хотим назвать первым, следующим образом:
Шаг 2: Над диапазоном мы используем поле для имени, чтобы присвоить имя диапазону, в этом примере я назвал диапазон как Новый .
Шаг 3. Теперь давайте перейдем к VBA, перейдите на вкладку « Разработчик » и нажмите Visual Basic, чтобы войти в VBA.
Шаг 4: Как только мы попадаем в VBA, нам нужно вставить модуль, чтобы мы могли писать в нем код. Сделайте следующее
Шаг 5: Теперь давайте начнем писать код, для этого нам нужно сначала назвать макрос следующим образом:
Код:
Шаг 6: Напишите следующий код, чтобы сначала активировать лист, чтобы использовать диапазон имен.
Код:
Шаг 7: Теперь давайте используем наш диапазон имен следующим образом:
Код:
Шаг 8: Как только мы запустим приведенный выше код, мы увидим, что каждая ячейка в нашем диапазоне имен имеет такое же значение, как показано ниже:
Шаг 9: Давайте также проверим, можем ли мы изменить свойства ячейки. Давайте изменим цвет диапазона, используя диапазон имен следующим образом:
Код:
Шаг 10: Запустите приведенный выше код, нажав F5 или кнопку Run, и увидите следующий результат,
Пример № 2
Сначала мы создадим именованный диапазон с помощью VBA и используем свойства диапазона имен в самом VBA. Выполните следующие шаги:
Шаг 1: Теперь давайте начнем писать код, для этого нам нужно сначала назвать макрос следующим образом:
Код:
Шаг 2: Объявите переменную, которая будет хранить имя диапазона имен следующим образом,
Код:
Шаг 3: Теперь давайте установим диапазон имен следующим образом, в этом примере все, что мы выбрали в качестве диапазона, будет нашим именованным диапазоном,
Код:
Это то, что я выбрал для именованного диапазона ячеек диапазона A1: C5 на листе 2,
Шаг 4: Теперь давайте назовем диапазон следующим кодом:
Код:
Шаг 5: Запустите приведенный выше код, мы увидим, что мы создали диапазон имен на листе 2 с именем namedrangefromselection,
Шаг 6: Теперь давайте воспользуемся этим именем в диапазоне, как мы использовали в предыдущем примере следующим образом:
Код:
Шаг 7: Запустите приведенный выше код с кнопки F5 и увидите результат следующим образом:
То, что нужно запомнить
- Именованные диапазоны - это имя, данное группе или диапазону ячеек.
- Именованный диапазон используется для обозначения определенного диапазона ячеек, к которому необходимо обращаться позже.
- Мы можем назвать диапазон в Excel или VBA.
- При создании именованного диапазона в VBA нам нужно использовать функцию добавления имени в VBA.
Рекомендуемые статьи
Это руководство по VBA Named Range. Здесь мы обсудим, как использовать Named Range в Excel VBA, а также с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: