Всичко за именовите диапазони на Excel - Име на диапазона на Excel

Anonim

Докато работите с Excel, сигурно сте чували за именовани диапазони в Excel. Може би от приятел, колега или някакъв онлайн урок. Дори аз много пъти го споменавах в моите статии. В тази статия ще научим за Намерените диапазони в Excel и ще изследваме всеки негов аспект.

Какви са именования диапазон в Excel?

Е, наименуваните диапазони не са нищо друго освен някои Excel диапазони, които са маркирани с някакво смислено име. Например, ако имате клетка, например B1, съдържа ежедневната цел, можете да я кръстите като конкретно „Target“. Сега можете да използвате „Target“, за да се обърнете към A1, вместо да пишете B1.
С две думи, Named range е просто именуване на диапазони.

Как да именуваме диапазон в Excel?

Определете името ръчно:

За да определите име за диапазон, можете да използвате клавишната комбинация CTRL+F3. Или можете да следвате тези стъпки.

    • Отидете на раздела Формула
    • Намерете раздела Дефинирани имена и щракнете върху Дефиниране на имена. Ще се отвори Name Manger.

    • Щракнете върху Ново.
    • Въведете Името.

  • Изберете обхват (работна книга или лист)
  • Напишете коментар, ако искате.
  • В полето Отнася се до полето напишете препратката или изберете диапазон с помощта на мишката.
  • Натиснете OK. Това е направено.

Сега можете да се обърнете към него, като просто напишете името му.
Има някои правила, които трябва да спазвате, когато създавате имена. Те са

  1. Имената не трябва да започват от цифри или специални знаци, освен долната черта (_) и обратната черта (\).
  2. Имената не могат да съдържат интервали и специални знаци, освен _ и \.
  3. Диапазонът не трябва да се нарича като препратки към клетки. Например имената A1, B1 или AZ100 и т.н. са невалидни.
  4. Не можете да посочите диапазон като „r“ и „c“, защото те са запазени за препратки към редове и колони.
  5. Два именовани диапазона не могат да имат едно и също име в работна книга.
  6. Същият диапазон може да има няколко имена.

Определете името автоматично

Е, през повечето време ще работите със таблица със структурирани данни. Те ще имат колони и редове с заглавия на колони и заглавия на редове. В повечето случаи тези имена имат значение за данните и бихте искали да кръстите обхвата си като тези заглавия на колони. Excel предоставя инструмент за автоматично наименуване на диапазони, използвайки заглавия. Следвай тези стъпки.

    • Диапазоните, които искате да посочите като заглавия
    • Натиснете CTRL+SHIFT+F3, или Намерете раздела Дефинирани имена в раздела Формула и щракнете върху Създаване от селекция.

    • Ще се появи полето за опции по -долу. Избрах Top Row само защото искам да кръстя този диапазон като заглавие и не искам да именувам редове.

  • Щракнете върху OK.

Сега всяка колона е посочена като заглавие. Всеки път, когато пишете, ще въведете формула, това име ще бъде посочено в опцията за използване.

Именуване на диапазон с помощта на Excel таблици

Когато организираме данни като таблица в Excel, използвайки CTRL + T, заглавията на колоните автоматично се присвояват като име на съответната колона. Трябва да проучите таблиците на Excel и техните предимства.

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

Е, ще има моменти, когато бихте искали да видите всички налични именовани диапазони в работната книга. За да видите всички диапазони с имена Натиснете CTRL+F3. Или можете да отидете на Раздел Формула> Мениджър на имена. Той ще изброи всички именовани диапазони, които са налични в работната книга. Можете да редактирате наличните диапазони с имена, да ги изтривате, да добавяте нови имена.

Няколко имена в един диапазон

Excel позволява на потребителите да назовават един и същ диапазон с различни имена. Например диапазон A2: A10 може да бъде наречен „Клиенти“ и „Клиенти“ едновременно. И двете имена ще се отнасят до същия диапазон A2: A10.
Но не можете да имате еднакви имена за два различни диапазона. Това премахва вероятността от неяснота.

Вземете списък с наименувани диапазони на лист

Така че, ако искате да имате списък с именовани диапазони и диапазоните, които те покриват, можете да използвате този пряк път, за да ги поставите на място в лист.

    • Изберете клетка, където искате да получите списъка с именувани диапазони.
    • Натиснете F3. Това ще отвори a Paste Име диалогов прозорец.
    • Кликнете върху списък за поставяне бутон.

  • Списъкът ще бъде поставен върху избраната клетка и нататък.


Ако щракнете двукратно върху името на имената на диапазоните в полето за име на паста, те ще бъдат записани като формули в клетката. Опитай.

Актуализирайте ръчно назованите диапазони

Е, когато вмъкнете клетка в именован диапазон, тя се актуализира автоматично и я разширява. Но ако добавите данни в края на таблицата, ще трябва да актуализирате посоченото поле. За да актуализирате Named Ranges, изпълнете следните стъпки.

  • Натиснете CTRL+F3, за да отворите мениджъра на имена.
  • Щракнете върху посочения диапазон, който искате да редактирате. Щракнете върху Редактиране.
  • В Отнася се до колона, въведете диапазона, до който искате да разширите, и натиснете OK.

И е направено. Това е ръчно актуализиране на именувани диапазони. Въпреки това можем да го направим динамичен, като използваме някои формули.

Актуализирайте динамично назованите диапазони

Разумно е да направите вашите именовани диапазони динамични, така че да не се налага да ги редактирате винаги, когато данните ви препълват предварително дефинирания диапазон.
Разгледах го в отделна статия, наречена Dynamic Named диапазони. Можете да научите и разберете подробно ползите от него тук.

Изтриване на именовани диапазони

Когато изтриете част от именувания диапазон, той автоматично регулира своя обхват. Но когато изтриете целия диапазон от имена, изчезва от списъка с имена. Всяка формула, зависима от тези диапазони, ще покаже грешка #REF или ще даде неправилен изход (функции за броене).
По някаква причина, ако искате да изтриете именовани диапазони, следвайте тези стъпки.

  • Натиснете CTRL+F3. Мениджърът на имена ще се отвори.
  • Изберете Намерени диапазони, които искате да изтриете.
  • Щракнете върху бутона Delete или натиснете бутона Delete на клавиатурата.


Внимание: Преди да изтриете посочените диапазони, уверете се, че няма формули, които зависят от тези имена. Ако има такива, първо ги преобразувайте в диапазони. В противен случай ще видите грешка #REF.

Изтриване на имена с грешки

Excel предоставя инструмент за премахване на имена, които имат само грешки. Не е необходимо да идентифицирате всеки от тях сами. За да изтриете имена с грешки, изпълнете следните стъпки:

    • Отворете Мениджър на имена (CTRL+F3).
    • Кликнете върху падащото меню Филтър в горния десен ъгъл.
    • Изберете „Име с грешки“

  • Изберете всички и натиснете бутона за изтриване.

И ги няма. Всички имена с грешки ще бъдат изтрити незабавно от записа.

Назовани диапазони с формули

Най -доброто използване на наименувани диапазони се преживява с формулите. Формулите стават по -гъвкави и четими с Named Ranges. Да видим как.

Лесни за писане формули

Да кажем, че сте кръстили диапазон като „Елементи“. Сега списъкът с елементи, който искате да броите „Моливи“. С име е лесно да напишете тази формула COUNTIF. Просто пишете

= COUNTIF (Елемент, "Молив")

Веднага щом напишете началната скоба на формулата, ще се появи списъкът с наличните именовани диапазони. Без име бихте написали gi COUNTIF функция на Excel с диапазони, за които може да се наложи първо да разгледате диапазона, след което да изберете диапазона или да го въведете във формулата.

Excel обслужва наличните диапазони от имена.

Имената на диапазони се показват като предложения, когато въведете някоя буква след = знак. Същото като Excel показва списъка с формули. Например, ако въведете = u, всеки метод и именован диапазон ще се показват, започвайки с u, за да можете да ги използвате лесно.

Направете константи, използвайки именовани диапазони

Досега научихме за именуване на диапазони, но всъщност можете да назовавате и стойности. Например, ако името на Вашия клиент е Sunder Pichai, можете да направите име „Client“ и то се отнася до „Sundar Pichai“. Сега, когато пишете = Client в която и да е клетка, ще се показва Sundar Pichai.


Не само текст, но и можете да зададете номер като константа за работа. Например определяте цел. Или стойността на нещо, което няма да се промени.
Абсолютни и относителни препратки с наименувани диапазони

Позоваването с именовани диапазони в е много гъвкаво. Например, ако напишете името на именован диапазон в относителна клетка, той ще се държи като съответна препратка. Вижте изображението по -долу.

Но когато го използвате с формули, той ще се държи като абсолютен. Е, през повечето време ще ги използвате с формули, така че можете да кажете, че те са по подразбиране абсолютни, но всъщност те са гъвкави.

Но можем да ги направим и относителни.

Как да направите относителни именовани диапазони в Excel?

