Autor: Valy Greavu

Elemente de programarea funcțională în Excel Modern

Programarea funcțională este o paradigmă de programare care pune accentul pe utilizarea de funcții predefinite sau definite utilizator prin intermediul funcțiilor de tip Lambda. Începând cu versiunile cloud ale Microsoft Excel, acesta s-a schimbat complet odată cu introducerea funcțiilor pentru vectori dinamici și a funcțiilor Lambda. Trecerea de la VBA la funcțiile dinamice, aduce un spor de securitate prin accesibilitatea și colaborarea în comun (coauthoring) pe aceleași fișiere în cloud. Ulterior lansării funcțiilor dinamice în Excel on-line, acestea au fost implementate și în Google Sheets, majoritatea “aplicațiilor”  Excel fiind compatibile acum în ambele variante cloud.

În dezvoltarea de software comercial, programarea funcțională nu are foarte mult succes, dar odată cu noile direcții de big data, machine learning, AI, aplicații de prelucrare statistică precum R devin din ce în ce mai populare și utilizate atât în mediul academic cât și în companiile private. Ca alternativă la R, Microsoft a dezvoltat limbajul M, cunoscut astăzi ca Power Query M utilizat în importul și prelucrarea datelor, corelat cu Excel, la început, și Power BI în prezent. Pe lângă acestea Python este o alternativă puternică la analiza de date, dar care este un limbaj multi-paradigmă, abordarea în rezolvarea diferitelor tipuri de probleme fiind în special procedural sau orientat pe obiecte. Programatorii Python pot dezvolta aplicații în mod funcțional prin utilizarea funcțiilor lambda, map(), reduce(), filter(), funcții pe care le întâlnim și în mediile enumerate anterior. Nu putem încheia acest paragraf fără a menționa Haskell, care continuă să fie unul din cele mai cunoscute medii de dezvoltare funcțională, apărut încă din anii ’90 ca un standard în domeniu, care se dezvoltă și este utilizat în continuare în rezolvarea diferitelor tipuri de probleme.

Elemente de sintaxă

Așa cum specificam la începutul articolului, o funcție definitorie pentru rezolvarea problemelor în Excel este funcția Lambda() care are sintaxa generală: 

=Lambda(variabila1; variabila2; calcule). 

Menționez că în acest articol folosesc separatorul ; (punct și virgulă) specific localizării românești a sistemului de operare. În versiunile cu localizare engleză, separatorul între parametrii funcțiilor este , (virgula). De asemenea, sensul de vector în acest articol este acela de bloc de celule sau șir de valori delimitate de un separator cunoscut. A2:A9 reprezintă un bloc de celule clasic, iar A2# reprezintă un bloc de celule dinamic rezultat în urma operațiunilor de split a unui șir de valori A ={ 1, 3, 5, 7, 9 }. De asemenea, în Excel, poziția unui element într-un vector începe de la 1 nu de la 0 cum este în programarea clasică. Există o singură excepție, pe care o cunoaștem, dată de funcția OFFSET() care începe numărarea elementelor unui vector de la poziția 0.

În funcție de contextul utilizării în cadrul altor funcții dinamice, Lambda poate avea variabile de input dedicate. De exemplu:

=SCAN(valoare inițială; vector; LAMBDA(a; v; calcule)), în care a este acumulatorul sau valoarea calculată până în prezent, iar v este valoarea curentă de pe vector;

=MAP(vector; LAMBDA(v; calcule)), în care v este valoarea curentă de pe vector;

=BYROW(tabel; LAMBDA(r; calcule)), în care r este toată linia curentă din tabel;

=MAKEARRAY(i; j; LAMBDA(r; c; calcule )) în care r reprezintă rândul curent și c reprezintă coloana curentă din matricea i*j generată.

Pentru calcul complex și integrarea mai multor pași în efectuarea anumitor operații, funcția utilizată este LET() cu următoarea sintaxă:

