Functia OFFSET

[Criss]

Buna tuturor.
Poate cineva sa explice functia OFFSET ? Am citit ce a fost publicat pe Dr.Excel , am citit si ce am mai gasit pe internet , dar nu am inteles mare lucru.
Multumesc mult.

[gecs]

Am sa incerc eu si incep cu un exemplu:

=OFFSET($A$1,2,3,10,5)

va returna referinta la range-ul $D$3:$H$12. Intr-o formulare in cuvinte, interpretand argumentele folosite in formula de mai sus, asta ar insemna:

returneaza referinta la range-ul care are 10 randuri si 5 coloane si a carui celula din stanga-sus se afla la 2 randuri si 3 coloane distanta de A1.

Semnificatia argumentelor:
- primul argument este o referinta si poate fi privit ca un punct de reper - de la acea celula se interpreteaza urmatoarele 2 argumente;
- urmatoarele doua argumente reprezinta distanta in randuri si coloane de la "reper" la inceputul (celula din stanga-sus) range-ului returnat de functie;
- al treilea si al patrulea argument reprezinta numarul de randuri si numarul de coloane pe care il va avea range-ul returnat de functie.

Avantajele acestei functii stau de fapt in posibilitatea de "parametrizare" a oricaruia dintre argumente. Tot un exemplu - cel "clasic" pentru definirea unui range dinamic:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100))

va returna range-ul care incepe in Sheet1!$A$1 si are atatea randuri cate va returna COUNTA(Sheet1!$A$1:$A$100) si o singura coloana (daca al cincilea argument lipseste, se considera ca e implicit 1). COUNTA(range) returneaza numarul de celule care nu sunt lipsite de continut din range-ul furnizat ca argument. Daca folosim formula de mai sus si avem valori (sau formule) in primele 3 randuri ale coloanei A, referinta returnata de OFFSET va fi Sheet1!$A$1:$A$3. Daca mai adaugam valori si in A4 si in A5, formula va returna referinta Sheet1!$A$1:$A$5. Daca la astea mai adaugam o valoare in A7, formula va returna referinta la range-ul Sheet1!$A$1:$A$6.

Mai putem folosi functia OFFSET pentru a "sectiona" un range. Exemplu:

=OFFSET($A$1:$E$10,1,1,3,2)

va returna referinta la range-ul $B$2:$C$4. Asta poate ca suna inutil, pentru ca acelasi lucru il obtinem si daca primul argument al lui OFFSET e $A$1, dar daca range-ul $A$1:$E$10 e din alta foaie si denumit, sa spunem, "range1", formula:

=OFFSET(range1,1,1,3,2)

va returna referinta la acelasi range $B$2:$C$4, dar din foaia unde se afla range-ul respectiv denumit "range1", fara sa fie nevoie sa stim unde incepe acel range si in ce foaie anume se afla.

Un alt exemplu: daca pe coloana A, din A1 in A10 avem o serie de numere, iar pe primul rand, incepand cu B1, dorim sa obtinem sumele acestor numere in functie de numarul coloanei (in B1 suma primelor 2 numere, in C1 suma primelor 3 numere s.a.m.d.), putem sa folosim tot o formula pe baza de OFFSET:

=SUM(OFFSET($A$1,0,0,COLUMN()))

formula de mai sus se scrie in B1 si se copiaza pana in J1.

Se pot imagina multe exemple de folosire a lui OFFSET pentru a generaliza anumite formule astfel incat sa le putem copia fara grija pe un rand sau o coloana intreaga a unui tabel, fara sa trebuiasca sa modificam apoi fiecare formula in functie de pozitia ei in foaie, dar pentru o mai buna intelegere a functionarii lui OFFSET cred ca cel mai bine ar fi sa pui intrebari specifice si iti vom raspunde cu placere.

[sarighiol]

Numele „_incasari” din „Manager nume” se referă la următoarele funcții:

=OFFSET(Venit_Chelt_2011!$F$3;0;0;COUNTA(Venit_Chelt_2011!$A$3:$A$5098))

