Kaip naudoti vardus formulėse „Excel“

Šiame straipsnyje sužinosime, kaip naudoti vardus „Excel“ formulėje.

Scenarijus:

Dirbdami su „Excel“ turite būti girdėję apie „Excel“ nurodytus diapazonus. Galbūt iš draugo, kolegos ar kokios nors internetinės pamokos. Aš tai daug kartų minėjau savo straipsniuose. Šiame straipsnyje mes sužinosime apie „Excel“ pavadintą diapazoną ir išnagrinėsime kiekvieną jo aspektą.

Pavadinti diapazonai „Excel“

Formulės -> Vardų tvarkyklė -> Apibrėžkite vardą

Na, pavadinti diapazonai yra ne kas kita, kaip kai kurie „Excel“ diapazonai, kuriems suteiktas prasmingas pavadinimas. Pavyzdžiui, jei turite langelį B1, kuriame yra kasdienių tikslų, galite pavadinti tą langelį kaip „Tikslą“. Dabar galite naudoti „Target“, kad nurodytumėte A1, o ne rašytumėte B1.

Trumpai tariant, pavadintas diapazonas yra tik diapazonų pavadinimai.

Kaip pavadinti diapazoną „Excel“?

Nustatykite vardą rankiniu būdu:

Norėdami apibrėžti diapazono pavadinimą, galite naudoti nuorodą CTRL + F3. Arba galite atlikti šiuos veiksmus.

Eiti į Formulė Skirtukas
Raskite skyrių „Apibrėžti vardai“ ir spustelėkite „Apibrėžti vardus“. Tai atvers Pavadinimas Manger.
Spustelėkite Naujas.
Įveskite Pavadinimas.
Pasirinkite apimtį (darbaknygę ar lapą)
Jei norite, parašykite komentarą.
Laukelyje „Nurodo langelį“ parašykite nuorodą arba pasirinkite diapazoną naudodami pelę.
Spustelėkite Gerai. Padaryta.

Dabar galite kreiptis į jį tiesiog įvesdami jo pavadinimą.

Kuriant pavadinimus reikia laikytis tam tikrų taisyklių. Stai keleta.

Pavadinimai neturėtų prasidėti nuo skaitmenų ar specialiųjų simbolių, išskyrus pabraukimą (_) ir pasvirąjį brūkšnį (\).
Pavadinimuose negali būti tarpų ir jokių specialių simbolių, išskyrus _ ir \.
Diapazonas neturėtų būti vadinamas ląstelių nuoroda. Pavyzdžiui, A1, B1 arba AZ100 ir kt.
Negalite pavadinti diapazono „r“ ir „c“, nes jie skirti eilučių ir stulpelių nuorodoms.
Du pavadinti diapazonai negali turėti to paties pavadinimo darbo knygoje.
Tas pats diapazonas gali turėti kelis pavadinimus.

Na, dažniausiai dirbsite su struktūrinių duomenų lentelėmis. Jie turės stulpelius ir eilutes su stulpelių antraštėmis ir eilučių antraštėmis. Ir dažniausiai šie pavadinimai yra reikšmingi duomenims, ir jūs norite pavadinti savo diapazoną kaip šias stulpelių antraštes. „Excel“ suteikia įrankį automatiškai pavadinti diapazonus naudojant antraštes. Atlikite šiuos veiksmus.

Diapazonai, kuriuos norite įvardyti kaip jų antraštes
Paspauskite CTRL+SHIFT+F3, arba Raskite apibrėžtų vardų skyrių skirtuke „Formulė“ ir spustelėkite „Sukurti iš pasirinkimo“.
Bus parodytas žemiau esantis parinkčių langelis. Pasirinkau tik viršutinę eilutę, nes noriu pavadinti šį diapazoną kaip antraštę ir nenoriu pavadinti eilučių.
Spustelėkite Gerai.

Dabar kiekvienas stulpelis pavadintas jų antrašte. Kai įvedate formulę, šie pavadinimai bus išvardyti kaip parinktys, kurias galima pasiekti.

Kai „Excel“ lentelėje pateikiame duomenis naudodami CTRL + T, stulpelio antraštė automatiškai priskiriama atitinkamo stulpelio pavadinimui. Turėtumėte ištirti „Excel“ lenteles ir jų naudą.