=LET(variabilă1; calcul1; variabilă2; calcul2; … variabilăn; calculn; output)

În care variabilele trebuie să aibă nume unice care nu pot începe cu numere și care pot fi apelate în următoarele calcule în ordinea definirii lor. Output poate fi numele unei variabile sau un calcul specific nedefinit anterior. În multe cazuri calculele pot fi constante sau blocuri de celule. În același timp, calculele dintr-un LET, permit definirea funcțiilor recursive, utilizabile în secvențe simulat-repetitive de execuție a unor calcule ulterioare.

Exemplificare: metode de înmulțirea a valorilor de pe două coloane.

Se dă tabelul de valori A2:B6 în care se dorește înmulțirea valorilor pe fiecare linie. Orice utilizator de Excel poate să facă acest calcul clasic prin înmulțirea valorii din A2 cu cea din B2 și apoi copierea formulei în jos. Pe de altă parte această problemă poate fi rezolvată foarte simplu cu un BYROW() așa cum este specificat în E2, doar că salvarea vine din funcția PRODUCT() care ne permite să tratăm unitar linia. Dacă am dori de exemplu să ridicăm la putere fiecare din valori sau pur și simplu să le comparăm pentru a returna numărul mai mare, această abordare cu un simplu BYROW() nu mai funcționează. Varianta în care putem trata fiecare din numerele unei linii, în mod unitar pentru întreg tabelul este cea din D2 în care am creat funcția recursivă freq care efectuează calculul pe care îl dorim aplicat în BYROW pentru fiecare linie.

În continuarea articolului vom exemplifica câteva elemente de programare funcțională în Excel, cu accent pe rezolvarea problemelor de tip matrice de triplete. Acest tip particular de problemă presupune determinarea tuturor combinațiilor pentru care x<y<z, pentru un N specific de valori ale unui vector dat. Nu încercăm în acest articol să rezolvăm Problema celor trei corpuri :), chiar dacă x, y și z sunt dinamice, ci  încercăm să ne limităm la modelele și problemele concrete de algoritmică de pe popularul site Codility. Problema matricelor de triplete este doar un punct de plecare în rezolvarea altor tipuri de probleme de algoritmică. Nevoia acestor tipuri de matrice a apărut din cauză că în nici o versiune de Excel (Microsoft sau Google) nu sunt implementate structuri de control repetitive. Abia când ajungi la un anumit nivel de complexitate în rezolvarea problemelor, realizezi cât de util ar fi să utilizezi o secvență for sau foreach.

Problema matricei de triplete

Menționam anterior că în Excel nu avem FOR. Dar putem rezolva diferite probleme prin alte artificii, de multe ori prin  ajutorul este dat de funcția SEQUENCE() care permite simularea unui număr de pași de execuția a unei funcții. De exemplu dacă am avea valoarea 10 în A2, putem rula o funcție care să efectueze 10 operațiuni de adunare, asemănător unei declarații for:

=LET(_i; 1; _i+SEQUENCE(A2)) , a cărei rezultat este un vector cu valori de la 2 la 11.

Trecând la nivelul următor, dacă am avea un șir de numere, pentru a îl înmulți pe fiecare cu fiecare în afară de el însuși, în Python va trebui să folosim un dublu FOR. Varianta de rezolvare în Excel poate fi foarte spectaculoasă, prin crearea unei matrice cu MAKEARRAY(), în care pe baza variabilelor r și c din lambda putem să înmulțim fiecare număr cu oricare altul fără a avea nevoie de FOR. Exemplificare:

În B2 avem șirul de valori {2;-1;0;1;2;3;4;5} pe care îl transformăm în vector în A5 prin utilizarea funcției: –TEXTSPLIT(B1; ;”;”).

Pentru a genera tabelul de rezultate în C5 utilizăm funcția:

