Adunare din multiple Worksheet cu conditii multiple

Ce este nou in Microsoft Excel 2016?
Informatii despre cum se utilizeaza Microsoft Excel 2016
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
silvi
Mesaje: 6
Membru din: Vin Sep 07, 2018 10:24 am

Adunare din multiple Worksheet cu conditii multiple

Mesaj de silvi » Joi Sep 13, 2018 9:02 pm

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!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IPP
Moderator
Moderator
Mesaje: 3787
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de IPP » Vin Sep 14, 2018 7:52 am

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
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

silvi
Mesaje: 6
Membru din: Vin Sep 07, 2018 10:24 am

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de silvi » Vin Sep 14, 2018 9:04 am

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

TudyBTH
Moderator
Moderator
Mesaje: 905
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de TudyBTH » Vin Sep 14, 2018 9:33 am

Buna,

Aveti atasata si o varianta cu TABELE (in loc de data range) in suc1 si suc2
Capture.JPG
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

IPP
Moderator
Moderator
Mesaje: 3787
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de IPP » Vin Sep 14, 2018 9:56 am

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
Moderator
Moderator
Mesaje: 905
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de TudyBTH » Vin Sep 14, 2018 10:16 am

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])
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

silvi
Mesaje: 6
Membru din: Vin Sep 07, 2018 10:24 am

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de silvi » Vin Sep 14, 2018 7:52 pm

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
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

TudyBTH
Moderator
Moderator
Mesaje: 905
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de TudyBTH » Vin Sep 14, 2018 10:01 pm

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)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

Indigo-99
Mesaje: 55
Membru din: Dum Sep 02, 2018 8:21 am

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de Indigo-99 » Vin Sep 14, 2018 10:23 pm

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.
"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
Sir Arthur C. Clarke

silvi
Mesaje: 6
Membru din: Vin Sep 07, 2018 10:24 am

Re: Adunare din multiple Worksheet cu conditii multiple

Mesaj de silvi » Dum Sep 16, 2018 8:42 am

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!

Scrie răspuns

Înapoi la “Intrebari despre Excel 2016”