Na, bus atvejų, kai norėsite matyti visus galimus pavadintus diapazonus darbo knygoje. Norėdami pamatyti visus vardų diapazonus, paspauskite CTRL+F3. Arba galite eiti į Formulės skirtukas > Vardų vadybininkas. Čia bus išvardyti visi pavadinti diapazonai, kuriuos galima rasti darbaknygėje. Galite redaguoti turimus pavadintus diapazonus, juos ištrinti, pridėti naujų pavadinimų.

Vieno diapazono keli pavadinimai

„Excel“ leidžia vartotojams tą patį diapazoną pavadinti skirtingais pavadinimais. Pavyzdžiui, diapazonas A2: A10 vienu metu gali būti pavadintas „Klientai“ ir „Klientai“. Abu pavadinimai nurodys tą patį diapazoną A2: A10.

Bet jūs negalite turėti tų pačių pavadinimų dviem skirtingiems diapazonams. Tai yra gerai. Tai pašalina dviprasmybių galimybę.

Gaukite įvardytų diapazonų sąrašą lape

Taigi, jei norite turėti pavadintų diapazonų sąrašą ir diapazonus, kuriuos jie apima, galite naudoti šį nuorodą, kad įklijuotumėte juos į lapą.

Pasirinkite langelį, kuriame norite gauti pavadintų diapazonų sąrašą.
Paspauskite F3. Tai atvers a įklijuoti sąrašą dialogo langas.
Spustelėkite įklijuoti sąrašą mygtuką.
Sąrašas bus įklijuotas pasirinktose ląstelėse ir toliau.

Jei dukart spustelėsite pavadintų diapazonų pavadinimą įklijavimo pavadinimo lauke, jie bus parašyti kaip formulės langelyje. Pabandyk tai.

Rankiniu būdu atnaujinkite pavadintus diapazonus

Na, kai įterpiate langelį į pavadintą diapazoną, jis automatiškai atnaujinamas ir išplečiamas. Bet jei lentelės pabaigoje pridėsite duomenų, turėsite atnaujinti pavadintą diapazoną. Norėdami atnaujinti pavadintus diapazonus, atlikite šiuos veiksmus.

Paspauskite CTRL+F3 atidaryti vardų vadybininkas.
Spustelėkite pavadintą diapazoną, kurį norite redaguoti. Spustelėkite Redaguoti.
Skiltyje „Nurodo stulpelį“ įveskite diapazoną, į kurį norite išplėsti, ir paspauskite Gerai.

Ir tai padaryta. Tai rankinis pavadintų diapazonų atnaujinimas. Tačiau mes galime padaryti jį dinamišką naudodami tam tikras formules.

Dinamiškai atnaujinkite pavadintus diapazonus

Išmintinga padaryti pavadintus diapazonus dinamiškus, kad nereikėtų jų redaguoti, kai duomenys viršija iš anksto nustatytą diapazoną.

Aš tai apžvelgiau atskirame straipsnyje „Dinaminiai pavadinti diapazonai“. Čia galite išsamiai sužinoti ir suprasti jo naudą.

Pavadintų diapazonų trynimas

Kai ištrinate įvardyto diapazono suminę dalį, ji automatiškai koreguoja savo diapazoną. Bet kai ištrinate visą vardų diapazoną, jis išnyksta iš vardų sąrašo. Bet kuri formulė, priklausomai nuo šių diapazonų, rodys #REF klaidą arba duos neteisingą išvestį (skaičiavimo funkcijos).

Dėl kokių nors priežasčių, jei norite ištrinti pavadintus diapazonus, atlikite šiuos veiksmus.

Paspauskite CTRL+F3. Bus atidarytas vardų tvarkytojas.
Pasirinkite pavadintus diapazonus, kuriuos norite ištrinti.
Spustelėkite mygtuką „Ištrinti“ arba klaviatūros mygtuką „Ištrinti“.

Atsargiai: Prieš ištrindami nurodytus diapazonus, įsitikinkite, kad jokios formulės nepriklauso nuo šių pavadinimų. Jei tokių yra, pirmiausia konvertuokite juos į diapazonus. Priešingu atveju pamatysite #REF klaidą.

Vardų ištrynimas su klaidomis

„Excel“ siūlo įrankius, skirtus tik pavadinimams, kuriuose yra klaidų, ištrinti. Jums nereikia identifikuoti kiekvieno iš jų. Jei norite ištrinti pavadinimus su klaidomis, atlikite šiuos veiksmus:

