POSREDNA funkcija v Excelu
Posredna funkcija v Excelu je vgrajena funkcija, ki se uporablja za sklicevanje in pridobivanje vrednosti celic iz besedilnega niza, ta formula se sklicuje iz navedene celice, ima dva argumenta, prvi argument ni obvezen, drugi argument pa neobvezen, ki se nanaša na vrsto ujemanja, lahko to funkcijo uporabite tudi z drugo formulo.
Funkcija INDIRECT v Excelu ima več načinov uporabe. Vrne sklic na celico, določen z besedilnim nizom. Če na primer celica B1 vsebuje niz, recimo, da imata obseg c4 in c4 vrednost »Jessica«. Če bomo zdaj uporabili funkcijo INDIRECT in referenčno besedilo predali kot B1, bo vrnila vrednost, ki jo vsebuje obseg celic c4, ki je »Jessica«.
Excelova funkcija INDIRECT omogoča posredno določanje naslova celice. Kot lahko v zgornjem primeru POSREDNEGA Excela, če celica B1 vsebuje besedilo C4, ta posredna formula excel vrne vsebino celice C4 ( Jessica ). Ta funkcija je izjemno uporabna funkcija. Funkcijo INDIRECT lahko uporabite kadar koli želite spremeniti sklic na celico znotraj posredne formule excel, ne da bi spremenili samo formulo.
POSREDNA Excel formula
Pojasnilo
Ref_text je sklic na celico, ki vsebuje sklic v slogu A1, sklic v stilu R1C1, ime, opredeljeno kot sklic, ali sklic na celico kot besedilni niz.
Argument a1 ni obvezen
| a1 | Napačno ali 0, če je besedilo ref_text v slogu R1C1 |
| a1 | Izpuščeno ali True (1), če je besedilo ref_text v slogu A1 |
Kako uporabiti posredno funkcijo v Excelu?
Vzemimo primer POSREDNEGA excela, preden uporabimo funkcijo naslova v Excelovem delovnem zvezku:
Denimo, da imamo za spletno mesto www.xyz.com Googlove analitične podatke za obiskovalce spletnega mesta iz različnih kanalov občinstva za pet različnih držav, kot je prikazano spodaj v tabeli:
Funkcijo INDIRECT v Excelu lahko uporabimo za pretvorbo besedilnega niza v sklic, torej sklicevanje na celico H4 in uporabo tega imena kot sklica. V tem primeru je uporaba funkcije INDIRECT v Excelu sklicevanje na obsege imen z uporabo vrednosti celice, zato bi v celici I4 lahko preprosto uporabili funkcijo vsote za izračun skupnega števila obiskovalcev iz države Kanade.
Zdaj lahko neposredno izračunamo vsoto obiskovalcev iz vsake države s funkcijo vsote, toda z uporabo funkcije INDIRECT v Excelu lahko izračunamo vsoto obiskovalcev iz različnih držav tako, da spremenimo sklic na ime, namesto da spremenimo samo formulo posrednega excela.
Za vsakega obiskovalca države smo ustvarili obseg imen; to lahko enostavno storite tako, da vnesete obseg imen v prostor tik pod odložišče, kot je prikazano spodaj.
Druga metoda je, da greste v formule-> upravitelj imen -> izberite obseg, ki ga želite poimenovati -> kliknite upravitelja imen -> vnesite obseg imen.
Podobno je ustvaril imenovano paleto v Excelu tudi za obiskovalce iz drugih držav.
Zdaj v I4 izračunamo vsoto obiskovalcev iz države Kanade s pomočjo funkcije INDIRECT in z uporabo obsega imen v H4 (Kanada, ime zgoraj za F3: F7), dobimo skupno vsoto obiskovalca.
Če ustvarimo seznam podatkov z uporabo preverjanja veljavnosti podatkov za države in ga uporabimo za H4
Če spremenimo različna imena držav iz Kanade v Indijo, ZDA, Avstralijo ali Singapur, dobimo skupno vsoto obiskovalcev v I4 s pomočjo funkcije INDIRECT v Excelu.
Torej za vsak primer ne spreminjamo posredne formule excel, ki je v celici I4; spreminjamo sklic na ime v H4 in izračunavamo vsoto obiskovalcev za vsako državo.
Lahko se premaknemo še korak naprej in se lahko sklicujemo na imena delovnih listov tudi s pomočjo funkcije INDIRECT v Excelu. Na primer, zdaj bomo uporabili funkcijo INDIRECT za sklicevanje na različne liste v delovnem zvezku.
Ustvarili smo različne liste z imeni držav za skupno število prodaj, opravljenih z vsakim iskalnim kanalom za različne države.
Za Indijo
Za ZDA
Podobno tudi za druge države.
Spet bomo izračunali skupno prodajo po posameznem kanalu s pomočjo funkcije INDIRECT v Excelu za različne liste z imeni držav kot referenco.
V tem primeru, ker imamo podatke o prodaji na različnih listih, bomo morali združiti različne elemente, ki so obseg podatkov, zato se bo ref_text začel z imenom lista, ki je v tem primeru v B4 Indija, nato bomo uporabite operator & imenovan ampersand, ki se uporablja za združevanje.
Tako dobimo vsoto prodaje za Indijo; zdaj, če spremenimo ime države lista v B4 iz Indije v druge države, kot so ZDA, Singapur ali Kanada, bomo dobili skupno prodajno vrednost teh držav.
Tudi v tem primeru smo preprosto spremenili vrednosti ref_text v celici B4, ne da bi spremenili glavno formulo posrednega excela , ki je v C4, da dobimo vsoto prodaje za različne države, ki so na različnih listih, s pomočjo funkcije INDIRECT.
Zdaj bomo uporabili funkcijo INDIRECT z referenco R1C1, da dobimo prodajno vrednost zadnjega meseca. V stolpcih B, C in D imamo prodajo za različne mesece za tri mesece, januarja, februarja in marca, ki lahko rastejo tudi do mesecev, kot so april, maj, junij itd. Tabela prodaj bo torej vedno naraščala, odvisno od dodajanja prihodnje mesečne prodaje. V tem primeru POSREDNEGA Excela želimo izračunati prodajo zadnjega meseca.
Torej, tukaj je zadnji mesec marec in želimo izračunati skupno prodajo marčevskega meseca, ki znaša 249.344 USD, s pomočjo funkcije INDIRECT.
V tem primeru INDIRECT Excel bomo našli uporabo drugega argumenta funkcije INDIRECT
INDIRECT (ref_text, a1)
a1: ki je neobvezen argument, je logična vrednost (logična vrednost), ki določa, kateri tip sklica (ref_text) je v celici
če je a1 resničen (1) ali izpuščen, ref_text velja za sklic v slogu A1
če je a1 false (0), se ref_text razlaga kot slog R1C1
V čem se slog A1 razlikuje od sloga R1C1?
Običajno funkcija INDIRECT v Excelu uporablja zapis A1. Vsak naslov celice je sestavljen iz črke stolpca in številke vrstice. Vendar funkcija INDIRECT v Excelu podpira tudi zapis R1C1. V tem sistemu se celica A1 imenuje celica R1C1, celica A2 kot R2C1 itd.
Če želite preklopiti na zapis R1C1, izberite Datoteka-> možnost, da odprete pogovorno okno možnosti excel, kliknite zavihek s formulo in kljukico do možnosti referenčnega sloga R1C1. Zdaj boste opazili, da se črke v stolpcu spremenijo v številke. In vse sklice na celice v vaših formulah se tudi prilagodijo.
Ko torej uporabimo slog R1C1 v posredni funkciji v Excelu, vrednost a1 posredujemo kot napačno, če uporabimo slog A1, pa vrednost true prenesemo na a1.
Zdaj bomo v tem primeru POSREDNI Excel uporabili slog R1C1 zaradi zahteve, saj bi stolpci z mesecem naraščali.
Z uporabo sloga R1C1 je skupna vrednost prodaje v vrstici 10, zadnja vrednost stolpca, ki je v stolpcu D, pa se izračuna s funkcijo COUNTA , ki bo dala zadnjo številko stolpca, ki vsebuje vrednost. Torej bo štetje (10:10) v tem primeru dalo 4, kar je številka stolpca v slogu R1C1. Tako smo stopili v stik s to vrednostjo, da smo dobili R10C4, ki vsebuje želeno vrednost, ki je skupna prodajna vrednost zadnjega meseca (249.344 USD).
In ker tukaj uporabljamo slog R1C1, bomo podali napačno vrednost za argument a1.
Zdaj pa dodajte še en stolpec z mesecem aprilom.
Pridobite nove prodajne vrednosti prejšnjega meseca, ki ustrezajo vrednosti zadnjega meseca aprila. Če bomo dodali še več mesecev, bomo vedno dobili novo prodajno vrednost v zadnjem mesecu, ne da bi spremenili formulo posrednega excela za vsak mesec. To je posebnost funkcije INDIRECT v Excelu.
Kako prilagoditi matrično matriko v funkciji INDIRECT Excel VLOOKUP?
Imamo pet držav in iskalni kanal ter prodajo, ki jo je spletno mesto izvedlo po teh različnih kanalih iz različnih držav. Zdaj v celici B3 želimo dobiti prodajo, ki je bila izvedena z organskim iskanjem iz države ZDA.
Za vsako tabelo za različne države smo vsako tabelo poimenovali s kratkim imenom za državo, Ind za Indijo, države za ZDA, Can za Kanado itd .
Če za pridobitev vrednosti neposredno uporabimo funkcijo INDIREKT Excel vlookup, kot je navedeno spodaj
Dobili smo napako, ker funkcija INDIRECT Excel vlookup ne more prepoznati polja tabele, ki je bila posredovana kot vrednost celice B2, zato bomo s funkcijo INDIRECT v Excelu naredili vlookup matriko_tabel kot veljaven argument.
Če spremenimo vrednosti B1 in B2, bomo po potrebi dobili različne vrednosti prodaje v B3.
Opomba: Območja imen ne razlikujejo med velikimi in malimi črkami. Zato so Ind, IND in InD enaki Excelu.
V naslednjem primeru INDIRECT Excel bomo s pomočjo funkcije INDIRECT v Excelu ustvarili odvisen seznam. Imamo seznam položaja zaposlenega v podjetju; potem imamo za vsako delovno mesto imena zaposlenih.
Zdaj bomo v celici I2 ustvarili seznam za različne vrste zaposlenih kot njihove položaje, v I3 pa bomo prikazali seznam, ki bo odvisen od vrednosti v I2. Če je I2 režiser, potem mora I3 odražati imena direktorjev, ki sta jih Andrew in Micheal; če je I2 upravitelj, potem mora I3 odražati imena upravitelja, to so Camille, David, Davis in William, in na enak način za nadzornika.
V tem primeru bomo ponovno uporabili funkcijo INDIRECT v Excelu za ustvarjanje odvisnega seznama. Validacija podatkov, pri nastavitvah, ki izberejo merila za preverjanje kot seznam in v polju vira, bomo v Excelu uporabljali funkcijo INDIRECT, kot sledi.
= POSREDNO ($ I $ 2)
Ko izbiramo imena direktorjev v I3, se to odraža kot seznam z imeni Andrew in Micheal, ko je I2 Manager, potem I3 odraža imena upraviteljev Camille, David, Davis in William in podobno za nadzornika, kot je prikazano spodaj na sliki.
Stvari, ki si jih je treba zapomniti
- Argument ref_text mora biti veljaven sklic na celico, sicer pa bo INDIRECT vrnil #REF! Napaka. Argument ref_text se lahko nanaša na drug delovni zvezek.
- Če se ref_text sklicuje na drug delovni zvezek (zunanja referenca), mora biti drugi delovni zvezek odprt. Če izvorni delovni zvezek ni odprt, INDIRECT vrne #REF! Vrednost napake.
- Če se ref_text nanaša na obseg celic zunaj omejitve vrstic 1.048.576 ali omejitve stolpcev 16.384 (XFD), INDIRECT vrne #REF! Napaka.
