Problema Excel – Transpose Like – Versiunea 2 (UPDATED 23.01.2017)

După interesul crescut pentru problema anterioară de Excel, un cititor a sugerat o variantă ”mai realistă” a problemei, inroducând conceptul de calendar.

Calendarul este generat de o aplicație alternativă și ajunge ca sursă de date într-o foaie nouă de Excel. Structura calendarului este: Marca, Nume Prenume, DataStart, DataFinal, Situație.

image

Situațiile din tabel au următoarea semnificație: CO – Concediu Odihnă, CM – Concediu Medical, D – Delegație, ZL – Zi Liberă. In Foaia colectivă de prezență trebuie să apară doar indicativul.

Cerințele problemei

În foaia FCP (Foaia Colectivă de Prezență) se dorește completarea cât mai corectă a datelor în așa fel încât să apară numărul de ore lucrate pe fiecare zi în parte, prin preluare din SursaDate, nimic, 0 (zero) sau X dacă nu există ore lucrate și coloana Situație în cazul în care data se află înregistrată în tabelul foaia de calcul Calendar.

Fișierul FCP – Transpose Like – ver 2.xlsx  poate fi descărcat de la această adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq6gtIkcY3l5QPujXyQ

Constrângeri: nu sunt permise adăugiri de coloane suplimentare. Încercați să evitați folosirea VBA.

Indicații: Nu sunt.

Transmiterea soluțiilor

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

Premii

Așteptăm propuneri de sponsorizare pentru cele mai bune soluții. Smile (pe bune – le anunț aici dacă se oferă cineva)

UPDATE – Rezultatele pe 23.01.2017

Nu știu câți dintre voi au așteptat rezultatele acestei formule.

Prima soluție a sosit după  de la Iulian

În semnătura lui Iulian este trecută compania PROWORK COOPERATIVE din Italia. Interesant este că Iulian a oferit o soluție după doar două ore de la publicarea articolului.

Soluția lui Iulian se bazează pe un Index și Lookup cu vector de rezultat iar soluția lui întoarce rezultate corecte pe sursele de date propuse:

Iulian

Formula lui Iulian pentru celula C6 este:

=IFNA(INDEX(SursaDate!$D:$D;MATCH(DATE($B$3;$B$2;C$5)&$A6;INDEX(SursaDate!$A:$A&SursaDate!$B:$B;);0));IFNA(LOOKUP(2;1/(Calendar!$C:$C<=DATE($B$3;$B$2;C$5))/(Calendar!$D:$D>=DATE($B$3;$B$2;C$5))/(Calendar!$A:$A=$A6);Calendar!$E:$E);”X”))

Formula lui Iulian poate fi îmbunătățită prin adresarea specifică a coloanelor din cele doua tabele de date în loc de folosirea referinței la nivel de coloană. Am văzut de multe ori în practică referirea la nivel de coloana, dar asta presupune calcul în formule complexe până la ultima linie din foaia de calcul, ceea ce duce la o scădere de performanță.

A doua soluție a sosit de la FlorinE.

Soluția lui Florin folosește o formulă array pe bază de Index Match condițional.

FlorinE

Formula lui Florin pentru celula C6 este:

=IFERROR(INDEX(SursaDate!$D$1:$D$94;MATCH(1;(DAY(SursaDate!$A$1:$A$94)=FCP!C$5)*(SursaDate!$B$1:$B$94=FCP!$A6);0));IFERROR(INDEX(Calendar!$E$1:$E$7;MATCH(1;(Calendar!$A$1:$A$7=FCP!$A6)*(DAY(Calendar!$C$1:$C$7)<=FCP!C$5)*(DAY(Calendar!$C$1:$C$7)+Calendar!$D$1:$D$7-Calendar!$C$1:$C$7>=FCP!C$5);0));0))

Formula este funcțională și întoarce rezultate corecte și complete. Soluția lui Florin este modul în care am rezolvat și eu problema numai că în loc de adresarea pe celule eu am folosit adresarea specifică pe coloane de tabel.

Un pic mai optim, soluția lui Florin ar fi putut fi scrisă:

=IFERROR(INDEX(tblSD[Nr ore lucrate];MATCH(1;(DAY(tblSD[Data])=C$5)*(tblSD[ [ MarcaAngajat] ]=$A6);0));IFERROR(INDEX(tblCalendar[Situație];MATCH(1;(tblCalendar[ [ MarcaAngajat] ]=$A6)*(DAY(tblCalendar[DataStart])<=C$5)*(DAY(tblCalendar[DataStart])+tblCalendar[DataFinal]-tblCalendar[DataStart]>=C$5);0));0))

Din formula lui Florin lipsește luna calendaristică și anul, rezultând că nu se aplică pentru surse de date care se ”întind” pe mai multe luni calendaristice.

A treia soluția a venit de la Ciprian Stoian

El a propus două metode de rezolvare: Una clasică cu formule și una cu Power Query.

Mesajul complet a lui Ciprian este:

1. Cea din pagina FCP e cu formule și are ca sursă tabelele date.

2. Cea din pagina Query funcționează doar în Office 2016 (de testat și pe 2013 dacă este instalat Power Query). Pagina Tabelă sau view SQL e folosită ca sursă în acest moment, dar într-un mediu de lucru real ar trebui să fie un view în SQL. Pentru „floricele” anul și luna pot fi definiți ca parametri în șirul de conexiune. Userul ar avea o singură foaie în Excel și cu un simplu refresh are FCP-ul dorit.

Având în vedere că avem cu toții de învîțat ceva din formulele lui Ciprian voi încărca direct fișierul lui Ciprian de la adresa: Fișier Transpose Like v 2 CiprianS

Formula lui Ciprian este diferită de tot ce știam până acum. Valoarea formulei pentru celula C6 este:

=CHOOSE(1+IfOre+2*IFCal;””;Ore;INDEX(tblCalendar[Situație];rCal))

CiprianSF1

Mi-a luat aproape 1 minut să mă minunez și să descoper cum a fost implementată. Dacă vă uitați cu atenție la formulă referințele cu albastru sunt referințe de blocuri de celule, dar în fapt, aceste referințe sunt obținute printr-un set de formule din NameManager.

Cei care doresc să studieze pot descărca fișierul de mai sus pentru o analiză detaliată:

image

Despre a doua soluție, cei care sunt interesați pot consulta de asemenea fișierul atașat. Trebuie doar să aveți Power Query instalat.

Soluția propusă de mine

Având în vedere că este vorba de o continuare a unui articol, personal am propus o formulă array ca o continuare a celei din articolul precedent.

Formula în schimb nu este completă, în timpul redactării ajungând la concluzia că varianta aleasă nu este optimă.

FormulaValy - Offset dinamic

Formula funcționează acum doar pe două intervale de timp din calendar. Exemplu angajatul cu Marca 123 are 3 intervale în calendar. Ingeniozitatea formulei pe lângă monstruozitatea sa este dată de faptul că folosește un concept de Offset dinamic. În lina 11 preiau în idex valorile ofsetului de pe coloana 2 din calendar, în cazul în care există marca. Altfel pun 0 (linia 13). Zona de Else a IF-ului de pe lina 10 semnifică faptul că marca există în calendar dar nu este pe intevalul de date de la linia 11. Așa că la linia 14 generez un nou IF care să compare cu un nivel mai jos față de prima valoare întâlnită a câmpului Marca. Căutarea stabilește în mod dinamic începutul blocului de căutare la adresa celei dea doua valori a mărci de pe coloana specifică.

Pare dificil dar funcționează parțial. Pentru al treilea interval ar trebui modificată locația lui 0 de pe lina 23.

Personal ca să le pot scrie mai ușor astfel de formule le dezvolt bucată cu bucată în Notepad++.

Menționez că am mai primit o soluție parțială din partea FlorentineiS cu IF si SUMPRODUCT.

 

Sper că vă plac și apreciați rezolvărilor fanilor Excel de pe Internet. De asemenea, sper să vă fie utile.

În cazul în care mai aveți probleme interesante de Excel vă rog să nu ezitați să mă contactați!

Respect!

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!

 

Blog la WordPress.com.

SUS ↑