Kuidas Excelis väärtuste vahemikku määrata

Microsoft Exceli kasutamisel ei tea kasutaja väga sageli ette, kui palju teavet selle tulemusel tabelisse tuleb. Seetõttu ei saa me kõigis olukordades aru, millist vahemikku tuleks hõlmata. Lahtrite kogum on ju muutuv mõiste. Sellest probleemist vabanemiseks on vaja muuta vahemiku genereerimine automaatseks, et see põhineks ainult kasutaja sisestatud andmete hulgal.

Lahtrite vahemike automaatne muutmine Excelis

Exceli automaatsete vahemike eeliseks on see, et need muudavad valemite kasutamise palju lihtsamaks. Lisaks võimaldavad need oluliselt lihtsustada keeruliste andmete analüüsi, mis sisaldavad suurt hulka valemeid, mis sisaldavad palju funktsioone. Saate anda sellele vahemikule nime ja seejärel värskendatakse seda automaatselt sõltuvalt sellest, milliseid andmeid see sisaldab.

Kuidas teha Excelis automaatset vahemiku muutmist

Oletame, et olete investor, kes peab investeerima mõnda objekti. Selle tulemusena tahame saada teavet selle kohta, kui palju saate kogu selle aja jooksul, mil raha selle projekti jaoks töötab, teenida. Sellegipoolest peame selle teabe saamiseks regulaarselt jälgima, kui palju kogukasumit see objekt meile toob. Koostage sama aruanne, mis sellel ekraanipildil.

Esmapilgul on lahendus ilmne: peate lihtsalt kogu veeru kokku võtma. Kui sinna ilmuvad kanded, uuendatakse summat iseseisvalt. Kuid sellel meetodil on palju puudusi:

1. Kui probleem sel viisil lahendatakse, ei saa veerus B sisalduvaid lahtreid muuks otstarbeks kasutada.
2. Selline tabel tarbib palju RAM-i, mis muudab dokumendi kasutamise nõrkades arvutites võimatuks.

Seetõttu on vaja see probleem lahendada dünaamiliste nimede kaudu. Nende loomiseks peate tegema järgmise toimingute jada:

1. Minge peamenüüs asuvale vahekaardile "Valemid". Seal on jaotis "Määratletud nimed", kus on nupp "Määra nimi", millel peame klõpsama.
2. Järgmisena ilmub dialoogiboks, kus peate täitma väljad, nagu on näidatud ekraanipildil. Oluline on märkida, et peame funktsiooni rakendama = NIHETUS koos funktsiooniga KONTROLLIMAautomaatse värskendamise vahemiku loomiseks. 
3. Pärast seda peame funktsiooni kasutama SUM, mis kasutab argumendina meie dünaamilist ulatust. 

Pärast nende sammude sooritamist näeme, kuidas "sissetuleku" vahemikku kuuluvate lahtrite katvus uueneb, kui lisame sinna uusi elemente.

OFFSET funktsioon Excelis

Vaatame funktsioone, mille me varem väljale “vahemik” salvestasime. Funktsiooni kasutamine KÕRVALDAMINE saame määrata vahemiku suuruse, kui palju lahtreid veerus B on täidetud. Funktsiooni argumendid on järgmised:

1. Käivitage lahter. Selle argumendiga saab kasutaja näidata, millist vahemiku lahtrit loetakse üleval vasakul. See annab aru alla ja paremale.
2. Vahemiku nihe ridade kaupa. Seda vahemikku kasutades määrame lahtrite arvu, mille võrra peaks toimuma nihe vahemiku vasakpoolsest ülaosast. Võite kasutada mitte ainult positiivseid väärtusi, vaid ka nulli ja miinust. Sel juhul ei pruugi nihe üldse toimuda või see viiakse läbi vastupidises suunas.
3. Vahemiku nihe veergude kaupa. See parameeter on sarnane eelmisele, ainult see võimaldab teil määrata vahemiku horisontaalse nihke astme. Siin saate kasutada ka nii nulli kui ka negatiivseid väärtusi.
4. Kõrguse ulatus. Tegelikult teeb selle argumendi pealkiri meile selgeks, mida see tähendab. See on lahtrite arv, mille võrra vahemik peaks suurenema.
5. Vahemiku väärtus laiuses. Argument on sarnane eelmisele, ainult see puudutab veerge.

