Adunare din multiple Worksheet cu conditii multiple

[silvi]

Buna,

Am rasfoit pe aici postari legate de adunarea datelor din mai multe foi de calcul, cu acelasi cap de coloana in scopul centralizarii lor intr-o alta foaie (acelasi workbook) , in functie de 2 sau 3 criterii.

Am gasit indicii cum ca cea mai indicata functie ar fi cea de genul
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng"))), dar nu reusesc sa ii dau de capat sub nicio forma.

Daca gasiti un minut sa aruncati o privire, v-as fi recunoscatoare.

Practic trebuie sa adun in foaia CENTRAL toate valorile din coloada D, din ambele foi, suc1 si suc2, daca sunt din acelasi proiect, pe acelasi departament si din aceeasi luna. Am incercat cu o singura conditie (proiect) si imi da 0.

Conditiile ar fi Proiect, departament, luna, (eventual si an).

Atasez fisier pentru orientare.

Multumesc pentru orice ajutor!

[IPP]

Buna ziua

Inainte de toate si ca principiu: atunci cand aveti la dispozitie instrumentul Table care se autodimensioneaza in functie de informatia introdusa, e gresit sa definiti in name manager sau sa faceti referinte in formule la nivel de intreaga coloana. Pentru exemplul dvs. acest lucru a insemnat o crestere a dimensiunii fisierului cu vreo 8% la fel si cresterea timpului de salvare sau recalculare eventuale formule.
In aceeasi idee, in formula de mai jos am pus referinte pana la randul 100, dvs. le puteti inlocui in functie de realitate insa nu va recomand sa folositi din start randul 1048536.

O varianta ar fi sa folositi o adunare de rezultate a unor functii sumifs, vedeti in fisierul atasat formula din C3 ce poate fi copiata spre dreapta si in jos atat cat e cazul
=SUMIFS('suc1'!$D$2:$D$100;'suc1'!$G$2:$G$100;CENTRAL!$B$2;'suc1'!$F$2:$F$100;CENTRAL!$B3;'suc1'!$H$2:$H$100;CENTRAL!C$2)+SUMIFS('suc2'!$D$2:$D$100;'suc2'!$G$2:$G$100;CENTRAL!$B$2;'suc2'!$F$2:$F$100;CENTRAL!$B3;'suc2'!$H$2:$H$100;CENTRAL!C$2)

Daca aveti mai multe foi-sursa eu as incerca un macro care sa aduca toata informatia intr-o singura foaie si de acolo se poate folosi un pivot table.

IP

[silvi]

Multumesc frumos, IPP, pentru timpul acordat, speram sa gasesc o formula mai scurta.

[TudyBTH]

Buna,

Aveti atasata si o varianta cu TABELE (in loc de data range) in suc1 si suc2

Capture.JPG

[IPP]

Buna ziua

@TudyBTH
Banuiesc ca ati folosit Power Query. In acest caz, cand ati facut Apend Query, cum ati evitat o adaugare gresita a informatiei dat fiind faptul ca denumirea de coloana "Proiect" apare la sursa in primul tabel ok iar in al doilea are un spatiu gol la sfarsit?

Multumesc
IP

[TudyBTH]

Buna,

Intocmai (Apend). Solutia este pur demonstrativa pentru ca nu se cunosc multe date relevante referitoare la destinatia rezultatului sau disponibilitatea de a folosi obiectul Table in loc de range, de aceea am inlocuit "Proiect " din al doile tabel cu "Proiect".
In cazul in care aceasta ar deveni o problema reala (diferenta dintre cele doua denumiri), ea poate fi rezolvata in Apend Query cu o coloana custom si o formula de genul:

Cod: Selectaţi tot

= Table.AddColumn(Source, "CombCol3", each if[Column3]=null then [#"Column3 "] else [Column3])

[silvi]

Multumesc tuturor pentru sprijin. eu inca mai lucrez la variants cu SUMIFS si INDIRECT.

Am deslusit formula pana la urma :

am definit ca interval numele foilor, ca sa pot face adunarile din toate foile. doar ca m spre exemplu, in formula evidentiata verde , imi aduna doar valorile dintr-un sheet in functie de P1.
In cea rosie, aduna doar dintr-un sheet valorile functie de p1, D1 si luna 9.

trebuie sa imi insumeze ambele foi, care ar trebui sa aiba rezultatul dublu.
atasez fisier ca exemplu

[TudyBTH]

Incercati urmatoarea formula

Cod: Selectaţi tot

=SUMPRODUCT((INDEX(CHRO;0;7)=$B$2)*(INDEX(CHRO;0;6)=$B3)*(INDEX(CHRO;0;8)=C$2))+SUMPRODUCT((INDEX(CHFR;0;7)=$B$2)*(INDEX(CHFR;0;6)=$B3)*(INDEX(CHFR;0;8)=C$2))

unde:
CHRO=CHRO!$A$2:$I$1564
CHFR=CHFR!$A$2:$I$1564

sunt nume definite pentru cele doua surse

Deoarece nu am gasit in foaia CENTRAL un camp pentru an, am dedus ca nu aveti in cele doua foi decat date referitoare la un singur an (deci formula nu verifica anul)

[Indigo-99]

Multumesc tuturor pentru sprijin. eu inca mai lucrez la variants cu SUMIFS si INDIRECT.

Daca nu ai reusit inca, am sa iti dau o formula cu SUMIFS si INDIRECT.
Oricum erai pe aproape.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$O$2:$O$3&"'!D:D"),INDIRECT("'"&$O$2:$O$3&"'!G:G"),$B$2,INDIRECT("'"&$O$2:$O$3&"'!F:F"),$B3,INDIRECT("'"&$O$2:$O$3&"'!H:H"),C$2)) - pentru primul fisier atasat.

Nu ti-a iesit pentru ca foloseai pentru numele foilor un range cu multe celule goale. Trebuia strict $O$2:$O$3 (ptr. suc1 si suc2)
Daca doresti sa mai adaugi foi, in viitor, creaza un name range dinamic si inlocuieste $O$2:$O$3 cu numele pe care l-ai dat (ex. NumeFoi).

SFAT: Nu folosi toata coloana ca range adica A:A sau G:G aici poti folosi si celule goale si poti alege un range mai mare decat ai date.
Sa zicem ca ai date in G2:G20 dar poti sa pui un range mai mare daca stii ca o sa ai mai multe date in viitor (Ex. G2:G100, G2:G1000...)

Eu in formula data am lasat toata coloana, ca sa poti vedea ca formula functioneaza, dar functie de formulele folosite iti va incetini sistemul deoarece va trebui sa verifice si acele celule goale.

[silvi]

Buna, Indigo-99,

Functioneaza super!

Iti multumesc foarte mult atat pentru corectarea formulei, cat si pentru indicatii (ar fi durat mult pana sa imi dau seama). O sa studiez range-urile dinamice.

Multumesc mult din nou! Duminica faina tuturor!