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

Содержание

Excel. Как менять диапазон ячеек в формулах

Автор Амина С. На чтение 9 мин Опубликовано 26.06.2020

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

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

Содержание

Как выбирать ячейки, строки и столбцы
Диапазон ячеек: пример
Как заполнить диапазон
Перемещение диапазона
Копирование и вставка диапазона
Как вставить определенную строку или столбец
Именованные диапазоны
Пример 1
Пример 2
Пример 3
Автоматически изменяемые диапазоны

Как выбирать ячейки, строки и столбцы

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

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

Чтобы выбрать ячейку, нужно нажать по ней. Каждая ячейка имеет адрес. Например, расположенная на пересечении колонки C и ряда 3 называется C3.

Соответственно, чтобы выбрать колонку, необходимо нажать на букву, отображающую название столбца. В нашем случае это колонка C.

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

Диапазон ячеек: пример

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

Важно! Не квадратик в правом нижнем углу, а просто как бы потянуть за эту ячейку. Квадратик – это маркер автозаполнения, это немного другое.

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

Как заполнить диапазон

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

Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2. 5
Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

В результате получится следующее. Здесь мы заполнили все нужные ячейки цифрами 2.

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

После этого осуществляем уже знакомое нами перетаскивание.

Перемещение диапазона

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

Потом нужно просто переместить его в нужное место и отпустить мышь.

1112

Копирование и вставка диапазона

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

Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена».

Следующий этап – вставить нужную информацию в другое место. Для этого необходимо найти ячейку, которая будет служить левым верхним углом диапазона, после чего аналогично вызвать контекстное меню, но при этом найти пункт «Вставить». Также можно воспользоваться стандартной комбинацией Ctrl + V, которая работает абсолютно в любых программах.

Как вставить определенную строку или столбец

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

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

Таким вот способом у нас получилось вставить строку.

Именованные диапазоны

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

Назначить имя диапазону можно через Диспетчер имен, который можно найти в меню Формулы – Определенные имена – Диспетчер имен.

Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.

Пример 1

Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.

В общем, наши действия следующие:

Выделить необходимый диапазон.
Перейти на вкладку «Формулы» и там найти команду «Присвоить имя».
Далее появится диалоговое окно, в котором необходимо указать имя диапазона. В нашем случае это – «Продажи».
Там еще находится поле «Область», которое дает возможность выбрать лист, на котором этот диапазон находится.

Проверьте, чтобы был указан правильный диапазон. Формула должна быть следующей: =’1сезон’!$B$2:$B$10
Кликните «ОК». 19

Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.

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

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

В некоторых случаях лучше использовать относительную ссылку.

Пример 2

Давайте теперь определим сумму продаж для каждого из четырех времен года. Ознакомиться с информацией о продажах можно на листе 4_сезона.

На этом скриншоте диапазоны следующие.

B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.

Конечно, чтобы воплотить эту задачу в реальность, вы можете создать несколько диапазонов, но это немного неудобно. Значительно лучше воспользоваться одним. Чтобы упростить так себе жизнь, необходимо использовать относительную адресацию. В таком случае достаточно просто иметь один диапазон, который в случае с нами получит название «Сезонные_продажи»

Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10

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

Далее методика действий та же самая.

Теперь нам нужно ввести в ячейку B11 формулу =СУММ(Сезонные_Продажи). Далее, используя маркер автозаполнения, переносим ее в соседние ячейки, и получается такой результат.

Рекомендация: если нажать клавишу F2 при имеющемся выделении содержащей формулу с именем диапазона ячейки, правильные ячейки будут выделены синей рамкой.

Пример 3

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

=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

Более того, если начать вводить имя диапазона, Excel его автоматически предложит вместе с другими формулам.

Автоматически изменяемые диапазоны

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

Предположим, вы – инвестор, и вам требуется знать, сколько всего денег вы получили за время инвестиций в определенный объект. И предположим, у вас есть такой отчет.

