Problema Excel – Transpose Like [UPDATED 11.01.2017]


Pentru cititorii mei care sunt fani Excel, am reușit să găsesc o problemă foarte faină pe care o supun rezolvării publice. Am numit problema Transpose Like pentru că seamănă puțin cu operațiunea de Paste cu Transpose dar nu este același lucru. Sunt sigur că modelul acesta poate fi aplicat și în alte cazuri particulare decât cel al pontajelor.

Cerințele problemei

Se dă o echipă formată dintr-un anumit număr de persoane. Fiecare completează un formular de pontaj zilnic într-o aplicație paralelă. De asemenea, pot apărea situații speciale și altcineva (șeful de echipă sau un responsabil de pontaj) notează zilnic cazurile speciale. Sursa de date din aplicația paralelă este exportată la final de lună în format tabelar normalizat: Data, MarcaAngajat, NumePrenume, Ziua, nr ore lucrate, Alte situații. Fișierul de completat poate fi descărcat de la adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq54Eb2x80NDXk66y7w

 

Raportul este în formatul fișei colective de prezență (figura 1): MarcaAngajat, NumePrenume, și zilele 1,2,3…30/31.

Raportul trebuie să preia datele din sursa de date și să afișeze în dreptul fiecărui angajat numărul de ore lucrate sau situațiile speciale.

Foaie colectivă de prezență

Constrângeri

Problema trebuie rezolvată fără folosirea macro-urilor. Aș fi curios totuși dacă cineva oferă la o soluție cu Macro, sau de ce nu una în SQL Smile.

Indicații

Sunt permise adăugări de coloane ajutătoare în Sursa de date. Nu sunt permise adăugarea de noi coloane în Foaia colectivă. Zilele de weekend pot fi completate cu valoarea 0.

Transmiterea soluțiilor

Aștept cu interes rezolvarea voastră până mâine (10.01.2017) ora 11:59 pe adresa de e-mail valy.greavu@outlook.com cu subiectul Problema Transpose Like.

Premii

Pentru studenții care rezolvă corect problema, ofer recomandarea mea pentru angajarea pentru orice post care implică cunoștințe peste medie de Excel.

Pentru ceilalți: Respect! Smile

[UPDATE 10.01.2017]

Mulțumesc tuturor prietenilor care au reușit să citească articolul și să încerce să rezolve problema. Am primit în total 8 soluții diferite. Le voi descrie pe rând, în ordinea sosirii fără a încerca să realizez un top. Toate soluțiile primite sunt funcționale. Urmează să execut o serie de teste de performanță pentru a stabili soluția optimă.

Prima soluție a venit de la Alexandru D, care a folosit 3 coloane ajutătoare în sursa de date pentru a determina unicitatea unei înregistrări, după care a folosit un vlookup pentru a aduce datele în formatul de raport cerut (vezi imagine).

Solutie Alexandru D

A doua soluție a venit de la Florin E. El a folosit două coloane suplimentare în sursa de date, chiar dacă ar fi putut folosi doar una, dacă analizăm cu atenție formula din coloana Zi+Marca.

Solutie Florin E

Soluția lui Florin folosește formula NetworkDays, dar asta presupune alterarea capului de tabel din Foaia colectivă de prezență. Uneori soluția de alterare a structurii tabelului nu este acceptabilă.

A treia soluție a venit de la Ciprian Stoian, un bun prieten, care a mai oferit soluții deosebite la problemele pe care le-am lansat pe aici. Ca să fac o glumă, Ciprian este cel care a dus la extremă conceptul de ERP în formatul Excel Resource Planning Smile (concept ironic preluat de la un prieten de pe FB). Ciprian folosește o singură coloană ajutătoare și o formulă Index cu Sumproduct, fiind din punctul meu de vedere una din cele mai abstracte soluții primite.

Ciprian Stoian

