Kā programmā Excel iestatīt vērtību diapazonu

Lietojot Microsoft Excel, ļoti bieži lietotājs iepriekš nezina, cik daudz informācijas rezultātā būs tabulā. Tāpēc mēs ne visās situācijās saprotam, kāds diapazons ir jāaptver. Galu galā šūnu kopums ir mainīgs jēdziens. Lai atbrīvotos no šīs problēmas, diapazona ģenerēšana ir jāpadara automātiska, lai tā būtu balstīta tikai uz lietotāja ievadīto datu apjomu.

Automātiski mainot šūnu diapazonus programmā Excel

Programmas Excel automātisko diapazonu priekšrocība ir tā, ka tie ievērojami atvieglo formulu lietošanu. Turklāt tie ļauj ievērojami vienkāršot sarežģītu datu analīzi, kas satur lielu skaitu formulu, kas ietver daudzas funkcijas. Šim diapazonam varat piešķirt nosaukumu, un pēc tam tas tiks automātiski atjaunināts atkarībā no tajā ietvertajiem datiem.

Kā programmā Excel automātiski mainīt diapazonu

Pieņemsim, ka esat investors, kuram ir jāiegulda kādā objektā. Rezultātā mēs vēlamies iegūt informāciju par to, cik daudz jūs varat nopelnīt kopumā par visu laiku, ko nauda strādās šim projektam. Tomēr, lai iegūtu šo informāciju, mums regulāri jāseko līdzi, cik lielu kopējo peļņu šis objekts mums nes. Izveidojiet tādu pašu pārskatu kā šajā ekrānuzņēmumā.

No pirmā acu uzmetiena risinājums ir acīmredzams: jums vienkārši jāapkopo visa kolonna. Ja tajā parādās ieraksti, summa tiks atjaunināta neatkarīgi. Bet šai metodei ir daudz trūkumu:

1. Ja problēma tiks atrisināta šādā veidā, B ailē iekļautās šūnas nebūs iespējams izmantot citiem mērķiem.
2. Šāda tabula patērēs daudz RAM, kas padarīs neiespējamu dokumenta izmantošanu vājos datoros.

Tāpēc šī problēma ir jāatrisina, izmantojot dinamiskos nosaukumus. Lai tos izveidotu, jums jāveic šāda darbību secība:

1. Dodieties uz cilni “Formulas”, kas atrodas galvenajā izvēlnē. Būs sadaļa “Definētie vārdi”, kur ir poga “Piešķirt vārdu”, uz kuras mums jānoklikšķina.
2. Pēc tam parādīsies dialoglodziņš, kurā jums jāaizpilda lauki, kā parādīts ekrānuzņēmumā. Ir svarīgi atzīmēt, ka mums ir jāpiemēro funkcija = PĀRVIETOJUMS kopā ar funkciju CHECKlai izveidotu automātiskās atjaunināšanas diapazonu. 
3. Pēc tam mums ir jāizmanto funkcija SUM, kas kā argumentu izmanto mūsu dinamisko diapazonu. 

Pēc šo darbību veikšanas mēs varam redzēt, kā tiek atjaunināts “ienākumu” diapazonam piederošo šūnu pārklājums, kad mēs tur pievienojam jaunus elementus.

OFFSET funkcija programmā Excel

Apskatīsim funkcijas, kuras iepriekš ierakstījām laukā “diapazons”. Izmantojot funkciju APGLABĀŠANA mēs varam noteikt diapazona apjomu, ņemot vērā to, cik šūnu ailē B ir aizpildītas. Funkcijas argumenti ir šādi:

1. Sākt šūnu. Izmantojot šo argumentu, lietotājs var norādīt, kura diapazona šūna tiks uzskatīta par augšējo kreiso pusi. Tas ziņos uz leju un pa labi.
2. Diapazona nobīde ar rindām. Izmantojot šo diapazonu, mēs iestatām šūnu skaitu, par kādu jānotiek nobīdei no diapazona augšējās kreisās šūnas. Varat izmantot ne tikai pozitīvas vērtības, bet arī nulli un mīnusu. Šajā gadījumā pārvietošana var nenotikt vispār vai arī tā tiks veikta pretējā virzienā.
3. Diapazons nobīdīts ar kolonnām. Šis parametrs ir līdzīgs iepriekšējam, tikai tas ļauj iestatīt diapazona horizontālās nobīdes pakāpi. Šeit varat arī izmantot gan nulles, gan negatīvas vērtības.
4. Augstuma diapazona apjoms. Faktiski šī argumenta nosaukums skaidri parāda, ko tas nozīmē. Šis ir šūnu skaits, par kuru diapazonam vajadzētu palielināties.
5. Diapazona vērtība platumā. Arguments ir līdzīgs iepriekšējam, tikai tas attiecas uz kolonnām.

