Как да създадете именовани диапазони в Excel (Ръководство стъпка по стъпка)

Какво има в името?

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

В този урок ще научите как да създавате именовани диапазони в Excel и как да го използвате, за да спестите време.

Именовани диапазони в Excel - Въведение

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

Нали?

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

Сега, вместо да използвате препратка към клетка (като A1 или A1: A10), можете просто да използвате името, което сте му присвоили.

Да предположим например, че имате набор от данни, както е показано по -долу:

В този набор от данни, ако трябва да се позовавате на диапазона, който има Дата, ще трябва да използвате A2: A11 във формули. По същия начин за представители на продажбите и продажби ще трябва да използвате B2: B11 и C2: C11.

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

Назованите диапазони на Excel улесняват препращането към набори от данни в Excel.

Можете да създадете именован диапазон в Excel за всяка категория данни и след това да използвате това име вместо препратките към клетки. Например датите могат да бъдат кръстени „Дата“, данните на представителите по продажбите могат да бъдат наречени „SalesRep“, а данните за продажбите - „Продажби“.

Можете също да създадете име за една клетка. Например, ако имате процент от комисионната за продажби в клетка, можете да посочите тази клетка като „Комисионна“.

Предимства от създаването на именовани диапазони в Excel

Ето предимствата от използването на именувани диапазони в Excel.

Използвайте имена вместо препратки към клетки

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

Например, можете да използвате = SUM (SALES) вместо = SUM (C2: C11) за горния набор от данни.

Разгледайте формулите, изброени по -долу. Вместо да използвам препратки към клетки, аз използвах именованите диапазони.

• Брой продажби със стойност над 500: = COUNTIF (Продажби,>> 500 ″)
• Сума от всички продажби, извършени от Tom: = SUMIF (SalesRep, „Tom”, Sales)
• Комисионна, спечелена от Джо (продажбите от Джо, умножени по процент комисионна):
  = SUMIF (SalesRep, „Joe“, Sales)*Комисионна

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

Няма нужда да се връщате към набора от данни, за да изберете клетки

Друго съществено предимство от използването на Named Ranges в Excel е, че не е нужно да се връщате назад и да избирате диапазоните от клетки.

Можете просто да въведете няколко азбуки от този именен диапазон и Excel ще покаже съответстващите именовани диапазони (както е показано по -долу):

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

Използвайки именовани диапазони в Excel, можете да направите формулите на Excel динамични.

Например, в случай на комисионна за продажби, вместо да използвате стойността 2,5%, можете да използвате именования диапазон.

Сега, ако по -късно вашата компания реши да увеличи комисионната до 3%, можете просто да актуализирате именования диапазон и всички изчисления автоматично ще се актуализират, за да отразят новата комисионна.

Как да създадете именовани диапазони в Excel

Ето три начина за създаване на именовани диапазони в Excel:

Метод #1 - Използване на Дефиниране на име

Ето стъпките за създаване на именовани диапазони в Excel с помощта на Дефиниране на име:

• Изберете диапазона, за който искате да създадете именован диапазон в Excel.
• Отидете на Формули -> Определете име.
• В диалоговия прозорец Ново име въведете Името, което искате да присвоите на избрания диапазон от данни. Можете да посочите обхвата като цялата работна книга или конкретен работен лист. Ако изберете конкретен лист, името няма да е налично на други листове.
• Щракнете върху OK.

Това ще създаде именован диапазон SALESREP.

Метод #2: Използване на полето за име

• Изберете диапазона, за който искате да създадете име (не избирайте заглавки).
• Отидете в полето за име вляво от лентата с формули и въведете името на името, с което искате да създадете именования диапазон.
• Имайте предвид, че създаденото тук име ще бъде достъпно за цялата работна книга. Ако искате да го ограничите до работен лист, използвайте Метод 1.

Метод #3: Използване на опцията „Създаване от избор“

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

Например, в набора от данни по -долу, ако искате бързо да създадете три именовани диапазона (Date, Sales_Rep и Sales), тогава можете да използвате метода, показан по -долу.

Ето стъпките за бързо създаване на именувани диапазони от набор от данни:

• Изберете целия набор от данни (включително заглавките).
• Отидете на Формули -> Създаване от селекция (Клавишна комбинация - Control + Shift + F3). Той ще отвори диалоговия прозорец „Създаване на имена от избор“.
• В диалоговия прозорец Създаване на имена от избор проверете опциите, където имате заглавките. В този случай избираме горния ред само като заглавката е в горния ред. Ако имате заглавки както в горния ред, така и в лявата колона, можете да изберете и двете. По същия начин, ако вашите данни са подредени, когато заглавките са само в лявата колона, тогава проверявате само опцията Лява колона.

Това ще създаде три именовани диапазона - Date, Sales_Rep и Sales.

Имайте предвид, че той автоматично взема имена от заглавките. Ако има интервал между думите, той вмъква долна черта (тъй като не можете да имате интервали в именувани диапазони).

Конвенция за именуване на именовани диапазони в Excel

Има някои правила за именуване, които трябва да знаете, докато създавате именовани диапазони в Excel:

• Първият знак в именования диапазон трябва да бъде буква и знак за подчертаване (_) или обратна наклонена черта (\). Ако е нещо друго, ще покаже грешка. Останалите знаци могат да бъдат букви, цифри, специални знаци, точка или долна черта.
• Не можете да използвате имена, които също представляват препратки към клетки в Excel. Например, не можете да използвате AB1, тъй като той също е препратка към клетка.
• Не можете да използвате интервали, докато създавате именувани диапазони. Например не можете да имате представител за продажби като именен диапазон. Ако искате да комбинирате две думи и да създадете именован диапазон, използвайте долна черта, точка или главни знаци, за да го създадете. Например можете да имате Sales_Rep, SalesRep или SalesRep.
  • Докато създава именувани диапазони, Excel третира главни и малки букви по същия начин. Например, ако създадете именен диапазон SALES, тогава няма да можете да създадете друг именован диапазон, като „продажби“ или „Продажби“.
• Именован диапазон може да бъде с дължина до 255 знака.

Твърде много именовани диапазони в Excel? Не се притеснявай

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

Ами ако не си спомняте името на имената, която сте създали?

Не се притеснявай - ето няколко полезни съвета.

Получаване на имената на всички именовани диапазони

Ето стъпките за получаване на списък с всички именовани диапазони, които сте създали:

• Отидете в раздела Формули.
• В групата Определено име, щракнете върху Използване във формула.
• Кликнете върху „Поставяне на имена“.

Това ще ви даде списък с всички именовани диапазони в тази работна книга. За да използвате именован диапазон (във формули или клетка), щракнете двукратно върху него.

Показване на съответстващите наименувани диапазони

• Ако имате представа за Името, въведете няколко начални знака и Excel ще покаже падащо меню на съвпадащите имена.

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

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

• Отидете в раздела Формули и кликнете върху Мениджър на имена.
• Диалоговият прозорец Мениджър на имена ще изброи всички именовани диапазони в тази работна книга. Щракнете двукратно върху именования диапазон, който искате да редактирате.
• В диалоговия прозорец Редактиране на име направете промените.
• Щракнете върху OK.
• Затворете диалоговия прозорец Мениджър на имена.

Полезни преки пътища за имена (Силата на F3)

Ето някои полезни клавишни комбинации, които ще ви бъдат полезни, когато работите с именовани диапазони в Excel:

• За да получите списък с всички именовани диапазони и да го поставите във Формула: F3
• За да създадете ново име с помощта на диалоговия прозорец за мениджър на имена: Control + F3
• За да създадете именовани диапазони от селекция: Control + Shift + F3

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

Досега в този урок създадохме статични именовани диапазони.

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

Например, ако A1: A10 е посочен като „Продажби“, той винаги ще се отнася до A1: A10.

Ако добавите още данни за продажбите, тогава ще трябва да отидете ръчно и да актуализирате препратката в посочения диапазон.

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

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

Например, например, ако добавя две допълнителни точки с данни за продажбите, динамичен именован диапазон автоматично ще се отнася до A1: A12.

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

Нека да видим как да създадем динамични именовани диапазони в Excel.

Да предположим, че имаме данни за продажбите в клетка A2: A11.

Ето стъпките за създаване на динамични именовани диапазони в Excel:

1. 1. Отидете в раздела Формула и щракнете върху Дефиниране на име.
   2. В диалоговия прозорец Ново име въведете следното:
      • Име: Продажби
      • Обхват: Работна тетрадка
      • Отнася се до: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” и ””))
   3. Щракнете върху OK.

Свършен!

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

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

За да обясните как работи това, трябва да знаете малко повече за функцията Excel INDEX.

Повечето хора използват INDEX, за да върнат стойност от списък въз основа на номера на ред и колона.

Но функцията INDEX има и друга страна.

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

Например, ето формулата, която сме използвали за създаване на динамичен именен диапазон:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

ИНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Тази част от формулата се очаква да върне стойност (която би била 10 -тата стойност от списъка, като се има предвид, че има десет елемента).

Въпреки това, когато се използва пред препратка (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) връща препратката към клетката вместо стойността.

Следователно тук се връща = $ A $ 2: $ A $ 11

Ако добавим две допълнителни стойности към колоната за продажби, тя ще върне = $ A $ 2: $ A $ 13

Когато добавяте нови данни към списъка, функцията Excel COUNTIF връща броя на непразни клетки в данните. Този номер се използва от функцията INDEX за извличане на препратката към клетката на последния елемент в списъка.

Забележка:

• Това ще работи само ако в данните няма празни клетки.
• В горния пример съм присвоил голям брой клетки (A2: A100) за формулата с именования диапазон. Можете да коригирате това въз основа на набора от данни.

Можете също да използвате функцията OFFSET за създаване на динамични именовани диапазони в Excel, но тъй като функцията OFFSET е нестабилна, тя може да доведе до бавна работна книга на Excel. INDEX, от друга страна, е полулетлив, което го прави по-добър избор за създаване на Dynamic Named Ranges в Excel.

Може да ви харесат и следните ресурси на Excel:

• Безплатни шаблони на Excel.
• Безплатно онлайн обучение по Excel (онлайн видео курс от 7 части).
• Полезни примери за макро кодове на Excel.
• 10 разширени примера за Excel VLOOKUP.
• Създаване на падащ списък в Excel.
• Създаване на именован диапазон в Google Таблици.
• Как да препратите към друг лист или работна книга в Excel