OFFSET() este o funcție complexă care poate fi folosită în căutare în mod singular (extrage o singură valoare) sau în mod array pentru extragerea mai multor valori dintr-un bloc de celule.
Sintaxa: OFFSET(reference, rows, cols, [height], [width])
Referința reprezintă locul din foaia de calcul de raportare la indexul de căutare.
În cazul foii de calcul date, putem alege celula A2 ca referință, aceasta devenind în modelul prezentat coloana 0 (zero) linia 0 (zero). Comparativ VLOOKUP() folosește numerotarea de la 1 a coloanelor la fel ca și INDEX() și MATCH().
Parametrii obligatorii rows și cols specifică numărul de linii, respectiv coloane de la care se realizează extragerea datelor în raport cu referința specificată.
Exemplu:
=OFFSET(A2;0;0) va extrage valoarea 1, valorile 0;0 precizând că se va extrage valoarea exact din celula de referință.
=OFFSET(A2;1;1) va extrage valoarea 7, iar
=OFFSET(A2;4;4) valoarea 25.
În contextul în care dorim extragerea ultimei valori din cadrul unui bloc de celule putem folosi uneori și funcțiile ROWS() și COLUMNS() din care trebuie să scădem valoarea 1.
În exemplul nostru se poate înlocui =OFFSET(A2;4;4) cu:
=OFFSET(A2;ROWS($A$2:$E$6)-1;COLUMNS($A$2:$E$6)-1)
În care ROWS($A$2:$E$6) și COLUMNS($A$2:$E$6) vor returna valoarea 5.
Parametrii height și width sunt opționali și presupun definirea unui bloc dinamic de căsuțe, perimetru, pentru care dorim să efectuăm operațiuni sau din care să extragem date.
De exemplu, dacă dorim să însumăm datele din primele 4 celule ale tabelului nostru de date putem folosi formula:
=SUM(OFFSET(A2;0;0;2;2)) rezultatul acesteia fiind 16 = 1+2+6+7.
Dacă folosim formula:
=SUM(OFFSET(A2;1;1;2;2)) vom obține rezultatul 40 pentru că se face referire la blocul de celule B3:C4.
Pentru extragerea de date cu utilizarea parametrilor opționali este obligatoriu să scriem OFFSET() în mod array.
De exemplu, dacă dorim să extragem într-o zonă distinctă valorile dintr-un tabel sub forma altui tabel de alt perimetru, va trebui să:
1) Selectăm blocul de celule de destinație, în cazul nostru dorim să extragem un bloc de valori de 3 coloane și 2 linii începând cu valoarea din celula A2:
2) Tastăm formula: =OFFSET(A2;0;0;2;3)
În care: 0;0; reprezintă poziția de start față de A2, 2 numărul de linii și 3 numărul de coloane.
3) Apăsăm combinația de taste CTRL+SHIFT+ENTER.
Dacă vom selecta o singură celulă sau un bloc de celule mai mic decât perimetrul 2×3 vom obține valori corespondente doar pentru blocul selectat. Dacă selectăm un număr mai mare de căsuțe decât blocul destinație vom obține în celulele din afara perimetrului 2×3 eroarea #N/A.
Cunoscând astfel structura parametrilor obligatorii putem specifica în mod dinamic numărul liniei și a coloanei de la intersecția cărora dorim să extragem o valoare.
În articolul Excel IF() – Insane Level utilizatorul Radu G. propusese o formulă de rezolvare matricială a problemei de calcul a adaosului comercial pentru două intervale de valori, prin identificarea valorii procentului prin combinare cu două formule MATCH().
Un exemplu mai simplu față de cel prezentat: dacă din tabelul cu tabla înmulțirii dorim să extragem valoarea calculată de la intersecția liniilor cu coloanelor, folosind o zonă distinctă de introducere a datelor, va trebui să folosim două formule MATCH() pentru parametrii rows și cols.
Pentru a extrage în mod dinamic toate valorile de pe o linie pe baza unei valori date, va trebui să folosim din nou tehnica array: selectăm 10 celule în zona de extragere, scriem formula cu MATCH() pentru parametrul rows, și apăsăm combinația CTRL+SHIFT+ENTER.
În care:
– MATCH(O7;A3:A12;0) va returna numărul liniei pe care se află numărul introdus în celula O7,
– Prima valoare 1, reprezintă numărul coloanei de la care să înceapă extragerea în raport cu referința A2,
– A doua valoare 1 reprezintă înălțimea blocului de extragere, în cazul nostru dorim valori doar de pe o singură linie,
– 10 reprezintă numărul ce cifre/celule rezultat.
Aplicația 1 – Determinarea numărului de ore alocate activităților dintr-o etapă
Foarte multe aplicații economice în Excel conțin date denormalizate, dar care au o logică concretă pentru autorul datelor.
Una din aplicațiile comune mai multor domenii de activitate este aceea de înregistrare a efortului pentru diferite etape de activitate.
Zona de activități este denumită ca un tabel cu numele tblEtape. Nu știm câte activități pot fi într-o etapă și în același timp aplicația permite adăugarea de etape noi în lista de activități.
Raportul de ore pe etape presupune să însumăm orele de pe coloana Număr ore, specific fiecărei etape.
Soluții alternative
Probabil principala metodă de calcul ar fi aceea de a introduce o nouă linie în tabel sub fiecare etapa. Acest lucru presupune alterarea structurii normale ale tabelului și în același timp nu este o operațiune care poate fi replicată și pentru alte fișiere cu aceeași structură/conținut, la fiecare dintre ele fiind nevoie de introducerea unei linii noi.
Utilizarea unui tabel pivot nu este posibilă din cauză că avem linii care nu conțin valori la Etapa.
Specific pentru numele activităților din tabelul de exemplu s-ar putea forța o soluție cu SUMIF() cu criteriul de căutare cu wildcard. Pentru etapa de analiză formula ar fi: =SUMIF(tblEtape[Activitate];”Activitate A*”;tblEtape[Număr ore])
Acestă funcție funcționează doar în acest caz particular și se bazează pe faptul că toate numele de activități din Analiză încep cu șirul “Activitate A”, simbolul asterix (*) fiind utilizat pentru tot ce este scris după acest șir.
Soluția propusă pentru raportul de ore
Constrângerea aplicației este ca etapele trecute în raport să fie în aceeași ordine ca cele din tabelul de activități.
Provocarea este de a identifica numărul de linii de activități pentru fiecare etapă. Ele pot varia și în acest context nu avem cum să specificăm concret înălțimea blocului de cellule de pe care se realizează suma. De asemenea, trebuie să identificăm exact dacă o etapă este ultima sau nu, formula de calcul fiind diferită pentru etapele intermediare față de ultima etapă din raport.
Folosind tehnica coloanelor intermediare vom calcula numărul de linii dintre două etape, iar dacă etapa este ultima în listă vom calcula numărul de linii totale ale tabelului de activități:
Pe coloana Match din tabelul de rezultat avem formula: =MATCH(F3;tblEtape[Etapa];0) care va determina poziția numelui etapei în lista de Etape. Funcția de pe coloana Câte linii? Începe cu IFNA() care verifică faptul dacă etapa specificată în raport este ultima în listă sau nu. Dacă rezultatul funcției MATCH() este #N/A se calculează numărul de linii prin numărarea liniilor totale din table minus poziția ultimei etape din raport în tabelul de activități.
Cumulând totul într-o singură celulă vom obține suma totală a orelor pentru fiecare etapă.
Formula pentru celula G3 devine astfel:
=SUM(OFFSET($A$2;MATCH(F3;tblEtape[Etapa];0);2;IFNA(MATCH(F4;tblEtape[Etapa];0);ROWS(tblEtape[Activitate])+1)-MATCH(F3;tblEtape[Etapa];0);1))
OFFSET-ul determină un număr de celule relative la numele etapei specificată în raport.
Tehnica de a obține automat numele etapelor în raport nu este posibilă din cauza faptului că nu sunt completate numele etapelor pe fiecare linie (tabel denormalizat).
Notă: Pentru ușurința realizării rapoartelor recomandăm întotdeauna completarea tuturor celulelor din carul unui tabel și reducerea cât mai mare a numărului de celule merged.
Pentru a testa și/sau completa modelul puteți să descărcați fișierul de aici.
Aplicația 2 – Identificarea unui număr pe două intervale de valori cu condiție suplimentară
Problemele de determinare a anumitor valori specifice pe baza combinării unor de intervale date de valori, reprezintă un model de aplicații economice cu utilizare foarte largă. Majoritatea utilizatorilor de Excel folosesc funcția IF() pentru determinarea acestor valori. Din punct de vedere tehnic apar o serie de neajunsuri legate de dimensiunea intervalelor, plus probleme de complexitate în contextul introducerii altor constrângeri.
În exemplul de mai jos avem un tabel de discount-uri determinat pe stocul existent de produse precum și pe cantitatea solicitată prin comandă. Valorile sunt defalcate pe fiecare categorie de produs. Valorile de discount sunt pur orientative dar au în vedere că pentru stocuri mai mari acordăm discount-uri mai mari. De asemenea, pentru cantități comandate mai mari avem discount-uri crescute.
În exemplul nostru, la un stoc de până la 500 de bucăți din categoria altele, acordăm un discount de 19 pentru cantități între 10-15 produse comandate.
Singura soluție alternativă pe care o identific este aceea de a realiza IF-uri pe intervale pe categorii de produse. Optim în acest model de rezolvare este să pornim de la tipul categoriei de produs: Dulciuri sau altele, după care să replicăm IF-urile pe celelalte două intervale (Stoc și Cantitate).
Celulele B9:B11 sunt celule de input pentru valori: Stocul existent, cantitatea comandată și categoria de produs.
Celula D10 conține valoarea indexată din tabel determinată prin aplicarea unei funcții OFFSET.
Provocarea este de identifica exact poziția de căutare din tabel, iar pentru a explica modul de formare a formulei finale folosim 3 funcții MATCH() intermediare:
Formulele pentru intermediar sunt:
G9: =MATCH($B$9; $A$4:$A$7; 1) – determină numărul liniei din tabelul de date în care se încadrează stocul. Valoarea 101 va oferi rezultatul 1 pentru că este echivalent linei 1 din tabel, parametrul 1 din MATCH() semnificând valoare mai mică decât valoarea de pe linie.
G10: =MATCH($B$11;$B$2:$I$2;0) – determină poziția categoriei în tabelul de date. Chiar dacă celulele sunt merged, valoarea rezultat va fi 1 pentru Dulciuri și 5 pentru Altele. Această valoare ne ajută să specificăm OFFSET-ului care este numărul coloanei din tabelul cu date de la care se va executa extragerea discount-ului.
G11: =MATCH(B10;$B$3:$E$3;1) – determină coloana din tabelul cu date de la care se va face extragerea prin combinare cu valoarea determinată de valoarea categoriei.
Simplificat, funcția specifică din celula D10 va fi: =OFFSET(A3;G9;G10+G11-1)
În mod combinat fără, coloane intermediare, funcția din D10 este:
=OFFSET(A3;MATCH($B$9; $A$4:$A$7; 1);MATCH($B$11;$B$2:$I$2;0)+MATCH(B10;$B$3:$E$3;1) -1)
Pentru a testa și/sau completa modelul puteți să descărcați fișierul de aici.
Sper să vă fie util!
Referințe suplimentare:
Valy Greavu - Live Blog 