Lai programmā Excel veiktu precīzus aprēķinus, ir svarīgi saprast, kā darbojas dažādu veidu šūnu atsauces.
A1 pret R1C1 Atsauces
Excel darblapās ir daudz šūnu, un (pēc noklusējuma) katra šūna tiek identificēta pēc tās kolonnas burts kam seko rindas numurs. To sauc par A1 stila atsaucēm. Piemēri: A1, B4, C6
A1 atsauces stils
Pēc izvēles varat pārslēgties uz R1C1 atsauces režīms atsaukties uz šūnas rindu & kolonnas numurs. Tā vietā, lai atsauktos uz šūnu A1, jūs atsauktos uz R1C1 (1. rinda, 1. sleja). Šūna C4 tiks apzīmēta ar R4C3.
R1C1 atsauces stils
R1C1 stila atsauces programmā Excel ir ārkārtīgi retas. Ja vien jums nav pamatota iemesla, jums, iespējams, vajadzētu pieturēties pie noklusējuma A1 stila atsauces režīma. Tomēr, ja izmantojat VBA, jūs, iespējams, sastapsities ar šo atsauces stilu.
Pārslēdzieties uz R1C1 atsauces stilu
Lai mainītu atsauces stilu, dodieties uz Fails> Opcija> Formula. Atzīmējiet izvēles rūtiņu blakus R1C1 atsauces stils.
Nosauktie diapazoni
Viena no Excel nepietiekami izmantotajām funkcijām ir funkcija Nosauktie diapazoni. Tā vietā, lai atsauktos uz šūnu (vai šūnu grupu) pēc tās atrašanās vietas (piemēram, B3 vai R3C2), varat nosaukt šo diapazonu un formulās vienkārši atsaukties uz diapazona nosaukumu.
Lai nosauktu diapazonu:
- Atlasiet šūnu vai šūnas, kurām vēlaties piešķirt nosaukumu
- Noklikšķiniet lodziņa diapazona nosaukuma iekšpusē
- Ievadiet vēlamo vārdu
- Nospiediet Enter
Tagad varat atsaukties uz šūnu A1, tā vietā ierakstot = range_name1. Tas ir ļoti noderīgi, strādājot ar lielām darbgrāmatām ar vairākām darblapām.
Šūnu diapazons
Izmantojot Excel iebūvētās funkcijas, jums var būt nepieciešams atsaukties uz šūnu diapazoniem. Šūnu diapazoni parādās šādi “A1: B3”. Šī atsauce attiecas uz visām šūnām starp A1 un B3: šūnas A1, A2, A3, B1, B2, B3.
Lai atlasītu šūnu diapazonu, ievadot formulu:
- Ierakstiet diapazonu (atdaliet sākuma un beigu diapazonu ar semikolu)
- Izmantojiet peli, lai noklikšķinātu uz pirmās šūnas atsauces, turiet nospiestu peles pogu un velciet vajadzīgo diapazonu.
- Turiet nospiestu taustiņu Shift un pārvietojieties ar bulttaustiņiem, lai izvēlētos savu diapazonu
Absolūtās (iesaldētās) un relatīvās atsauces
Ievadot šūnu atsauces formulās, varat izmantot relatīvās vai absolūtās (iesaldētās) atsauces. Kopējot formulu jaunā šūnā, relatīvās atsauces pārvietosies proporcionāli. Absolūtās atsauces paliks nemainīgas. Apskatīsim dažus piemērus:
Relatīvā atsauce
Relatīvā atsauce programmā Excel izskatās šādi
= A1
Kopējot un ielīmējot formulu ar relatīvām atsaucēm, relatīvās atsauces pārvietosies proporcionāli. Ja vien nenorādāt citādi, jūsu šūnu atsauces pēc noklusējuma būs relatīvas (neiesaldētas).
Piemērs: ja kopējat “= A1” uz leju vienā rindā, atsauce mainās uz “= A2”.
Absolūto (saldēto) šūnu atsauces
Ja nevēlaties, lai šūnu atsauces pārvietotos, kopējot formulu, varat “iesaldēt” šūnu atsauces, pievienojot dolāra zīmes ($ s) tās atsauces priekšā, kuru vēlaties iesaldēt. Tagad, kopējot un ielīmējot formulu, šūnu atsauce paliks nemainīga. Varat izvēlēties iesaldēt rindas atsauci, slejas atsauci vai abus.
A1: nekas nav iesaldēts
$ A1: kolonna ir iesaldēta, bet rinda nav iesaldēta
A $ 1: rinda ir iesaldēta, bet kolonna nav iesaldēta
$ A $ 1: gan rinda, gan kolonna ir iesaldēta
Absolūtās atsauces saīsne
Manuāli pievienot formulās dolāra zīmes ($ s) nav ļoti praktiski. Tā vietā, veidojot formulu, izmantojiet taustiņu F4, lai pārslēgtos starp absolūtām/relatīvām šūnu atsaucēm.
Absolūto šūnu atsauces piemērs
Kad jums faktiski vajadzētu iesaldēt šūnas atsauci? Viens izplatīts piemērs ir tad, ja jums ir ievades šūnas, uz kurām bieži atsaucas. Tālāk sniegtajā piemērā mēs vēlamies aprēķināt pārdošanas nodokli katram izvēlnes vienumu daudzumam. Pārdošanas nodoklis ir nemainīgs visām precēm, tāpēc mēs atkārtoti norādīsim pārdošanas nodokļa šūnu.
Lai atrastu kopējo pārdošanas nodokli, D slejā ievadiet formulu ‘= (B3*C3)*$ C $ 1’ un nokopējiet formulu uz leju.
Jaukta atsauce
Jūs, iespējams, esat dzirdējuši par jauktu šūnu atsaucēm. Jaukta atsauce ir tad, ja rindas vai kolonnas atsauce ir bloķēta (bet ne abas).
Jaukta atsauce
Atcerieties, ka, izmantojot taustiņu “F4”, jūs varat pārvietoties pa relatīvajām, absolūtajām šūnu atsaucēm.
Šūnu atsauces - rindu/kolonnu ievietošana un dzēšana
Jums var rasties jautājums, kas notiek ar jūsu šūnu atsaucēm, ievietojot vai dzēšot rindas/kolonnas?
Šūnu atsauce tiks automātiski atjaunināta, lai atsauktos uz sākotnējo šūnu. Tas tā ir neatkarīgi no tā, vai šūnas atsauce ir iesaldēta.
3D atsauces
Dažreiz jums var būt nepieciešams strādāt ar vairākām darblapām ar identiskiem datu modeļiem. Programma Excel ļauj atsaukties uz vairākām lapām vienlaikus, manuāli neievadot katru darblapu. Varat atsaukties uz lapu diapazonu, kas ir līdzīgs tam, kā būtu atsauce uz šūnu diapazonu. Piemērs “1. lapa: lapa 5! A1” atsaucas uz šūnām A1 visās lapās no 1. lapas līdz 5. lapai.
Apskatīsim piemēru:
Jūs vēlaties saskaitīt kopā pārdotās vienības par katru produktu visos veikalos. Katram veikalam ir sava darblapa, un visām darblapām ir identisks formāts. Jūs varat izveidot līdzīgu formulu:
Tas nav pārāk grūti ar tikai četrām darblapām, bet ko darīt, ja jums būtu 40 darba lapas? Vai tiešām vēlaties manuāli pievienot katru šūnas atsauci?
Tā vietā varat izmantot 3D atsauci, lai vienlaikus viegli atsauktos uz vairākām lapām (līdzīgi kā ar atsauci uz šūnu diapazonu).
Esi uzmanīgs! Jūsu darblapu secībai ir nozīme. Ja starp atsauces lapām (StoreA un StoreD) pārvietojat citu lapu, šī lapa tiks iekļauta. Un otrādi, ja pārvietojat lapu ārpus lokšņu diapazona (pirms StoreA vai pēc StoreD), tā vairs netiks iekļauta.
Apļveida šūnu atsauce
Apļveida šūnu atsauce ir tad, kad šūna atsaucas uz sevi. Piemēram, ja šūnas B1 rezultātu izmanto kā ievadi šūnai B1, tiek izveidota apļveida atsauce. Šūnai nav nepieciešams tieši atsaukties uz sevi Var būt starpposmi.
Piemērs:
Šajā gadījumā šūnas B2 formula ir “A2+A3+B2”. Tā kā atrodaties šūnā B2, vienādojumā nedrīkst izmantot B2. Tas aktivizēs apļveida atsauci, un vērtība šūnā “B2” automātiski tiks iestatīta uz “0”.
Parasti apļveida atsauces ir lietotāja kļūdas rezultāts, taču dažos gadījumos, iespējams, vēlēsities izmantot apļveida atsauci. Galvenais apļveida atsauces izmantošanas piemērs ir vērtību atkārtota aprēķināšana. Lai to izdarītu, jums jādodas uz Fails> Iespējas> Formulas un Iespējots iteratīvs aprēķins:
Ārējās atsauces
Dažreiz, aprēķinot datus, jums var būt nepieciešams atsaukties uz datiem, kas nav jūsu darbgrāmatā. To sauc par ārēju atsauci (saite).
Lai, veidojot formulu, atlasītu ārēju atsauci, dodieties uz ārējo darbgrāmatu un atlasiet atsauci kā parasti.
Lai pārietu uz citu darbgrāmatu, varat izmantot īsinājumtaustiņu CTRL + TAB vai doties uz Skats> Pārslēgt Windows.
Kad esat atlasījis šūnu, redzēsit, ka ārējā atsauce izskatās šādi:
Ņemiet vērā, ka darbgrāmatas nosaukums ir iekavās [].
Kad esat aizvēris atsauces darbgrāmatu, atsauce parādīs faila atrašanās vietu:
Atverot darbgrāmatu, kurā ir ārējā saite, jums tiks piedāvāts iespējot saišu automātisku atjaunināšanu. Ja jūs to darāt, programma Excel atvērs atsauces vērtību ar darbgrāmatas pašreizējo vērtību. Pat ja tas ir slēgts! Esi uzmanīgs! Jūs to varat vai nevēlaties.
Nosauktie diapazoni un ārējās atsauces
Kas notiek ar jūsu ārējo šūnu atsauci, kad atsauces darbgrāmatā tiek pievienotas vai dzēstas rindas / kolonnas? Ja abas darbgrāmatas ir atvērtas, šūnu atsauces tiek automātiski atjauninātas. Tomēr, ja abas darbgrāmatas nav atvērtas, šūnu atsauces netiks atjauninātas un vairs nebūs derīgas. Saistot ar ārējām darbgrāmatām, tas rada milzīgas bažas. Tas izraisa daudzas kļūdas.
Ja izveidojat saiti uz ārējām darbgrāmatām, šūnas atsaucei ir jāpiešķir nosaukums ar nosauktu diapazonu (sīkāku informāciju skatiet iepriekšējā sadaļā). Tagad jūsu formula attieksies uz nosaukto diapazonu neatkarīgi no tā, kādas izmaiņas notiek ārējā darbgrāmatā.
