Kad būtų galima nurodyti langelius ir diapazonus, bet kuris skaičiuoklės įrankis veikia. Ir „Excel“ yra geriausia ir galingiausia.
Šioje pamokoje aptarsiu viską, ką reikia žinoti apie tai, kaip nurodyti „Excel“ langelius ir diapazonus. Be pagrindinės nuorodos tame pačiame lape, didžioji šios pamokos dalis būtų apie kaip nurodyti kitą „Excel“ lapą ar darbaknygę.
Nors nėra daug skirtumų, kaip tai veikia, kai nurodote kitą to paties failo lapą arba nurodote visiškai atskirą „Excel“ failą, tos nuorodos formatas šiek tiek pasikeičia.
Be to, yra keletas svarbių dalykų, kuriuos reikia atsiminti, kai nurodote kitą lapą ar kitus išorinius failus.
Bet nerimauti … nieko per daug beprotiška!
Kai baigsite šią pamoką, žinosite viską, ką reikia žinoti apie langelių ir diapazonų nuorodą „Excel“ (būti toje pačioje darbaknygėje ar kitoje darbaknygėje).
Pradėkime!
Ląstelės nuoroda tame pačiame lape
Tai yra pats paprasčiausias nuorodos lygis, kai nurodote to paties lapo langelį.
Pavyzdžiui, jei esu B1 langelyje ir noriu kreiptis į A1 langelį, formatas būtų toks:
= A1
Kai tai padarysite, langelio, kuriame naudojate šią nuorodą, vertė bus tokia pati kaip A1 langelyje. Ir jei atliksite kokių nors A1 langelio pakeitimų, tai atsispindės langelyje, kuriame naudojote šią nuorodą.
Ląstelės nuoroda kitame lape
Jei toje pačioje darbaknygėje turite nurodyti kitą lapą, turite naudoti toliau nurodytą formatą.
Sheet_name! Cell_address
Pirma, turite lapo pavadinimą, po kurio yra šauktukas, po kurio eina langelio nuoroda.
Taigi, jei jums reikia nurodyti 1 lapo langelį A1, turite naudoti šią nuorodą:
= 1 lapas! A1
Ir jei norite nurodyti langelių diapazoną kitame lape, turite naudoti šį formatą:
Sheet_name! First_cell: Last_cell
Taigi, jei norite nurodyti diapazoną A1: C10 kitame tos pačios darbaknygės lape, turite naudoti toliau pateiktą nuorodą:
= 1 lapas! A1: C10
Atminkite, kad aš jums parodžiau tik nuorodą į langelį ar diapazoną. Tiesą sakant, jūs juos naudosite formulėse. Tačiau aukščiau paminėtų nuorodų formatas išliks tas pats
Daugeliu atvejų jūsų nurodytos darbalapio pavadinime būtų keli žodžiai. Pavyzdžiui, gali būti Projekto duomenys arba pardavimo duomenys.
Jei turite tarpus ar ne abėcėlinius simbolius (pvz., @,!, #, -ir tt), pavadinimą turite naudoti atskirose kabutėse.
Pavyzdžiui, jei norite nurodyti langelį A1 lape, pavadintame Pardavimo duomenys, naudosite toliau pateiktą nuorodą:
= „Pardavimo duomenys“! A1
Ir jei lapo pavadinimas yra Pardavimų duomenys, tada, norėdami nurodyti šio lapo langelį A1, turite naudoti toliau pateiktą nuorodą:
= „Pardavimų duomenys“! A1
Kai nurodote tos pačios darbaknygės lapą ir vėliau pakeičiate darbalapio pavadinimą, jums nereikia jaudintis, kad nuoroda sugenda. „Excel“ automatiškai atnaujins šias nuorodas už jus.
Nors puiku žinoti šių nuorodų formatą, praktiškai nėra taip gera kiekvieną kartą jas įvesti rankiniu būdu. Tai užimtų daug laiko ir būtų labai klaidinga.
Leiskite parodyti jums geresnį būdą, kaip sukurti langelių nuorodas „Excel“.
Automatinis nuorodos į kitą tos pačios darbo knygos lapą kūrimas
Daug geresnis būdas sukurti langelio nuorodą į kitą lapą yra tiesiog nukreipti „Excel“ į langelį/diapazoną, į kurį norite sukurti nuorodą, ir leisti „Excel“ ją sukurti pačiai.
Tai užtikrins, kad jums nereikės nerimauti, kad trūksta šauktuko ar kabučių ar atsiranda bet kokia kita formato problema. „Excel“ automatiškai sukurs jums tinkamą nuorodą.
Toliau pateikiami veiksmai, kaip automatiškai sukurti nuorodą į kitą lapą:
- Pasirinkite langelį dabartinėje darbaknygėje, kur jums reikia nuorodos
- Įveskite formulę, kol jums reikės nuorodos (arba lygybės ženklo, jei norite tik nuorodos)
- Pasirinkite lapą, į kurį turite kreiptis
- Pasirinkite langelį/diapazoną, kurį norite nurodyti
- Paspauskite „Enter“, kad gautumėte formulės rezultatą (arba tęskite darbą su formule)
Pirmiau minėti veiksmai automatiškai sukurs nuorodą į langelį/diapazoną kitame lape. Šias nuorodas taip pat galėsite pamatyti formulės juostoje. Baigę galite tiesiog paspausti įvesties klavišą ir jis duos jums rezultatą.
Pvz., Jei langelyje A1: A10 yra tam tikrų duomenų lape, pavadintame Pardavimų duomenys, ir norite gauti šių verčių sumą dabartiniame lape, atlikite šiuos veiksmus:
- Dabartiniame lape (kur jums reikia rezultato) įveskite šią formulę: = Sum (
- Pasirinkite lapą „Pardavimo duomenys“.
- Pasirinkite diapazoną, kurį norite pridėti (A1: A10). Kai tik tai padarysite, „Excel“ automatiškai sukurs nuorodą į šį diapazoną (tai galite pamatyti formulės juostoje)
- Paspauskite įvesties klavišą.
Kai kuriate ilgą formulę, gali reikėti nurodyti kito lapo langelį ar diapazoną, o tada grįžti prie kilmės lapo ir nurodyti tam tikrą langelį/diapazoną.
Kai tai padarysite, pastebėsite, kad „Excel“ automatiškai įterpia lapo nuorodą į lapą, kuriame turite formulę. Nors tai gerai ir nekenkia, to nereikia. Tokiu atveju galite pasirinkti palikti nuorodą arba pašalinti ją rankiniu būdu.
Kitas dalykas, kurį turite žinoti kurdami nuorodas pasirinkdami lapą ir tada langelį/diapazoną, yra tai, kad „Excel“ visada sukurs santykinė nuoroda (t. y. nuorodos su n0 $ ženklu). Tai reiškia, kad jei nukopijuoju ir įklijuoju formulę (vieną su nuoroda į kitą lapą) į kitą langelį, ji automatiškai pakoreguos nuorodą.
Štai pavyzdys, paaiškinantis santykines nuorodas.
Tarkime, kad dabartinio lapo A1 langelyje naudoju šią formulę (norėdami nurodyti langelį A1 langelio pavadinime „SalesData“)
= Pardavimo duomenys! A1
Dabar, jei nukopijuosiu šią formulę ir įklijuosiu į langelį A2, formulė pasikeis į:
= Pardavimo duomenys! A1
Taip atsitinka todėl, kad formulė yra santykinė ir kai ją nukopijuoju ir įklijuoju, nuorodos bus automatiškai pakoreguotos.
Jei noriu, kad ši nuoroda visada nurodytų langelį A1 „SalesData“ lape, turėsiu naudoti šią formulę:
= Pardavimo duomenys! $ A $ 1
Dolerio ženklas prieš eilutę ir stulpelio numerį užrakina šias nuorodas, kad jos nesikeistų.
Čia yra išsami pamoka, kurioje galite sužinoti daugiau apie absoliučias, mišrias ir santykines nuorodas.
Dabar, kai aptarėme, kaip pateikti nuorodą į kitą tos pačios darbo knygos lapą, pažiūrėkime, kaip galime kreiptis į kitą darbo knygą.
Kaip pateikti nuorodą į kitą „Excel“ darbaknygę
Kai langelį ar diapazoną nurodote kitoje „Excel“ darbaknygėje, šios nuorodos formatas priklausys nuo to, ar ta darbaknygė yra atidaryta, ar uždaryta.
Ir, žinoma, darbaknygės pavadinimas ir darbalapiai taip pat turi įtakos nustatant formatą (priklausomai nuo to, ar pavadinime yra tarpinių ar ne abėcėlės ženklų).
Taigi pažiūrėkime į skirtingus išorinių nuorodų į kitą darbo knygą formatus skirtingais atvejais.
Išorinė nuoroda į atvirą darbo knygą
Kalbant apie nuorodą į išorinę atvirą darbaknygę, turite nurodyti darbaknygės pavadinimą, darbalapio pavadinimą ir langelio/diapazono adresą.
Žemiau pateikiamas formatas, kurį turite naudoti nurodydami išorinę atvirą darbaknygę
= '[FileName] SheetName! CellAddress
Tarkime, kad turite darbaknygę „ExampleFile.xlsx“ ir norite kreiptis į šios darbo knygos 1 lapo langelį A1.
Žemiau yra nuoroda į tai:
= [PavyzdysFailas.xlsx] Pardavimo duomenys! A1
Jei išorinės darbaknygės pavadinime arba lapo pavadinime (arba abiejuose) yra tarpų, turite pridėti failo pavadinimą (laužtiniuose skliausteliuose) ir lapo pavadinimą viengubomis kabutėmis.
Žemiau pateikiami pavyzdžiai, kai vardai turėtų būti pateikiami pavienėmis kabutėmis:
= '[Pavyzdinis failas.xlsx] Pardavimo duomenys'! A1 = '[PavyzdysFailas.xlsx] Pardavimo duomenys'! A1 = '[Pavyzdinis failas.xlsx] Pardavimo duomenys'! A1
Kaip automatiškai sukurti nuorodą į kitą darbaknygę
Vėlgi, nors gerai žinoti formatą, geriausia jo nerašyti rankiniu būdu.
Vietoj to, tiesiog nukreipkite „Excel“ teisinga kryptimi ir jis sukurs šias nuorodas. Tai daug greičiau, nes klaidų tikimybė yra daug mažesnė.
Pvz., Jei turite tam tikrus duomenis langelio A1: A10 darbaknygėje, pavadintoje „Pavyzdinis failas“ lape, pavadintame „Pardavimo duomenys“, ir norite gauti šių verčių sumą dabartiniame lape, atlikite šiuos veiksmus :
- Dabartiniame lape (kur jums reikia rezultato) įveskite šią formulę: = Sum (
- Eikite į darbaknygę „Pavyzdinis failas“
- Pasirinkite lapą „Pardavimo duomenys“.
- Pasirinkite diapazoną, kurį norite pridėti (A1: A10). Kai tik tai padarysite, „Excel“ automatiškai sukurs nuorodą į šį diapazoną (tai galite pamatyti formulės juostoje)
- Paspauskite įvesties klavišą.
Tai iš karto sukurtų formulę su teisingomis nuorodomis.
Vienas dalykas, kurį pastebėtumėte kurdami nuorodą į išorinę darbo knygą, yra tai, kad ji visada sukurs absoliučias nuorodas. Tai reiškia, kad prieš eilutės ir stulpelio numerius yra $ ženklas. Tai reiškia, kad jei nukopijuojate ir įklijuojate šią formulę į kitus langelius, dėl visiškos nuorodos ji ir toliau nurodys tą patį diapazoną.
Jei norite tai pakeisti, nuorodas turite pakeisti rankiniu būdu.
Išorinė nuoroda į uždarą darbo knygą
Kai atidaroma išorinė darbaknygė ir jūs nurodote šią darbaknygę, jums tereikia nurodyti failo pavadinimą, lapo pavadinimą ir langelio/diapazono adresą.
Bet kai tai uždaryta, „Excel“ neturi supratimo, kur ieškote nurodytų langelių/diapazono.
Štai kodėl kurdami nuorodą į uždarą darbaknygę taip pat turite nurodyti failo kelią.
Žemiau pateikiama nuoroda, kuri nurodo A1 langelį darbalapio pavyzdinio failo Sheet1 darbalapyje. Kadangi šis failas nėra atidarytas, jis taip pat nurodo vietą, kurioje failas yra išsaugotas.
= 'C: \ Users \ sumit \ Desktop \ [Pavyzdinis failas.xlsx] Sheet1'! $ A $ 1
Aukščiau pateiktą nuorodą sudaro šios dalys:
- Failo kelias - vieta jūsų sistemoje ar tinkle, kur yra išorinis failas
- Failo pavadinimas - išorinės darbaknygės pavadinimas. Tai taip pat apimtų failo plėtinį.
- Lapo pavadinimas - lapo, kuriame nurodote langelius/diapazonus, pavadinimas
- Ląstelės/diapazono adresas - tikslus ląstelės/diapazono adresas, į kurį kreipiatės
Kai sukuriate išorinę nuorodą į atidarytą darbaknygę ir uždarote darbaknygę, pastebėsite, kad nuoroda automatiškai keičiasi. Uždarius išorinę darbaknygę, „Excel“ automatiškai įterpia nuorodą į failo kelią.
Failo vietos keitimo poveikis nuorodoms
Kai sukuriate nuorodą į langelį/diapazoną išoriniame „Excel“ faile ir uždarote, dabar taip pat naudojamas failo kelias.
Bet jei pakeisite failo vietą, jūsų darbo knygoje (kurioje sukuriate nuorodą) niekas nesikeis. Bet kadangi jūs pakeitėte vietą, nuoroda nutrūko.
Taigi, jei uždarysite ir atidarysite šią darbaknygę, ji jums pasakys, kad nuoroda neveikia, ir jūs turite arba atnaujinti nuorodą, arba visiškai ją nutraukti. Tai parodys raginimą, kaip parodyta žemiau:
Spustelėjus „Atnaujinti“, bus rodomas kitas raginimas, kuriame galėsite pasirinkti nuorodų redagavimo parinktis (kuris parodys žemiau esantį dialogo langą)
Jei norite, kad šie failai būtų susieti, galite nurodyti naują failo vietą spustelėdami Atnaujinti reikšmes. „Excel“ atidaro jums dialogo langą, kuriame galite nurodyti naują failo vietą naršydami ten ir pasirinkdami.
Nuoroda į apibrėžtą pavadinimą (toje pačioje arba išorinėje darbaknygėje)
Kai turite nurodyti langelius ir diapazonus, geresnis būdas yra sukurti apibrėžtus diapazonų pavadinimus.
Tai naudinga, nes tai leidžia lengvai nurodyti šiuos diapazonus naudojant pavadinimą, o ne ilgą ir sudėtingą nuorodos adresą.
Pavyzdžiui, lengviau naudoti = SalesData vietoj = [Pavyzdinis failas.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10
Ir jei jūs naudojote šį apibrėžimą, pavadintą keliose formulėse, ir jums reikia pakeisti nuorodą, tai turite padaryti tik vieną kartą.
Štai žingsniai, kaip sukurti pavadintą diapazoną ląstelių diapazonui:
- Pasirinkite visas langelius, kuriuos norite įtraukti į pavadintą diapazoną
- Spustelėkite skirtuką Formulės
- Spustelėkite parinktį Apibrėžti vardą (ji yra apibrėžtų vardų grupėje)
- Dialogo lange Naujas pavadinimas suteikite šiam diapazonui pavadinimą (šiame pavyzdyje naudoju pavadinimą „SalesData“). Atminkite, kad pavadinime negali būti tarpų
- Palikite apimtį kaip darbaknygę (nebent turite rimtų priežasčių ją padaryti lapo lygiu)
- Įsitikinkite, kad nuorodos į diapazoną yra teisingos.
- Spustelėkite Gerai.
Dabar jūsų pavadintas diapazonas buvo sukurtas ir galite jį naudoti vietoj ląstelių nuorodų su langelių adresais.
Pavyzdžiui, jei noriu gauti visų šių „SalesData“ diapazono langelių sumą, galite naudoti šią formulę:
= SUM (pardavimo duomenys)
O kas, jei norite naudoti šį pavadintą diapazoną, yra kiti darbalapiai ar net kitos darbaknygės?
Tu gali!
Jums tiesiog reikia laikytis to paties formato, kurį aptarėme aukščiau esančiame skyriuje.
Nereikia grįžti į šio straipsnio pradžią. Pateiksiu čia visus pavyzdžius, kad suprastumėte idėją.
Darbaknygės ir darbalapio lygių pavadinimų diapazonai: Kai sukuriate darbaknygės lygį pavadinimu diapazonas, tokiu pavadinimu gali būti tik vienas diapazonas. Taigi, jei nurodote jį iš išorinių darbaknygių ar toje pačioje darbaknygėje esančių darbalapių, jums nereikia nurodyti darbalapio pavadinimo (nes jį galima naudoti visoje darbaknygėje). Jei naudojate darbalapio lygio pavadintą diapazoną, jį galėsite naudoti tik darbalapyje ir, nurodydami jį, taip pat turėsite nurodyti darbalapio pavadinimą.Nuoroda į apibrėžtą pavadinimą tame pačiame darbalapyje arba darbaknygėje
Jei sukūrėte apibrėžtą darbaknygės lygio pavadinimą, galite jį naudoti bet kurioje darbaknygės vietoje, naudodamiesi pačiu apibrėžtu pavadinimu.
Pavyzdžiui, jei noriu gauti visų mūsų sukurto pavadinto diapazono langelių (SaledData) sumą, galiu naudoti šią formulę:
= SUM (SaledData)
Jei sukūrėte darbalapio lygį, pavadintą diapazonas, galite naudoti šią formulę tik tuo atveju, jei nurodytas diapazonas yra sukurtas tame pačiame lape, kuriame naudojate formulę.
Jei norite jį naudoti kitame lape (tarkime, Sheet2), turite naudoti šią formulę:
= SUMA (1 lapas! $ A $ 1: $ A $ 10)
Ir jei lapo pavadinime yra tarpų ar raidinių ir skaitinių simbolių, lapo pavadinimą turėsite rašyti pavienėmis kabutėmis.
= SUM („1 lapas“! $ A $ 1: $ A $ 10)
Nuoroda į apibrėžtą pavadinimą kitoje darbaknygėje (atidaryta arba uždaryta)
Jei norite nurodyti pavadintą diapazoną kitoje darbaknygėje, turėsite nurodyti darbaknygės pavadinimą ir tada diapazono pavadinimą.
Pvz., Jei turite „Excel“ darbaknygę pavadinimu „ExampleFile.xlsx“ ir pavadintą diapazoną su pavadinimu „SalesData“, galite naudoti toliau pateiktą formulę, kad gautumėte šio diapazono sumą iš kitos darbaknygės:
= SUM (ExampleFile.xlsx! SalesData)
Jei failo pavadinime yra tarpų, turite juos naudoti atskirose kabutėse.
= SUM ('Pavyzdinis failas.xlsx'! SalesData)
Jei turite lapo lygio pavadintus diapazonus, turite nurodyti darbaknygės pavadinimą ir darbalapį, kai nurodote jį iš išorinės darbaknygės.
Žemiau pateikiamas lapo lygio pavadinimo diapazono nuorodos pavyzdys:
= SUM ('[Pavyzdys File.xlsx] Sheet1'! SalesData)
Kaip jau minėjau aukščiau, visada geriausia sukurti pavadintus darbaknygių lygių diapazonus, nebent turite rimtų priežasčių sukurti vieno lygio darbalapio lygį.
Jei uždaroje darbaknygėje nurodote pavadintą diapazoną, turėsite nurodyti ir failo kelią. Žemiau yra to pavyzdys:
= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)
Kai atviroje darbaknygėje sukuriate nuorodą į pavadintą diapazoną ir uždarote darbaknygę, „Excel“ automatiškai pakeičia nuorodą ir prideda failo kelią.
Kaip sukurti nuorodą į pavadintą diapazoną
Jei kuriate ir dirbate su daugybe pavadintų diapazonų, neįmanoma prisiminti kiekvieno vardo.
„Excel“ jums padeda parodydama visų jūsų sukurtų pavadintų diapazonų sąrašą ir leidžia juos įterpti į formules vienu paspaudimu.
Tarkime, kad sukūrėte pavadintą diapazoną „SalesData“, kurį norite naudoti formulėje, kad SUMAUTumėte visas įvardyto diapazono reikšmes.
Štai žingsniai, kaip tai padaryti:
- Pasirinkite langelį, kuriame norite įvesti formulę.
- Įveskite formulę iki taško, kuriame reikia įterpti nurodytą diapazoną
- Paspauskite klaviatūros klavišą F3. Bus atidarytas dialogo langas Įklijuoti vardą su visų sukurtų pavadinimų sąrašu
- Dukart spustelėkite norimą įterpti pavadinimą.
Pirmiau minėti veiksmai įterptų pavadinimą į formulę ir galėtumėte toliau dirbti su formule.
Pastaba: ši technika veikia tik tų pavadinimų diapazonuose, kurie yra toje pačioje darbaknygėje. Jei norite gauti vardus išorinėje darbaknygėje, turėsite ten eiti, patikrinti pavadinimą naudodami dialogo langą Įklijuoti pavadinimą, tada grįžti ir naudoti jį savo formulėje (teisingu formatu, naudodami failo pavadinimą).Tai viskas, ką jums reikia žinoti apie tai, kaip nurodyti kitus lapus ar darbaknyges ir kaip sukurti išorinę nuorodą „Excel“.
Tikimės, kad ši pamoka jums buvo naudinga.
