Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția Excel INDIRECT în Excel pentru a crea o referință de celulă din text.
Prezentare generală a funcției INDIRECTE
Funcția INDIRECT Creează o referință de celulă dintr-un șir de text.
(Observați cum apar datele introduse de formulă)
Funcția INDIRECTă Sintaxă și intrări:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - Un șir care reprezintă o referință de celulă sau o referință de interval. Șirul poate fi în format R1C1 sau A1 sau poate fi un interval denumit.
a1 - OPȚIONAL: Indică dacă referința este în format R1C1 sau A1. FALS pentru R1C1 sau TRUE / Ommitted pentru A1.
Care este funcția INDIRECTĂ?
Funcția INDIRECT vă permite să dați un șir de text și să cereți computerului să interpreteze acel șir ca o referință reală. Aceasta poate fi utilizată pentru a face referire la un interval pe aceeași foaie, o foaie diferită sau chiar un registru de lucru diferit.
ATENȚIE: Funcția INDIRECTĂ este una dintre funcțiile volatile. De cele mai multe ori, când lucrați în foaia de calcul, computerul va recalcula o formulă numai dacă intrările și-au schimbat valorile. Cu toate acestea, o funcție volatilă recalculează fiecare de când faceți o modificare la orice celulă. Trebuie folosită precauție pentru a vă asigura că nu provocați un timp mare de recalculare din cauza utilizării excesive a funcției volatile sau a faptului că multe celule depind de rezultatul unei funcții volatile.
Creați o referință de celulă
Spuneți că doriți să preluați valoarea din A2, dar doriți să vă asigurați că formula dvs. rămâne pe A2, indiferent de rândurile noi inserate / eliminate. Ai putea scrie o formulă de
| 1 | = INDIRECT („A2”) |
Rețineți că argumentul din funcția noastră este șirul de text „A2” și nu o referință de celulă. De asemenea, deoarece acesta este un șir de text, nu este necesar să indicați o referință absolută, cum ar fi $ A $ 2. Textul nu se va schimba niciodată și, prin urmare, această formulă va indica întotdeauna A2, indiferent unde este mutat.
Număr de rând INDIRECT
Puteți concatena șiruri de text și valori din celule împreună. În loc să scriem „A2”, așa cum am făcut anterior, putem prelua o valoare numerică din celula B2 și o putem folosi în formula noastră. Am scrie o formulă de genul
| 1 | = INDIRECT („A” & B2) |
Simbolul „&” este utilizat aici pentru a concatena șirul de text „A” cu valoarea din celula B2. Deci, dacă valoarea B2 este în prezent 10, atunci formula noastră ar citi acest lucru ca
| 123 | = INDIRECT („A” & 10)= INDIRECT ("A10")= A10 |
Valoare INDIRECTĂ a coloanei
De asemenea, puteți concatena în referința coloanei. De data aceasta, să spunem că știm că dorim să obținem o valoare din rândul 10, dar vrem să putem schimba din ce coloană să tragem. Vom pune litera coloanei dorită în celula B2. Formula noastră ar putea arăta ca.
| 1 | = INDIRECT (B2 & "10") |
Dacă valoarea lui B2 este „G”, atunci formula noastră evaluează așa
| 123 | = INDIRECT („G” & 10)= INDIRECT („G10”)= G10 |
Stil r1c1 INDIRECT
În exemplul nostru anterior, a trebuit să folosim o literă pentru a indica referința coloanei. Acest lucru se datorează faptului că foloseam ceea ce este cunoscut sub denumirea de stil A1. În stilul A1, coloanele sunt date de o literă, iar rândurile sunt date de cifre. Referințele absolute sunt indicate folosind „$” înainte de articolul pe care dorim să rămână absolut.
În r1c1, atât rândurile, cât și coloanele sunt începute folosind numărul. Referința absolută la a1 ar fi scrisă ca
| 1 | = R1C1 |
Puteți citi acest lucru ca „Rândul 1, Coloana 1”. Referințele relative sunt date folosind paranteze, dar numărul indică poziția relativ la celula cu formula. Deci, dacă am scrie o formulă în celula A10 și ar trebui să ne referim la A1, am scrie formula
| 1 | = R [-9] C |
Puteți citi acest lucru ca „Celula 9 rânduri în sus, dar în aceeași coloană.
Motivul pentru care acest lucru ar putea fi util este că INDIRECT poate sprijini utilizarea notației r1c1. Luați în considerare exemplul anterior în care preluam o valoare din rândul 10, dar am vrut să putem schimba coloana. În loc să dăm o scrisoare, să spunem că punem un număr în celula B2. Formula noastră ar putea arăta așa
| 1 | = INDIRECT („R10C” & B2, FALS) |
Am omis cele 2nd argument până acum. Dacă acest argument este omis sau este adevărat, funcția va evalua folosind stilul A1. Deoarece este fals, va evalua în r1c1. Să presupunem că valoarea B2 este 5. Formula noastră va evalua acest lucru așa
| 12 | = INDIRECT ("R10C5", FALS)= $ E $ 10 |
Diferențe INDIRECTE cu A1 vs r1c1
Amintiți-vă că am arătat anterior că, din moment ce conținutul acestei formule a fost un șir de text, nu s-a schimbat niciodată?
| 1 | = INDIRECT („A2”) |
Această formulă se va uita întotdeauna la celula A2, indiferent de locul în care mutați formula. În r1c1, deoarece puteți indica poziția relativă folosind paranteze, această regulă nu rămâne consecventă. Dacă plasați această formulă în celula B2
| 1 | = INDIRECT („RC [-1]”) |
Se va uita la celula A2 (deoarece coloana A este una în stânga coloanei B). Dacă copiați această formulă în celula B3, textul din interior va rămâne același, dar INDIRECTUL se va uita acum la celula A3.
INDIRECT cu numele foii
De asemenea, puteți combina un nume de foaie în referințele dvs. INDIRECTE. O regulă importantă de reținut este că ar trebui să plasați ghilimele unice în jurul numelor și trebuie să separați numele foii de referința celulei cu un semn de exclamare.
Să presupunem că am avut această configurație, în care afirmăm numele, rândul și coloana foii noastre.
Formula noastră de a combina toate acestea într-o referință ar arăta astfel:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Formula noastră va fi apoi evaluată astfel:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Sheet2'! B5 |
Din punct de vedere tehnic, din moment ce cuvântul „Sheet2” nu conține spații, nu avem nevoie ghilimelele unice. Este perfect valabil să scrii ceva de genul
| 1 | = Sheet2! A2 |
Cu toate acestea, nu strică să plasați ghilimelele atunci când nu aveți nevoie de ele. Este recomandată să le includeți astfel încât formula dvs. să poată gestiona instanțele în care ar putea fi necesare.
INDIRECT la un alt registru de lucru
Vom menționa, de asemenea, că INDIRECT poate crea o referință la un alt registru de lucru. Limita este că INDIRECT nu va prelua valori dintr-un registru de lucru închis, deci această utilizare specială are caracter practic limitat. Dacă registrul de lucru către care indică INDIRECT nu este deschis, funcția va arunca un „#REF!” eroare.
Sintaxa la scrierea numelui registrului de lucru este că trebuie să fie între paranteze drepte. Să folosim această configurare și să încercăm să preluăm o valoare din celula C7.
Formula noastră ar fi
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Din nou, acordați atenție plasării ghilimelelor unice, a parantezelor și a semnului exclamării. Formula noastră va fi apoi evaluată astfel:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Summary" & "'! C7")= INDIRECT ("'[Sample.xslx] Summary'! C7")= '[Sample.xlsx] Rezumat'! C7 |
INDIRECT pentru a construi un interval dinamic
Când aveți un set mare de date, este important să încercați să optimizați formulele, astfel încât să nu facă mai multă muncă decât este necesar. De exemplu, mai degrabă decât să facem referire la toate coloana A, am putea dori doar să facem referire la numărul exact de celule din lista noastră. Luați în considerare următorul aspect:
În celula B2, am plasat formula
| 1 | = COUNTA (A: A) |
Funcția COUNTA este foarte ușor de calculat pentru computer, deoarece verifică pur și simplu câte celule din col A au o anumită valoare, spre deosebire de faptul că trebuie să facă verificări logice sau operații matematice.
Acum, să construim formula noastră care va însuma valorile din coloana A, dar vrem să ne asigurăm că se uită doar la intervalul exact cu valori (A2: A5). Vom scrie formula noastră ca
| 1 | = SUM (INDIRECT ("A2: A" & B2)) |
INDIRECTUL nostru va prelua numărul 5 din celula B2 și va crea o referință la intervalul A2: A5. SUM poate utiliza apoi acest interval pentru calculul său. Dacă adăugăm o altă valoare în celula A6, atunci numărul din B2 se va actualiza, iar formula noastră SUM se va actualiza automat și pentru a include această nouă valoare.
ATENȚIE: Odată cu introducerea tabelelor în Office 2007, este mult mai eficient să vă stocați datele într-un tabel și să utilizați o referință structurală, mai degrabă decât să construiți formula pe care am folosit-o în acest exemplu datorită naturii volatile a INDIRECT. Cu toate acestea, pot fi cazuri în care trebuie să creați o listă de articole și să nu puteți utiliza un tabel.
Diagramă dinamică cu INDIRECT
Să luăm exemplul anterior și să mergem încă un pas. În loc să scriem o formulă care să ne ofere suma valorilor, vom crea o gamă numită. Am putea numi acest interval „MyData” și să-l facem referire
| 1 | = INDIRECT („A2: A” & COUNTA ($ A: $ A)) |
Rețineți că, din moment ce introducem acest lucru într-un interval Named, am schimbat referința la B2 și, în schimb, am introdus funcția COUNTA direct acolo.
Acum că avem acest interval numit, l-am putea folosi într-un grafic. Vom crea o diagramă liniară goală, apoi vom adăuga o serie de date. Pentru valorile seriei, ai putea scrie ceva de genul
| 1 | = Sheet1! MyData |
Graficul va folosi acum această referință pentru a trasa valorile. Pe măsură ce se adaugă mai multe valori în coloana A, INDIRECT se va referi la un interval din ce în ce mai mare, iar graficul nostru va continua să rămână actualizat cu toate valorile nou adăugate.
Validare dinamică a datelor cu INDIRECT
Atunci când colectați informații de la utilizatori, uneori este nevoie să faceți opțiunile dintr-o singură alegere în funcție de o alegere anterioară. Luați în considerare acest aspect, unde prima noastră coloană permite utilizatorului să aleagă între fructe, legume și carne.
În 2nd coloană, nu vrem să avem o listă mare care să arate toate opțiunile posibile, deoarece am redus deja lucrurile puțin. Deci, am creat alte 3 liste care arată astfel:
Apoi, le vom atribui fiecăruia aceste listele într-un domeniu numit. Adică, toate fructele vor fi într-o gamă numită „Fructe”, iar legumele în „Legume” etc.
Înapoi în tabelul nostru, suntem gata să configurăm validarea datelor în 2nd coloană. Vom crea o validare de tip Listă, cu o intrare de:
| 1 | = INDIRECT (A2) |
INDIRECT va citi în alegerea făcută în coloana A și va vedea numele unei categorii. Am definit intervale cu aceste nume, astfel încât INDIRECT va lua apoi numele respectiv și va crea o referință la intervalul dorit.
Note Aditionale
Utilizați funcția INDIRECT pentru a crea o referință de celulă din text.
Mai întâi creați șirul de text care reprezintă o referință de celulă. Șirul trebuie să fie fie în litera și numărul rândului obișnuit în coloană stil A1 (M37), fie în stil R1C1 (R37C13). Puteți tasta referința direct, dar de obicei veți face referință la celule care definesc rândurile și coloanele. În cele din urmă, introduceți formatul de referință al celulei pe care îl alegeți. ADEVĂRAT sau Omis pentru referință stil A1 sau FALS pentru stil R1C1.
În timp ce lucrați cu formule INDIRECTE, vă recomandăm să utilizați Funcția ROW pentru a obține numărul rândului unei referințe sau FUNCȚIA COLONNĂ pentru a obține numărul coloanei (nu litera) unei referințe.
Reveniți la lista tuturor funcțiilor din Excel
INDIRECT în Foi de calcul Google
Funcția INDIRECTĂ funcționează exact la fel în Foi de calcul Google ca în Excel:
