Saites programmā Excel — absolūtas, relatīvas un jauktas. Kļūdas, strādājot ar relatīvām saitēm programmā Excel

Lai atvieglotu aprēķinus, izmantojot formulas automātiskajā režīmā, tiek izmantotas atsauces uz šūnām. Atkarībā no rakstīšanas veida tos iedala trīs galvenajos veidos:

1. Relatīvās saites. Izmanto vienkāršiem aprēķiniem. Formulas kopēšana ir saistīta ar koordinātu maiņu.
2. Absolūtās saites. Ja nepieciešams veikt sarežģītākus aprēķinus, šī opcija ir piemērota. Fiksēšanai izmantojiet simbolu “$”. Piemērs: $1.
3. jauktas saites. Šis adresācijas veids tiek izmantots aprēķinos, kad ir nepieciešams atsevišķi fiksēt kolonnu vai rindu. Piemērs: $A1 vai A$1.

Ja nepieciešams kopēt ievadītās formulas datus, tiek izmantotas atsauces ar absolūto un jaukto adresāciju. Rakstā ar piemēriem tiks atklāts, kā tiek veikti aprēķini, izmantojot dažāda veida saites.

Relatīvā šūnu atsauce programmā Excel

Šī ir rakstzīmju kopa, kas nosaka šūnas atrašanās vietu. Saites programmā tiek automātiski rakstītas ar relatīvo adresāciju. Piemēram: A1, A2, B1, B2. Pārejot uz citu rindu vai kolonnu, tiek mainītas rakstzīmes formulā. Piemēram, sākuma pozīcija A1. Pārvietojoties horizontāli, burts tiek mainīts uz B1, C1, D1 utt. Tāpat izmaiņas notiek, pārvietojoties pa vertikālu līniju, tikai šajā gadījumā mainās cipars – A2, A3, A4 utt. Ja nepieciešams dublēt tāda paša veida aprēķins blakus šūnā, aprēķins tiek veikts, izmantojot relatīvo atsauci. Lai izmantotu šo funkciju, veiciet dažas darbības:

1. Tiklīdz dati ir ievadīti šūnā, pārvietojiet kursoru un noklikšķiniet ar peli. Izcelšana ar zaļu taisnstūri norāda uz šūnas aktivizāciju un gatavību turpmākam darbam.
2. Nospiežot taustiņu kombināciju Ctrl + C, mēs kopējam saturu starpliktuvē.
3. Mēs aktivizējam šūnu, kurā vēlaties pārsūtīt datus vai iepriekš uzrakstītu formulu.
4. Nospiežot kombināciju Ctrl + V, mēs pārsūtām sistēmas starpliktuvē saglabātos datus.

Eksperta padoms! Lai veiktu tāda paša veida aprēķinus tabulā, izmantojiet life hack. Atlasiet šūnu, kurā ir iepriekš ievadītā formula. Virziet kursoru virs neliela kvadrāta, kas parādās apakšējā labajā stūrī, un turot peles kreiso pogu, velciet uz apakšējo rindu vai galējo kolonnu atkarībā no veiktās darbības. Atlaižot peles pogu, aprēķins tiks veikts automātiski. Šo rīku sauc par automātiskās aizpildīšanas marķieri.

Relatīvās saites piemērs

Lai padarītu to skaidrāku, apsveriet aprēķina piemēru, izmantojot formulu ar relatīvu atsauci. Pieņemsim, ka sporta veikala īpašniekam pēc gada darba ir jāaprēķina peļņa no pārdošanas.

Darbību secība:

1. Piemērā redzams, ka ailes B un C tika izmantotas, lai aizpildītu pārdoto preču daudzumu un tā cenu. Attiecīgi, lai uzrakstītu formulu un saņemtu atbildi, atlasiet kolonnu D. Formula izskatās šādi: = B2*C

Pievērs uzmanību! Lai atvieglotu formulas rakstīšanas procesu, izmantojiet nelielu triku. Ieliec zīmi “=”, uzklikšķini uz pārdoto preču daudzuma, uzstādi “*” zīmi un spied uz preces cenas. Formula aiz vienādības zīmes tiks ierakstīta automātiski.