Atidarykite vardų tvarkyklę (CTRL+F3).
Spustelėkite išskleidžiamąjį meniu Filtras viršutiniame dešiniajame kampe.
Pasirinkite „Vardas su klaidomis“
Pasirinkite Visi ir paspauskite ištrynimo mygtuką.

Ir jų nebėra. Visi pavadinimai su klaidomis bus nedelsiant ištrinti iš įrašo.

Pavadinti diapazonai su formulėmis

Geriausias vardinių diapazonų panaudojimas tiriamas naudojant formules. Formulės yra tikrai lanksčios ir lengvai skaitomos naudojant pavadintus diapazonus. Pažiūrėkime, kaip.

Lengva rašyti formules

Dabar tarkime, kad pavadinote diapazoną kaip „Elementai“. Dabar elementų sąraše norite suskaičiuoti „Pieštukai“. Su pavadinimais lengva parašyti šią COUNTIF formulę. Tiesiog rašyk

= COUNTIF (Prekė,"Pieštukas")

Kai tik parašysite formulės pradinį skliaustą, pasirodys galimų pavadintų diapazonų sąrašas

Be pavadinimo „Excel“ funkcijai COUNTIF parašytumėte diapazoną, kuriam pirmiausia gali tekti pažvelgti į diapazoną, tada pasirinkti diapazoną arba įvesti jį į formulę.

„Excel“ aptarnauja galimus pavadinimų diapazonus.

Įvardyti diapazonai rodomi kaip pasiūlymai, kai įvedate bet kokią raidę. Kaip „Excel“ rodo formulių sąrašą. Pavyzdžiui, jei įvedate = u, kiekviena formulė ir pavadintas diapazonas bus rodomi pradedant u, kad galėtumėte juos lengvai naudoti.

Sudarykite konstantas naudodami pavadintus diapazonus

Iki šiol mes sužinojome apie diapazonų pavadinimus, tačiau iš tikrųjų galite įvardyti ir vertes. Pvz., Jei jūsų kliento vardas yra Sunder Pichai, tuomet galite pavadinti „Client“ ir tai reiškia rašyti „Sundar Pichai“. Dabar, kai rašysite = klientas bet kurioje ląstelėje, bus rodomas Sundar Pichai.

Ne tik tekstą, bet ir numerius galite priskirti pastoviems darbui. Pavyzdžiui, jūs nustatote tikslą. Arba to, kas nesikeis, vertė.

Absoliuti ir santykinė nuoroda su pavadintais diapazonais

Nuoroda į pavadintus diapazonus yra labai lanksti. Pavyzdžiui, jei į pavadintą diapazoną įrašysite pavadinto diapazono pavadinimą į santykinį langelį, jis elgsis kaip santykinė nuoroda. Žiūrėkite žemiau esantį paveikslėlį.

Tačiau kai jį naudosite su formulėmis, jis elgsis kaip absoliutus. Na, dažniausiai jūs juos naudosite su formulėmis, taigi galite sakyti, kad jie pagal nutylėjimą yra Absoliutūs bet iš tikrųjų jie yra lankstūs.

Bet mes taip pat galime juos padaryti santykiniais.

Kaip sukurti santykinius pavadintus diapazonus „Excel“?

Tarkime, jei noriu pavadinti diapazoną „Befor“, kuris nurodys langelį kairėje, kur jis parašytas. Kaip tai padaryti? Atlikite šiuos veiksmus:

Paspauskite CTRL+F3
Spustelėkite Naujas
Skiltyje „Vardas“ įveskite „Befor“.
Skiltyje „Nurodo:“ kairėje parašykite langelio adresą. Pavyzdžiui, jei esate B1 langelyje, skiltyje „Nurodo:“ parašykite „= A2“. Įsitikinkite, kad jame nėra $ ženklo.

Visur, kur formulėje rašysite „Befor“, ji nurodys langelį, paliktą joje.

Čia aš jį anksčiau naudoju stulpelio funkcijoje. Formulė grąžina kairiojo langelio, kuriame jis parašytas, stulpelio numerį. Mano nuostabai, A1 rodo paskutinio stulpelio stulpelio numerį. Tai reiškia, kad lapas yra apskritas. Maniau, kad bus rodoma #REF klaida.

Suteikti pavadinimą dažnai naudojamoms formulėms?

Dabar šis yra nuostabus. Daug kartų darbalapyje vėl ir vėl naudojate tą pačią formulę. Pavyzdžiui, galbūt norėsite patikrinti, ar vardas yra jūsų klientų sąraše, ar ne. Ir šis poreikis gali atsirasti daug kartų. Tam kiekvieną kartą parašysite tą pačią sudėtingą formulę.

