Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

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

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

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

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

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу ...
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты: Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий ...
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

стрелка синий правый пузырь Создайте динамический именованный диапазон в 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?


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

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    mozart777 · 2 years ago
    really, really not helpful
  • To post as a guest, your comment is unpublished.
    loyiso · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    marge · 6 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Iran · 7 years ago
    Thanks for good article