Пайвандҳо дар Excel - мутлақ, нисбӣ ва омехта. Хатогиҳо ҳангоми кор бо истинодҳои нисбӣ дар Excel

Барои осон кардани ҳисобҳо бо формулаҳо дар реҷаи автоматӣ истинод ба чашмакҳо истифода мешавад. Вобаста ба намуди навиштан онҳо ба се намуди асосӣ тақсим мешаванд:

1. Пайвандҳои нисбӣ. Барои ҳисобҳои оддӣ истифода мешавад. Нусхабардории формула тағир додани координатҳоро талаб мекунад.
2. Пайвандҳои мутлақ. Агар ба шумо лозим ояд, ки ҳисобҳои мураккабтар кунед, ин вариант мувофиқ аст. Барои ислоҳ кардани аломати "$" истифода баред. Мисол: $A$1.
3. пайвандҳои омехта. Ин навъи адресҳо дар ҳисобкуниҳо ҳангоми зарурати ҷудо кардани сутун ё сатр истифода мешавад. Мисол: $A1 ё A$1.

Дар ҳолати зарурати нусхабардории маълумоти формулаи воридшуда, истинодҳо бо суроғаи мутлақ ва омехта истифода мешаванд. Дар мақола бо мисолҳо нишон дода мешавад, ки чӣ гуна ҳисобҳо бо истифода аз намудҳои гуногуни истинодҳо анҷом дода мешаванд.

Истиноди нисбии ҳуҷайра дар Excel

Ин маҷмӯи аломатҳоест, ки ҷойгиршавии ячейкаро муайян мекунанд. Истинодҳо дар барнома ба таври худкор бо адреси нисбӣ навишта мешаванд. Масалан: A1, A2, B1, B2. Гузариш ба сатр ё сутуни дигар аломатҳои формуларо тағир медиҳад. Масалан, мавқеи ибтидоии A1. Ба таври уфуқӣ ҳаракат кардан ҳарфро ба B1, C1, D1 ва ғайра иваз мекунад. Ҳамин тавр, ҳангоми ҳаракат дар хати амудӣ тағирот ба амал меояд, танҳо дар ин ҳолат адад тағйир меёбад – A2, A3, A4 ва ғайра. Агар такрор кардан лозим бошад. ҳисобкунии як навъ дар ячейкаи ҳамсоя, ҳисоб бо истифода аз истинод нисбӣ анҷом дода мешавад. Барои истифодаи ин хусусият, чанд қадамро иҷро кунед:

1. Хамин ки маълумот ба ячейка дохил карда шуд, курсорро ба харакат дароварда, бо муш пахш кунед. Равшансозӣ бо росткунҷаи сабз аз фаъол шудани ячейка ва омодагӣ ба кори минбаъда шаҳодат медиҳад.
2. Бо пахш кардани маҷмӯи калидҳои Ctrl + C, мо мундариҷаро ба буфер нусхабардорӣ мекунем.
3. Мо чашмакеро, ки шумо мехоҳед ба он маълумот интиқол диҳед ё формулаи қаблан навишташударо фаъол мекунем.
4. Бо пахш кардани комбинатсияи Ctrl + V мо маълумоти дар буфери система захирашударо интиқол медиҳем.

Маслиҳати мутахассис! Барои анҷом додани ҳамон намуди ҳисобҳо дар ҷадвал, лайфхакро истифода баред. Чашмакеро, ки формулаи қаблан воридшуда дорад, интихоб кунед. Курсорро ба болои квадрати хурде, ки дар кунҷи рости поён пайдо мешавад, гузоред ва тугмаи чапи мушро нигоҳ доред, вобаста ба амали иҷрошуда ба сатри поён ё сутуни шадид кашед. Бо озод кардани тугмаи муш, ҳисобкунӣ ба таври худкор анҷом дода мешавад. Ин асбобро аломати пуркунии худкор меноманд.

Намунаи пайванди нисбӣ

Барои равшантар кардани он, мисоли ҳисобкуниро бо истифода аз формула бо истинодҳои нисбӣ баррасӣ кунед. Фарз мекунем, ки соҳиби як мағозаи варзишӣ пас аз як соли кор бояд фоидаи фурӯшро ҳисоб кунад.

Тартиби амалҳо:

1. Мисол нишон медиҳад, ки сутунҳои B ва C барои пур кардани миқдори моли фурӯхташуда ва нархи он истифода мешуданд. Мувофиқи он, барои навиштани формула ва гирифтани ҷавоб, сутуни D-ро интихоб кунед. Формула чунин менамояд: = B2 *C

Диққат диҳед! Барои осон кардани раванди навиштани формула, ҳиллаи каме истифода баред. Аломати «=»-ро гузоред, микдори моли фурухташударо клик кунед, аломати «*»-ро гузошта, нархи махсулотро пахш кунед. Формула пас аз аломати баробар ба таври худкор навишта мешавад.

2. Барои ҷавоби ниҳоӣ, тугмаи "Enter" -ро пахш кунед. Минбаъд шумо бояд маблағи умумии фоидаро аз дигар намудҳои маҳсулот ҳисоб кунед. Хуб, агар шумораи сатрҳо зиёд набошад, пас ҳама амалҳоро дастӣ иҷро кардан мумкин аст. Барои дар як вақт пур кардани шумораи зиёди сатрҳо дар Excel як функсияи муфид мавҷуд аст, ки имкон медиҳад формуларо ба ячейкаҳои дигар интиқол диҳед.
3. Курсорро дар кунҷи рости поёни росткунҷа бо формула ё натиҷаи анҷомёфта ҳаракат кунед. Намуди зоҳирии салиби сиёҳ ҳамчун сигналест, ки курсорро ба поён кашола кардан мумкин аст. Хамин тавр, хисоби автоматии фоидаи аз руи хар як махсулот гирифташуда алохида ба рох монда мешавад.
4. Бо озод кардани тугмаи пахшшудаи муш, мо дар ҳама сатрҳо натиҷаҳои дуруст мегирем.

Бо пахш кардани чашмаки D3, шумо мебинед, ки координатҳои ячейка ба таври худкор тағир дода шудаанд ва ҳоло чунин ба назар мерасад: =B3 *C3. Аз ин бармеояд, ки пайвандҳо нисбӣ буданд.

Хатогиҳои эҳтимолӣ ҳангоми кор бо истинодҳои нисбӣ

Бешубҳа, ин функсияи Excel ҳисобҳоро хеле содда мекунад, аммо дар баъзе мавридҳо метавонад мушкилот ба миён ояд. Мисоли оддии ҳисоб кардани коэффициенти фоидаи ҳар як моли молро дида мебароем:

1. Ҷадвал созед ва пур кунед: A – номи маҳсулот; B – миқдори фурӯхташуда; C – арзиш; D - маблағи гирифташуда. Фарз мекунем, ки дар ассортимент ҳамагӣ 11 адад мавҷуд аст. Аз ин ру, бо назардошти тавсифи сутунхо 12 сатр пур карда, маблаги умумии фоида Д.
2. Дар чашмаки E2 клик кунед ва ворид кунед =D2/D13.
3. Пас аз пахш кардани тугмаи «Ворид» коэффисиенти ҳиссаи нисбии фурӯши банди аввал пайдо мешавад.
4. Сутунро ба поён дароз кунед ва натиҷаро интизор шавед. Аммо, система хатои "#DIV/0!" медиҳад.

Сабаби хатогӣ истифодаи истинодҳои нисбӣ барои ҳисобҳо мебошад. Дар натичаи нусхабардории формула координатахо тагйир меёбанд. Яъне, барои E3, формула чунин хоҳад буд =D3/D13. Азбаски чашмаки D13 пур карда нашудааст ва аз ҷиҳати назариявӣ арзиши сифр дорад, барнома бо маълумоте хато медиҳад, ки тақсим ба сифр ғайриимкон аст.

Муҳимтарин! Барои ислохи хато формуларо тавре навиштан лозим аст, ки координатахои D13 собит шаванд. Адреси нисбӣ чунин функсия надорад. Барои ин, як намуди дигари пайвандҳо вуҷуд дорад - мутлақ. 

Чӣ тавр шумо истиноди мутлақро дар Excel эҷод мекунед

Ба шарофати истифодаи рамзи $ координатаҳои ячейкаро ислоҳ кардан имконпазир гардид. Ин чӣ гуна кор мекунад, мо минбаъд баррасӣ хоҳем кард. Азбаски барнома суроғаи нисбиро ба таври нобаёнӣ истифода мебарад, мувофиқан барои мутлақи он, шумо бояд як қатор амалҳоро иҷро кунед. Биёед ҳалли хатои «Чӣ гуна коэффицентро аз фурўши якчанд номгӯи молро пайдо кунем»-ро таҳлил карда, ҳисобкуниро бо истифодаи адресатсияи мутлақ анҷом диҳем:

1. Тугмаи E2-ро клик кунед ва координатаҳои пайвандро ворид кунед =D2/D13. Азбаски истинод нисбӣ аст, барои ислоҳи маълумот бояд рамз гузошта шавад.
2. Координатаҳои чашмаки D-ро ислоҳ кунед Барои иҷрои ин амал, бо гузоштани аломати "$" пеш аз ҳарф бо сутун ва рақами сатр.

Маслиҳати мутахассис! Барои осон кардани вазифаи воридшавӣ чашмакро барои таҳрири формула фаъол кардан ва чанд маротиба тугмаи F4-ро пахш кардан кифоя аст. То он даме, ки шумо арзишҳои қаноатбахш ба даст оред. Формулаи дуруст чунин аст: =D2/$D$13.

3. Тугмаи "Enter" -ро пахш кунед. Дар натиҷаи амалҳои иҷрошуда бояд натиҷаи дуруст пайдо шавад.
4. Барои нусхабардории формула маркерро ба сатри поён кашед.

Аз сабаби истифодаи адресатсияи мутлақ дар ҳисобҳо, натиҷаҳои ниҳоӣ дар сатрҳои боқимонда дуруст хоҳанд буд.

Чӣ тавр истиноди омехтаро дар Excel гузоштан мумкин аст

Барои ҳисобҳои формулаҳо на танҳо истинодҳои нисбӣ ва мутлақ, балки истинодҳои омехта низ истифода мешаванд. Хусусияти фарқкунандаи онҳо дар он аст, ки онҳо яке аз координатҳоро ислоҳ мекунанд.

• Масалан, барои тагйир додани мавкеи сатр, шумо бояд дар пеши нишони харф аломати $ нависед.
• Баръакс, агар аломати доллар пас аз аломати ҳарф навишта шавад, он гоҳ нишондиҳандаҳои сатр бетағйир мемонанд.

Аз ин бармеояд, ки барои халли масъалаи пештараи муайян кардани коэффициенти фуруши мол бо истифода аз адресатсияи омехта раками сатрро дуруст кардан лозим аст. Яъне аломати $ пас аз ҳарфи сутун гузошта мешавад, зеро координатҳои он ҳатто дар истинодҳои нисбӣ тағйир намеёбанд. Як мисол меорем:

1. Барои ҳисобҳои дақиқ, ворид кунед =D1/$D$3 ва "Enter" -ро пахш кунед. Барнома ҷавоби дақиқ медиҳад.
2. Барои интиқол додани формула ба чашмакҳои минбаъда дар сутун ва ба даст овардани натиҷаҳои дақиқ, дастаро ба чашмаки поён кашед.
3. Дар натиҷа, барнома ҳисобҳои дуруст медиҳад.

Диққат! Агар шумо аломати $-ро дар назди ҳарф гузошта бошед, он гоҳ Excel хатои "#DIV/0!" медиҳад, ки ин маънои онро дорад, ки ин амалиёт иҷро намешавад.

Суроғаи "SuperAbsolute"

Дар охир, биёед як мисоли дигари истиноди мутлақро дида бароем - суроғаи "SuperAbsolute". Хусусиятҳо ва фарқиятҳои он чӣ гунаанд. Рақами тахминии 30-ро гиред ва онро ба чашмаки B2 ворид кунед. Маҳз ин рақам рақами асосӣ хоҳад буд, бо он як қатор амалҳоро иҷро кардан лозим аст, масалан, онро ба қудрат баланд кунед.

