Calcule cu funcții dinamice pe tabele merged în #Excel

În articolul trecut, expuneam problemele pe care le putem avea dacă păstrăm datele într-un tabel cu celule unite (merged). Articolul curent este mai mult un exercițiu de prezentare a unor funcții dinamice pentru rezolvarea problemei de însumare a valorilor dintr-un tabel cu merged cells. Atenție! Nivelul de dificultate poate fi considerat prea ridicat, dar încerc să explic pas cu pas.

În imagine este prezentat tabelul original, coloanele A:D și raportul final în coloanele T:U.

Formula din T3 este:

=LET(tc; TOCOL(B2:B100);

coln; SCAN(0;tc;LAMBDA(a;v;IF(a=0;v;IF(v=0;a;v))));

ft; HSTACK(coln; D2:D100);

un; UNIQUE(TAKE(ft;;1));

suma; BYROW(un;LAMBDA(r; SUM(FILTER(TAKE(ft;;-1);TAKE(ft;;1)=r))));

HSTACK(un; suma))

Ca să ajungem gradual la această formulă, în coloana G avem formula: =TOCOL(B2:B17)

Rezultatul formulei este un array care conține valorile specifice din tabelul cu date, înlocuind celulele din merged cells cu 0. Notam în articolul precedent că în merged cells, valoarea afișată se află doar în prima celulă din bloc, restul fiind vide. TOCOL() realizează automat umplerea cu 0 a celulelor blank.

