Як створити динамічний іменований діапазон в Excel?
Зазвичай Іменовані хребти дуже корисні для користувачів Excel, ви можете визначити ряд значень у стовпці, дати цьому стовпцю ім'я, а потім ви можете посилатися на цей діапазон за назвою замість посилань на комірку. Але найчастіше вам потрібно додавати нові дані, щоб у майбутньому розширити значення даних вашого згаданого діапазону. У цьому випадку вам доведеться повернутися до Формули > Менеджер імен та перевизначте діапазон, щоб включити нове значення. Щоб уникнути цього, ви можете створити динамічний іменований діапазон, що означає, що вам не потрібно коригувати посилання на клітинки кожного разу, коли ви додаєте новий рядок або стовпець до списку.
Створіть динамічний іменований діапазон в Excel, створивши таблицю
Створіть динамічний іменований діапазон в Excel за допомогою функції
Створіть динамічний іменований діапазон в Excel за допомогою коду VBA
Створіть динамічний іменований діапазон в Excel, створивши таблицю
Якщо ви використовуєте Excel 2007 або пізнішої версії, найпростіший спосіб створити динамічний іменований діапазон - це створити іменовану таблицю Excel.
Скажімо, у вас є ряд наступних даних, які повинні стати динамічними іменованими діапазонами.
1. По-перше, я визначу назви діапазонів для цього діапазону. Виберіть діапазон A1: A6 і введіть назву Дата в Ім'я Box, Потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Визначити ім'я для діапазону B1: B6 як Saleprice таким же чином. Одночасно я створюю формулу = сума (ціна продажу) у порожній комірці див. знімок екрана:
2. Виберіть діапазон і натисніть Insert > таблиця, див. скріншот:
3, в Створити таблицю підказка, поставте галочку У моїй таблиці є заголовки (якщо діапазон не має заголовків, зніміть його), натисніть OK і дані про діапазон перетворені в таблицю. Дивіться скріншоти:
 |
 |
 |
4. І коли ви вводите нові значення після даних, названий діапазон буде автоматично коригуватися, а створена формула також буде також змінена. Дивіться такі скріншоти:
 |
|
 |
Примітки:
1. Ваші нові дані, що вводяться, повинні бути поруч із вищезазначеними даними, це означає, що між новими даними та існуючими даними немає порожніх рядків або стовпців.
2. У таблиці ви можете вставити дані між існуючими значеннями.
Створіть динамічний іменований діапазон в Excel за допомогою функції
У програмі Excel 2003 або ранішій версії перший спосіб буде недоступний, тому ось вам інший спосіб. Наступні ЗМІСТИТИ () функція може зробити для вас цю користь, але це дещо складно. Припустимо, у мене є діапазон даних, який містить імена діапазонів, які я визначив, наприклад, A1: A6 назва діапазону - Дата та B1: B6 назва діапазону - Ціна продажу, одночасно я створюю формулу для Ціна продажу. Дивіться знімок екрана:
Ви можете змінити назви діапазонів на назви динамічних діапазонів, виконавши такі дії:
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) в Відноситься до текстове поле для Ціна продажу назва діапазону.
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 для програм.
3. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.
Код 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. За допомогою цього коду назви діапазонів не відображаються в Ім'я Box, щоб зручно переглядати та використовувати назви діапазонів, я встановив Kutools для Excel, З його Область переходів, перелічені створені імена динамічного діапазону.
2. За допомогою цього коду весь діапазон даних можна розширити вертикально або горизонтально, але пам’ятайте, що між даними при введенні нових значень між даними не повинно бути порожніх рядків або стовпців.
3. Коли ви використовуєте цей код, діапазон даних повинен починатися з комірки А1.
Пов'язана стаття:
Як автоматично оновити діаграму після введення нових даних у Excel?
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
