Kaip „Excel“ nustatyti verčių diapazoną

Naudodamasis Microsoft Excel labai dažnai vartotojas iš anksto nežino, kiek informacijos bus dėl to lentelėje. Todėl ne visose situacijose suprantame, koks diapazonas turėtų būti taikomas. Juk ląstelių rinkinys yra kintama sąvoka. Norint atsikratyti šios problemos, diapazono generavimas turi būti automatinis, kad jis būtų pagrįstas tik vartotojo įvestu duomenų kiekiu.

Automatiškai keičiamas langelių diapazonas „Excel“.

Automatinių diapazonų programoje „Excel“ pranašumas yra tas, kad jie žymiai palengvina formulių naudojimą. Be to, jie leidžia žymiai supaprastinti sudėtingų duomenų, kuriuose yra daug formulių, apimančių daugybę funkcijų, analizę. Galite suteikti šiam diapazonui pavadinimą, tada jis bus automatiškai atnaujintas, atsižvelgiant į tai, kokie duomenys jame yra.

Kaip automatiškai pakeisti diapazoną „Excel“.

Tarkime, kad esate investuotojas, kuriam reikia investuoti į kokį nors objektą. Dėl to norime gauti informacijos apie tai, kiek iš viso galite uždirbti per visą laiką, kol pinigai bus naudojami šiam projektui. Nepaisant to, norėdami gauti šią informaciją, turime reguliariai stebėti, kiek bendro pelno šis objektas mums atneša. Padarykite tą pačią ataskaitą kaip ir šioje ekrano kopijoje.

Iš pirmo žvilgsnio sprendimas akivaizdus: tereikia susumuoti visą stulpelį. Jei jame atsiranda įrašų, suma bus atnaujinta savarankiškai. Tačiau šis metodas turi daug trūkumų:

1. Jei problema bus išspręsta tokiu būdu, B stulpelyje esančių langelių nebus galima naudoti kitiems tikslams.
2. Tokia lentelė sunaudos daug RAM, todėl bus neįmanoma naudoti dokumento silpnuose kompiuteriuose.

Taigi šią problemą būtina išspręsti naudojant dinaminius pavadinimus. Norėdami juos sukurti, turite atlikti šią veiksmų seką:

1. Eikite į skirtuką „Formulės“, kuris yra pagrindiniame meniu. Bus skyrius „Apibrėžti vardai“, kuriame yra mygtukas „Priskirti vardą“, kurį turime spustelėti.
2. Tada pasirodys dialogo langas, kuriame turėsite užpildyti laukus, kaip parodyta ekrano kopijoje. Svarbu pažymėti, kad turime taikyti funkciją = POSKLINIMAS kartu su funkcija TIKRINTINorėdami sukurti automatinio atnaujinimo diapazoną. 
3. Po to turime naudoti funkciją SUMA, kuris kaip argumentą naudoja mūsų dinaminį diapazoną. 

Atlikę šiuos veiksmus matome, kaip atnaujinama „pajamų“ diapazonui priklausančių langelių aprėptis, kai ten pridedame naujų elementų.

OFFSET funkcija programoje Excel

Pažvelkime į funkcijas, kurias anksčiau įrašėme lauke „diapazonas“. Naudojant funkciją NAIKINIMAS galime nustatyti diapazono dydį, atsižvelgiant į tai, kiek B stulpelio langelių yra užpildyta. Funkcijos argumentai yra tokie:

1. Pradėti langelį. Naudodamas šį argumentą vartotojas gali nurodyti, kuris diapazono langelis bus laikomas viršutiniame kairiajame kampe. Bus pranešama žemyn ir į dešinę.
2. Diapazonas poslinkis eilutėmis. Naudodami šį diapazoną nustatome langelių skaičių, kuriuo turėtų būti poslinkis nuo viršutinio kairiojo diapazono langelio. Galite naudoti ne tik teigiamas vertes, bet ir nulį bei minusą. Tokiu atveju poslinkis gali visai neįvykti arba bus atliktas priešinga kryptimi.
3. Diapazonas poslinkis stulpeliais. Šis parametras yra panašus į ankstesnį, tik jis leidžia nustatyti diapazono horizontalaus poslinkio laipsnį. Čia taip pat galite naudoti nulines ir neigiamas reikšmes.
4. Aukščio diapazono dydis. Tiesą sakant, šio argumento pavadinimas aiškiai parodo, ką jis reiškia. Tai yra langelių skaičius, kuriuo diapazonas turėtų padidėti.
5. Diapazono reikšmė pločiu. Argumentas panašus į ankstesnį, tik jis susijęs su stulpeliais.

