Как создать динамический именованный диапазон в Excel?
Обычно именованные диапазоны очень полезны для пользователей Excel: вы можете выделить набор значений в столбце, присвоить ему имя и затем ссылаться на этот диапазон по имени вместо использования стандартных ссылок на ячейки. Однако чаще всего вам нужно добавлять новые данные, чтобы расширить диапазон в будущем. В этом случае приходится возвращаться в меню Формулы > Менеджер имён и заново определять диапазон, включая новые значения. Чтобы избежать этой рутины, создайте динамический именованный диапазон — он автоматически подстраивается под добавление новых строк или столбцов, и вам больше не придётся каждый раз обновлять ссылки вручную.
Создание динамического именованного диапазона в Excel путём создания таблицы
Создание динамического именованного диапазона в Excel с помощью функции
Создание динамического именованного диапазона в Excel с помощью кода VBA
Создание динамического именованного диапазона в Excel путём создания таблицы
Если вы работаете в Excel 2007 или более поздней версии, самый простой способ создать динамический именованный диапазон — это преобразовать данные в именованную таблицу Excel.
Допустим, у вас есть следующий диапазон данных, который нужно преобразовать в динамический именованный диапазон.
1. Сначала задайте имя диапазону ячеек. Выделите диапазон A1:A6, введите имя Date в Поле имени и нажмите клавишу Enter. Аналогичным образом присвойте имя Saleprice диапазону B1:B6. Затем в пустой ячейке создайте формулу =SUM(Saleprice) (см. скриншот):
2. Выделите диапазон и нажмите Вставка > Таблица (см. скриншот):
3. В окне Создание таблицы установите флажок Моя таблица содержит заголовки (если диапазон не содержит заголовков, снимите флажок) и нажмите кнопку ОК. Диапазон данных будет преобразован в таблицу (см. скриншоты):
 |  |  |
4. После ввода новых значений вслед за существующими данными именованный диапазон автоматически обновится, и формула изменится соответственно (см. скриншоты ниже):
 | |  |
Примечания:
1. Новые данные должны размещаться непосредственно рядом с существующими — между ними не должно быть пустых строк или столбцов.
2. В таблице вы можете вставлять данные между уже существующими значениями.
Создание динамического именованного диапазона в Excel с помощью функции
В Excel 2003 и более ранних версиях первый метод недоступен, поэтому предлагаем альтернативный способ. Следующая функция OFFSET( ) поможет решить эту задачу, хотя и выглядит несколько громоздкой. Допустим, у вас есть диапазон данных с заданными мной именами ячеек: например, A1:A6 с именем Date, а также B1:B6 с именем Saleprice. Одновременно я создаю формулу для Saleprice (см. скриншот):
Вы можете преобразовать Имя ячейки в динамический Имя ячейки, выполнив следующие шаги:
1. Перейдите в меню Формулы > Менеджер имен (см. скриншот):
2. В диалоговом окне Менеджер имен выберите нужный элемент и нажмите кнопку Изменить.
3. В появившемся диалоговом окне Редактировать имя введите формулу =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) в поле Ссылка на (см. скриншот):
4. Затем нажмите кнопку ОК и повторите шаги 2 и 3, чтобы скопировать эту формулу =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) в поле Ссылка на для имени Saleprice — имя ячейки.
5. Динамические именованные диапазоны успешно созданы. Как только вы добавите новые значения после существующих данных, именованный диапазон автоматически обновится, и соответствующая формула изменится (см. скриншоты):
| | |
Примечание: Если в середине диапазона есть пустые ячейки, результат формулы окажется некорректным. Дело в том, что непустые ячейки не учитываются, из-за чего ваш диапазон станет короче, чем должен быть, и последние ячейки будут пропущены.
Совет: пояснение к этой формуле:
- =OFFSET(reference,rows,cols,[height],[width])
- =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)
- ссылкасоответствует начальной позиции ячейки; в данном примере это Sheet1!$A$1;
- строка указывает количество строк, на которое вы переместитесь вниз относительно начальной ячейки (или вверх, если указано отрицательное значение). В данном примере 0 означает, что список начнётся с первой строки вниз
- столбец определяет, на сколько столбцов вы переместитесь вправо от начальной ячейки (или влево, если указано отрицательное значение). В приведённой выше формуле значение 0 означает, что диапазон не расширяется ни на один столбец вправо.
- [высота] соответствует высоте (то есть количеству строк) диапазона, начинающегося с скорректированной позиции. $A:$A подсчитает все введённые элементы в столбце A.
- [ширина] соответствует ширине (то есть количеству столбцов) диапазона, начинающегося с скорректированной позиции. В приведённой выше формуле список будет занимать 1 столбец.
Вы можете адаптировать эти аргументы под свои нужды.
Создание динамического именованного диапазона в Excel с помощью кода VBA
Если у вас несколько столбцов, вы можете повторить и ввести отдельную формулу для каждого из оставшихся столбцов, но это будет длительный и рутинный процесс. Чтобы упростить задачу, можно использовать код для автоматического создания динамического именованного диапазона.
1. Активируйте нужный лист.
2. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.
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?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек