Kako nastaviti obseg vrednosti v Excelu

Pri uporabi programa Microsoft Excel zelo pogosto uporabnik ne ve vnaprej, koliko informacij bo posledično v tabeli. Zato v vseh situacijah ne razumemo, kakšen obseg je treba pokriti. Navsezadnje je niz celic spremenljiv koncept. Da bi se znebili te težave, je potrebno ustvariti samodejno generiranje obsega, tako da bo temeljilo izključno na količini podatkov, ki jih je vnesel uporabnik.

Samodejno spreminjanje obsegov celic v Excelu

Prednost samodejnih obsegov v Excelu je, da olajšajo uporabo formul. Poleg tega omogočajo bistveno poenostavitev analize kompleksnih podatkov, ki vsebujejo veliko število formul, ki vključujejo številne funkcije. Temu obsegu lahko daste ime, nato pa se bo samodejno posodobil glede na podatke, ki jih vsebuje.

Kako narediti samodejno spremembo obsega v Excelu

Recimo, da ste investitor, ki mora investirati v nek predmet. Posledično želimo pridobiti informacije o tem, koliko lahko skupaj zaslužite za ves čas, ko bo denar deloval za ta projekt. Kljub temu pa moramo za pridobitev teh informacij redno spremljati, koliko skupnega dobička nam prinaša ta objekt. Naredite enako poročilo kot na tem posnetku zaslona.

Na prvi pogled je rešitev očitna: le sešteti morate celoten stolpec. Če se v njem pojavijo vnosi, bo znesek posodobljen neodvisno. Toda ta metoda ima veliko pomanjkljivosti:

1. Če se težava reši na ta način, celic v stolpcu B ne bo mogoče uporabiti za druge namene.
2. Takšna tabela bo porabila veliko RAM-a, kar bo onemogočilo uporabo dokumenta na šibkih računalnikih.

Zato je treba ta problem rešiti z dinamičnimi imeni. Če jih želite ustvariti, morate izvesti naslednje zaporedje dejanj:

1. Pojdite na zavihek »Formule«, ki se nahaja v glavnem meniju. Tam bo razdelek »Določena imena«, kjer je gumb »Dodeli ime«, na katerega moramo klikniti.
2. Nato se prikaže pogovorno okno, v katerem morate izpolniti polja, kot je prikazano na posnetku zaslona. Pomembno je omeniti, da moramo uporabiti funkcijo = PREMIK skupaj s funkcijo PREGLEDda ustvarite obseg s samodejnim posodabljanjem. 
3. Po tem moramo uporabiti funkcijo SUM, ki kot argument uporablja naš dinamični razpon. 

Ko končamo te korake, lahko vidimo, kako se posodablja pokritost celic, ki spadajo v obseg »dohodka«, ko tja dodamo nove elemente.

Funkcija OFFSET v Excelu

Oglejmo si funkcije, ki smo jih prej zapisali v polje »obseg«. Uporaba funkcije ODSTRANJEVANJE lahko določimo obseg obsega glede na to, koliko celic v stolpcu B je zapolnjenih. Argumenti funkcije so naslednji:

1. Začetek celice. S tem argumentom lahko uporabnik navede, katera celica v obsegu bo obravnavana kot zgornja leva. Poročalo se bo navzdol in desno.
2. Obseg zamaknjen za vrstice. Z uporabo tega obsega nastavimo število celic, za katere naj pride do odmika od zgornje leve celice obsega. Uporabite lahko ne samo pozitivne vrednosti, ampak nič in minus. V tem primeru se premik morda sploh ne zgodi ali pa bo izveden v nasprotni smeri.
3. Odmik obsega s stolpci. Ta parameter je podoben prejšnjemu, le da vam omogoča nastavitev stopnje vodoravnega premika območja. Tukaj lahko uporabite tudi ničelne in negativne vrednosti.
4. Količina razpona v višini. Pravzaprav nam naslov tega argumenta pojasni, kaj pomeni. To je število celic, za katere naj se obseg poveča.
5. Vrednost razpona v širini. Argument je podoben prejšnjemu, le da se nanaša na stolpce.

