Kā izveidot nosauktos diapazonus programmā Excel (soli pa solim)

Kas ir nosaukumā?

Ja strādājat ar Excel izklājlapām, tas varētu nozīmēt daudz laika ietaupījumu un efektivitāti.

Šajā apmācībā jūs uzzināsit, kā programmā Excel izveidot nosauktos diapazonus un kā to izmantot, lai ietaupītu laiku.

Nosauktie diapazoni programmā Excel - ievads

Ja kādam man ir jāzvana vai jāatsaucas uz mani, viņš izmantos manu vārdu (tā vietā, lai teiktu, ka tēviņš uzturas tik un tā vietā ar tik un tik augumu un svaru).

Taisnība?

Līdzīgi programmā Excel varat šūnai vai šūnu diapazonam piešķirt nosaukumu.

Tagad, nevis izmantojiet šūnu atsauci (piemēram, A1 vai A1: A10), varat vienkārši izmantot tai piešķirto nosaukumu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Ja šajā datu kopā ir jāatsaucas uz diapazonu, kuram ir datums, formulās būs jāizmanto A2: A11. Līdzīgi tirdzniecības pārstāvim un pārdošanai jums būs jāizmanto B2: B11 un C2: C11.

Lai gan ir labi, ja jums ir tikai daži datu punkti, bet, ja jums ir milzīgas sarežģītas datu kopas, šūnu atsauču izmantošana, lai atsauktos uz datiem, varētu būt laikietilpīga.

Izmantojot Excel nosauktos diapazonus, ir viegli atsaukties uz datu kopām programmā Excel.

Programmā Excel varat izveidot nosauktu diapazonu katrai datu kategorijai un pēc tam izmantot šo nosaukumu šūnu atsauču vietā. Piemēram, datumus var nosaukt par “Datumu”, pārdošanas pārstāvju datus - “SalesRep”, bet pārdošanas datus - “Pārdošana”.

Varat arī izveidot vienas šūnas nosaukumu. Piemēram, ja šūnā ir pārdošanas komisijas procentuālā daļa, varat šo šūnu nosaukt kā “Komisija”.

Nosaukto diapazonu izveides priekšrocības programmā Excel

Šeit ir norādīto diapazonu izmantošanas priekšrocības programmā Excel.

Šūnu atsauču vietā izmantojiet vārdus

Veidojot nosauktos diapazonus programmā Excel, šūnu atsauču vietā varat izmantot šos nosaukumus.

Piemēram, iepriekšminētajai datu kopai varat izmantot = SUM (SALES), nevis = SUM (C2: C11).

Apskatiet zemāk uzskaitītās formulas. Šūnu atsauču vietā esmu izmantojis nosauktos diapazonus.

• Pārdošanas gadījumu skaits, kuru vērtība pārsniedz 500: = COUNTIF (pārdošanas apjoms,> 500 ″)
• Visu Toma veikto pārdošanas darījumu summa: = SUMIF (SalesRep, “Tom”, Sales)
• Džo nopelnīto komisijas maksu (Džo pārdošanas apjoms reizināts ar komisijas procentuālo daļu):
  = SUMIF (SalesRep, ”Joe”, Sales)*Komisija

Jūs piekrītat, ka šīs formulas ir viegli izveidot un viegli saprast (it īpaši, ja kopīgojat to ar kādu citu vai pats to atkārtoti apmeklējat.

Lai atlasītu šūnas, nav jāatgriežas datu kopā

Vēl viena būtiska priekšrocība, izmantojot Excel nosauktos diapazonus, ir tā, ka jums nav jāatgriežas un jāizvēlas šūnu diapazoni.

Jūs varat vienkārši ierakstīt pāris šī nosaukuma diapazona alfabētus, un programmā Excel tiks parādīti atbilstošie nosauktie diapazoni (kā parādīts zemāk):

Nosauktie diapazoni padara formulas dinamiskas

Izmantojot programmā Excel nosauktos diapazonus, varat padarīt Excel formulas dinamiskas.

Piemēram, pārdošanas komisijas gadījumā 2,5%vērtības vietā varat izmantot nosaukumu diapazonu.

Tagad, ja jūsu uzņēmums vēlāk nolemj palielināt komisijas maksu līdz 3%, varat vienkārši atjaunināt nosaukto diapazonu, un visi aprēķini tiks automātiski atjaunināti, lai atspoguļotu jauno komisijas maksu.

Kā izveidot nosauktos diapazonus programmā Excel

Šeit ir trīs veidi, kā programmā Excel izveidot nosauktos diapazonus:

1. metode - nosakot nosaukumu

Tālāk ir norādītas darbības, lai programmā Excel izveidotu nosauktos diapazonus, izmantojot funkciju Define Name.

• Atlasiet diapazonu, kuram programmā Excel vēlaties izveidot nosaukto diapazonu.
• Dodieties uz Formulas -> Definēt vārdu.
• Dialoglodziņā Jauns nosaukums ierakstiet nosaukumu, kuru vēlaties piešķirt atlasītajam datu diapazonam. Jūs varat norādīt darbības jomu kā visu darbgrāmatu vai konkrētu darblapu. Ja atlasīsit noteiktu lapu, nosaukums nebūs pieejams citās lapās.
• Noklikšķiniet uz Labi.

Tādējādi tiks izveidots nosaukts diapazons SALESREP.

2. metode: nosaukuma lodziņa izmantošana

• Atlasiet diapazonu, kuram vēlaties izveidot nosaukumu (neatlasiet galvenes).
• Dodieties uz nosaukuma lodziņu formulas joslas kreisajā pusē un ierakstiet tā nosaukuma nosaukumu, ar kuru vēlaties izveidot nosaukto diapazonu.
• Ņemiet vērā, ka šeit izveidotais nosaukums būs pieejams visai darbgrāmatai. Ja vēlaties to ierobežot līdz darblapai, izmantojiet 1. metodi.

3. metode: izmantojiet opciju Izveidot no atlases

Tas ir ieteicamais veids, ja jums ir dati tabulas veidā un vēlaties izveidot nosaukto diapazonu katrai kolonnai/rindai.

Piemēram, ja vēlaties zemāk esošajā datu kopā ātri izveidot trīs nosauktos diapazonus (datums, pārdošanas_atbilde un pārdošana), varat izmantot tālāk norādīto metodi.

Tālāk ir norādītas darbības, lai ātri izveidotu nosauktos diapazonus no datu kopas.

• Atlasiet visu datu kopu (ieskaitot galvenes).
• Dodieties uz Formulas -> Izveidot no atlases (Tastatūras saīsne - Control + Shift + F3). Tas atvērs dialoglodziņu “Izveidot vārdus no atlases”.
• Dialoglodziņā Izveidot vārdus no atlases pārbaudiet opcijas, kurās ir galvenes. Šajā gadījumā mēs izvēlamies tikai augšējo rindu, jo galvene atrodas augšējā rindā. Ja galvenes ir gan augšējā rindā, gan kreisajā slejā, varat izvēlēties abas. Līdzīgi, ja jūsu dati ir sakārtoti, kad galvenes atrodas tikai kreisajā slejā, tad atzīmējat tikai opciju Kreisā kolonna.

Tādējādi tiks izveidoti trīs nosaukti diapazoni - datums, pārdošanas_atbilde un pārdošana.

Ņemiet vērā, ka tas automātiski uztver nosaukumus no galvenēm. Ja starp vārdiem ir atstarpe, tas ievieto pasvītrojumu (jo nosauktajos diapazonos nedrīkst būt atstarpes).

Nosaukšanas konvencija nosauktajiem diapazoniem programmā Excel

Veidojot nosauktos diapazonus programmā Excel, jums jāzina daži nosaukšanas noteikumi:

• Nosauktā diapazona pirmajai rakstzīmei jābūt burtam un pasvītrojuma rakstzīmei (_) vai slīpsvītrai (\). Ja tas ir kaut kas cits, tas parādīs kļūdu. Atlikušās rakstzīmes var būt burti, cipari, speciālās rakstzīmes, punkts vai pasvītrojums.
• Programmā Excel nevar izmantot nosaukumus, kas apzīmē arī šūnu atsauces. Piemēram, jūs nevarat izmantot AB1, jo tā ir arī šūnu atsauce.
• Veidojot nosauktos diapazonus, nevar izmantot atstarpes. Piemēram, pārdošanas pārstāvis nevar būt nosaukts diapazons. Ja vēlaties apvienot divus vārdus un izveidot nosaukto diapazonu, izmantojiet tā pasvītrojumu, punktu vai lielos burtus. Piemēram, jums var būt Sales_Rep, SalesRep vai SalesRep.
  • Veidojot nosauktos diapazonus, programma Excel lielos un mazos burtus apstrādā vienādi. Piemēram, ja izveidojat nosauktu diapazonu PĀRDOŠANA, tad nevarēsit izveidot citu nosauktu diapazonu, piemēram, “pārdošana” vai “Pārdošana”.
• Nosauktais diapazons var būt līdz 255 rakstzīmēm garš.

Pārāk daudz nosaukto diapazonu programmā Excel? Neuztraucieties

Dažreiz lielās datu kopās un sarežģītos modeļos programmā Excel varat izveidot daudz nosaukto diapazonu.

Ko darīt, ja neatceraties izveidotā nosauktā diapazona nosaukumu?

Neuztraucieties - šeit ir daži noderīgi padomi.

Visu nosaukto diapazonu nosaukumu iegūšana

Lai iegūtu sarakstu ar visiem nosauktajiem diapazoniem, kurus izveidojāt, veiciet tālāk norādītās darbības.

• Dodieties uz cilni Formulas.
• Grupā Definētais nosaukums noklikšķiniet uz Izmantot formulā.
• Noklikšķiniet uz “Ielīmēt vārdus”.

Tādējādi tiks parādīts visu šīs darbgrāmatas nosaukto diapazonu saraksts. Lai izmantotu nosaukto diapazonu (formulās vai šūnā), veiciet dubultklikšķi uz tā.

Parādot atbilstošos nosauktos diapazonus

• Ja jums ir kāda ideja par nosaukumu, ierakstiet dažas sākotnējās rakstzīmes, un programma Excel parādīs atbilstošo nosaukumu nolaižamo izvēlni.

Kā rediģēt nosauktos diapazonus programmā Excel

Ja esat jau izveidojis nosaukumu diapazonu, varat to rediģēt, veicot šādas darbības:

• Dodieties uz cilni Formulas un noklikšķiniet uz Vārdu pārvaldnieks.
• Dialoglodziņā Nosaukumu pārvaldnieks tiks uzskaitīti visi šīs darbgrāmatas nosauktie diapazoni. Veiciet dubultklikšķi uz nosauktā diapazona, kuru vēlaties rediģēt.
• Dialoglodziņā Nosaukuma rediģēšana veiciet izmaiņas.
• Noklikšķiniet uz Labi.
• Aizveriet dialoglodziņu Vārdu pārvaldnieks.

Noderīgi nosaukti diapazona saīsnes (F3 spēks)

Šeit ir daži noderīgi īsinājumtaustiņi, kas noderēs, strādājot ar nosauktajiem diapazoniem programmā Excel:

• Lai iegūtu visu nosaukto diapazonu sarakstu un ielīmētu to formulā: F3
• Lai izveidotu jaunu nosaukumu, izmantojot vārdu pārvaldnieka dialoglodziņu: Control + F3
• Lai no atlases izveidotu nosauktos diapazonus: Control + Shift + F3

Dinamisku nosaukto diapazonu izveide programmā Excel

Līdz šim šajā apmācībā mēs esam izveidojuši statiskus nosauktos diapazonus.

Tas nozīmē, ka šie nosauktie diapazoni vienmēr atsaucās uz vienu un to pašu datu kopu.

Piemēram, ja A1: A10 ir nosaukts kā “Pārdošana”, tas vienmēr attiecas uz A1: A10.

Ja pievienojat vairāk pārdošanas datu, jums manuāli jāiet un jāatjaunina atsauce nosauktajā diapazonā.

Datu kopu pasaulē, kas arvien paplašinās, tas var aizņemt daudz laika. Katru reizi, kad iegūstat jaunus datus, iespējams, būs jāatjaunina programmā Excel norādītie diapazoni.

Lai atrisinātu šo problēmu, programmā Excel varam izveidot dinamiskus nosauktos diapazonus, kas automātiski ņemtu vērā papildu datus un iekļautu tos esošajā nosauktajā diapazonā.

Piemēram, piemēram, ja es pievienoju divus papildu pārdošanas datu punktus, dinamiskais nosauktais diapazons automātiski atsauktos uz A1: A12.

Šāda veida dinamisko nosaukto diapazonu var izveidot, izmantojot funkciju Excel INDEX. Tā vietā, lai, veidojot nosaukto diapazonu, norādītu šūnu atsauces, mēs norādām formulu. Formula tiek automātiski atjaunināta, kad dati tiek pievienoti vai dzēsti.

Apskatīsim, kā programmā Excel izveidot dinamiskus nosauktos diapazonus.

Pieņemsim, ka mums ir pārdošanas dati šūnā A2: A11.

Šeit ir norādītas darbības, lai programmā Excel izveidotu dinamiskus nosauktos diapazonus:

1. 1. Dodieties uz cilni Formula un noklikšķiniet uz Definēt vārdu.
   2. Dialoglodziņā Jauns vārds ierakstiet:
      • Nosaukums: Pārdošana
      • Darbības joma: darba grāmata
      • Attiecas uz: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Noklikšķiniet uz Labi.

Gatavs!

Tagad jums ir dinamisks diapazons ar nosaukumu “Pārdošana”. Tas tiks automātiski atjaunināts ikreiz, kad tam pievienosit datus vai noņemsit no tiem datus.

Kā darbojas dinamiskie nosauktie diapazoni?

Lai izskaidrotu, kā tas darbojas, jums jāzina mazliet vairāk par Excel INDEX funkciju.

Lielākā daļa cilvēku izmanto INDEX, lai atgrieztu vērtību no saraksta, pamatojoties uz rindu un kolonnu numuru.

Bet funkcijai INDEX ir arī otra puse.

To var izmantot atgriezt šūnas atsauci ja to izmanto kā šūnu atsauces daļu.

Piemēram, šeit ir formula, ko esam izmantojuši, lai izveidotu dinamisku nosaukto diapazonu:

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

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””)) -> Paredzams, ka šī formulas daļa atgriezīs vērtību (kas būtu 10. vērtība no saraksta, ņemot vērā, ka ir desmit vienības).

