Excel формуласындағы ұяшықты қалай қатыруға болады

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

Бұл жағдайда Excel бағдарламасында ұяшық сілтемесін түзете аласыз. Және мұны бірден бірнеше жолмен жасауға болады. Осы мақсатқа қалай жетуге болатынын егжей-тегжейлі қарастырайық.

Excel сілтемесі дегеніміз не

Парақ ұяшықтардан тұрады. Олардың әрқайсысында нақты ақпарат бар. Басқа ұяшықтар оны есептеулерде пайдалана алады. Бірақ олар деректерді қайдан алу керектігін қалай түсінеді? Бұл оларға сілтеме жасауға көмектеседі.

Әрбір сілтеме бір әріп пен бір саннан тұратын ұяшықты белгілейді. Әріп бағанды, ал сан жолды білдіреді. 

Сілтемелердің үш түрі бар: абсолютті, салыстырмалы және аралас. Екіншісі әдепкі бойынша орнатылады. Абсолютті сілтеме - бағанның да, бағанның да тұрақты мекенжайы бар сілтеме. Сәйкесінше, аралас - бөлек баған немесе жол бекітілген.

1 әдісі

Баған және жол мекенжайларын сақтау үшін мына қадамдарды орындаңыз:

1. Формула бар ұяшықты басыңыз.
2. Бізге қажетті ұяшық үшін формула жолын басыңыз.
3. F4 басыңыз.

Нәтижесінде ұяшық сілтемесі абсолютті болып өзгереді. Оны сипатты доллар белгісімен тануға болады. Мысалы, егер сіз B2 ұяшығын басып, содан кейін F4 түймесін бассаңыз, сілтеме келесідей болады: $B$2.

Әр ұяшықтағы мекенжай бөлігінің алдындағы доллар белгісі нені білдіреді?

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

2 әдісі

Бұл әдіс алдыңғы әдіспен дерлік бірдей, тек F4 пернесін екі рет басу керек. мысалы, егер бізде B2 ұяшығы болса, содан кейін ол B$2 болады. Қарапайым сөзбен айтқанда, осылайша сызықты түзетіп алдық. Бұл жағдайда бағанның әрпі өзгереді.

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

3 әдісі

Бұл алдыңғы әдіспен толығымен бірдей, тек F4 пернесін үш рет басу керек. Сонда ғана бағанға сілтеме абсолютті болады, ал жол тұрақты болып қалады.

4 әдісі

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

Ұяшықтарды үлкен ауқымға бекіту

Жоғарыда көрсетілген әдістерді орындауда ешқандай қиындық тудырмайтынын көреміз. Бірақ міндеттер нақты. Мысалы, бізде бірден бірнеше ондаған формулалар болса, не істеу керек, олардың сілтемелері абсолютті түрге айналуы керек. 

Өкінішке орай, стандартты Excel әдістері бұл мақсатқа жете алмайды. Ол үшін VBA-Excel деп аталатын арнайы қосымшаны пайдалану керек. Онда Excel бағдарламасымен жалпы тапсырмаларды әлдеқайда жылдам орындауға мүмкіндік беретін көптеген қосымша мүмкіндіктер бар.

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

1. Ұяшықтар.
2. Макро
3. Әртүрлі типтегі функциялар.
4. Сілтемелер мен массивтер.

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

1. Аралықты таңдаңыз.
2. Орнатқаннан кейін пайда болатын VBA-Excel қойындысын ашыңыз. 
3. «Формулаларды құлыптау» опциясы орналасқан «Функциялар» мәзірін ашыңыз.
   

   

4. Содан кейін қажетті параметрді көрсету қажет диалогтық терезе пайда болады. Бұл қондырма баған мен бағанды ​​бөлек, бірге бекітуге, сондай-ақ пакетпен бұрыннан бар бекітуді жоюға мүмкіндік береді. Сәйкес радио түймені пайдаланып қажетті параметр таңдалғаннан кейін, сіз «OK» түймесін басу арқылы әрекеттеріңізді растауыңыз керек.

мысал

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

Біздің мысалда ол үшін = формуласын енгізу керекB2*C2. Көріп отырғаныңыздай, бұл өте қарапайым. Ұяшықтың немесе оның жеке бағанының немесе жолының мекенжайын қалай түзетуге болатынын сипаттау үшін оның мысалын пайдалану өте оңай. 