=MAKEARRAY(ROWS(A5#); ROWS(A5#); LAMBDA(r; c; IF(r=c; „-„; INDEX(A5#;r)*INDEX(A5#; c))))

În care vectorul A5# este folosit ca input pentru numărul de linii și coloane ale matricei rezultat, apoi pentru fiecare r<>c calculăm produsul numerelor de pe vector rezultate prin indexarea vectorului cu r – linia curentă și c – coloana curentă.

Abordarea aceasta este foarte rapidă și puternică, dar este limitată la un număr maxim de 16.384 (2 la puterea 14) elemente în vector, echivalent numărului de coloane din Excel. 

Adevărata provocare apare în momentul în care dorim să efectuăm operații cu oricare din trei numere de pe vector. În programarea clasică folosim trei declarații FOR pentru a evalua fiecare din cele trei numere. În Excel suntem limitați la 1D al unui SEQUENCE() sau 2D-ul unei MAKEARRAY(), ceea ce înseamnă că pentru a extrage oricare din 3 numere în care x<y<z, trebuie să definim o matrice separată de căutare pe vector. Căutarea și extragerea de valori o putem realiza cu funcția INDEX(), TAKE() sau CHOOSECOLS() sau CHOOSEROWS() dar trebuie să specificăm o valoare clară pentru parametrul linie sau coloană a acestor funcții.

În cercetarea noastră am identificat că numărul de combinații unice în care fiecare poziție este mai mică decât cea anterioară este numărul de elemente ale vectorului combinate câte 3. În vectorul {-2;-1;0;1;2;3;4;5 } avem 8 elemente, ceea ce înseamnă că dacă aplicăm formula COMBIN(8;3) obținem valoarea 56.

Într-o primă variantă de rezolvare a acestei matrice de triplete am folosit o abordare foarte complicată de calcul prin care generam toate combinațiile posibile de numere de la 1 la 8, pornind de la 1;1;1 până la 8;8;8 după care făceam filtrare pentru x<y<z. Această operațiune îmi limita numărul de valori din vector la 101, pentru că pentru a genera toate combinațiile era nevoie de 101^3 ceea ce ne aduce foarte aproape de numărul maxim de linii din Excel: 2 la puterea 20 – 1.048.576.

Pentru a putea crește numărul de elemente care pot fi tratate în vector a trebuit să optimizăm funcția de generare a matricelor de căutare prin eliminarea numărului de elemente la puterea a treia, crescând de la 101 la 185 numărul de elemente care pot fi tratate în matrice. Ajungem la a doua limitare determinată de numărul de linii pentru că 185 prin combinare cu 3 ajunge la aproape numărul maxim de linii din Excel.

Exemplificare problemă matrice de triplete:

Pentru a putea rezolva problema am proiectat-o în jurul unui SCAN care este o funcție dedicată parcurgerii element cu element dintr-un vector. Problema lui SCAN este că nu poate returna valori de tip tabel și nu poate folosi ca input tabele. Ceea ce înseamnă că dacă dorim să tratăm un tabel într-un scan cel mai bine îl concatenăm cu delimitatori specifici, tabelul cu 3 coloane cu valorile 1, 2, 3 devine prin concatenare valoarea 1;2;3 pe care o putem descompune ulterior.

Funcția care determină toate combinațiile de valori din C5 este:

=LET(sir;B1;split; TEXTSPLIT(sir;;”;”); nrElemente; ROWS(split);
          combinatii; COMBIN(nrElemente; 3);

TripleG; SCAN(„1;2;2”; SEQUENCE(combinatii);

LAMBDA(a;v;

       LET(arr; –TEXTSPLIT(a;”;”);

           x; TAKE(arr;;1); y; TAKE(TAKE(arr;;2);;-1); z; TAKE(arr;;-1);

           newx; IF(AND(y=nrElemente-1; z=nrElemente); IF(x<nrElemente-2; x+1; x); x);

           newy; IF(newx=x; IF(z=nrElemente; IF(y<nrElemente-1; y+1; y);y); newx+1);

           newz; IF(newx=x; IF(z<nrElemente;z+1;y+2); newy+1);

newx&”;”&newy&”;”&newz

))); TripleG)

În care sir-ul din B1 este împărțit în valori în variabila split după care obținem numărul de elemente cu funcția ROWS, pe baza cărui determinăm numărul de combinatii a câte trei cu funcția COMBIN(). În TripleG definim de fapt vectorul rezultat cu ajutorul funcției SCAN cu cele două valori pe caremle putem manipula: a – acumulatorul și v– valoarea curentă. Având în vedere că prima combinație de valori este 1;2;3 pornim în SCAN de la 1;2;2, aceasta devenind prima valoare a lui a. Ulterior în funcția LAMBDA definită la nivelul SCAN-ului introducem un nou LET pentru a efectua calculele intermediare, pentru fiecare valoare determinând în funcție de elementele din a și nrElemente ale vectorului care este noua valoare a lui x, y sau z. În propunerea aceasta de rezolvare mi nu ne interesează niciodată valoarea curentă v, ci doar acumulatorul, funcția LET din LAMBDA determinând rezultatul poziției curente care va fi folosit la următoarea iterație ca a – valoarea de anterioară de verificat. În acest exemplu am preferat funcția TAKE() pentru că implică mai puțin scris față de CHOOSECOLS, care este destinat în special returnării de rezultate de tip vector, sau se putea utiliza funcția INDEX() pentru extragere valori din acumulator.

Funcțiile IF specifice calcului fiecărui newx, newy, newz , variabile care sunt concatenate la final cu simbolul & (ampersand), returnează valoarea aferentă poziției curente din vectorul de scanare combinatii. Niciodată x nu va fi mai mare decât numărul de elemente -2, y de numărul de elemente -1 iar z poate atinge valoarea maximă a numărului de elemente prin y+2 sau newy+1. În general sunt operații de algebră de clasele primare dar soluția implică parcurgerea etapizată a fiecărei valori prin abordarea strategiei de descompunere și obținere etapizată a rezultatului cu direcție de la newz spre newx. 

Odată ce avem matricea de triplete cu toate combinațiile unice crescătoare putem efectua toate produsele a oricăror trei numere din vector. Asta înseamnă că trebuie să parcurgem din nou vectorul, operațiune pe care o putem realiza atât cu MAP() cât și cu SCAN(). Având în vedere că nu trebuie să ne mai raportăm la valoarea anterioară, cel mai rapid în context este MAP(). Funcția din E5 devine astfel: 

=MAP(C5#; LAMBDA( v; LET(_v; –TEXTSPLIT(v;”;”); vector; $A$5#; INDEX(vector;TAKE(_v;;1))*INDEX(vector;CHOOSECOLS(_v;2))*INDEX(vector;TAKE(_v;;-1)))))

În care valoarea curentă de pe vectorul C5# este descompusă în variabila _v care este utilizat în indexul de la finalul LET-ului pentru a o înmulți cu celelalte valori de pe vector.

Opinia multor dezvoltatori de software este că programarea funcțională crește gradul de complexitate a soluțiilor finale, ceea ce rezultă într-un grad foarte mic de adopție pentru încă o perioadă  de timp. În același timp, în aplicațiile de tip Excel avem o serie de limitări la nivelul ordinului de valori care pot fi incluse în seturile de date analizate și de multe ori performanțele lasă mult de dorit. Excel like formulas, un slogan de promovare a Power Platform din suita Microsoft 365, încadrează oarecum abordarea din prezentul articol în tendința dezvoltării și utilizării din ce în ce mai mult a platformelor de tip low-code. Dincolo de toate acestea, puterea funcțiilor dinamice dă posibilitatea utilizatorilor de Excel  modern să renunțe la vechiul VBA încurajat și de noile componente Automate incluse în Excel-ul on-line.