Как создать динамический именованный диапазон в Excel?
Как правило, Именованные диапазоны очень полезны для пользователей Excel, вы можете определить серию значений в столбце, дать этому столбцу имя, а затем вы можете ссылаться на этот диапазон по имени вместо ссылок на его ячейки. Но чаще всего вам нужно добавлять новые данные, чтобы в будущем расширить значения данных вашего указанного диапазона. В этом случае вам нужно вернуться к Формулы > Менеджер имен и переопределите диапазон, чтобы включить новое значение. Чтобы избежать этого, вы можете создать динамический именованный диапазон, что означает, что вам не нужно настраивать ссылки на ячейки каждый раз, когда вы добавляете новую строку или столбец в список.
Создайте динамический именованный диапазон в Excel, создав таблицу
Создать динамический именованный диапазон в Excel с помощью функции
Создать динамический именованный диапазон в Excel с кодом VBA
Создайте динамический именованный диапазон в Excel, создав таблицу
Если вы используете Excel 2007 или более поздние версии, самый простой способ создать динамический именованный диапазон - это создать именованную таблицу Excel.
Скажем, у вас есть ряд следующих данных, которые должны стать динамическим именованным диапазоном.
1. Во-первых, я определю имена диапазонов для этого диапазона. Выберите диапазон A1: A6 и введите имя Время в Имя Box, Затем нажмите Enter ключ. Таким же образом можно определить имя диапазона B1: B6 как Saleprice. Заодно создаю формулу = сумма (Цена продажи) в пустой ячейке см. снимок экрана:
2. Выберите диапазон и нажмите Вставить > Настольные, см. снимок экрана:
3. В Создать таблицу окно подсказки, отметьте В моей таблице есть заголовки (если у диапазона нет заголовков, снимите флажок), нажмите OK кнопку, и данные диапазона были преобразованы в таблицу. Смотрите скриншоты:
 |
 |
 |
4. И когда вы вводите новые значения после данных, именованный диапазон автоматически настраивается, и созданная формула также будет изменена. См. Следующие скриншоты:
 |
|
 |
Ноты:
1. Новые вводимые данные должны быть рядом с указанными выше, это означает, что между новыми и существующими данными нет пустых строк или столбцов.
2. В таблице вы можете вставить данные между существующими значениями.
Создать динамический именованный диапазон в Excel с помощью функции
В Excel 2003 или более ранней версии первый метод будет недоступен, поэтому есть другой способ. Следующее СМЕЩЕНИЕ () function может оказать вам такую услугу, но это несколько хлопотно. Предположим, у меня есть диапазон данных, который содержит имена диапазонов, которые я определил, например, A1: A6 название диапазона Времякачества B1: B6 название диапазона Цена продажи, в то же время я создаю формулу для Цена продажи. Смотрите скриншот:
Вы можете изменить имена диапазонов на имена динамических диапазонов, выполнив следующие действия:
1. Перейти к щелчку Формулы > Менеджер имен, см. снимок экрана:
2. В Менеджер имен диалоговом окне выберите элемент, который вы хотите использовать, и щелкните Редактировать .
3. В выскочившем Редактировать имя диалоговое окно введите эту формулу = СМЕЩЕНИЕ (Лист1! $ A $ 1; 0; 0; СЧЁТ ($ A: $ A); 1) в Относится к текстовое поле, см. снимок экрана:
4. Затем нажмите OK, а затем повторите шаги 2 и 3, чтобы скопировать эту формулу = СМЕЩЕНИЕ (Лист1! $ B $ 1; 0, 0; COUNTA ($ B: $ B), 1) в Относится к текстовое поле для Цена продажи название диапазона.
5. И были созданы динамические именованные диапазоны. Когда вы вводите новые значения после данных, именованный диапазон автоматически настраивается, и созданная формула также будет изменена. Смотрите скриншоты:
|
|
|
Примечание: Если в середине диапазона есть пустые ячейки, результат формулы будет неправильным. Это связано с тем, что непустые ячейки не учитываются, поэтому ваш диапазон будет короче, чем должен, а последние ячейки в диапазоне будут исключены.
Совет: объяснение этой формулы:
- = СМЕЩЕНИЕ (ссылка; строки; столбцы; [высота]; [ширина])
- = СМЕЩЕНИЕ (Лист1! $ A $ 1; 0; 0; СЧЁТ ($ A: $ A); 1)
- ссылка соответствует начальной позиции ячейки, в этом примере Sheet1! $ A $ 1;
- строка относится к количеству строк, которые вы собираетесь переместить вниз относительно начальной ячейки (или вверх, если вы используете отрицательное значение.), в этом примере 0 означает, что список будет начинаться с первой строки вниз
- обзор соответствует количеству столбцов, которые вы переместите вправо относительно начальной ячейки (или влево, используя отрицательное значение.), в приведенной выше формуле примера 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 ключ для запуска кода, и будут сгенерированы некоторые динамические именованные диапазоны, которые именуются значениями первой строки, а также создает динамический диапазон, называемый Мои данные который охватывает все данные.
5. Когда вы вводите новые значения после строк или столбцов, диапазон также будет расширен. Смотрите скриншоты:
 |
|
 |
Ноты:
1. С этим кодом имена диапазонов не отображаются в Имя Box, для удобства просмотра и использования названий диапазонов я установил Kutools for Excel, С его Область переходов, отображаются имена созданных динамических диапазонов.
2. С помощью этого кода весь диапазон данных может быть расширен по вертикали или горизонтали, но помните, что при вводе новых значений между данными не должно быть пустых строк или столбцов.
3. При использовании этого кода диапазон данных должен начинаться с ячейки A1.
Связанная статья:
Как автоматически обновлять диаграмму после ввода новых данных в Excel?
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
