Диапазон ячеек в excel: Выделение диапазона ячеек — Служба поддержки Майкрософт

VBA Excel. Выделенный диапазон ячеек (адрес, выбор, строки)

Определение адреса выделенного диапазона ячеек на листе Excel с помощью кода VBA. Определение номера первой и последней строки. Программное выделение диапазона.

1. Адрес выделенного диапазона

2. Выделение ячеек и диапазонов

3. Определение номеров первой и последней строки

Адрес выделенного диапазона

Для определения адреса выделенного диапазона ячеек в VBA Excel используется свойство Address объекта Selection.

Объект Selection — это совокупность всех выделенных ячеек на листе Excel. Это может быть одна ячейка, смежный или несмежный диапазон ячеек, представляющий коллекцию смежных диапазонов. Если выделение состоит из несмежного диапазона, адреса смежных диапазонов, из которых он состоит, будут перечислены через запятую.

Смежный диапазон

— прямоугольная область смежных (прилегающих друг к другу) ячеек.

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

Стоит отметить: несмотря на то, что в выделенном диапазоне может содержаться много ячеек, активной может быть только одна. Она представлена объектом ActiveCell. Для определения ее адреса в коде VBA Excel также используется свойство Address.

1 2 3 4 5 6

Sub Primer1() MsgBox «Адрес выделенного диапазона: » & Selection.Address & _ vbNewLine & «Адрес активной ячейки: » & ActiveCell.Address & _ vbNewLine & «Номер строки активной ячейки: » & ActiveCell.Row & _ vbNewLine & «Номер столбца активной ячейки: » & ActiveCell.Column End Sub

Скопируйте и запустите код на выполнение.

В результате получите что-то вроде этого, зависящее от того, какие диапазоны вы выберите:

Определение адресов выделенного диапазона и активной ячейки

Выделение ячеек и диапазонов

Выделить несмежный диапазон ячеек можно следующим образом:

Sub Primer2() Range(«B4:C7,E5:F7,D8»).Select End Sub

Как видно из примера, в адресной строке объекта Range перечисляются адреса смежных диапазонов, составляющих общий несмежный диапазон, через запятую. Выделение осуществляется методом Select объекта Range.

Определение номеров первой и последней строки

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

1 2 3 4 5 6 7

Sub Primer3() Dim i1 As Long, i2 As Long i1 = Selection. Cells(1).Row i2 = Selection.Cells(Selection.Cells.Count).Row MsgBox «Первая строка: » & i1 & _ vbNewLine & «Последняя строка: » & i2 End Sub

Результат будет таким, зависит от выделенного диапазона:

Номера первой и последней строки выделенного смежного диапазона

Таким же образом можно вычислить номера первого и последнего столбцов выделенного диапазона, которые можно использовать для обработки информации по столбцам.

Обратите внимание, что для несмежных диапазонов этот пример не работает.

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

---

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Возможности Excel. Часть 2

Возможности Excel. Часть 2

Мария Антонова, специалист по обучению

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

«Мастер функций», меню «Вставка»/«Функции».

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

Одним из аргументов большинства функций является так называемая «ссылка на ячейку» («адрес ячейки») вида «D4» (буква обозначает столбец, цифра – номер строки, на пересечении которых находится ячейка) или диапазон ячеек вида «A6:E23» (A6 – верхняя левая ячейка, E23 – нижняя правая, ограничивающие диапазон).

Большой популярностью пользуется функция

СУММ(), суммирующая числа в заданном диапазоне ячеек.

Кроме ссылок на ячейки аргументами СУММ() могут быть числа или другие функции.

Функция ПРОИЗВЕД() перемножит числа в ячейках из указанного диапазона.

Посчитать количество ячеек, содержащих числа, в т.ч. даты и время, можно при помощи функции СЧЕТ().

СЧЕТЗ() – посчитает количество заполненных ячеек из заданного диапазона с любыми данными.

