Pentru fanii de Excel am primit de la un alt pasionat o problemă deosebit de faină și utilă multora din noi.

Cerințele problemei:

Se dă o echipă formată dintr-un număr finit de membri: A, B, C, D, E, F, G, H etc.

Fiecare dintre ei, lucrează la un anumit task (activitate) (t1, t2, t3 etc). Una și aceeași persoană poate lucra în același timp la unul sau mai multe task-uri (activități).

Structura unui task: NumeTask, Responsabil, Reviewer. Responsabilii sunt introduși manual, Reviewerii se alocă aleator de aplicație.

Constraints:

a) Fiecare dintre ei, poate face echipă cu oricare dintre ceilalți, dar doar pe un singur task (i.e. dacă A lucrează la taskurile t1 si t2, la t1 poate avea un anumit partener: E dar la t2 obligatoriu un alt partener: B);

b) Să nu existe parteneri încrucișați (i.e. interzis situația asta: A lucrează la t1 cu partenerul C, iar C lucreaza la t8 cu partenerul A);

c) Distribuirea fiecăruia ca partener trebuie facută în mod echilibrat;

d) fără VBA (doar cu funcții Excel).

Se cere:

O atribuire în mod aleatoriu, a acestor parteneri. (cu un F9 sa se faca refresh)

Indicii:

F9 se folosește când modul de calculare a foii de calcul este setat pe manual (Formulas, Calculation Options, Manual)

O combinație incorectă:

O combinație corectă:

Nu sunt constrângeri de istoric pe alocarea curentă de task-uri.

Puteți alege o altă combinație sau structură a tabelelor. Ceea ce v-am prezentat eu este doar un exemplu.

[UPDATE DUPA ORA 22/20.01.2015]

Așadar, după cum am promis revin cu o serie de soluții.

Soluția lui Cătălin L.

Cătălin este de profesie Virus Analyst la BitDefender și unul dintre cei mai buni studenți ai mei de la masterul Administrarea Afacerilor. A primit 10 la seminar pentru rezolvarea aproape de 80% a problemei.

Mesajul său este:

”Am rezolvat problema, zic eu, folosind Numbers de la Apple, ca nu am la indemana un Excel. Am folosit cateva campuri precalculate ca sa nu scriu o formula prea mare.”

Fișierul său este la adresa: https://onedrive.live.com/view.aspx?resid=6EC38785F56DAE91!360575&ithint=file%2cxlsx&app=Excel&authkey=!AHsr4EX9wWnob7c

sau http://1drv.ms/1uNFoww

Sau accesați direct folderul: https://onedrive.live.com/?cid=6EC38785F56DAE91&id=6EC38785F56DAE91%212853 și deschideți/descărcați fișierul: Combinații unice Catalin L.xlsx

Soluția nu este finală pentru că are destul de multe constante (Hard-Code) pentru a putea fi acceptată.

Soluția mea

Soluția propusă de mine se bazează pe puțin mai multă matematică. În prima fază ID-urile Reviewer-ilor se alocă automat prin funcția =RANDBETWEEN(1;$J$1) în care J1 este maximul ID-urilor din tabela persoane care au un nume în dreptul lor: =COUNTIFS(tblPersons[ID];”<>0″;tblPersons[Name];”<>”)

Normal că acest maxim din J1 nu funcționează corect pentru că dacă ștergem un nume intermediar, numărul rezultat prin acel COUNTIFS nu mai este concludent. Făcând o analogie la tabelele din bazele de date, orice înregistrare vidă nu mai are un ID alocat, așa că structura tabelului tblPersons nu este normalizat din punct de vedere atomic.

Trecem peste această scăpare și continuăm.

Problema concretă constă în a determina unicitatea unui tuplu:

A-B = B-A

În acest context am preferat să utilizez ID-uri numerice în loc de nume concrete sau ID-uri de tip litere.

Ca să pot determina unicitatea tuplului a trebuit să iau în considerare faptul că două numere de diferite mărimi pot da același rezultat: 4+10 = 12+2 ceea ce m-ar fi încurcat în a determina unicitatea unui tuplu.

Soluția găsită de mine a fost folosirea funcției SINH() în reprezentarea numerelor în loc de numerele pur și simplu. SINH() returnează sinusul hiperbolic al unui număr. Ăăă știu, e complicat modul în care am ajuns eu la acea formulă, dar se pare că e cea mai sigură metodă de a determina un număr suficient de diversificat pentru a putea să obții sume diferite din două numere reale destul de apropiate.