2. Lai iegūtu galīgo atbildi, nospiediet "Enter". Tālāk jums jāaprēķina kopējā peļņas summa, kas saņemta no cita veida produktiem. Nu, ja rindu skaits nav liels, tad visas manipulācijas var veikt manuāli. Lai programmā Excel vienlaikus aizpildītu lielu skaitu rindu, ir viena noderīga funkcija, kas ļauj pārsūtīt formulu uz citām šūnām.
3. Pārvietojiet kursoru virs taisnstūra apakšējā labā stūra ar formulu vai gatavo rezultātu. Melna krusta parādīšanās kalpo kā signāls, ka kursoru var vilkt uz leju. Tādējādi tiek veikts automātisks saņemtās peļņas aprēķins par katru preci atsevišķi.
4. Atlaižot nospiesto peles pogu, mēs iegūstam pareizos rezultātus visās rindās.

Noklikšķinot uz šūnas D3, jūs varat redzēt, ka šūnas koordinātas ir automātiski mainītas, un tagad tās izskatās šādi: =B3*C3. No tā izriet, ka saites bija relatīvas.

Iespējamas kļūdas, strādājot ar relatīvām saitēm

Neapšaubāmi, šī Excel funkcija ievērojami vienkāršo aprēķinus, taču dažos gadījumos var rasties grūtības. Apskatīsim vienkāršu piemēru katras preces peļņas koeficienta aprēķināšanai:

1. Izveido tabulu un aizpildi: A – preces nosaukums; B – pārdotais daudzums; C – pašizmaksa; D ir saņemtā summa. Pieņemsim, ka sortimentā ir tikai 11 preces. Tāpēc, ņemot vērā aiļu aprakstu, tiek aizpildītas 12 rindas un kopējā peļņas summa ir D
2. Noklikšķiniet uz šūnas E2 un ievadiet =D2/D13.
3. Pēc pogas “Enter” nospiešanas parādās pirmās preces pārdošanas relatīvās daļas koeficients.
4. Izstiepiet kolonnu uz leju un gaidiet rezultātu. Tomēr sistēma parāda kļūdu “#DIV/0!”

Kļūdas iemesls ir relatīvas atsauces izmantošana aprēķinos. Formulas kopēšanas rezultātā mainās koordinātas. Tas ir, E3 formula izskatīsies šādi =D3/D13. Tā kā šūna D13 nav aizpildīta un teorētiski tai ir nulles vērtība, programma sniegs kļūdu ar informāciju, ka dalīšana ar nulli nav iespējama.

Svarīgi! Lai labotu kļūdu, ir jāuzraksta formula tā, lai D13 koordinātas būtu fiksētas. Relatīvā adresācijai šādas funkcionalitātes nav. Lai to izdarītu, ir cita veida saites - absolūtas. 

Kā programmā Excel izveidot absolūtu saiti

Pateicoties simbola $ lietošanai, kļuva iespējams salabot šūnas koordinātas. Kā tas darbojas, mēs apsvērsim tālāk. Tā kā programma pēc noklusējuma izmanto relatīvo adresāciju, attiecīgi, lai to padarītu absolūtu, jums būs jāveic vairākas darbības. Analizēsim kļūdas “Kā atrast koeficientu no vairāku preču vienību pārdošanas” risinājumu, veicot aprēķinu, izmantojot absolūto adresāciju:

1. Noklikšķiniet uz E2 un ievadiet saites koordinātas =D2/D13. Tā kā saite ir relatīva, datu labošanai ir jāiestata simbols.
2. Labot šūnas D koordinātas Lai veiktu šo darbību, pirms burta, kas norāda kolonnu un rindas numuru, novietojiet zīmi “$”.

Eksperta padoms! Lai atvieglotu ievadīšanas uzdevumu, pietiek ar to, lai aktivizētu šūnu formulas rediģēšanai un vairākas reizes nospiestu taustiņu F4. Līdz iegūsi apmierinošas vērtības. Pareizā formula ir šāda: =D2/$D$13.

3. Nospiediet pogu "Enter". Veikto darbību rezultātā vajadzētu parādīties pareizajam rezultātam.
4. Velciet marķieri uz apakšējo rindiņu, lai kopētu formulu.

Tā kā aprēķinos tiek izmantota absolūtā adresācija, tad atlikušajās rindās gala rezultāti būs pareizi.

Kā programmā Excel ievietot jauktu saiti

Formulu aprēķinos tiek izmantotas ne tikai relatīvās un absolūtās atsauces, bet arī jauktās atsauces. To atšķirīgā iezīme ir tā, ka tie nosaka vienu no koordinātām.

• Piemēram, lai mainītu rindas pozīciju, burta apzīmējuma priekšā ir jāraksta zīme $.
• Gluži pretēji, ja dolāra zīme ir rakstīta pēc burta apzīmējuma, tad rindā esošie rādītāji paliks nemainīgi.

