Relatīvā un absolūtā šūnu atsauce un formatēšana

2024 Autors: Geoffrey Carr | [email protected]. Pēdējoreiz modificēts: 2023-12-17 10:58

Šajā nodarbībā mēs apspriežam šūnu atsauces, kā kopēt vai pārvietot formulu un formatēt šūnas. Lai sāktu, noskaidrosim to, ko mēs domājam pēc šūnu atsaucēm, kas pamato lielu formulu un funkciju jaudu un daudzveidību. Konkrēta izpratne par to, kā šūnu atsauces darbosies, ļaus jums vislabāk izmantot Excel izklājlapas!

SKOLAS NAVIGĀCIJA

Kāpēc jums ir nepieciešamas formulas un funkcijas?
Formulas definēšana un izveide
Relatīvā un absolūtā šūnu atsauce un formatēšana
Noderīgas funkcijas, kas jums jāzina
Meklējumi, diagrammas, statistika un pagaidu tabulas

Piezīme: mēs tikai pieņemam, ka jūs jau zināt, ka šūna ir viena no izklājlapas kvadrātiem, kas izvietota kolonnās un rindās, uz kurām atsaucas burti un cipari, kas darbojas horizontāli un vertikāli.

Kas ir šūnu atsauce?

"Šūnu atsauce" ir šūna, uz kuru atsaucas cits šūna. Piemēram, ja šūnā A1 jums ir = A2. Tad A1 attiecas uz A2.

Apskatīsim to, ko mēs teicām 2. nodarbībā par rindām un kolonnas, lai mēs varētu tālāk izpētīt šūnu atsauces.

Izklājlapā esošās šūnas norāda rindas un kolonnas. Kolonnas ir vertikālas un marķētas ar burtiem. Rindas ir horizontālas un marķētas ar cipariem.

Izklājlapas pirmā šūna ir A1, kas nozīmē A sleja, 1. rinda, B3 attiecas uz šūnu, kas atrodas otrajā kolonnā, trešajā rindā un tā tālāk.

Mācību mērķiem par šūnu atsaucēm mēs dažreiz rakstīsim tos kā rindu, kolonnu, šī tabula nav derīga nozīme, un tā vienkārši ir paredzēta, lai padarītu lietas skaidrākas.

Šūnu atsauces veidi

Pastāv trīs veidu šūnu atsauces.

Absolūtais - tas nozīmē, ka šūnas atsauce paliek nemainīga, ja jūs kopējat vai pārvietojat šūnu uz citu šūnu. To dara, piestiprinot rindu un kolonnu, tādēļ tas nemainās, kad tiek kopēts vai pārvietots.

Relatīvā relatīvā atsauce nozīmē, ka šūnas adrese mainās, kad to kopējat vai pārvietojat; t.i., šūnas atsauce ir atkarīga no tās atrašanās vietas.

Jaukts - tas nozīmē, ka, ja kopējat vai pārvietojat šūnu, varat izvēlēties enkura rindu vai kolonnu, lai tas mainītu, bet otru neizmanto. Piemēram, jūs varētu norādīt rindas atsauci, tad pārvietojiet šūnu divās rindās un četrās kolonnās, un rindas atsauce paliek nemainīga. Mēs to tālāk paskaidrosim.

Relatīvās atsauces

Let 's atsaukties uz šo agrāko piemēru - pieņemsim, ka šūnā A1 mums ir formula, kas vienkārši saka = A2. Tas nozīmē, ka Excel izvadi šūnā A1 neatkarīgi no tā, kas ievadīts šūnā A2. Šūnā A2 mēs esam ievadījuši "A2", lai Excel parādītu vērtību "A2" šūnā A1.

Tagad pieņemsim, ka mums ir jāizdara vieta mūsu izklājlapā, lai iegūtu vairāk datu. Mums ir jāpievieno slejas virs un rindas pa kreisi, tāpēc mums ir jāpārvieto šūna uz leju un pa labi, lai atbrīvotu vietu.

Kamēr pārvietojat šūnu pa labi, slejas numurs palielinās. Kad jūs pārvietojat to uz leju, rindas numurs palielinās. Šūnu, uz kuru tas norāda, arī šūnu atsauce mainās. Tas ir parādīts zemāk:

Turpinot mūsu piemēru un apskatot tālāk redzamo grafiku, ja jūs kopējat ailes A1 saturu divus pa labi un četrus leju, jūs to pārvietojāt uz šūnu C5.

Mēs kopēja šūnu divas kolonnas pa labi un četras uz leju. Tas nozīmē, ka esam mainījuši šūnu, uz kuru attiecas divi pāri un četri lejup. A1 = A2 tagad ir C5 = C6. Tā vietā, lai atsauktos uz A2, tagad šūna C5 attiecas uz šūnu C6.

Parādīta vērtība ir 0, jo šūna C6 ir tukša. C6 formātā mēs ierakstiet "Es esmu C6" un tagad C5 parāda "Es esmu C6".

Piemērs: teksta formula

Pamēģināsim vēl vienu piemēru. Atcerieties no 2. nodarbības, kur mums bija jāsadala pilns vārds uzvārdu un uzvārdu? Kas notiek, kad mēs kopējam šo formulu?

Uzrakstiet formulu = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) vai nokopējiet tekstu uz C3 šūnu. Nekopējiet faktisko šūnu, tikai tekstu, kopējiet tekstu, pretējā gadījumā tas atjauninās atsauci.

Jūs varat rediģēt šūnas saturu izklājlapas augšdaļā lodziņā blakus vietai, kurā saka "fx". Šī kastīte ir garāka nekā šūna ir plaša, tāpēc to ir vieglāk rediģēt.

Tagad mums ir:

Nekas sarežģīts, mēs tikko esam uzrakstījuši jaunu formulu šūnā C3. Tagad kopējiet C3 ar šūnām C2 un C4. Ievērojiet tālāk minētos rezultātus.

Izmantojiet kursoru, lai izceltu šūnas C2, C3 un C4. Novietojiet kursoru uz šūnu B2 un ielīmējiet saturu. Paskaties, kas noticis - rodas kļūda: "#REF." Kāpēc tas notiek?

Kad mēs kopējām šūnas no kolonnas C uz B kolonnu, tā atjaunināja atsauces vienu kolonnu pa kreisi = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Tas mainīja katru atsauci uz A2 uz kolonnu pa kreisi no A, bet kolonnā pa kreisi no A slejas nav. Tādējādi dators nezina, ko jūs domājat.

Jaunā formula B2, piemēram, ir = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) un rezultāts ir #REF:

Formulas kopēšana uz šūnu diapazonu

Šūnu kopēšana ir ļoti ērta, jo jūs varat rakstīt vienu formulu un nokopēt to lielā apgabalā, un atsauce tiek atjaunināta.Tas novērš nepieciešamību rediģēt katru šūnu, lai nodrošinātu, ka tas norāda uz pareizo vietu.

Ar "diapazonu" mēs domājam vairāk nekā vienu šūnu. Piemēram, (C1: C10) ir visas šūnas no šūnas C1 līdz šūnai C10. Tā ir šūnu kolonna. Cits piemērs (A1: AZ1) ir augšējā rinda no kolonnas A līdz kolonnai AZ.

Ja diapazons šķērso pieci kolonnas un desmit rindas, tad jūs norādāt diapazonu, rakstot augšējā kreisā šūna un apakšējā labajā pusē, piemēram, A1: E10. Tas ir kvadrātveida laukums, kas šķērso rindas un kolonnas, nevis tikai daļu no kolonnas vai rindas daļas.

Šeit ir piemērs, kas parāda, kā kopēt vienu šūnu vairākām atrašanās vietām. Pieņemsim, ka mēs vēlamies, lai izklājlapā tiktu parādīti mēneša plānotie izdevumi, lai mēs varētu izveidot budžetu. Mēs izveidojam šādu izklājlapu:

Tagad nokopējiet formulu šūnā C3 (= B3 + C2) uz pārējo sleju, lai nodrošinātu mūsu budžeta izpildes bilanci. Excel atjauno šūnas atsauci, kad to kopējat. Rezultāts ir parādīts zemāk:

Kā redzat, katra jaunā šūna tiek atjaunināta radinieks uz jauno atrašanās vietu, tādēļ šūnu C4 atjauno savu formulu uz = B4 + C3:

Cell C5 atjaunina uz = B5 + C4 un tā tālāk:

Absolūtās atsauces

Absolūtais atsauce nemainās, pārvietojot vai kopējot šūnu. Mēs izmantojam $ zīmi, lai izveidotu absolūtu atsauci - atcerieties, ka domājat par dolāra zīmi kā enkura.

Piemēram, ievadiet formulu = $ A $ 1 jebkurā šūnā. Kolonnas A priekša iezīmē kolonnu, kolonnas nemaina, $ 1 priekš rindas 1 nozīmē kolonnas nomaiņu, ja kopējat vai pārvietojat šūnu uz citu šūnu.

Kā redzat tālāk redzamajā piemērā, šūnā B1 ir relatīva atsauce = A1.Ja mēs kopējam B1 četrām šūnām zem tā, relatīvā atsauce = A1 mainās uz šūnu pa kreisi, tāpēc B2 kļūst par A2, B3 kļūstot A3 utt. Šīm šūnām, protams, nav ievadīta vērtība, tāpēc izeja ir nulle.

Tomēr, ja mēs izmantojam = $ A1 $ 1, piemēram, C1 un kopējam to četrās šūnās zem tā, atsauce ir absolūta, tādēļ tā nekad nemainās, un izvade vienmēr ir vienāda ar vērtību A1 šūnā.

Pieņemsim, ka jūs sekojat savām interesēm, piemēram, tālāk sniegtajā piemērā. Formula C4 = B4 * B1 ir "procentu likme" * * "bilance" = "procenti gadā".

Tagad jūs esat mainījis budžetu un esat ietaupījis papildu 2 000 ASV dolāru, lai iegādātos kopfondu. Pieņemsim, ka tas ir fiksēta procentu likmes fonds, un tas maksā tādu pašu procentu likmi. Ievadiet jauno kontu un līdzsvaru izklājlapā un pēc tam nokopējiet formulu = B4 * B1 no šūnas C4 uz šūnu C5.

Jaunais budžets izskatās šādi:

Jaunais kopfonds nopelna 0 ASV dolārus par procentiem gadā, kas nevar būt taisnība, jo procentu likme ir skaidri 5 procenti.

Excel izceļ šūnas, uz kurām ir atsauces uz formulu. Jūs varat redzēt iepriekš, ka atsauce uz procentu likmi (B1) tiek pārvietota uz tukšu šūnu B2. Mums vajadzēja izdarīt atsauci uz absolūto B1, rakstot $ B $ 1, izmantojot dolāru zīmi, lai piesaistītu rindu un sleju atsauci.

Pārrakstīt pirmo aprēķinu C4, lai lasītu = B4 * $ B $ 1, kā parādīts zemāk:

Tad pārkopējiet šo formulu no C4 uz C5. Izklājlapa tagad izskatās šādi:

Tā kā mēs nokopējām formulu vienu šūnu uz leju, t.i., rindu palielināja par vienu, jaunā formula ir = B5 * $ B $ 1. Savukārt kopējo fondu procentu likmi tagad aprēķina pareizi, jo procentu likme ir piesaistīta šūnai B1.

Šis ir labs piemērs tam, kad jūs varētu izmantot nosaukumu, lai norādītu uz šūnu. Nosaukums ir absolūts piemērs. Piemēram, lai piešķirtu nosaukumu "procentu likme" šūnai B1, ar peles labo pogu noklikšķiniet uz šūnas un pēc tam atlasiet "definēt nosaukumu".

Vārdi var attiekties uz vienu šūnu vai diapazonu, un jūs varat izmantot nosaukumu formulā, piemēram, = interest rate * 8 ir tāda pati kā rakstiski = $ B $ 1 * 8.

Jauktas atsauces

Jauktas atsauces ir tad, kad arī rinda vai kolonna ir noenkurota.

Piemēram, pieņemsim, ka jūs esat lauksaimnieks, kurš veido budžetu. Jums pieder arī barības veikals un sēklu pārdošana. Jūs gatavojaties stādīt kukurūzu, sojas pupas un lucerna. Tālāk redzamajā izklājlapā ir redzamas izmaksas par akriem. "Cena par akriem" = "cena par mārciņu" * "mārciņas sēklas uz akriem" - tas ir tas, ka tas tev izmaksās augu akriem.