Pēdējie divi argumenti nav jānorāda, ja tas nav nepieciešams. Šajā gadījumā diapazona vērtība būs tikai viena šūna. Piemēram, ja norādāt formulu =OFFSET(A1;0;0), šī formula attieksies uz to pašu šūnu, kas norādīta pirmajā argumentā. Ja vertikālā nobīde ir iestatīta uz 2 vienībām, tad šajā gadījumā šūna atsauksies uz šūnu A3. Tagad sīki aprakstīsim, ko nozīmē funkcija CHECK.

COUNT funkcija programmā Excel

Izmantojot funkciju CHECK mēs nosakām, cik šūnu ailē B kopā esam aizpildījuši. Tas ir, izmantojot divas funkcijas, mēs nosakām, cik šūnu diapazonā ir aizpildītas, un, pamatojoties uz saņemto informāciju, nosaka diapazona lielumu. Tāpēc galīgā formula būs šāda: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Apskatīsim, kā pareizi saprast šīs formulas principu. Pirmais arguments norāda uz to, kur sākas mūsu dinamiskais diapazons. Mūsu gadījumā tā ir šūna B2. Citiem parametriem ir nulles koordinātas. Tas liek domāt, ka mums nav vajadzīgas nobīdes attiecībā pret augšējo kreiso šūnu. Viss, ko mēs aizpildām, ir diapazona vertikālais izmērs, kuru izmantojām kā funkciju CHECK, kas nosaka to šūnu skaitu, kurās ir daži dati. Ceturtais parametrs, ko mēs aizpildījām, ir vienība. Tādējādi mēs parādām, ka diapazona kopējam platumam jābūt vienai kolonnai.

Tādējādi, izmantojot funkciju CHECK lietotājs var izmantot atmiņu pēc iespējas efektīvāk, ielādējot tikai tās šūnas, kurās ir dažas vērtības. Attiecīgi darbā nebūs papildu kļūdu, kas saistītas ar datora, kurā darbosies izklājlapa, slikto veiktspēju.

Attiecīgi, lai noteiktu diapazona lielumu atkarībā no kolonnu skaita, jums jāveic līdzīga darbību secība, tikai šajā gadījumā trešajā parametrā ir jānorāda vienība, bet ceturtajā - formula. CHECK.

Mēs redzam, ka ar Excel formulu palīdzību jūs varat ne tikai automatizēt matemātiskos aprēķinus. Tas ir tikai piliens jūrā, taču patiesībā tie ļauj automatizēt gandrīz jebkuru darbību, kas ienāk prātā.

Dinamiskās diagrammas programmā Excel

Tātad pēdējā solī mēs varējām izveidot dinamisko diapazonu, kura lielums pilnībā atkarīgs no tā, cik aizpildīto šūnu tas satur. Tagad, pamatojoties uz šiem datiem, varat izveidot dinamiskas diagrammas, kas automātiski mainīsies, tiklīdz lietotājs veiks izmaiņas vai pievienos papildu kolonnu vai rindu. Darbību secība šajā gadījumā ir šāda:

1. Mēs izvēlamies savu diapazonu, pēc kura ievietojam diagrammu ar tipu “Histogramma ar grupēšanu”. Šo vienumu varat atrast sadaļā "Ievietot" sadaļā "Diagrammas – histogramma".
2. Veicam peles kreiso klikšķi uz nejaušas histogrammas kolonnas, pēc kuras funkcijas rindā tiks parādīta funkcija =SERIES(). Ekrānuzņēmumā varat redzēt detalizētu formulu. 
3. Pēc tam formulā ir jāveic dažas izmaiņas. Diapazons jāaizstāj pēc “Sheet1!” uz diapazona nosaukumu. Tā rezultātā tiks parādīta šāda funkcija: =RINDA(1. lapa!$B$1;;1. lapa!ienākumi;1)
4. Tagad atliek ziņojumam pievienot jaunu ierakstu, lai pārbaudītu, vai diagramma tiek atjaunināta automātiski vai nē.

Apskatīsim mūsu diagrammu.

Apkoposim, kā mums tas izdevās. Iepriekšējā darbībā mēs izveidojām dinamisko diapazonu, kura lielums ir atkarīgs no tā, cik daudz elementu tajā ir. Lai to izdarītu, mēs izmantojām funkciju kombināciju CHECK и APGLABĀŠANA. Mēs nosaucām šo diapazonu, un pēc tam izmantojām atsauci uz šo nosaukumu kā mūsu histogrammas diapazonu. Kuru konkrētu diapazonu izvēlēties kā datu avotu pirmajā posmā, nav tik svarīgi. Galvenais ir vēlāk to aizstāt ar diapazona nosaukumu. Tādā veidā jūs varat ietaupīt daudz RAM.

