Funkcia NEPRIAME v programe Excel
Nepriama funkcia v programe Excel je vstavaná funkcia, ktorá sa používa na referenciu a získanie hodnôt buniek z textového reťazca, tento vzorec berie odkaz z odkazovanej bunky, má dva argumenty, prvý argument nie je voliteľný, zatiaľ čo druhý argument je voliteľný, čo odkazuje na typ zhody, je možné túto funkciu použiť aj s iným vzorcom.
Funkcia INDIRECT v programe Excel má rôzne využitie. Vráti odkaz na bunku určený textovým reťazcom. Ak napríklad bunka B1 obsahuje reťazec, povedzme rozsah c4 a c4 majú hodnotu „Jessica“. Teraz, ak použijeme funkciu INDIRECT a odovzdáme referenčný text ako B1, vráti hodnotu obsiahnutú v rozsahu buniek c4, ktorá je „Jessica“.
Funkcia Excel INDIRECT vám umožňuje NEPRIAME určiť adresu bunky. Ako môžete vo vyššie uvedenom príklade NEPRIAMEHO Excelu, ak bunka B1 obsahuje text C4, tento vzorec nepriameho programu Excel vráti obsah bunky C4 ( Jessica ). Táto funkcia je mimoriadne užitočná. Funkciu INDIRECT môžete použiť kedykoľvek, keď chcete zmeniť odkaz na bunku vo vzorci nepriameho programu Excel bez toho, aby ste zmenili samotný vzorec.
NEPRIAMY vzorec programu Excel
Vysvetlenie
Ref_text je odkaz na bunku, ktorá obsahuje odkaz v štýle A1, odkaz v štýle R1C1, názov definovaný ako odkaz alebo odkaz na bunku ako textový reťazec.
argument a1 je voliteľný
| a1 | False alebo 0, ak je ref_text v štýle R1C1 |
| a1 | Vynechané alebo pravdivé (1), keď je ref_text v štýle A1 |
Ako používať funkciu NEPRIAME v Exceli?
Pred použitím funkcie adresy v zošite programu Excel si vezmime príklad INDIRECT excel:
Predpokladajme, že pre web www.xyz.com máme analytické údaje Google pre návštevníkov tohto webu z iného publika v piatich rôznych krajinách, ako je uvedené v nasledujúcej tabuľke:
Funkciu INDIRECT v programe Excel môžeme použiť na účely prevodu textového reťazca na referenciu, teda odkazu na bunku H4 a použitia tohto názvu ako referencie. Použitie funkcie INDIRECT v programe Excel, v tomto prípade, je odkazovanie na rozsahy mien pomocou hodnôt bunky, takže v bunke I4 by sme mohli jednoducho použiť funkciu sum na výpočet celkového počtu návštevníkov z krajiny Kanada.
Teraz môžeme priamo vypočítať súčet návštevníkov z každej krajiny pomocou funkcie súčtu, ale pomocou funkcie INDIRECT v programe Excel môžeme vypočítať súčet návštevníkov z rôznych krajín tak, že namiesto zmeny nepriameho excelového vzorca sami zmeníme odkaz na meno.
Vytvorili sme rozsah mien pre každého návštevníka krajiny; to sa dá ľahko urobiť zadaním rozsahu mien do priestoru tesne pod schránkou, ako je to znázornené nižšie.
Ďalšou metódou je, že môžete prejsť na vzorce-> správca mien -> vyberte rozsah, ktorý chcete pomenovať -> kliknite na správcu mien -> zadajte rozsah mien.
Podobne vytvoril pomenovaný rozsah v programe Excel aj pre návštevníkov z iných krajín.
Teraz v I4, výpočtom súčtu návštevníka z krajiny Kanada pomocou funkcie INDIRECT a použitím rozsahu mien v H4 (Kanada, vyššie uvedený názov pre F3: F7), dostaneme celkovú sumu návštevníka.
Ak vytvoríme zoznam údajov pomocou overenia údajov pre krajiny a použijeme tento zoznam na H4
A keď zmeníme rôzne názvy krajín z Kanady na Indiu, USA, Austráliu alebo Singapur, dostaneme celkový súčet návštevníka v I4 pomocou funkcie INDIRECT v programe Excel.
Takže pre každý prípad nemeníme vzorec nepriameho excelu, ktorý je v bunke I4; meníme odkaz na meno v H4 a počítame súčet návštevníkov pre každú krajinu.
Môžeme sa posunúť o krok ďalej a môžeme odkazovať na názvy na úrovni pracovných hárkov aj pomocou funkcie INDIRECT v programe Excel. Napríklad teraz budeme používať funkciu INDIRECT v súvislosti s rôznymi listami v zošite.
Vytvorili sme rôzne hárky s názvami krajín pre celkový počet predajov uskutočnených s každým vyhľadávacím kanálom pre rôzne krajiny.
Pre Indiu
Pre USA
Podobne aj pre ostatné krajiny.
Opäť budeme počítať celkový predaj uskutočnený prostredníctvom každého kanála pomocou funkcie INDIRECT v programe Excel pre rôzne hárky s názvami krajín ako referenciou.
V tomto prípade, keďže máme údaje o predaji v rôznych hárkoch, budeme musieť zreťaziť rôzne prvky, ktoré sú rozsahom údajov, takže ref_text bude začínať názvom hárka, ktorým je v tomto prípade India v B4, potom budeme použite operátor & zvaný ampersand, ktorý sa používa na zreťazenie.
Dostaneme teda celkovú sumu predaja pre Indiu; ak teraz zmeníme názov krajiny listu v B4 z Indie na ďalšie krajiny, ako sú USA, Singapur alebo Kanada, dostaneme celkovú hodnotu predaja týchto krajín.
Opäť v tomto prípade sme jednoducho zmenili hodnoty ref_text v bunke B4 bez toho, aby sme zmenili hlavný vzorec nepriameho excelu , ktorý je v C4, aby sme pomocou funkcie INDIRECT dostali súčet tržieb pre rôzne krajiny, ktoré sú v rôznych listoch.
Teraz použijeme funkciu INDIRECT pomocou odkazu R1C1 na získanie predajnej hodnoty za posledný mesiac. Máme tržby za rôzne mesiace v stĺpcoch B, C a D za tri mesiace január, február a mar, ktoré môžu narásť na ďalšie mesiace ako apríl, máj, jún atď. Tabuľka výpredajov sa teda bude neustále zväčšovať v závislosti od pridania predaja v nasledujúcom mesiaci. V tomto príklade NEPRIAMEHO programu Excel teda chceme vypočítať predaj za posledný mesiac.
Posledným mesiacom je teda Mar a pomocou funkcie INDIRECT chceme vypočítať celkový predaj v marcovom mesiaci, ktorý je 249 344 USD.
V tomto príklade INDIRECT Excel nájdeme použitie druhého argumentu funkcie INDIRECT
NEPRIAME (ref_text, a1)
a1: čo je voliteľný argument, je to logická hodnota (logická hodnota), ktorá určuje, aký typ odkazu (ref_text) je obsiahnutý v bunke
ak a1 je true (1) alebo je vynechané, ref_text sa považuje za referenciu v štýle A1
ak a1 je false (0), ref_text sa interpretuje ako štýl R1C1
Čím sa líši štýl A1 od štýlu R1C1?
Funkcia INDIRECT v programe Excel zvyčajne používa notáciu A1. Každá adresa bunky pozostáva z písmena stĺpca a čísla riadku. Funkcia INDIRECT v programe Excel však podporuje aj zápis R1C1. V tomto systéme sa bunka A1 označuje ako bunka R1C1, bunka A2 ako R2C1 atď.
Ak chcete zmeniť notáciu R1C1, vyberte príkaz Súbor-> možnosť, aby ste otvorili dialógové okno možností programu Excel, kliknite na kartu vzorec a začiarknite políčko s možnosťou referenčného štýlu R1C1. Teraz si všimnete, že všetky písmená stĺpcov sa menia na čísla. A upravia sa aj všetky odkazy na bunky vo vašich vzorcoch.
Takže keď použijeme štýl R1C1 vo funkcii INDIRECT v programe Excel, odovzdáme hodnotu a1 ako nepravdivú, a ak použijeme štýl A1, potom hodnotu true odovzdáme a1.
Teraz v tomto príklade INDIRECT Excel budeme používať štýl R1C1 kvôli požiadavke, pretože stĺpce s mesiacom budú pribúdať.
Pri použití štýlu R1C1 je celková hodnota predaja v riadku 10 a hodnota posledného stĺpca, ktorá sa nachádza v stĺpci D, sa vypočíta pomocou funkcie COUNTA , ktorá poskytne číslo posledného stĺpca obsahujúceho hodnotu. Count (10:10) in this case will give 4, which the column number in R1C1 style. Takže sme túto hodnotu kontaktovali, aby sme dostali R10C4, ktorý obsahuje požadovanú hodnotu, ktorá predstavuje celkovú hodnotu predaja za posledný mesiac (249 344 dolárov).
A keďže tu používame štýl R1C1, zadáme nesprávnu hodnotu pre argument a1.
Teraz pridajme ešte jeden stĺpec s mesiacom aprílový výpredaj.
Získajte teda nové hodnoty predaja za posledný mesiac zodpovedajúce hodnotám za posledný mesiac apríl. Podobne, ak pridáme ďalšie mesiace, získame vždy novú predajnú hodnotu z minulého mesiaca bez toho, aby sme pre každý mesiac zmenili vzorec nepriameho excelu. Toto je špecialita funkcie INDIRECT v programe Excel.
Ako prispôsobiť pole tabuľky vo funkcii INDIRECT Excel VLOOKUP?
Máme päť krajín a vyhľadávací kanál a predaj, ktorý web realizoval prostredníctvom týchto rôznych kanálov z rôznych krajín. Teraz v bunke B3 chceme získať predaj, ktorý bol uskutočnený prostredníctvom organických vyhľadávaní z krajiny Spojené štáty americké.
Pre každú tabuľku pre rôzne krajiny sme pomenovali každú tabuľku s krátkym názvom pre krajinu, Ind pre Indiu, Štáty pre USA, Can pre Kanadu atď .
Ak na získanie hodnoty priamo použijeme funkciu INDIRECT Excel vlookup, ako je uvedené nižšie
Dostaneme chybu, pretože funkcia INDIRECT Excel vlookup nie je schopná rozpoznať odovzdané pole tabuľky ako hodnotu bunky B2, preto použijeme funkciu INDIRECT v programe Excel, aby vlookup umožnil rozpoznanie tabuľky_array ako platného argumentu.
Ak zmeníme hodnoty B1 a B2, dostaneme podľa potreby rôzne hodnoty Sale v B3.
Poznámka: V rozsahu mien sa nerozlišujú veľké a malé písmená. Preto sú Ind, IND a InD rovnaké pre Excel.
V nasledujúcom príklade INDIRECT Excel budeme na vytvorenie závislého zoznamu používať funkciu INDIRECT v programe Excel. Máme zoznam pozícií zamestnanca v spoločnosti; potom máme pre každú pozíciu mená zamestnancov.
Teraz v bunke I2 vytvoríme zoznam rôznych pozícií zamestnancov ako ich pozícií a v I3 ukážeme zoznam, ktorý bude závisieť od hodnoty v I2. Ak je I2 režisérom, potom by I3 mal odrážať mená režisérov, ktoré Andrew a Micheal; ak I2 je manažér, potom I3 by mala odzrkadľovať mená manažéra, ktorý je Camille, David, Davis a William, a rovnako tak pre vedúceho.
V tomto prípade opäť použijeme funkciu INDIRECT v programe Excel na vytvorenie závislého zoznamu. Overenie údajov, v nastaveniach výberu kritérií overenia ako zoznamu a v zdrojovom poli použijeme funkciu INDIRECT v programe Excel nasledovne.
= NEPRIAMY ($ I $ 2)
Keď vyberáme mená riaditeľov v I3, odráža to zoznam s menami Andrew a Micheal, keď I2 je manažér, potom I3 odráža mená manažérov Camille, Davida, Davisa a Williama a podobne pre vedúceho, ako je znázornené nižšie na obrázku.
Na čo treba pamätať
- Argument ref_text musí byť platný odkaz na bunku, inak INDIRECT vráti #REF! Chyba. Argument ref_text môže odkazovať na iný zošit.
- Ak ref_text odkazuje na iný zošit (externý odkaz), musí byť druhý zošit otvorený. Ak zdrojový zošit nie je otvorený, funkcia INDIRECT vráti #REF! Chybná hodnota.
- Ak ref_text odkazuje na rozsah buniek mimo limitu riadkov 1 048 576 alebo limitu stĺpcov 16 384 (XFD), funkcia INDIRECT vráti #REF! Chyba.
