Dolenni yn Excel – absoliwt, cymharol a chymysg. Gwallau wrth weithio gyda chysylltiadau cymharol yn Excel

Er mwyn hwyluso cyfrifiadau gan ddefnyddio fformiwlâu yn y modd awtomatig, defnyddir cyfeiriadau at gelloedd. Yn dibynnu ar y math o ysgrifennu, fe'u rhennir yn dri phrif fath:

1. Dolenni cymharol. Defnyddir ar gyfer cyfrifiadau syml. Mae copïo fformiwla yn golygu newid y cyfesurynnau.
2. Cysylltiadau absoliwt. Os oes angen i chi gynhyrchu cyfrifiadau mwy cymhleth, mae'r opsiwn hwn yn addas. I drwsio defnyddiwch y symbol “$”. Enghraifft: $A$1.
3. cysylltiadau cymysg. Defnyddir y math hwn o gyfeiriad mewn cyfrifiadau pan fo angen gosod colofn neu linell ar wahân. Enghraifft: $A1 neu A$1.

Os oes angen copïo data'r fformiwla a gofnodwyd, defnyddir cyfeiriadau â chyfeiriadau absoliwt a chymysg. Bydd yr erthygl yn datgelu gydag enghreifftiau sut y gwneir cyfrifiadau gan ddefnyddio gwahanol fathau o ddolenni.

Cyfeirnod cell cymharol yn Excel

Dyma set o nodau sy'n diffinio lleoliad cell. Mae dolenni yn y rhaglen yn cael eu hysgrifennu'n awtomatig gyda chyfeiriadau cymharol. Er enghraifft: A1, A2, B1, B2. Mae symud i res neu golofn wahanol yn newid y nodau yn y fformiwla. Er enghraifft, man cychwyn A1. Mae symud yn llorweddol yn newid y llythyren i B1, C1, D1, ac ati. Yn yr un modd, mae newidiadau'n digwydd wrth symud ar hyd llinell fertigol, dim ond yn yr achos hwn mae'r rhif yn newid - A2, A3, A4, ac ati. Os oes angen dyblygu cyfrifiad o'r un math mewn cell gyfagos, gwneir cyfrifiad gan ddefnyddio cyfeirnod cymharol. I ddefnyddio'r nodwedd hon, dilynwch ychydig o gamau:

1. Cyn gynted ag y caiff y data ei fewnbynnu i'r gell, symudwch y cyrchwr a chliciwch gyda'r llygoden. Mae amlygu gyda phetryal gwyrdd yn dynodi gweithrediad y gell a pharodrwydd ar gyfer gwaith pellach.
2. Trwy wasgu'r cyfuniad allweddol Ctrl + C, rydyn ni'n copïo'r cynnwys i'r clipfwrdd.
3. Rydym yn actifadu'r gell yr ydych am drosglwyddo data iddi neu fformiwla a ysgrifennwyd yn flaenorol.
4. Trwy wasgu'r cyfuniad Ctrl + V rydym yn trosglwyddo'r data a arbedwyd yn y clipfwrdd system.

Cyngor arbenigol! I wneud yr un math o gyfrifiadau yn y tabl, defnyddiwch hac bywyd. Dewiswch y gell sy'n cynnwys y fformiwla a gofnodwyd yn flaenorol. Gan hofran y cyrchwr dros sgwâr bach sy'n ymddangos yn y gornel dde isaf, a dal botwm chwith y llygoden, llusgwch i'r rhes waelod neu'r golofn eithafol, yn dibynnu ar y weithred a gyflawnir. Trwy ryddhau botwm y llygoden, bydd y cyfrifiad yn cael ei wneud yn awtomatig. Gelwir yr offeryn hwn yn farciwr llenwi ceir.

Enghraifft o ddolen gymharol

Er mwyn ei gwneud yn gliriach, ystyriwch enghraifft o gyfrifiad gan ddefnyddio fformiwla â chyfeirnod perthynol. Tybiwch fod angen i berchennog siop chwaraeon ar ôl blwyddyn o waith gyfrifo'r elw o werthiannau.

