Hvernig á að stilla gildissvið í Excel

Þegar Microsoft Excel er notað, veit notandinn mjög oft ekki fyrirfram hversu miklar upplýsingar verða í töflunni vegna þess. Þess vegna skiljum við ekki í öllum tilfellum hvaða svið ætti að ná yfir. Þegar öllu er á botninn hvolft er mengi frumna breytilegt hugtak. Til að losna við þetta vandamál er nauðsynlegt að gera sviðsmyndunina sjálfvirka þannig að hún byggist eingöngu á því magni gagna sem notandinn sló inn.

Breytir sjálfkrafa reitum í Excel

Kosturinn við sjálfvirk svið í Excel er að þau gera það mun auðveldara að nota formúlur. Auk þess gera þær kleift að einfalda verulega greiningu flókinna gagna sem innihalda mikinn fjölda formúla, sem innihalda margar aðgerðir. Þú getur gefið þessu sviði nafn og þá verður það uppfært sjálfkrafa eftir því hvaða gögn það inniheldur.

Hvernig á að gera sjálfvirka sviðsbreytingu í Excel

Segjum að þú sért fjárfestir sem þarf að fjárfesta í einhverjum hlut. Fyrir vikið viljum við fá upplýsingar um hversu mikið þú getur þénað samtals allan þann tíma sem peningarnir munu vinna fyrir þetta verkefni. Engu að síður, til þess að fá þessar upplýsingar, þurfum við að fylgjast reglulega með því hversu mikinn heildarhagnað þetta hlutur færir okkur. Gerðu sömu skýrslu og í þessari skjámynd.

Við fyrstu sýn er lausnin augljós: þú þarft bara að leggja saman allan dálkinn. Ef færslur birtast í henni verður upphæðin uppfærð sjálfstætt. En þessi aðferð hefur marga ókosti:

1. Ef vandamálið er leyst á þennan hátt verður ekki hægt að nota hólfin sem eru í dálki B í öðrum tilgangi.
2. Slík borð mun eyða miklu vinnsluminni, sem gerir það ómögulegt að nota skjalið á veikum tölvum.

Þess vegna er nauðsynlegt að leysa þetta vandamál með kraftmiklum nöfnum. Til að búa til þá verður þú að framkvæma eftirfarandi röð aðgerða:

1. Farðu í flipann „Formúlur“ sem er staðsettur í aðalvalmyndinni. Það verður hluti „Skilgreind nöfn“, þar sem er hnappur „Úthluta nafni“ sem við þurfum að smella á.
2. Næst birtist gluggi þar sem þú þarft að fylla út reitina eins og sýnt er á skjámyndinni. Það er mikilvægt að hafa í huga að við þurfum að beita fallinu = TILLÆSING ásamt aðgerðinni CHECKtil að búa til sjálfvirkt uppfærslusvið. 
3. Eftir það þurfum við að nota aðgerðina SUMMA, sem notar hreyfisvið okkar sem rök. 

Eftir að hafa lokið þessum skrefum getum við séð hvernig umfang frumanna sem tilheyra „tekju“ sviðinu er uppfært þegar við bætum við nýjum þáttum þar.

OFFSET virka í Excel

Við skulum skoða aðgerðirnar sem við skráðum í „svið“ reitnum áðan. Að nota aðgerðina FÖRGUN við getum ákvarðað magn sviðs miðað við hversu margar frumur í dálki B eru fylltar. Aðgerðarrökin eru sem hér segir:

1. Byrja klefi. Með þessum rökum getur notandinn gefið til kynna hvaða hólf á bilinu verður talið efst til vinstri. Það mun tilkynna niður og til hægri.
2. Svið á móti línum. Með því að nota þetta svið stillum við fjölda hólfa sem offset ætti að eiga sér stað frá efst til vinstri hólf sviðsins. Þú getur notað ekki aðeins jákvæð gildi, heldur núll og mínus. Í þessu tilviki getur tilfærslan alls ekki átt sér stað, eða hún verður framkvæmd í gagnstæða átt.
3. Svið á móti dálkum. Þessi breytu er svipuð þeirri fyrri, aðeins hún gerir þér kleift að stilla hversu lárétt tilfærsla sviðsins er. Hér geturðu líka notað bæði núll og neikvæð gildi.
4. Magn sviðs í hæð. Raunar gerir titill þessarar röksemdarfærslu okkur ljóst hvað það þýðir. Þetta er fjöldi frumna sem svið ætti að aukast um.
5. Gildi sviðsins í breidd. Röksemdafærslan er svipuð og fyrri, aðeins snertir dálkana.