Да речем, ако искам да кръстя диапазон „Преди“, който ще се отнася до клетката вляво, където и да е написана. Как да направя това? Следвай тези стъпки:

  • Натиснете CTRL+F3
  • Щракнете върху Ново
  • Въведете „Befor“ в раздела „Име“.
  • В секцията „Отнася се до:“ напишете адреса на клетката вляво. Например, ако сте в клетка B1, напишете „= A2“ в раздела „Отнася се до:“. Уверете се, че няма знак $.


Сега, където и да напишете „Befor” във формула, тя ще се отнася до клетката, оставена от нея.

Тук използвах преди във функция COLUMN. Формулата връща номера на колоната в лявата клетка, където е записана. За мое учудване, A1 показва номера на колоната на последната колона. Което означава, че листът е кръгъл. Мислех, че ще покаже грешка #REF.

Дайте име на често използваните формули?

Сега това е невероятно. Много пъти използвате една и съща формула отново и отново в работен лист. Например може да искате да проверите дали име е в списъка ви с клиенти или не. И тази нужда може да възникне много пъти. За това всеки път ще пишете една и съща сложна формула.

= АКО (COUNTIF (Клиент, I3), "В списъка", "Не в списъка")

Какво ще кажете, ако просто въведете „= IsInCustomer“ в клетка и тя ще ви покаже дали стойността в лявата клетка е в списъка с клиенти или не?

Например, тук съм подготвил таблица. Сега просто искам да напиша „= IsInCustomer“ в J5 и бих искал да видя дали стойността в I5 е в списъка с клиенти или не. За да направите това, следвайте тези стъпки.

    • Натиснете CTRL+F3
    • Щракнете върху Ново
    • В Name напишете „IsInCustomer“
    • В „Отнася се до“ напишете формулата си. = АКО (COUNTIF (Клиент, I5), "В списъка", "Не в списъка")

  • Натиснете бутона OK.

Сега, където въведете „IsInCustomer“, той ще провери стойността в лявата клетка в списъка с клиенти.

Това ви спира да повтаряте себе си отново и отново.

Приложете именовите диапазони към формулите

Толкова много пъти ние дефинираме имена на нашите диапазони, след като вече сме написали формули въз основа на диапазони. Например имам обща цена като клетки = E2*F2. Как можем да го променим Единици*Unit_Cost.

    • Изберете формулите.
    • Отидете на раздела формула. Кликнете върху падащото меню Дефиниране на име.

  • Кликнете върху Прилагане на имена.
  • Ще се появи списък с всички именовани диапазони. Изберете правилните имена и натиснете OK.


И сега имената се прилагат. Можете да го видите в лентата с формули.

Лесни за четене формули с наименувани диапазони

Както видяхте, наименуваните диапазони улесняват четенето на формулите. Ако напиша = COUNTIF („A2: A100“, B2), никой няма да разбере какво се опитвам да преброя, докато не види данните или някой не им го обясни.

Но ако напиша = COUNTIF (регион, „изток“), повечето потребители веднага ще разберат, че броим появата на „изток“ в региона с име.

Преносими формули

Назованите диапазони правят много лесно да копирате и поставяте формули, без да се притеснявате за промяна на препратките. И можете да вземете една формула от една работна книга в друга и тя ще работи добре, докато и освен ако работната книга на дестинацията няма същите имена.

Например, ако имате формула = COUNTIF (регион, изток) в таблицата за разпространение и имате друга работна книга клиенти който също има именуван диапазон „Регион“. Сега, ако копирате тази формула директно навсякъде в работната книга, тя ще ви покаже правилната информация. Структурата на данните няма да има значение. Няма значение къде, по дяволите, е тази колона в работната ви книга. Това ще работи правилно.

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

Придвижвайте се лесно в работната книга

Улеснява се навигирането в работна книга с именувани диапазони. Просто трябва да въведете име на име в полето за име. Excel ще ви отведе до диапазона, без значение къде се намирате в работната книга. Като се има предвид, че посоченият диапазон е от обхвата на работната книга.
Например, ако сте на лист 10 и искате да получите списък с клиенти и не знаете на кой лист е той. Просто отидете в полето за име и напишете „клиент“. Ще бъдете насочени към посочения диапазон за част от секундата.


Това ще намали усилията за запомняне на диапазоните.

Навигирайте с помощта на хипервръзки с именован диапазон

Когато листът ви е голям и често преминавате от една точка в друга, обичате да използвате хипервръзки, за да се движите лесно. Добре наречените диапазони могат да работят перфектно с хипервръзки. За да добавите хипервръзки с именовани диапазони, следвайте тези стъпки.

    • Изберете клетка, където искате хипервръзка
    • Натиснете CTRL+K или отидете на Insert Tab> HyperLink, за да отворите диалоговия прозорец Insert Hyperlink.

    • Щракнете върху Място в този документ.
    • Превъртете надолу, за да видите наличните именовани диапазони под Дефинирани имена

  • Изберете именования диапазон, за да вмъкнете хипервръзка към този диапазон.

