Excel бағдарламасында мәндер ауқымын қалай орнатуға болады

Microsoft Excel бағдарламасын пайдалану кезінде пайдаланушы кестеде қанша ақпарат болатынын алдын ала білмейді. Сондықтан біз барлық жағдайларда қандай ауқымды қамту керектігін түсінбейміз. Өйткені, ұяшықтар жиыны айнымалы ұғым болып табылады. Бұл мәселеден құтылу үшін диапазонды генерациялауды тек пайдаланушы енгізген деректер көлеміне негізделген етіп автоматты түрде жасау керек.

Excel бағдарламасындағы ұяшық ауқымдарын автоматты түрде өзгерту

Excel бағдарламасындағы автоматты диапазондардың артықшылығы - олар формулаларды пайдалануды айтарлықтай жеңілдетеді. Сонымен қатар, олар көптеген функцияларды қамтитын формулалардың үлкен санын қамтитын күрделі деректерді талдауды айтарлықтай жеңілдетуге мүмкіндік береді. Бұл ауқымға атау беруге болады, содан кейін ол құрамында қандай деректерге байланысты автоматты түрде жаңартылады.

Excel бағдарламасында ауқымды автоматты түрде өзгерту әдісі

Сіз қандай да бір нысанға инвестиция салу керек инвесторсыз делік. Нәтижесінде, біз осы жоба үшін ақша жұмыс істейтін барлық уақыт ішінде жалпы қанша ақша табуға болатыны туралы ақпарат алғымыз келеді. Дегенмен, бұл ақпаратты алу үшін біз бұл нысанның бізге қанша жалпы пайда әкелетінін үнемі бақылауымыз керек. Осы скриншоттағыдай есепті жасаңыз.

Бір қарағанда, шешім анық: тек бүкіл бағанды ​​қорытындылау керек. Онда жазбалар пайда болса, сома дербес жаңартылады. Бірақ бұл әдіс көптеген кемшіліктерге ие:

1. Егер мәселе осылай шешілсе, В бағанына енгізілген ұяшықтарды басқа мақсаттарда пайдалану мүмкін болмайды.
2. Мұндай кесте жедел жадты көп тұтынады, бұл құжатты әлсіз компьютерлерде пайдалану мүмкін емес.

Демек, бұл мәселені динамикалық атаулар арқылы шешу қажет. Оларды жасау үшін келесі әрекеттер тізбегін орындау керек:

1. Негізгі мәзірде орналасқан «Формулалар» қойындысына өтіңіз. «Анықталған аттар» бөлімі болады, онда «Ат тағайындау» түймесі бар, оны басу керек.
2. Әрі қарай, скриншотта көрсетілгендей өрістерді толтыру қажет диалогтық терезе пайда болады. Функцияны қолдануымыз керек екенін ескеру маңызды = ығысу функциясымен бірге CHECKавтоматты жаңарту ауқымын жасау. 
3. Осыдан кейін біз функцияны пайдалануымыз керек SUM, ол біздің динамикалық диапазонымызды аргумент ретінде пайдаланады. 

Осы қадамдарды орындағаннан кейін біз «кіріс» диапазонына жататын ұяшықтарды қамтудың жаңа элементтерді қосу арқылы қалай жаңартылатынын көре аламыз.

Excel бағдарламасындағы OFFSET функциясы

Бұрын «ауқым» өрісінде жазған функцияларды қарастырайық. Функцияны пайдалану Диспозалы В бағанындағы қанша ұяшық толтырылғанын ескере отырып, диапазон мөлшерін анықтай аламыз. Функцияның аргументтері келесідей:

1. Ұяшықты бастау. Бұл аргумент арқылы пайдаланушы ауқымдағы қай ұяшық жоғарғы сол жақта қарастырылатынын көрсете алады. Ол төмен және оңға қарай есеп береді.
2. Ауқым жолдар бойынша ауыстырылады. Осы ауқымды пайдаланып, диапазонның жоғарғы сол жақ ұяшығынан ығысу орын алатын ұяшықтар санын орнатамыз. Сіз тек оң мәндерді ғана емес, нөл мен минусты да пайдалана аласыз. Бұл жағдайда орын ауыстыру мүлде болмауы мүмкін немесе ол қарама-қарсы бағытта жүзеге асырылады.
3. Ауқым бағандар бойынша ауыстырылады. Бұл параметр алдыңғыға ұқсас, тек диапазонның көлденең жылжу дәрежесін орнатуға мүмкіндік береді. Мұнда нөлдік және теріс мәндерді де пайдалануға болады.
4. Биіктіктегі диапазон мөлшері. Шын мәнінде, бұл дәлелдің атауы бізге оның нені білдіретінін анық көрсетеді. Бұл ауқым ұлғаюы керек ұяшықтар саны.
5. Ені бойынша ауқымның мәні. Аргумент алдыңғыға ұқсас, тек бағандарға қатысты.