Te ei pea kahte viimast argumenti täpsustama, kui te seda ei vaja. Sel juhul on vahemiku väärtus ainult üks lahter. Näiteks kui määrate valemi =OFFSET(A1;0;0), viitab see valem samale lahtrile, mis esimeses argumendis. Kui vertikaalnihkeks on seatud 2 ühikut, viitab lahter sel juhul lahtrile A3. Nüüd kirjeldame üksikasjalikult, mida see funktsioon tähendab KONTROLLIMA.

COUNT funktsioon Excelis

Funktsiooni kasutamine KONTROLLIMA määrame, mitu lahtrit veerus B oleme kokku täitnud. See tähendab, et kahe funktsiooni abil määrame kindlaks, mitu lahtrit vahemikus on täidetud, ja saadud teabe põhjal määrame vahemiku suuruse. Seetõttu on lõplik valem järgmine: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Vaatame, kuidas selle valemi põhimõtet õigesti mõista. Esimene argument viitab sellele, kust meie dünaamiline ulatus algab. Meie puhul on see lahter B2. Teistel parameetritel on nullkoordinaadid. See viitab sellele, et me ei vaja ülemise vasakpoolse lahtri suhtes nihkeid. Kõik, mida me täidame, on vahemiku vertikaalne suurus, mille funktsiooni kasutasime KONTROLLIMA, mis määrab andmeid sisaldavate lahtrite arvu. Neljas parameeter, mille me täitsime, on ühik. Seega näitame, et vahemiku kogulaius peaks olema üks veerg.

Seega, kasutades funktsiooni KONTROLLIMA kasutaja saab kasutada mälu võimalikult tõhusalt, laadides ainult need lahtrid, mis sisaldavad mõnda väärtust. Sellest tulenevalt ei esine töös täiendavaid vigu, mis on seotud selle arvuti halva jõudlusega, milles tabel töötab.

Sellest tulenevalt peate veergude arvust sõltuva vahemiku suuruse määramiseks tegema sarnase toimingute jada, ainult sel juhul peate määrama kolmandas parameetris ühiku ja neljandas valemi. KONTROLLIMA.

Näeme, et Exceli valemite abil ei saa te ainult matemaatilisi arvutusi automatiseerida. See on vaid piisk meres, kuid tegelikult võimaldavad need automatiseerida peaaegu kõik toimingud, mis meelde tulevad.

Dünaamilised diagrammid Excelis

Nii saime viimases etapis luua dünaamilise vahemiku, mille suurus sõltub täielikult sellest, kui palju täidetud lahtreid see sisaldab. Nüüd saate nende andmete põhjal luua dünaamilisi diagramme, mis muutuvad automaatselt niipea, kui kasutaja teeb muudatusi või lisab täiendava veeru või rea. Toimingute jada on sel juhul järgmine:

1. Valime oma vahemiku, mille järel sisestame tüübi "Histogramm koos rühmitusega" diagrammi. Selle üksuse leiate jaotise "Diagrammid – histogramm" jaotisest "Sisesta".
2. Teeme histogrammi juhuslikul veerul hiire vasaku klõpsu, mille järel kuvatakse funktsiooni real funktsioon =SERIES(). Ekraanipildil näete üksikasjalikku valemit. 
3. Pärast seda tuleb valemis teha mõned muudatused. Peate asendama vahemiku pärast „Sheet1!” vahemiku nimele. Selle tulemuseks on järgmine funktsioon: =RIDA(leht1!$B$1;;leht1!sissetulek;1)
4. Nüüd jääb üle aruandele uus kirje lisada, et kontrollida, kas diagrammi värskendatakse automaatselt või mitte.

Vaatame oma diagrammi.