Zadnjih dveh argumentov vam ni treba navesti, če vam ni treba. V tem primeru bo vrednost obsega samo ena celica. Na primer, če podate formulo =ODOMIK(A1;0;0), se bo ta formula nanašala na isto celico kot tista v prvem argumentu. Če je navpični odmik nastavljen na 2 enoti, se bo v tem primeru celica nanašala na celico A3. Zdaj pa podrobno opišemo, kaj funkcija pomeni PREGLED.

Funkcija COUNT v Excelu

Uporaba funkcije PREGLED ugotovimo, koliko celic v stolpcu B smo skupaj izpolnili. To pomeni, da z dvema funkcijama določimo, koliko celic v obsegu je zapolnjenih, in na podlagi prejetih informacij določimo velikost obsega. Zato bo končna formula naslednja: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Oglejmo si, kako pravilno razumeti načelo te formule. Prvi argument kaže, kje se začne naš dinamični razpon. V našem primeru je to celica B2. Nadaljnji parametri imajo ničelne koordinate. To nakazuje, da ne potrebujemo odmikov glede na zgornjo levo celico. Vse, kar izpolnimo, je navpična velikost obsega, za katerega smo uporabili funkcijo PREGLED, ki določa število celic, ki vsebujejo nekatere podatke. Četrti parameter, ki smo ga izpolnili, je enota. Tako pokažemo, da mora biti skupna širina obsega en stolpec.

Tako z uporabo funkcije PREGLED uporabnik lahko čim bolj učinkovito uporablja pomnilnik tako, da naloži le tiste celice, ki vsebujejo nekaj vrednosti. V skladu s tem ne bo dodatnih napak pri delu, povezanih s slabim delovanjem računalnika, na katerem bo preglednica delovala.

V skladu s tem, da bi določili velikost obsega glede na število stolpcev, morate izvesti podobno zaporedje dejanj, le da morate v tem primeru določiti enoto v tretjem parametru in formulo v četrtem PREGLED.

Vidimo, da s pomočjo Excelovih formul ne morete samo avtomatizirati matematičnih izračunov. To je le kaplja v morje, v resnici pa vam omogočajo avtomatizacijo skoraj vseh operacij, ki vam pridejo na misel.

Dinamični grafikoni v Excelu

Tako smo v zadnjem koraku lahko ustvarili dinamični obseg, katerega velikost je popolnoma odvisna od tega, koliko zapolnjenih celic vsebuje. Zdaj lahko na podlagi teh podatkov ustvarite dinamične grafikone, ki se bodo samodejno spremenili takoj, ko bo uporabnik naredil kakršne koli spremembe ali dodal dodaten stolpec ali vrstico. Zaporedje dejanj v tem primeru je naslednje:

1. Izberemo svoj obseg, po katerem vstavimo grafikon tipa "Histogram z združevanjem". Ta element najdete v razdelku »Vstavi« v razdelku »Grafikoni – Histogram«.
2. Z levim klikom miške kliknemo na naključni stolpec histograma, po katerem se v funkcijski vrstici prikaže funkcija =SERIES(). Na posnetku zaslona si lahko ogledate podrobno formulo. 
3. Po tem je treba v formulo narediti nekaj sprememb. Zamenjati morate obseg za "Sheet1!" na ime obsega. Posledica tega bo naslednja funkcija: =ROW(List1!$B$1;;List1!dohodek;1)
4. Zdaj je treba v poročilo dodati nov zapis, da preverimo, ali se grafikon samodejno posodablja ali ne.

Oglejmo si naš diagram.

Povzemimo, kako nam je uspelo. V prejšnjem koraku smo ustvarili dinamični obseg, katerega velikost je odvisna od tega, koliko elementov vsebuje. Za to smo uporabili kombinacijo funkcij PREGLED и ODSTRANJEVANJE. Ta obseg smo poimenovali, nato pa smo sklic na to ime uporabili kot obseg našega histograma. Kateri določen obseg izbrati kot vir podatkov na prvi stopnji ni tako pomembno. Glavna stvar je, da ga pozneje zamenjate z imenom obsega. Na ta način lahko prihranite veliko RAM-a.

