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дин стандарттык функционалдуулугунан айырмаланып, макрос сизге дароо белгилүү бир уячаны коюуга жана коддун бир нече саптарында белгилүү иш-аракеттерди аткарууга мүмкүндүк берет. Маалыматты партия менен иштетүү үчүн пайдалуу, мисалы, кошумчаларды орнотуунун жолу жок болсо (мисалы, жеке эмес, компаниянын компьютери колдонулат).

Биринчиден, сиз макростун негизги түшүнүгү башка объекттерди камтышы мүмкүн болгон объектилер экенин түшүнүшүңүз керек. Workbooks объекти электрондук китеп (б.а. документ) үчүн жооптуу. Ал ачык документтин бардык барактарынын жыйындысы болгон Sheets объектисин камтыйт. 

Демек, клеткалар Cells объекти болуп саналат. Ал белгилүү бир барактын бардык клеткаларын камтыйт.

Ар бир объект кашаадагы аргументтер менен квалификацияланат. Клеткаларда алар ушул тартипте шилтеме кылынат. Биринчи саптын номери, андан кийин тилкенин номери же тамгасы келтирилет (эки формат тең алгылыктуу).

Мисалы, C5 уячасына шилтемени камтыган код сапы төмөнкүдөй болот:

Иш китептери("Book2.xlsm").Барактар("Тизме2").Клеткалар(5, 3)

Иш китептери("Book2.xlsm").Барактар("Тизме2").Клеткалар(5, "C")

Ошондой эле объект аркылуу клеткага кире аласыз Тыкан. Жалпысынан алганда, ал диапазонго шилтеме берүү үчүн арналган (анын элементтери, демек, абсолюттук же салыштырмалуу болушу мүмкүн), бирок сиз Excel документиндегидей форматта уячанын атын жөн гана бере аласыз.

Бул учурда, сызык ушундай болот.

Иш китептери("Book2.xlsm").Барактар("Тизме2").Арача("C5")

Бул параметр ыңгайлуураак сезилиши мүмкүн, бирок биринчи эки варианттын артыкчылыгы - сиз кашаанын ичиндеги өзгөрмөлөрдү колдонуп, абсолюттук эмес, салыштырмалуу шилтемени бере аласыз, ал натыйжаларга жараша болот. эсептөөлөр.

Ошентип, макростарды программаларда эффективдүү колдонсо болот. Чынында, бул жерде уячаларга же диапазондорго бардык шилтемелер абсолюттук болот, ошондуктан алар менен да бекитилиши мүмкүн. Ырас, бул анчалык ыңгайлуу эмес. Макросторду колдонуу алгоритмде кадамдардын көп саны бар татаал программаларды жазууда пайдалуу болушу мүмкүн. Жалпысынан алганда, абсолюттук же салыштырмалуу шилтемелерди колдонуунун стандарттуу жолу алда канча ыңгайлуу. 

Тыянактар

Биз клетка шилтемеси деген эмне экенин, ал кантип иштейт, эмне үчүн экенин түшүндүк. Биз абсолюттук жана салыштырмалуу шилтемелердин ортосундагы айырманы түшүндүк жана бир түрдү экинчи түргө айландыруу үчүн эмне кылуу керектигин түшүндүк (жөнөкөй сөз менен айтканда, даректи оңдоо же аны бошотуп алуу). Көп сандаган баалуулуктар менен кантип дароо кыла аларыңызды түшүндүк. Эми сизде бул функцияны туура кырдаалдарда колдонууга ийкемдүүлүк бар.