„Venit_Chelt_2011” este o foaie de lucru Excel. „Venituri_2011” este o altă foaie. În această foaie există formula:

=IF(SUMPRODUCT(--(_explic=$A3);--(_data_l=COLUMN()-1);_incasari)=0;"";SUMPRODUCT(--(_explic=$A3);--(_data_l=COLUMN()-1);_incasari))

„_explic” și „data_l” sunt alte două nume, vizibile în „Manager nume”. „_explic” este folosit în coloana cu explicații din „Venit_Chelt_2011”, unde se află date validate dintr-o listă cu numele cheltuielilor aflată în altă foaie, iar „data_l” face referire la coloana din „Venit_Chelt_2011” în care scriu cu cifre luna înregistrării veniturilor.
În foaia „Venit_Chelt_2011” înregistrez veniturile la rând, în funcție de dată; pe o singură coloană înregistrez și veniturile prin casă și veniturile prin contul bancar. În „Venituri_2011” ele se totalizează pe anumite categorii, date de „_explic”, care le adună pe rânduri, și de luna în care am înregistrat veniturile, fiind câte o coloană pentru fiecare lună.
Începând cu anul 2012 trebuie să separ în foaia „Venit_Chelt_2012”veniturile prin casă de cele prin bancă, ca să pot avea evidență separată a veniturilor, pe lângă evidența totală. Coloana veche cu toate încasările am numit-o „Încasări prin casă”, iar lângă ea am inserat încă o coloană pe care am numit-o „Încasări prin bancă”. Pentru a se aduna și ea în „Venituri_2012”, am intrat la numele „_incasari” din „Manager nume” și am adăugat în referință ceea ce este cu roșu mai jos:

=OFFSET(Venit_Chelt_2011!$F$3;0;0;1;COUNTA(Venit_Chelt_2011!$A$3:$A$5098))

Excel nu a vrut să recunoască, în „Venituri_2012” toate celulele sunt pline cu #VALOARE!.
Unde am greșit? Cum altfel pot determina programul să-mi facă adunarea pe două coloane în loc de una?
Am atașat registrul la acest mesaj.

[gecs]

Din cate inteleg eu, ai gresit si la OFFSET (cand ai modificat) si la SUMPRODUCT (ca n-ai modificat).

Formula:

Cod: Selectaţi tot

=OFFSET(Venit_Chelt_2011!$F$3;0;0;1;COUNTA(Venit_Chelt_2011!$A$3:$A$5098)) 

iti returneaza un range de 1 singur rand si cate coloane reies din rezultatul returnat de COUNTA(Venit_Chelt_2011!$A$3:$A$5098). Ma intreb daca ai inteles explictiile mele de mai sus... Al patrulea argument al lui OFFSET specifica cate randuri are range-ul returnat, ori daca scrii acolo 1, iti va returna un range cu 1 rand. Al cincilea argument al lui OFFSET specifica cate coloane sa aiba range-ul returnat, ori in formula aia tu numeri randuri cu COUNTA.

Din explicatiile tale inteleg ca vrei sa aduni conditionat numerele de pe coloanele F si G din foaia Venit_Chelt_2012. In situatia asta ai trei variante:

1. Sa faci o suma de 2 SUMPRODUCT-uri - primul cu al treilea argument reprezentat de numele _incasari_casa, care refera range-ul cu date de pe coloana F din foaia Venit_Chelt_2012 (editezi numele _incasari in Name Manager); al doilea SUMPRODUCT cu al treilea argument reprezentat de numele _incasari_cont, care refera range-ul cu date de pe coloana G din foaia Venit_Chelt_2012 (creezi noul nume in Name Manager si poti folosi aceeasi formula ca la _incasari_casa, dar cu al treilea argument cu valoarea 1 in loc de 0);
2. Lasi un singur SUNPRODUCT in formula, dar aduni valorile din cele doua range-uri _incasari_casa si _incasari_cont intr-un nume nou, tot in Name Manager. Numele nou poate fi _incasari_total si folosesti formula

   Cod: Selectaţi tot

   =_incasari_casa+_incasari_cont

