Contorizare/insumare dupa criterii multiple

Ce este nou in Microsoft Excel 2016?
Informatii despre cum se utilizeaza Microsoft Excel 2016
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Sâm Feb 08, 2020 11:35 pm

Buna seara!
Va supun atentiei urmatoarea problema:
-in fisierul exemplu atasat, in foaia 1, ar fi o lista de interventii facuta asupra unor "masini", defectele constatate, cauzele probabile si costurile remedierilor… Fisierul real este rezultatul unei interogari a unei baze de date si este mult mai complex, in sensul ca pot fi mult mai multe "defecte"...
-in foaia 2 sunt grupate piesele implicate in defectele respective pe "subansamble". Aceste "subansamble" nu apar ca atare in foaia 1. Si piesele, si subansamblele sunt in realitate mai multe...
Problema mea consta in completarea unor centralizatoare, cum am incercat sa le schitez in foaia 3:
-contorizarea tipurilor de defectiuni pe fiecare masina. Aici, daca in timpul unei interventii s-a actionat asupra mai multor piese ale unui subansamblu, sa se numere o singura data (ex: Ag5 din 01.01.2020, s-a actionat asupra "ax" si asupra "bujie", ambele din subansamblul "motor", se numara o singura data...)
-sumarizarea costurilor. In cazul acesta, trebuie insumate toate costurile, defalcate pe "subansamble" ( in exemplul de mai sus "ax" si "bujie"... se aduna 140 la "motor"...)
Multumesc!!!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

csaba1960
Moderator
Moderator
Mesaje: 174
Membru din: Mie Feb 02, 2011 4:05 pm
Localitate: Cluj-Napoca

Re: Contorizare/insumare dupa criterii multiple

Mesaj de csaba1960 » Mar Feb 11, 2020 12:09 pm

Buna ziua
Ce va propun rezolvarea problemei cu VBA.
Am schimbat un pic structura la Foaia1.
Modelul pe care va propun poate contine mai multe randuri pentru o reparatie, totadeauna numai cu o singura piesa.
Aceasta este in pagina Reparatii.
Se poate face programel pentru a transfera datele din modelul Dvs in al meu. Are numele Sub tranSfer
In pagina date sunt datele Dvs, usor altfel aranjate. In coloanele 3-4 sunt piesele si din ce subansamblu fac parte.

Programul se lanseaza cu Start, din pagina Reparatii.
Se pot adauga cu ajutorul programului inregistari in pagina reparatii.
Data se poate adauga NUMAI daca faceti DoubleClick pe TextBoxul respectiv si din panoul calendar selectati tot cu DoubleClick.
Masina si Piesele se aleg din ComboBoxuri. Defectul respectiv cauza se completeaza cu text (Si aici as recomanda tot lista ca si la piese)
Cu Adaugare se adauga un rand nou, o sa vedeti in lista ce ati adaugat.
Cu Inregistrarea se trec randurile din lista in pagina Reparatii.

In pagina Centralizare se face ceea ce doreati de fapt Dvs.
Am combinat, deocamdata, cele doua.
In coloaba B aveti numarul de reaparatii pe auto, in coloanele urmatoare, numarul de reparatii facute la subansamble, in dreapta lor costul total reparatii pe subansamble.

Programelul ce este in spate este deocamdata o versiune de incercare, dar se poate extinde la un numar dorit de piese, subansamble.
Se poate complica centralizarea si pe diferite perioade calendaristice.
Aceasta este numai un punct de pornire.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Mie Feb 12, 2020 10:20 am

Multumesc, Csaba, pentru efortul tau si pentru disponibilitate!
Solutia ta este interesanta, dar pentru ceea ce vreau eu sa fac inca nu este utilizabila pentru ca fisierul pe care lucrez e rezultatul unui export dintr-o baza de date, nu il completez eu. Fiecare interventie asupra unei "masini" poate avea pana la 14 "defecte", in plus sunt multe alte coloane cu diferite proprietati ale "masinilor", nerelevante in contextul problemei mele, de aceea, cand am incercat rutina "transfer", au rezultat randuri aiurea, si cred ca ar fi mult de munca pentru a fi puse in concordanta fisierul si rutina.

In esenta problema mea consta in a afla raspuns la intrebarea daca COUNTIFS si SUMIFS accepta pentru fiecare zona de cautare criteriu de cautare de forma OR(c1,c2,...,cn). Generalizand, daca aveam aceleasi criterii pentru toate zonele, zona de cautare poate fi una singura, dar necontinua? Si in caz ca nu accepta asa ceva, era buna o solutie VBA...

Am pus aceeasi intrebare pe alte trei site-uri; n-am primit raspuns decat pe unul, o solutie cu Power Query si Pivot Table care s-ar parea ca functioneaza, numai ca trebuie sa mai exersez eu tehnicile respective...