Imenovani obsegi in njihova uporaba

Zdaj pa se podrobneje pogovorimo o tem, kako pravilno ustvariti imenovane obsege in jih uporabiti za izvajanje nalog, ki so nastavljene za uporabnika Excela.

Privzeto uporabljamo običajne naslove celic, da prihranimo čas. To je uporabno, ko morate enkrat ali večkrat napisati obseg. Če ga je treba uporabljati ves čas ali mora biti prilagodljiv, je treba uporabiti imenovane obsege. Omogočajo veliko lažje ustvarjanje formul in uporabniku ne bo tako težko analizirati kompleksnih formul, ki vključujejo veliko število funkcij. Opišimo nekaj korakov pri ustvarjanju dinamičnih razponov.

Vse se začne s poimenovanjem celice. Če želite to narediti, ga preprosto izberite in nato v polje njegovega imena vnesite ime, ki ga potrebujemo. Pomembno je, da si je lahko zapomniti. Pri poimenovanju je treba upoštevati nekatere omejitve:

1. Največja dolžina je 255 znakov. To je povsem dovolj, da dodelite ime, ki vam ga srce poželi.
2. Ime ne sme vsebovati presledkov. Torej, če vsebuje več besed, jih je mogoče ločiti s podčrtajem.

Če moramo pozneje na drugih listih te datoteke prikazati to vrednost ali jo uporabiti za nadaljnje izračune, potem ni treba preklopiti na prvi list. Lahko samo zapišete ime te celice obsega.

Naslednji korak je ustvarjanje imenovanega obsega. Postopek je načeloma enak. Najprej morate izbrati obseg in nato določiti njegovo ime. Po tem se lahko to ime uporablja pri vseh drugih podatkovnih operacijah v Excelu. Na primer, imenovani obsegi se pogosto uporabljajo za definiranje vsote vrednosti.

Poleg tega je mogoče ustvariti imenovan obseg z uporabo zavihka Formule z orodjem Nastavi ime. Ko ga izberemo, se prikaže okno, kjer moramo izbrati ime za naš obseg in ročno določiti območje, na katerega se bo razširil. Določite lahko tudi, kje bo ta obseg deloval: na enem listu ali v celotni knjigi.

Če je obseg imen že ustvarjen, je za njegovo uporabo na voljo posebna storitev, imenovana upravitelj imen. Omogoča ne samo urejanje ali dodajanje novih imen, ampak tudi brisanje, če jih ne potrebujete več.

Upoštevati je treba, da pri uporabi imenovanih obsegov v formulah, potem ko jih izbrišete, formule ne bodo samodejno prepisane s pravilnimi vrednostmi. Zato lahko pride do napak. Zato se morate pred brisanjem imenovanega obsega prepričati, da ni uporabljen v nobeni od formul.

Drug način za ustvarjanje imenovanega obsega je, da ga pridobite iz tabele. Če želite to narediti, obstaja posebno orodje, imenovano "Ustvari iz izbora". Kot razumemo, morate za uporabo najprej izbrati obseg, ki ga bomo uredili, in nato nastaviti mesto, kjer imamo naslove. Posledično bo Excel na podlagi teh podatkov avtomatsko obdelal vse podatke in samodejno dodelil naslove.

Če naslov vsebuje več besed, jih Excel samodejno loči s podčrtajem.

Tako smo ugotovili, kako ustvariti dinamične poimenovane obsege in kako vam omogočajo avtomatizacijo dela z velikimi količinami podatkov. Kot lahko vidite, je dovolj, da uporabite več funkcij in programska orodja, vgrajena v funkcionalnost. Sploh ni nič zapletenega, čeprav se začetniku na prvi pogled morda zdi tako.