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.

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. (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:

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.

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))

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ă:

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ă.

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!