Dacă doriți să o testați, formula pentru celula C5 este: =INDEX(tblSD[Pontaj];SUMPRODUCT(–(tblSD[[ MarcaAngajat]]=FCP!$A5);–(DAY(tblSD[Data])=FCP!C$4);ROW(tblSD[Data])-ROW(tblSD[[#Headers];[Data]])))

 

Un alt fan Excel semnat Tudor H, a trimis una din soluțiile cele mai apropiate de ceea ce îmi propusesem să vă prezint eu. Tudor folosește o formulă Array cu Index și Match condițional în formatul (conditie1)*(conditie2).

Tudor H

Pentru cei care doresc să testeze formula, pentru celula C5 aceasta este:

=IFERROR(IF(SUMPRODUCT(tblSD[[Nr ore lucrate]:[Nr ore lucrate]]*(tblSD[[Data]:[Data]]=FCP!C$4)*(tblSD[[ MarcaAngajat]:[ MarcaAngajat]]=FCP!$A5))=0;INDEX(tblSD[[ Alte situații]:[ Alte situații]];MATCH(1;(tblSD[[Data]:[Data]]=FCP!C$4)*(tblSD[[ MarcaAngajat]:[ MarcaAngajat]]=FCP!$A5);0));SUMPRODUCT(tblSD[[Nr ore lucrate]:[Nr ore lucrate]]*(tblSD[[Data]:[Data]]=FCP!C$4)*(tblSD[[ MarcaAngajat]:[ MarcaAngajat]]=FCP!$A5)));””)

Pentru cei mai puțin experimentați, introducerea formulei nu se realizează cu Enter ci cu Ctrl+Shift+Enter (modul de introducere formule array)

IPP, moderator pe comunitatea ITLearning.ro a transmis o soluție cu două coloane ajutătoare și un VLOOKUP. Simplu și util.

IPP

Explicații despre modul în care a rezolvat IPP și cum a gândit soluția puteți găsi la adresa: http://www.itlearning.ro/forum/viewtopic.php?f=119&p=55527#p55527

 

Dan C a oferit o soluție, de asemenea, bazată pe Index Match cu două coloane ajutătoare:

Dan C

Posibilitatea de a putea introduce coloane ajutătoare simplifică mult problema pentru că Match-ul se face după o valoare compusă, nemaifiind necesare condiții multiple așa cum este specificat în soluția lui Tudor H.

A 7-a soluție a fost oferită de Ionut H care a folosit o coloană ajutătoare drept cheie de căutare pentru un Match din Index.

Ionut H

Pentru testare formula lui Ionut H pentru celula C5 este:

=IF(ISERROR(INDEX(SursaDate!$D:$D;MATCH($B$3&$B$2&C$4&$A5;SursaDate!$F:$F;0)));0;IF(INDEX(SursaDate!$D:$D;MATCH($B$3&$B$2&C$4&$A5;SursaDate!$F:$F;0))=””;INDEX(SursaDate!$E:$E;MATCH($B$3&$B$2&C$4&$A5;SursaDate!$F:$F;0));INDEX(SursaDate!$D:$D;MATCH($B$3&$B$2&C$4&$A5;SursaDate!$F:$F;0))))

Atenție, raportul lui Ionut este modificat fiind inclus anul în formulă și luna în format 1, 2, 3…12. În felul acesta soluția lui Ionuț este aplicabilă pentru surse de date care se ”întind” pe mai multe luni calendaristice.

Aproape de închiderea ediției am primit și cea mai lungă formulă ca soluție la această problemă. Ea vine de la colegul meu Florin M. și este oarecum elaborată pentru a calcula în mod diferit starea de la alte situații.

Florin M

În sursa de date Florin folosește un IF pentru coloana ajutătoare, dar are alte două tabele de validare. Asta complică puțin problema. În partea de formulă folosește o combinație între IF, SUMPRODUCT și VLOOKUP.

La toate soluțiile rezultate sunt corecte. Mulțumesc tuturor pentru participare și dacă mai aveți probleme provocatoare, nu ezitați să mă contactați!

[L UPDATE]

Cineva a sesizat că nu am pus soluția mea. Personal am mers din start pe idea de a nu folosi coloane ajutătoare, dar este importantă ziua din data pontajului. Așa că am folosit funcția DAY pentru coloana data din tabelul sursă de date. De asemenea, am folosit o altă formă de Match condițional (cu ampersant) pentru a putea include marca angajat și ziua. Da, formula mea este un pic mai complicată și se execută mai greu. Este o formulă array.

Valy G

Pentru a testa formula, valoarea acesteia pentru celula C5 este:

=IF(ISNA(INDEX(tblSD[Nr ore lucrate];MATCH($A5&C$4;tblSD[ [ MarcaAngajat] ]&DAY(tblSD[Data]);0)));”-„;
IF(INDEX(tblSD[Nr ore lucrate];MATCH($A5&C$4;tblSD[ [ MarcaAngajat] ]&DAY(tblSD[Data]);0))=0;
INDEX(tblSD[ [ Alte situații] ];MATCH($A5&C$4;tblSD[ [ MarcaAngajat] ]&DAY(tblSD[Data]);0));
INDEX(tblSD[Nr ore lucrate];MATCH($A5&C$4;tblSD[ [ MarcaAngajat] ]&DAY(tblSD[Data]);0))))

Atenție, este o formulă array deci trebuie introdusă cu Ctrl+Shift+Enter.

In data de 11.01.2017 s-a mai primit o solutie de la Adrian B care a oferit două formule de calcul plus o soluție în VBA. În prima soluție Adrian folosește o formulă array cu Index, Match si Indirect iar in a doua soluție folosește o formulă clasică cu IF, CountIF, Indirect și Sumproduct.

=IF(COUNTIFS(INDIRECT(„tblSD[MarcaAngajat]”);$G11;INDIRECT(„tblSD[Data]”);DATE(2017;1;I$10))=0;0;IF(SUMPRODUCT((INDIRECT(„tblSD[MarcaAngajat]”)=$G11)*(INDIRECT(„tblSD[Data]”)=DATE(2017;1;I$10))*((INDIRECT(„tblSD[Nr ore lucrate]”))<>0))*8=0;”X”;SUMPRODUCT((INDIRECT(„tblSD[MarcaAngajat]”)=$G11)*(INDIRECT(„tblSD[Data]”)=DATE(2017;1;I$10))*((INDIRECT(„tblSD[Nr ore lucrate]”))<>0))*8))

Soluția VBA în schimb este oarecum diferită. Adrian a pus un buton în pagina cu sursa de date și a construit un Macro în spatele acesteia. Idea este că macro se execută mult mai repede pe cantități mai mari de date.

Codul sursă al Macro lui Adrian este:

    Application.ScreenUpdating = False
   
    st = Timer

    With Sheets(sd)
   
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
   
        For i = 2 To lr
   
            isDay = CLng(Day(.Cells(i, 1)))
            isEmp = .Cells(i, 2)
           
            If .Cells(i, 4) = „” And .Cells(i, 5) = „” Then
           
                erH = MsgBox(„Nr. Ore = 0 / Alte Situatii = Null” & vbNewLine & „Please check line ” & i & vbNewLine & „Do you want to abort?”, vbYesNo, „Eroare!”)
                If erH = vbYes Then
                    Set erH = Nothing
                    Exit Sub
                Else
                    GoTo erHyes
                End If
               
            ElseIf .Cells(i, 4) = „” Then
           
                With Sheets(fcp)
                    Set dayFnd = .Range(.Cells(fcpHotRow, 3), .Cells(fcpHotRow, .Cells(fcpHotRow, Columns.Count).End(xlToLeft).Column)).Find(isDay, lookat:=xlWhole, LookIn:=xlValues)
                    Set empFnd = .Columns(1).Find(isEmp, lookat:=xlWhole, LookIn:=xlValues)
                End With
               
                fcpCol = dayFnd.Column
                fcpRow = empFnd.Row
               
                Sheets(fcp).Cells(fcpRow, fcpCol) = .Cells(i, 5)
                  
            Else
               
                With Sheets(fcp)
                    Set dayFnd = .Range(.Cells(fcpHotRow, 3), .Cells(fcpHotRow, .Cells(fcpHotRow, Columns.Count).End(xlToLeft).Column)).Find(isDay, lookat:=xlWhole, LookIn:=xlValues)
                    Set empFnd = .Columns(1).Find(isEmp, lookat:=xlWhole, LookIn:=xlValues)
                End With
               
                fcpCol = dayFnd.Column
                fcpRow = empFnd.Row
               
                Sheets(fcp).Cells(fcpRow, fcpCol) = .Cells(i, 4)
               
            End If
        
erHyes:

            Set erH = Nothing
            Set dayFnd = Nothing
            Set empFnd = Nothing
   
        Next i
       
    End With
   
    With Sheets(fcp)
   
        totCol = .Cells(fcpHotRow, Columns.Count).End(xlToLeft).Column
        lastFCProw = .Cells(Rows.Count, 1).End(xlUp).Row
       
        For k = fcpHotRow + 1 To lastFCProw
            .Cells(k, totCol) = WorksheetFunction.Sum(.Range(.Cells(k, 3), .Cells(k, totCol – 1)))
        Next k
       
        .Select
   
    End With
   
    tmr = Round(Timer – st, 2)
   
    MsgBox „Succesfully ran in ” & tmr & ” seconds.”

    Application.ScreenUpdating = True

End Sub

 

Un articol util care explică pas cu pas cum se realizează un Index cu Match cu criterii multiple este: INDEX MATCH with multiple criteria sau How to Use INDEX+MATCH With Multiple Criteria in 5 Easy Steps.

Sper să vă fie util!

 

Anunțuri

6 gânduri despre “Problema Excel – Transpose Like [UPDATED 11.01.2017]

    • Am văzut firul respectiv. Mulțumesc frumos! Prima solutie am primit-o. Foarte faină. Pe a doua nu. Având in vedere ca sunt mai multe solutii faine trebuie sa rulez teste de performanta. :)

      Apreciază

  1. Inspirat de solutia d-lui IPP am scurtat si eu putin formula (array in C5)
    =IFERROR(INDEX(tblSD[Nr ore lucrate]&tblSD[[ Alte situații]];MATCH(FCP!C$4&FCP!$A5;tblSD[Data]&tblSD[[ MarcaAngajat]];0));””)
    Pentru ca aceasta formula intoarce rezultatele in format text, se modifica si formula pentru suma orelor (AH5, tot array)
    =SUMPRODUCT(IF(ISNUMBER(–C5:AG5);VALUE(C5:AG5)))

    Apreciat de 1 persoană

  2. Salutare,

    Cred ca din codul VBA lipsesc primele 3 linii cu declararea variabilelor:

    Public Const sd as String = „SursaDate”
    Public Const fcp as String = „FCP”
    Public Const fcpHotRow as Long = 4

    Apreciază

  3. Pingback: Problema Excel – Transpose Like – Versiunea 2 | Valy Greavu's Live Blog

  4. Nu este nevoie de coloane ajutatoare.
    Pe foaia „FCP”:
    – in loc de luna „Ianuarie” (text), se va trece 01.01.2017, cu formatare „MMMM”
    – in locul zilelor lunii (ca numar) se trec datele zilelor lunii cu formatare „D”
    – formula din celula C5 va fi:
    {=IF(SUMIFS(SursaDate!$D:$D,SursaDate!$B:$B,$A5,SursaDate!$A:$A,C$4)>0,SUMIFS(SursaDate!$D:$D,SursaDate!$B:$B,$A5,SursaDate!$A:$A,C$4),IF(COUNTIFS(SursaDate!$A:$A,C$4,SursaDate!$B:$B,$A5)>0,INDEX(SursaDate!$E:$E,MATCH(C$4&$A5,SursaDate!$A:$A&SursaDate!$B:$B,0)),””))}

    Apreciază

Lasă un răspuns

Completează mai jos detaliile tale sau dă clic pe un icon pentru a te autentifica:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s