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

Šajā stundā mēs apspriežam šūnu atsauces, kā kopēt vai pārvietot formulu un formatēt šūnas. Vispirms noskaidrojiet, ko mēs saprotam ar šūnu atsaucēm, kas ir pamatā daudzu formulu un funkciju jaudai un daudzpusībai. Konkrēts priekšstats par to, kā šūnu atsauču darbs ļaus jums iegūt maksimālu labumu no jūsu Excel izklājlapām!

SKOLAS NAVIGĀCIJA

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

Piezīme: mēs tikai pieņemam, ka jūs jau zināt, ka šūna ir viens no izklājlapas laukumiem, kas sakārtots kolonnās un rindās, uz kurām atsaucas burti un skaitļi, kas darbojas horizontāli un vertikāli.

Kas ir šūnu atsauce?

“Šūnu atsauce” ir šūna, uz kuru attiecas cita šūna. Piemēram, ja šūnā A1 ir = A2. Tad A1 attiecas uz A2.

Apskatīsim, ko mēs teicām 2. stundā par rindām un kolonnām, lai mēs varētu sīkāk izpētīt šūnu atsauces.

Tabulā esošās šūnas ir norādītas rindās un kolonnās. Kolonnas ir vertikālas un marķētas ar burtiem. Rindas ir horizontālas un marķētas ar cipariem.

Pirmā izklājlapas šūna ir A1, kas nozīmē A sleju, 1. rindu, B3 attiecas uz šūnu, kas atrodas otrajā slejā, trešajā rindā, un tā tālāk.

Lai uzzinātu par šūnu atsaucēm, mēs reizēm tos rakstīsim kā rindu, kolonnu, tas nav derīgs apzīmējums izklājlapā un vienkārši domāts, lai padarītu lietas skaidrākas.

Šūnu atsauču veidi

Ir trīs veidu šūnu atsauces.

Absolute - tas nozīmē, ka šūnas atsauce paliek tāda pati, ja kopējat vai pārvietojat šūnu uz jebkuru citu šūnu. Tas tiek darīts, piestiprinot rindu un kolonnu, tāpēc tas netiek mainīts, kad kopēts vai pārvietots.

Relatīvais - relatīvā atsauce nozīmē, ka šūnas adrese mainās, kopējot vai pārvietojot to; t.i., šūnu atsauce ir saistīta ar tās atrašanās vietu.

Jaukta - tas nozīmē, ka varat kopēt vai pārvietot šūnu vai nu rindu, vai kolonnu, lai mainītos un otrs nebūtu. Piemēram, jūs varat nostiprināt rindas atskaiti, tad pārvietot šūnu uz leju divās rindās un četrās kolonnās un rindas atskaite paliek nemainīga. Mēs to tālāk paskaidrosim.

Relatīvās atsauces

Aplūkosim šo agrāko piemēru - pieņemsim, ka šūnā A1 mums ir formula, kas vienkārši saka = A2. Tas nozīmē Excel izvadi šūnā A1, kas tiek ievadīts šūnā A2. Šūnā A2 esam ierakstījuši “A2”, lai Excel šūnā A1 parādītu vērtību “A2”.

Tagad pieņemsim, ka mūsu izklājlapā ir jāievieto vairāk datu. Mums ir jāpievieno kolonnas iepriekš un rindas pa kreisi, tāpēc mums ir jāpārvieto šūna uz leju un pa labi, lai padarītu istabu.

Kad pārvietojat šūnu pa labi, kolonnas numurs palielinās. Pārvietojot to uz leju, rindas numurs palielinās. Šūnas, uz kurām tā norāda, šūnu atsauce arī mainās. Tas ir parādīts tālāk:

Turpinot ar mūsu piemēru un aplūkojot zemāk redzamo grafiku, ja kopējat A1 šūnu saturu pa labi un četri uz leju, jūs esat pārvietojis to uz šūnu C5.

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

Parādītā vērtība ir 0, jo šūna C6 ir tukša. Šūnā C6 ierakstām “Es esmu C6”, un tagad C5 parāda “Es esmu C6”.

Piemērs: Teksta formula

Izmēģināsim vēl vienu piemēru. Atcerieties no 2. stundas, kur mums bija jāsadala pilns vārds uz vārdu un uzvārdu? Kas notiek, ja mēs kopējam šo formulu?

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

Jūs varat rediģēt šūnas saturu izklājlapas augšdaļā lodziņā blakus, kur ir teikts “fx”. Šis lodziņš ir garāks par šūnu platumu, tāpēc ir vieglāk rediģēt.

Tagad mums ir:

Nekas sarežģīts, mēs tikko rakstījām jaunu formulu šūnā C3. Tagad kopējiet C3 uz C2 un C4 šūnām. Ievērojiet tālāk norādītos rezultātus:

Tagad mums ir Alexander Hamilton un Thomas Jefferson vārdi.

Izmantojiet kursoru, lai iezīmētu šūnas C2, C3 un C4. Novietojiet kursoru uz B2 šūnu un ielīmējiet saturu. Paskaties, kas notika - mēs saņemam kļūdu: „#REF.” Kāpēc tas ir?

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

Tā mainīja katru atsauci uz A2 uz kolonnu pa kreisi no A, bet A kolonnas kreisajā pusē nav kolonnas. Tāpēc dators nezina, ko tu domā.

Piemēram, jaunā formula B2 ir = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) un rezultāts ir #REF:

Formulas kopēšana šūnu diapazonā

Šūnu kopēšana ir ļoti ērta, jo jūs varat uzrakstīt vienu formulu un kopēt to uz lielu laukumu un atsauce tiek atjaunināta. Tas ļauj izvairīties no katras šūnas rediģēšanas, lai nodrošinātu, ka tā norāda uz pareizo vietu.

Ar “diapazonu” mēs saprotam vairāk nekā vienu šūnu. Piemēram, (C1: C10) ir visas šūnas no šūnas C1 līdz šūnai C10. Tātad tā ir šūnu kolonna. Vēl viens piemērs (A1: AZ1) ir augšējā rinda no A kolonnas uz A sleju.

Ja diapazons šķērso piecas kolonnas un desmit rindas, tad jūs norādāt diapazonu, rakstot augšējo kreiso un apakšējo labo pusi, 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 ilustrē, kā kopēt vienu šūnu vairākās vietās. Pieņemsim, ka mēs vēlamies parādīt mūsu plānotos izdevumus mēnesī izklājlapā, lai mēs varētu sagatavot budžetu. Mēs veidojam izklājlapu šādi:

Tagad kopējiet formulu šūnā C3 (= B3 + C2) pārējā kolonnā, lai iegūtu mūsu budžeta bilanci. Kopējot to, Excel atjaunina šūnu atsauci. Rezultāts ir parādīts zemāk:

Kā redzat, katru jaunu šūnu atjaunina radinieks uz jauno atrašanās vietu, tāpēc C4 šūna atjaunina formulu = B4 + C3:

Šūnu C5 atjauninājumi ir = B5 + C4, un tā tālāk:

Absolūtās atsauces

Ja pārvietojat vai kopējat šūnu, absolūtā atsauce nemainās. Mēs izmantojam $ zīmi, lai izdarītu absolūtu atsauci - atcerēties to, domājiet par dolāra zīmi kā enkuru.

Piemēram, jebkurā šūnā ievadiet formulu = $ A $ 1. $ Kolonnas priekšā nozīmē, ka kolonna netiek mainīta, $ 1 rindas priekšā nozīmē, ka neizmaina kolonnu, kad kopējat vai pārvietojat šūnu uz jebkuru citu šūnu.

Kā redzams zemāk redzamajā piemērā, B1 šūnā ir relatīva atsauce = A1.Kad mēs kopējam B1 uz č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ļūt par A3, utt. Šīm šūnām acīmredzami nav vērtības, tāpēc izejas vērtība ir nulle.

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

Pieņemsim, ka jūs sekojat līdzi jūsu interesēm, piemēram, zemāk redzamajā piemērā. Formula C4 = B4 * B1 ir “procentu likme” * “bilance” = “procenti gadā”.

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

Jaunais budžets izskatās šādi:

Jaunais kopieguldījumu fonds nopelna $ 0 procentos gadā, kas nevar būt pareizs, jo procentu likme ir acīmredzami 5 procenti.

Excel izceļ šūnas, uz kurām attiecas formulas atsauces. Iepriekš redzams, ka atsauce uz procentu likmi (B1) tiek pārvietota uz tukšu šūnu B2. Mums vajadzēja izdarīt atsauci uz B1 absolūto, rakstot $ B $ 1, izmantojot dolāru zīmi, lai nostiprinātu rindas un kolonnas atsauci.

Pirmo aprēķinu pārrakstiet C4, lai lasītu = B4 * $ B $ 1, kā parādīts tālāk:

Tad kopējiet šo formulu no C4 uz C5. Šī izklājlapa tagad izskatās šādi:

Tā kā mēs kopējām formulu vienu šūnu uz leju, t.i., rindu palielinot par vienu, jaunā formula ir = B5 * $ B $ 1. Ieguldījumu fondu procentu likme tiek aprēķināta pareizi, jo procentu likme ir nostiprināta pie B1.

Tas ir labs piemērs tam, kad jūs varētu izmantot “vārdu”, lai atsauktos uz šūnu. Nosaukums ir absolūta atsauce. Piemēram, lai piešķirtu nosaukumam “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 tas pats, kas rakstot = $ B $ 1 * 8.

Jauktas atsauces

Jauktas atsauces ir, kad arī rindu vai kolonna ir nostiprināta.

Piemēram, pieņemsim, ka esat lauksaimnieks, kas veido budžetu. Jums pieder arī barības krātuve un pārdodiet sēklas. Jūs gatavojaties stādīt kukurūzu, sojas pupas un lucernu. Zemāk redzamā izklājlapā ir norādītas izmaksas par akriem. “Maksa par akru” = “cena par mārciņu” * “mārciņas sēklu uz akru” - tas būs tas, kas jums izmaksās, lai audzētu akru.

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

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

Mēs pievienojam divas slejas: “sēklu svars krājumā” un pēc tam “krājumu vērtība”. Tagad kopējiet šūnu D2 uz F4 un ņemiet vērā, ka rindas atsauce sākotnējās formulas pirmajā daļā ($ B2) tiek atjaunināta, lai rinda 4, bet kolonna ir fiksēta, jo $ nostiprina to uz "B."

Tā ir jaukta atsauce, jo kolonna ir absolūta un rinda ir relatīva.

Cirkulārās atsauces

Apkārtraksta 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:

Parādās brīdinājuma ekrāns:

Excel tikai norāda, ka ekrāna apakšdaļā ir apļveida atsauce, lai jūs to nepamanītu. Ja jums ir apļveida atsauce un aizverat izklājlapu un atverat to vēlreiz, programma Excel paziņos uznirstošajā logā, ka jums ir apļveida atsauce.

Ja jums ir cirkulāra atsauce, ikreiz, kad atverat izklājlapu, Excel jums parādīs šo uznirstošo logu, ka jums ir apļveida atsauce.

Atsauces uz citām darblapām

“Darbgrāmata” ir “darblapu” kolekcija. Vienkārši sakot, tas nozīmē, ka vienā Excel failā (darbgrāmatā) var būt vairākas izklājlapas (darblapas). Kā redzams zemāk redzamajā piemērā, mūsu piemērā darbgrāmatā ir daudzas darblapas (sarkanā krāsā).

Pēc noklusējuma darblapas ir nosauktas Sheet1, Sheet2 utt. Jūs izveidojat jaunu, noklikšķinot uz “+” Excel ekrāna apakšā.

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

Vai arī varat vienkārši divreiz uzklikšķināt uz cilnes un pārdēvēt to.

Darblapas atsauces sintakse ir = darblapas! Jūs varat izmantot šāda veida atsauces, ja viena un tā pati vērtība tiek izmantota divās darblapās, kuru piemēri varētu būt:

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

Zemāk ir piemērs darblapai “interese”, kas atsaucas uz darblapas “aizdevumu”, šūna B1.

Ja paskatāmies uz “aizdevuma” darblapu, mēs redzam atsauci uz aizdevuma summu:

Nākošais…

Mēs ceram, ka jums tagad ir stingra izpratne par šūnu atsaucēm, ieskaitot relatīvo, absolūtu un jauktu. Ir noteikti daudz.

Tas ir šodienas mācību stundā, 4. stundā mēs apspriedīsim dažas noderīgas funkcijas, kuras jūs varētu vēlēties zināt ikdienas Excel lietošanā.