Þú þarft ekki að tilgreina síðustu tvær rökin ef þú þarft þess ekki. Í þessu tilviki verður sviðsgildið aðeins ein reit. Til dæmis, ef þú tilgreinir formúluna =OFFSET(A1;0;0), mun þessi formúla vísa til sama hólfs og sá í fyrstu röksemdinni. Ef lóðrétta offsetið er stillt á 2 einingar, þá vísar reiturinn í reit A3 í þessu tilfelli. Nú skulum við lýsa í smáatriðum hvað aðgerðin þýðir CHECK.

COUNT fall í Excel

Að nota aðgerðina CHECK við ákveðum hversu margar frumur í dálki B við höfum fyllt samtals. Það er, með því að nota tvær aðgerðir, ákveðum við hversu margar frumur á bilinu eru fylltar, og á grundvelli upplýsinganna sem berast, ákvarðar stærð bilsins. Þess vegna verður lokaformúlan eftirfarandi: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Við skulum skoða hvernig á að skilja meginregluna um þessa formúlu rétt. Fyrstu rökin benda á hvar okkar kraftmikla svið byrjar. Í okkar tilviki er þetta klefi B2. Frekari færibreytur hafa núllhnit. Þetta bendir til þess að við þurfum ekki offset miðað við efsta vinstra hólfið. Allt sem við erum að fylla út er lóðrétt stærð sviðsins, sem við notuðum aðgerðina sem CHECK, sem ákvarðar fjölda frumna sem innihalda einhver gögn. Fjórða færibreytan sem við fylltum út er einingin. Þannig sýnum við að heildarbreidd sviðsins ætti að vera einn dálkur.

Þannig að nota aðgerðina CHECK notandinn getur notað minnið á eins skilvirkan hátt og hægt er með því að hlaða aðeins þær frumur sem innihalda einhver gildi. Samkvæmt því verða engar frekari villur í vinnunni sem tengist lélegri afköstum tölvunnar sem töflureiknið virkar á.

Í samræmi við það, til að ákvarða stærð sviðsins eftir fjölda dálka, þarftu að framkvæma svipaða röð aðgerða, aðeins í þessu tilfelli þarftu að tilgreina eininguna í þriðju færibreytunni og formúluna í fjórða CHECK.

Við sjáum að með hjálp Excel formúla er ekki aðeins hægt að gera sjálfvirkan stærðfræðilega útreikninga. Þetta er bara dropi í hafið, en í raun gera þeir þér kleift að gera sjálfvirkan nánast hvaða aðgerð sem þér dettur í hug.

Kvikmyndir í Excel

Svo, í síðasta skrefi, gátum við búið til kraftmikið svið, stærð þess fer algjörlega eftir því hversu margar fylltar frumur það inniheldur. Nú, byggt á þessum gögnum, geturðu búið til kvik töflur sem breytast sjálfkrafa um leið og notandinn gerir einhverjar breytingar eða bætir við viðbótardálki eða línu. Röð aðgerða í þessu tilviki er sem hér segir:

1. Við veljum svið okkar, eftir það setjum við inn töflu af gerðinni „Histogram with grouping“. Þú getur fundið þetta atriði í „Insert“ hlutanum í „Charts – Histogram“ hlutanum.
2. Við gerum vinstri músarsmellingu á handahófskenndan dálk í súluritinu, eftir það birtist fallið =SERIES() í falllínunni. Á skjámyndinni er hægt að sjá nákvæma formúlu. 
3. Eftir það þarf að gera nokkrar breytingar á formúlunni. Þú þarft að skipta um svið eftir „Sheet1!“ að nafni sviðsins. Þetta mun leiða til eftirfarandi aðgerð: =ROW(Blað1!$B$1;;Blað1!tekjur;1)
4. Nú er eftir að bæta nýrri skrá við skýrsluna til að athuga hvort grafið sé uppfært sjálfkrafa eða ekki.

Við skulum kíkja á skýringarmyndina okkar.

Við skulum draga saman hvernig við gerðum það. Í fyrra skrefi bjuggum við til kraftmikið svið, stærð þess fer eftir því hversu marga þætti það inniheldur. Til að gera þetta notuðum við blöndu af aðgerðum CHECK и FÖRGUN. Við gáfum þessu svið nafn og síðan notuðum við tilvísunina í þetta nafn sem svið súluritsins okkar. Hvaða tiltekna svið á að velja sem gagnagjafa á fyrsta stigi er ekki svo mikilvægt. Aðalatriðið er að skipta því út fyrir nafn sviðsins síðar. Þannig geturðu sparað mikið af vinnsluminni.