Қажет болмаса, соңғы екі дәлелді көрсетудің қажеті жоқ. Бұл жағдайда ауқым мәні тек бір ұяшық болады. Мысалы, формуланы көрсетсеңіз =OFSET(A1;0;0), бұл формула бірінші аргументтегі ұяшыққа сілтеме жасайды. Егер тік ығысу 2 бірлікке орнатылса, онда бұл жағдайда ұяшық A3 ұяшығына сілтеме жасайды. Енді функцияның нені білдіретінін егжей-тегжейлі сипаттайық CHECK.

Excel бағдарламасындағы COUNT функциясы

Функцияны пайдалану CHECK В бағанында барлығы қанша ұяшықты толтырғанымызды анықтаймыз. Яғни, екі функцияны пайдалана отырып, біз диапазондағы қанша ұяшық толтырылғанын анықтаймыз және алынған ақпарат негізінде диапазон өлшемін анықтаймыз. Сондықтан соңғы формула келесідей болады: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Осы формуланың принципін қалай дұрыс түсінуге болатынын қарастырайық. Бірінші аргумент біздің динамикалық ауқымымыздың қай жерде басталатынын көрсетеді. Біздің жағдайда бұл В2 ұяшығы. Басқа параметрлердің координаттары нөлге тең. Бұл жоғарғы сол жақ ұяшыққа қатысты ығысулар қажет емес екенін көрсетеді. Біз толтырып жатқан нәрсе - бұл функцияны пайдаланған диапазонның тік өлшемі CHECK, ол кейбір деректерді қамтитын ұяшықтардың санын анықтайды. Біз толтырған төртінші параметр - бірлік. Осылайша, біз диапазонның жалпы ені бір баған болуы керек екенін көрсетеміз.

Осылайша, функцияны пайдалану CHECK пайдаланушы кейбір мәндері бар ұяшықтарды ғана жүктеу арқылы жадты барынша тиімді пайдалана алады. Тиісінше, электрондық кесте жұмыс істейтін компьютердің нашар жұмысымен байланысты жұмыста қосымша қателер болмайды.

Тиісінше, бағандар санына байланысты диапазон өлшемін анықтау үшін ұқсас әрекеттер тізбегін орындау керек, тек осы жағдайда үшінші параметрде бірлікті, ал төртіншіде формуланы көрсету керек. CHECK.

Excel формулаларының көмегімен тек математикалық есептеулерді автоматтандыруға болмайтынын көреміз. Бұл мұхиттағы тамшы ғана, бірақ іс жүзінде олар ойға келген кез келген дерлік операцияны автоматтандыруға мүмкіндік береді.

Excel бағдарламасындағы динамикалық диаграммалар

Сонымен, соңғы қадамда біз динамикалық диапазонды жасай алдық, оның өлшемі толығымен толтырылған ұяшықтардың санына байланысты. Енді осы деректер негізінде пайдаланушы кез келген өзгерістер енгізген немесе қосымша баған немесе жол қосқанда автоматты түрде өзгеретін динамикалық диаграммаларды жасай аласыз. Бұл жағдайда әрекеттер тізбегі келесідей:

1. Біз диапазонымызды таңдаймыз, содан кейін «Гистограмма топтастыру» түріндегі диаграмманы енгіземіз. Бұл элементті «Диаграммалар – гистограмма» бөліміндегі «Кірістіру» бөлімінде табуға болады.
2. Гистограмманың кездейсоқ бағанында тінтуірдің сол жақ түймешігімен шертеміз, содан кейін функция жолында =SERIES() функциясы көрсетіледі. Скриншотта сіз егжей-тегжейлі формуланы көре аласыз. 
3. Осыдан кейін формулаға кейбір өзгерістер енгізу қажет. "1-парақ!"-дан кейінгі ауқымды ауыстыру керек. диапазон атауына. Бұл келесі функцияға әкеледі: =ROW(1-парақ!$B$1;;1-парақ!табыс;1)
4. Енді диаграмма автоматты түрде жаңартылғанын немесе жаңартылмағанын тексеру үшін есепке жаңа жазба қосу қалды.

Диаграммамызға назар аударайық.

Мұны қалай жасағанымызды қорытындылайық. Алдыңғы қадамда біз динамикалық диапазонды жасадық, оның мөлшері оның қанша элементтен тұратынына байланысты. Ол үшін біз функциялардың комбинациясын қолдандық CHECK и Диспозалы. Біз бұл диапазонды атадық, содан кейін гистограмманың ауқымы ретінде осы атқа сілтеме қолдандық. Бірінші кезеңде деректер көзі ретінде қандай нақты ауқымды таңдау соншалықты маңызды емес. Ең бастысы, оны кейінірек диапазон атауымен ауыстыру. Осылайша сіз көп жедел жадты үнемдей аласыз.