No tā izriet, ka, lai atrisinātu iepriekšējo problēmu ar preču pārdošanas koeficienta noteikšanu, izmantojot jauktu adresāciju, ir nepieciešams fiksēt rindas numuru. Tas ir, $ zīme tiek novietota aiz kolonnas burta, jo tās koordinātas nemainās pat relatīvā atsaucē. Ņemsim piemēru:

1. Lai veiktu precīzus aprēķinus, ievadiet =D1/$D$3 un nospiediet "Enter". Programma sniedz precīzu atbildi.
2. Lai pārvietotu formulu uz nākamajām šūnām kolonnā un iegūtu precīzus rezultātus, velciet turi uz apakšējo šūnu.
3. Rezultātā programma sniegs pareizos aprēķinus.

Uzmanību! Ja burta priekšā iestatīsiet $ zīmi, tad Excel parādīs kļūdu “#DIV/0!”, kas nozīmēs, ka šo darbību nevar veikt.

"SuperAbsolute" adresācija

Noslēgumā apskatīsim vēl vienu absolūtās saites piemēru – “SuperAbsolute” adresāciju. Kādas ir tās īpašības un atšķirības. Paņemiet aptuveno skaitli 30 un ievadiet to šūnā B2. Tieši šis skaitlis būs galvenais, ar to ir jāveic virkne darbību, piemēram, jāpaaugstina līdz jaudai.

1. Lai visas darbības pareizi izpildītu, C ailē ievadiet šādu formulu: =$B$2^$D2. D kolonnā ievadām grādu vērtību.

2. Pēc pogas “Enter” nospiešanas un formulas aktivizēšanas mēs stiepjam marķieri uz leju kolonnā.
3. Mēs iegūstam pareizos rezultātus.

Galvenais ir tas, ka visas veiktās darbības attiecās uz vienu fiksētu šūnu B2, tāpēc:

• Kopējot formulu no šūnas C3 uz šūnu E3, F3 vai H3, rezultāts netiks mainīts. Tas paliks nemainīgs – 900.
• Ja nepieciešams ievietot jaunu kolonnu, mainīsies šūnas koordinātas ar formulu, bet arī rezultāts paliks nemainīgs.

Šī ir saites “SuperAbsolute” īpatnība: ja nepieciešams pārvietot, rezultāts nemainīsies. Tomēr ir situācijas, kad dati tiek ievietoti no trešo pušu avotiem. Tādējādi kolonnas tiek pārvietotas uz sāniem, un dati tiek iestatīti vecajā veidā kolonnā B2. Kas notiek šajā gadījumā? Sajaucot, formula mainās atbilstoši veiktajai darbībai, tas ir, tā vairs nenorādīs uz B2, bet gan uz C2. Bet tā kā ievietošana tika veikta B2, gala rezultāts būs nepareizs.

Atsauce! Lai varētu ievietot makro no trešo pušu avotiem, ir jāiespējo izstrādātāja iestatījumi (pēc noklusējuma tie ir atspējoti). Lai to izdarītu, dodieties uz Opcijas, atveriet lentes iestatījumus un atzīmējiet izvēles rūtiņu labajā kolonnā pretī “Izstrādātājs”. Pēc tam tiks atvērta piekļuve daudzām funkcijām, kas iepriekš bija paslēptas no vidusmēra lietotāja acīm.

Tas rada jautājumu: vai ir iespējams modificēt formulu no šūnas C2, lai sākotnējais skaitlis tiktu savākts no šūnas B, neskatoties uz jaunu datu kolonnu ievietošanu? Lai nodrošinātu, ka izmaiņas tabulā neietekmē kopsummas noteikšanu, instalējot datus no trešo pušu avotiem, veiciet šādas darbības:

1. Šūnas B2 koordinātu vietā ievadiet šādus rādītājus: =NETIEŠA(“B2”). Rezultātā pēc pārvietošanas formulēšanas sastāvs izskatīsies šādi: =NETIEŠA(“B2”)^$E2.
2. Pateicoties šai funkcijai, saite vienmēr norāda uz kvadrātu ar koordinātām B2 neatkarīgi no tā, vai tabulā tiek pievienotas vai noņemtas kolonnas.

Jāsaprot, ka šūna, kurā nav datu, vienmēr parāda vērtību “0”.

Secinājumi

Pateicoties trīs aprakstīto saišu veidu izmantošanai, parādās daudz iespēju, kas atvieglo darbu ar aprēķiniem programmā Excel. Tāpēc, pirms sākat strādāt ar formulām, vispirms izlasiet saites un to uzstādīšanas noteikumus.