Pe coloana F mai este pus și un IF de control pentru cazul în care se alocă în mod aleatoriu un număr asemănător Responsabilului.

Pe Coloana G am folosit o combinație de funcții MATCH() cu IF() și ROW()  care îmi determină dacă acel număr obținut prin însumarea SINH()-urilor de ID-uri este unic în lista de rezultate (coloana F) sau nu.

Restul este apă de ploaie: formatare condițională la nivel de celulă și apoi la nivel de linie.

Fișierul poate fi vizualizat aici: http://1drv.ms/1uoLZP8 sau: https://onedrive.live.com/redir?resid=6EC38785F56DAE91!360578&authkey=!AHsr4EX9wWnob7c&ithint=file%2cxlsx sau din folderul: https://onedrive.live.com/?cid=6EC38785F56DAE91&id=6EC38785F56DAE91%212853

Verdictul

Nimeni nu a obținut sticla de vin, așa că ”potul” se multiplică cu 5: 4 sticle de vin real livrate la adresa fizică a celui care rezolvă problema! Una mi-o păstrez mie! :))

Să ne întoarcem la cerințele de business și să analizăm ce nu este bine în soluția mea:

1. Matematic vorbind când ai un număr mic de angajați, combinațiile unice pentru task-uri este limitat. Exemplu: dacă ai 2 angajați poți crea doar un singur task unic: A responsabil – B reviewer. Sau invers.

Dacă ai trei angajați poți crea trei task-uri unice: A-B, B-C, A-C sau combinații unice ale lor.

Chiar și la mai mulți angajați și mai multe task-uri, funcția de randomizare dă de mai multe ori ”rateuri” referindu-ne aici la faptul că trebuie să apeși de mai multe ori tasta F9 (Calculate Now) pentru a obține rezultate unice.

Poate pentru mulți nu este o problemă, dar cerința este cerință.

Indicația mea este de a compara ID-ul Reviewer-ului cu ID-ul responsabilului ca să eliminăm acele SameID și cu rezultatele anterioar obținute mai sus. Ceea ce nu am descoperit încă este cum determini care sunt rezultatele de mai sus. O posibilă soluție este eliminarea ranzomizării.

2. A doua cerință: Distribuirea fiecăruia ca partener trebuie facută în mod echilibrat;

Asta înseamnă că dacă un angajat a primit deja un task, ar trebui să alocăm altora care nu au primit deja, și care nu au același ID cu cel al resposabilului. Numărul de review-uri alocate este specificat în tabelul de Angajați sub forma unui countif din tabelul de task-uri.

Aici nu am nici o idee, dar mai investigăm. :)

[/UPDATE]

[UPDATE 21.01.2015 H22:00]

Așa cum am promis revin cu update-ul zilei la această problemă a combinațiilor unice. Personal nu am reușit să mai ”sap” după soluții pentru problemele din Verdictul anterior dar am primit o soluție surprinzător de frumoasă de la un fost coleg de muncă.

Ciprian S. propune o soluție cu o funcție de tip Array care rezolvă cu o uninicitate a combinațiilor foarte mare problema duplicatelor din problema 1 a verdictului. Rămâne deschisă problema 2 – Distribuirea echilibrată a reviewer-ilor pe task-urile deschise.

De-a dreptul spectaculos în soluția lui Ciprian este modul în care a tratat condiția de unicitate. Eu vă prezentasem acea formulă ”sefe”: SINH(). El a găsit o metodă mult mai simplă și anume concatenarea între cel mai mic număr dintre ID-ul Responsabilului pe task și ID-ul responsabilului pentru Review. Din punctul meu de vedere, faptul că știam puțină matematică prezentând o funcție de mare nișă pălește în fața simplității geniale de a alipi minimul și maximul unui număr pentru determinarea duplicității unei combinații.

Chiar dacă în schimbul de mailuri Ciprian mi-a adus aminte de o perioadă din mileniul trecut când lucram ca tehnoredactor la PIM, la el a ajuns problema prin intermediul unei puternice comunități de Office din România: http://www.itlearning.ro/forum/viewtopic.php?f=99&t=7353. În prezent Ciprian este economist la o companie din Iași.

Dar cum rigoarea academică nu lasă o soluție să treacă fără a o întoarce pe toate părțile am găsit relativ repede o excepție în funcționarea corectă a validării unicității combinațiilor. Priviți cu atenție imaginea: În cazul perechilor 1-1111 și 11-111 avem o duplicitate la metoda alipirii.

Și dacă am trecut la teste, am descoperit că nici SINH()-ul nu este suficient de bun pentru numere mari pentru că ultimul număr care poate fi interpretat în Excel de SINH() este 709!

