Cuprins
La prima vedere (mai ales la citirea ajutorului), funcția INDIRECT (INDIRECT) pare simplu și chiar inutil. Esența sa este de a transforma textul care arată ca un link într-un link cu drepturi depline. Acestea. dacă trebuie să ne referim la celula A1, atunci putem fie să facem în mod obișnuit o legătură directă (introduceți un semn egal în D1, faceți clic pe A1 și apăsați Enter), fie putem folosi INDIRECT in acelasi scop:
Vă rugăm să rețineți că argumentul funcției – o referință la A1 – este introdus între ghilimele, astfel încât, de fapt, este text aici.
„Ei bine, bine”, spui tu. „Și care este beneficiul?”
Dar nu judeca după prima impresie – este înșelătoare. Această caracteristică vă poate ajuta în multe situații.
Exemplul 1. Transpune
Un clasic al genului: trebuie să răsuciți diametrul vertical
canelura la orizontală (transpune). Desigur, puteți utiliza o inserție sau o funcție specială TRANSP (TRANSPUNE) într-o formulă matrice, dar vă puteți descurca cu ajutorul nostru INDIRECT:
Logica este simplă: pentru a obține adresa celulei următoare, lipim litera „A” cu caracterul special „&” și numărul coloanei celulei curente, pe care ne-o dă funcția COLOANA (COLOANĂ).
Procedura inversă este mai bine făcută puțin diferit. Deoarece de această dată trebuie să formăm o legătură cu celulele B2, C2, D2 etc., este mai convenabil să folosim modul de legătură R1C1 în loc de clasica „bătălie pe mare”. În acest mod, celulele noastre vor diferi doar în numărul coloanei: B2=R1C2, C2=R1C3, D2=R1C4 etc
Aici intervine al doilea argument al funcției opționale. INDIRECT. Daca este egal MINCIUT (FALS), apoi puteți seta adresa linkului în modul R1C1. Deci, putem transpune cu ușurință intervalul orizontal înapoi la vertical:
Exemplul 2. Suma după interval
Am analizat deja o modalitate de însumare a unei ferestre (interval) de o dimensiune dată pe o foaie folosind funcția ELIMINAREA (DECALAJ). O problemă similară poate fi rezolvată și folosind INDIRECT. Dacă trebuie să rezumăm datele doar dintr-o anumită perioadă, atunci le putem lipi din bucăți și apoi le putem transforma într-o legătură cu drepturi depline, pe care o putem introduce în funcție USM (SUMĂ):
Exemplul 3. Lista verticală de tabel inteligent
Uneori, Microsoft Excel nu tratează numele tabelelor și coloanele inteligente ca legături complete. Deci, de exemplu, când încercați să creați o listă derulantă (fila Date – Validarea datelor) pe baza coloanei Angajați de la masa inteligentă oameni vom primi o eroare:
Dacă „împachetăm” legătura cu funcția noastră INDIRECT, atunci Excel îl va accepta cu ușurință și lista noastră derulantă va fi actualizată dinamic atunci când adăugați noi angajați la sfârșitul tabelului inteligent:
Exemplul 4. Legături de neîntrerupt
După cum știți, Excel corectează automat adresele de referință din formule atunci când inserați sau ștergeți coloane de rând pe o foaie. În cele mai multe cazuri, acest lucru este corect și convenabil, dar nu întotdeauna. Să presupunem că trebuie să transferăm numele din directorul angajaților în raport:
Dacă puneți legături obișnuite (introduceți =B2 în prima celulă verde și copiați-o), atunci când ștergeți, de exemplu, Dasha, vom primi #LINK! eroare în celula verde corespunzătoare ei. (#REF!). În cazul utilizării funcției pentru a crea legături INDIRECT nu va fi o astfel de problemă.
Exemplul 5: Colectarea datelor din mai multe foi
Să presupunem că avem 5 foi cu rapoarte de același tip de la diferiți angajați (Mikhail, Elena, Ivan, Sergey, Dmitry):
Să presupunem că forma, dimensiunea, poziția și succesiunea mărfurilor și lunilor din toate tabelele sunt aceleași - doar numerele diferă.
Puteți colecta date din toate foile (nu le rezumați, ci le puneți una sub cealaltă într-o „grămadă”) cu o singură formulă:
După cum puteți vedea, ideea este aceeași: lipim linkul la celula dorită a foii date și INDIRECT îl transformă într-un „live”. Pentru comoditate, deasupra tabelului, am adăugat literele coloanelor (B,C,D), iar în dreapta – numerele de rând care trebuie luate din fiecare foaie.
capcane
Dacă utilizați INDIRECT (INDIRECT) trebuie să vă amintiți despre punctele sale slabe:
- Dacă conectați la un alt fișier (prin lipirea numelui fișierului între paranteze drepte, numele foii și adresa celulei), atunci funcționează numai în timp ce fișierul original este deschis. Dacă îl închidem, primim eroarea #LINK!
- INDIRECT nu se poate referi la un interval denumit dinamic. Pe statică – nicio problemă.
- INDIRECTA este o funcție volatilă sau „volatilă”, adică este recalculată pentru orice modificare în orice celulă a foii, și nu doar influențarea celulelor, ca în funcțiile normale. Acest lucru are un efect negativ asupra performanței și este mai bine să nu vă lăsați duși de mesele INDIRECTE mari.
- Cum să creați o gamă dinamică cu dimensionare automată
- Însumarea pe o fereastră de interval de pe o foaie cu funcția OFFSET