Šajā rakstā mēs uzzināsim, kā lietot vārdus formulā programmā Excel.
Scenārijs:
Strādājot ar Excel, jūs noteikti esat dzirdējuši par nosauktajiem diapazoniem programmā Excel. Varbūt no drauga, kolēģa vai kādas tiešsaistes apmācības. Es to esmu daudzkārt pieminējis savos rakstos. Šajā rakstā mēs uzzināsim par nosaukto diapazonu programmā Excel un izpētīsim katru tā aspektu.
Nosauktie diapazoni programmā Excel
Formulas -> Vārdu pārvaldnieks -> Definēt vārdu
Nosauktie diapazoni ir nekas cits kā daži Excel diapazoni, kuriem tiek piešķirts nozīmīgs nosaukums. Piemēram, ja jums ir šūna, piemēram, B1, kas satur ikdienas mērķus, 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 saīsni CTRL + F3. Vai arī varat veikt šīs darbības.
- Iet uz Formula Tab
- Atrodiet sadaļu Definētie vārdi un noklikšķiniet uz Definēt vārdus. Tas atvērs Vārds 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 ir pabeigts.
Tagad jūs varat atsaukties uz to, vienkārši ierakstot tā nosaukumu.
Veidojot nosaukumus, jāievēro daži noteikumi. Te ir daži.
- 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 utt.
- 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.
Lielāko daļu laika jūs strādāsit ar strukturētu datu tabulām. 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 automātiskai nosaukšanai, izmantojot virsrakstus. 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 tikai augšējo rindu, jo vēlos nosaukt šo diapazonu kā virsrakstu un nevēlos nosaukt rindas.
- Noklikšķiniet uz Labi.
Tagad katra kolonna ir nosaukta par to virsrakstu. Ievadot formulu, šie nosaukumi tiks uzskaitīti kā opcijas, kurām var piekļūt.
Kad Excel tabulējam datus, izmantojot CTRL + T, slejas virsraksts automātiski tiek piešķirts kā attiecīgās slejas nosaukums. Jums vajadzētu izpētīt Excel tabulas un to priekšrocības.
Būs reizes, kad vēlaties redzēt visus pieejamos nosauktos diapazonus darbgrāmatā. Lai skatītu visus nosaukumu diapazonus Nospiediet CTRL+F3. Vai arī varat doties uz Cilne Formula > Vārdu pārvaldnieks. Tas uzskaitīs visus nosauktos diapazonus, 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 ir labi. Tas novērš neskaidrību iespēju.
Lapā iegūstiet nosaukto diapazonu sarakstu
Tātad, ja vēlaties izveidot nosaukto diapazonu sarakstu un to aptvertos diapazonus, varat izmantot šo saīsni, lai tos ielīmētu lapā.
- Atlasiet šūnu, kurā vēlaties iegūt nosaukto diapazonu sarakstu.
- Nospiediet F3. Tas atvērs a ielīmējiet sarakstu dialoglodziņš.
- Noklikšķiniet uz ielīmēšanas sarakstu pogu.
- Saraksts tiks ielīmēts atlasītajās šūnās 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 diapazons. Lai atjauninātu nosauktos diapazonus, rīkojieties šādi.
- Nospiediet CTRL+F3 lai atvērtu vārdu menedžeris.
- 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
Izdzēšot nosauktā diapazona summas daļu, tā automātiski pielāgo savu diapazonu. Bet, izdzēšot visu nosaukumu diapazonu, tas pazūd no vārdu saraksta. Jebkura formula atkarībā no šiem diapazoniem parādīs #REF kļūdu vai arī sniegs nepareizu izvadi (skaitīšanas funkcijas).
Jebkura iemesla dēļ, ja vēlaties dzēst nosauktos diapazonus, vienkārši izpildiet šīs darbības.
- 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īkus, lai dzēstu tikai 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ā filtra augšējā labajā 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
Ar formulām tiek pētīts, kā vislabāk izmantot nosauktos diapazonus. Formulas kļūst patiešām elastīgas un lasāmas, izmantojot nosauktos diapazonus. 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 nosaukumiem ir viegli uzrakstīt šo COUNTIF formulu. Vienkārši rakstiet
= COUNTIF (Lieta,"Zīmulis")
Tiklīdz rakstīsiet formulas sākuma iekavu, parādīsies pieejamo nosaukto diapazonu saraksts
Bez nosaukuma jūs uzrakstītu diapazonu Excel funkcijai COUNTIF, kurai, iespējams, vispirms būs jāapskata diapazons, pēc tam atlasiet diapazonu vai ierakstiet to formulā.
Excel apkalpo pieejamos nosaukumu diapazonus.
Nosauktie diapazoni tiek parādīti kā ieteikumi, rakstot jebkuru burtu. Kā Excel parāda formulu sarakstu. Piemēram, ja ierakstāt = u, katra formula un nosauktais diapazons tiks parādīti, sākot ar u, lai tos varētu ē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, varat izveidot vārdu “Client”, un tas attiecas uz “Sundar Pichai”. Tagad, kad rakstīsit = klients jebkurā šūnā, tas parādīs Sundaru Pichai.
Ne tikai tekstu, bet arī numurus, kuriem strādāt, varat piešķirt kā nemainīgus. 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 jūs ierakstāt nosaukta diapazona nosaukumu relatīvajā šūnā nosauktajam diapazonam, tas darbosies kā relatīva 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, tāpēc 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 “Befor”, kas atsaucas 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, lai kur jūs rakstītu formulā “Befor”, tas atsauksies uz šūnu, kas tai atstāta.
Šeit es to iepriekš izmantoju funkcijā 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 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 Labi.
Tagad, lai kur jūs rakstītu “IsInCustomer”, tas pārbaudīs vērtību klientu saraksta kreisajā šūnā.
Tas neļauj jums atkārtot sevi atkal un atkal.
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” gadījumus 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 tāds pats nosaukums un tikai tad.
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ģioni, tas darbosies perfekti.
Viegli orientēties darbgrāmatā
Ir vieglāk orientēties darbgrāmatā ar nosauktajiem diapazoniem. Jums vienkārši jāievada vārda 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ārvietojaties 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 šajā šūnā būs klientu vārdi, kas ietilpst Klientu nosauktajā diapazonā. 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
Ceru, ka šis raksts par to, kā lietot vārdus formulā programmā Excel, ir paskaidrojošs. Šeit atrodiet citus rakstus par diapazonu nosaukšanu un saistītās Excel formulas. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Nosaukuma lodziņš programmā Excel : Excel nosaukuma lodziņš ir nekas cits kā mazs displeja laukums Excel lapas augšējā kreisajā stūrī, kas parāda aktīvās šūnas nosaukumu vai diapazonus programmā Excel. Atsaucēm varat pārdēvēt šūnu vai masīvu.
Kā iegūt darblapas lapas nosaukumu programmā Excel : CELL funkcija programmā Excel iegūst informāciju par jebkuru darblapu, piemēram, kolonu, saturu, faila nosaukumu utt. Utt. Uzziniet, kā iegūt lapas nosaukumu, izmantojot funkciju CELL.
Kā iegūt secīgu rindu numuru programmā Excel: Dažreiz mums tabulā jāiegūst secīgs rindas numurs, tas var būt sērijas numurs vai jebkas cits. Šajā rakstā mēs uzzināsim, kā Excel numurēt rindas no datu sākuma.
Palieliniet skaitli Excel teksta virknē: Ja jums ir liels vienumu saraksts un jums jāpalielina vecā teksta pēdējais skaitlis programmā Excel, jums būs nepieciešama palīdzība no abām funkcijām TEKSTS un RIGHT.
Populāri raksti:
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.