Nosauktie diapazoni un to izmantošana

Tagad parunāsim sīkāk par to, kā pareizi izveidot nosauktos diapazonus un izmantot tos, lai veiktu Excel lietotājam iestatītos uzdevumus.

Pēc noklusējuma mēs izmantojam parastās šūnu adreses, lai ietaupītu laiku. Tas ir noderīgi, ja jums ir nepieciešams ierakstīt diapazonu vienu vai vairākas reizes. Ja tas ir jāizmanto visu laiku vai tam ir jābūt adaptīvam, tad jāizmanto nosauktie diapazoni. Tie ievērojami atvieglo formulu izveidi, un lietotājam nebūs tik grūti analizēt sarežģītas formulas, kas ietver lielu skaitu funkciju. Aprakstīsim dažas darbības, kas saistītas ar dinamisko diapazonu izveidi.

Viss sākas ar šūnas nosaukšanu. Lai to izdarītu, vienkārši atlasiet to un pēc tam tā nosaukuma laukā ierakstiet mums vajadzīgo nosaukumu. Ir svarīgi, lai to būtu viegli atcerēties. Ir daži ierobežojumi, kas jāņem vērā, piešķirot nosaukumu:

1. Maksimālais garums ir 255 rakstzīmes. Tas ir pilnīgi pietiekami, lai piešķirtu vārdu, kuru vēlas jūsu sirds.
2. Nosaukumā nedrīkst būt atstarpes. Tāpēc, ja tajā ir vairāki vārdi, tad tos var atdalīt, izmantojot pasvītrojuma zīmi.

Ja vēlāk citās šī faila lapās šī vērtība ir jāparāda vai jāpiemēro, lai veiktu turpmākus aprēķinus, tad nav nepieciešams pārslēgties uz pašu pirmo lapu. Varat vienkārši pierakstīt šīs diapazona šūnas nosaukumu.

Nākamais solis ir izveidot nosauktu diapazonu. Procedūra būtībā ir tāda pati. Vispirms ir jāizvēlas diapazons un pēc tam jānorāda tā nosaukums. Pēc tam šo nosaukumu var izmantot visās pārējās datu operācijās programmā Excel. Piemēram, nosauktie diapazoni bieži tiek izmantoti, lai definētu vērtību summu.

Turklāt ir iespējams izveidot nosauktu diapazonu, izmantojot cilni Formulas, izmantojot rīku Set Name. Pēc tā atlasīšanas parādīsies logs, kurā mums jāizvēlas sava diapazona nosaukums, kā arī jānorāda apgabals, uz kuru tas tiks manuāli paplašināts. Varat arī norādīt, kur šis diapazons darbosies: vienā lapā vai visā grāmatā.

Ja nosaukumu diapazons jau ir izveidots, tad, lai to izmantotu, ir īpašs pakalpojums, ko sauc par nosaukumu pārvaldnieku. Tas ļauj ne tikai rediģēt vai pievienot jaunus nosaukumus, bet arī dzēst tos, ja tie vairs nav vajadzīgi.

Jāpatur prātā, ka, formulās izmantojot nosauktos diapazonus, pēc to dzēšanas formulas netiks automātiski pārrakstītas ar pareizajām vērtībām. Tāpēc var rasties kļūdas. Tāpēc pirms nosauktā diapazona dzēšanas ir jāpārliecinās, ka tas netiek izmantots nevienā no formulām.

Vēl viens veids, kā izveidot nosauktu diapazonu, ir iegūt to no tabulas. Lai to izdarītu, ir īpašs rīks ar nosaukumu “Izveidot no atlases”. Kā mēs saprotam, lai to izmantotu, vispirms ir jāizvēlas diapazons, kuru mēs rediģēsim, un pēc tam jāiestata vieta, kurā mums ir virsraksti. Rezultātā, pamatojoties uz šiem datiem, programma Excel automātiski apstrādās visus datus un automātiski tiks piešķirti nosaukumi.

Ja virsrakstā ir vairāki vārdi, programma Excel tos automātiski atdalīs ar pasvītrojumu.

Tādējādi mēs izdomājām, kā izveidot dinamiskus nosaukumu diapazonus un kā tie ļauj automatizēt darbu ar lielu datu apjomu. Kā redzat, pietiek izmantot vairākas funkcijas un funkcionalitātē iebūvētos programmas rīkus. Nav nekā sarežģīta, lai gan iesācējam no pirmā acu uzmetiena tā var šķist.