3. Sa nu mai definesti in Name Mnageer decat numele _incasari_cont si sa faci adunarea celor doua range-uri in SUMPRODUCT:

   Cod: Selectaţi tot

   SUMPRODUCT(--(_explic=$A3),--(_data_l=COLUMN()-1),(_incasari_casa+_incasari_cont))

Cand folosesti SUMPRODUCT trebuie sa ai grija ca toate argumentele sa aiba aceeasi dimensiune. Daca sunt range-uri-coloana, toate sa aiba acelasi numar de randuri, iar daca sunt range-uri-rand, sa aiba acelasi numar de coloane. De asemenea, nu poti folosi in aceeasi functie si range-uri-coloana si range-uri-rand - trebuie sa fie de un singur fel, ori coloana, ori rand.

In alta ordine de idei, in tabelul din foaia Venituri_2012, pe primele doua randuri ai pe coloana A:
Disponibil din anul precedent în casă
Disponibil din anul precedent în bancă
in afara de faptul ca ai scris inversat valorile, cred ca si formularea nu e cea mai buna - probabil ca ar fi mai bine:
Disponibil din luna precedenta în casă
Disponibil din luna precedenta în bancă
daca folosesti formule pentru fiecare luna si in situatia asta, in tabelul din foaia Venit_Chelt_2012, pe coloana [EXPLICATII] va trebui sa ai o inregistrare cu Disponibil din luna precedenta în casă pentru fiecare luna, iar pe colana [ÎNCASĂRI ÎN CONTUL BANCAR] a acelei inregistrari sa fie valoarea 0 si o inregistrare cu Disponibil din luna precedenta în bancă pentru fiecare luna, iar pe colana [ÎNCASĂRI PRIN CASĂ] a acelei inregistrari sa fie valoarea 0. In felul asta poti folosi aceeasi formula in tot tabelul din foaia Venituri_2012.

[sarighiol]

Mulțumesc foarte mult! Din nou m-ați salvat din încurcătură.
Varianta nr. 3 pe care mi-o descrieți am încercat-o. Am definit pe lângă numele vechi _incasari, încă un nume _incasari_cont și l-am adăugat la SUMPRODUCT. Am făcut însă o greșeală: în loc să scriu _incasari+_incasari_cont, am scris _incasari;_incasari_cont La matematică dacă greșești semnul, greșești tot!
Deasemenea am greșit și la al cincilea argument al lui OFFSET care nu avea cum să funcționeze pe coloane; aici nu înțelesesem, deși oarecum am bănuit.
Am citit și despre SUMPRODUCT, însă nu înțelesesem ce înseamnă ca toate argumentele sa aiba aceeasi dimensiune... adică același număr de rânduri sau coloane.
Contabilitatea noastră este în partidă simplă, dar după cum cred că ați observat deja, există câteva particularități, printre care și faptul că la începutul anului calendaristic prima chitanță tăiată trebuie să fie cu suma disponibilă din anul precedent, operațiune inexistentă în contabilitate din câte mi-au spus alți contabili. De aceea apar acele rubrici. Asta ne-ar mai lipsi, să tăiem lună de lună chitanță cu disponibil din luna precedentă!
Încă o dată vă mulțumim și ne rugăm să vă dea Dumnezeu sănătate să mai ajutați și pe alții din aceștia care au învățat Excel așa „din zbor”!

[gecs]

...
Încă o dată vă mulțumim și ne rugăm să vă dea Dumnezeu sănătate să mai ajutați și pe alții din aceștia care au învățat Excel așa „din zbor”!

Multumim pentru urari, dar si noi, "astialalti", tot "din zbor" am invatat Excel, ca asa e cel mai la-ndemana - totul e sa vrei, sa ai rabdare si sa cauti sa-ntelegi ce faci acolo. Din cate imi dau seama, la capitolul intelegere se constata progrese .

In cazul inregistrarilor cu:
Disponibil din anul precedent în casă
Disponibil din anul precedent în bancă
eu le-as scoate in afara tabelului, pentru ca atunci nu mai vad rostul folosirii formulelor pe lunile febtruarie, martie etc., sau poate nu inteleg eu bine, nefiind contabil de meserie...

