V tem članku se bomo naučili, kako s funkcijo INDIRECT v Excelu pridobiti prirastek referenc celic.
Scenarij:
Pri delu z nabori podatkov na različnih listih moramo včasih iskati zaporedje vrednosti z drugega lista z uporabo funkcije INDIRECT. Na primer izvlečenje pomembnih informacij v različnih podatkovnih nizih iz različnih listov v glavni list. Za to morda uporabljate vrednost kopiranja in lepljenja iz podatkovnega lista na glavni list. Toda vrnjena vrednost se ne posodobi, ko se vrednost posodobi v podatkovnem listu. Spodnja razložena formula prihrani vaše ponavljajoče se delo, pri čemer vsak element po enega poberete z drugega lista.
Kako rešiti težavo?
Za to težavo bomo razmislili o tem, kako uporabljamo izbiranje elementov z istega lista, nato pa bomo s formulo izvlekli seznam vrednosti iz različnih listov. Excel INDIRECT & CELL funkcija bo uporabljena za izdelavo formule, kot je opisano spodaj.
Splošna formula:
| = INDIRECT ("ime_ lista" & "!" & CELL ("naslov", cell_ref) |
list_name: ime lista
cell_ref: sklic na celico brez imena lista.
Primer:
Vse to je lahko zmedeno za razumevanje. Vzemimo primer, da bi razumeli, kako s formulo izvleči niz vrednosti iz različnih listov. Tukaj imamo delovni list z vrednostmi na različnih listih. S formulo moramo izvleči seznam vrednosti
Uporabite formulo:
| = NEPOSREDNO ($ C $ 3 & "!" & CELL ("naslov", B1)) |
Pojasnilo:
- Ko izvlečete vrednost z drugega lista. Excel sprejema sintakso, ki je Sheet1! B1. (Tu je Sheet1 ime lista in B1 referenca celice).
- Funkcija CELL vrne sklic na celico in poda sklic na funkcijo INDIRECT.
- & operator se pridruži ali združi dva ali več argumentov.
- INDIRECT funkcija vrne vrednost v podanem sklicu celice.
Tu je B1 sklic na celico istega lista, vendar je vrednost izvlečena iz podatkovnega lista.
Vrednost v celici podatkovnega lista B1 je 5800. Kopirajte formulo, da dobite seznam vrednosti, ki se začnejo od vrednosti, pridobljene iz prvega rezultata.
Tu imamo seznam vrednosti, ki se začnejo od celice B1 do B7 v podatkovnem listu.
Excel povečuje sklic na celico z drugega lista z uporabo sklica na celico.
Vemo, kako iz istega lista dobiti prirast referenčne celice. Uporabimo formulo za izvlečenje vrednosti iz istega lista. Splošna formula je = cell_ref.
Ta formula naredi tako, da prilepi vrednost iz naslova, ki je referenca celice. Ko se v formuli uporablja sklic na celico. Uporablja se lahko za različna opravila, vrednost pa se posodablja, ko se vrednost posodablja iz celice. Razširite formulo na spodnje celice, da dobite seznam vrednosti.
Kot lahko vidite tukaj, imamo zahtevano zbirko. Zdaj moramo te vrednosti prenesti na glavni list. Excel sprejme skladnjo pri ekstrahiranju vrednosti z drugega lista, ki je = Sklic! A1. (Tukaj je referenca ime lista, A1 pa referenca celice).
Kopirajte formulo v preostale celice s kombinacijo tipk Ctrl + D ali povlecite navzdol z desnega dna celice.
Kot lahko vidite, imamo tukaj vse zahtevane vrednosti iz referenčnega lista. Obstaja še en način za pridobivanje vrednosti iz različnih listov.
Izvlecite seznam vrednosti iz različnih listov z uporabo imenovanega obsega.
Z metodo lahko pokličemo celotno matriko neposredno z imenovanim obsegom z različnih listov. Za to bomo samo izbrali matriko ali tabelo in izbrani del poimenovali v polju z imenom in ga poklicali po imenu, kjer koli je potrebno na delovnem listu.
Tu smo izbrali celotno tabelo in jo poimenovali Podatki. Polje z imenom je v zgornjem levem kotu lista, kot je označeno na zgornjem listu.
Imenovano tabelo bomo izvlekli iz novega lista. Pojdite na nov list in izberite enako število celic ter vnesite spodnjo formulo.
Uporabite formulo:
| { = Podatki } |
Opomba: uporabite Ctrl + Shift + Enter namesto Enter v formulo, ki deluje za matrike. Ne uporabljajte ročno postavljenih naramnic.
Kot lahko vidimo, formula deluje dobro. Vsako od teh metod je mogoče uporabiti povsod, kjer je to potrebno.
Tu so vse opombe glede uporabe formule.
Opombe:
- Te formule izvlečejo samo vrednosti, ne pa tudi oblike. Enako oblikovanje lahko uporabite z orodjem za slikanje oblik na različnih listih.
- Vrednost se posodobi, ko spremenimo vrednost v sklicu na celico.
- Vrednosti znotraj formule lahko uredimo z različnimi operatorji, kot je matematični operater za številke, operator "&" za besedilne vrednosti.
Upam, da je ta članek o tem, kako z uporabo funkcije INDIRECT doseči prirastek referenčne celice, razložljiv. Več člankov o pridobivanju vrednosti s formulami najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte
Vse o imenovanih obsegih programa Excel: Excelovi obsegi, označeni z imeni, so enostavni za uporabo v formulah Excel. Vse o tem izveš tukaj.
Polje z imenom v Excelu : Excelovo polje z imenom ni nič drugega kot majhno območje prikaza zgoraj levo na Excelovem listu, ki prikazuje ime aktivne celice ali obsege v Excelu. Za sklice lahko celico ali matriko preimenujete.
Kako pridobiti ime lista delovnega lista v Excelu : Funkcija CELL v Excelu vam posreduje informacije o katerem koli delovnem listu, kot so stolpec, vsebina, ime datoteke … itd. Tukaj se naučite, kako dobiti ime lista s funkcijo CELL.
Kako dobiti zaporedno številko vrstice v Excelu: Včasih moramo v tabelo dobiti zaporedno številko vrstice, lahko je to za serijsko številko ali karkoli drugega. V tem članku se bomo naučili, kako oštevilčiti vrstice v Excelu od začetka podatkov.
Povečajte število v besedilnem nizu v Excelu: Če imate velik seznam elementov in morate povečati zadnjo številko besedila starega besedila v Excelu, boste potrebovali pomoč dveh funkcij BESEDILO in DESNO.
Priljubljeni članki:
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.