В тази статия ще научим как да използваме имена във формула в Excel.
Сценарий :
Докато работите с Excel, сигурно сте чували за именовани диапазони в Excel. Може би от приятел, колега или някакъв онлайн урок. Много пъти съм го споменавал в статиите си. В тази статия ще научим за Named Range в Excel и ще изследваме всеки негов аспект.
Назовани диапазони в Excel
Формули -> Мениджър на имена -> Дефиниране на име
Е, наименуваните диапазони не са нищо друго освен някои Excel диапазони, на които е дадено някакво смислено име. Например, ако имате клетка, например B1, съдържаща ежедневни цели, можете да посочите тази клетка като конкретно „Target“. Сега можете да използвате „Target“, за да се обърнете към A1, вместо да пишете B1.
С две думи, Named range е просто именуване на диапазони.
Как да именуваме диапазон в Excel?
Определете името ръчно:
За да определите име на диапазон, можете да използвате пряк път CTRL + F3. Или можете да следвате тези стъпки.
- Отидете на Формула Раздел
- Намерете раздела Дефинирани имена и щракнете върху Дефиниране на имена. Това ще отвори Име Мангер.
- Щракнете върху Ново.
- Въведете Името.
- Изберете обхват (работна книга или лист)
- Напишете коментар, ако искате.
- В полето Отнася се до полето напишете препратката или изберете диапазон с помощта на мишката.
- Натиснете OK. Готово е.
Сега можете да се обърнете към него, като просто напишете името му.
Има някои правила, които трябва да спазвате, когато създавате имена. Ето няколко.
- Имената не трябва да започват от цифри или специални знаци, освен долната черта (_) и обратната черта (\).
- Имената не могат да съдържат интервали и специални знаци, освен _ и \.
- Диапазонът не трябва да се посочва като препратки към клетки. Например A1, B1 или AZ100 и др.
- Не можете да посочите диапазон като „r“ и „c“, защото те са запазени за препратки към редове и колони.
- Два именовани диапазона не могат да имат едно и също име в работна книга.
- Същият диапазон може да има няколко имена.
През повечето време ще работите с таблици със структурирани данни. Те ще имат колони и редове с заглавия на колони и заглавия на редове. В повечето случаи тези имена имат значение за данните и бихте искали да кръстите обхвата си като тези заглавия на колони. Excel предоставя инструмент за автоматично именоване на диапазони с помощта на заглавия. Следвай тези стъпки.
- Диапазоните, които искате да посочите като заглавия
- Натиснете CTRL+SHIFT+F3, или Намерете раздела Дефинирани имена в раздела Формула и щракнете върху Създаване от селекция.
- Ще се появи полето за опции по -долу. Избрах само Top Row, тъй като искам да кръстя този диапазон като заглавие и не искам да именувам редове.
- Щракнете върху OK.
Сега всяка колона е посочена като заглавие. Всеки път, когато въведете формула, тези имена ще бъдат изброени като опции, до които можете да получите достъп.
Когато табулираме данни в Excel с помощта на CTRL + T, заглавието на колоната автоматично се присвоява като име на съответната колона. Трябва да проучите таблиците на Excel и техните предимства.
Е, ще има моменти, когато бихте искали да видите всички налични именовани диапазони в работната книга. За да видите всички диапазони с имена Натиснете CTRL+F3. Или можете да отидете на Раздел „Формула“ > Мениджър на имена. Това ще изброи всички именовани диапазони, които са налични в работната книга. Можете да редактирате наличните диапазони с имена, да ги изтривате, да добавяте нови имена.
Няколко имена в един диапазон
Excel позволява на потребителите да назовават един и същ диапазон с различни имена. Например диапазон A2: A10 може да бъде наречен „Клиенти“ и „Клиенти“ едновременно. И двете имена ще се отнасят до същия диапазон A2: A10.
Но не можете да имате еднакви имена за два различни диапазона. Това е добре. Това премахва вероятността от неяснота.
Вземете списък с наименувани диапазони на лист
Така че, ако искате да имате списък с именовани диапазони и диапазоните, които те обхващат, можете да използвате този пряк път, за да ги поставите на място в лист.
- Изберете клетка, където искате да получите списък с именувани диапазони.
- Натиснете F3. Това ще отвори a списък за поставяне диалогов прозорец.
- Кликнете върху пастата списък бутон.
- Списъкът ще бъде поставен върху избрани клетки и нататък.
Ако щракнете двукратно върху името на имената на диапазоните в полето за име на паста, те ще бъдат записани като формули в клетката. Опитай.
Актуализирайте ръчно назованите диапазони
Е, когато вмъкнете клетка в именен диапазон, тя се актуализира автоматично и я разширява. Но ако добавите данни в края на таблицата, ще трябва да актуализирате посочения диапазон. За да актуализирате Named Ranges, следвайте тези стъпки.
- Натиснете CTRL+F3 за да отворите мениджър на имена.
- Щракнете върху посочения диапазон, който искате да редактирате. Щракнете върху Редактиране.
- В Отнася се до колона, въведете диапазона, до който искате да разширите, и натиснете OK.
И е направено. Това е ръчно актуализиране на именувани диапазони. Въпреки това можем да го направим динамичен, като използваме някои формули.
Актуализирайте динамично назованите диапазони
Разумно е да направите вашите именовани диапазони динамични, така че да не се налага да ги редактирате винаги, когато данните ви препълват предварително дефинирания диапазон.
Разгледах го в отделна статия, наречена Dynamic Named диапазони. Можете да научите и разберете подробно ползите от него тук.
Изтриване на именовани диапазони
Когато изтриете сумарната част от именувания диапазон, тя автоматично регулира своя обхват. Но когато изтриете целия диапазон от имена, изчезва от списъка с имена. Всяка формула, в зависимост от тези диапазони, ще покаже грешка #REF или ще даде неправилен изход (функции за броене).
По някаква причина, ако искате да изтриете именовани диапазони, просто следвайте тези стъпки.
- Натиснете CTRL+F3. Мениджърът на имена ще се отвори.
- Изберете Намерени диапазони, които искате да изтриете.
- Щракнете върху бутона Delete или натиснете бутона Delete на клавиатурата.
Внимание: Преди да изтриете посочените диапазони, уверете се, че няма формули, които зависят от тези имена. Ако има такива, първо ги преобразувайте в диапазони. В противен случай ще видите грешка #REF.
Изтриване на имена с грешки
Excel предоставя инструменти за изтриване на имена, които имат само грешки. Не е необходимо да идентифицирате всеки от тях сами. За да изтриете имена с грешки, изпълнете следните стъпки:
- Отворете Мениджър на имена (CTRL+F3).
- Кликнете върху падащия филтър в горния десен ъгъл.
- Изберете „Име с грешки“
- Изберете всички и натиснете бутона за изтриване.
И ги няма. Всички имена с грешки ще бъдат изтрити незабавно от записа.
Назовани диапазони с формули
Най -доброто използване на наименувани диапазони се изследва с формули. Формулите стават наистина гъвкави и четими с именовани диапазони. Да видим как.
Лесни за писане формули
Да кажем, че сте кръстили диапазон като „Елементи“. Сега списъкът с елементи, който искате да броите „Моливи“. С имена е лесно да се напише тази формула COUNTIF. Просто пишете
= COUNTIF (Вещ, "Молив")
Веднага след като напишете началната скоба на формулата, ще се появи списъкът с наличните именовани диапазони
Без име бихте написали дайте диапазон на функцията COUNTIF на Excel, за която може да се наложи първо да разгледате диапазона, след което да изберете диапазона или да го въведете във формула.
Excel обслужва наличните диапазони от имена.
Назованите диапазони се показват като предложения, когато въвеждате някоя буква. Както Excel показва списъка с формули. Например, ако въведете = u, всяка формула и именуван диапазон ще бъдат показани, започвайки с u, за да можете да ги използвате лесно.
Направете константи, използвайки именовани диапазони
Досега научихме за именуване на диапазони, но всъщност можете да назовавате и стойности. Например, ако името на вашия клиент е Sunder Pichai, можете да направите име „Client“ и то се отнася до „Sundar Pichai“. Сега, когато пишете = Client във всяка клетка, ще се показва Sundar Pichai.
Не само текст, но и можете да зададете числа като постоянни за работа. Например определяте цел. Или стойността на нещо, което няма да се промени.
Абсолютни и относителни препратки с наименувани диапазони
Позоваването с именовани диапазони в е много гъвкаво. Например, ако напишете името на именован диапазон в относителна клетка към посочения диапазон, той ще се държи като относителна препратка. Вижте изображението по -долу.
Но когато го използвате с формули, той ще се държи като абсолютен. През повечето време ще ги използвате с формули, така че можете да кажете, че те са по подразбиране абсолютни но всъщност те са гъвкави.
Но можем да ги направим и относителни.
Как да направите относителни именовани диапазони в Excel?
Да речем, ако искам да кръстя диапазон „Befor“, който ще се отнася до клетката вляво, където и да е написана. Как да направя това? Следвай тези стъпки:
- Натиснете 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 тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Полето за име в Excel : Полето за име на Excel не е нищо друго освен малка област за показване в горния ляв ъгъл на Excel лист, която показва името на активната клетка или диапазони в Excel. Можете да преименувате клетка или масив за справки.
Как да получите име на лист на работен лист в Excel : Функцията CELL в Excel ви дава информация относно всеки работен лист, като колона, съдържание, име на файл и др. Научете как да получите името на листа с помощта на функцията CELL тук.
Как да получите последователен номер на ред в Excel: Понякога трябва да получим последователен номер на ред в таблица, може да е за сериен номер или нещо друго. В тази статия ще научим как да номерираме редове в Excel от началото на данните.
Увеличете число в текстов низ в Excel: Ако имате голям списък от елементи и трябва да увеличите последния номер на текста на стария текст в Excel, ще ви е необходима помощ от двете функции TEXT и RIGHT.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.