В тази статия ще научим как да създадем таблица и да я кръстим в 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 таблици
Когато табулираме данни в 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 не го позволява.
Но това не означава, че не може да се направи. Можете да именувате диапазони в таблица и след това да ги използвате за валидиране. Excel няма проблем с него.
Обхват на имените
Досега говорихме за именувани диапазони с обхват на работна книга. Какво? Не го ли обсъждахме? Добре, така че нека бързо разберем какъв е обхватът на именуваните диапазони.
Какъв е обхватът на диапазон от имена?
Обхватът на кладенеца определя къде може да бъде разпознат диапазон от имена. Всяко име не може да бъде разпознато извън неговия обхват. Например име в работна книга1 не може да бъде разпознато в различни работни книги. Excel предоставя две опции за обхват на имена на диапазони Работен лист и Работна книга.
Как да определим обхват на именования диапазон?
Когато създавате нов диапазон от имена, можете да видите раздел „Обхват:“. Кликнете върху падащото меню и изберете обхвата за диапазона от имена. Не можете да промените обхвата, след като сте създали именен диапазон. Така че по -добре го направете преди. По подразбиране това е работна книга.
Надявам се, че тази статия за това как да създадете таблица и да дадете име на таблицата в Excel е обяснителна. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да използвате пряк път за превключване между абсолютни и относителни препратки в Excel : F4 пряк път за преобразуване на абсолютни в относителни препратки и същото използване на пряк път за обратното в Excel.
Как да използвате клавишни комбинации за сливане и центриране в Excel : Използвайте Алт и след това следвайте з, м и ° С за сливане и центриране на клетки в Excel.
Как да изберете цяла колона и ред с помощта на клавишни комбинации в Excel : Използвайте Ctrl + Космос за да изберете цяла колона и Преместване + Космос за да изберете целия ред, като използвате клавишна комбинация в Excel
Поставете специален пряк път в Mac и Windows : В Windows, клавишната комбинация за специална паста е Ctrl + Алт + V. Докато в Mac използвайте Ctrl + КОМАНДА + V комбинация от клавиши, за да отворите специална паста диалогов прозорец в Excel.
Как да вмъкнете пряк път в Excel : Използвайте Ctrl + Преместване + = за да отворите Вмъкване диалогов прозорец, където можете да вмъкнете ред, колона или клетки в Excel.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.