И свършено. Имате хипервръзка към избрания от вас именен диапазон. Използвайки това, можете да създадете индекс с именовани диапазони, който можете да видите и щракнете, за да отидете директно до тях. Това ще направи вашата работна книга наистина лесна за употреба.

Именован диапазон и проверка на данни

Назованите диапазони и валидирането на данни са направени един за друг. Назованите диапазони правят валидирането на данните силно персонализирано. Става много по -лесно да добавите валидиране от списък, използвайки именен диапазон. Нека да видим как…

    • Отидете в раздела Данни
    • Кликнете върху Проверка на данни
    • Изберете Списък в секцията „Разрешаване:“
    • В секцията „Източник:“ въведете „= клиент“ (напишете какъвто именуван диапазон имате)

  • Натиснете OK

Сега тази клетка ще има имена на клиенти, които са част от диапазона с имена на клиенти. Лесно, нали.

Зависима или каскадна валидация на данни с наименувани диапазони

Какво ще стане, ако искате каскадно или зависимо валидиране на данни. Например, ако искате падащ списък с категории, плодове и зеленчуци. Сега, ако изберете плодове, следващото падащо меню трябва да показва опция само плодове, а ако изберете Зеленчук, тогава само зеленчуци.

Това може лесно да се постигне, като се използват именовани диапазони. Научи как.

  • Зависимо падащо меню с помощта на Named Range
  • Други начини за каскадно валидиране на данни

Няма валидиране на данни с имена на таблични данни

Въпреки че таблиците на Excel предоставят структурирани имена, те не могат да се използват с валидиране на данни и условно форматиране. Не знам защо Excel не го позволява.
Но това не означава, че не може да се направи. Можете да именувате диапазони в таблица и след това да ги използвате за валидиране. Excel няма проблем с него.

Обхват на имените

Досега говорихме за именувани диапазони с обхват на работна книга. Какво? Не го ли обсъждахме? Добре, така че нека бързо разберем какъв е обхватът на именуваните диапазони.

Какво представлява обхватът на диапазон от имена?

Обхватът на кладенеца определя къде може да бъде разпознат диапазон от имена. Всяко име не може да бъде разпознато извън обхвата му. Например име в работна книга1 не може да бъде разпознато в различна работна книга. Excel предоставя две опции за обхват на имена на диапазони Работен лист и Работна книга.

Как да определим обхват на именования диапазон?

Когато създавате нов диапазон от имена, можете да видите раздел „Обхват:“. Кликнете върху падащото меню и изберете обхвата за диапазона от имена. Не можете да промените обхвата, след като сте създали именен диапазон. Така че по -добре го направете преди. По подразбиране това е работна книга.

Обхват на работната тетрадка

Това е обхватът по подразбиране за именуван диапазон. Име, определено с обхват на работна книга, може да се използва в цялата работна книга, в която е дефинирана (не в други работни книги).
Всички горепосочени примери имат обхват на работна книга.

Обхват на работния лист

Име, дефинирано с обхват на работен лист, може да се използва само в дефиниран работен лист. Например, ако дефинирам „Общо“ за обща клетка с обхват на sheet1. Тогава общата сума ще бъде призната само на лист 1. Други листове няма да разпознаят.

Искам обхват на Excel

Excel няма глобален или кажете Excel обхват. Всъщност бих искал да дефинирам някои имена, които могат да бъдат разпознати във всички работни книги в моята система. Ако някой знае как можем да направим това, нека ми каже.

Редактиране на обхвата след създаване на имена

Не можеш. Excel не ви позволява да редактирате обхвата на именуван диапазон, след като създадете. Тъй като всички именовани диапазони на лист по подразбиране са с обхват на работна книга и може да искате да промените обхвата им на лист.
За да направите това, просто направете копие на този лист и Excel ще направи всяко име на този лист локално, за да избегне неясноти. Вече можете да изтриете оригиналния лист, ако желаете.

Изрежете диапазона на имената на пастата

Когато изрежете и поставите именован диапазон от една дестинация на друга, препратката се променя на ново местоположение. Например, ако имате именован диапазон „Клиент“ в A2: A10 и го изрежете и поставете в B2: B10, тогава името на клиента ще се отнася до ново местоположение B2: B10.

Динамични именовани диапазони в Excel

17 невероятни функции на таблици в Excel

Популярни статии:

50 преки пътища в Excel за повишаване на вашата производителност

Как да използвате функцията VLOOKUP в Excel

Как да използвате функцията COUNTIF в Excel

Как да използвате функцията SUMIF в Excel