Для этого есть функция «Динамические имена». Чтобы присвоить его, необходимо осуществить следующие действия:

Открыть окно «Присвоить имя».
Заполнить поля так, как указано на скриншоте. 26

Важно обратить внимание, что вместо диапазона используется формула с функцией СМЕЩ совместно с функцией

СЧЕТ.

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

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

Оцените качество статьи. Нам важно ваше мнение:

Excel 10. Диапазон и вычисления в нем – Эффективная работа в MS Office

По окончании этого урока вы сможете:

Работать с диалоговыми окнами «Вставка функции» и «Аргументы функции»
Присвоить имя диапазону ячейки и работать с именами диапазонов
Ввести формулу с абсолютным и относительным адресом ячейки.

Скачайте файл тут. Откройте файл. (Признаюсь – не владею ни одним из этих видов творчества. Просто взяла из Интернета)

1. Диалоговые окна «Вставка функции» и «Аргументы функции»

Шаг 1. Вводим в ячейку А7 Максимум и переходим в ячейку В7:

Шаг 2. Открываем диалоговое окно «Вставка функции» (лента Формулы → команда Вставить функцию):

Как только мы вызвали диалоговое окно «Вставка функции», в вычисляемой ячейке появился знак «=».

Шаг 3. Выбираем в списке из 10 последних использовавших функций «МАКС» (поле «Выберите функцию»).

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

В поле «Поиск функции» вы можете ввести описание нужной вам функции, и Excel услужливо поможет вам в поиске.

При нажатии кнопки выпадающего меню поля «Категории» вы увидите перечень всех категорий функций:

Нашли функцию МАКС? ОК!

Но у нас с вами мелкая неприятность – диалоговое окно «Аргументы функции» перекрывает ряд чисел:

Шаг 4. Щелкаем по значку со стрелкой справа от поля «Число!». Диалоговое окно свернется и вам надо выбрать диапазон, в котором будет искаться максимум.

Шаг 5. Щелкаем по значку со стрелкой справа от поля «Число!» – диалоговое окно «Аргументы функции» развернется. Нажимаем ОК.

В предыдущих версиях Excel эта операция носила название «Мастер функций.

Понять и запомнить!

Кнопка со стрелкой, которая позволяет свернуть и развернуть диалоговое окно, встречается очень часто, так что рекомендую запомнить эту полезную операцию

2. Имя диапазона

Хорошо, если диапазон для вычислений у нас небольшой, хорошо, если вычисляемая ячейка находится по вычисляемым диапазоном. Но мечты и реальность не всегда совпадают. Вот тут на помощь придет полезная возможность, которая называется «Имя диапазона».

1 способ.

Шаг 1. Выделяем диапазон ячеек В1:В6.

Шаг 2. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Создать из выделенного):

Откроется диалоговое окно «Создание имен из выделенного диапазона». Оставим активным параметр «в строке выше». ОК.

2 способ.

Шаг 3. Выделите диапазон ячеек А1:А6.

Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):

Откроется диалоговое окно «Создание имени».

По умолчанию содержимое первой ячейки выделенного диапазона становится именем диапазона. Но никто не мешает нам задать другое имя. Обратите внимание, что пробел заменился на нижнее тире. Что поделаешь? Требование Excel.
Определение области действия имени: вся книга или конкретный лист.
Пояснения, которые мы сочтем нужным дать этому диапазону. Пояснения не участвуют в вычислениях, операциях и во всем другом прочем. Это «напоминалка» для нас.
Определенная величина диапазона. Но мы можем скорректировать величину диапазона. Например, у нас прибавились записи, которые следует отнести к этому диапазону. Тем более, что рядом присутствует кнопка со стрелкой, которая позволит нам на время свернуть диалоговое окно.

Понять и запомнить!

При присвоении имени диапазону название не должно содержать пробелов.

Шаг 5. Нажимаем на кнопку выпадающего меню имени ячейки (неважно, где находится активная ячейка):

Шаг 6. Щелкаем ЛМ по первому имени в этом списке:

Диапазон с именем «Наименование_курса» выделился полностью.

Понять и запомнить!

Разница между двумя способами:
1. Имя диапазона определяется автоматически
2.

Имя диапазона можно задать по своему желанию

Шаг 7. Щелкните в любой ячейке по вашему выбору. Набираем «=су»:

Двойной щелчок ЛМ по «СУММ».

Шаг 8. Выбираем диапазон по имени (набираем непосредственно в ячейке «ц». сразу появляется выпадающий список, в котором присутствуют функции, начинающие на «ц», и имя нашего диапазона). Двойной щелчок ЛМ по имени диапазона:

Шаг 9. И обязательно закрывающая скобка! Требования Excel по части синтаксиса написания формул надо соблюдать.

Шаг 10. Нажимаем Enter:

Смотрим на строку формул: =СУММ(Цена). То есть по имени определил соответствующий диапазон.

Шаг 11. Лента Формулы → группа команд Определенные имена → команда Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:

В этом окне вы можете создать новое имя для диапазона, изменить или удалить имеющееся имя диапазона. В поле «Диапазон» прописано: на каком листе книги расположен диапазон и адреса граничных ячеек. Кнопка с стрелкой, которая находится справа от этого поля, позволяет свернуть диалоговое окно и изменить диапазон.

3. Абсолютный и относительный адрес ячейки

Скопируем одну таблицу. Вот тут внимание! Если вы просто выделите весь диапазон и перенесете на другое место листа (Excel 5), то вы увидите следующую картину:

Потом потратим драгоценное время на настройки ширины и высоты ячеек. Так что не торопитесь.

Шаг 1. Выделяем таблицу со значениями и копируем в буфер обмена:

Шаг 2. Вставляем содержимое буфера обмена в ячейку F1 специальной вставкой с сохранение ширины столбцов:

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

Сейчас мы подсчитаем стоимость курса для группы из 20 человек (больше набирать смысла нет). Для первой таблицы введите в диапазоне С2:С6 число «20» в каждую ячейку. Для второй таблицы введите число «20» в ячейку Н1:

Шаг 3. В ячейку D2 введите формулу «=ПРОИЗВЕД(В2;С2). Можно проще: «=В2*С2»:

Шаг 4. А теперь распространим эту формулу по всему столбцу стоимости курсов. Это можно сделать двумя способами.

1 способ.

Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
Нажать ЛМ и, не отпуская, протянуть на весь диапазон:

2 способ.

Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
Дважды щелкнуть ЛМ:

Посмотрим на формулы в каждой ячейке.

Шаг 5. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

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

Понять и запомнить!

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

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

Поработаем со второй таблицей.

Шаг 6. В ячейку Н2 вводим формулу «=G2*h2»:

Теперь нам надо указать, что при копировании формулы ссылка будет только на ячейку Н1, то есть адрес этой ячейки будет абсолютным. Для указания абсолютного адреса в Excel служит знак «$» или знак доллара. Если не лень, ставьте вручную (Shift+4 в английской раскладке клавиатуры). Но лучше воспользоваться функциональной клавишей F4, которой все равно какая раскладка клавиатуры):

Если вы работаете за ноутбуком, то не забудьте в дополнение одновременно нажить клавишу Fn.

Шаг 7. Поместите курсор на имя ячейки «Н1» в формуле и нажмите Fn на клавиатуре:

Попробуйте пощелкать.

Относительный адрес. Относительная адресация – это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное (используется по умолчанию).
Абсолютный адрес. Абсолютная адресация – это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное. Выглядит такиой адрес следующим образом: $А$1. Знак доллара обозначает, что значения строки и столбца «закреплены».
Смешанный или частичный адрес. Частичная абсолютная адресация указывается, если при копировании формулы не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца: B$5; D$12.

Понять и запомнить!

F4 – единственный способ изменить тип адреса ячейки!

Шаг 8. Нажмем Enter и скопируем формулу по диапазону:

Шаг 9. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