Trefn y camau gweithredu:

1. Mae'r enghraifft yn dangos bod colofnau B ac C wedi'u defnyddio i lenwi nifer y nwyddau a werthwyd a'u pris. Yn unol â hynny, i ysgrifennu'r fformiwla a chael yr ateb, dewiswch golofn D. Mae'r fformiwla yn edrych fel hyn: = B2 *C

Talu sylw! I hwyluso'r broses o ysgrifennu fformiwla, defnyddiwch ychydig o dric. Rhowch yr arwydd “=”, cliciwch ar faint o nwyddau a werthwyd, gosodwch yr arwydd “*” a chliciwch ar bris y cynnyrch. Bydd y fformiwla ar ôl yr arwydd cyfartal yn cael ei ysgrifennu'n awtomatig.

2. Ar gyfer yr ateb terfynol, pwyswch “Enter”. Nesaf, mae angen i chi gyfrifo cyfanswm yr elw a dderbyniwyd o fathau eraill o gynhyrchion. Wel, os nad yw nifer y llinellau yn fawr, yna gellir cyflawni pob triniaeth â llaw. I lenwi nifer fawr o resi ar yr un pryd yn Excel, mae un swyddogaeth ddefnyddiol sy'n ei gwneud hi'n bosibl trosglwyddo'r fformiwla i gelloedd eraill.
3. Symudwch y cyrchwr dros gornel dde isaf y petryal gyda'r fformiwla neu'r canlyniad gorffenedig. Mae ymddangosiad croes ddu yn arwydd y gellir llusgo'r cyrchwr i lawr. Felly, gwneir cyfrifiad awtomatig o'r elw a dderbyniwyd ar gyfer pob cynnyrch ar wahân.
4. Trwy ryddhau botwm pwyso'r llygoden, rydyn ni'n cael y canlyniadau cywir ym mhob llinell.

Trwy glicio ar gell D3, gallwch weld bod y cyfesurynnau cell wedi'u newid yn awtomatig, ac yn awr yn edrych fel hyn: =B3 *C3. Mae'n dilyn bod y cysylltiadau yn gymharol.

Gwallau posibl wrth weithio gyda chysylltiadau cymharol

Yn ddi-os, mae'r swyddogaeth Excel hon yn symleiddio'r cyfrifiadau yn fawr, ond mewn rhai achosion gall anawsterau godi. Gadewch i ni ystyried enghraifft syml o gyfrifo cyfernod elw pob eitem o nwyddau:

1. Creu tabl a llenwi: A – enw'r cynnyrch; B – nifer a werthwyd; C – cost; D yw'r swm a dderbyniwyd. Tybiwch mai dim ond 11 eitem sydd yn yr amrywiaeth. Felly, gan ystyried y disgrifiad o'r colofnau, mae 12 llinell wedi'u llenwi a chyfanswm yr elw yw D
2. Cliciwch ar gell E2 a mynd i mewn =D2/D13.
3. Ar ôl pwyso'r botwm "Enter", mae cyfernod cyfran gymharol gwerthiannau'r eitem gyntaf yn ymddangos.
4. Estynnwch y golofn i lawr ac aros am y canlyniad. Fodd bynnag, mae'r system yn rhoi'r gwall “#DIV/0!”

Y rheswm am y gwall yw'r defnydd o gyfeirnod cymharol ar gyfer cyfrifiadau. O ganlyniad i gopïo'r fformiwla, mae'r cyfesurynnau'n newid. Hynny yw, ar gyfer E3, bydd y fformiwla yn edrych fel hyn =D3/D13. Oherwydd nad yw cell D13 wedi'i llenwi a bod ganddi werth sero yn ddamcaniaethol, bydd y rhaglen yn rhoi gwall gyda'r wybodaeth bod rhannu â sero yn amhosibl.

Pwysig! I gywiro'r gwall, mae angen ysgrifennu'r fformiwla yn y fath fodd fel bod cyfesurynnau D13 yn sefydlog. Nid oes gan gyfarchion cymharol unrhyw swyddogaeth o'r fath. I wneud hyn, mae math arall o ddolenni - absoliwt. 