Tomēr, ja to izmanto atsauces priekšā (=2 USD:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) tā atgriež atsauci uz šūnu vērtības vietā.

Tādējādi šeit tas atgriežas = $ A $ 2: $ A $ 11

Ja pārdošanas slejai pievienosim divas papildu vērtības, tā atgriezīsies = $ A $ 2: $ A $ 13

Pievienojot sarakstam jaunus datus, funkcija Excel COUNTIF atgriež datu tukšo šūnu skaitu. Funkcija INDEX izmanto šo numuru, lai ielādētu saraksta pēdējā vienuma šūnu atsauci.

Piezīme:

• Tas darbotos tikai tad, ja datos nebūtu tukšu šūnu.
• Iepriekš minētajā piemērā formulai Nosauktais diapazons esmu piešķīris lielu skaitu šūnu (A2: A100). Varat to pielāgot, pamatojoties uz savu datu kopu.

Varat arī izmantot funkciju OFFSET, lai programmā Excel izveidotu dinamiskus nosauktos diapazonus, tomēr, tā kā funkcija OFFSET ir nepastāvīga, tā var izraisīt lēnu Excel darbgrāmatu. Savukārt INDEX ir daļēji gaistošs, kas padara to par labāku izvēli, lai programmā Excel izveidotu dinamiskus nosauktos diapazonus.

Jums varētu patikt arī šādi Excel resursi:

• Bezmaksas Excel veidnes.
• Bezmaksas tiešsaistes Excel apmācība (7 daļu tiešsaistes video kurss).
• Noderīgi Excel makro koda piemēri.
• 10 Papildu Excel VLOOKUP piemēri.
• Nolaižamā saraksta izveide programmā Excel.
• Nosaukta diapazona izveide Google izklājlapās.
• Kā atsaukties uz citu lapu vai darbgrāmatu programmā Excel