Перейти к основному содержанию

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

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

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

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

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


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

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

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

документ-динамический-диапазон1

1. Во-первых, я определю имена диапазонов для этого диапазона. Выберите диапазон A1: A6 и введите имя Время в Имя Box, Затем нажмите Enter ключ. Таким же образом можно определить имя диапазона B1: B6 как Saleprice. Заодно создаю формулу = сумма (Цена продажи) в пустой ячейке см. снимок экрана:

документ-динамический-диапазон2

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

документ-динамический-диапазон3

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

документ-динамический-диапазон4 -2 документ-динамический-диапазон5

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

документ-динамический-диапазон6 -2 документ-динамический-диапазон7

Ноты:

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

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


стрелка синий правый пузырь Создать динамический именованный диапазон в Excel с помощью функции

В Excel 2003 или более ранней версии первый метод будет недоступен, поэтому есть другой способ. Следующее СМЕЩЕНИЕ () function может оказать вам такую ​​услугу, но это несколько хлопотно. Предположим, у меня есть диапазон данных, который содержит имена диапазонов, которые я определил, например, A1: A6 название диапазона Время, и B1: B6 название диапазона Цена продажи, в то же время я создаю формулу для Цена продажи. Смотрите скриншот:

документ-динамический-диапазон2

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

1. Перейти к щелчку Формулы > Менеджер имен, см. снимок экрана:

документ-динамический-диапазон8

2. В Менеджер имен диалоговом окне выберите элемент, который вы хотите использовать, и щелкните Редактировать .

документ-динамический-диапазон9

3. В выскочившем Редактировать имя диалоговое окно введите эту формулу = СМЕЩЕНИЕ (Лист1! $ A $ 1; 0; 0; СЧЁТ ($ A: $ A); 1) в Относится к текстовое поле, см. снимок экрана:

документ-динамический-диапазон10

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

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

документ-динамический-диапазон6 -2 документ-динамический-диапазон7

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

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

  • = СМЕЩЕНИЕ (ссылка; строки; столбцы; [высота]; [ширина])
  • -1
  • = СМЕЩЕНИЕ (Лист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. Когда вы вводите новые значения после строк или столбцов, диапазон также будет расширен. Смотрите скриншоты:

документ-динамический-диапазон12
-1
документ-динамический-диапазон13

Ноты:

1. С этим кодом имена диапазонов не отображаются в Имя Box, для удобства просмотра и использования названий диапазонов я установил Kutools for Excel, С его Область переходов, отображаются имена созданных динамических диапазонов.

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

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


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

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

Лучшие инструменты для офисной работы

Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

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

вкладка kte 201905


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
really, really not helpful
This comment was minimized by the moderator on the site
please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
This comment was minimized by the moderator on the site
You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
This comment was minimized by the moderator on the site
Thanks for good article
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations