INDIREKTNA funkcija u Excelu
Neizravna funkcija u Excelu je ugrađena funkcija koja se koristi za referenciranje i dobivanje vrijednosti ćelija iz tekstualnog niza, ova formula uzima referencu iz navedene ćelije, ima dva argumenta, prvi argument nije obvezan, dok drugi argument nije obavezan koji se odnosi na vrsta podudaranja, ova se funkcija može koristiti i s drugom formulom.
INDIRECT funkcija u excelu ima više načina korištenja. Vraća referencu na ćeliju navedenu u tekstualnom nizu. Na primjer, ako ćelija B1 sadrži niz, recimo da raspon c4 i c4 ima vrijednost "Jessica". Sada, ako ćemo upotrijebiti funkciju INDIRECT i proslijediti referentni tekst kao B1, vratit će vrijednost koju sadrži raspon ćelija c4 koja je "Jessica".
Excelova funkcija INDIRECT omogućuje vam da indirektno odredite adresu stanice. Kao što možete u gornjem primjeru INDIREKTNOG Excela, ako ćelija B1 sadrži tekst C4, ova neizravna excel formula vraća sadržaj ćelije C4 ( Jessica ). Ova je funkcija izuzetno korisna funkcija. Funkciju INDIRECT možete koristiti kad god želite promijeniti referencu na ćeliju unutar neizravne excel formule bez promjene same formule.
INDIREKTNA Excel formula
Obrazloženje
Ref_text je referenca na ćeliju koja sadrži referencu u stilu A1, referencu u stilu R1C1, naziv definiran kao referenca ili referenca na ćeliju kao tekstualni niz.
Argument a1 nije obvezan
| a1 | Lažno ili 0, kada je ref_text u stilu R1C1 |
| a1 | Izostavljeno ili True (1) kada je ref_text u stilu A1 |
Kako koristiti neizravnu funkciju u programu Excel?
Uzmimo INDIRECT excel primjer prije korištenja funkcije adrese u Excelovoj radnoj knjizi:
Pretpostavimo da za web mjesto www.xyz.com imamo podatke Google analitike za posjetitelje web mjesta iz različitih kanala publike za pet različitih zemalja kao što je prikazano dolje u tablici:
Funkciju INDIRECT u Excelu možemo koristiti za pretvaranje tekstualnog niza u referencu, pozivajući se tako na ćeliju H4 i koristeći to ime kao referencu. Upotreba funkcije INDIRECT u Excelu, u ovom je slučaju referenciranje raspona imena pomoću vrijednosti ćelije, tako da bismo u ćeliji I4 mogli jednostavno koristiti funkciju zbroja za izračunavanje ukupnog broja posjetitelja iz zemlje Kanade.
Sada možemo izravno izračunati zbroj posjetitelja iz svake zemlje pomoću funkcije zbroja, ali pomoću funkcije INDIRECT u excelu možemo izračunati zbroj posjetitelja iz različitih zemalja mijenjajući referencu imena, a ne mijenjajući samu formulu neizravnog excela.
Stvorili smo raspon imena za svakog posjetitelja u zemlji; to se može učiniti jednostavno unošenjem raspona imena u prostor neposredno ispod međuspremnika, kao što je prikazano dolje.
Druga metoda je da odete na formule-> upravitelj imena -> odaberite raspon koji želite imenovati -> kliknite upravitelj imena -> upišite raspon imena.
Slično tome, stvorio je imenovani asortiman u Excelu i za posjetitelje iz drugih zemalja.
Sada, u I4, izračunavanjem zbroja posjetitelja iz zemlje Kanade pomoću funkcije INDIRECT i korištenjem raspona imena u H4 (Kanada, gornji naziv za F3: F7), dobivamo ukupan zbroj posjetitelja.
Ako izradimo popis podataka pomoću provjere valjanosti podataka za zemlje i primijenimo taj popis na H4
I mijenjajući različita imena država iz Kanade u Indiju, SAD, Australiju ili Singapur, dobivamo ukupan zbroj posjetitelja u I4 pomoću funkcije INDIREKT u Excelu.
Dakle, za svaki slučaj ne mijenjamo neizravnu formulu excel koja se nalazi u ćeliji I4; mijenjamo referencu imena u H4 i izračunavamo zbroj posjetitelja za svaku zemlju.
Možemo se pomaknuti korak dalje i možemo se pozvati na nazive na razini radnog lista, također koristeći funkciju INDIRECT u Excelu. Na primjer, sada ćemo koristiti funkciju INDIRECT u odnosu na različite listove u radnoj knjizi.
Izradili smo različite listove s imenima država za ukupan broj prodaja ostvarenih sa svakim kanalom pretraživanja za različite zemlje.
Za Indiju,
Za SAD,
Slično tome, i za druge zemlje.
Opet ćemo izračunati ukupnu prodaju ostvarenu putem svakog kanala koristeći INDIRECT funkciju u Excelu za različite listove s nazivima država kao referencom.
U ovom slučaju, budući da podatke o prodaji imamo u različitim listovima, morat ćemo spojiti različite elemente koji su raspon podataka, pa će ref_text započeti s imenom lista, što je u ovom slučaju u B4 Indija, tada ćemo koristite operator & nazvan ampersand koji se koristi za spajanje.
Dakle, dobivamo ukupan zbroj prodaje za Indiju; sada, ako promijenimo naziv države lista u B4 iz Indije u druge zemlje poput SAD-a, Singapura ili Kanade, dobit ćemo ukupnu prodajnu vrijednost tih zemalja.
Opet, u ovom smo slučaju jednostavno promijenili vrijednosti ref_text u ćeliji B4, a da nismo promijenili glavnu neizravnu excel formulu, koja je u C4, kako bismo dobili zbroj prodaje za različite zemlje koje se nalaze u različitim listovima pomoću funkcije INDIRECT.
Sada ćemo upotrijebiti funkciju INDIRECT koristeći referencu R1C1 da bismo dobili prodajnu vrijednost prošlog mjeseca. Imamo prodaju za različite mjesece u stupcima B, C i D za tri mjeseca siječnja, veljače i ožujka koja mogu narasti i na više mjeseci poput travnja, svibnja, lipnja itd. Dakle, tablica prodaje će uvijek rasti, ovisno o dodatku nadolazeće mjesečne prodaje. Dakle, u ovom neizravnom primjeru Excela želimo izračunati prodaju posljednjeg mjeseca.
Dakle, ovdje je zadnji mjesec ožujak, a mi želimo izračunati ukupnu prodaju mjeseca ožujka, koja iznosi 249.344 USD, pomoću funkcije INDIREKT.
U ovom primjeru INDIRECT Excela pronaći ćemo upotrebu drugog argumenta funkcije INDIRECT
INDIRECT (ref_text, a1)
a1: što je neobavezni argument, to je logička vrijednost (logička vrijednost) koja određuje koji je tip reference (ref_text) sadržan u ćeliji
ako je a1 tačno (1) ili izostavljeno, ref_text se smatra referencom u stilu A1
ako je a1 netočno (0), ref_text se tumači kao stil R1C1
Po čemu se stil A1 razlikuje od stila R1C1?
Uobičajeno, funkcija INDIREKT u programu Excel koristi oznaku A1. Svaka adresa ćelije sastoji se od slova stupca i broja retka. Međutim, funkcija INDIRECT u programu Excel također podržava R1C1 notaciju. U ovom se sustavu ćelija A1 naziva ćelijom R1C1, ćelija A2 kao R2C1 itd.
Da biste prešli na R1C1 notaciju, odaberite Datoteka-> mogućnost da biste otvorili dijaloški okvir excel opcija, kliknite karticu formule i kvačicu u opciju R1C1 referentni stil. Sad ćete primijetiti da se slova u stupcu mijenjaju u brojeve. I sve reference na ćelije u vašim formulama također se prilagođavaju.
Dakle, kada koristimo stil R1C1 u INDIRECT funkciji u excelu, vrijednost a1 prosljeđujemo kao lažnu, a ako koristimo A1-stil, vrijednost vrijednost true prosljeđujemo u a1.
Sada ćemo u ovom neizravnom primjeru Excela koristiti stil R1C1 zbog zahtjeva jer će stupci s mjesecom rasti.
Upotrebom stila R1C1, ukupna vrijednost prodaje nalazi se u retku 10, a zadnja vrijednost stupca, koja se nalazi u stupcu D, izračunavat će se pomoću funkcije COUNTA , koja će dati zadnji broj stupca koji sadrži vrijednost. Dakle, brojanje (10:10) u ovom će slučaju dati 4, što je broj stupca u stilu R1C1. Dakle, kontaktirali smo tu vrijednost kako bismo dobili R10C4 koji sadrži željenu vrijednost koja je ukupna prodajna vrijednost prošlog mjeseca (249.344 USD).
A, budući da ovdje koristimo stil R1C1, proslijedit ćemo lažnu vrijednost za argument a1.
Sad, dodajmo još jedan stupac s mjesecom travanjskom rasprodajom.
Dakle, nabavite nove vrijednosti prodaje prošlog mjeseca koje odgovaraju vrijednosti iz posljednjeg mjeseca travnja. Slično tome, ako dodamo još mjeseci, uvijek ćemo dobiti novu prošlomjesečnu prodajnu vrijednost bez mijenjanja neizravne formule excel za svaki mjesec. Ovo je posebnost INDIRECT funkcije u excelu.
Kako prilagoditi niz tablice u funkciji INDIRECT Excel VLOOKUP?
Imamo pet zemalja i kanal za pretraživanje i prodaju koju je web stranica izvršila putem različitih kanala iz različitih zemalja. Sada, u ćeliji B3, želimo ostvariti prodaju putem organskih pretraživanja iz zemlje Sjedinjene Države.
Za svaku tablicu za različite zemlje imenovali smo svaku tablicu kratkim nazivom za zemlju, Ind za Indiju, države za SAD, Can za Kanadu i tako dalje .
Ako izravno koristimo funkciju INDIRECT Excel vlookup za dobivanje vrijednosti, kao što je učinjeno u nastavku
Dobivamo pogrešku jer funkcija INDIRECT Excel vlookup ne može prepoznati niz tablice prosljeđen kao vrijednost B2 ćelije, pa ćemo upotrijebiti INDIRECT funkciju u excelu kako bi vlookup dobio tablicu_ масиv prepoznat kao valjani argument.
Ako promijenimo vrijednosti B1 i B2, dobit ćemo različite vrijednosti prodaje u B3, prema potrebi.
Napomena: Rasponi imena ne razlikuju velika i mala slova. Stoga su Ind, IND i InD jednaki Excelu.
U sljedećem primjeru INDIRECT Excel koristit ćemo funkciju INDIRECT u Excelu za stvaranje ovisnog popisa. Imamo popis položaja zaposlenika u tvrtki; tada za svako radno mjesto imamo imena zaposlenika.
Sada ćemo u ćeliji I2 stvoriti popis za različite redove zaposlenika kao njihova mjesta, a u I3 prikazat ćemo popis koji će ovisiti o vrijednosti u I2. Ako je I2 redatelj, tada bi I3 trebao odražavati imena redatelja koja su Andrew i Micheal; ako je I2 upravitelj, tada bi I3 trebao odražavati imena menadžera koji su Camille, David, Davis i William, a na isti način i za nadzornika.
U ovom ćemo slučaju opet upotrijebiti funkciju INDIRECT u Excelu za stvaranje ovisnog popisa. Provjera valjanosti podataka, u postavkama odabirom kriterija za provjeru valjanosti kao popisa i u izvornom polju, koristit ćemo funkciju INDIRECT u Excelu kako slijedi.
= Neizravno ($ I $ 2)
Kada odabiremo imena direktora u I3, to se odražava kao popis s imenima Andrew i Micheal, kada je I2 upravitelj, tada I3 odražava imena menadžera Camille, David, Davis i William, a slično za nadzornika kao što je prikazano dolje na slici.
Stvari koje treba zapamtiti
- Argument ref_text mora biti valjana referenca na ćeliju, ili će INDIRECT vratiti #REF! Pogreška. Argument ref_text može se odnositi na drugu radnu knjigu.
- Ako se ref_text odnosi na drugu radnu knjigu (vanjsku referencu), druga radna knjiga mora biti otvorena. Ako izvorna radna knjiga nije otvorena, INDIRECT vraća #REF! Vrijednost pogreške.
- Ako se ref_text odnosi na raspon ćelija izvan ograničenja retka od 1.048.576 ili ograničenja stupca od 16.384 (XFD), INDIRECT vraća #REF! Pogreška.
