În urmă cu ceva timp scriam articolul Operațiuni cu data și timpul în #Excel în care am încercat să explic cât mai detaliat diferite funcții și calcule legate de dată și timp. Astăzi în schimb vă propun o mică funcție care să calculeze câte luni sunt între două date calendaristice. Problema a venit ca un comentariu pe canalul meu de Youtube.
Avem așadar două date și trebuie să determinăm numărul de luni între ele. Problema care apare este dată de faptul că nu avem luni întregi și putem avea ani care nu sunt consecutivi între cele două date.
Cea mai simplă metodă este să folosești funcția: =DATEDIF(C3; C4; „m”) care returnează 30.
Pentru frumusețea lucrului în schimb vă propun o formulă personalizată care poate returna un rezultat mai precis… prin introducerea funcției MROUND() cu precizie de 0,25.
în care al doilea parametru din variabilele luni1 și luni2 este parametrul de precizie. DATEDIF() lucrează cu precizie de 0,50 dar în funcția custom putem stabili noi precizia.
Dacă dorim să calculăm precizia exactă cu DATEDIF() trebuie să introducem partea de zecimale calculate pe zile. Funcția ar fi:
În urma sesizării unui profesionist în Excel am identificat că articolul acesta și metoda propusă sunt eronate pentru că nu generează toate combinațiile posibile.
Ca să respectăm matematica numărul de combinații maxime rămâne limitat la 189 (momentan) având în vedere că valoarea lui COMPIN(190;3) este mai mare decât 2^20.
[/UPDATE]
Astăzi nu am mai reușit să programez o sesiune O oră de Excel. Program foarte încărcat, între care o întâlnire cu boardul RCW (Romanian Creative Week) pentru organizarea în facultatea noastră a celei de a doua ediții a evenimentului: UniCredit Fintech Hackathon
Dar am reușit în timp ce rezolvam alte probleme să găsesc o formă optimizată a matricei de triplete în Excel.
La ce este bună această matrice?
Explicam în articolul din PIN Magazine – Programarea funcțională în Excel Modern că în Excel nu avem o funcție FOR() baza iterațiilor în, cred, toate limbajele de programare actuale. Dar avem soluții detaliate în acel articol. Marea problemă a versiunii prezentate acolo dar și în articolele despre algoritmi din acest blog, este limitarea numărului de elemente dintr-un vector pe care le poți prelucra. În acea primă versiune funcția putea genera matrice de triplete pentru maximum 101 elemente ale unui vector.
Scopul acestor matrici este intermediar pentru prelucrarea unor seturi de numere luate câtre 3, fără repetiții. Ai un șir de numere și vrei să le adune pe toate cu alte câte două fără să repeți secvența. Cum faci? Ca să iei toate combinațiile unice de câte 3 trebuie să indexezi vectorul, iar pentru asta trebuie să știi ce poziții să apelezi. Acesta este scopul acestui algoritm: determinarea tuturor combinațiilor de poziții ale unui vector, luate câte 3.
Noua variantă elimină funcția SCAN() și introduce o matrice generată. Matricea este foarte mare consumatoare de resuse dar depășește limitele impuse până la versiunile curente.
Exemplificare în Excel
În acestă imagine am prezentat rezultatul pe două coloane ca să ocupe mai puțin spațiu.
în care cheia este variabila matrix care conține funcția MAKEARRAY() care generează dinamic toate combinațiile de numere și asta având doar 2 variabile r și c din care generez cele 3 numere _n1, _n2, _n3. Este o aplatizarea de fapt a unei dimensiuni 3D într-un plan RxC.
Ca să pot sorta elementele am utilizat funcția recursivă freq în care splitez orice valoare rezultată în matrice ca să o pot sorta, după care în variabila finală TripleG păstrez doar valorile unice.
Vizual codul dacă nu apare corect în scriptul de mai sus:
Exemplificare rulare la 999 elemente. Aici se observă că există o mică problemă de sortare a numerelor pentru că ele sunt tratate ca valori text în urma concatenărilor.
Cam asta ar fi. Makearray() rămâne una din funcțiile cele mai surprinzătoare până acum din ceea ce am descoperit recent.
În urmă cu ceva ani, unul din profesorii mei preferați (am mai mulți) ne povestea despre chinurile proiectării și implementării unui „motoraș” de repartizare a opțiunilor studenților sau candidaților pe anumite locuri în ordinea mediilor și preferințelor. Prima dată l-a făcut în SQL pentru admiterea la facultate. Ulterior l-a făcut în R. Recunosc că am privit oarecum cu invidie, pentru că în SQL chiar dacă am învățat destul de bine la master, nu am reușit să îl implementez, iar partea de R nu am înțeles-o prea bine, chiar dacă am mai cochetat din când în când.
Însă din momentul în care am început cercetarea profundă a programării funcționale în Excel, am reușit să fac lucruri pe care altă dată doar le visam și asta fără ajutorul generatoarelor de text, care sunt depășite de aceste metode. I-am mai spus lui ChatGPT că în Excel NU există FOR. El o ține pe a lui. Nu recomand! :)
Acest articol este un tribut pentru toți profesorii (unii actuali colegi ai mei) care m-au îndrumat și încurajat de-a lungul timpului să performez.
Fișierul de lucru poate fi accesat și descărcat de la adresa: AlocareOptiuni v1.xlsx. Acest fișier funcționează doar în versiunile Office 365 sau versiunile mai noi care suport funcții dinamice.
Proiecția problemei în Excel
În exemplul prezentat în fișier, se presupune că avem o listă de profesori care au disponibile teme pentru lucrările de licență și un anumit număr de locuri pe fiecare temă. Ca să poată funcționa algoritmul propus, este esențial să codificăm fiecare temă în mod numeric. Numărul de locuri le specificăm tot numeric în dreptul codului fiecărei teme. Temele cu 0 locuri vor fi excluse automat din motorașul de repartizare.
Opțiunile studenților sunt prezentate ca un șir de opțiuni delimitate prin ; (punct și virgulă) în care fiecare număr este codul unei teme. Algoritmul aranjează sursa de date în ordine descrescătoare a mediilor după care face repartizarea după prima opțiune, apoi a doua dacă la prima opțiune nu mai sunt locuri și așa mai departe.
Funcția pe care o puteți întâlni și în fișierul Excel în celula K2 este:
în care:
locs este blocul de teme de la A2:B21 și care trebuie înlocuit în funcție cu propriile coduri
optiuni sunt numele studenților cu opțiunile lor și media. Există și posibilitatea de a aborda alocarea și în funcție de principiul primul venit primul servit. În acest caz, dacă ordinea este cea prezentată în foaia de calcul, trebuie să modificăm valorile de pe coloana G cu numere descrescătoare.
Pentru a reduce complexitatea și numărul de caractere, am definit la începutul formulei, 4 funcții recursive: _fCumulare(tab), _fDecumulare(acumulate), _fLast(x) și _fLastN(x) pe care le utilizez în _scan cu precădere și în result.
Explicam în articolele trecute că un SCAN() nu poate lucra cu tabele ci doar cu o singură valoare. Ca să pot scana totuși tabele întregi, am nevoie de a unifica aceste tabele în valori parsabile.
Prin cod se observă un 999 la linia 8, în _scan și apoi în result. Acest 999 mă ajută să determin dacă o opțiune este în afara numărului de locuri disponibile.
Complexitatea acelui SCAN() poate fi văzută într-o proiecție intermediară aici:
Valoarea de final a fiecărui șir intermediar (acumulatorul din SCAN) este o combinație de 0 și rezultatul prelucrării: 999 sau valoarea opțiunii care îndeplinește criteriile alocării pentru acel student.
În momentul în care am alocat un loc, ca să pot face scăderea lui din locurile rămase am apelat la scăderea a două matrici în variabila ramase (linia 19) care adună rezultatul decumulării acumulatorului curent din intro (linia 12) cu o matrice cu două coloane generată dinamic în funcție de numărul de linii rămase din intro.
Vizual această oprațiune de scădere a matricilor ar fi:
Această tehnică mi-a permis să am oricâte opțiuni din partea unui student și să le tratez pe toate în funcție de ce a mai rămas în acumulator.
Chinul cel mare nu a fost la SCAN ci la integrarea rezultatelor în aceeași formulă. În versiunile intermediare MAP() era soluția logică la calculul variabilei resut. Până la urmă l-am folosit, dar am lucrat doar cu șiruri fără reutilizare funcțiilor de Cumulare și Decumulare. Efectiv nu poți într-un MAP dintr-un LET să folosești rezultatul intermediar al lui SCAN pe care sa-l transformi din nou în tabelar, chiar dacă rezultatul în proiectezi ca valoare atomică cumulată.
În celula C2 pentru a calcula pentru fiecare temă studenții alocați am implementat un simplu TEXTJOIN() de filtrare. Formula din C2: =TEXTJOIN(„;”;;FILTER($K$2:$K$18;$L$2:$L$18=A2;””)). Trebuie să modificați adresele dacă aveți mai multe sau mai puține opțiuni.
Cam asta ar fi.. cazurile de aplicare pot fi diverse, important este să aranjam datele de intrare corect dacă dorim să utilizăm acest „motoraș” de repartizare.
Valy Greavu - Live Blog 