Netiesioginė funkcija „Excel“
Netiesioginė „Excel“ funkcija yra integruota funkcija, naudojama nuorodoms gauti ir gauti langelio reikšmes iš teksto eilutės, ši formulė remiasi nuoroda iš nurodytos langelio, ji turi du argumentus, pirmasis argumentas nėra pasirenkamas, o antrasis - neprivalomas, nurodantis rungtynių tipą, šią funkciją galima naudoti ir su kita formule.
Netiesioginė „Excel“ funkcija turi kelis naudojimo būdus. Jis grąžina langelio nuorodą, nurodytą teksto eilute. Pvz., Jei langelyje B1 yra eilutė, tarkime, kad c4 ir c4 diapazonai turi vertę „Jessica“. Dabar, jei naudosime funkciją „NETIESIOGINIS“ ir persiųsime nuorodos tekstą kaip B1, ji grąžins langelio c4 reikšmę „Jessica“.
„Excel“ netiesioginė funkcija leidžia netiesiogiai nurodyti langelio adresą. Kaip galite aukščiau pateiktame NETIESIOGINIO „Excel“ pavyzdyje, jei langelyje B1 yra tekstas C4, ši netiesioginė „Excel“ formulė pateikia langelio C4 turinį ( Jessica ). Ši funkcija yra nepaprastai naudinga funkcija. Funkciją NETIESIOGINIS galite naudoti visada, kai norite pakeisti nuorodą į langelį netiesioginėje „Excel“ formulėje, nekeisdami pačios formulės.
NETIESIOGINĖ „Excel“ formulė
Paaiškinimas
„Ref_text“ yra nuoroda į langelį, kuriame yra A1 stiliaus nuoroda, R1C1 stiliaus nuoroda, pavadinimas apibrėžtas kaip nuoroda arba nuoroda į langelį kaip teksto eilutę.
a1 argumentas yra neprivalomas
| a1 | False arba 0, kai ref_text yra R1C1 stiliaus |
| a1 | Praleistas arba „True“ (1), kai „ref_text“ yra A1 stiliaus |
Kaip naudoti netiesioginę funkciją „Excel“?
Paimkime NETIESIOGINĮ „Excel“ pavyzdį prieš naudodami adreso funkciją „Excel“ darbaknygėje:
Tarkime, kad svetainėje www.xyz.com mes turime „Google“ analizės duomenis, skirtus svetainės lankytojams iš skirtingų auditorijos kanalų penkiose skirtingose šalyse, kaip parodyta lentelėje:
„Excel“ funkciją galime naudoti norėdami konvertuoti teksto eilutę į nuorodą, taigi nurodydami langelį H4 ir naudodami šį pavadinimą kaip nuorodą. Šiuo atveju funkcijos „INDIRECT“ naudojimas programoje „Excel“ yra nuoroda į vardų diapazonus, naudojant langelio reikšmes, todėl I4 langelyje mes tiesiog galėtume naudoti sumos funkciją, kad apskaičiuotume bendrą lankytojų iš Kanados šalies skaičių.
Dabar mes galime tiesiogiai apskaičiuoti lankytojų sumą iš kiekvienos šalies naudodami sumos funkciją, tačiau naudodami funkciją INDIRECT programoje excel, galime apskaičiuoti lankytojų iš skirtingų šalių sumą, pakeisdami vardo nuorodą, o ne pakeisdami pačią netiesioginę excel formulę.
Kiekvienam šalies lankytojui sukūrėme vardų diapazoną; tai būtų galima lengvai padaryti įvedus vardų diapazoną tiesiai po iškarpine, kaip parodyta žemiau.
Kitas metodas yra tai, kad galite pereiti į formules-> vardų tvarkytuvė -> pasirinkti diapazoną, kurį norite pavadinti -> spustelėkite vardų tvarkyklę -> įveskite pavadinimų diapazoną.
Panašiai jis sukūrė įvardytą asortimentą ir lankytojams iš kitų šalių.
Apskaičiuodami lankytojo iš Kanados sumą naudodamiesi INDIRECT funkcija ir naudodamiesi pavadinimų diapazonu H4 (Kanada, aukščiau pateiktas pavadinimas F3: F7), gauname bendrą lankytojo sumą.
Jei sukursime duomenų sąrašą naudodami šalių patvirtinimą ir pritaikysime šį sąrašą H4
Pakeitus skirtingų šalių pavadinimus iš Kanados į Indiją, JAV, Australiją ar Singapūrą, gauname bendrą lankytojo sumą I4, naudodamiesi „INDIRECT“ funkcija „Excel“.
Taigi kiekvienu atveju mes nekeičiame netiesioginės „Excel“ formulės, esančios I4 langelyje; keičiame vardo nuorodą H4 ir skaičiuojame kiekvienos šalies lankytojų sumą.
Mes galime žengti dar vieną žingsnį ir galime nurodyti darbalapio lygio pavadinimus, taip pat naudodami „INDIRECT“ funkciją „Excel“. Pavyzdžiui, dabar naudosime funkciją „NETIESIOGINIS“, nurodydami skirtingus darbaknygės lapus.
Mes sukūrėme skirtingus lapus su šalių pavadinimais, nurodydami bendrą pardavimo skaičių, atliktą kiekviename paieškos kanale skirtingoms šalims.
Indijai,
JAV,
Panašiai ir kitoms šalims.
Vėlgi, apskaičiuosime bendrą kiekvieno kanalo pardavimą naudodami „INDIRECT“ funkciją „Excel“ skirtingiems lapams su šalių pavadinimais kaip nuoroda.
Tokiu atveju, kadangi pardavimo duomenis turime skirtinguose lapuose, reikės susieti skirtingus elementus, kurie yra duomenų diapazonas, todėl „ref_text“ prasidės lapo pavadinimu, kuris šiuo atveju yra Indija B4, tada mes naudokite & operatorių, vadinamą ampersand, kuris naudojamas sujungimui.
Taigi gauname bendrą Indijos pardavimų sumą; dabar, jei pakeisime lapo šalies pavadinimą B4 iš Indijos į kitas šalis, tokias kaip JAV, Singapūras ar Kanada, gausime bendrą tų šalių pardavimo vertę.
Vėlgi, šiuo atveju mes tiesiog pakeitėme „ ref_text“ reikšmes langelyje B4, nekeisdami pagrindinės netiesioginės „Excel“ formulės, kuri yra C4, kad gautume skirtingų šalių, kurios yra skirtinguose lapuose, naudodamos funkciją INDIRECT, pardavimo sumą.
Dabar norėdami gauti praėjusio mėnesio pardavimo vertę, naudosime funkciją „NETIESIOGINIS“, naudodami nuorodą R1C1. Skirtingų mėnesių pardavimai B, C ir D stulpeliuose trims mėnesiams sausio, vasario ir kovo mėnesiams gali išaugti iki kitų mėnesių, tokių kaip balandis, gegužė, birželis ir pan. Taigi, pardavimo lentelė visada augs, priklausomai nuo artėjančio mėnesio pardavimo. Taigi šiame netiesioginiame „Excel“ pavyzdyje norime apskaičiuoti praėjusio mėnesio pardavimą.
Taigi, čia paskutinis mėnuo yra kovas, ir mes norime apskaičiuoti bendrą kovo mėnesio pardavimą, kuris yra 249 344 USD, naudodami funkciją NETIESIOGINIS.
Šiame netiesioginiame „Excel“ pavyzdyje rasime antrojo INDIRECT funkcijos argumento naudojimą
NETIESIOGINIS (ref_text, a1)
a1: kuris yra neprivalomas argumentas, tai yra loginė reikšmė (loginė reikšmė), nurodanti, kokio tipo nuoroda (ref_text) yra langelyje
jei a1 yra teisinga (1) arba praleista, ref_text laikomas A1 stiliaus nuoroda
jei a1 yra klaidingas (0), ref_text aiškinamas kaip R1C1 stilius
Kuo A1 stilius skiriasi nuo R1C1 stiliaus?
Paprastai netiesioginė „Excel“ funkcija naudoja A1 žymėjimą. Kiekvieną langelio adresą sudaro stulpelio raidė ir eilutės numeris. Tačiau netiesioginė „Excel“ funkcija taip pat palaiko R1C1 žymėjimą. Šioje sistemoje langelis A1 vadinamas ląstele R1C1, ląstelė A2 - R2C1 ir pan.
Norėdami pakeisti R1C1 žymėjimą, pasirinkite Failas-> parinktį, kad atidarytumėte „Excel“ parinkčių dialogo langą, spustelėkite skirtuką formulė ir pažymėkite R1C1 nuorodos stiliaus parinktį. Dabar pastebėsite, kad visos stulpelio raidės keičiamos į skaičius. Taip pat koreguojamos visos ląstelių nuorodos jūsų formulėse.
Taigi, kai „Excel“ funkcijoje netiesiogiai naudojame R1C1 stilių, a1 reikšmę perduodame kaip klaidingą, o jei naudosime stilių A1, tada vertę a1 perduodame tikra.
Dabar šiame netiesioginiame „Excel“ pavyzdyje mes naudosime stilių R1C1 dėl reikalavimo, nes stulpeliai su mėnesiu didės.
Naudojant stilių R1C1, visa pardavimo vertė yra 10 eilutėje, o paskutinė stulpelio vertė, esanti D stulpelyje, bus apskaičiuota naudojant funkciją COUNTA , kuri suteiks paskutinį stulpelio numerį, kuriame yra vertė. Taigi, skaičiuokite (10:10), tokiu atveju gausite 4, kurio stulpelio numeris R1C1 stiliaus. Taigi, mes susisiekėme su šia verte, kad gautume R10C4, kuriame yra norima vertė, kuri yra bendra praėjusio mėnesio pardavimo vertė (249 344 USD).
Kadangi mes čia naudojame R1C1 stilių, mes pateiksime klaidingą argumento a1 vertę.
Dabar pridėkime dar vieną stulpelį su balandžio pardavimo mėnesiu.
Taigi, gaukite naujas praėjusio mėnesio pardavimo vertes, atitinkančias balandžio paskutinio mėnesio vertes. Panašiai, jei pridėsime daugiau mėnesių, visada gausime naują praėjusio mėnesio pardavimo vertę, nekeisdami kiekvieno mėnesio netiesioginės „Excel“ formulės. Tai yra netiesioginės „Excel“ funkcijos specialybė.
Kaip pritaikyti lentelių masyvą funkcijoje NETIESIOGINIS „Excel VLOOKUP“?
Mes turime penkias šalis, paieškos kanalą ir pardavimą, kurį svetainė vykdė šiais skirtingais kanalais iš skirtingų šalių. Dabar, B3 langelyje, norime, kad pardavimas būtų atliktas atliekant natūralias paieškas iš Jungtinių Valstijų šalies.
Kiekvienai lentelei skirtingoms šalims mes pavadinome kiekvieną lentelę su trumpu šalies pavadinimu, Ind - Ind, JAV - JAV, Kanada - Can ir kt .
Jei mes tiesiogiai naudojame funkciją NETIESIOGINIS „Excel vlookup“, kad gautume vertę, kaip daroma toliau
Gauname klaidą, nes „INDIRECT Excel vlookup“ funkcija negali atpažinti perduoto lentelės masyvo kaip langelio B2 reikšmės, todėl naudosime „INDIRECT“ funkciją programoje „Excel“, kad „vlookup“ gautų table_array kaip galiojantį argumentą.
Jei pakeisime B1 ir B2 reikšmes, B3 gausime skirtingas pardavimo vertes, kaip reikalaujama.
Pastaba: vardų diapazonai neskiria didžiųjų ir mažųjų raidžių. Taigi - „Ind“, „IND“ ir „InD“ yra tas pats, kas „Excel“.
Kitame NETIESIOGINIO „Excel“ pavyzdyje mes naudosime funkciją NETIESIOGINIS, kad sukurtume priklausomą sąrašą. Mes turime darbuotojo pareigybių įmonėje sąrašą; tada kiekvienai pareigybei turime darbuotojų vardus.
Dabar langelyje I2 sukursime sąrašą skirtingų darbuotojų rangų kaip jų pozicijų, o I3 parodysime sąrašą, kuris priklausys nuo I2 vertės. Jei I2 yra režisierius, tada I3 turėtų atspindėti Andrew ir Michealo režisierių vardus; jei I2 yra vadybininkas, tada I3 turėtų atspindėti vadybininko, kuris yra Camille, David, Davis ir William, vardus ir taip pat vadovui.
Šiuo atveju, norėdami sukurti priklausomą sąrašą, vėl naudosime funkciją INDIRECT. Duomenų patvirtinimas, nustatymuose pasirinkdami patvirtinimo kriterijus kaip sąrašą ir šaltinio lauke, „Excel“ funkcijoje naudosime INDIRECT funkciją taip.
= NETIESIOGINIS ($ I $ 2)
Kai mes pasirenkame direktorių vardus I3, tai atspindi sąrašą su vardais Andrew ir Micheal, kai I2 yra vadybininkas, tada I3 atspindi vadybininko Camille, David, Davis ir William vardus ir panašiai kaip vadovui, kaip parodyta žemiau paveiksle.
Ką reikia atsiminti
- Argumentas „ref_text“ turi būti teisinga langelio nuoroda, kitaip INDIRECT grąžins #REF! Klaida. Ref_text argumentas gali nurodyti kitą darbaknygę.
- Jei ref_text nurodo kitą darbaknygę (išorinę nuorodą), kita darbaknygė turi būti atidaryta. Jei šaltinio darbaknygė nėra atidaryta, netiesioginis grąžina #REF! Klaidos vertė.
- Jei „ref_text“ nurodo langelių diapazoną, viršijantį 1 048 576 eilutės ribą arba 16 384 stulpelių ribą (XFD), „INDIRECT“ pateikia #REF! Klaida.