Әрине, бұл мысалда автоматты толтыру маркерін пайдаланып формуланы төмен сүйреп көруге болады, бірақ бұл жағдайда ұяшықтар автоматты түрде өзгертіледі. Сонымен, D3 ұяшығында сандар сәйкесінше 3-ке ауыстырылатын басқа формула болады. Әрі қарай – D4 схемасы бойынша формула = B4 * C4, D5 – ұқсас пішінді алады, бірақ саны 5 және т.б.

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

Доллар кірісін анықтау керек делік. Оны В7 ұяшығына салайық. Кішкене ностальгияға ие болайық және бір доллар үшін 35 рубльдің құнын көрсетейік. Тиісінше, доллардағы кірісті анықтау үшін рубльдегі соманы доллар бағамына бөлу қажет.

Міне, бұл біздің мысалда қалай көрінеді.

Егер біз алдыңғы нұсқаға ұқсас формуланы жазып көрсек, онда біз сәтсіздікке ұшыраймыз. Сол сияқты, формула сәйкесінше өзгереді. Біздің мысалда ол келесідей болады: =E3*B8. Осы жерден біз көре аламыз. формуланың бірінші бөлігі Е3-ке айналғанын және біз өзімізге бұл тапсырманы қойдық, бірақ формуланың екінші бөлігін B8-ге өзгертудің қажеті жоқ. Сондықтан біз анықтаманы абсолюттіге айналдыруымыз керек. Мұны F4 пернесін баспай, доллар белгісін қою арқылы жасауға болады.

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

Формуланың өзі келесідей болады:

=D2/$B$7

Назар! Біз екі доллар белгісін көрсеттік. Осылайша біз бағдарламаға бағанды ​​да, жолды да бекіту керек екенін көрсетеміз.

Макростардағы ұяшық сілтемелері

Макрос – әрекеттерді автоматтандыруға мүмкіндік беретін ішкі бағдарлама. Excel бағдарламасының стандартты функционалдылығынан айырмашылығы, макрос белгілі бір ұяшықты бірден орнатуға және кодтың бірнеше жолында белгілі әрекеттерді орындауға мүмкіндік береді. Ақпаратты пакеттік өңдеу үшін пайдалы, мысалы, қондырмаларды орнату мүмкіндігі болмаса (мысалы, жеке компьютер емес, компанияның компьютері пайдаланылады).

Алдымен макростың негізгі ұғымы басқа объектілерді қамтитын объектілер екенін түсіну керек. Жұмыс кітаптары объектісі электронды кітапқа (яғни құжат) жауап береді. Ол ашық құжаттың барлық парақтарының жиынтығы болып табылатын Sheets нысанын қамтиды. 

Сәйкесінше, ұяшықтар Ұяшықтар нысаны болып табылады. Ол белгілі бір парақтың барлық ұяшықтарын қамтиды.

Әрбір нысан жақшаға алынған аргументтермен сәйкестендіріледі. Ұяшықтар жағдайында оларға осы ретпен сілтеме жасалады. Алдымен жол нөмірі, содан кейін баған нөмірі немесе әріп көрсетіледі (екі пішім де қолайлы).

Мысалы, C5 ұяшығына сілтеме бар код жолы келесідей болады:

Жұмыс кітаптары («Book2.xlsm»). Парақтар («Тізім2»).Ұяшықтар(5, 3)

Жұмыс кітаптары («Book2.xlsm»). Парақтар («Тізім2»). Ұяшықтар (5, «С»)

Сондай-ақ, ұяшыққа нысан арқылы қол жеткізуге болады Таза. Тұтастай алғанда, ол ауқымға сілтеме беруге арналған (айтпақшы, оның элементтері абсолютті немесе салыстырмалы болуы мүмкін), бірақ сіз Excel құжатындағыдай пішімде ұяшық атын жай ғана бере аласыз.

Бұл жағдайда сызық келесідей болады.

Жұмыс кітаптары («Book2.xlsm»). Парақтар («Тізім2»). Ауқым («C5»)

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

Осылайша, макростарды бағдарламаларда тиімді пайдалануға болады. Шын мәнінде, ұяшықтарға немесе ауқымдарға барлық сілтемелер абсолютті болады, сондықтан олармен де бекітіледі. Рас, бұл соншалықты ыңғайлы емес. Макростарды қолдану алгоритмдегі қадамдар саны көп күрделі бағдарламаларды жазу кезінде пайдалы болуы мүмкін. Жалпы алғанда абсолютті немесе салыстырмалы сілтемелерді пайдаланудың стандартты тәсілі әлдеқайда ыңғайлы. 

Қорытындылар

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