Excel е най-добрият ви инструмент за продуктивност. Научете как да създавате именувани диапазони в Excel с помощта на VBA и намерете съвети за спестяване на време при работа с данни и формули.
Microsoft Excel е мощно софтуерно приложение, което можете да използвате за създаване, проследяване и анализ на данни. В допълнение към стандартните функции на Excel можете също да създавате VBA макроси за автоматизиране на определени задачи.
Искате ли да научите за създаването на именувани диапазони и използването на VBA макроси в Excel? Ако е така, попаднали сте на правилното място. В тази публикация в блога ще обсъдим как да създадете именувани диапазони в Excel с помощта на VBA.
Ще предоставим и някои примери за това как можете да използвате именувани диапазони в собствената си работа. Да започваме!
Какво представлява наименуван диапазон в Microsoft Excel?
Използвайки именуван диапазон вместо препратка към колона/ред, можете да се обърнете към клетка или група от клетки с персонализирано име. Възможността да указвате данните във вашите клетки е огромно предимство от използването на именувани диапазони.
Ето бърз пример за това как можете да използвате именувани диапазони, за да разберете дали разходите за доставка са таксувани с цената на продукта:
- = (B7 + B5 * C4) * (1 + A3)
- =(Такса за доставка + Цена на продукта * Количество) * (1 + Данъчна ставка)
Опция номер две, както можете да видите, ви дава незабавна индикация дали цената на продуктите включва разходите за доставка. Това улеснява потребителя да разбере как работи формулата, без да се налага да губи време да преглежда клетките, за да разбере какво е какво.
Как да използвате именувани диапазони в Excel?
Като финансов анализатор може да се очаква да експериментирате с различни проценти. Всичко от данъчна ставка до прогнозиран процент на инфлация може да се използва като пример. Можете да използвате именувани диапазони, за да организирате променливи, които или се променят рядко (като месец или година), или ще останат статични за дълъг период (като процент на инфлация).
Ето списък с общи имена, които може да използвате често, когато работите с именувани диапазони в Excel:
- Дата на отчета
- година
- месец
- FcstID
- Данъчна ставка
- Необработени данни
Това са само някои от възможностите - можете да наименувате диапазон от всичко, което искате, което се придържа към правилата на Excel за именувани диапазони.
Създавайте уникални имена
Създаването на именуван диапазон е лесно. Всичко, което трябва да направите сега, е да изберете клетката(ите), към която искате да се обърнете, и да въведете име в полето за име.
Тъй като името ви не може да съдържа интервали, трябва да изписвате главна първата буква на всяка нова дума или да използвате долна черта (_), за да разделяте термините. За да финализирате установяването на наименования диапазон, натиснете клавиша ENTER, след като сте приключили с въвеждането на името.
Забележка : Всеки именуван диапазон, установен с полето за име, има обхват на работна книга. Това означава, че всеки работен лист във вашия файл на Excel има достъп до посочения диапазон.
Създаване на имена чрез диспечера на имена
Можете да променяте и създавате нови именувани диапазони в диспечера на имена (раздел Формули > група Дефинирани имена > бутон Мениджър на имена), ако желаете да персонализирате още повече именуваните диапазони.
Трябва да знаете, че Name Manager ви позволява да:
- Променете името на именуван диапазон, който вече съществува,
- Направете промяна на референтната формула,
- Определете обхвата на кои работни листове името може да бъде достъпно.
Сега, след като разбирате какво е именуван диапазон и как да го създадете, нека да разгледаме някои практически примери за това как можете да ги използвате в работата си.
Практически примери за това как да използвате именувани диапазони
Пример 1: Създаване на динамичен диапазон
Динамичният диапазон е именуван диапазон, който автоматично се настройва, за да включва нови данни, които се добавят към работния лист. Това е чудесен начин да спестите време, когато данните ви се променят често.
Пример 2: Създаване на 3D диапазон
3D диапазон е именуван диапазон, който препраща към данни в множество работни листове. Това може да ви спести много време, ако работите с големи количества данни, които са организирани по подобен начин в множество работни листове.
Пример 3: Създаване на именуван масив
Именуваният масив е вид динамичен диапазон, който се отнася до масив от стойности. Това може да бъде чудесен начин за препратка към данни, които са организирани в табличен формат.
Автоматизирайте именувани диапазони с VBA макроси
Сега, след като прегледахме бързия преглед на именуваните диапазони, нека разгледаме някои VBA макроси, които могат да ви помогнат да автоматизирате използването им.
Добавяне на именуван диапазон
Кодът на VBA по-долу демонстрира как да конструирате различни видове именувани диапазони.
|
Sub NameRange_Add() „ЦЕЛ: Различни начини за създаване на именуван диапазон „ИЗТОЧНИК: SoftwareKeep.com Затъмнена клетка като обхват Dim rng As Range Dim RangeName като низ Dim CellName като низ „Препратка към една клетка (обхват на работна книга) RangeName = "Цена" Име на клетка = "D7"
Задайте клетка = Работни листове ("Лист1"). Диапазон (Име на клетка) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell „Препратка към една клетка (обхват на работен лист) RangeName = "Година" Име на клетка = "A2"
Задайте клетка = Работни листове ("Лист1"). Диапазон (Име на клетка) Работни листове ("Sheet1"). Имена. Добавяне на име: = име на диапазон, препраща към: = клетка „Референция за диапазон от клетки (обхват на работна книга) RangeName = "myData" Име на клетка = "F9:J18"
Задайте клетка = Работни листове ("Лист1"). Диапазон (Име на клетка) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell „Таен наименуван диапазон (не се показва в диспечера на имена) RangeName = "Потребителско име" Име на клетка = "L45"
Задайте клетка = Работни листове ("Лист1"). Диапазон (Име на клетка) ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=клетка, Visible:=False End Sub |
Преминаване през именувани диапазони
Следващият макрокод на VBA демонстрира как да преминете през именуваните диапазони на вашата електронна таблица.
|
Sub NamedRange_Loop() „ЦЕЛ: Изтрийте всички именувани диапазони в активната работна книга „ИЗТОЧНИК: SoftwareKeep.com Dim nm като име „Преминете през всеки именуван диапазон в работната книга За всеки nm в ActiveWorkbook.Names Debug.Print nm.Name, nm.RefersTo Следваща нм
„Преминете през всеки именуван диапазон с обхват до конкретен работен лист За всеки nm в работни листове ("Лист1").Имена Debug.Print nm.Name, nm.RefersTo Следваща нм End Sub |
Изтриване на всички именувани диапазони
Тази функция на VBA ще ви помогне да изчистите голяма част от именувани диапазони в Excel, които вече не ви трябват във вашия проект.
|
Sub NamedRange_DeleteAll() „ЦЕЛ: Изтриване на всички наименувани диапазони в ActiveWorkbook (области за печат по избор) „ИЗТОЧНИК: SoftwareKeep.com Dim nm като име Dim DeleteCount As Long „Изтриване на PrintAreas също? UserAnswer = MsgBox("Искате ли да пропуснете областите за печат?", vbYesNoCancel) If UserAnswer = vbYes Then SkipPrintAreas = True If UserAnswer = vbCancel Then Exit Sub „Манипулатор на грешки в случай на грешка при изтриване на функцията При грешка GoTo Skip „Прегледайте всяко име и изтрийте За всеки nm в ActiveWorkbook.Names Ако SkipPrintAreas = True And Right(nm.Name, 10) = "Print_Area" Тогава GoTo Skip
„Манипулатор на грешки в случай на грешка при изтриване на функцията При грешка GoTo Skip
„Изтриване на именуван диапазон nm.Изтриване DeleteCount = DeleteCount + 1
Пропускане: „Нулиране на манипулатора на грешки При грешка GoTo 0 Следващия
„Докладване на резултата Ако DeleteCount = 1 Тогава MsgBox "[1] име беше премахнато от тази работна книга." Друго MsgBox "[" & DeleteCount & "] имена бяха премахнати от тази работна книга." Край Ако End Sub |
Изтриване на именувани диапазони с грешки
Само именувани диапазони с грешки ще бъдат изтрити с този VBA код. Тези проблеми могат да бъдат предизвикани от изтриването на работни листове или редове/колони.
|
Sub NamedRange_DeleteErrors() „ЦЕЛ: Изтриване на всички именувани диапазони с грешка #REF в ActiveWorkbook „ИЗТОЧНИК: SoftwareKeep.com Dim nm като име Dim DeleteCount As Long „Прегледайте всяко име и изтрийте За всеки nm в ActiveWorkbook.Names Ако InStr(1, nm.RefersTo, "#REF!") > 0 Тогава „Манипулатор на грешки в случай на грешка при изтриване на функцията При грешка GoTo Skip
„Изтриване на именуван диапазон nm.Изтриване DeleteCount = DeleteCount + 1 Край Ако Пропускане: „Нулиране на манипулатора на грешки При грешка GoTo 0 Следващия
„Докладване на резултата Ако DeleteCount = 1 Тогава MsgBox "[1] грешно име беше премахнато от тази работна книга." Друго MsgBox "[" & DeleteCount & "] грешни имена бяха премахнати от тази работна книга." Край Ако
End Sub |
TL; DR
- Именуваните диапазони са чудесен начин да организирате данните си в Excel.
- Можете да ги използвате за създаване на динамични диапазони, 3D диапазони и именувани масиви.
- Можете също така да автоматизирате използването на именувани диапазони с макроси на VBA.
Заключение
Excel е невероятен инструмент, който може да направи дори най-обикновените задачи вълнуващи за изпълнение с обширни функции за автоматизация. Ако искате да улесните живота си, научаването как да работите с разширени инструменти като Named Ranges с VBA е чудесен начин да започнете.
Надяваме се, че тази статия ви е помогнала да научите малко повече за това как да използвате именувани диапазони в Excel. Както винаги, ако имате въпроси, не се колебайте да ни оставите коментар по-долу!
Благодаря ви, че прочетохте.
Още нещо
Търсите още съвети? Разгледайте другите ни ръководства в нашия блог или посетете нашия Помощен център за богата информация как да отстраните различни проблеми.
Регистрирайте се за нашия бюлетин и отворете нашите публикации в блогове, промоции и кодове за отстъпка по-рано. Освен това вие ще сте първите, които ще научат за най-новите ни ръководства, сделки и други вълнуващи актуализации!
Препоръчани статии
» Как да добавяте и премахвате водещи нули в Excel
» Как да използвате формули „Ако клетката съдържа“ в Excel
» Как да заключите и отключите Scroll Lock в Excel?
Чувствайте се свободни да се свържете с въпроси или искания, които искате да покрием.