Nafngreind svið og notkun þeirra

Við skulum nú tala nánar um hvernig á að búa til nafngreind svið rétt og nota þau til að framkvæma verkefnin sem eru stillt fyrir Excel notandann.

Sjálfgefið er að við notum venjuleg farsímaföng til að spara tíma. Þetta er gagnlegt þegar þú þarft að skrifa svið einu sinni eða oftar. Ef það þarf að nota það allan tímann eða það þarf að vera aðlögunarhæft, þá ætti að nota nafngreind svið. Þeir gera það mun auðveldara að búa til formúlur og það verður ekki svo erfitt fyrir notandann að greina flóknar formúlur sem innihalda mikinn fjölda aðgerða. Við skulum lýsa nokkrum af þeim skrefum sem taka þátt í að búa til kvik svið.

Þetta byrjar allt með því að gefa frumunni nafn. Til að gera þetta skaltu einfaldlega velja það og skrifa síðan nafnið sem við þurfum í reitinn fyrir nafn þess. Það er mikilvægt að það sé auðvelt að muna það. Það eru nokkrar takmarkanir sem þarf að hafa í huga við nafngift:

1. Hámarkslengd er 255 stafir. Þetta er alveg nóg til að gefa nafn sem hjartað þráir.
2. Nafnið má ekki innihalda bil. Þess vegna, ef það inniheldur nokkur orð, þá er hægt að aðskilja þau með undirstrikinu.

Ef síðar á öðrum blöðum þessarar skráar þurfum við að sýna þetta gildi eða nota það til að framkvæma frekari útreikninga, þá er engin þörf á að skipta yfir í fyrsta blaðið. Þú getur bara skrifað niður nafnið á þessum sviðshólfi.

Næsta skref er að búa til nafngreint svið. Aðferðin er í grundvallaratriðum sú sama. Fyrst þarftu að velja svið og tilgreina síðan nafn þess. Eftir það er hægt að nota þetta nafn í öllum öðrum gagnaaðgerðum í Excel. Til dæmis eru nefnd svið oft notuð til að skilgreina summu gilda.

Að auki er hægt að búa til nafngreint svið með því að nota formúluflipann með því að nota Set Name tólið. Eftir að við höfum valið það birtist gluggi þar sem við þurfum að velja nafn á úrvalið okkar, auk þess að tilgreina svæðið sem það mun ná til handvirkt. Þú getur líka tilgreint hvar þetta svið mun starfa: innan eins blaðs eða í gegnum bókina.

Ef nafnasvið hefur þegar verið búið til, þá er sérstök þjónusta sem kallast nafnastjóri til að nota það. Það gerir ekki aðeins kleift að breyta eða bæta við nýjum nöfnum, heldur einnig að eyða þeim ef þeirra er ekki lengur þörf.

Hafa ber í huga að þegar nafngreind svið eru notuð í formúlum, eftir að þeim hefur verið eytt, verður formúlunum ekki sjálfkrafa skrifað yfir með réttum gildum. Þess vegna geta villur komið upp. Þess vegna, áður en þú eyðir nafngreindu sviði, þarftu að ganga úr skugga um að það sé ekki notað í neinni af formúlunum.

Önnur leið til að búa til nafngreint svið er að fá það úr töflu. Til að gera þetta er sérstakt tól sem heitir „Búa til úr vali“. Eins og við skiljum, til að nota það, verður þú fyrst að velja svið sem við munum breyta og síðan stilla staðinn þar sem við höfum fyrirsagnirnar. Þar af leiðandi, byggt á þessum gögnum, mun Excel sjálfkrafa vinna úr öllum gögnum og titlum verður sjálfkrafa úthlutað.

Ef titillinn inniheldur nokkur orð mun Excel sjálfkrafa aðgreina þau með undirstrik.

Þannig komumst við að því hvernig á að búa til kraftmikil nafngreind svið og hvernig þau gera þér kleift að gera sjálfvirkan vinnu með mikið magn af gögnum. Eins og þú sérð er nóg að nota nokkrar aðgerðir og forritatólin sem eru innbyggð í virknina. Það er alls ekkert flókið, þó það kunni að virðast svo fyrir byrjendur við fyrstu sýn.