Acest articol pune la dispoziția cititorilor o metodă de interpretare a textelor, extragere date și numărare a lor, folosind Microsoft Excel 365. De menționat că există instrumente superioare pentru această procedură, scopul meu de a folosi Excelul fiind unul de îmbunătățire a tehnicii de creare a formulelor complexe în Excel.

Tehnica wordcount este utilă pentru a evalua lungimea sau densitatea textului și poate fi utilizată în diferite contexte, cum ar fi analiza de texte sau de documente, pentru a evalua complexitatea sau pentru a verifica dacă un text se încadrează într-o limită de cuvinte. In mod obișnuit, această tehnică se bazează pe algoritmi de procesare a limbajului natural (NLP) și implică împărțirea textului în cuvinte și numărarea lor. În plus, se pot lua în considerare și alți factori, cum ar fi eliminarea cuvintelor de legătură sau a cuvintelor mici (cum ar fi „a”, „și”, „la” etc.), pentru a obține un număr mai precis.

Tehnica wordcount poate fi realizată manual, prin numărarea cuvintelor pe care le vedeți pe o pagină, sau poate fi efectuată automat, prin intermediul unor instrumente specializate sau software de procesare a textului. Rezultatul poate varia în funcție de tehnica și instrumentele utilizate.

În exemplul pe care vi-l propun am preluat primele 2000 de paragrafe de text din Preludiul Fundației de Isaac Asimov (pentru generația TikTok, este vorba despre o carte SF ). În fișierul pus la dispoziție aici, am păstrat doar primele 500 de linii.

Un instrument gratuit destul de interesant pentru wordcount poate fi accesat on-line la adresa: https://www.online-utility.org/text/analyzer.jsp

Comparativ rezultatele obținute pe primele 2000 de paragrafe:

Diferența de apariții pentru personajele principale este destul de mică, ceea ce îmi confirmă faptul că metoda aplicată în Excel este fezabilă. Problema este consumul de resurse și procesor pentru a interpreta cantități mari de text, Excel nefiind un instrument dedicat pentru analize volume mari de date.

Etapele implementării în Excel

Dacă ați descărcat fișierul WordCount – Excel 500.xlsx, veți observa că are 3 pagini și definită o singură formulă Lambda() în Name manager. Funcția de substituție fReplace2 am definit-o în articolul: Recursive Lambda() sau cum să scapi de „cârnații” de Substitute() de Substitute() în #Excel și este folosită cu scopul de a elimina caracterele speciale, diacritice și o serie de cuvinte de legătură. Cuvintele și caracterele sunt înregistrate în foaia de calcul Lambda. Lista de acolo poate fi modificată după caz. La momentul scrierii acestui articol este posibil ca problemele de performanță pe volume mari de text să fie cauzate de funcția Offset() utilizată în fReplace2, pentru că odată cu creșterea numărului de cuvinte de eliminat funcționează din ce în ce mai greu. Aici se impun ceva optimizări.

În foaia de calcul Textul începând cu celula C2 am utilizat funcția:

=LET(result; 
     UNIQUE(LOWER(TEXTSPLIT(
            fReplace2(A2;Lambda!$A$2;Lambda!$B$2);" "))); 
            FILTER(result;result<>""))

în care variabila result definită cu funcția LET() îmi permite ulterior filtrarea cu FILTER a rezultatelor care nu sunt nule, rezultate în urma înlocuirii acestora cu funcția fReplace2. Am folosit Lower() ca să transform toate cuvintele în litere mici, cu scopul de a preveni duplicarea pe aceleași cuvinte scrise în diferite formate și locuri ale propozițiilor.

Pentru cei care nu doresc să deschidă fișierul Excel, aici imaginea rezultată ca despărțire a textului cu TEXTSPLIT pe baza caracterului spațiu (” „).

Pe coloana B am folosit o funcție simplă de numărare a numărului de coloane rezultat ca urmare a execuției funcției de pe C2.

