Functia INDIRECT()
Funcţia INDIRECT() este mai puţin cunoscută.
Poate fi folosită la centralizatoare, atunci când se doreşte să se preia date din alte foi de lucru, folosind numele foilor de lucru.
Diferă de funcţia VLOOKUP, care foloseşte o valoare de căutare şi este mai rapidă decât atunci când se preia valoarea cerută folosind = (egal) şi click pe celula din foaia de lucru.
Un exemplu simplu
Să presupunem că aveţi un fişier în care se află 30 de foi de lucru, fiecare având ca nume numărul de inventar (sau codul produsului, nr.matricol etc.) şi că doriţi să le centralizaţi într-o altă foaie de lucru, numită Centralizator, la finalul aceluiaşi fişier.
Condiţia este ca foile de lucru sa fie identice ca structură, adică valoarea care urmează să fie preluată în centralizator să se afle în aceeaşi celulă în foile de lucru.
De obicei, acest lucru se face prin realizarea unei foi de lucru şablon care se copie în fişier de câte ori este nevoie (o metodă rapidă de copiere: se ţine apăsată tasta CTRL şi se trage eticheta la dreapta cu mouse-ul, având butonul din stânga apăsat - drag&drop; apoi se redenumeşte foaia de lucru).
Copierea șablonului se mai poate face și folosind o macrocomandă.
Un exemplu simplu de fişă:
Dorim ca funcţia INDIRECT() să preia datele din celulele B3 (denumirea) şi B8 (valoarea de intrare) ale fiecărei fişe şi să le introducă în foaia de lucru centralizator, în coloanele corespunzătoare denumirii şi valorii de inventar.
La început, centralizatorul conţine doar numerele de inventar. Celelalte coloane vor fi umplute folosind funcţia INDIRECT().
În partea de jos a imaginii se pot observa etichetele care indică numărul de inventar. Desigur, numele poate fi si mai detaliat - de ex. 2008 vitrina frigorifica.
În orice caz, coloana cu nr. inventar va trebui să conţină EXACT numele etichetei, altfel funcţia va întoarce eroarea #REF!.
Pentru a prelua denumirea produsului din fişa acestuia se foloseşte formula
=INDIRECT("'"&C5&"'!B3")
unde
C5 este numele foii de lucru
B3 este celula din care va fi preluată denumirea produsului.
Pentru a prelua valoarea de intrare din fişă, se foloseşte formula
=INDIRECT("'"&C5&"'!B8")
Aici se modifică doar celula din foaia de lucru - B8.
Atenţie!
Funcţia conţine deopotrivă ghilimele duble şi simple. Cel mai simplu este să copiaţi formula de mai sus şi să modificaţi (dacă este cazul) doar adresele celulelor la care face referire funcţia:
- C5 (celula în care se află numărul de inventar în acest caz) şi
- B3 sau B8 (celulele în care se află denumirea şi valoarea de inventar).
În imaginea de mai jos, ghilimelele duble sunt marcate cu roşu.
Altfel zis, în formula de mai sus, îi spunem aplicaţiei Excel:
- să meargă în foaia de lucru al cărui nume îl găseşte în celula C5 şi
- să preia de acolo valoarea din celula B8.
Dacă formula îşi face treaba, se copie funcţia în celulele de mai jos, prin tragere cu mouse-ul având butonul din stânga apăsat.
O altă variantă, cu acelaşi rezultat, este combinaţia de funcţii INDIRECT şi ADDRESS.
O descriere completă în română se află pe pagina
Funcția se poate folosi și ”invers”, prin preluarea datelor dintr-un centralizator și adăugarea lor în alte foi de lucru.
În imaginea de mai sus, valorile au fost preluate în funcție de nr. crt.
Foaia ”1” ia valori de pe rândul cu nr. crt. 1, foaia ”2” - de pe rândul cu nr. crt. 2 etc.
Pentru a afla adresa și numele foii de lucru active se folosește funcția =CELL("filename",A1).
Însă formula are ca rezultat întreaga cale a fișierului, de exemplu
C:\teste EXCEL\[indirect.xlsx]1
Pentru a obține doar numele foii de lucru (aici 1), se pot folosi funcțiile MID și FIND, unde
- MID va extrage doar o parte a rezultatului funcției CELL și
- FIND va indica de la care caracter va începe extragerea (aici FIND caută caracterul ”]”).
În acest caz, nr. inventar este preluat de pe coloana C, iar valoarea, de pe coloana D.
Aici, numărul rândului este dat de 2+numele foii de lucru.
Formulele folosite în exemplul de mai sus pentru preluare valori în foile de lucru sunt:
="Nr. inv: "&INDIRECT("'centralizator'!C"&(2+(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))))
și
="Valoare: "&INDIRECT("'centralizator'!D"&(2+(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))))