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