Cuprins
Felicitări! Ai ajuns în ultima zi a maratonului 30 de funcții Excel în 30 de zile. A fost o călătorie lungă și interesantă în timpul căreia ați învățat multe lucruri utile despre funcțiile Excel.
În cea de-a 30-a zi a maratonului, vom dedica studiului funcției INDIRECT (INDIRECT), care returnează linkul specificat de șirul de text. Cu această funcție, puteți crea liste derulante dependente. De exemplu, atunci când selectați o țară dintr-o listă derulantă, determină opțiunile care vor apărea în lista derulantă a orașului.
Deci, să aruncăm o privire mai atentă la partea teoretică a funcției INDIRECT (INDIRECT) și explorați exemple practice de aplicare a acestuia. Dacă aveți informații suplimentare sau exemple, vă rugăm să le împărtășiți în comentarii.
Funcția 30: INDIRECT
Funcţie INDIRECT (INDIRECT) returnează linkul specificat de șirul de text.
Cum poți folosi funcția INDIRECT?
Din moment ce funcţia INDIRECT (INDIRECT) returnează un link dat de un șir de text, îl puteți folosi pentru a:
- Creați o legătură inițială care nu se schimbă.
- Creați o referință la un interval denumit static.
- Creați un link utilizând informații despre foaie, rând și coloană.
- Creați o matrice de numere care nu se schimbă.
Sintaxă INDIRECT (INDIRECT)
Funcţie INDIRECT (INDIRECT) are următoarea sintaxă:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) este textul linkului.
- a1 – dacă este egal cu TRUE (TRUE) sau nu este specificat, atunci stilul link-ului va fi folosit A1; iar dacă FALSE (FALSE), atunci stilul R1C1.
Capcane INDIRECT (INDIRECT)
- Funcţie INDIRECT (INDIRECT) este recalculat ori de câte ori se schimbă valorile din foaia de lucru Excel. Acest lucru vă poate încetini foarte mult registrul de lucru dacă funcția este utilizată în multe formule.
- Dacă funcţia INDIRECT (INDIRECT) creează un link către un alt registru de lucru Excel, acel registru de lucru trebuie să fie deschis sau formula va raporta o eroare #REF! (#LEGĂTURĂ!).
- Dacă funcţia INDIRECT (INDIRECT) face referire la un interval care depășește limita de rând și coloană, formula va raporta o eroare #REF! (#LEGĂTURĂ!).
- Funcţie INDIRECT (INDIRECT) nu poate face referire la un interval denumit dinamic.
Exemplul 1: creați o legătură inițială fără deplasare
În primul exemplu, coloanele C și E conțin aceleași numere, sumele acestora fiind calculate folosind funcția USM (SUM) sunt de asemenea la fel. Cu toate acestea, formulele sunt ușor diferite. În celula C8, formula este:
=SUM(C2:C7)
=СУММ(C2:C7)
În celula E8, funcția INDIRECT (INDIRECT) creează o legătură către celula de pornire E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Dacă introduceți un rând în partea de sus a foii și adăugați valoarea pentru ianuarie (ian), atunci suma din coloana C nu se va modifica. Formula se va schimba, reacționând la adăugarea unei linii:
=SUM(C3:C8)
=СУММ(C3:C8)
Cu toate acestea, funcția INDIRECT (INDIRECT) fixează E2 ca celulă de început, deci ianuarie este inclus automat în calculul totalurilor coloanei E. Celula finală s-a schimbat, dar celula de început nu a fost afectată.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Exemplul 2: Link către un interval denumit static
Funcţie INDIRECT (INDIRECT) poate crea o referință la un interval numit. În acest exemplu, celulele albastre formează intervalul NumList. În plus, se creează și un interval dinamic din valorile din coloana B NumListDyn, în funcție de numărul de numere din această coloană.
Suma pentru ambele intervale poate fi calculată dând pur și simplu numele său ca argument pentru funcție USM (SUM), după cum puteți vedea în celulele E3 și E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
În loc să tastați un nume de interval într-o funcție USM (SUMĂ), Vă puteți referi la numele scris într-una dintre celulele foii de lucru. De exemplu, dacă numele NumList este scrisă în celula D7, atunci formula din celula E7 va fi astfel:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Din pacate functia INDIRECT (INDIRECT) nu poate crea o referință de interval dinamic, așa că atunci când copiați această formulă în celula E8, veți primi o eroare #REF! (#LEGĂTURĂ!).
Exemplul 3: creați un link utilizând informații despre foaie, rând și coloană
Puteți crea cu ușurință o legătură bazată pe numerele rândurilor și coloanelor, precum și folosind valoarea FALSE (FALSE) pentru al doilea argument al funcției INDIRECT (INDIRECT). Acesta este modul în care este creată legătura de stil R1C1. În acest exemplu, am adăugat suplimentar numele foii la link – „MyLinks”!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Exemplul 4: Creați o matrice de numere care nu se schimbă
Uneori trebuie să utilizați o serie de numere în formulele Excel. În exemplul următor, dorim să facem media celor trei numere cele mai mari din coloana B. Numerele pot fi introduse într-o formulă, așa cum se face în celula D3:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Dacă aveți nevoie de o matrice mai mare, atunci este puțin probabil să doriți să introduceți toate numerele în formulă. A doua opțiune este utilizarea funcției RÂND (RÂND), așa cum se face în formula matrice introdusă în celula D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
A treia opțiune este utilizarea funcției RÂND (STRING) împreună cu INDIRECT (INDIRECT), așa cum se face cu formula matrice din celula D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Rezultatul pentru toate cele 3 formule va fi același:
Cu toate acestea, dacă sunt inserate rânduri în partea de sus a foii, a doua formulă va returna un rezultat incorect datorită faptului că referințele din formulă se vor schimba odată cu deplasarea rândului. Acum, în loc de media celor trei cele mai mari numere, formula returnează media celor trei, al patrulea și al cincilea cel mai mare numere.
Utilizarea funcțiilor INDIRECT (INDIRECT), a treia formulă păstrează referințele de rând corecte și continuă să arate rezultatul corect.