Sut ydych chi'n gwneud dolen absoliwt yn Excel

Diolch i'r defnydd o'r symbol $, daeth yn bosibl trwsio'r cyfesurynnau cell. Sut mae hyn yn gweithio, byddwn yn ystyried ymhellach. Gan fod y rhaglen yn defnyddio cyfeiriadau cymharol yn ddiofyn, yn unol â hynny, i'w wneud yn absoliwt, bydd angen i chi gyflawni nifer o gamau gweithredu. Gadewch i ni ddadansoddi'r ateb i'r gwall "Sut i ddod o hyd i'r cyfernod o werthu sawl eitem o nwyddau", gan berfformio'r cyfrifiad gan ddefnyddio cyfeiriad absoliwt:

1. Cliciwch ar E2 a nodwch gyfesurynnau'r ddolen =D2/D13. Gan fod y ddolen yn gymharol, rhaid gosod symbol i drwsio'r data.
2. Trwsiwch gyfesurynnau cell D I gyflawni'r weithred hon, rhowch arwydd “$” o flaen y llythyren sy'n nodi'r golofn a rhif y rhes.

Cyngor arbenigol! Er mwyn hwyluso'r dasg o fynd i mewn, mae'n ddigon i actifadu'r gell ar gyfer golygu'r fformiwla a phwyso'r allwedd F4 sawl gwaith. Hyd nes y cewch werthoedd boddhaol. Mae'r fformiwla gywir fel a ganlyn: =D2/$D$13.

3. Pwyswch y botwm "Enter". O ganlyniad i'r gweithredoedd a gyflawnir, dylai'r canlyniad cywir ymddangos.
4. Llusgwch y marciwr i'r llinell waelod i gopïo'r fformiwla.

Oherwydd y defnydd o gyfeiriadau absoliwt yn y cyfrifiadau, bydd y canlyniadau terfynol yn y rhesi sy'n weddill yn gywir.

Sut i roi dolen gymysg yn Excel

Ar gyfer cyfrifiadau fformiwla, defnyddir nid yn unig gyfeiriadau cymharol ac absoliwt, ond hefyd rhai cymysg. Eu nodwedd nodedig yw eu bod yn trwsio un o'r cyfesurynnau.

• Er enghraifft, i newid safle llinell, rhaid i chi ysgrifennu'r arwydd $ o flaen dynodiad y llythyren.
• I'r gwrthwyneb, os yw'r arwydd ddoler yn cael ei ysgrifennu ar ôl dynodiad y llythyr, yna bydd y dangosyddion yn y llinell yn aros yn ddigyfnewid.

O hyn mae'n dilyn, er mwyn datrys y broblem flaenorol o ran pennu cyfernod gwerthu nwyddau gan ddefnyddio cyfeiriadau cymysg, bod angen gosod rhif y llinell. Hynny yw, gosodir yr arwydd $ ar ôl llythyren y golofn, oherwydd nid yw ei gyfesurynnau'n newid hyd yn oed mewn cyfeiriad cymharol. Gadewch i ni gymryd enghraifft:

1. Ar gyfer cyfrifiadau cywir, nodwch =D1/$D$3 a phwyswch “Enter”. Mae'r rhaglen yn rhoi'r union ateb.
2. I symud y fformiwla i gelloedd dilynol i lawr y golofn a chael canlyniadau cywir, llusgwch yr handlen i'r gell waelod.
3. O ganlyniad, bydd y rhaglen yn rhoi'r cyfrifiadau cywir.

Sylw! Os ydych chi'n gosod yr arwydd $ o flaen y llythyr, yna bydd Excel yn rhoi gwall “#DIV/0!”, a fydd yn golygu na ellir cyflawni'r llawdriniaeth hon.

Cyfeiriad “SuperAbsolute”.

