Как создать динамический именованный диапазон в Excel?

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

Создание динамического именованного диапазона в Excel путем создания таблицы

Создание динамического именованного диапазона в Excel с помощью функции

Создание динамического именованного диапазона в Excel с помощью кода VBA

Создание динамического именованного диапазона в Excel путем создания таблицы

Если вы используете Excel 2007 или более поздние версии, самый простой способ создать динамический именованный диапазон — это создать именованную таблицу Excel.

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

1. Сначала я определю имена диапазонов для этого диапазона. Выберите диапазон A1:A6 и введите имя Date в Поле имени, затем нажмите клавишу Enter. Таким же образом определите имя Saleprice для диапазона B1:B6. В то же время я создаю формулу =sum(Saleprice) в пустой ячейке, см. скриншот:

2. Выберите диапазон и нажмите Вставка > Таблица, см. скриншот:

3. В появившемся окне Создание таблицы отметьте пункт У моей таблицы есть заголовки (если у диапазона нет заголовков, снимите этот флажок), нажмите кнопку OK, и данные диапазона будут преобразованы в таблицу. См. скриншоты:

4. И когда вы вводите новые значения после данных, именованный диапазон автоматически скорректируется, и созданная формула также изменится. См. следующие скриншоты:

Примечания:

1. Вводимые вами новые данные должны быть смежными с предыдущими данными, то есть между новыми и существующими данными не должно быть пустых строк или столбцов.

2. В таблице вы можете вставлять данные между существующими значениями.

Создание динамического именованного диапазона в Excel с помощью функции

В Excel 2003 или более ранних версиях первый метод недоступен, поэтому вот другой способ для вас. Следующая функция OFFSET( ) может сделать это за вас, но она несколько сложна. Предположим, у меня есть диапазон данных, который содержит имена диапазонов, которые я определил, например, A1:A6 с именем диапазона Date и B1:B6 с именем диапазона Saleprice, в то же время я создаю формулу для Saleprice. См. скриншот:

Вы можете изменить имена диапазонов на динамические имена диапазонов, выполнив следующие шаги:

1. Перейдите в Формулы > Диспетчер имен, см. скриншот:

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

3. В открывшемся диалоговом окне Изменение имени введите эту формулу =OFFSET(Лист1!$A$1, 0, 0, COUNTA($A:$A), 1) в текстовое поле Ссылается на, см. скриншот:

4. Затем нажмите OK и повторите шаги 2 и 3, чтобы скопировать эту формулу =OFFSET(Лист1!$B$1, 0, 0, COUNTA($B:$B), 1) в текстовое поле Ссылается на для имени диапазона Saleprice.

5. И динамические именованные диапазоны были созданы. Когда вы вводите новые значения после данных, именованный диапазон автоматически скорректируется, и созданная формула также изменится. См. скриншоты:

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

Совет: объяснение этой формулы:

• =OFFSET(ссылка,строки,столбцы,[высота],[ширина])
• 
• =OFFSET(Лист1!$A$1, 0, 0, COUNTA($A:$A), 1)

• ссылка соответствует начальной позиции ячейки, в этом примере Лист1!$A$1;
• строка относится к количеству строк, на которое вы собираетесь переместиться вниз относительно начальной ячейки (или вверх, если вы используете отрицательное значение). В этом примере 0 указывает, что список начнется с первой строки вниз.
• столбец соответствует количеству столбцов, на которое вы переместитесь вправо относительно начальной ячейки (или влево, используя отрицательное значение). В приведенной выше формуле 0 указывает расширение на 0 столбцов вправо.
• [высота] соответствует высоте (или количеству строк) диапазона, начинающегося с скорректированной позиции. $A:$A подсчитает все введенные элементы в столбце A.
• [ширина] соответствует ширине (или количеству столбцов) диапазона, начинающегося с скорректированной позиции. В приведенной выше формуле список будет иметь ширину в 1 столбец.

Вы можете изменить эти аргументы по своему усмотрению.

Создание динамического именованного диапазона в Excel с помощью кода VBA

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

1. Активируйте ваш рабочий лист.

2. Нажмите и удерживайте клавиши ALT + F11, и откроется окно Microsoft Visual Basic for Applications.

3. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуля.

Код VBA: создание динамического именованного диапазона

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Затем нажмите клавишу F5 для запуска кода, и будут созданы некоторые динамические именованные диапазоны, которые называются значениями первой строки, а также создается динамический диапазон под названием MyData, который охватывает все данные.

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

Примечания:

1. С этим кодом имена диапазонов не отображаются в Поле имени. Чтобы удобно просматривать и использовать имена диапазонов, я установил Kutools для Excel, с его Панелью навигации перечислены созданные динамические имена диапазонов.

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

3. При использовании этого кода ваш диапазон данных должен начинаться с ячейки A1.

Связанная статья:

Как автоматически обновить диаграмму после ввода новых данных в Excel?