Како створити именоване опсеге у Екцелу (Корак по корак водич)

Шта је у имену?

Ако радите са Екцел табелама, то може значити много уштеде времена и ефикасности.

У овом водичу ћете научити како да креирате Намед Рангес у Екцелу и како да га користите за уштеду времена.

Именовани распони у Екцелу - увод

Ако неко мора да ме позове или да ме позове, користиће моје име (уместо да каже да мужјак борави на том месту на таквој висини и тежини).

Јел тако?

Слично, у Екцелу можете дати име ћелији или опсегу ћелија.

Сада, уместо да користите референцу ћелије (попут А1 или А1: А10), можете једноставно да користите име које сте јој доделили.

На пример, претпоставимо да имате скуп података као што је приказано испод:

У овом скупу података, ако морате да се позивате на опсег који има датум, мораћете да користите А2: А11 у формулама. Слично, за представника продаје и продају мораћете да користите Б2: Б11 и Ц2: Ц11.

Иако је у реду када имате само неколико тачака података, али у случају да имате огромне сложене скупове података, коришћење референци ћелија за упућивање на податке може одузети много времена.

Екцел именовани распони олакшавају позивање на скупове података у Екцелу.

Можете да креирате именовани опсег у Екцелу за сваку категорију података, а затим да користите то име уместо референци ћелије. На пример, датуми се могу назвати „Датум“, подаци продајног представника могу се назвати „СалесРеп“, а подаци о продаји могу се назвати „Продаја“.

Такође можете креирати име за једну ћелију. На пример, ако у ћелији имате проценат провизије од продаје, можете ту ћелију дати назив „Провизија“.

Предности стварања именованих опсега у Екцелу

Ево предности коришћења именованих опсега у Екцелу.

Користите имена уместо референци на ћелије

Када креирате Намед Рангес у Екцелу, можете користити ова имена уместо референци на ћелије.

На пример, можете користити = СУМ (САЛЕС) уместо = СУМ (Ц2: Ц11) за горњи скуп података.

Погледајте доле наведене формуле. Уместо да користим референце ћелија, користио сам Намед Рангес.

• Број продаја у вредности већој од 500: = ЦОУНТИФ (Продаја, ”> 500 ″)
• Збир свих продаја које је обавио Том: = СУМИФ (СалесРеп, „Том”, Салес)
• Провизију коју је зарадио Јое (продаја коју је Јое помножио са процентом провизије):
  = СУМИФ (СалесРеп, „Јое“, Салес)*Комисија

Сложићете се да је ове формуле лако створити и лако разумети (посебно ако их делите са неким другим или их поново прегледате.

Нема потребе да се враћате на скуп података да бисте изабрали ћелије

Још једна значајна предност коришћења именованих опсега у Екцелу је та што се не морате враћати и бирати опсеге ћелија.

Можете само да откуцате неколико абецеда тог именованог опсега и Екцел ће приказати одговарајуће именоване опсеге (као што је приказано испод):

Именовани распони чине формуле динамичким

Коришћењем Именованих опсега у Екцелу можете да учините Екцел формуле динамичким.

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

Сада, ако ваша компанија касније одлучи да повећа провизију на 3%, можете једноставно ажурирати Намед Ранге и све калкулације ће се аутоматски ажурирати тако да одражавају нову провизију.

Како створити именоване опсеге у програму Екцел

Ево три начина за креирање именованих опсега у Екцелу:

Метод #1 - Употреба дефинисања имена

Ево корака за креирање именованих опсега у Екцелу помоћу дефинисања имена:

• Изаберите опсег за који желите да креирате Именовани опсег у програму Екцел.
• Идите на Формуле -> Дефинирај назив.
• У дијалогу Ново име откуцајте Име које желите да доделите изабраном опсегу података. Опсег можете навести као целу радну свеску или одређени радни лист. Ако изаберете одређени лист, назив неће бити доступан на другим листовима.
• Притисните ОК.

Ово ће створити Намед Ранге САЛЕСРЕП.

Метод #2: Коришћење оквира за име

• Изаберите опсег за који желите да креирате име (не бирајте заглавља).
• Идите у оквир за име са леве стране траке формуле и откуцајте име са којим желите да креирате именовани опсег.
• Имајте на уму да ће Име створено овде бити доступно за целу Радну свеску. Ако желите да га ограничите на радни лист, користите метод 1.

Метод #3: Коришћење опције Креирај из селекције

Ово је препоручени начин када имате податке у табеларном облику и желите да креирате именовани опсег за сваку колону/ред.

На пример, у доњем скупу података, ако желите брзо да креирате три именована опсега (Датум, Салес_Реп и Салес), можете користити доле приказани метод.

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

• Изаберите цео скуп података (укључујући заглавља).
• Идите на Формуле -> Креирај из селекције (Тастерска пречица - Цонтрол + Схифт + Ф3). Отвориће се дијалошки оквир „Креирај имена из избора“.
• У дијалогу Креирање имена из избора проверите опције на којима имате заглавља. У овом случају бирамо горњи ред само зато што је заглавље у горњем реду. Ако имате заглавља и у горњем реду и у левој колони, можете да изаберете оба. Слично, ако су ваши подаци распоређени само када су заглавља у левој колони, онда само проверите опцију Лева колона.

Ово ће створити три именована опсега - Датум, Салес_Реп и Салес.

Имајте на уму да аутоматски преузима имена из заглавља. Ако постоји размак између речи, он додаје доњу црту (јер не можете имати размаке у именованим опсезима).

Конвенција о именовању именованих опсега у Екцелу

Постоје нека правила именовања која морате знати приликом стварања Именованих опсега у Екцелу:

• Први знак именованог опсега треба да буде слово и знак за подвлачење (_) или обрнута црта (\). Ако је било шта друго, приказаће грешку. Преостали знакови могу бити слова, бројеви, посебни знакови, тачка или подвлака.
• Не можете да користите имена која такође представљају референце ћелија у Екцелу. На пример, не можете да користите АБ1 јер је то и референца ћелије.
• Не можете да користите размаке при креирању именованих опсега. На пример, не можете имати представника продаје као именовани опсег. Ако желите да комбинујете две речи и креирате Намед Ранге, употребите доњу црту, тачку или велика слова да бисте је створили. На пример, можете имати Салес_Реп, СалесРеп или СалесРеп.
  • Приликом креирања именованих опсега, Екцел на исти начин третира велика и мала слова. На пример, ако креирате именовани опсег ПРОДАЈА, тада нећете моћи да креирате други именовани опсег као што је „продаја“ или „Продаја“.
• Именовани опсег може имати највише 255 знакова.

Превише именованих опсега у Екцелу? Не брините

Понекад у великим скуповима података и сложеним моделима можете створити много именованих опсега у Екцелу.

Шта ако се не сећате имена Именованог опсега који сте створили?

Не брините - ево неколико корисних савета.

Добијање имена свих именованих асортимана

Ево корака за добијање листе свих именованих опсега које сте креирали:

• Идите на картицу Формуле.
• У групи Дефинед Намед кликните на Усе ин Формула.
• Кликните на „Залепи имена“.

Ово ће вам дати списак свих именованих опсега у тој радној свесци. Да бисте користили именовани опсег (у формулама или ћелији), двапут кликните на њега.

Приказивање одговарајућих именованих опсега

• Ако имате неку идеју о имену, откуцајте неколико почетних знакова и Екцел ће приказати падајући мени одговарајућих имена.

Како уредити именоване опсеге у програму Екцел

Ако сте већ креирали Именовани опсег, можете га уредити следећим корацима:

• Идите на картицу Формуле и кликните на Управитељ имена.
• У дијалогу Менаџер имена биће наведени сви именовани опсези у тој радној свесци. Двапут кликните на Намед Ранге који желите да уредите.
• У дијалогу Уређивање имена унесите измене.
• Притисните ОК.
• Затворите дијалог Наме Манагер.

Корисне пречице са именованим опсегом (снага Ф3)

Ево неколико корисних пречица на тастатури које ће вам бити корисне када радите са Именованим опсезима у Екцелу:

• Да бисте добили листу свих именованих опсега и залепили је у формулу: Ф3
• Да бисте креирали ново име помоћу дијалога Наме Манагер: Цонтрол + Ф3
• Да бисте креирали именоване опсеге из избора: Цонтрол + Схифт + Ф3

Креирање динамичких именованих опсега у програму Екцел

До сада смо у овом водичу створили статичке називне домете.

То значи да би се ти именовани распони увек односили на исти скуп података.

На пример, ако је А1: А10 именовано као „продаја“, увек би се односило на А1: А10.

Ако додате још података о продаји, мораћете ручно да ажурирате референцу у именованом опсегу.

У свету све ширих скупова података, ово вам може одузети много времена. Сваки пут када добијете нове податке, можда ћете морати да ажурирате Намед Рангес у Екцелу.

Да бисмо решили овај проблем, можемо створити динамичке именоване опсеге у Екцелу који би аутоматски узимали у обзир додатне податке и укључивали их у постојећи именовани распон.

На пример, на пример, ако додам две додатне тачке са подацима о продаји, динамички именовани опсег би се аутоматски односио на А1: А12.

Ова врста распона динамичких назива може се креирати помоћу Екцел ИНДЕКС функције. Уместо да наведемо референце ћелија при креирању Намед Ранге -а, ми наводимо формулу. Формула се аутоматски ажурира када се подаци додају или избришу.

Хајде да видимо како да креирате динамичке именоване опсеге у Екцелу.

Претпоставимо да имамо податке о продаји у ћелији А2: А11.

Ево корака за креирање динамичких именованих опсега у Екцелу:

1. 1. Идите на картицу Формула и кликните на Дефинирај име.
   2. У дијалошки оквир Ново име откуцајте следеће:
      • Назив: Продаја
      • Опсег: Радна свеска
      • Односи се на: = $ А $ 2: ИНДЕКС ($ А $ 2: $ А $ 100, ЦОУНТИФ ($ А $ 2: $ А $ 100, ”” & ””))
   3. Притисните ОК.

Готово!

Сада имате динамички именовани опсег са именом „Продаја“. Ово би се аутоматски ажурирало сваки пут када му додате податке или уклоните податке из њих.

Како функционишу динамички именовани распони?

Да бисте објаснили како ово функционише, морате знати нешто више о Екцел ИНДЕКС функцији.

Већина људи користи ИНДЕКС за враћање вредности са листе на основу броја ретка и колоне.

Али функција ИНДЕКС има и другу страну.

На то се може навићи врати референцу ћелије када се користи као део референце ћелије.

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

= $ А $ 2: ИНДЕКС ($ А $ 2: $ А $ 100, ЦОУНТИФ ($ А $ 2: $ А $ 100, "" & ""))

ИНДЕКС ($ А $ 2: $ А $ 100, ЦОУНТИФ ($ А $ 2: $ А $ 100, ”” & ””) -> Очекује се да ће овај део формуле вратити вредност (која би била десета вредност са листе, с обзиром да постоји десет ставки).

Међутим, када се користи испред референце (=$ А $ 2:ИНДЕКС ($ А $ 2: $ А $ 100, ЦОУНТИФ ($ А $ 2: $ А $ 100, ”” & ””))) враћа референцу ћелији уместо вредности.

Дакле, овде се враћа = $ А $ 2: $ А $ 11

Ако додамо две додатне вредности у колону продаје, онда би се вратило = $ А $ 2: $ А $ 13

Када на листу додате нове податке, функција Екцел ЦОУНТИФ враћа број ћелија које нису празне у подацима. Овај број функција ИНДЕКС користи за дохват референце ћелије последње ставке на листи.

Белешка:

• Ово би функционисало само ако у подацима нема празних ћелија.
• У горе наведеном примеру, доделио сам велики број ћелија (А2: А100) за формулу Намед Ранге. Ово можете прилагодити на основу скупа података.

Функцију ОФФСЕТ можете користити и за креирање динамичких именованих опсега у Екцелу, међутим, будући да је функција ОФФСЕТ нестабилна, то може довести до споре Екцел радне свеске. С друге стране, ИНДЕКС је полухлап, што га чини бољим избором за креирање динамичких именованих опсега у Екцелу.

Можда ће вам се допасти и следећи Екцел извори:

• Бесплатни предлошци програма Екцел.
• Бесплатна онлајн Екцел обука (7-део онлајн видео курс).
• Примери корисних Екцел макро кодова.
• 10 напредних примера Екцел ВЛООКУП -а.
• Креирање падајуће листе у програму Екцел.
• Креирање именованог опсега у Гоогле табелама.
• Како се позивати на други лист или радну свеску у Екцелу