Kaudne funktsioon Excelis
Kaudne funktsioon Excelis on sisseehitatud funktsioon, mida kasutatakse lahtri väärtuste viitamiseks ja hankimiseks tekstistringist, see valem võtab viite viidatud lahtrilt, sellel on kaks argumenti, esimene argument pole valikuline, teine argument on valikuline, mis viitab vaste tüübi järgi saab seda funktsiooni kasutada ka mõne muu valemiga.
KAUDSE funktsiooniga Excelis on mitu kasutust. See tagastab tekstistringiga määratud lahtriviite. Näiteks kui lahter B1 sisaldab stringi, siis oletame, et vahemikel c4 ja c4 on väärtus „Jessica”. Kui nüüd kasutame funktsiooni INDIRECT ja edastame viiteteksti kui B1, tagastab see lahtrivahemiku c4 väärtuse, mis on „Jessica”.
Exceli funktsioon INDIRECT võimaldab teil lahtriaadressi määratleda KOHE. Nagu ülaltoodud KAUDSE Exceli näites saate, kui lahter B1 sisaldab teksti C4, tagastab see kaudne Exceli valem lahtri C4 sisu ( Jessica ). See funktsioon on äärmiselt kasulik funktsioon. Funktsiooni Kaudne saab kasutada alati, kui soovite muuta kaudse exceli valemi lahtrisse viitamist ilma valemit ennast muutmata.
KAUDSE Exceli valem
Selgitus
Ref_text on viide lahtrile, mis sisaldab A1-stiilis viidet, R1C1-stiilis viidet, viitena määratletud nime või viidet lahtrile tekstistringina.
a1 argument on valikuline
| a1 | Väär või 0, kui ref_text on stiilis R1C1 |
| a1 | Välja jäetud või tõene (1), kui ref_text on stiilis A1 |
Kuidas kasutada funktsiooni INDIRECT Excelis?
Võtame enne Exceli töövihiku aadressifunktsiooni kasutamist INDIRECT exceli näite:
Oletame, et veebisaidi www.xyz.com jaoks on meil Google'i analüüsiandmed veebisaidi külastajate jaoks erinevast vaatajaskanalist viie erineva riigi jaoks, nagu on näidatud allpool tabelis:
Funktsiooni INDIRECT saab Excelis kasutada tekstistringi viiteks teisendamiseks, viidates lahtrile H4 ja kasutades seda nime viitena. Funktsiooni INDIRECT kasutamine Excelis on sel juhul viidatud nimevahemikele, kasutades lahtri väärtusi, nii et lahtris I4 võiksime Kanadast pärit külastajate koguarvu arvutamiseks kasutada lihtsalt funktsiooni summa.
Nüüd saame summeerimisfunktsiooni abil otse arvutada külastajate summa igast riigist, kuid funktsiooni INDIRECT abil excelis saame arvutada erinevatest riikidest pärit külastajate summa, muutes nimeviidet, mitte muutes kaudset exceli valemit.
Oleme loonud igale riigikülastajale nimevahemiku; seda saab hõlpsasti kirjutada nimevahemik vahetult lõikepuhvri alla olevasse ruumi, nagu allpool näidatud.
Teine meetod on see, et võite minna valemitesse -> nimehaldur -> valige vahemik, millele soovite nime anda -> klõpsake nimehalduril -> tippige nimevahemik.
Samamoodi lõi see Excelis nimelise vahemiku ka teistest riikidest pärit külastajatele.
Arvutades I4-s, arvutades külastaja summa riigist Kanada, kasutades funktsiooni INDIRECT ja kasutades nimevahemikku H4-s (Kanada, ülalnimetus F3 jaoks: F7), saame külastaja koguarvu.
Kui loome riikide andmete kontrollimisega andmete loendi ja rakendame selle loendi H4-le
Ja muutes erinevad riiginimed Kanadast India, USA, Austraalia või Singapurini, saame Exceli funktsiooni INDIRECT abil külastaja summa I4-s.
Seega ei muuda me iga juhtumi puhul kaudse exceli valemit, mis on lahtris I4; muudame nimeviidet H4-s ja arvutame iga riigi külastajate summa.
Saame liikuda ühe sammu edasi ja viidata töölehe taseme nimedele, kasutades ka Excelis funktsiooni INDIRECT. Näiteks kasutame nüüd funktsiooni INDIRECT, viidates töövihiku erinevatele lehtedele.
Oleme loonud erinevad lehed, millel on riikide nimed, kõigi otsingukanalite kaupade müügiarvude kohta.
India jaoks
USA jaoks
Samamoodi ka teiste riikide puhul.
Jällegi arvutame iga kanali kaudu tehtud kogumüügi, kasutades Exceli funktsiooni INDIRECT erinevatele lehtedele, kus on viidatud riikide nimed.
Sellisel juhul, kuna meil on müügiandmeid erinevates lehtedes, peame liitma erinevad elemendid, mis on andmevahemik, nii et ref_text algab lehe nimega, mis on B4 puhul antud juhul India, siis kasutage liitmiseks kasutatavat & operaatorit ampersand.
Niisiis, saame India müügi kogusumma; nüüd, kui muudame lehe riigi nime B4-s Indiast teistele riikidele nagu USA, Singapur või Kanada, saame nende riikide kogu müügiväärtuse.
Jällegi oleme sel juhul lihtsalt muutnud lahtris B4 ref_text väärtusi, muutmata peamist kaudset exceli valemit, mis on lahtris C4, et saada erinevate riikide lehtede müügisumma funktsiooni INDIRECT abil.
Nüüd kasutame eelmise kuu müügiväärtuse saamiseks funktsiooni INDIRECT, kasutades viidet R1C1. Erinevate kuude müük on veergudes B, C ja D kolmeks kuuks jaanuariks, veebruariks ja märtsiks, mis võib kasvada järgmisteks kuudeks nagu apr, mai, juuni jne. Niisiis, müügitabel kasvab alati, sõltuvalt eelseisva kuu müügi lisamisest. Seega tahame selles kaudses Exceli näites arvutada viimase kuu müügi.
Nii et siin on viimane kuu märts ja tahame funktsiooni INDIRECT abil välja arvutada märtsi kuu kogu müügi, mis on 249 344 dollarit.
Selles INDIRECT Exceli näites leiame funktsiooni INDIRECT teise argumendi kasutamise
INDIRECT (ref_text, a1)
a1: mis on valikuline argument, see on loogiline väärtus (Boole'i väärtus), mis määrab, millist tüüpi viide (ref_text) lahtris sisaldub
kui a1 on tõene (1) või välja jäetud, loetakse ref_text A1-stiilis viiteks
kui a1 on vale (0), tõlgendatakse ref_text R1C1 stiilina
Mille poolest erineb A1 stiil R1C1 stiilist?
Tavaliselt kasutab Exceli funktsioon INDIRECT A1 tähistust. Iga lahtriaadress koosneb veeru tähest ja rea numbrist. Exceli funktsioon INDIRECT toetab aga ka R1C1 tähistamist. Selles süsteemis nimetatakse lahtrit A1 lahtriks R1C1, lahtrit A2 kui R2C1 jne.
R1C1 märgistusele üleminekuks valige Exceli suvandite dialoogiboksi avamiseks Fail-> suvand, klõpsake vahekaarti Valem ja valikut R1C1 viide stiil. Nüüd märkate, et veergude tähed muutuvad kõik numbriteks. Ja kõik teie valemite lahtriviited kohanduvad ka.
Niisiis, kui kasutame excelis funktsioonis INDIRECT R1C1 stiili, edastame a1 väärtuse valeks ja kui kasutame stiili A1, siis edastame väärtuse a1 tõeseks.
Nüüd kasutame selles kaudses Exceli näites nõude tõttu stiili R1C1, kuna kuu veerud suurenevad.
Kasutades stiili R1C1, on kogu müügiväärtus 10. real ja veeru D viimane veeru väärtus arvutatakse funktsiooni COUNTA abil, mis annab viimase väärtusega veeru numbri. Niisiis, arv (10:10) annab sel juhul 4, mille veeru number on R1C1 stiilis. Niisiis, oleme selle väärtusega ühendust võtnud, et saada R10C4, mis sisaldab soovitud väärtust, mis on eelmise kuu kogu müügiväärtus (249 344 dollarit).
Ja kuna me kasutame siin stiili R1C1, edastame argumendile a1 valeväärtuse.
Lisagem nüüd veel üks veerg aprilli müügi kuuga.
Nii saate uue eelmise kuu müügiväärtused, mis vastavad aprilli viimase kuu omadele. Samamoodi, kui lisame veel mitu kuud, saame alati uue eelmise kuu müügiväärtuse, muutmata iga kuu kaudset exceli valemit. See on kaudse funktsiooni eripära excelis.
Kuidas funktsiooni INDIRECT Excel VLOOKUP tabelimassiivi kohandada?
Meil on viis riiki ja otsingukanal ning müük, mille veebisait tegi nende erinevate kanalite kaudu erinevatest riikidest. Nüüd tahame lahtris B3 saada Ameerika Ühendriikide orgaaniliste otsingute kaudu tehtud müüki.
Erinevate riikide jaoks mõeldud iga tabeli jaoks oleme iga tabeli nimetanud riigi lühinimega, Ind Ind, USA osariigid, Kanada Kanada jne .
Kui me kasutame väärtuse saamiseks otse funktsiooni INDIRECT Excel vlookup, nagu allpool kirjeldatud
Saame vea, kuna funktsioon INDIRECT Excel vlookup ei suuda tuvastada lahtri B2 väärtusena edastatud tabeli massiivi, seega kasutame funktsiooni INDIRECT Excelis, et muuta vlookup tabeli_array kehtivaks argumendiks.
Kui muudame B1 ja B2 väärtusi, saame vastavalt B3-le erinevad müügi väärtused.
Märkus. Nimevahemikud ei ole tõstutundlikud. Seega - Ind, IND ja InD on kõik Exceliga samad.
Järgmises INDIRECT Exceli näites kasutame sõltumatu loendi loomiseks funktsiooni INDIRECT Excelis. Meil on nimekiri töötaja ametikohast ettevõttes; siis on meil iga ametikoha jaoks töötajate nimed.
Nüüd loome lahtris I2 loendi töötajate erinevatele ametikohtadele nende ametikohtadena ja lahtris I3 loendi, mis sõltub I2 väärtusest. Kui I2 on režissöör, siis peaks I3 kajastama režissööride nimesid, mida Andrew ja Micheal; kui I2 on juhataja, siis peaks I3 kajastama juhi nimesid, kes on Camille, David, Davis ja William ning samamoodi juhendaja jaoks.
Sel juhul kasutame jällegi sõltuva loendi loomiseks funktsiooni INDIRECT Excelis. Andmete valideerimine, loendis valideerimiskriteeriumite valimisel ja allikaväljal kasutame Excelis funktsiooni INDIRECT järgmiselt.
= KOHE ($ I $ 2)
Kui valime I3-s direktorinimed, peegeldub loend nimedega Andrew ja Micheal, kui I2 on juhataja, siis I3 peegeldab juhi Camille, Davidi, Davise ja Williami nimesid ning sarnaselt juhendajale, nagu allpool joonisel näidatud.
Asjad, mida meeles pidada
- Argument ref_text peab olema kehtiv lahtriviide, vastasel juhul tagastab INDIRECT #REF! Viga. Argument ref_text võib viidata mõnele teisele töövihikule.
- Kui ref_text viitab mõnele teisele töövihikule (välisele viitele), peab teine töövihik olema avatud. Kui allika töövihik pole avatud, tagastab INDIRECT #REF! Vea väärtus.
- Kui ref_text viitab lahtrivahemikule, mis jääb väljapoole rea limiiti 1 048 576 või veerusimiiti 16 384 (XFD), tagastab INDIRECT #REF! Viga.