Атаулы диапазондар және олардың қолданылуы

Енді аталған ауқымдарды қалай дұрыс құру және оларды Excel пайдаланушысы үшін орнатылған тапсырмаларды орындау үшін пайдалану туралы толығырақ сөйлесейік.

Әдепкі бойынша біз уақытты үнемдеу үшін кәдімгі ұяшық мекенжайларын қолданамыз. Бұл ауқымды бір немесе бірнеше рет жазу қажет болғанда пайдалы. Егер оны үнемі пайдалану қажет болса немесе бейімделу қажет болса, онда аталған ауқымдарды пайдалану керек. Олар формулаларды жасауды айтарлықтай жеңілдетеді және пайдаланушыға көптеген функцияларды қамтитын күрделі формулаларды талдау қиын болмайды. Динамикалық диапазондарды құруға қатысты кейбір қадамдарды сипаттайық.

Барлығы ұяшықты атаудан басталады. Мұны істеу үшін жай ғана оны таңдаңыз, содан кейін оның атауының өрісіне бізге қажет атауды жазыңыз. Есте сақтау оңай болуы маңызды. Атау кезінде кейбір шектеулерді ескеру қажет:

1. Максималды ұзындық – 255 таңба. Бұл сіздің жүрегіңіз қалаған атауды тағайындау үшін жеткілікті.
2. Атауда бос орындар болмауы керек. Сондықтан, егер ол бірнеше сөзден тұрса, онда астын сызу таңбасының көмегімен оларды бөлуге болады.

Егер кейінірек осы файлдың басқа парақтарында бұл мәнді көрсету немесе одан әрі есептеулерді орындау үшін қолдану қажет болса, онда ең бірінші параққа ауысудың қажеті жоқ. Сіз жай ғана осы ауқым ұяшығының атын жаза аласыз.

Келесі қадам атаулы ауқымды жасау болып табылады. Процедура негізінен бірдей. Алдымен ауқымды таңдау керек, содан кейін оның атын көрсетіңіз. Осыдан кейін бұл атау Excel бағдарламасындағы барлық басқа деректер операцияларында қолданылуы мүмкін. Мысалы, аталған ауқымдар мәндердің қосындысын анықтау үшін жиі пайдаланылады.

Сонымен қатар, «Формулалар» қойындысы арқылы «Атын орнату» құралын пайдаланып, аталған ауқымды жасауға болады. Біз оны таңдағаннан кейін, біздің ауқымымыз үшін атауды таңдау керек терезе пайда болады, сондай-ақ ол қолмен кеңейтілетін аумақты көрсету керек. Сондай-ақ, бұл ауқым қай жерде жұмыс істейтінін көрсетуге болады: бір парақта немесе бүкіл кітапта.

Егер атаулар ауқымы бұрыннан жасалған болса, оны пайдалану үшін атау менеджері деп аталатын арнайы қызмет бар. Ол жаңа атауларды өңдеуге немесе қосуға ғана емес, сонымен қатар қажет болмаса, оларды жоюға мүмкіндік береді.

Формулалардағы атаулы ауқымдарды пайдаланған кезде, оны жойғаннан кейін формулалар дұрыс мәндермен автоматты түрде қайта жазылмайтынын есте ұстаған жөн. Сондықтан қателер болуы мүмкін. Сондықтан, аталған ауқымды жоймас бұрын, оның формулалардың ешқайсысында қолданылмағанына көз жеткізу керек.

Атаулы ауқымды құрудың тағы бір жолы оны кестеден алу болып табылады. Мұны істеу үшін «Таңдаудан жасау» деп аталатын арнайы құрал бар. Түсінуімізше, оны пайдалану үшін алдымен біз өңдейтін ауқымды таңдау керек, содан кейін бізде тақырыптар бар орынды орнату керек. Нәтижесінде, осы деректер негізінде Excel барлық деректерді автоматты түрде өңдейді және тақырыптар автоматты түрде тағайындалады.

Егер тақырыпта бірнеше сөз болса, Excel оларды автоматты түрде астын сызу арқылы бөледі.

Осылайша, біз динамикалық атаулы диапазондарды қалай құру керектігін және олар деректердің үлкен көлемімен жұмысты қалай автоматтандыруға мүмкіндік беретінін анықтадық. Көріп отырғаныңыздай, бірнеше функцияларды және функционалдылыққа енгізілген бағдарлама құралдарын пайдалану жеткілікті. Бір қарағанда жаңадан бастаушыға солай көрінсе де, күрделі ештеңе жоқ.