Lai varētu atsaukties uz šūnām un diapazoniem, visi izklājlapu rīki darbojas. Un Excel ir labākais un visspēcīgākais.
Šajā apmācībā es apskatīšu visu, kas jums jāzina par atsauci uz šūnām un diapazoniem programmā Excel. Neatkarīgi no pamata atsaucēm tajā pašā lapā, lielākā daļa šīs apmācības būtu par kā atsaukties uz citu lapu vai darbgrāmatu programmā Excel.
Lai gan tā darbības principā nav lielas atšķirības, atsaucoties uz citu lapu tajā pašā failā vai atsaucoties uz pilnīgi atsevišķu Excel failu, šīs atsauces formāts nedaudz mainās.
Ir arī dažas svarīgas lietas, kas jums jāpatur prātā, atsaucoties uz citu lapu vai citiem ārējiem failiem.
Bet uztraucieties … nekas nav pārāk traks!
Kad esat pabeidzis šo apmācību, jūs zināt visu, kas jāzina par atsaucēm uz šūnām un diapazoniem programmā Excel (atrodieties tajā pašā darbgrāmatā vai citā darbgrāmatā).
Sāksim!
Atsauce uz šūnu tajā pašā lapā
Šis ir visvienkāršākais atsauces līmenis, kad jūs atsaucaties uz vienas lapas šūnu.
Piemēram, ja esmu šūnā B1 un vēlos atsaukties uz šūnu A1, formāts būtu šāds:
= A1
To darot, vērtība šūnā, kurā izmantojat šo atsauci, būs tāda pati kā šūnā A1. Un, ja jūs veicat izmaiņas šūnā A1, tās tiks atspoguļotas šūnā, kurā esat izmantojis šo atsauci.
Atsauce uz šūnu citā lapā
Ja jums ir jāatsaucas uz citu lapu tajā pašā darbgrāmatā, izmantojiet tālāk norādīto formātu.
Sheet_name! Cell_address
Pirmkārt, jums ir lapas nosaukums, kam seko izsaukuma zīme, kam seko šūnas atsauce.
Tātad, ja jums ir jāatsaucas uz 1. lapas šūnu A1, jums jāizmanto šāda atsauce:
= 1. lapa! A1
Un, ja vēlaties atsaukties uz šūnu diapazonu citā lapā, jums jāizmanto šāds formāts:
Sheet_name! First_cell: Last_cell
Tātad, ja vēlaties atsaukties uz diapazonu A1: C10 citā tās pašas darbgrāmatas lapā, jums jāizmanto tālāk norādītā atsauce.
= 1. lapa! A1: C10
Ņemiet vērā, ka esmu parādījis tikai atsauci uz šūnu vai diapazonu. Patiesībā jūs tos izmantotu formulās. Bet iepriekš minēto atsauču formāts paliks nemainīgs
Daudzos gadījumos jūsu norādītās darblapas nosaukumā būtu vairāki vārdi. Piemēram, tas varētu būt Projekta dati vai pārdošanas dati.
Ja jums ir atstarpes vai rakstzīmes, kas nav alfabētiskas (piemēram, @,!, #, -utt.), Nosaukums jāizmanto atsevišķās pēdiņās.
Piemēram, ja vēlaties atsaukties uz šūnu A1 ar lapu ar nosaukumu Pārdošanas dati, izmantojiet tālāk norādīto atsauci.
= 'Pārdošanas dati'! A1
Un, ja lapas nosaukums ir Pārdošanas dati, tad, lai atsauktos uz šīs lapas šūnu A1, jums jāizmanto tālāk norādītā atsauce:
= 'Pārdošanas dati'! A1
Atsaucoties uz lapu tajā pašā darbgrāmatā un vēlāk mainot darblapas nosaukumu, jums nav jāuztraucas par atsauces sadalīšanos. Excel automātiski atjauninās šīs atsauces jūsu vietā.
Lai gan ir lieliski zināt šo atsauču formātu, praksē nav tik laba ideja katru reizi tos ievadīt manuāli. Tas būtu laikietilpīgs un ļoti pakļauts kļūdām.
Ļaujiet man parādīt labāku veidu, kā izveidot šūnu atsauces programmā Excel.
Automātiska atsauces izveide uz citu lapu tajā pašā darbgrāmatā
Daudz labāks veids, kā izveidot šūnu atsauci uz citu lapu, ir vienkārši norādīt Excel uz šūnu/diapazonu, uz kuru vēlaties izveidot atsauci, un ļaut programmai Excel to izveidot pašam.
Tas nodrošinās, ka jums nav jāuztraucas par izsaukuma zīmes vai pēdiņu pazušanu vai citu formāta problēmu parādīšanos. Excel automātiski izveidos jums pareizo atsauci.
Tālāk ir norādītas darbības, lai automātiski izveidotu atsauci uz citu lapu:
- Atlasiet šūnu pašreizējā darbgrāmatā, kur nepieciešama atsauce
- Ierakstiet formulu, līdz nepieciešama atsauce (vai vienādības zīme, ja vēlaties tikai atsauci)
- Atlasiet lapu, uz kuru jums ir jāatsaucas
- Atlasiet šūnu/diapazonu, uz kuru vēlaties atsaukties
- Nospiediet Enter, lai iegūtu formulas rezultātu (vai turpinātu strādāt pie formulas)
Iepriekš minētās darbības automātiski izveidotu atsauci uz šūnu/diapazonu citā lapā. Šīs atsauces varēsit redzēt arī formulas joslā. Kad esat pabeidzis, varat vienkārši nospiest taustiņu Enter, un tas sniegs rezultātu.
Piemēram, ja jums ir daži dati šūnā A1: A10 lapā ar nosaukumu Pārdošanas dati un vēlaties iegūt šo vērtību summu pašreizējā lapā, rīkojieties šādi:
- Pašreizējā lapā (kur nepieciešams rezultāts) ierakstiet šādu formulu: = Sum (
- Atlasiet lapu “Pārdošanas dati”.
- Atlasiet diapazonu, kuru vēlaties pievienot (A1: A10). Tiklīdz jūs to izdarīsit, Excel automātiski izveidos atsauci uz šo diapazonu (to varat redzēt formulas joslā)
- Nospiediet ievadīšanas taustiņu.
Veidojot garu formulu, iespējams, jums būs jāatsaucas uz šūnu vai diapazonu citā lapā un pēc tam jāatgriežas sākuma lapā un jāatsaucas uz kādu šūnu/diapazonu.
To darot, pamanīsit, ka programma Excel automātiski ievieto atsauci uz lapu, kurā atrodas formula. Lai gan tas ir kārtībā un nekaitē, tas nav vajadzīgs. Šādā gadījumā jūs varat izvēlēties saglabāt atsauci vai noņemt to manuāli.
Vēl viena lieta, kas jums jāzina, veidojot atsauces, atlasot lapu un pēc tam šūnu/diapazonu, ir tā, ka programma Excel vienmēr izveidos relatīvā atsauce (t.i., atsauces ar n0 $ zīmi). Tas nozīmē, ka, ja es kopētu un ielīmētu formulu (vienu ar atsauci uz citu lapu) kādā citā šūnā, tā automātiski pielāgotu atsauci.
Šeit ir piemērs, kas izskaidro relatīvās atsauces.
Pieņemsim, ka pašreizējās lapas A1 šūnā izmantoju šādu formulu (lai atsauktos uz šūnu A1 lapas nosaukumā SalesData)
= Pārdošanas dati! A1
Tagad, ja es nokopēšu šo formulu un ielīmēšu šūnu A2, formula mainīsies uz:
= Pārdošanas dati! A1
Tas notiek tāpēc, ka formula ir relatīva, un, kad es to kopēju un ielīmēju, atsauces tiks automātiski pielāgotas.
Ja es vēlos, lai šī atsauce vienmēr atsauktos uz šūnu A1 SalesData lapā, man būs jāizmanto šāda formula:
= Pārdošanas dati! $ A $ 1
Dolāra zīme pirms rindas un kolonnas numura bloķē šīs atsauces, lai tās nemainītos.
Šeit ir detalizēta apmācība, kurā varat uzzināt vairāk par absolūtām, jauktajām un relatīvajām atsaucēm.
Tagad, kad esam apskatījuši, kā atsaukties uz citu lapu tajā pašā darbgrāmatā, redzēsim, kā mēs varam atsaukties uz citu darbgrāmatu.
Kā atsaukties uz citu darbgrāmatu programmā Excel
Atsaucoties uz šūnu vai diapazonu uz citu Excel darbgrāmatu, šīs atsauces formāts ir atkarīgs no tā, vai šī darbgrāmata ir atvērta vai aizvērta.
Un, protams, formāta noteikšanā ir nozīme arī darbgrāmatas nosaukumam un darblapām (atkarībā no tā, vai nosaukumā ir atstarpes vai bez alfabēta rakstzīmes).
Tātad, apskatīsim dažādus ārējo atsauču formātus uz citu darbgrāmatu dažādos scenārijos.
Ārēja atsauce uz atvērtu darbgrāmatu
Runājot par atsauci uz ārēju atvērtu darbgrāmatu, jums jānorāda darbgrāmatas nosaukums, darblapas nosaukums un šūnas/diapazona adrese.
Tālāk ir norādīts formāts, kas jāizmanto, atsaucoties uz ārēju atvērtu darbgrāmatu
= '[FileName] SheetName! CellAddress
Pieņemsim, ka jums ir darbgrāmata “ExampleFile.xlsx” un vēlaties atsaukties uz šīs darbgrāmatas lapas 1 šūnu A1.
Zemāk ir atsauce uz šo:
= [PiemērsFile.xlsx] Pārdošanas dati! A1
Ja ārējās darbgrāmatas nosaukumā vai lapas nosaukumā (vai abos) ir atstarpes, jums jāpievieno faila nosaukums (kvadrātiekavās) un lapas nosaukums ar pēdiņām.
Tālāk ir sniegti piemēri, kad jums vajadzētu būt vārdiem atsevišķās pēdiņās:
= '[Piemērs File.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Pārdošanas dati'! A1 = '[Piemērs File.xlsx] Pārdošanas dati'! A1
Kā automātiski izveidot atsauci uz citu darbgrāmatu
Atkal, lai gan ir labi zināt formātu, vislabāk to neievadīt manuāli.
Tā vietā vienkārši norādiet programmu Excel pareizajā virzienā, un tā jums izveidos šīs atsauces. Tas ir daudz ātrāk, jo ir daudz mazāk kļūdu.
Piemēram, ja jums ir daži dati šūnā A1: A10 darbgrāmatā ar nosaukumu “Piemēra fails” lapā ar nosaukumu “Pārdošanas dati” un vēlaties iegūt šo vērtību summu pašreizējā lapā, veiciet tālāk norādītās darbības. :
- Pašreizējā lapā (kur nepieciešams rezultāts) ierakstiet šādu formulu: = Sum (
- Dodieties uz darbgrāmatu “Faila piemērs”
- Atlasiet lapu “Pārdošanas dati”.
- Atlasiet diapazonu, kuru vēlaties pievienot (A1: A10). Tiklīdz jūs to izdarīsit, Excel automātiski izveidos atsauci uz šo diapazonu (to varat redzēt formulas joslā)
- Nospiediet ievadīšanas taustiņu.
Tas uzreiz izveidotu formulu ar pareizām atsaucēm.
Viena lieta, ko pamanīsit, veidojot atsauci uz ārēju darbgrāmatu, ir tā, ka tā vienmēr radīs absolūtas atsauces. Tas nozīmē, ka pirms rindu un kolonnu numuriem ir zīme $. Tas nozīmē, ka, kopējot un ielīmējot šo formulu citās šūnās, absolūtās atsauces dēļ tā turpinās atsaukties uz to pašu diapazonu.
Ja vēlaties to mainīt, atsauces ir jāmaina manuāli.
Ārēja atsauce uz slēgtu darbgrāmatu
Ja ir atvērta ārējā darbgrāmata un jūs atsaucaties uz šo darbgrāmatu, jums vienkārši jānorāda faila nosaukums, lapas nosaukums un šūnas/diapazona adrese.
Bet, kad tas ir aizvērts, programmai Excel nav ne jausmas, kur meklēt minētās šūnas/diapazonu.
Tāpēc, veidojot atsauci uz slēgtu darbgrāmatu, ir jānorāda arī faila ceļš.
Zemāk ir atsauce, kas atsaucas uz šūnu A1 darblapas Sheet1 darblapā. Tā kā šis fails nav atvērts, tas attiecas arī uz vietu, kur fails tiek saglabāts.
= 'C: \ Users \ sumit \ Desktop \ [Piemērs File.xlsx] Sheet1'! $ A $ 1
Iepriekš minētajā atsaucē ir šādas daļas:
- Faila ceļš - vieta jūsu sistēmā vai tīklā, kur atrodas ārējais fails
- Faila nosaukums - ārējās darbgrāmatas nosaukums. Tas ietver arī faila paplašinājumu.
- Lapas nosaukums - lapas nosaukums, kurā jūs atsaucaties uz šūnām/diapazoniem
- Šūnas/diapazona adrese - precīza šūnas/diapazona adrese, uz kuru jūs atsaucaties
Veidojot ārēju atsauci uz atvērtu darbgrāmatu un pēc tam aizverot darbgrāmatu, pamanīsit, ka atsauce tiek automātiski mainīta. Pēc ārējās darbgrāmatas aizvēršanas Excel automātiski ievieto atsauci arī uz faila ceļu.
Faila atrašanās vietas maiņas ietekme uz atsaucēm
Izveidojot atsauci uz šūnu/diapazonu ārējā Excel failā un pēc tam to aizverot, atsauce tagad izmanto arī faila ceļu.
Bet tad, ja maināt faila atrašanās vietu, jūsu darbgrāmatā (kurā veidojat atsauci) nekas nemainīsies. Bet, tā kā esat mainījis atrašanās vietu, saite tagad ir bojāta.
Tātad, aizverot un atverot šo darbgrāmatu, tā jums pateiks, ka saite ir bojāta, un jums ir vai nu jāatjaunina saite, vai arī tā ir pilnībā jāizjauc. Tas parādīs uzvedni, kā parādīts zemāk:
Noklikšķinot uz Atjaunināt, tiks parādīta cita uzvedne, kurā varēsit izvēlēties saišu rediģēšanas opcijas (kas parādīs zemāk redzamo dialoglodziņu)
Ja šie faili ir jāsaglabā saistīti, varat norādīt faila jauno atrašanās vietu, noklikšķinot uz Atjaunināt vērtības. Programma Excel atver jums dialoglodziņu, kurā varat norādīt jaunā faila atrašanās vietu, pārvietojoties tur un atlasot to.
Atsauce uz noteiktu vārdu (tajā pašā vai ārējā darbgrāmatā)
Ja jums ir jāatsaucas uz šūnām un diapazoniem, labāks veids ir izveidot definētus diapazonu nosaukumus.
Tas ir noderīgi, jo tas ļauj viegli atsaukties uz šiem diapazoniem, izmantojot nosaukumu, nevis garu un sarežģītu atsauces adresi.
Piemēram, ir vieglāk izmantot = SalesData, nevis = [Piemērs File.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10
Un, ja esat izmantojis šo definēto nosaukumu vairākās formulās un jums ir jāmaina atsauce, jums tas jādara tikai vienu reizi.
Tālāk ir norādītas darbības, lai izveidotu nosauktu diapazonu šūnu diapazonam.
- Atlasiet visas šūnas, kuras vēlaties iekļaut nosauktajā diapazonā
- Noklikšķiniet uz cilnes Formulas
- Noklikšķiniet uz opcijas Definēt vārdu (tā ir definēto vārdu grupā)
- Dialoglodziņā Jauns nosaukums piešķiriet šim diapazonam nosaukumu (šajā piemērā es izmantoju nosaukumu SalesData). Atcerieties, ka nosaukumā nedrīkst būt atstarpes
- Saglabājiet darbības jomu kā darbgrāmatu (ja vien jums nav nopietna iemesla to padarīt lapas līmeņa)
- Pārliecinieties, vai atsauces uz diapazonu ir pareizas.
- Noklikšķiniet uz Labi.
Tagad jūsu nosauktais diapazons ir izveidots, un jūs varat to izmantot šūnu atsauču vietā ar šūnu adresēm.
Piemēram, ja es vēlos iegūt visu šo SalesData diapazona šūnu summu, varat izmantot šādu formulu:
= SUM (pārdošanas dati)
Un ko darīt, ja vēlaties izmantot šo nosaukto diapazonu, ir citas darblapas vai pat citas darbgrāmatas?
Jūs varat!
Jums vienkārši jāievēro tas pats formāts, par kuru mēs runājām iepriekšējā sadaļā.
Nav nepieciešams atgriezties pie šī raksta sākuma. Ļaujiet man minēt visus piemērus šeit, lai jūs iegūtu ideju.
Darbgrāmatas Vs darblapas līmeņa nosaukti diapazoni: Izveidojot darbgrāmatas līmeni ar nosaukumu diapazons, ar šādu nosaukumu var būt tikai viens diapazons. Tātad, ja atsaucaties uz to no ārējām darbgrāmatām vai darblapām tajā pašā darbgrāmatā, jums nav jānorāda darblapas nosaukums (jo tas ir pieejams lietošanai visā darbgrāmatā). Ja izmantojat darblapas līmeņa nosaukumu diapazonu, to varēsit izmantot tikai darblapā, un, atsaucoties uz to, jums būs jānorāda arī darblapas nosaukums.Atsauce uz definēto nosaukumu tajā pašā darblapā vai darbgrāmatā
Ja esat izveidojis definēto nosaukumu darbgrāmatas līmenim, varat to izmantot jebkurā darbgrāmatas vietā, vienkārši izmantojot pašu definēto nosaukumu.
Piemēram, ja vēlos iegūt visu mūsu izveidotajā nosauktajā diapazonā esošo šūnu summu (SaledData), varu izmantot šādu formulu:
= SUM (SaledData)
Ja esat izveidojis darblapas līmeņa nosaukumu diapazons, varat izmantot šo formulu tikai tad, ja nosauktais diapazons ir izveidots tajā pašā lapā, kurā izmantojat formulu.
Ja vēlaties to izmantot citā lapā (teiksim Sheet2), jums jāizmanto šāda formula:
= SUM (1. lapa! $ A $ 1: $ A $ 10)
Un, ja lapas nosaukumā ir atstarpes vai burtciparu rakstzīmes, lapas nosaukums būs jāievieto atsevišķās pēdiņās.
= SUM ('1. lapa'! $ A $ 1: $ A $ 10)
Atsauce uz definēto nosaukumu citā darbgrāmatā (atvērta vai slēgta)
Ja vēlaties atsaukties uz nosauktu diapazonu citā darbgrāmatā, jums būs jānorāda darbgrāmatas nosaukums un pēc tam diapazona nosaukums.
Piemēram, ja jums ir Excel darbgrāmata ar nosaukumu ExampleFile.xlsx un nosaukts diapazons ar nosaukumu SalesData, varat izmantot zemāk esošo formulu, lai iegūtu šī diapazona summu no citas darbgrāmatas:
= SUM (ExampleFile.xlsx! SalesData)
Ja faila nosaukumā ir atstarpes, tās jāizmanto atsevišķās pēdiņās.
= SUM ('Piemērs File.xlsx'! SalesData)
Ja jums ir lapas līmeņa nosaukti diapazoni, jums ir jānorāda darbgrāmatas nosaukums, kā arī darblapa, atsaucoties uz to no ārējās darbgrāmatas.
Tālāk ir sniegts piemērs lapas līmeņa nosauktajam diapazonam:
= SUM ('[Piemērs File.xlsx] Sheet1'! SalesData)
Kā jau minēju iepriekš, vienmēr vislabāk ir izveidot darbgrāmatas līmeņa nosauktos diapazonus, ja vien jums nav nopietna iemesla izveidot pirmā līmeņa darblapu.
Ja slēgtā darbgrāmatā atsaucaties uz nosauktu diapazonu, jums būs jānorāda arī faila ceļš. Tālāk ir sniegts piemērs.
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Izveidojot atsauci uz nosauktu diapazonu atvērtā darbgrāmatā un pēc tam aizverot darbgrāmatu, programma Excel automātiski maina atsauci un pievieno faila ceļu.
Kā izveidot atsauci uz nosaukto diapazonu
Ja veidojat un strādājat ar daudziem nosauktajiem diapazoniem, nav iespējams atcerēties katra vārdu.
Excel jums palīdz, parādot visu izveidoto nosaukto diapazonu sarakstu, un ļauj tos ievietot formulās ar vienu klikšķi.
Pieņemsim, ka esat izveidojis nosauktu diapazonu SalesData, kuru vēlaties izmantot formulā, lai summētu visas nosauktā diapazona vērtības.
Lai to izdarītu, veiciet tālāk norādītās darbības.
- Atlasiet šūnu, kurā vēlaties ievadīt formulu.
- Ievadiet formulu līdz vietai, kur nepieciešams ievietot nosaukto diapazonu
- Nospiediet tastatūras taustiņu F3. Tiks atvērts dialoglodziņš Ielīmēt vārdu ar visu izveidoto vārdu sarakstu
- Veiciet dubultklikšķi uz nosaukuma, kuru vēlaties ievietot.
Iepriekš minētās darbības ievietotu nosaukumu formulā, un jūs varat turpināt strādāt pie formulas.
Piezīme. Šī metode darbojas tikai nosaukumu diapazoniem, kas atrodas tajā pašā darbgrāmatā. Ja vēlaties iegūt vārdus ārējā darbgrāmatā, jums būs jādodas uz turieni, jāpārbauda nosaukums, izmantojot dialoglodziņu Ielīmēt nosaukumu, un pēc tam jāatgriežas un jāizmanto tā formulā (pareizajā formātā, izmantojot faila nosaukumu).Tas ir viss, kas jums jāzina par to, kā atsaukties uz citām lapām vai darbgrāmatām un kā izveidot ārēju atsauci programmā Excel.
Ceru, ka šī apmācība jums šķita noderīga.