При копировании формулы адрес с ценой курса соответствующим образом меняется, а адрес ячейки Н1 повторяется – это абсолютный адрес.

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

Теперь вы сможете:

Работать с диалоговыми окнами «Вставка функции» и «Аргументы функции»
Присвоить имя диапазону ячейки и работать с именами диапазонов
Ввести формулу с абсолютным и относительным адресом ячейки.

По ходу дела мы узнали, что есть:

Ещё одна возможность специальной вставки буфера обмена
Работа с курсором заполнения диапазона (черный крестик)
Режим «Показать формулы»

Диапазоны Excel

❮ Назад Далее ❯

Диапазоны

Диапазон — важная часть Excel, поскольку она позволяет работать с выбором ячеек.

Существует четыре различных операции выбора;

Выбор ячейки
Выбор нескольких ячеек
Выбор столбца
Выбор строки

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

Поле имени

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

Подробнее о поле имени вы узнаете позже в этой главе.

Выбор ячейки

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

Проще всего использовать мышь для выбора ячеек.

Чтобы выбрать ячейку A1 , щелкните по ней:

Выбор нескольких ячеек

Можно выбрать более одной ячейки, нажав и удерживая нажатой CTRL или Command и щелкнув левой кнопкой мыши по ячейкам. Закончив выбор, вы можете отпустить CTRL или Command .

Рассмотрим пример: выберите ячейки A1 , A7 , C1 , C7 и B4 .

Это выглядело как на картинке ниже?

Выбор столбца

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

Чтобы выбрать столбец A , щелкните букву A в строке столбца:

Выбор строки

Строки выбираются левой кнопкой мыши. Это выберет все ячейки на листе, связанные с этой строкой.

Чтобы выбрать строку 1 , щелкните ее номер в строке строки:

Выбор всего листа

Всю таблицу можно выбрать, щелкнув треугольник в верхнем левом углу таблицы:

Теперь выбрана вся таблица:

Примечание: Вы также можете выбрать всю таблицу, нажав нажав Ctrl+A для Windows или Cmd+A для MacOS.

Выбор диапазонов

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

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

Существует два способа выбора диапазона ячеек

Поле имени
Перетащите, чтобы отметить диапазон.

Самый простой способ — перетащить и отметить. Давайте не будем усложнять и начнем с этого.

Как перетащить и отметить диапазон, шаг за шагом:

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

Давайте рассмотрим пример того, как пометить диапазон

A1:E10 .

Примечание: После этого примера вы узнаете, почему диапазон называется A1:E10 .

Выберите ячейку A1 :

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

Отпустите левую кнопку мыши, когда вы отметили диапазон A1:E10 :

Вы успешно выбрали диапазон A1:E10 . Молодец!

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

: между ними.
Ссылка на верхний левый угол : Ссылка на правый нижний угол
Диапазон, показанный на рисунке, имеет значение A1:E10 :
На данный момент лучше всего использовать метод перетаскивания и отметки, так как он проще и нагляднее.
В следующей главе вы узнаете о заполнении и о том, как это применимо к диапазонам, которые мы только что изучили.
Проверьте себя с помощью упражнений
Упражнение Excel:
Введите ссылку на ячейку для Столбец A, Строка 1 :
Запустите упражнение
❮ Предыдущий Следующее ❯