https://www.myonlinetraininghub.com/exc ... ria#p12660

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

Re: Contorizare/insumare dupa criterii multiple

Mesaj de TudyBTH » Joi Feb 13, 2020 12:19 am

Buna,

Problema dv consta in faptul ca 'exportul' va ofera date pivotate, iar pentru a le prelucra trebuie intai sa le depivotati.
Aveti deci urmatoarele variante:
1. Depivotarea acestora prin Query. Pentru ca structura fisierului sursa este variabila, acest proces va trebui sa il faceti manual, adica sa transformati zona cu date in tabel si sa adaptati query la fiecare nou 'export de date'. Aveti in atasament un exemplu concret in care se face depivotarea si gruparea pieselor direct cu query.
2. Depivotarea lor cu VBA. Depivotarea se poate face automat dar trebuie sa puneti la dispozitie capul de tabel original, cu denumirile coloanelor asa cum apar in raportul exportat.
3. Extragerea datelor direct din baza de date. Aceasta ar fi cea mai buna solutie daca aveti acces la baza de date respectiva.
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.

gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Joi Feb 13, 2020 7:09 am

Buna dimineata!
Urmarind si exemplul postat de TudyBTH, cred ca cea mai eleganta solutie e depivotarea cu PQ, si daca mai fac cateva exercitii, devin "expert" :D !
Inca o data va multumesc celor ce va straduiti sa ne oferiti o solutie!
O zi buna!

gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Joi Feb 13, 2020 3:29 pm

Vorba unei reclame destul de difuzata : "Cine cauta, gaseste...repede"!
Relativ...


https://www.excelacademy.ro/numararea-c ... -avansati/
https://www.excelacademy.ro/cum-insumam ... -avansati/

Si daca adaugam si depivotarea...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Contorizare/insumare dupa criterii multiple

Mesaj de TudyBTH » Joi Feb 13, 2020 4:12 pm

Indiferent daca folositi functiile Excel (Countif, Sumif) sau query (fiecare varianta avand avantajele si dezavantajele ei) problema dumneavoastra ramane aceeasi: in raportul sursa aveti un numar variabil de coloane din care trebuie sa extrageti date (depivotarea!).
In ambele variante va trebui
- fie sa adaptati formulele/query conform formatului concret al raportului (numarul de coloane piesa#-pret#)
- fie sa construiti un sistem dinamic care sa raspunda unui numar variabil de piese/Auto/Data (lucru nu tocmai usor de realizat)

Deci, eu personal voi urmari cu viu interes progresul pe care il faceti pe acesta cale.
Momentan, raman la concluzia ca in situatia pe care ati descris-o ar trebui aplicata fie o solutie VBA, fie o interogare directa a bazei de date din care provine raportul sursa.
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.

gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Vin Feb 14, 2020 8:50 am

Buna dimineata!
Interogarea directa a bazei de date nu o pot face, exportul il obtin folosind o interogare creata de Departamentul IT si modificarea ei nu este considerate oportuna doar pentru ceea ce imi trebuie mie…
Pentru depivotare cu VBA, inteleg ca va trebuie capul de tabel original pentru a incerca sa ma ajutati, insa din nefericire nu am voie sa-l public - politica de confidentialitate… Atasez un tabel gol in care am modificat titlurile de coloane. Veti observa ca am 14 calupuri de coloane care ar trebui depivotate. Daca puteti sa-mi sugerati un exemplu de cod pe care sa incerc eu sa-l multiplic, sa-l adaptez tabelului meu real...
Altfel, ramane sa folosesc metoda cu PQ.

Va multumesc!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Contorizare/insumare dupa criterii multiple

Mesaj de TudyBTH » Vin Feb 14, 2020 5:10 pm

Fisierul atasat ar trebui sa va ajute.
Fisierul cu raportul trebuie sa contina o singura foaie sau raportul sa se afle in Sheet1
Datele trebuiesc sa fie organizate in tabel (Ctrl+T daca nu sunt)
Fsierul trebuie sa fie deschis.
Campurile de tipul "numeCamp#" vor fi identificate automat si vor fi afisate intr-o lista. Trebuie sa selectati dintre ele pe cele care va intereseaza.
Datele depivotate vor fi scrise intr-un tabel in foaia Data.
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.

gh19612005
Mesaje: 184
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Contorizare/insumare dupa criterii multiple

Mesaj de gh19612005 » Sâm Feb 15, 2020 11:11 am

Buna dimineata!
Functioneaza impecabil! Sunt convins ca ma va ajuta foarte mult.
Nu am cuvinte sa va multumesc indeajuns…
Va doresc o zi buna!

Scrie răspuns

Înapoi la “Intrebari despre Excel 2016”