Teeme kokkuvõtte, kuidas me seda tegime. Eelmises etapis lõime dünaamilise vahemiku, mille suurus sõltub sellest, kui palju elemente see sisaldab. Selleks kasutasime funktsioonide kombinatsiooni KONTROLLIMA и KÕRVALDAMINE. Panime sellele vahemikule nime ja seejärel kasutasime selle nime viidet oma histogrammi vahemikuna. See, milline konkreetne vahemik esimesel etapil andmeallikaks valida, pole nii oluline. Peaasi, et see hiljem vahemiku nimega asendada. Nii saate säästa palju RAM-i.

Nimetatud vahemikud ja nende kasutusalad

Räägime nüüd üksikasjalikumalt, kuidas nimega vahemikke õigesti luua ja kasutada neid Exceli kasutajale seatud ülesannete täitmiseks.

Vaikimisi kasutame aja säästmiseks tavalisi lahtriaadresse. See on kasulik, kui peate vahemiku üks või mitu korda kirjutama. Kui seda on vaja pidevalt kasutada või see peab olema kohanemisvõimeline, tuleks kasutada nimega vahemikke. Need muudavad valemite loomise palju lihtsamaks ja kasutajal pole nii keeruline analüüsida keerulisi valemeid, mis sisaldavad suurt hulka funktsioone. Kirjeldame mõningaid dünaamiliste vahemike loomise etappe.

Kõik algab lahtrile nime panemisest. Selleks valige see lihtsalt ja kirjutage selle nime väljale vajalik nimi. On oluline, et seda oleks lihtne meeles pidada. Nime määramisel tuleb arvestada mõningate piirangutega:

1. Maksimaalne pikkus on 255 tähemärki. See on täiesti piisav, et määrata nimi, mida teie süda soovib.
2. Nimi ei tohi sisaldada tühikuid. Seega, kui see sisaldab mitut sõna, on võimalik need eraldada alakriipsuga.

Kui hiljem selle faili teistel lehtedel peame selle väärtuse kuvama või rakendama seda edasiste arvutuste tegemiseks, siis pole vaja lülituda kõige esimesele lehele. Saate lihtsalt selle vahemiku lahtri nime üles kirjutada.

Järgmine samm on nimelise vahemiku loomine. Menetlus on põhimõtteliselt sama. Kõigepealt peate valima vahemiku ja seejärel määrama selle nime. Pärast seda saab seda nime kasutada kõigis muudes Exceli andmetoimingutes. Näiteks nimega vahemikke kasutatakse sageli väärtuste summa määratlemiseks.

Lisaks on võimalik luua nimega vahemik, kasutades vahekaarti Valemid, kasutades tööriista Määra nimi. Pärast selle valimist ilmub aken, kus peame valima oma vahemiku nime ja määrama ala, kuhu see käsitsi laieneb. Samuti saate määrata, kus see vahemik töötab: ühel lehel või kogu raamatus.

Kui nimevahemik on juba loodud, siis selle kasutamiseks on spetsiaalne teenus nimehaldur. See võimaldab mitte ainult redigeerida või lisada uusi nimesid, vaid ka neid kustutada, kui neid enam ei vajata.

Arvestada tuleb sellega, et nimeliste vahemike kasutamisel valemites, siis peale selle kustutamist valemeid automaatselt õigete väärtustega üle ei kirjutata. Seetõttu võivad esineda vead. Seetõttu peate enne nimega vahemiku kustutamist veenduma, et seda ei kasutata üheski valemis.

Teine võimalus nimega vahemiku loomiseks on hankida see tabelist. Selleks on spetsiaalne tööriist nimega “Loo valikust”. Nagu me mõistame, peate selle kasutamiseks esmalt valima vahemiku, mida me muudame, ja seejärel määrama koha, kus meil on pealkirjad. Selle tulemusena töötleb Excel nende andmete põhjal automaatselt kõik andmed ja pealkirjad määratakse automaatselt.

Kui pealkiri sisaldab mitut sõna, eraldab Excel need automaatselt alakriipsuga.

Nii mõtlesime välja, kuidas luua dünaamilisi nimega vahemikke ja kuidas need võimaldavad automatiseerida tööd suurte andmemahtudega. Nagu näha, piisab mitme funktsiooni ja funktsionaalsusesse sisseehitatud programmitööriistade kasutamisest. Üldse pole midagi keerulist, kuigi see võib algajale esmapilgul nii tunduda.