Ievadiet izmaksas par akriem kā = B2 * C2 šūnā D2. Jūs sakāt, ka vēlaties piestiprināt cenu par mārciņu sleju. Tad pārkopējiet šo formulu uz citām rindām tajā pašā slejā:

Tagad jūs vēlaties uzzināt sēklu inventarizācijas vērtību. Lai uzzinātu inventarizācijas vērtību, jums ir nepieciešama cena par mārciņu un mārciņu daudzums inventāra sarakstā.

Mēs pievienojam divas kolonnas: "Inventāra sēklu mārciņa" un pēc tam "Inventāra vērtība". Tagad nokopējiet šūnu D2 uz F4 un atzīmējiet, ka rindas atsauce sākotnējās formulas pirmajā daļā ($ B2) tiek atjaunināta uz rindu 4, taču kolonna paliek nemainīga, jo $ piesaista to "B."

Šī ir jaukta atsauce, jo sleja ir absolūta, un rinda ir relatīva.

Apļveida atsauces

Apkārtējā atsauce ir tad, kad formula attiecas uz sevi.

Piemēram, jūs nevarat rakstīt c3 = c3 + 1. Šāda veida aprēķinu sauc par "iterāciju", kas nozīmē, ka tā atkārtojas. Excel neatbalsta iterāciju, jo tā visu aprēķina tikai vienu reizi.

Ja jūs mēģināt to izdarīt, ierakstot SUM (B1: B5) šūnā B5:

Excel tikai informē, ka ekrāna apakšdaļā ir redzama apļveida atsauce, lai jūs varētu to nepamanīt. Ja jums ir cirkulāra atsauce un aizveriet izklājlapu un atveriet to vēlreiz, Excel jums parādīs uznirstošo logu, kurā ir norāde par apkārtrakstu.

Ja jums ir cirkulāra atsauce, katru reizi, kad atverat izklājlapu, Excel jums pateiks šo uznirstošo logu, kurā jums ir cirkulāra atsauce.

Atsauces uz citām darblapām

"Darbgrāmata" ir "darblapu" kolekcija. Vienkārši izsakoties, tas nozīmē, ka tajā pašā Excel failā (darbgrāmatā) var būt vairākas izklājlapas (darblapas). Kā jūs varat redzēt zemāk redzamajā piemērā, mūsu piemēra darbgrāmatā ir daudz darba lapu (sarkanā krāsā).

Pēc noklusējuma darblapas ir nosauktas Sheet1, Sheet2 un tā tālāk. Jūs izveidojat jaunu, noklikšķinot uz "+" ekrāna apakšdaļā.

Jūs varat mainīt darblapas nosaukumu uz kaut ko noderīgu, piemēram, "aizdevums" vai "budžets", ar peles labo pogu noklikšķinot uz darblapas cilnes, kas redzama Excel programmas ekrāna apakšdaļā, izvēloties pārdēvēt un ievadīt jaunu nosaukumu.

Vai arī jūs varat vienkārši dubultklikšķi uz cilnes un pārdēvēt to.

Darblapas atsauces sintakse ir = darblapas! Šūna. Jūs varat izmantot šāda veida atsauču, ja vienā vērtībā tiek izmantotas divas darblapas, piemēram, tas varētu būt:

Šodienas datums
Valūtas konvertēšanas kurss no dolāriem uz eiro
Viss, kas attiecas uz visām darbgrāmatas darblapām

Zemāk ir piemērs darblapas "interese", atsaucoties uz darblapu "aizdevums", šūnu B1.

Ja mēs skatāmies uz "aizdevuma" darblapu, mēs varam redzēt atsauci uz aizdevuma summu:

Nākamā …

Mēs ceram, ka jums tagad ir stingra saziņa ar šūnu atsaucēm, tostarp relatīva, absolūta un jaukta. Protams, ir daudz.

Tas ir par mūsdienu nodarbību 4. nodarbībā, mēs apspriedīsim dažas noderīgas funkcijas, kuras jūs, iespējams, vēlēsities zināt ikdienas Excel lietošanai.