Funcția INDIRECTĂ în Excel
Funcția indirectă în Excel este o funcție încorporată care este utilizată pentru referință și obținerea valorilor celulei dintr-un șir de text, această formulă ia referință din celula menționată, are două argumente, primul argument nu este opțional, în timp ce al doilea argument este opțional, care se referă la tipul de potrivire, această funcție poate fi utilizată și cu o altă formulă.
Funcția INDIRECTĂ în Excel are mai multe utilizări. Returnează referința de celulă specificată de un șir de text. De exemplu, dacă celula B1 conține un șir, să presupunem că intervalul c4 și c4 au valoarea „Jessica”. Acum, dacă vom folosi funcția INDIRECTĂ și vom transmite textul de referință ca B1, va returna valoarea conținută de intervalul de celule c4 care este „Jessica”.
Funcția INDIRECT a Excel vă permite să specificați o adresă de celulă INDIRECT. După cum puteți în exemplul INDIRECT Excel de mai sus, dacă celula B1 conține textul C4, această formulă excelă indirectă returnează conținutul celulei C4 ( Jessica ). Această funcție este o funcție extrem de utilă. Puteți utiliza funcția INDIRECT ori de câte ori doriți să modificați referința la o celulă dintr-o formulă excelă indirectă, fără a modifica formula în sine.
Formula Excel INDIRECTĂ
Explicaţie
Ref_text este o referință la o celulă care conține o referință în stil A1, o referință în stil R1C1, un nume definit ca referință sau o referință la o celulă ca șir de text.
argumentul a1 este opțional
| a1 | Fals sau 0, când ref_text este în stil R1C1 |
| a1 | Omis sau Adevărat (1) când ref_text este în stil A1 |
Cum se folosește funcția INDIRECTĂ în Excel?
Să luăm un exemplu Excel INDIRECT înainte de a utiliza funcția de adresă în registrul de lucru Excel:
Să presupunem că pentru un site web www.xyz.com avem datele Google Analytics pentru vizitatorii site-ului web de pe un canal de audiență diferit pentru cinci țări diferite, după cum se arată mai jos în tabel:
Putem folosi funcția INDIRECT în Excel pentru a converti un șir de text într-o referință, deci referindu-ne la celula H4 și folosind numele respectiv ca referință. Utilizarea funcției INDIRECT în excel, în acest caz, este de a face referință la intervalele de nume folosind valorile unei celule, astfel încât în celula I4, am putea folosi pur și simplu funcția sumă pentru a calcula numărul total de vizitatori dintr-o țară Canada.
Acum, putem calcula direct suma vizitatorilor din fiecare țară folosind funcția sumă, dar folosind funcția INDIRECT în excel, putem calcula suma vizitatorilor din diferite țări prin schimbarea referinței de nume, mai degrabă decât prin schimbarea în sine a formulei indirecte Excel.
Am creat gama de nume pentru fiecare vizitator al țării; acest lucru se poate face cu ușurință prin tastarea intervalului de nume în spațiul de sub clipboard, așa cum se arată mai jos.
O altă metodă este că puteți merge la formule-> manager de nume -> selectați intervalul pe care doriți să îl denumiți -> faceți clic pe manager de nume -> tastați intervalul de nume.
În mod similar, a creat o gamă numită în excel și pentru vizitatorii din alte țări.
Acum, în I4, calculând suma vizitatorului din țara Canada utilizând funcția INDIRECT și folosind gama de nume din H4 (Canada, un nume de mai sus pentru F3: F7), obținem suma totală a unui vizitator.
Dacă creăm o listă de date folosind validarea datelor pentru țări și aplicăm lista respectivă la H4
Și schimbând diferitele nume de țară din Canada în India, SUA, Australia sau Singapore, obținem suma totală a vizitatorilor în I4 folosind funcția INDIRECT în Excel.
Deci, pentru fiecare caz, nu schimbăm formula excel indirectă, care se află în celula I4; schimbăm referința de nume în H4 și calculăm suma vizitatorilor pentru fiecare țară.
Ne putem deplasa cu un pas mai departe și putem face referire la numele la nivel de foaie de lucru folosind și funcția INDIRECT în excel. De exemplu, vom folosi acum funcția INDIRECT în referință la diferite foi din registrul de lucru.
Am creat diferite foi cu numele țărilor pentru numărul total de vânzări realizate cu fiecare canal de căutare pentru diferite țări.
Pentru India,
Pentru SUA,
În mod similar, pentru alte țări.
Din nou, vom calcula vânzările totale realizate prin fiecare canal folosind funcția INDIRECT în Excel pentru diferite foi cu nume de țară ca referință.
În acest caz, deoarece avem date despre vânzări în diferite foi, va trebui să concatenăm diferite elemente care sunt intervalul de date, astfel încât ref_text va începe cu numele foii, care este India în acest caz în B4, atunci vom utilizați operatorul & numit ampersand care este utilizat pentru concatenare.
Deci, obținem suma totală a vânzărilor pentru India; acum, dacă schimbăm numele țării din B4 din India în alte țări precum SUA, Singapore sau Canada, vom obține valoarea totală de vânzare a acestor țări.
Din nou, în acest caz, am schimbat pur și simplu valorile ref_text din celula B4 fără a schimba formula principală indirectă excel, care este în C4, pentru a obține suma vânzărilor pentru diferite țări care se află în diferite foi folosind funcția INDIRECT.
Acum, vom folosi funcția INDIRECT folosind referința R1C1 pentru a obține valoarea de vânzare din ultima lună. Avem vânzări pentru diferite luni în coloanele B, C și D pentru trei luni ianuarie, februarie și mar, care pot crește până la alte luni, cum ar fi aprilie, mai, iunie și așa mai departe. Deci, tabelul de vânzări va crește întotdeauna în funcție de adăugarea vânzării lunii viitoare. Deci, în acest exemplu INDIRECT Excel, vrem să calculăm vânzările din ultima lună.
Deci, aici ultima lună este Mar și dorim să calculăm vânzarea totală a lunii martie, care este de 249.344 USD, folosind funcția INDIRECT.
În acest exemplu INDIRECT Excel, vom găsi utilizarea celui de-al doilea argument al funcției INDIRECT
INDIRECT (ref_text, a1)
a1: care este un argument opțional, este o valoare logică (o valoare booleană) care specifică ce tip de referință (ref_text) este conținut în celulă
dacă a1 este adevărat (1) sau omis, ref_text este considerat o referință în stil A1
dacă a1 este fals (0), ref_text este interpretat ca un stil R1C1
Cum diferă stilul A1 de stilul R1C1?
În mod normal, funcția INDIRECT în Excel utilizează notația A1. Fiecare adresă de celulă constă dintr-o literă de coloană și un număr de rând. Cu toate acestea, funcția INDIRECT în Excel acceptă, de asemenea, notația R1C1. În acest sistem, celula A1 este denumită celula R1C1, celula A2 ca R2C1 și așa mai departe.
Pentru a trece la notația R1C1, alegeți Fișier-> opțiune pentru a deschide caseta de dialog cu opțiuni Excel, faceți clic pe fila formulă și bifați opțiunea stilului de referință R1C1. Acum, veți observa că literele coloanei se transformă în cifre. Și toate referințele de celule din formulele dvs. se ajustează, de asemenea.
Deci, atunci când folosim stilul R1C1 în funcția INDIRECT în excel, trecem valoarea a1 ca fiind falsă și dacă folosim stilul A1, atunci trecem valoarea adevărată la a1.
Acum, în acest exemplu INDIRECT Excel, vom folosi stilul R1C1 datorită cerinței, deoarece coloanele cu lună ar crește.
Folosind stilul R1C1, valoarea totală de vânzare este în rândul 10, iar ultima valoare a coloanei, care se află în coloana D, va fi calculată folosind funcția COUNTA , care va da ultimul număr de coloană care conține o valoare. Deci, numărați (10:10) în acest caz va da 4, care este numărul coloanei în stilul R1C1. Deci, am contactat această valoare pentru a obține R10C4 care conține valoarea dorită, care este valoarea totală de vânzare din ultima lună (249.344 USD).
Și, deoarece folosim stilul R1C1 aici, vom transmite o valoare falsă pentru argumentul a1.
Acum, permiteți-ne să adăugăm încă o coloană cu reducerile din luna aprilie.
Deci, obțineți noi valori ale vânzărilor de luna trecută corespunzătoare celei din ultima lună a lunii aprilie. În mod similar, dacă vom adăuga mai multe luni, vom obține întotdeauna o nouă valoare de vânzare de luna trecută, fără a modifica formula excel indirectă pentru fiecare lună. Aceasta este specialitatea funcției INDIRECT în excel.
Cum se personalizează matricea de tabele în funcția INDIRECT Excel VLOOKUP?
Avem cinci țări și un canal de căutare și vânzarea pe care site-ul web a făcut-o prin aceste canale diferite din diferite țări. Acum, în celula B3, dorim să obținem vânzarea prin căutări organice din țara Statelor Unite.
Pentru fiecare tabel pentru țări diferite, am numit fiecare tabel cu un nume scurt pentru țară, Ind pentru India, state pentru SUA, Can pentru Canada și așa mai departe .
Dacă folosim direct funcția vlookup INDIRECT Excel pentru a obține valoarea, așa cum se face mai jos
Primim o eroare deoarece funcția vlookup Excel INDIRECT nu este capabilă să recunoască matricea de tabel transmisă ca valoare a celulei B2, așa că vom folosi funcția INDIRECT în excel pentru a face ca vlookup să recunoască table_array ca argument valid.
Dacă schimbăm valorile B1 și B2, vom obține valori de vânzare diferite în B3, după cum este necesar.
Notă: Intervalele de nume nu sunt sensibile la majuscule și minuscule. Prin urmare - Ind, IND și InD sunt la fel pentru Excel.
În următorul exemplu INDIRECT Excel, vom folosi funcția INDIRECT în Excel pentru a crea o listă dependentă. Avem o listă a poziției unui angajat într-o companie; apoi, pentru fiecare post, avem numele angajaților.
Acum, în celula I2, vom crea o listă pentru diferite ranguri de angajați ca poziții și, în I3, vom afișa o listă care va depinde de valoarea din I2. Dacă I2 este director, atunci I3 ar trebui să reflecte numele directorilor pe care Andrew și Micheal; dacă I2 este Manager, atunci I3 ar trebui să reflecte numele managerului care este Camille, David, Davis și William și în același mod pentru supraveghetor.
În acest caz, din nou, vom folosi funcția INDIRECT în excel pentru a crea lista dependentă. Validarea datelor, în setările selectând criteriile de validare ca listă și în câmpul sursă, vom folosi funcția INDIRECT în excel după cum urmează.
= INDIRECT ($ I $ 2)
Când selectăm numele directorilor în I3 se reflectă ca o listă cu numele Andrew și Micheal, când I2 este Manager, atunci I3 reflectă numele managerilor Camille, David, Davis și William și în mod similar pentru supraveghetor așa cum se arată mai jos în figură.
Lucruri de amintit
- Argumentul ref_text trebuie să fie o referință de celulă validă, sau INDIRECT va returna #REF! Eroare. Argumentul ref_text se poate referi la un alt registru de lucru.
- Dacă ref_text se referă la un alt registru de lucru (o referință externă), celălalt registru de lucru trebuie să fie deschis. Dacă registrul de lucru sursă nu este deschis, INDIRECT returnează #REF! Valoarea erorii.
- Dacă ref_text se referă la un interval de celule în afara limitei de rând de 1.048.576 sau a limitei de coloană de 16.384 (XFD), INDIRECT returnează un #REF! Eroare.
