Imaginați-vă că avem câteva cifre de vânzări pentru o companie:
Și că dorim să găsim cifrele totale pentru anul până în prezent. Putem adăuga o listă verticală astfel:
Pentru a putea specifica luna curentă. Prin urmare, acum vrem să stabilim anul până în prezent pentru luna martie. Cel mai simplu format ar fi să existe o formulă care să se extindă pe întreaga gamă:
Și apoi am schimba doar formulele în fiecare lună.
Cu toate acestea, Excel permite o altă abordare. Am putea configura un interval dinamic a cărui dimensiune variază în luna în care ne aflăm. Pe măsură ce schimbăm luna în meniul derulant, dimensiunea intervalului se schimbă.
Deci, pentru luna martie, intervalul are o lungime de 3 coloane, iar pentru luna iunie ar fi de 6 luni.
Mărimea intervalului este guvernată de lună. O modalitate de a formula acest lucru este de a utiliza funcția Lună:
= Lună (c8)
Unde c8 este adresa celulei din meniul nostru derulant. Totuși, metoda preferată este utilizarea funcției MATCH pentru a determina poziția lunilor curente în toate lunile din raportul nostru:
MATCH (c8, $ c $ 3: $ j $ 3,0)
Unde:
• c8 este adresa celulei din luna curentă
• C3: J3 este adresa tuturor lunilor noastre
• 0 este pentru a asigura o potrivire exactă
Acum putem specifica dimensiunea intervalului nostru dinamic prin funcția OFFSET care are 5 argumente:
= OFFSET (referință, rânduri, coluri, înălțime, lățime)
Unde:
• Referința este colțul din stânga sus al gamei noastre dinamice - celula C5 - prima celulă pe care dorim să o însumăm
• Rânduri - numărul de rânduri în jos din celula noastră de bază - acesta este 0
• Cols - numărul de cols vizavi de apelul nostru de bază - acesta este 0
• Lățimea intervalului nostru dinamic - care este 3 în acest caz. Cu toate acestea, deoarece dorim ca intervalul să varieze în funcție de lună, vom pune aici formulele noastre MATCH
• Aceasta este înălțimea intervalului nostru dinamic, care este 1
Deci, formulele noastre OFFSET sunt:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
În cele din urmă, trebuie să-i spunem Excel să SUME acest lucru pentru a da formulele complete ca:
= SUM (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
Avem:
Acum, dacă schimbăm luna din meniul derulant, cifra corectă din an până în prezent curge prin:
Deoarece aceasta este o actualizare automată, această abordare are următoarele avantaje:
• Nu este nevoie să modificați formulele în fiecare lună
• Deoarece există mai puține modificări ale formulelor, mai puține posibilități de eroare
• Foaia de calcul poate fi utilizată de cineva care are cunoștințe Excel limitate - poate schimba doar meniul derulant și nu poate fi deranjat de formule