= IF (COUNTIF (Klientas, I3), „Sąraše“, „Ne sąraše“)

Ką daryti, jei langelyje tiesiog įvedate „= IsInCustomer“ ir jis parodys, ar vertė kairiajame langelyje yra klientų sąraše, ar ne?

Pavyzdžiui, aš čia paruošiau lentelę. Dabar aš tiesiog noriu J5 įvesti „= IsInCustomer“ ir norėčiau pamatyti, ar vertė yra I5 yra klientų sąraše arba ne. Norėdami tai padaryti, atlikite šiuos veiksmus.

Paspauskite CTRL+F3
Spustelėkite Naujas
Varde parašykite „IsInCustomer“
Skiltyje „Nurodo“ parašykite savo formulę. = IF (COUNTIF (Klientas, I5), „Sąraše“, „Ne sąraše“)
Paspauskite mygtuką Gerai.

Dabar, kad ir kur rašytumėte „IsInCustomer“, jis patikrins vertę kairiajame klientų sąrašo langelyje.

Tai neleidžia jums kartoti savęs vėl ir vėl.

Formulėms taikyti pavadintus diapazonus

Tiek kartų mes apibrėžiame savo diapazonų pavadinimus po to, kai jau parašėme formules pagal diapazonus. Pavyzdžiui, aš turiu bendrą kainą kaip langelius = E2*F2. Kaip mes galime tai pakeisti Vienetai*Vieneto_kaina.

Pasirinkite formules.
Eikite į formulės skirtuką. Spustelėkite išskleidžiamąjį meniu Apibrėžti vardą.
Spustelėkite Taikyti vardus.
Bus rodomas visų pavadintų diapazonų sąrašas. Pasirinkite tinkamus vardus ir spustelėkite Gerai.

Ir dabar pavadinimai taikomi. Tai galite pamatyti formulės juostoje.

Lengvai skaitomos formulės su pavadintais diapazonais

Kaip matėte, pavadinti diapazonai leidžia lengvai skaityti formules. Jei rašysiu = COUNTIF („A2: A100“, B2), niekas nesupras, ką aš bandau suskaičiuoti, kol nepamatys duomenų arba kas nors jiems to nepaaiškins.

Bet jei parašysiu = COUNTIF (regionas, „rytai“), dauguma vartotojų iškart supras, kad skaičiuojame „rytų“ įvykius regione, pavadintame diapazone.

Nešiojamos formulės

Įvardyti diapazonai leidžia labai lengvai kopijuoti ir įklijuoti formules, nesirūpinant dėl nuorodų keitimo. Ir jūs galite perkelti vieną formulę iš vienos darbaknygės į kitą ir ji veiks gerai, kol paskirties darbaknygė bus to paties pavadinimo.

Pavyzdžiui, jei turite formulę = COUNTIF (regionas, rytai) paskirstymo lentelėje ir turite kitą darbaknygę klientų kuris taip pat turi pavadintą diapazoną „Regionas“. Dabar, jei nukopijuosite šią formulę bet kur toje darbaknygėje, ji parodys teisingą informaciją. Duomenų struktūra nebus svarbi. Nesvarbu, kur, po velnių, yra jūsų darbo knygos stulpelis. Tai veiks teisingai.

Aukščiau esančiame paveikslėlyje aš naudojau tą pačią formulę dviejuose skirtinguose failuose, kad suskaičiuotų regionų sąraše esantį skaičių arba rytus. Dabar jie yra skirtinguose stulpeliuose, tačiau kadangi abu jie yra pavadinti regionais, jie veiks puikiai.

Lengvai naršykite darbaknygėje

Darosi lengviau naršyti darbaknygėje su pavadintais diapazonais. Jums tereikia įvesti vardo pavadinimą pavadinimo laukelyje. „Excel“ nuves jus į diapazoną, nesvarbu, kur esate darbaknygėje. Atsižvelgiant į tai, kad nurodytas diapazonas yra darbaknygės apimtis.

Pavyzdžiui, jei esate 10 lape ir norite gauti klientų sąrašą ir nežinote, kuriame lape jis yra. Tiesiog eikite į pavadinimo laukelį ir įveskite „klientas“. Per sekundės dalį būsite nukreipti į nurodytą diapazoną.