1. Барои дуруст иҷро кардани ҳамаи амалҳо, формулаи зеринро дар сутуни C ворид кунед: =$B$2^$D2. Дар сутуни D мо арзиши дараҷаҳоро ворид мекунем.

2. Пас аз пахш кардани тугмаи "Enter" ва фаъол кардани формула, мо маркерро дар сутун дароз мекунем.
3. Мо натичахои дуруст мегирем.

Хулоса ин аст, ки ҳамаи амалҳои иҷрошуда ба як ячейкаи собит B2 дахл доранд, бинобар ин:

• Нусхабардории формула аз чашмаки C3 ба чашмаки E3, F3 ё H3 натиҷаро тағир намедиҳад. Он бетағйир боқӣ мемонад - 900.
• Агар ба шумо сутуни нав ворид кардан лозим бошад, координатҳои чашмак бо формула тағир меёбанд, аммо натиҷа низ бетағйир мемонад.

Хусусияти истиноди "SuperAbsolute" ин аст: агар ба шумо ҳаракат кардан лозим бошад, натиҷа тағир намеёбад. Бо вуҷуди ин, ҳолатҳое ҳастанд, ки маълумот аз манбаъҳои тарафи сеюм ворид карда мешавад. Ҳамин тариқ, сутунҳо ба паҳлӯ кӯчонида мешаванд ва маълумот дар сутуни B2 бо усули кӯҳна гузошта мешавад. Дар ин ҳолат чӣ мешавад? Ҳангоми омехта, формула мувофиқи амали иҷрошуда тағир меёбад, яъне дигар на ба B2, балки ба C2 ишора мекунад. Аммо азбаски воридкунӣ дар B2 сохта шудааст, натиҷаи ниҳоӣ нодуруст хоҳад буд.

Истинод! Барои ворид кардани макросҳо аз манбаъҳои тарафи сеюм, шумо бояд танзимоти таҳиягарро фаъол созед (онҳо бо нобаёнӣ хомӯш карда шудаанд). Барои ин, ба Параметрҳо гузаред, танзимоти лентаро кушоед ва қуттии сутуни ростро дар муқобили "Таҳиягар" қайд кунед. Пас аз он, дастрасӣ ба бисёр функсияҳое, ки қаблан аз чашми корбари оддӣ пинҳон буданд, кушода мешавад.

Дар ин ҷо савол ба миён меояд: оё метавон формуларо аз ячейкаи C2 тағир дод, то рақами аслӣ аз ячейкаи В ҷамъоварӣ шавад, сарфи назар аз ворид кардани сутунҳои нави маълумот? Барои он, ки тағирот дар ҷадвал ҳангоми насб кардани маълумот аз манбаъҳои сеюм ба муайянкунии ҷамъ таъсир нарасонад, шумо бояд қадамҳои зеринро иҷро кунед:

1. Ба ҷои координатҳои ячейкаи B2, нишондиҳандаҳои зеринро ворид кунед: =БАЙРУСТАМ (“B2”). Дар натиҷа, пас аз кӯчонидани таркиби формула чунин хоҳад буд: =БАЙРУСЫМ("B2")^$E2.
2. Ба шарофати ин функсия, пайванд ҳамеша ба мураббаъ бо координатҳои B2 ишора мекунад, новобаста аз он ки сутунҳо дар ҷадвал илова карда шудаанд ё хориҷ карда мешаванд.

Бояд фаҳмид, ки ячейкае, ки ягон маълумот надорад, ҳамеша арзиши "0" -ро нишон медиҳад.

хулоса

Бо шарофати истифодаи се намуди истинодҳои тавсифшуда, имкониятҳои зиёде пайдо мешаванд, ки кор бо ҳисобҳо дар Excel осонтар мешаванд. Аз ин рӯ, пеш аз оғози кор бо формулаҳо, аввал истинодҳо ва қоидаҳои насби онҳоро хонед.