Как создать диапазон в excel

Адаптированный перевод статьи Тома Огера (Tom Auger)
Named Ranges in Excel that Automatically Expand (Dynamic Ranges Part 1).
Статья была доступна здесь.

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

Что такое даты для Excel?

Именованные диапазоны в Excel — это отличный инструмент. Они позволяют делать такие вещи, как выпадающие списки в пункте Проверка данных. Или можно присвоить имя диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать координаты (A1:B5).

Одна из неприятностей, связанных с поддержкой списков — необходимость править диапазон в Формулы > Диспетчер имён после каждого добавления/удаления строк данных в исходном диапазоне. Чтобы избежать подобной ситуации, можно создать динамический диапазон, применив формулы вместо жёстко заданных координат. Чаще всего используется функция СМЕЩ, как показано ниже. Запрос «Excel динамический диапазон» в любом поисковике вернёт сотни ссылок, большинство из которых будут вариантами формулы:

=СМЕЩ(Лист!$A$1, 0, 0, СЧЁТЗ ($A:$A), 1)

СМЕЩ возвращает диапазон, модифицированный относительно базового – пункт Ссылка. Смещпострокам и Смещпостолбцам смещают начало диапазона на соответствующее число строк и столбцов. Высота и Ширина задают количество строк и столбцов в диапазоне.

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

Смещпострокам: обычно 0, т.к. стартовую позицию мы уже определили.

Смещпостолбцам: так же обычно 0, по той же самой причине.

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

Ширина: количество столбцов в нашем диапазоне (минимум 1).

Понятно, что функция СМЕЩ сама по себе не слишком полезна для наших целей. Если вы собираетесь использовать статические числа, то откажитесь от СМЕЩ, непосредственно прописав диапазон. Причина, по которой мы используем СМЕЩ — возможность замены параметров (аргументов) формулами. Они и позволяют получить динамическую часть.

Типичная формула динамического диапазона, которую можно найти на просторах интернета, использует СЧЁТ (для числовых данных) или СЧЁТЗ (для текста). Обе эти функции подсчитывают количество непустых ячеек. Если мы подсчитаем количество непустых ячеек в одном столбце и введём это число в параметр Высота, то мы получим диапазон от начальной ячейки до последней ячейки с записью. Но это в теории.

В действительности, если вы внимательно читали, мы получим диапазон высотой в то количество строк, которое мы получили от СЧЁТЗ. Если в диапазоне присутствуют пустые ячейки, то выходной диапазон окажется короче, чем необходимо, и последние ячейки потеряются. Помните об этой тонкости.

Наиболее часто динамический диапазон используется в следующих случаях:

в ссылках в формулах для других таблиц
для определения исходных диапазонов сводных таблиц
для определения исходных диапазонов диаграмм
для определения наборов переменных выпадающих списков

Ссылка на именованные диапазоны

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

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

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

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

Как создать именованный диапазон книги:

Как указано выше, обычно он создается путем ввода имени в поле «Имя» с левой стороны строки формул. Обратите внимание, что имя не может содержать пробелов.

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

Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. Ссылайтесь на этот именованный диапазон только по имени на том же листе, но на другом листе необходимо использовать имя листа, включая «!», имя диапазона (например: «Имя» «=Лист1! Имя»).

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

Как создать именованный диапазон определенного листа:

Выделите диапазон, которому нужно присвоить имя.
Перейдите на вкладку «Формулы» на ленте Excel в верхней части окна.
Нажмите кнопку «Присвоить имя» на вкладке формул.
В диалоговом окне «Создание имени» в поле «Область» выберите конкретный лист, где расположен диапазон, которому нужно присвоить имя (например, «Лист1»), чтобы связать имя с этим листом. Если выбрать вариант «Книга», это будет имя книги.

Пример из worksheet Specific Именованный диапазон: выбранный диапазон для имени A1:A10

Выбранное имя диапазона — «Имя». В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку «=Имя». Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: «= Лист1!Имя».

Ссылка на именованный диапазон

В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls.

Sub FormatRange() Range("MyBook.xls!MyRange").Font.Italic = True End Sub

В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls.

Sub FormatSales() Range("[Report.xls]Sheet1!Sales").BorderAround Weight:=xlthin End Sub

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

Sub ClearRange() Application.Goto Reference:="MyBook.xls!MyRange" Selection.ClearContents End Sub

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

Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub

Пример кода предоставил: Деннис Валлентин VSTO & .NET & Excel

В этом примере в качестве формулы для проверки данных используется именованный диапазон. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10.

Sub Add_Data_Validation_From_Other_Worksheet() 'The current Excel workbook and worksheet, a range to define the data to be validated, and the target range 'to place the data in. Dim wbBook As Workbook Dim wsTarget As Worksheet Dim wsSource As Worksheet Dim rnTarget As Range Dim rnSource As Range 'Initialize the Excel objects and delete any artifacts from the last time the macro was run. Set wbBook = ThisWorkbook With wbBook Set wsSource = .Worksheets("Sheet2") Set wsTarget = .Worksheets("Sheet1") On Error Resume Next .Names("Source").Delete On Error GoTo 0 End With 'On the source worksheet, create a range in column A of up to 98 cells long, and name it "Source". With wsSource .Range(.Range("A2"), .Range("A100").End(xlUp)).Name = "Source" End With 'On the target worksheet, create a range 8 cells long in column D. Set rnTarget = wsTarget.Range("D2:D10") 'Clear out any artifacts from previous macro runs, then set up the target range with the validation data. With rnTarget .ClearContents With .Validation .Delete .Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:="=Source" 'Set up the Error dialog with the appropriate title and message .ErrorTitle = "Value Error" .ErrorMessage = "You can only choose from the list." End With End With End Sub

Циклический переход по ячейкам в именованном диапазоне

В приведенном ниже примере выполняется циклический переход по каждой ячейке именованного диапазона с помощью цикла For Each. Next. Если значение любой ячейки в диапазоне превышает значение Limit , цвет ячейки изменяется на желтый.

Sub ApplyColor() Const Limit As Integer = 25 For Each c In Range("MyRange") If c.Value > Limit Then c.Interior.ColorIndex = 27 End If Next c End Sub

Об участнике

Деннис Валлентин (Dennis Wallentin) — автор блога VSTO & .NET & Excel, посвященного решениям .NET Framework для Excel и службам Excel. Деннис разрабатывает решения Excel более 20 лет и также является соавтором книги «Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)».

Поддержка и обратная связь

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

Обратная связь

Были ли сведения на этой странице полезными?

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

Как и в Excel, так в Гугл Таблицах диапазонам и отдельным ячейкам можно давать имена. Делается это для того, чтобы было легче проводить вычисления в больших таблицах. Использование именованных диапазонов в Google Таблицах упрощает работу с ячейками и делает формулы понятнее.

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

1. Выделите нужные ячейки.
2. Нажмите на Данные , а далее перейдите в Именованные диапазоны . Справа откроется меню.

3. Укажите название для выбранного диапазона.

4. Чтобы изменить диапазон, нажмите на значок выбрать диапазон данных.

5. Выберите новый диапазон в таблице или введите его в текстовом поле, а затем нажмите ОК.

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

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

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

Чтобы выделить весь столбец или строку, щелкните заголовок столбца или строки.

заголовки столбцов и строк на листе