Nereikia nurodyti paskutinių dviejų argumentų, jei to nereikia. Šiuo atveju diapazono reikšmė bus tik viena ląstelė. Pavyzdžiui, jei nurodote formulę =OFFSET(A1;0;0), ši formulė nurodys tą patį langelį kaip ir pirmajame argumente. Jei vertikalus poslinkis nustatytas į 2 vienetus, tokiu atveju langelis nurodo langelį A3. Dabar išsamiai apibūdinkime, ką reiškia funkcija TIKRINTI.

COUNT funkcija programoje „Excel“.

Naudojant funkciją TIKRINTI nustatome, kiek B stulpelio langelių iš viso užpildėme. Tai yra, naudodamiesi dviem funkcijomis nustatome, kiek langelių diapazone yra užpildyta, ir pagal gautą informaciją nustatome diapazono dydį. Todėl galutinė formulė bus tokia: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Pažiūrėkime, kaip teisingai suprasti šios formulės principą. Pirmasis argumentas nurodo, kur prasideda mūsų dinaminis diapazonas. Mūsų atveju tai yra ląstelė B2. Kiti parametrai turi nulines koordinates. Tai rodo, kad mums nereikia poslinkių, palyginti su viršutiniu kairiuoju langeliu. Viskas, ką mes užpildome, yra vertikalus diapazono dydis, kurį naudojome kaip funkciją TIKRINTI, kuris nustato langelių, kuriose yra tam tikrų duomenų, skaičių. Ketvirtasis parametras, kurį užpildėme, yra vienetas. Taigi parodome, kad bendras diapazono plotis turėtų būti vienas stulpelis.

Taigi, naudojant funkciją TIKRINTI vartotojas gali kuo efektyviau naudoti atmintį įkeldamas tik tuos langelius, kuriuose yra tam tikros reikšmės. Atitinkamai, nebus jokių papildomų klaidų, susijusių su prastu kompiuterio, kuriame veiks skaičiuoklė, našumu.

Atitinkamai, norint nustatyti diapazono dydį priklausomai nuo stulpelių skaičiaus, turite atlikti panašią veiksmų seką, tik šiuo atveju reikia nurodyti vienetą trečiajame parametre, o formulę - ketvirtame. TIKRINTI.

Matome, kad Excel formulių pagalba galima ne tik automatizuoti matematinius skaičiavimus. Tai tik lašas jūroje, tačiau iš tikrųjų jie leidžia automatizuoti beveik bet kokią operaciją, kuri ateina į galvą.

Dinaminės diagramos programoje „Excel“.

Taigi paskutiniame etape galėjome sukurti dinaminį diapazoną, kurio dydis visiškai priklauso nuo to, kiek jame yra užpildytų langelių. Dabar, remdamiesi šiais duomenimis, galite sukurti dinamines diagramas, kurios automatiškai pasikeis, kai tik vartotojas atliks pakeitimus arba pridės papildomą stulpelį ar eilutę. Veiksmų seka šiuo atveju yra tokia:

1. Mes pasirenkame savo diapazoną, po kurio įterpiame „Histogramos su grupavimu“ tipo diagramą. Šį elementą rasite skilties „Diagramos – histograma“ skiltyje „Įterpti“.
2. Kairiuoju pelės mygtuku spustelėkite atsitiktinį histogramos stulpelį, po kurio funkcijos eilutėje bus rodoma funkcija =SERIES(). Ekrano kopijoje galite pamatyti išsamią formulę. 
3. Po to reikia atlikti kai kuriuos formulės pakeitimus. Turite pakeisti diapazoną po „Sheet1!“ į diapazono pavadinimą. Dėl to bus atlikta tokia funkcija: =ROW(1 lapas!$B$1;;1 lapas!pajamos;1)
4. Dabar belieka pridėti naują įrašą prie ataskaitos, kad patikrintumėte, ar diagrama atnaujinama automatiškai, ar ne.

Pažvelkime į mūsų diagramą.

Apibendrinkime, kaip tai padarėme. Ankstesniame žingsnyje sukūrėme dinaminį diapazoną, kurio dydis priklauso nuo to, kiek elementų jame yra. Norėdami tai padaryti, naudojome funkcijų derinį TIKRINTI и NAIKINIMAS. Šiam diapazonui suteikėme pavadinimą, o tada nuorodą į šį pavadinimą panaudojome kaip savo histogramos diapazoną. Kurį konkretų diapazoną pasirinkti kaip duomenų šaltinį pirmame etape, nėra taip svarbu. Svarbiausia yra vėliau jį pakeisti diapazono pavadinimu. Tokiu būdu galite sutaupyti daug RAM.

