Excel – Problemă cu combinații unice [UPDATED 23.01 H15:44)

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

Duplicate

O combinație corectă:

DuplicateOk

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.

image

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

Solutie_Marius

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.

 

Localități din România în #Excel și metadate pentru #SharePoint

Astăzi de ziua României m-am hotărât să partajez cu voi un fișier pe care eu îl consider foarte util în activitatea de zi cu zi din mai multe domenii socio-economice. romania flag_only_pic

Este vorba de lista tuturor localităților din România, grupate pe Regiuni, Județe, Superior (comună în cele mai multe cazuri). Pe lângă acestea puteți găsi în fișierul Excel și Codul SIRUTA (Sistemul Informatic al Registrului Unitatilor Teritorial – Administrative din Romania) care poate fi consultat la adresa: http://www.123coduri.ro/cauta-in-baza-de-date-coduri-siruta.php

Util pentru mulți dintre voi poate fi latitudine și longitudinea localității precum și adresa directă către localizarea geografică de pe Google Maps sau Bing Maps.

Fișierul ZIP poate fi descărcat de la adresa: http://1drv.ms/1uNFoww 

Fișierele nu conțin Macro-uri.

Câteva cazuri de utilizare în SharePoint

Metodele descrise mai jos au fost testate în SharePoint Server 2013 și SharePoint Online din Office 365.

Utilizarea ca listă

Pentru a utiliza datele în formatul de listă din SharePoint trebuie să parcurgeți următorii pași:

  1. Deschideți fișierul Excel Localitati-Ro.xlsx
  2. În meniul Table Tools, Design, Export, Export Table to SharePoint List
  3. Tastați adresa site-ului în care doriți să utilizați aceste date după care numele listei și o scurtă descriere.
  4. Apăsați Next, inventariați listele de câmpuri după care apăsați Finish.

Lista se crează în câteva minute bune, având în vedere numărul mare de înregistrări.

Atenție! Din cauza setărilor de performanță cu privire la limitarea numărului de înregistrări dintr-o listă, nu veți putea să vedeți toate înregistrările din listă.

image

Pentru detalii legate despre modul de schimbare a limitei de vizualizare în SharePoint on-premisses dar și impactul acesteia consultați articolul: How to Change the List View Threshold and Other Resource Throttling Settings sau pentru 2013, articolul: SharePoint 2013: List View Threshold.

Utilizarea ca metadate

Pentru a utiliza aceste informații ca metadate în SharePoint 2013 sau online trebuie să parcurgeți următoarea procedură:

  1. Site Settings, Term store management
  2. Săgeata în jos din dreptul Site Collection – Numele site-ului vostru, Import Term Set

image

3. În fereastra care apare alegeți șisierul CSV LocalitatiMetaImport.csv și apăsați Ok.
4. Apăsați Ok la final.

Metadatele create se pot utiliza în coloane specifice din Biblioteci de documente sau Liste. Pentru a mapa această coloană într-o bibliotecă de documente trebuie să parcurgeți următoarele etape:

  1. Deschideți setările bibliotecii în care doriți să adăugați metadatele
  2. Din secțiunea Columns a paginii de setări apăsați opțiunea Create column
  3. Tastați numele coloanei, exemplu: Locație, alegeți opțiunea Managed Metadata, tastați descrierea
  4. Selectați Localități din lista arborescentă de jos
  5. Apăsați Ok

image

Ulterior pe biblioteca de documente, în momentul în care faceți upload puteți să tastați numele localității sau să îl alegeți din arborele de metadate. De asemenea, puteți folosi coloana de metadate ca filtru pentru documentele specifice.

Fișierul LocalitatiMetaImport.csv este salvat sub encoding UTF-8 pentru a permite utilizarea diacriticilor în numele localităților.

Pentru cei care doresc să citească mai multe detalii despre importul termenilor în structuri de metadate: Managed metadata input file format.

Sper să vă fie util și aștept cu interes feedback-ul vostru!

#Excel – Exercițiu cu intervale de timp [UPDATED]

Știu că a trecut ceva vreme de la ultimul articol de tipul acestor tipuri de exerciții (vezi posturile cu șirurile, IF-urile și formatarea).

Astăzi vă provoc să vă puneți ”creierii pe bigudiuri” cu intervalele de timp.

Se dă următorul tabel cu date. Angajații pleacă în delegații. Singuri sau cu un coleg. Data plecării poate fi intr-o lună din an și data de intoarcere poate fi în aceeași lună sau în alta.

image

Folosind metoda coloanelor ajutătoare sau nu, determinați în mod exact numărul de zile petrecut de Ionel în luna iunie, asemănător raportului de mai sus, permițând operatorului să folosească acest tabel pentru toți angajații sau orice lună calendaristică.

Aștept rezultatele voastre prin e-mail la adresa: valy.greavu@outlook.com cu subiectul: Problema cu date până pe 14/11/2014 ora 21:30.

Ulterior voi analiza soluțiile și vă voi ”da” și soluția mea.

Aștept cu interes!

[UPDATE 14.11.2014 – H22]

Se pare că am primit doar două soluții. Nu mi-am ales bine ziua de postare a articolului, sau poate problema a fost chiar grea, sau lumea nu a fost interesată. Au mai fost 2-3 întrebări din public și în total 80 de vizualizări ale articolului. :)

Vali C. a trimis prima soluție, dar puțin incompletă, puțin alambicată, dar care include funcție FIND() care ar putea imbunătăți soluția propusă de mine prin aplicabilitatea pe nume ciudate de tipul Ionel/Ionela Marin/Marina/Marinela/Marinica sau Vasile/Vasilica. Trebuie să luăm în considerare că un astfel de tabel nu-l facem la nivel de prenume ci Marcă/Nume Prenume.

Vali C. a lucrat aproximativ 50 de minute la soluție, și a folosit 4 coloane ajutătoare.

Ionut L. (fost student al meu) a trimis o soluție funcțională pe care o găsiți în fișier în foaia de calcul IonutL. Interesant cat de mult s-a chinuit să facă acel IF de pe ultima coloană ajutătoare, lucrând mai mult de o oră la soluția sa.

Ionuț, devine astfel câștigătorul acestei probe! Felicitări și o strângere caldă de mână electronică! :)

Soluția mea, propune o abordare diferită prin care calculez pe coloane ajutătoare numărul de zile de delegație, Zilele în luna în care a plecat și dacă este cazul zilele în luna în care s-a întors.

Apoi pentru a calcula numărul de zile se folosește o sumă de sumIFS() cu condițile din raport: Nume și Luna.

Puteți descărca fișierul de la adresa: http://1drv.ms/1x2oIRk

Formulele de calcul:

image

Succes și dacă sunt idei mai faine le așteptăm cu interes! :)

Blog la WordPress.com.

SUS ↑