Excel VBA Lacings and Cells
В этой статье
Диапазоны и клетки в VBA
Адрес
Диапазон клеток
Написание в клетках
Отчеты из клеток
Non Contigressection
. ячеек
Смещение от ячейки или диапазона
Установка ссылки на диапазон
Изменение размера диапазона
СМЕЩЕНИЕ и изменение размера
Все ячейки на листе
ИСПОЛЬЗОВАНИЕ РАСПОЛОЖЕНИЯ
CURCESTREGION
.
Другие примеры диапазона
Для каждого
Сортировка
Найти
Адрес диапазона
Диапазон в массив
Массив в диапазон
Суммарный диапазон
Диапазон подсчета
Диапазоны и ячейки в VBA
Электронные таблицы Excel хранят данные в ячейках. Ячейки организованы в строки и столбцы. Каждую ячейку можно идентифицировать по точке пересечения ее строки и столбца (пример B3 или R3C2).
Диапазон Excel относится к одной или нескольким ячейкам (например, A3:B4)
Адрес ячейки
Нотация A1
номер строки (от 1 до 1 048 576). Это называется адресом ячейки.
В VBA вы можете обратиться к любой ячейке, используя Range Object .
' См. ячейку B4 на текущем активном листе Диапазон MsgBox("B4") ' Обратитесь к ячейке B4 на листе под названием «Данные». Рабочие листы MsgBox("Данные").Range("B4") ' Обратитесь к ячейке B4 на листе с именем «Данные» в другой ОТКРЫТОЙ книге. ' под названием "Мои данные" MsgBox Workbooks("Мои данные").Worksheets("Data").Range("B4")

Нотация R1C1
В нотации R1C1 ячейка обозначается буквой R, за которой следует номер строки, затем буква ‘C’, за которой следует столбец Число. например, B4 в нотации R1C1 будет называться R4C2. В VBA вы используете Ячейки Объект для использования нотации R1C1:
' См. ячейку R[6]C[4], т.е. D6 Cells(6, 4) = "D6"
Диапазон ячеек
Обозначение A1
Чтобы сослаться на более чем одну ячейку, используйте «:» между начальным адресом ячейки и последним адресом ячейки. Следующее будет относиться ко всем ячейкам от A1 до D10:
Range("A1:D10")
Нотация R1C1
Для ссылки на более чем одну ячейку используйте «,» между начальным адресом ячейки и последним адресом ячейки. Следующее будет относиться ко всем ячейкам от A1 до D10:

Диапазон(Ячейки(1, 1), Ячейки(10, 4))
Запись в ячейки
Чтобы записать значения в ячейку или непрерывную группу ячеек, просто обратитесь к диапазону, поставьте знак = и затем напишите значение для сохранения:
' Сохранить F5 в ячейке с адресом F6 Диапазон ("F6") = "F6" ' Сохраните E6 в ячейке с адресом R[6]C[5], т.е. E6 Ячейки (6, 5) = "E6" ' Сохранить A1:D10 в диапазоне A1:D10 Диапазон("A1:D10") = "A1:D10" ' или Диапазон (Ячейки (1, 1), Ячейки (10, 4)) = "A1: D10"
Чтение из ячеек
Чтобы прочитать значения из ячеек, просто обратитесь к переменной для хранения значений, поставьте знак = и затем обратитесь к диапазону для чтения:
Dim val1 Дим знач2 ' Читать из ячейки F6 знач1 = Диапазон ("F6") ' Читать из ячейки E6 val2 = ячейки (6, 5) MsgBox значение1 Msgbox val2
Примечание. Чтобы сохранить значения из диапазона ячеек, вам нужно использовать массив вместо простой переменной.
Несмежные ячейки
Для обозначения несмежных ячеек используйте запятую между адресами ячеек:
' Сохранить 10 в ячейках A1, A3 и A5. Диапазон ("А1, А3, А5") = 10 ' Сохранить 10 в ячейках A1:A3 и D1:D3) Range("A1:A3, D1:D3") = 10
Кодирование VBA стало проще
Остановить поиск кода VBA в сети. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!
Узнать больше
Пересечение ячеек
Для ссылки на несмежные ячейки используйте пробел между адресами ячеек:
' Сохранить столбец D в D1:D10 ', что является общим между A1: D10 и D1: F10 Диапазон("A1:D10 D1:G10") = "Цветок D"
Смещение от ячейки или диапазона
Используя функцию смещения, вы можете переместить ссылку из заданного диапазона (ячейки или группы ячеек) на указанное количество_строк и количество_столбцов.
Синтаксис смещения
Диапазон.Смещение(количество_строк, количество_столбцов)
Смещение от ячейки
' СМЕЩЕНИЕ от ячейки A1 ' Обратитесь к самой ячейке ' Переместить 0 строк и 0 столбцов Диапазон ("A1"). Смещение (0, 0) = "A1" ' Переместить 1 строку и 0 столбцов Диапазон ("A1"). Смещение (1, 0) = "A2" ' Переместить 0 строк и 1 столбец Диапазон ("A1"). Смещение (0, 1) = "B1" ' Переместить 1 строку и 1 столбец Диапазон ("A1"). Смещение (1, 1) = "B2" ' Переместить 10 строк и 5 столбцов Диапазон ("A1"). Смещение (10, 5) = "F11"
Смещение от диапазона
' Переместить ссылку в диапазон A1:D4 на 4 строки и 4 столбца ' Новая ссылка - E5:H8 Диапазон("A1:D4").Смещение(4,4) = "E5:H8"
Установка ссылки на диапазон
Чтобы назначить диапазон переменной диапазона: объявите переменную типа Range, затем используйте команду Set, чтобы установить ее в диапазон. Обратите внимание, что вы должны использовать команду SET, так как RANGE является объектом:
' Объявить переменную Range Затемнить мой диапазон как диапазон ' Установить переменную в диапазоне A1:D4 Установите мой диапазон = диапазон («A1: D4») ' Выводит $A$1:$D$4 MsgBox myRange. Address
Программирование VBA | Генератор кода работает на вас!
Изменение размера диапазона
Метод изменения размера объекта Range изменяет размер эталонного диапазона:
Dim myRange As Range ' Диапазон для изменения размера Установите myRange = Диапазон («A1: F4») ' Выводит $A$1:$E$10 Debug.Print myRange.Resize(10, 5).Address
Верхняя левая ячейка измененного диапазона совпадает с верхней левой ячейкой исходного диапазона
Синтаксис изменения размера
Range.Resize(number_of_rows, number_of_columns)
СМЕЩЕНИЕ и изменение размера
Смещение не изменяет размеры диапазона, но перемещает его на указанное количество строк и столбцов. Изменение размера не изменяет положение исходного диапазона, но изменяет размеры на указанное количество строк и столбцов.
Все ячейки листа
Объект «Ячейки» относится ко всем ячейкам листа (1048576 строк и 16384 столбца).
'Очистить все ячейки в листах Cells.Clear
ИспользуетсяДиапазон
Свойство UsedRange дает вам прямоугольный диапазон от используемой ячейки верхней левой ячейки до используемой ячейки правой нижней ячейки активного листа.
Dim ws As Рабочий лист Установите ws = ActiveSheet ' $B$2:$L$14, если L2 является первой ячейкой с любым значением ' и L14 - последняя ячейка с любым значением на ' активный лист Debug.Print ws.UsedRange.Address
CurrentRegion
Свойство CurrentRegion предоставляет непрерывный прямоугольный диапазон от верхней левой ячейки до правой нижней используемой ячейки, содержащей указанную ячейку/диапазон.
Затемнить мой диапазон как диапазон Установите мой диапазон = диапазон («D4: F6») ' Выводит $B$2:$L$14 ' Если есть заполненный путь от D4:F16 до B2 И L14 Debug.Print myRange.CurrentRegion.Address ' Вы также можете обратиться к одной начальной ячейке Set myRange = Range("D4") ' Выводит $B$2:$L$14
AutoMacro | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!
Свойства диапазона
Вы можете получить адрес, номер строки/столбца ячейки и количество строк/столбцов в диапазоне, как указано ниже:
Затемнить мой диапазон как диапазон Установите мой диапазон = диапазон («A1: F10») ' Выводит $A$1:$F$10 Debug. Print myRange.Address Установите мой диапазон = диапазон («F10») ' Печатает 10 для строки 10 Debug.Print myRange.Row ' Печатает 6 для столбца F Debug.Print myRange.Column Установите myRange = Диапазон («E1: F5») ' Печатает 5 для количества строк в диапазоне Debug.Print myRange.Rows.Count ' Печатает 2 для количества столбцов в диапазоне Debug.Print myRange.Columns.Count
Последняя ячейка на листе
Вы можете использовать свойства Rows.Count и Columns.Count с объектом Cells , чтобы получить последнюю ячейку на листе:
' Напечатать номер последней строки ' Печатает 1048576 Debug.Print "Строки на листе: " & Rows.Count ' Напечатать номер последнего столбца ' Печатает 16384 Debug.Print "Столбцы на листе: " & Columns.Count 'Вывести адрес последней ячейки ' Выводит $XFD$1048576 Debug.Print "Адрес последней ячейки на листе:" & Cells(Rows.Count, Columns.Count)
Номер последней использованной строки в столбце
Свойство END позволяет перейти к последней ячейке в диапазоне, а End(xlUp) — к первой использованной ячейке из этой ячейки.
Dim lastRow As Long lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Номер последнего использованного столбца в строке
Dim lastCol As Long lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Свойство END переводит вас в последнюю ячейку в диапазоне, а End(xlToLeft) переводит вас влево к первой используемой ячейке из этой ячейки.
Вы также можете использовать свойства xlDown и xlToRight для перехода к первой нижней или правой используемой ячейке текущей ячейки.
Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!
Свойства ячейки
Общие свойства
Вот код для отображения часто используемых свойств ячейки
Затемнить ячейку как диапазон Установить ячейку = диапазон («A1») ячейка.Активировать Отладка.Печать ячейки.Адрес ' Напечатайте $A$1 Отладка.Печать ячейки.Значение ' Печатает 456 ' Адрес Отладка.Печать ячейки. Формула ' Выводит =СУММ(C2:C3) ' Комментарий Отладка.Печать ячейки.Комментарий.Текст ' Стиль Отладка.Печать ячейки.Стиль ' Формат ячейки Отладка.Печать ячейки.DisplayFormat.NumberFormat
Cell Font
Объект Cell.Font содержит свойства шрифта Cell:
Dim Cell As Range Установить ячейку = диапазон («A1») ' Обычный, курсив, полужирный и полужирный курсив cell.Font.FontStyle = "Жирный курсив" ' Такой же как ячейка.Шрифт.Полужирный = Истина cell.Font.Italic = Истина ' Установить шрифт Courier cell.Font.FontStyle = "Курьер" ' Установить цвет шрифта ячейка.Шрифт.Цвет = vbBlue ' или ячейка.Шрифт.Цвет = RGB(255, 0, 0) ' Установить размер шрифта ячейка.Шрифт.Размер = 20
Копировать и вставить
Вставить все
Диапазоны/ячейки можно копировать и вставлять из одного места в другое. Следующий код копирует все свойства исходного диапазона в целевой диапазон (эквивалентно CTRL-C и CTRL-V)
'Простая копия Диапазон("A1:D20"). Копировать Рабочие листы ("Лист2"). Диапазон ("B10"). Вставить 'или ' Скопировать с текущего листа на лист с именем "Лист2" Диапазон("A1:D20").Цель копирования:=Рабочие листы("Лист2").Range("B10")
Специальная вставка
Выбранные свойства исходного диапазона можно скопировать в место назначения с помощью параметра СПЕЦИАЛЬНАЯ ВСТАВКА:
' Вставить диапазон только как значения Диапазон("A1:D20").Копировать Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues
Возможные варианты для параметра Paste:
' Вставить специальные типы кслпастеалл кслпастеаллексцептбордерс кслпастеаллмергингкондиционалформатс кслпастеаллусингсаурцетеме кслпастеколумнвидс кслпастекомментс кслпастеформатс кслпастеформулас кслпастеформуласанднумберформатс кслпастевалидатион кслпастевалуес кслпастевалуесанднумберформатс
AutoFit Contents
Размер строк и столбцов можно изменить в соответствии с содержимым с помощью AutoFit:
' Изменить размер строк с 1 по 5 в соответствии с содержимым Строки("1:5"). Автоподбор ' Изменить размер столбцов A на B, чтобы они соответствовали содержимому Столбцы("A:B").Автоподбор
Дополнительные примеры диапазонов
Рекомендуется использовать средство записи макросов при выполнении необходимых действий через графический интерфейс. Это поможет вам понять различные доступные варианты и способы их использования.
Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!
Для каждого
Можно легко перебирать диапазон, используя конструкцию For Each , как показано ниже:
Для каждой ячейки в диапазоне ("A1:B100") ' Сделай что-нибудь с клеткой Следующая ячейка
На каждой итерации цикла одна ячейка в диапазоне назначается ячейке переменной, и для этой ячейки выполняются операторы цикла For. Цикл завершается, когда все ячейки обработаны.
Сортировка
Сортировка — это метод объекта Range. Вы можете отсортировать диапазон, указав параметры сортировки в Range. Sort. Приведенный ниже код сортирует столбцы A:C на основе ключа в ячейке C2. Порядок сортировки может быть xlAscending или xlDescending. Header:= xlYes следует использовать, если первая строка является строкой заголовка.
Столбцы("A:C").Ключ сортировки1:=Диапазон("C2"), _ порядок1:=xlПо возрастанию, Заголовок:=xlДа
Поиск
Поиск также является методом Range Object. Он находит первую ячейку, содержимое которой соответствует критериям поиска, и возвращает ячейку как объект Range. Он возвращает Ничего если совпадений нет.
Используйте метод FindNext (или FindPrevious), чтобы найти следующее (предыдущее) вхождение.
Следующий код изменит шрифт на «Arial Black» для всех ячеек в диапазоне, начинающихся с «Джон»:
Для каждого c в диапазоне («A1: A100») Если c Нравится "Джон*" Тогда c.Font.Name = "Черный шрифт Arial" Конец, если Следующий с
Следующий код заменит все вхождения «Тестировать» на «Пройдено» в указанном диапазоне:
С диапазоном ("a1:a500") Установите c = . Find("Для тестирования", LookIn:=xlValues) Если не c - это ничто, то первый адрес = c.Address Делать c.Value = "Пройдено" Установить c = .FindNext(c) Цикл, в то время как Not c Is Nothing и c.Address <> firstaddress Конец, если End With
Важно отметить, что для использования FindNext необходимо указать диапазон. Также вы должны указать условие остановки, иначе цикл будет выполняться вечно. Обычно адрес первой найденной ячейки сохраняется в переменной, и цикл останавливается, когда вы снова достигаете этой ячейки. Вы также должны проверить случай, когда ничего не найдено, чтобы остановить цикл.
Адрес диапазона
Используйте Range.Address для получения адреса в стиле A1
MsgBox Range("A1:D10").Address ' или Debug.Print Range("A1:D10").Address
Используйте xlReferenceStyle (по умолчанию xlA1) для получения адресов в стиле R1C1
MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1) ' или Debug. Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
Это полезно, когда вы имеете дело с диапазонами, хранящимися в переменных, и хотите обрабатывать только определенные адреса.
Автомакрос | Окончательная надстройка VBA | Нажмите для бесплатной пробной версии!
Диапазон в массив
Быстрее и проще передать диапазон в массив и затем обработать значения. Вы должны объявить массив как Variant, чтобы избежать вычисления размера, необходимого для заполнения диапазона в массиве. Размеры массива устанавливаются в соответствии с количеством значений в диапазоне.
Dim DirArray как вариант ' Сохраняем значения диапазона в массив DirArray = Диапазон ("a1: a5"). Значение ' Цикл для обработки значений Для каждого c в DirArray Отладка.Печать c Следующий
Массив в диапазон
После обработки вы можете записать массив обратно в диапазон. Чтобы записать массив в приведенном выше примере в диапазон, вы должны указать диапазон, размер которого соответствует количеству элементов в массиве.