=COLUMNS(C2#)

Având în vedere că nu știu câte coloane sunt rezultate în urma delimitării textului din A2, modul în care am numărat coloanele trebuia apelat dinamic, de aceea am folosit C2# care reprezintă modul de apelare a rezultatului unei funcții dynamic array.

Numărul maxim de coloane mă ajută ulterior în centralizarea cuvintelor în mod dinamic în funcție de textul introdus de utilizator în coloana A.

În foaia de calcul Analiza am două zone: zona de cuvinte unice și zona de filtrare a cuvintelor pe baza unui anumit număr de apariții:

Funcția utilizată pentru a obține cuvintele unice este destul de complicată. Aici am primit puțin ajutor de la ChatGPT, dar destul de greu cu el. Cel puțin partea de INDIRECT nu a reușit să o folosească.

=UNIQUE(
             LET(result;
                    SORT(LET(
                             data;INDIRECT("Textul!R2C3:R"&COUNTA(Textul!A:A)-1&"C"&MAX(Textul!B:B)-2;FALSE);
                            rows;ROWS(data);
                            cols;COLUMNS(data);
                            flatten;SEQUENCE(rows*cols;1;0);
                            values;UNIQUE(INDEX(data;MOD(flatten;rows)+1;INT(flatten/rows)+1));
                           values));
FILTER(result;(LEN(result)>3)*(NOT(ISNUMBER(VALUE(result)))))))

În funcția prezentată, definesc o variabilă rezult care va prelua datele sortate din următorul calcul, și care este filtrată pe ultima linie cu un FILTER, prin care exclud cuvintele mai mici sau egale cu 3 caractere (a, și, la etc) și cele care sunt de tip număr.

În al doilea LET() definesc alte variabile fiecare cu calculele lor. Variabila data (mândria mea din această formulă) este definită printr-o funcție INDIRECT complet dinamică în funcție de numărul de linii de text din foaia Textul și maximul de coloane din coloana B. Având în vedere că nu știu care este numărul maxim de coloane am utilizat acest INDIRECT în formatul R1C1 style (parametrul FALSE) de la final.

Variabilele rows și cols devin oarecum redundante pentru că le am deja în indirect, dar le folosesc pentru a simplifica celelalte calcule.

Variabila flatten o folosesc pentru a defini o secvență liniară de numere echivalentă numărului de linii și coloane din data. Rolul său devine un fel de căutare în matrice prin INDEX-ul din variabila values care se raportează dinamic la numărul de linii și coloane din data.

Pe coloana B am funcția de numărare condiționată CountIF()

=COUNTIF(Textul!$C$1:$IJ$2500;A3)

Aici nu am mai construit dinamic cu INDIRECT tabelul de date. Cu indirect funcția ar arăta:

=COUNTIF(INDIRECT("Textul!R2C3:R"&COUNTA(Textul!A:A)-1&"C"&MAX(Textul!B:B)-2;FALSE);A3)

În celula G2: am introdus o valoare echivalentă numărului minim de apariții a unui cuvânt. Având în vedere că zona de cuvinte unice este formată dintr-un array dinamic și o coloană de calcul pe baza lui, nu putem sorta datele după numărul de apariții. În F3: am introdus funcția:

=SORT(FILTER(A2:B9350;B2:B9350>G2);2;-1)

Care asigură sortarea după coloana 2 din blocul specificat, în ordine descendentă (-1) și filtrează valorile după valorile mai mari decât cele specificate în G2.

Cam asta ar fi. Multă muncă, dar un antrenament mental bun. Dacă v-a plăcut sau nu, nu ezitați să îmi lăsați un feedback.

Sper să fie util cuiva!

Problema criptării datelor, sau anonimizarea lor cum se spune „la GDPR” are foarte multe soluții ingenioase în diferite platforme. Am văzut ceva metode de criptare și în Excel, în mare parte prin utilizarea metodei Cifrul Cezar sau diferite tipuri de fonturi pentru ascunderea textului sau diferite combinații de funcții CHAR(), CODE().

Fișierele de descărcat pentru acest articol

Dacă aveți Office 365 și Excel-ul actualizat puteți descărca și utiliza fișierul direct de aici: Functie criptare.xlsx

Dacă nu aveți Office 365 dar aveți Google, puteți accesa fișierul de la această adresă: https://docs.google.com/spreadsheets/d/1jbcpZngfK9VA7OIVCsYEm3dRWUzMqBniV3FoXCD1-xc/edit?usp=sharing

Descriere problema

În fișierul din link avem coloana Nume Prenume pe care dorim să o criptăm după un anumit cod de criptare specificat în celula I2. Codul este alfanumeric și poate lua valori variate. Nu am testat pentru fraze întregi.

Ca să pot codifica un text după un alt text trebuie să folosim o funcție Lambda() pe care am salvat-o cu numele fCriptare. Pentru decriptare am folosit o altă funcție Lambda fDecriptare pe care am folosit-o pe coloana D dar și pe E pentru a testa funcția cu un cod incorect specificat in celula J2.

Cazul de utilizare clasic: transferul de date către entități externe. Criptezi datele originale despre nume in coloana C, după care copii ca valori rezultatele în alt fișier pe care vrei sa îl exporți transmiți cuiva. Dacă vrei să afle acele nume trebuie să-i trimiți codul de decriptare. Atenție, funcțiile Lambda sunt dependente de fișierul curent. Transmiterea către altcineva a unui fișier fără funcție va face imposibilă afișarea rezultatelor corecte. Funcțiile Lambda() pot fi transmise și ca text pur și simplu.

Vă reamintesc că separatorii în formulele pe localizare română sunt ; (punct și virgulă) iar în localizare pe engleză sunt , (virgula). Eu folosesc localizarea în română.

Funcția de criptare:

=LAMBDA(txt;cod;

LET(

c; LEN(cod);

txt_chars; MID(txt; SEQUENCE(LEN(txt)); 1);

cod_chars; MID(cod; MOD(SEQUENCE(LEN(txt))-1; c)+1; 1);

cript_chars; CHAR(MOD(CODE(txt_chars) – 32 + CODE(cod_chars) – 32; 94) + 32);

cript_text; TEXTJOIN(„”; TRUE; cript_chars); cript_text)

)

În care:

• LAMBDA(txt;cod;expr) este construcția care definește o funcție Lambda. Aceasta este o funcție fără nume, care poate fi definită și apelată într-un singur pas. În acest caz, txt și cod sunt argumentele funcției, iar expr este expresia pe care funcția o evaluează și returnează.
• LET() este o funcție care permite definirea unor variabile intermediare folosite în cadrul expresiei. În acest caz, variabilele intermediare sunt c, txt_chars, cod_chars, cript_chars și cript_text.
• LEN(cod) este o funcție care calculează lungimea șirului de caractere cod.
• MID(txt; SEQUENCE(LEN(txt)); 1) este o funcție care extrage un singur caracter din șirul de caractere txt folosind secvențele numerice generate de funcția SEQUENCE(LEN(txt)). Astfel, pentru fiecare poziție în șirul txt, se obține un singur caracter.
• MOD(SEQUENCE(LEN(txt))-1; c)+1 este o funcție care calculează o secvență de numere întregi care reprezintă pozițiile din codul de criptare pentru fiecare caracter din șirul txt. Prin MOD(SEQUENCE(LEN(txt))-1; c) se obține un vector cu numere de la 0 la c-1, iar adăugarea valorii 1 cu +1 asigură că vectorul va avea valori cuprinse între 1 și c.
• CHAR(MOD(CODE(txt_chars) – 32 + CODE(cod_chars) – 32; 94) + 32) este o funcție care calculează caracterele criptate pentru fiecare caracter din șirul txt. În primul rând, calculează codul Unicode pentru fiecare caracter din txt_chars și din cod_chars. Apoi, scade 32 din fiecare cod Unicode pentru a obține un număr întreg între 0 și 93. Acest număr întreg este apoi calculat cu modul de 94 și adăugat la 32 pentru a obține un alt număr întreg între 32 și 125, care este un alt interval de caractere ASCII imprimabile. Funcția CHAR() convertește acest număr întreg în caracterul ASCII corespunzător.
• TEXTJOIN(„”; TRUE; cript_chars) este o funcție care concatenează toate caracterele criptate obținute în șirul cript_chars. „” este separatorul folosit între fiecare caracter, iar TRUE specifică că orice valoare goală din cript_chars trebuie ignorată.

În cele din urmă, cript_text este variabila finală care conține șirul criptat obținut în urma evaluării.

Funcția SEQUENCE() din Excel este o funcție foarte interesanta, utilă pentru a genera o matrice sau un vector de numere întregi secvențiale. Această funcție poate fi utilă în multe scenarii, inclusiv în crearea de serii de numere pentru utilizarea în alte funcții sau în indexarea matricelor. Având în vedere că putem face un fel de iterații cu SEQUENCE(), mă duce cu gândul foarte aproape de instrucțiunea FOR din alte limbaje, cu limitările de rigoare legate de complexitatea calculelor și operațiilor.

Funcția de decriptare:

=LAMBDA(cript_text; cod;

LET(

c; LEN(cod);

cript_chars; MID(cript_text; SEQUENCE(LEN(cript_text)); 1);

cod_chars; MID(cod; MOD(SEQUENCE(LEN(cript_text))-1; c)+1; 1);

txt_chars; CHAR(MOD(CODE(cript_chars) – CODE(cod_chars); 94) + 32);

txt; TEXTJOIN(„”; TRUE; txt_chars)

; txt)

)

Este foarte asemănătoare cu funcția de criptare, doar că de data aceasta folosim operatiunea de scădere în variabila txt_chars.

Funcțiile Lambda sunt gestionate la mine cu Name Manager. Dar pot fi testate și în celule, de genul:

=LAMBDA(txt;cod;

executii)(A1;B1)

Cu presupunerea că în A1 este textul de criptat și în B1 codul de criptare.

Acest articol a fost scris cu ajutorul #ChatGPT după o referință de inspirație de la campionatul mondial de Excel: https://www.youtube.com/watch?v=Vg9qb134vzU&ab_channel=DiarmuidEarly

Sper să fie util cuiva!