Tai sumažins pastangas prisiminti diapazonus.

Naršykite naudodami hipersaitus su įvardytu diapazonu

Kai jūsų lapas yra didelis ir dažnai pereinate iš vieno taško į kitą, mėgstate lengvai naršyti naudodami hipersaitus. Gerai pavadinti diapazonai gali puikiai veikti su hipersaitais. Norėdami pridėti hipersaitų naudodami pavadintus diapazonus, atlikite šiuos veiksmus.

Pasirinkite langelį, kuriame norite hipersaito
Paspauskite CTRL+K arba eikite į skirtuką Įterpti> HyperLink, kad atidarytumėte dialogo langą Įterpti hipersaitą.
Spustelėkite Vieta šiame dokumente.
Slinkite žemyn, kad pamatytumėte galimus pavadintus diapazonus skiltyje „Apibrėžti vardai“
Pasirinkite pavadintą diapazoną, kad įterptumėte hipersaitą į tą diapazoną.

Ir padaryta. Jūs turite savo hipersaitą į pasirinktą pavadintą diapazoną. Naudodami tai galite sukurti pavadintų diapazonų indeksą, kurį galite pamatyti ir spustelėti, jei norite tiesiogiai pereiti prie jų. Tai padarys jūsų darbaknygę tikrai patogią vartotojui.

Pavadintas diapazonas ir duomenų patvirtinimas

Įvardyti diapazonai ir duomenų patvirtinimas yra sukurti vienas kitam. Įvardyti diapazonai leidžia labai pritaikyti duomenų patvirtinimą. Daug lengviau pridėti patvirtinimą iš sąrašo naudojant pavadintą diapazoną. Pažiūrėkime, kaip…

Eikite į skirtuką Duomenys
Spustelėkite Duomenų patvirtinimas
Skiltyje „Leisti:“ pasirinkite Sąrašas
Skiltyje „Šaltinis:“ įveskite „= Klientas“ (parašykite bet kurį pavadintą diapazoną)
Spustelėkite Gerai

Dabar šioje ląstelėje bus klientų, priklausančių klientų pavadinimų diapazonui, vardai. Lengva, ar ne.

Priklausomas arba pakopinis duomenų patvirtinimas su įvardytais diapazonais

O kas, jei norite pakopinio ar priklausomo duomenų patvirtinimo. Pavyzdžiui, jei norite išskleidžiamojo sąrašo, kuriame yra kategorijos „Vaisiai ir daržovės“. Dabar, jei pasirinksite vaisius, kitame išskleidžiamajame meniu turėtų būti rodomas tik vaisių pasirinkimas, o jei pasirinksite daržoves, tada tik daržovės.

Tai galima lengvai pasiekti naudojant pavadintus diapazonus. Išmokti kaip.

Priklausomas išskleidimas naudojant pavadintą diapazoną
Kiti pakopinių duomenų patvirtinimo būdai

Tikimės, kad šis straipsnis apie tai, kaip naudoti vardus „Excel“ formulėje, yra aiškinamasis. Čia rasite daugiau straipsnių apie diapazonų pavadinimus ir susijusias „Excel“ formules. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums el.

Pavadinimo langelis „Excel“ : „Excel“ pavadinimo laukas yra ne kas kita, kaip maža rodymo sritis „Excel“ lapo viršuje kairėje, rodanti aktyvios ląstelės pavadinimą arba „Excel“ diapazonus. Galite pervardyti langelį ar masyvą nuorodoms.

Kaip gauti „Excel“ darbalapio pavadinimą : „CELL“ funkcija „Excel“ suteikia jums informaciją apie bet kurį darbalapį, pvz., Stulpelį, turinį, failo pavadinimą ir tt. Sužinokite, kaip gauti lapo pavadinimą naudojant funkciją CELL

Kaip gauti nuoseklų eilutės numerį „Excel“: Kartais lentelėje turime gauti eilės numerį, tai gali būti eilės numeris ar bet kas kitas. Šiame straipsnyje mes išmoksime skaičiuoti „Excel“ eilutes nuo duomenų pradžios.

Padidinkite skaičių „Excel“ teksto eilutėje: Jei turite didelį elementų sąrašą ir jums reikia padidinti paskutinį senojo teksto teksto skaičių programoje „Excel“, jums reikės pagalbos iš dviejų TEKSTO ir DEŠINĖS funkcijų.

Populiarūs straipsniai:

Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.

Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.

Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.

Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.