Yn y diwedd, gadewch i ni edrych ar enghraifft arall o ddolen absoliwt – cyfeiriad “SuperAbsolute”. Beth yw ei nodweddion a'i wahaniaethau. Cymerwch y rhif bras 30 a rhowch ef yng nghell B2. Y rhif hwn fydd y prif un, mae angen cyflawni cyfres o weithredoedd ag ef, er enghraifft, i'w godi i bŵer.

1. Er mwyn cyflawni'r holl gamau gweithredu yn gywir, rhowch y fformiwla ganlynol yng ngholofn C: =$B$2^$D2. Yng ngholofn D rydyn ni'n nodi gwerth y graddau.

2. Ar ôl pwyso'r botwm "Enter" ac actifadu'r fformiwla, rydyn ni'n ymestyn y marciwr i lawr y golofn.
3. Rydym yn cael y canlyniadau cywir.

Y gwir amdani yw bod yr holl gamau a gyflawnwyd yn cyfeirio at un gell sefydlog B2, felly:

• Ni fydd copïo fformiwla o gell C3 i gell E3, F3, neu H3 yn newid y canlyniad. Bydd yn aros heb ei newid - 900.
• Os oes angen i chi fewnosod colofn newydd, bydd cyfesurynnau'r gell gyda'r fformiwla yn newid, ond bydd y canlyniad hefyd yn aros yr un fath.

Dyma hynodrwydd y ddolen “SuperAbsolute”: os oes angen i chi symud ni fydd y canlyniad yn newid. Fodd bynnag, mae sefyllfaoedd pan fydd data'n cael ei fewnosod o ffynonellau trydydd parti. Felly, mae'r colofnau'n cael eu symud i'r ochr, ac mae'r data wedi'i osod yn yr hen ffordd yng ngholofn B2. Beth sy'n digwydd yn yr achos hwn? Pan gaiff ei gymysgu, mae'r fformiwla'n newid yn ôl y weithred a gyflawnir, hynny yw, ni fydd bellach yn pwyntio at B2, ond at C2. Ond ers i'r mewnosodiad gael ei wneud yn B2, bydd y canlyniad terfynol yn anghywir.

Cyfeirnod! Er mwyn gallu mewnosod macros o ffynonellau trydydd parti, mae angen i chi alluogi gosodiadau datblygwr (maen nhw'n anabl yn ddiofyn). I wneud hyn, ewch i Opsiynau, agorwch y gosodiadau rhuban a thiciwch y blwch yn y golofn dde gyferbyn â “Datblygwr”. Ar ôl hynny, bydd mynediad i lawer o swyddogaethau a oedd wedi'u cuddio o'r blaen o lygaid y defnyddiwr cyffredin yn agor.

Mae hyn yn codi'r cwestiwn: a yw'n bosibl addasu'r fformiwla o gell C2 fel bod y rhif gwreiddiol yn cael ei gasglu o gell B, er gwaethaf mewnosod colofnau data newydd? Er mwyn sicrhau nad yw newidiadau yn y tabl yn effeithio ar benderfyniad y cyfanswm wrth osod data o ffynonellau trydydd parti, rhaid i chi gymryd y camau canlynol:

1. Yn lle cyfesurynnau cell B2, nodwch y dangosyddion canlynol: =INDIRECT("B2"). O ganlyniad, ar ôl symud y cyfansoddiad ffurfio bydd yn edrych fel hyn: =INDIRECT("B2")^$E2.
2. Diolch i'r swyddogaeth hon, mae'r ddolen bob amser yn pwyntio at y sgwâr gyda chyfesurynnau B2, ni waeth a yw colofnau'n cael eu hychwanegu neu eu tynnu yn y tabl.

Rhaid deall bod cell nad yw'n cynnwys unrhyw ddata bob amser yn dangos y gwerth “0”.

Casgliad

Diolch i'r defnydd o dri math o'r dolenni a ddisgrifir, mae llawer o gyfleoedd yn ymddangos sy'n ei gwneud hi'n haws gweithio gyda chyfrifiadau yn Excel. Felly, cyn i chi ddechrau gweithio gyda fformiwlâu, yn gyntaf darllenwch y dolenni a'r rheolau ar gyfer eu gosod.