Функция СЧИТАТЬПУСТОТЫ() – наоборот, посчитает количество пустых ячеек.

Если же мы хотим посчитать не все ячейки в столбце, а только те, которые отвечают определенному условию, воспользуемся функцией СЧЕТЕСЛИ(диапазон;критерий).

Аналогично работает функция СУММЕСЛИ(), которая суммирует числа, содержащиеся в ячейках, отвечающих условию.

Определят максимальное и минимальное значения в диапазоне ячеек функции МАКС()

и МИН().

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

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

ОКРУГЛ(округляемое число;число разрядов) – округляет числа.

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

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

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

Среднее значение можно вычислить с помощью функции СРЗНАЧ(). Пустые ячейки она игнорирует.

Еще одна интересная функция, оперирующая числами –

МЕДИАНА().

Эта функция вычисляет медиану диапазона чисел, т.е. такое число из заданного набора, значение которого делит набор на две равные по количеству чисел группы – те, что больше и те, что меньше медианы.

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

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

Читайте продолжение в следующей статье.

Читайте также:

Программа для развивающего центра

Развитие учебного центра

Приложение для курсов

Программа для школ музыки

Система для танцев

Система для семинаров

Программа для курсов

Система для школы развития

Диапазон Excel VBA — Работа с диапазоном и ячейками в VBA

Введение в диапазон и ячейки в VBA

Свойство диапазона

Свойство ячеек

Свойство смещения

осмотритесь в рабочей книге Excel, вы обнаружите, что все работает вокруг ячеек. Ячейка и диапазон ячеек — это место, где вы храните свои данные, а затем все начинается.

Чтобы максимально эффективно использовать VBA, вам нужно научиться использовать ячейки и диапазоны в своих кодах. Для этого вам нужно иметь четкое представление об объектах Range. Используя его, вы можете обращаться к ячейкам в своих кодах следующими способами:

• Одна ячейка.
• Диапазон ячеек.
• Строка или столбец. Таким образом, если вы пишете код для ссылки на объект RANGE, он будет выглядеть так:

   Application.Workbook("Workbook-Name").Worksheets("Sheet-Name").Range 

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

  • Вы можете прочитать значение из него.
  • В него можно ввести значение.
  • И вы можете вносить изменения в формат.

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

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

  • Свойство диапазона
  • Свойство ячеек
  • Offset Property

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

  Итак, приступим.

  Свойство диапазона

  Свойство диапазона — наиболее распространенный и популярный способ ссылки на диапазон в ваших кодах VBA. Со свойством Range вам просто нужно обратиться к адресу ячейки. Позвольте мне рассказать вам синтаксис.

   выражение.диапазон(адрес) 

  Здесь выражение представляет собой переменную, представляющую объект VBA. Поэтому, если вам нужно обратиться к ячейке A1, вам нужно будет написать строку кода:

   Application.Workbook("Book1").Worksheets("Sheet1").Range("A1") 

  Вышеупомянутое код сообщает VBA, что вы имеете в виду ячейку A1, которая находится на листе «Лист1» и книге «Книга1».

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

  Но вот что нужно понять. Поскольку вы используете VBA в Excel, нет необходимости использовать слово «Приложение». Таким образом, код будет таким:

   Рабочая книга («Книга1»). Рабочие листы («Лист1»). «Лист1»). Диапазон («А1») 

  Но, если вы уже находитесь на рабочем листе «Лист1», вы можете дополнительно сократить свой код и использовать только:

   Диапазон («А1») 

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

   Range("A1:A5") 

  В приведенном выше коде вы ссылались на диапазон от A1 до A5, который состоит из пяти ячеек. Вы также можете сослаться на именованный диапазон, используя объект диапазона. Допустим, вы назвали диапазон с именем «Скидка на продажу», чтобы сослаться на это, вы можете написать такой код:

    Диапазон («Скидка на продажу») 

  нужно сделать что-то вроде этого:

   Range("A1:B5,D5:G10") 

  И если вы хотите сослаться на всю строку или столбец, вам нужно ввести код, как показано ниже:

   Диапазон ("1:1")
  Range("A:A") 

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

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

  • Как установить (получить и изменить) значение ячейки с помощью кода VBA
  • Как выбрать Диапазон с использованием VBA в Excel
  • Как создать именованный диапазон с помощью VBA (статический + динамический) в Excel
  • Как объединить и разъединить ячейки в Excel с помощью кода VBA
  • Как проверить, пуста ли ячейка с помощью VBA в Excel
  • VBA ClearContents ( из ячейки, диапазона или всего листа)
  • Шрифт Excel VBA (цвет, размер, тип и жирность)
  • Автоподбор (строк, столбцов или всего листа) с помощью VBA
  • Как использовать свойство OFFSET с объект диапазона или ячейка в VBA
  • Перенос текста VBA (ячейка, диапазон и весь лист)
  • Как скопировать ячейку\диапазон на другой лист с помощью VBA
  • Как использовать диапазон/ячейку в качестве переменной в VBA в Excel
  • Как найти последние строки , столбец и ячейка с помощью VBA в Excel
  • Как использовать ActiveCell в VBA в Excel
  • Как обратиться к используемому диапазону с помощью VBA в Excel
  • Как изменить высоту строки/ширину столбца с помощью VBA в Excel
  • Как выбрать ВСЕ ячейки на рабочем листе с использованием кода VBA
  • Как вставить строку с помощью VBA в Excel
  • Как вставить столбец с помощью VBA в Excel

  1.

  Выберите и активируйте ячейку

  Если вы хотите выбрать ячейку, вы можете использовать диапазон. Выберите метод. Допустим, если вы хотите выбрать ячейку A5, все, что вам нужно сделать, это указать диапазон, а затем добавить после этого «.Select».

   Диапазон («A1»). Выберите 

  Этот код указывает VBA выбрать ячейку A5, и если вы хотите выбрать диапазон ячеек, вам просто нужно обратиться к этому диапазону и просто добавить «.Select» после этого.

   Диапазон («A1:A5»). Выберите 

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

   Диапазон("A1").Активировать 

  Здесь нужно помнить, что активировать можно только одну ячейку за раз. Даже если вы укажете диапазон с помощью метода «.Activate » , он выберет этот диапазон, но активной ячейкой будет первая ячейка диапазона.

  2. Введите значение в ячейку

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

   Range("A1").Value = "ExcelChamps" 

  В приведенном выше примере вы указали A1 в качестве диапазона, а затем добавили « .Value », что указывает VBA на доступ к свойству value клетки.

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

   Range("A1").Value = 9988 

  И если вы хотите ввести значение в диапазон ячеек, я имею в виду несколько ячеек, тогда все, что вам нужно сделать, это указать этот диапазон.

   Range("A1:A5").Value = "ExcelChamps" 

  И вот код, если вы имеете в виду непродолжительный диапазон.

   Range("A1:A5 , E2:E3").Value = "ExcelChamps" 

  3.

  Скопируйте и вставьте ячейку/диапазон

  С помощью свойства Range можно использовать метод «.Copy » для копирования и ячейки, а затем вставьте его в ячейку назначения. Скажем, если вам нужно скопировать ячейку A5, код для этого будет таким:

   Range("A5").Copy 

  Когда вы запустите этот код, он просто скопирует ячейку A5, но затем нужно вставить эту скопированную ячейку. в ячейку назначения. Для этого вам нужно добавить ключевое слово назначения после него, а затем ячейку, в которую вы хотите его вставить. Поэтому, если вы хотите скопировать ячейку A1, а затем вставить ее в ячейку E5, код будет таким:

   Range("A1"). Копировать Destination:=Range("E5") 

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

   Range("A1: A5"). Копировать место назначения: = Диапазон ("E5: E9") 

  Если вы скопировали диапазон ячеек, а затем, если вы упомянули одну ячейку в качестве диапазона назначения, VBA скопирует весь скопированный диапазон, начиная с ячейку, которую вы указали в качестве места назначения.

   Диапазон ("A1: A5"). Место назначения копирования: = Диапазон ("B1") 

  Когда вы запустите приведенный выше код, VBA скопирует диапазон A1: A5 и вставит его в B1: B5, даже если вы упомянули только B1 в качестве диапазона назначения.

  Совет: Точно так же, как метод «.Copy», вы можете использовать метод «.Cut», чтобы вырезать ячейку, а затем просто использовать место назначения, чтобы вставить ее.

  4.

  Использование свойства шрифта со свойством диапазона

  С помощью свойства диапазона вы можете получить доступ к свойству шрифта ячейки, которое поможет вам изменить все настройки шрифта. Всего у шрифта есть 18 различных свойств, к которым вы можете получить доступ. Допустим, если вы хотите сделать текст ЖИРНЫМ в ячейке A1, код будет таким:

   Range("A1").Font.Bold = True 

  Этот код указывает VBA получить доступ к свойству BOLD шрифта, который находится внутри диапазона A1, и вы установили для этого свойства значение TRUE. Теперь предположим, что вы хотите применить зачеркивание к ячейке A1, на этот раз код будет таким:

  . Как я уже сказал, вы можете использовать в общей сложности 18 различных свойств, поэтому не забудьте проверить их все, чтобы увидеть, какое из них полезно для вас.

  5.

  Удаление форматирования из ячейки

  Используя метод «.ClearFormats», вы можете удалить только формат из ячейки или диапазона ячеек. Все, что вам нужно сделать, это добавить «.ClearFormat» после указания диапазона, как показано ниже:

   Range("A1").ClearFormats 

  Когда вы запускаете приведенный выше код, он удаляет все форматирование из ячейки A1, и если вы хотите сделать это для всего диапазона, вы знаете, что делать, верно?

   Range("A1:A5").ClearFormats 

  Теперь приведенный выше код просто удалит форматирование из диапазона от A1 до A5.

  Свойство Cells

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

   выражение.Cells(Row_Number, Column_Number) 

  Здесь выражение представляет собой объект VBA, Row_Number — это номер строки ячейки, а Column_Number — это столбец ячейки. Итак, если вы хотите сослаться на ячейку A5, вы можете использовать код ниже:

   Cells(5,1) 

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

  Приведенный ниже код также будет относиться к ячейке A5:

   Ячейки (5, "A") 

  И для выбора VBA просто добавьте «.Select» в конце.

   Cells(5,1).Select 

  Приведенный выше код выберет ячейку A5, которая находится в 5-й строке и в первом столбце рабочего листа.

  Свойство OFFSET

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

  Предположим, что ваша активная ячейка — B5 прямо сейчас, и вы хотите перейти к ячейке, которая находится на 3 столбца справа и на 1 строку ниже от B5, вы можете сделать это OFFSET. Ниже приведен синтаксис, который вам нужно использовать для выражения OFFSET:

  . Смещение (RowOffset, ColumnOffset) 

  • RowOffset: В этом аргументе вам нужно указать число, которое сообщит VBA, сколько строк вы хотите ориентироваться. Положительное число определяет строку вниз, а отрицательное число определяет строку вверх.
  • ColumnOffset : В этом аргументе вам нужно указать число, которое сообщит VBA, сколько столбцов вы хотите перемещать. Положительное число определяет столбец справа, а отрицательное число определяет левый.

  Давайте для примера напишем простой код, который мы обсуждали выше.

  1. Во-первых, вам нужно определить диапазон, из которого вы хотите перемещаться, и введите следующий код:
     
  2. После этого введите «.Offset» и введите открывающие круглые скобки, как показано ниже:
     
  3. Далее вам нужно ввести номер строки, а затем номер столбца, по которому вы хотите перейти.
     
  4. В конце вам нужно добавить «.Select», чтобы указать VBA выбрать ячейку, в которую вы хотите перейти.
     

  Итак, когда вы запустите этот код, он выберет ячейку, которая находится на одну строку ниже и на 3 столбца справа от ячейки B5.

  Изменение размера диапазона с использованием СМЕЩЕНИЯ

  СМЕЩЕНИЕ позволяет не только перейти к ячейке, но и изменить размер диапазона. Продолжим приведенный выше пример.

   Range("B5").Offset(1, 3).Select 

  Приведенный выше код перемещает вас к ячейке E6, и теперь, допустим, вам нужно выбрать диапазон ячеек, состоящий из пяти столбцов и трех строк из Е6. Итак, что вам нужно сделать, это после использования OFFSET использовать свойство изменения размера, добавив «.Resize».

   Range("B5").Offset(1, 3).Resize 

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

   Range("B5").Offset(1, 3).Resize(3,5) 

  В конце добавьте «.Select», чтобы указать VBA выбрать диапазон, и когда вы запустите этот код, он выберите диапазон.

   Range("B5").Offset(1, 3).Resize(3, 5).Select 

  Итак, когда вы запустите этот код, он выберет диапазон от E6 до I8.

   Range("A1").Font.Strikethrough = True 

  Дополнительные руководства

  • Подсчет строк с использованием VBA в Excel
  • Шрифт Excel VBA (цвет, размер, тип и полужирный шрифт)
  • Excel VBA Скрытие и отображение столбца или строки
  • Применение границ к ячейке с помощью VBA в Excel
  • Поиск последней строки, столбца и ячейки с помощью VBA в Excel
  • Вставка строки с помощью VBA в Excel
  • Объединить ячейки в Excel с помощью кода VBA
  • Выберите диапазон/ячейку с помощью VBA в Excel
  • ВЫБЕРИТЕ ВСЕ ячейки на листе с помощью кода VBA
  • ActiveCell в VBA в Excel
  • Метод специальных ячеек в VBA в Excel
  • Свойство UsedRange в VBA в Excel
  • Автоподбор VBA (строки, столбцы или весь лист)
  • VBA ClearContents (из ячейки, диапазона или всего листа)
  • Копирование диапазона VBA на другой лист + рабочую книгу
  • VBA Ввод значения в ячейку (установка, получение и изменение)
  • Вставка столбца VBA (одиночный и множественный)
  • Именованный диапазон VBA | (статический + из выделения + динамический)
  • Смещение диапазона VBA
  • Диапазон сортировки VBA | (По убыванию, Несколько столбцов, Ориентация сортировки
  • Перенос текста VBA (ячейка, диапазон и весь лист)
  • Проверка VBA, если ячейка пуста + несколько ячеек

  ⇠ Вернуться к разделу Что такое VBA в Excel

  Полезные ссылки — Вкладка «Разработчик» — Visual Basic Редактор — Запустить макрос — Личная книга макросов — Средство записи макросов Excel — Вопросы интервью VBA — Коды VBA

  excel — Одна ячейка представляет диапазон ячеек

  Задавать вопрос

  спросил 10 лет, 8 месяцев назад

  Изменено 10 лет, 8 месяцев назад

  Просмотрено 19 тысяч раз

  Excel 2003 Вопрос:
  Я хочу, чтобы одна ячейка представляла диапазон ячеек. Так что другая формула может использовать его.

  Прямо сейчас у меня есть уравнение для суммирования часов, отработанных над конкретным проектом. Он ищет название проекта, а рядом с ним у меня есть столбец с указанием того, сколько времени я на него потратил. Это формула =СУММЕСЛИ(D1:D10,"проект1",C1:C10)

  Если вы посмотрите на первое изображение,
  Я хочу, чтобы B10 назвал диапазон Duration (B23-B28)
  Я хочу, чтобы C10 назвал диапазон проекта (C23-C28)

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

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

  ---

  ---

  • excel
  • мс-офис

  1

  Чтобы было немного понятнее.