Vardiniai diapazonai ir jų panaudojimas

Dabar pakalbėkime išsamiau apie tai, kaip teisingai sukurti pavadintus diapazonus ir naudoti juos atliekant užduotis, kurios yra nustatytos „Excel“ vartotojui.

Pagal numatytuosius nustatymus, norėdami sutaupyti laiko, naudojame įprastus ląstelių adresus. Tai naudinga, kai reikia vieną ar kelis kartus parašyti diapazoną. Jei jį reikia naudoti visą laiką arba jis turi būti prisitaikantis, tuomet reikia naudoti įvardytus diapazonus. Jie žymiai palengvina formulių kūrimą, o vartotojui nebus taip sunku analizuoti sudėtingas formules, kuriose yra daug funkcijų. Apibūdinkime kai kuriuos dinaminių diapazonų kūrimo veiksmus.

Viskas prasideda nuo ląstelės pavadinimo. Norėdami tai padaryti, tiesiog pasirinkite jį ir pavadinimo lauke įveskite mums reikalingą pavadinimą. Svarbu, kad tai būtų lengva įsiminti. Yra keletas apribojimų, į kuriuos reikia atsižvelgti skiriant pavadinimą:

1. Didžiausias ilgis yra 255 simboliai. To visiškai pakanka, kad priskirtumėte vardą, kurio trokšta jūsų širdis.
2. Pavadinime neturi būti tarpų. Todėl, jei jame yra keli žodžiai, juos galima atskirti pabraukimo ženklu.

Jei vėliau kituose šio failo lapuose turėsime rodyti šią reikšmę arba pritaikyti ją tolesniems skaičiavimams, tada nereikia pereiti prie pirmojo lapo. Galite tiesiog užsirašyti šio diapazono langelio pavadinimą.

Kitas žingsnis yra sukurti pavadintą diapazoną. Procedūra iš esmės ta pati. Pirmiausia turite pasirinkti diapazoną, tada nurodyti jo pavadinimą. Po to šis pavadinimas gali būti naudojamas visose kitose „Excel“ duomenų operacijose. Pavyzdžiui, pavadinti diapazonai dažnai naudojami reikšmių sumai apibrėžti.

Be to, naudojant skirtuką Formulės, naudojant įrankį Nustatyti pavadinimą, galima sukurti pavadintą diapazoną. Kai jį pasirinksime, pasirodys langas, kuriame turėsime pasirinkti savo diapazono pavadinimą, taip pat nurodyti sritį, į kurią jis bus išplėstas rankiniu būdu. Taip pat galite nurodyti, kur šis diapazonas veiks: viename lape ar visoje knygoje.

Jei vardų diapazonas jau buvo sukurtas, norint jį naudoti, yra speciali paslauga, vadinama vardų tvarkytuvu. Tai leidžia ne tik redaguoti ar pridėti naujų vardų, bet ir ištrinti, jei jie nebereikalingi.

Reikėtų nepamiršti, kad naudojant įvardintus diapazonus formulėse, tada jį ištrynus, formulės nebus automatiškai perrašytos tinkamomis reikšmėmis. Todėl gali atsirasti klaidų. Todėl prieš ištrindami pavadintą diapazoną turite įsitikinti, kad jis nėra naudojamas jokioje formulėje.

Kitas būdas sukurti pavadintą diapazoną yra gauti jį iš lentelės. Norėdami tai padaryti, yra specialus įrankis, vadinamas „Sukurti iš pasirinkimo“. Kaip suprantame, norėdami juo naudotis, pirmiausia turite pasirinkti diapazoną, kurį redaguosime, o tada nustatyti vietą, kurioje turime antraštes. Dėl to, remiantis šiais duomenimis, „Excel“ automatiškai apdoros visus duomenis, o pavadinimai bus priskirti automatiškai.

Jei pavadinime yra keli žodžiai, „Excel“ automatiškai juos atskirs pabraukimu.

Taigi, mes supratome, kaip sukurti dinamiškus pavadintus diapazonus ir kaip jie leidžia automatizuoti darbą su dideliais duomenų kiekiais. Kaip matote, pakanka naudoti kelias funkcijas ir į funkcionalumą integruotus programos įrankius. Visiškai nėra nieko sudėtingo, nors pradedančiajam iš pirmo žvilgsnio taip gali atrodyti.