Soluția ar fi să lucrăm cu materialul clientului: adică alocarea reviewer-ilor să se facă numai din lista de angajați disponibili fără randomizare, iar formula lui Ciprian pare cea mai corectă până acum pentru această situație:

=IFERROR(SMALL(IF(Nom[Index]<>[@[Cod_R]];IF((COUNTIF($D$1:D1;Nom[Index]&[@[Cod_R]])+COUNTIF($D$1:D1;[@[Cod_R]]&Nom[Index]))=0;Nom[Index]));RANDBETWEEN(1;$F$1-1-COUNTIF(B$1:C1;[@[Cod_R]])));””)

Fișierul Excel cu soluția lui Ciprian îl puteți accesa aici: https://onedrive.live.com/view.aspx?cid=6EC38785F56DAE91&resid=6EC38785F56DAE91%21360595&app=Excel sau în folderul:  https://onedrive.live.com/?cid=6EC38785F56DAE91&id=6EC38785F56DAE91%212853 cu numele: Combinații unice – Ciprian S.xlsx

Inițiatorul, cel care a născocit problema este bucuros de soluția lui Ciprian dar mai trebuie rezolvată problema alocării egale a reviewirilor din lista și cu speranța că nu vor exista situații de exepție genul: 1-1111.

Personal voi încerca să îmbunătățesc soluția mea, cu gandul la faptul că uneori tabelele relaționale sunt mult mai utile pentru asta. :) Ar fi util să întrebăm ceva specialiști în SQL cum ar trata subiectul? :)

[/UPDATE]

[UPDATE 23.01.2012 H15]

Se pare că în final avem o rezolvare acceptabilă a problemei oferită de Marius M. ca o continuare a soluției inițiale a lui Ciprian S.

Ciprian a pus mai multe update-uri pe site-ul http://www.itlearning.ro/forum/viewtopic.php?f=99&t=7353 dar se pare că Marius a găsit optimul de alocare a ID-urilor cu cea mai mică rată de dubluri, plus că reușește să facă alocarea echilibrată a reviewer-ilor în listă.

Soluția propusă de Marius M. poate fi consultată la adresa: https://onedrive.live.com/edit.aspx?cid=6EC38785F56DAE91&resid=6EC38785F56DAE91%21361092&app=Excel

sau în folderul  https://onedrive.live.com/?cid=6EC38785F56DAE91&id=6EC38785F56DAE91%212853 cu numele: Combinații unice – Marius M.xlsx

Un preview cu modulul testat al aplicației inițiate de Ciprian S și continuate de Marius M.

Mesajul complet a lui Marius care explică modul de rezolvare:

”V-am atasat solutia pe combinatii, am continuat pe solutia oferita de Ciprian, (care e foarte faina, si de la care am invatat foarte multe, nu stiam ca pot face asa ceva cu referinte structurate), dar am adaugat verificarea duplicatelor fara nici o concatenare, si fara sinus, ci cum v-am scris pe blog, cu COUNTIFS, o verificare pe randurile anterioare. Ciprian a mai propus o rezolvare cu concatenare, dar are limite, solutia asta cu COUNTIFS nu are nici o limita, si nici o exceptie.

Cel mai usor vedeti in coloana E (Status).

Apoi am propus si o incercare de distribuirie echilibrata, anume verific count-urile id-urilor atribuite pana la randul curent, si iau in considerare pentru asignare doar id-urile care au fost asignate cel mai putin. De exemplu, pana la randul curent toate id-urile au fost asignate, dar majoriatea de mai multe ori, iar doua id-uri cate o data fiecare, le iau in considerare pentru asignare cu randbetween doar pe cele 2.

Dand de mai multe ori F9, am observat ca uneori ramane un singur id cu cele mai putine asignari, dar daca il asigneaza se incalca conditia de unicitate, si atunci imi da eroare.

Vreau sa va multumesc pentru problema.”

Marius este absolvent al specializării de Informatică Economică și actualmente lucrează la http://www.hartehanks.com/

Mulțumim Marius! 

[/UPDATE]

Problema rămâne deschisă. Dacă peste timp vor mai apărea cititori ai acestui articol care vor avea soluții îmbunătățite nu ezitați să mă contactați. Chiar ar fi interesante analogii cu bazele de date sau de ce nu cu VBA. :)

În cazul în care mai aveți probleme faine de Excel și pe care ați dori să le spuneți unor dezbateri / soluții publice aștept cu interes provocările dumneavoastre.