Strādājot ar Excel, jums noteikti ir jābūt dzirdētam par nosauktajiem diapazoniem programmā Excel. Varbūt no drauga, kolēģa vai kādas tiešsaistes apmācības. Pat es to esmu daudzkārt pieminējis savos rakstos. Šajā rakstā mēs uzzināsim par nosauktajiem diapazoniem programmā Excel un izpētīsim visus tā aspektus.
Kas ir nosaukts diapazons programmā Excel?
Nosauktie diapazoni ir nekas cits kā daži Excel diapazoni, kas atzīmēti ar kādu nozīmīgu nosaukumu. Piemēram, ja jums ir šūna, teiksim B1, satur ikdienas mērķi, varat šo šūnu nosaukt kā īpaši “Mērķis”. Tagad varat izmantot “Target”, lai atsauktos uz A1, nevis rakstītu B1.
Īsumā, Nosauktais diapazons ir tikai diapazonu nosaukšana.
Kā nosaukt diapazonu programmā Excel?
Definējiet vārdu manuāli:
Lai definētu diapazona nosaukumu, varat izmantot īsinājumtaustiņu CTRL+F3. Vai arī varat veikt šīs darbības.
-
- Dodieties uz cilni Formula
- Atrodiet sadaļu Definētie vārdi un noklikšķiniet uz Definēt vārdus. Tas atvērs Name Manger.
-
- Noklikšķiniet uz Jauns.
- Ierakstiet nosaukumu.
- Atlasiet darbības jomu (darbgrāmatu vai lapu)
- Ja vēlaties, uzrakstiet komentāru.
- Lodziņā Atsauces uzrakstiet atsauci vai atlasiet diapazonu, izmantojot peli.
- Noklikšķiniet uz Labi. Tas tiek darīts.
Tagad jūs varat atsaukties uz to, vienkārši ierakstot tā nosaukumu.
Veidojot nosaukumus, jāievēro daži noteikumi. Viņi ir
- Nosaukumi nedrīkst sākties no cipariem vai īpašām rakstzīmēm, izņemot pasvītrojumu (_) un slīpsvītru (\).
- Vārdos nedrīkst būt atstarpes un īpašas rakstzīmes, izņemot _ un \.
- Diapazonu nevajadzētu nosaukt par šūnu atsaucēm. Piemēram, A1, B1 vai AZ100 uc nosaukumi nav derīgi.
- Diapazonu nevar nosaukt par “r” un “c”, jo tie ir rezervēti atsaucēm uz rindām un kolonnām.
- Divu nosauktu diapazonu darbgrāmatā nevar būt vienāds nosaukums.
- Vienam diapazonam var būt vairāki nosaukumi.
Automātiski definējiet vārdu
Lielāko daļu laika jūs strādāsit ar strukturētu datu tabulu. Viņiem būs kolonnas un rindas ar kolonnu virsrakstiem un rindu virsrakstiem. Un lielākoties šie nosaukumi ir nozīmīgi datiem, un jūs vēlaties nosaukt savu diapazonu kā šīs sleju virsrakstus. Programma Excel nodrošina rīku diapazonu nosaukšanai, izmantojot nosaukumus automātiski. Izpildiet šīs darbības.
-
- Diapazonus, kurus vēlaties nosaukt par virsrakstiem
- Nospiediet CTRL+SHIFT+F3, vai cilnē Formula atrodiet sadaļu Definētie vārdi un noklikšķiniet uz Izveidot no atlases.
-
- Parādīsies zemāk redzamais opciju lodziņš. Es izvēlējos augšējo rindu tikai tāpēc, ka vēlos nosaukt šo diapazonu kā virsrakstu un nevēlos nosaukt rindas.
- Noklikšķiniet uz Labi.
Tagad katra kolonna ir nosaukta par to virsrakstu. Ikreiz, kad ierakstāt formulu, šie nosaukumi tiks norādīti izmantojamajā opcijā.
Diapazona nosaukšana, izmantojot Excel tabulas
Kad mēs organizējam datus kā tabulu programmā Excel, izmantojot CTRL + T, kolonnu virsraksti tiek automātiski piešķirti kā attiecīgās slejas nosaukums. Jums vajadzētu izpētīt Excel tabulas un to priekšrocības.
Kā redzēt visus nosauktos diapazonus?
Reizēm darbgrāmatā vēlaties redzēt visus pieejamos nosauktos diapazonus. Lai skatītu visus nosaukumu diapazonus Nospiediet CTRL+F3. Vai arī varat doties uz Cilne Formula> Vārdu pārvaldnieks. Tajā tiks uzskaitīti visi nosauktie diapazoni, kas pieejami darbgrāmatā. Varat rediģēt pieejamos nosauktos diapazonus, dzēst tos, pievienot jaunus nosaukumus.
Viena diapazona vairāki nosaukumi
Excel ļauj lietotājiem nosaukt vienu un to pašu diapazonu ar dažādiem nosaukumiem. Piemēram, diapazonam A2: A10 vienlaikus var nosaukt “Klienti” un “Klienti”. Abi nosaukumi attiecas uz vienu un to pašu diapazonu A2: A10.
Bet jums nevar būt vienādi nosaukumi diviem dažādiem diapazoniem. Tas novērš neskaidrību iespēju.
Lapā iegūstiet nosaukto diapazonu sarakstu
Tātad, ja vēlaties izveidot nosaukto diapazonu sarakstu un diapazonus, kurus tie aptver, varat izmantot šo saīsni, lai tos ielīmētu vietā lapā.
-
- Atlasiet šūnu, kurā vēlaties iegūt nosaukto diapazonu sarakstu.
- Nospiediet F3. Tas atvērs a Lppaste Nosaukums dialoglodziņš.
- Klikšķiniet uz ielīmējiet sarakstu pogu.
- Saraksts tiks ielīmēts atlasītajā šūnā un turpmāk.
Ja ielīmēšanas nosaukuma lodziņā veicat dubultklikšķi uz nosaukto diapazonu nosaukuma, tie šūnā tiks ierakstīti kā formulas. Pamēģini.
Manuāli atjauniniet nosauktos diapazonus
Ievietojot šūnu nosauktajā diapazonā, tā tiek automātiski atjaunināta un paplašināta. Bet, ja tabulas beigās pievienojat datus, jums būs jāatjaunina nosauktais lauks. Lai atjauninātu nosauktos diapazonus, rīkojieties šādi.
- Nospiediet CTRL+F3, lai atvērtu vārdu pārvaldnieku.
- Noklikšķiniet uz nosauktā diapazona, kuru vēlaties rediģēt. Noklikšķiniet uz Rediģēt.
- Sadaļā Atsauce uz kolonnu ierakstiet diapazonu, uz kuru vēlaties izvērsties, un nospiediet Labi.
Un tas ir darīts. Šī ir nosaukto diapazonu manuāla atjaunināšana. Tomēr mēs varam padarīt to dinamisku, izmantojot dažas formulas.
Dinamiski atjauniniet nosauktos diapazonus
Ir prātīgi padarīt nosauktos diapazonus dinamiskus, lai tie nebūtu jārediģē ikreiz, kad jūsu dati pārsniedz iepriekš definēto diapazonu.
Esmu to aplūkojis atsevišķā rakstā ar nosaukumu Dynamic Named diapazoni. Šeit varat detalizēti uzzināt un izprast tā priekšrocības.
Nosaukto diapazonu dzēšana
Dzēšot kādu nosaukta diapazona daļu, tas automātiski pielāgo tā diapazonu. Bet, izdzēšot visu nosaukumu diapazonu, vārdu saraksts pazūd. Jebkura formula atkarībā no šiem diapazoniem parādīs kļūdu #REF, vai arī sniegs nepareizu rezultātu (skaitīšanas funkcijas).
Jebkura iemesla dēļ, ja vēlaties dzēst nosauktos diapazonus, rīkojieties šādi.
- Nospiediet CTRL+F3. Tiks atvērts vārdu pārvaldnieks.
- Atlasiet nosauktos diapazonus, kurus vēlaties dzēst.
- Noklikšķiniet uz pogas Dzēst vai nospiediet tastatūras pogu Dzēst.
Uzmanību: Pirms nosaukto diapazonu dzēšanas pārliecinieties, vai no šiem nosaukumiem nav atkarīgas formulas. Ja tādi ir, vispirms pārveidojiet tos diapazonos. Pretējā gadījumā tiks parādīta kļūda #REF.
Vārdu dzēšana ar kļūdām
Programma Excel nodrošina rīku, lai noņemtu nosaukumus, kuros ir tikai kļūdas. Jums nav jāidentificē katrs no tiem. Lai dzēstu nosaukumus ar kļūdām, rīkojieties šādi:
-
- Atveriet vārdu pārvaldnieku (CTRL+F3).
- Noklikšķiniet uz nolaižamās izvēlnes Filtrs labajā augšējā stūrī.
- Atlasiet “Vārds ar kļūdām”
- Atlasiet Visu un nospiediet dzēšanas pogu.
Un viņi ir aizgājuši. Visi vārdi ar kļūdām tiks nekavējoties izdzēsti no ieraksta.
Nosauktie diapazoni ar formulām
Vislabāk nosauktos diapazonus var izmantot, izmantojot formulas. Ar nosauktajiem diapazoniem formulas kļūst elastīgākas un lasāmākas. Redzēsim, kā.
Viegli rakstāmas formulas
Tagad pieņemsim, ka esat nosaucis diapazonu kā “Vienumi”. Tagad vienumu saraksts, kuru vēlaties saskaitīt, ir “Zīmuļi”. Ar nosaukumu ir viegli uzrakstīt šo COUNTIF formulu. Vienkārši rakstiet
= COUNTIF (vienums, zīmulis)
Tiklīdz rakstīsiet formulas sākuma iekavu, parādīsies pieejamo nosaukto diapazonu saraksts. Bez nosaukuma jūs uzrakstītu Excel gi COUNTIF funkciju ar diapazoniem, kuriem, iespējams, vispirms būs jāapskata diapazons, pēc tam atlasiet diapazonu vai ierakstiet to formulā.
Excel apkalpo pieejamos nosaukumu diapazonus.
Diapazonu nosaukumi tiek parādīti kā ieteikumi, rakstot jebkuru burtu aiz = zīmes. Tas pats, kas Excel, parāda formulu sarakstu. Piemēram, ja ierakstāt = u, katra metode un nosauktais diapazons tiks parādīti, sākot ar u, lai jūs varētu tos ērti izmantot.
Izveidojiet konstantes, izmantojot nosauktos diapazonus
Līdz šim mēs uzzinājām par diapazonu nosaukšanu, taču patiesībā varat nosaukt arī vērtības. Piemēram, ja jūsu klienta vārds ir Sunder Pichai, jūs varat izveidot vārdu “Client” un tas attiecas uz “Sundar Pichai”. Tagad, kad rakstāt = Klients jebkurā šūnā, tas parādīs Sundar Pichai.
Ne tikai tekstu, bet arī varat piešķirt numuru kā nemainīgu darbam. Piemēram, jūs definējat mērķi. Vai arī vērtība tam, kas nemainīsies.
Absolūtā un relatīvā atsauce ar nosauktajiem diapazoniem
Atsauces ar nosauktajiem diapazoniem ir ļoti elastīgas. Piemēram, ja relatīvā šūnā ierakstāt nosaukta diapazona nosaukumu, tas darbosies kā atbilstoša atsauce. Skatīt zemāk redzamo attēlu.
Bet, lietojot to ar formulām, tas darbosies kā absolūts. Lielākoties jūs tos izmantosit ar formulām jūs varat teikt, ka tie pēc noklusējuma ir absolūti, bet patiesībā tie ir elastīgi.
Bet mēs varam arī padarīt tos relatīvus.
Kā programmā Excel izveidot relatīvos nosauktos diapazonus?
Teiksim, ja es vēlos nosaukt diapazonu “Pirms”, kas attiecas uz šūnu pa kreisi, lai kur tas būtu rakstīts. Kā lai es to izdaru? Izpildiet šīs darbības:
- Nospiediet CTRL+F3
- Noklikšķiniet uz Jauns
- Sadaļā “Nosaukums” ierakstiet “Befor”.
- Sadaļā “Attiecas uz:” ierakstiet šūnas adresi kreisajā pusē. Piemēram, ja atrodaties šūnā B1, sadaļā “Atsaucas uz” ierakstiet “= A2”. Pārliecinieties, vai tajā nav $ zīmes.
Tagad visur, kur formulā rakstīsit “Befor”, tas attieksies uz šūnu, kas tai atstāta.
Šeit es iepriekš izmantoju funkciju SLEJA. Formula atgriež tās kreisās šūnas kolonnas numuru, kurā tā ir rakstīta. Man par pārsteigumu, A1 parāda pēdējās kolonnas kolonnas numuru. Tas nozīmē, ka lapa ir apaļa. Es domāju, ka tas parādīs #REF kļūdu.
Piešķirt nosaukumu bieži lietotajām formulām?
Tagad šis ir pārsteidzošs. Daudzas reizes darblapā atkal un atkal izmantojat vienu un to pašu formulu. Piemēram, iespējams, vēlēsities pārbaudīt, vai vārds ir iekļauts jūsu klientu sarakstā. Un šī vajadzība var rasties daudzas reizes. Šim nolūkam jūs katru reizi rakstīsit vienu un to pašu sarežģīto formulu.
= JA (COUNTIF (klients, I3), "sarakstā", "nav sarakstā")
Kā būtu, ja šūnā vienkārši ierakstītu “= IsInCustomer” un tas parādītu, vai kreisās šūnas vērtība ir klientu sarakstā vai ne?
Piemēram, šeit esmu sagatavojis tabulu. Tagad es tikai vēlos ierakstīt “= IsInCustomer” J5, un es vēlētos redzēt, vai vērtība ir I5 ir klientu sarakstā vai nav. Lai to izdarītu, rīkojieties šādi.
-
- Nospiediet CTRL+F3
- Noklikšķiniet uz Jauns
- Vārdā ierakstiet “IsInCustomer”
- Sadaļā “Attiecas uz” uzrakstiet savu formulu. = JA (COUNTIF (klients, I5), "sarakstā", "nav sarakstā")
- Nospiediet pogu OK.
Tagad, lai kur jūs rakstītu “IsInCustomer”, tas pārbaudīs vērtību klientu saraksta kreisajā šūnā.
Tas neļauj jums atkal un atkal atkārtot sevi.
Formulām lietot nosauktos diapazonus
Tik daudzas reizes mēs definējam nosaukumus saviem diapazoniem pēc tam, kad esam jau uzrakstījuši formulas, pamatojoties uz diapazoniem. Piemēram, man ir kopējā cena kā šūnas = E2*F2. Kā mēs to varam mainīt uz Vienības*Vienības_maksa.
-
- Atlasiet formulas.
- Dodieties uz cilni Formula. Noklikšķiniet uz nolaižamās izvēlnes Definēt vārdu.
- Noklikšķiniet uz Lietot vārdus.
- Tiks parādīts visu nosaukto diapazonu saraksts. Izvēlieties pareizos vārdus un noklikšķiniet uz Labi.
Un tagad tiek lietoti nosaukumi. To var redzēt formulas joslā.
Viegli lasāmas formulas ar nosauktajiem diapazoniem
Kā redzējāt, nosauktie diapazoni atvieglo formulu lasīšanu. Ja es rakstīšu = COUNTIF (“A2: A100”, B2), neviens nesapratīs, ko es cenšos saskaitīt, kamēr neredzēs datus vai kāds viņiem to nepaskaidros.
Bet, ja es rakstu = COUNTIF (reģions, “austrumi”), lielākā daļa lietotāju uzreiz sapratīs, ka mēs skaitām “austrumu” sastopamību reģionā nosauktajā diapazonā.
Pārnēsājamās formulas
Nosauktie diapazoni ļauj ļoti viegli kopēt un ielīmēt formulas, neuztraucoties par atsauču maiņu. Un jūs varat ņemt vienu formulu no vienas darbgrāmatas uz citu, un tā darbosies labi, līdz galamērķa darbgrāmatai būs vienādi nosaukumi.
Piemēram, ja jums ir formula = COUNTIF (reģions, austrumi) sadales tabulā, un jums ir cita darbgrāmata klientiem kam ir arī nosaukts diapazons “Reģions”. Tagad, ja jūs kopējat šo formulu tieši jebkurā vietā šajā darbgrāmatā, tā parādīs pareizu informāciju. Datu struktūrai nebūs nozīmes. Nav svarīgi, kur, pie velna, atrodas šī sleja jūsu darbgrāmatā. Tas darbosies pareizi.
Iepriekš redzamajā attēlā esmu izmantojis tieši tādu pašu formulu divos dažādos failos, lai saskaitītu reģionu sarakstā esošo skaitu vai austrumus. Tagad tie ir dažādās kolonnās, bet, tā kā abi ir nosaukti kā reģions, tas darbosies perfekti.
Viegli orientēties darbgrāmatā
Ir vieglāk orientēties darbgrāmatā ar nosauktajiem diapazoniem. Jums vienkārši jāievada nosaukuma nosaukums nosaukuma lodziņā. Excel jūs aizvedīs uz diapazonu, neatkarīgi no tā, kur atrodaties darbgrāmatā. Ņemot vērā, ka nosauktais diapazons ir darbgrāmatas darbības joma.
Piemēram, ja atrodaties 10. lapā un vēlaties iegūt klientu sarakstu un nezināt, uz kuras lapas tas ir. Vienkārši dodieties uz nosaukuma lodziņu un ierakstiet “klients”. Dažas sekundes laikā jūs tiksit novirzīts uz nosaukto diapazonu.
Tas samazinās centienus atcerēties diapazonus.
Pārvietojieties, izmantojot hipersaites ar nosaukto diapazonu
Ja jūsu lapa ir liela un jūs bieži pārejat no viena punkta uz otru, jums patīk izmantot hipersaites, lai ērti pārvietotos. Labi nosaukti diapazoni var lieliski darboties ar hipersaitēm. Lai pievienotu hipersaites, izmantojot nosauktos diapazonus, rīkojieties šādi.
-
- Izvēlieties šūnu, kurā vēlaties iegūt hipersaiti
- Nospiediet CTRL+K vai dodieties uz cilni Ievietot> HyperLink, lai atvērtu dialoglodziņu Ievietot hipersaiti.
-
- Noklikšķiniet uz Vieta šajā dokumentā.
- Ritiniet uz leju, lai sadaļā Definētie nosaukumi redzētu pieejamos nosauktos diapazonus
- Atlasiet nosaukto diapazonu, lai ievietotu hipersaiti uz šo diapazonu.
Un tas ir izdarīts. Jums ir hipersaite uz izvēlēto nosaukto diapazonu. Izmantojot to, jūs varat izveidot nosaukto diapazonu indeksu, ko varat redzēt un noklikšķināt, lai pārvietotos uz tiem tieši. Tas padarīs jūsu darbgrāmatu patiešām draudzīgu lietotājam.
Nosauktais diapazons un datu validācija
Nosauktie diapazoni un datu validācija tiek veidoti viens otram. Nosauktie diapazoni padara datu validāciju ļoti pielāgojamu. Ir daudz vieglāk pievienot validāciju no saraksta, izmantojot nosaukto diapazonu. Redzēsim, kā…
-
- Dodieties uz cilni Dati
- Noklikšķiniet uz Datu validācija
- Sadaļā “Atļaut:” atlasiet Saraksts
- Sadaļā “Avots:” ierakstiet “= Klients” (ierakstiet jebkuru nosaukto diapazonu)
- Noklikšķiniet uz Labi
Tagad šai šūnai būs klientu vārdi, kas ir daļa no Klientu nosaukto diapazona. Viegli, vai ne.
Atkarīga vai kaskādes datu validācija ar nosauktajiem diapazoniem
Ko darīt, ja vēlaties veikt kaskādes vai atkarīgu datu validāciju. Piemēram, ja vēlaties nolaižamo sarakstu ar kategorijām Augļi un dārzeņi. Tagad, ja izvēlaties augļus, citā nolaižamajā izvēlnē vajadzētu parādīt tikai augļus, un, ja izvēlaties Dārzeņi, tad tikai dārzeņus.
To var viegli sasniegt, izmantojot nosauktos diapazonus. Iemācies kā.
- Atkarīgs nolaižamais saraksts, izmantojot nosaukumu diapazonu
- Citi kaskādes datu validācijas veidi
Nav datu validācijas ar tabulēto datu nosaukumiem
Lai gan Excel tabulās ir strukturēti nosaukumi, tos nevar izmantot kopā ar datu validāciju un nosacītu formatējumu. Es nezinu, kāpēc Excel to neļauj.
Bet tas nenozīmē, ka to nevar izdarīt. Tabulā varat nosaukt diapazonus un pēc tam tos izmantot validācijai. Excel ar to nav nekādu problēmu.
Nosaukto diapazonu darbības joma
Līdz šim mēs runājām par nosauktajiem diapazoniem, kuriem bija darbgrāmatas darbības joma. Kas? Vai mēs to neapspriedām? Labi, tāpēc ļaujiet ātri saprast nosaukto diapazonu darbības jomu.
Kas ir nosaukumu diapazona darbības joma?
Labi tvērums nosaka, kur var atpazīt nosaukumu diapazonu. Nevienu nosaukumu nevar atpazīt no tā darbības jomas. Piemēram, vārdu darbgrāmatā1 nevar atpazīt dažādās darbgrāmatās. Programma Excel nodrošina divas iespējas nosaukto diapazonu darblapai un darbgrāmatai.
Kā definēt nosauktā diapazona darbības jomu?
Veidojot jaunu nosaukumu diapazonu, varat redzēt sadaļu “Darbības joma:”. Noklikšķiniet uz nolaižamā saraksta un izvēlieties sava vārdu diapazona darbības jomu. Pēc nosaukuma diapazona izveides nevar mainīt darbības jomu. Tāpēc labāk to darīt iepriekš. Pēc noklusējuma tā ir darbgrāmata.
Darbgrāmatas darbības joma
Šī ir noklusējuma darbības joma nosauktajam diapazonam. Nosaukumu, kas definēts ar darbgrāmatas apjomu, var izmantot visā darbgrāmatā, kurā tas ir definēts (nevis citās darbgrāmatās).
Visiem iepriekš minētajiem piemēriem bija darbgrāmatas darbības joma.
Darblapas darbības joma
Nosaukumu, kas ir definēts ar darblapas tvērumu, var izmantot tikai definēšanas darblapā. Piemēram, ja es definēju “Kopā” kopējai šūnai ar 1. lapas apjomu. Tad kopsumma tiks atzīta tikai 1. lapā. Citas lapas neatpazīs.
Es vēlos Excel darbības jomu
Programmai Excel nav globāla vai teiksim Excel darbības joma. Patiesībā es vēlētos definēt dažus vārdus, kurus var atpazīt visās manas sistēmas darbgrāmatās. Ja kāds zina, kā mēs to varam izdarīt, dariet man zināmu.
Rediģēšanas joma pēc vārdu izveidošanas
Jūs nevarat. Programma Excel neļauj rediģēt nosaukta diapazona darbības jomu pēc izveidošanas. Tā kā visi nosaukti diapazoni lapā ir pēc noklusējuma darbgrāmatas darbības joma, un, iespējams, vēlēsities mainīt to darbības jomu uz lapu.
Lai to izdarītu, vienkārši izveidojiet šīs lapas kopiju, un Excel padarīs katru šīs lapas vārdu vietēju, lai izvairītos no neskaidrībām. Ja vēlaties, tagad varat izdzēst oriģinālo lapu.
Izgriezt ielīmēto nosaukumu diapazonu
Izgriežot un ielīmējot nosaukto diapazonu no viena galamērķa uz citu, atsauce mainās uz jaunu atrašanās vietu. Piemēram, ja jums ir nosaukts diapazons “Klients” A2: A10 un sagriežat un ielīmējat to B2: B10, tad klienta vārds attiecas uz jauno atrašanās vietu B2: B10.
Dinamiski nosaukti diapazoni programmā Excel
17 pārsteidzošas Excel tabulu iezīmes
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti
Kā lietot funkciju VLOOKUP programmā Excel
Kā programmā Excel izmantot funkciju COUNTIF
Kā lietot funkciju SUMIF programmā Excel