[sarighiol]

In cazul inregistrarilor cu:
Disponibil din anul precedent în casă
Disponibil din anul precedent în bancă
eu le-as scoate in afara tabelului, pentru ca atunci nu mai vad rostul folosirii formulelor pe lunile febtruarie, martie etc., sau poate nu inteleg eu bine, nefiind contabil de meserie...

Da, în restul anului doar te încurci de aceste două rubrici, dar în foaia „Venituri 2012” cum poți face ca ele să apară în ianuarie, iar în celelalte luni nu?

[gecs]

Dupa parerea mea, varianta cea mai simpla e sa ramana fisierul asa, iar din februarie sa ascunzi randurile cu acele doua valori.

In fisierul

2012-Registru_jurnal_venituri_și_cheltuieli_și partizuri_1.xls

va trebui sa ascunzi randurile 3 si 4 "de mana" incepand cu februarie.

In fisierul

2012-Registru_jurnal_venituri_și_cheltuieli_și partizuri_1-1.xls

va trebui sa permiti executia macro-urilor si acele randuri se vor ascunde automat, daca data sistemului nu e in ianuarie. Testeaza (modifica data sistemului, mai precis luna, selectezi o alta foaie decat Venituri_2012 si apoi selectezi din nou Venituri_2012) si vezi cum functioneaza (nu uita ca dupa aceea sa modifici data sistemului inapoi, in ianuarie ). Te rog sa revii cu precizarea daca formulele de pe randurile 41 si 42 sunt corecte in situatia asta. Folosind SUBTOTAL cu primul argument cu valoarea 109 in loc de SUM se poate face suma numai pentru valorile vizibile din range-ul furnizat ca al doilea argument.

Ar fi bine sa ne spui si cu ce versiune de Excel lucrezi.

[sarighiol]

Folosesc Excel 2010.
În privința primei variante știu despre ce e vorba.
În privința celei de-a doua variante, suma din rândul 41 nu conținea și disponibilul din anul precedent, iar cea din rândul 42 era corectă.
Problemă ar fi la printat, că dacă printez toată foaia la sfârșitul anului, trebuie să schimb data computerului ca să apară și disponibilul din anul precedent.

[gecs]

În privința celei de-a doua variante, suma din rândul 41 nu conținea și disponibilul din anul precedent, iar cea din rândul 42 era corectă.
Problemă ar fi la printat, că dacă printez toată foaia la sfârșitul anului, trebuie să schimb data computerului ca să apară și disponibilul din anul precedent.

Pai atunci nu vad de ce ar trebui ascunse randurile respective, pentru ca pe lunile celelalte, in afara de ianuarie, nu vor aparea valori pe randurile 3 si 4, fiindca nu exista inregistrari cu aceasta denumire in tabelul din foaia Venit_Chelt_2012 pentru celelalte luni. Dupa parerea mea, dar, repet, eu nu sunt contabil de meserie, nu poti avea o suma nejustificata de numerele care o compun si in situatia asta, daca pe coloana ianuarie e musai ca totalul sa includa si valorile din randurile 3 si 4, atunci e gresit sa ascunzi randurile acelea.

Ar mai fi varianta in care scoti acele doua valori in afara tabelului, ca in fisierul atasat.

Problemă ar fi la printat, că dacă printez toată foaia la sfârșitul anului, trebuie să schimb data computerului ca să apară și disponibilul din anul precedent.

Problema asta n-o inteleg. Cum adica trebuie sa schimbi data computerului? Vorbesti despre varianta cu macro? Se poate ca inainte de printare, fara sa schimbi data computerului, sa faci vizibile cele doua randuri - macro-ul nu va reactiona in sensul de a le ascunde la loc pentru ca data nu e din luna ianuarie, deoarece actiunea macro-ului se produce doar la activarea foii Venituri_2012, adica atunci cand aceasta devine foaia curenta si la deschiderea fisierului.