Ca să pot completa automat valorile 0 cu numele celui din blocul de celule, propun în coloana I, funcția =SCAN(0;G2#;LAMBDA(a;v;IF(a=0;v;IF(v=0;a;v)))) , în care:

0 este valoarea curentă de la care pornește scanarea. În fapt ea este imput pentru parametrul a (acumulator) din LAMBDA(). G2# este referința către rezultatul funcției TOCOL() din etapa precedentă. Esența în rezolvare este funcția LAMBDA() care trebuie să aibă în SCAN() doi parametri: a – acumulat, v – valoare curentă. Normal, acești parametri se pot chema și altfel, dar am văzut că mulți utilizatori de Excel preferă acest format. SCAN-ul evaluează fiecare valoare din sursa de date (G2#) și pe baza execuției din LAMBDA afișează o valoare la fiecare linie. În momentul în care începe execuția LAMBDA() parametrul a are valoarea 0. Dacă ea este 0 atunci se afișează valoarea curentă din blocul de date. De asemenea, a preia automat valoarea curentă a lui V care este valoarea de linie. La următoarea linie, dacă v nu era 0, a devine primul nume, deci la rândul următor a nu va mai fi 0 IF-ul intr-înd pe FALSE. Aici, dacă V-ul este 0 se preia valoarea acumulator ceea ce înseamnă valoarea de deasupra. Dacă V nu este 0 atunci se preia noul V. Pare complicat, dar în fapt sunt doar două IF-uri, care trebuie să returneze două valori. Mai greu este să ajungi la înțelegerea care este valoarea lui V și a lui A pe o anumită linie.

Pentru a obține acum un tabel cu coloana rezultată din SCAN și coloana de valori din tabelul inițial, în K2 am folosit funcția: =HSTACK(I2#;D2:D17)

HSTACK() este o funcție care pentru fiecare parametru generează o nouă coloană într-un tabel array rezultat. Dacă aș fi folosit VSTACK() atunci rezultatul ar fi fost o singură coloană cu primele linii din prima coloană continuate cu cea din a doua coloană.

Combinând aceste 3 funcții avem o primă funcție LET() cu variabilele TC, COLN și FT. FT este de fapt final table pe care trebuie să-l parcurg pentru a ajunge la nume unice cu sume cumulate.

Ca să pot obține formula finală, în Q4: am introdus o simplă funcție UNIQUE() pe baza datelor din N:O. Formula este aplicată doar pentru coloana de nume. Pentru a calcula acum valorile ar fi foarte simplu să utilizăm un SUMIF() dar intenția mea este de a folosi acest calcul direct într-un LET() care presupune că trebuie să tratăm dinamic coloana de nume, linie cu linie, necunoscând totalul de linii rezultat.

Rezolvarea în această etapă se poate realiza cu MAP() sau BYROW() depinde de modul de abordare: BYROW() ia toate coloanele din linie, MAP() apelează doar o singură coloană dintr-un tabel, linie cu linie.

Am preferat BYROW() pentru această formulă pentru a prezenta o altă funcție, MAP() fiind destul de des prezentat în articolele de pe internet.

Astfel funcția din R4: =BYROW(Q4#;LAMBDA(r; SUM(FILTER($O$4:$O$19;$N$4:$N$19=r)))) , în care:

Q4# este coloana cu nume unice, r este parametrul din LAMBDA() care face referire la linia curentă, după care avem o sumă pe un FILTER() pe coloana de sume, pe baza condiției ca numele să fie egal cu valoarea lui r.

Dacă preferați MAP() în formula din R4 schimbăm pur și simplu BYROW cu MAP și funcționează identic.

Această a doua parte se transformă în formula din T3, în variabilele un și suma. Având în vedere că sunt înglobate într-un LET() nu avem cum să ne adresăm în ele la anumite celule, blocuri de celule dintr-o foaie de calcul. Această problemă o rezolvăm prin funcția TAKE() care ne permite să preluăm dintr-un tabel definit anterior, în cazul de față ft, un anumit număr de linii sau coloane. Al doilea parametru din TAKE() este numărul de linii , care la mine este omis, iar al treilea parametru este numărul de coloane. Dacă specificăm valoarea 1 va prelua prima coloana din stânga, valoarea 2, primele două coloane din stânga, iar dacă trecem valori cu -1, -2 se vor prelua ultima coloană din dreapta dintr-un tabel definit sau ultimele două (-2).

Ultima operație din formula din T3 este dată de unificarea prin HSTACK() a variabilelor un (nume unice) și suma (valorile însumate).

Puteți să descărcați fișierul Excel din acest articol de aici. Vă reamintesc că funcțiile dinamice sunt disponibile doar în anumite versiuni de Excel.

În concluzia acestui articol: nu mai utilizați tabele merged… și nu mai colectați date în Excel. Folosiți SharePoint!

P.S. Sunt convins că această problemă se poate rezolva și cu Power Query… dar poate într-un articol viitor.

15/09/2023

Calcule clasice pe tabele merged în Excel

De foarte multe ori mi-a fost dat să văd în practica de zi cu zi diferite metode de colectare a datelor în Excel. De fiecare dată când am ocazia sau merg la cursuri în producție le recomand oamenilor să nu mai colecteze datele în Excel. Sunt zeci de alternative. Și dacă totuși o fac, măcar să nu mai țină datele în format merged.

În exemplul din imagine este un tabel cu 4 coloane, iar în coloana Nr. Crt și Nume Prenume este aplicat la anumite celule opțiunea de Merge cells (unirea mai multor celule).

De ce fac oamenii asta? Pentru că li se pare că este mai frumos… cred… dar niciodată nu cred că își pun problema de utilitate. Mai sunt cei care au auzit pe la ceva cursuri de baze de date despre conceptul de normalizare și eliminarea redundanțelor. Da, poate fi dubios să scrii un nume de mai multe ori, dar ca utilitate pentru calcule și operații este mult mai util.

O altă greșeală de operare din Excel pe care o întâlnim în acest caz este de a păstra datele în format de raport. Asta pentru eventualitatea când ar trebui să listeze acel tabel la imprimantă. Altfel, numărul curent nu are nici o relevanță atât timp cât datele sunt păstrate într-o ordine nespecificată… bazată probabil pe prima intrare.

Raportându-ne la principiul drumului știut, lași de cele mai multe ori oamenii să lucreze cum vor în acel Excel și să își facă treaba cum cred ei mai bine. Doar că apar probleme când trebuie să faci calcule și operațiuni sau verificări cu datele dintr-un astfel de tabel.

Și totuși cum facem un raport simplu, om cu om pe baza acelui tabel și suma aferentă?

Metoda 1: Subtotalurile

Pentru a realiza un subtotal pe merged tables trebuie să selectăm tot tabelul, după care accesăm meniul Data, Subtotal.

Avantajul este dat de timpul de totalizare a valorilor pentru fiecare Nume Prenume în cazul de față. De menționat că putem efectua mai multe tipuri de operații de bază (Use function) pe mai multe coloane specifice (At each change in) și putem efectua acea operație pe mai multe coloane.

Dezavantajul pentru tabelele cu multe linii este acela că nu poți face sau tipări prea simplu, un raport cu nume și total încasat de exemplu, pentru că odată cu restrângerea nivelurilor de subtotal (cifrele din stânga sus) ne apar doar valorile nu și liniile de sinteză.

Un mare dezavantaj, de fapt ca tot merge-ul în sine, este că dacă adăugăm o linie nouă pentru un Nume Prenume din tabel, va trebui să refacem merge-ul. Intre valorile vechi si celula nouă.

Metoda 2: Coloană adițională

Metoda coloanelor adiționale (ajutătoare) poate fi o tehnică foarte bună de rezolvare a problemelor complexe prin descompunerea acestora în subprobleme rezolvabile secvențial (treptat).

O propunere de rezolvare este de a implementa metoda redundanței pentru a putea face separat calcule sintetice.

În exemplul meu am adăugat coloana Linie (de fapt numărul curent pentru fiecare) și Nume, în care am completat cu o formulă simplă valorile de care ar fi nevoie pentru calcule într-un raport separat.

În celula E2 am scris formula: =IF(ISBLANK(A2);E1;A2)

În care: cheia este jucată de funcția ISBLANK(). Doar o singură celulă (cea de sus) dintr-un grup de celule merged conține valoarea afișată în grup. Restul sunt vide, de aceea le putem interpreta cu această funcție pentru a adăuga permanent valoarea de deasupra.

Marele avantaj al acestei metode este că permite folosirea de funcții de căutare pe tabel, de tipul INDEX cu MATCH dar și funcțiile dinamice.

Un model simplu de raport prezentat în imagine presupune utilizarea funcției SUMIF() pentru a aduna toate valorile de pe coloana Suma prin compararea coloanei F cu valoarea curentă.

Un dezavantaj al metodei este că de fiecare dată când adăugăm o linie nouă în tabel trebuie să copiem formula de mai sus pe coloana E și F pentru a putea obține raportul de Total corect.

Desigur că există și o metodă mai elaborată prin utilizarea funcțiilor dinamice, dar într-un alt articol.

Dacă vreți să exersați direct pe fișier, acesta poate fi descărcat de aici.

Sper să vă fie util!

14/09/2023

Braille translator in Excel

Îmi ești dragă ca lumina ochilor mei!

Probabil una din cele mai de presus expresii ale iubirii, rostite de multe ori fără semnificația profundă pentru mulți dintre noi.

La ora actuală se estimează că la nivel mondial sunt peste 36 de milioane de persoane nevăzătoare, din care aproximativ 100.000 în România. Sunt convins că există pe lumea asta multe aplicații software care-i ajută pe mulți să învețe să scrie și să citească în limbajul Braille. Vedem și noi astfel de simboluri prin din ce în ce mai multe locuri civilizate și nu le înțelegem semnificația.

Acest articol este o propunere de utilizare cu scopul de a genera text tipăribil în format Braille (vezi imagine 1). Există mai multe site-uri pentru a genera text Braille sau dispozitive electronice (foarte scumpe) de a scrie și citi Braille.

În cazul în care cineva de la o organizație a nevăzătorilor dispune de o astfel de imprimantă vă rog să testați și să mă contactați dacă este nevoie de îmbunătățiri asupra aplicației.

Fișierul poate fi deschis de la adresa: https://1drv.ms/x/s!ApGubfWFh8NurOhXSganso3njoqMmg?e=ZVGZ3o

Pentru a putea vedea cardurile generate ar fi indicat să activați partea de Turn on images.

În fișierul Excel am folosit noua funcție IMAGE() care permite aducerea unei imagini de pe internet într-o celulă și a efectua operațiuni cu ea.

În fișier, textul poate fi editat doar în zonele dedicate. Fișierul este creat în versiunea Excel din Microsoft 365 deci nu poate fi utilizat pe calculatoarele personale dacă nu dispuneți de o astfel de licență.

Fișierul este în editare publică, pentru a permite tuturor testarea. Orice utilizare în alte scopuri decât cele dedicate excedă responsabilitatea autorului.

UPDATE 17.08.2023

Mulțumesc tuturor celor care au distribuit acest articol. Am avut deosebita plăcere de a discuta cu oameni foarte faini legat de utilitatea acestui exercițiu și despre experiența lor cu lumea IT. În sinteză oamenii sunt bine, dar dacă fiecare dintre noi s-ar putea implica mai mult, ar fi și mai bine.

Cel mai mult m-a ajutat la validare și completare cu caractere românești domnul Aurel Pătru, profesor nevăzător la Liceul special Sfânta Maria din Arad.

Braille pentru ei este important pentru a putea citi în diferite locuri, dar cel mai mult îi ajută funcțiile de accesibilitate din sistemele de operare. Ei folosesc aceste funcții atât pe calculatoare/laptop-uri cât și pe telefoanele mobile. Au o nevoie de comunicare la fel ca toți ceilați și personal mă bucur că suntem împreună pe Internet sau alte canale de comunicare.

Astăzi am avut plăcerea să discut și cu dl Dan Patzelt de la http://www.tactileimages.org/ și mi-a povestit mult despre cum încearcă ei să ajute nevăzătorii și provocările tehnice și financiare pe care trebuie să le depășească.

Dincolo de exercițiul meu, care și-a atins scopul, utilitatea Excelului creat… nu este la fel de mare pe cât o credeam la început, pentru că ei scriu textul în clasic iar echipamentele de imprimare fac translatarea în alfabet Braille automat. :) (de asta probabil sunt și atât de scumpe).

Am actualizat fișierul Excel cu diactitice, cu specificarea faptului că sunt probleme la interpretarea literelor mari pentru diacritice. Acestea au același cod ASCII în Excel atât pentru litere mari cât și pentru cele mici… De asemenea, am modificat puți partea de printare în carduri pentru a răspunde cerinței de a avea maxim 32 x 18 carduri (cum le spun eu) pe o pagină A4.

END UPDATE

Povestea mai pe larg

Mă antrenez pentru Campionatul mondial de Excel eSports: https://www.fmworldcup.com/excel-esports/ Chiar vreau să ajung în primii 50 din lume, deci trebuie să trag tare.

Una din teme este manipularea caracterelor și codurilor prin funcțiile CHAR(), CODE(), UNICODE() și UNICHAR(). De asemenea sunt foarte importante funcțiile de parsare text și tabele, coloane, rânduri și reunificarea lor.

Studiind codurile unicode am descoperit și codurile pentru caracterele Braille. Fiecare simbol Braille reprezintă un set de puncte aranjate într-o matrice de 2×3. Aceasta oferă o structură uniformă pentru litere și cifre. Cu toate că majoritatea literelor majuscule sunt reprezentate printr-un singur simbol Braille, cifrele sunt reprezentate prin două simboluri alăturate. Acest lucru ajută la distingerea clară între cifre și litere într-un text Braille, reducând astfel confuzia și greșelile de interpretare.

Mai auzisem despre limbaj, dar acum aveam un motiv să abordez problema translatării cu adevărat. În foaia de calcul Sursa se află un tabel care conține ceea ce am găsit pe Braille ASCII – Wikipedia. Sunt mai multe simboluri dar am scos dintre ele caracterele speciale din Germană. De asemenea, în tabelul de pe Wikipedia sunt ceva probleme de reprezentare. Tot în sursa am introdus caracter cu caracter sursa imaginilor pentru carduri. Acestea sunt foarte importante pentru printare. Dacă folosești doar punctuația nu cred că este suficient pentru o imprimantă de acest gen. Pe coloana Dots se află punctuația în format 01 în care fiecare 1 reprezintă un punct de pe matricea de 2×3.

Propunerea mea de limbaj este una destul de simplă și am încercat să mă validez cu site-ul școlii: http://www.spdv.ro/braille/ care mi s-a părut a fi unul din cele mai stabile și sigure. Am folosit și site-uri pentru Engleză: https://wecapable.com/braille-translator/english-to-braille-converter/

Trebuie menționat că în sursă nu am diacriticele din limba română pentru că nu am găsit codurile Unicode pentru ele, neexistând o corespondență între vizual (setul de puncte – Dots) și unicode-ul zecimal de pe coloana DecUni.

Propunerea este simplă și prin faptul că nu este adaptată complexității metodelor de scriere din Engleză. M-am documentat pe site-ul https://en.wikipedia.org/wiki/English_Braille ca să înâeleg până unde se poate ajunge, dar mai este mult de lucru pentru acel format de scriere. Menționez că am interacționat cu „AI-urile” pentru a încerca să înțeleg mai repede și mai bine modul de scriere, dar dacă Bard este un pic mai răsărit ca iON () tot cu ChatGPT am ajuns să mă încurc cel mai tare. Problema majoră a lor este că oferă răspunsuri variate și contradictorii pentru aceleași întrebări. Problemele mele sunt legate de seturile de caractere Unicode, restul până la 256 din formatul Engleză (gradul 2), pentru care nu găsesc codurile cu echivalența, iar ChatGPT mi-a oferit variante și variante care mai de care mai lipsite de încredere, așa că pe moment am renunțat la ele. Dacă are cineva corespondența simbol – Unicode, Meaning vă rog să mă contactați.

Principala provocare a fost să înțeleg de ce sunt diferențe de reprezentare între Engleză și Română. De exemplu pentru Engleză literele mari sunt prefixate de cardul 000001 (~6) iar în română de cardul 000101 (~46). Aceasta este și propunerea articolului. De asemenea, există tehnici de scriere în care se scrie totul cu litere mari și literele mici sunt prefixate cu card specific.

Cel mai greu din punct de vedere tehnic mi-a fost să reprezint numerele dintr-un text. Numerele și în română și în engleză (grad 1) sunt prefixate de cardul 001111 (~3456) echivalentul # (diez) din setul de caractere ASCII.

Pentru asta am studiat separate descompunerea unui text în litere, apoi ca să-l pot compara cu litera anterioară sau următoare l-am generat în coloană separate. Am identificat așadar când apare prima literă într-o coloană și în acest caz i-am introdus un # (diez).

Formula din D7: =IF(ISNUMBER(–B7);IF(ISNUMBER(–C7);B7;”#”&B7);B7)

Pentru a descompune un text în fiecare literă a sa, am folosit funcția MID() cu SEQUENCE()

Formula din B7: =MID(B4;SEQUENCE(LEN(B4));1)

Ca să pot ulterior să o folosesc într-o singură funcție cu LET() a trebuit să pun cele două coloane pe o singură coloană, în format linie cu line (rows) apoi să le transform într-un nou tabel cu Wraprows() ca să pot să aplic fiecărei linii formula inițială din D7 folosind funcția BYROW().

Surpriza a fost că în momentul în care am încercat să le unific, opțiunea de TOCOL() din două variabile de tip coloană nu funcționează, așa că a trebuit să unesc textul celor două coloane în formatul: col1&col2 (rezultat în L6) după care să aplic o altă funcție decât MAP() sau BYROW() care nu funcționează pentru splitare. Astfel ca să pot splita o coloană în mai multe coloane (linie cu linie) a trebuit să construiesc funcția din M6:

Care mi-a permis să fac un tabel cu două coloane și căruia să-i pot aplica un BYROW() cu funcția din D7.

=LET(
Data; L6#;
LenData; LEN(Data);
Rows; COUNTA(Data);
NumCols; 2;
Tabel; SEQUENCE(Rows; NumCols; 1; 1);
CharIndex; MOD(Tabel - 1; LenData) + 1;
CharPosition; INT((Tabel - 1) / LenData) + 1;
INDEX(MID(Data; CharIndex; 1); CharPosition; SEQUENCE(1; NumCols))
)

A fost hard dar sper să se fi meritat și să fie de ajutor cuiva!

Pentru întrebări și comentarii puteți folosi secțiunea dedicată din site!

O zi luminoasă tuturor!

16/08/2023