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!

În urmă cu ceva timp scrisesem un articol despre Operațiuni cu data și timpul în #Excel. Mi se părea că am atins majoritatea operațiilor… dar se pare că nu.

Una din operațiile care ar fi necesare este aceea a adăugarea unui număr de luni la o dată. Se utilizează de obicei la contracte și alte scadențe.

În acest articol vă propun două metode de rezolvare.

 

Funcția EDATE()

Această funcție este disponibilă doar în versiunile de Office 365 și Google Docs.

Sintaxa:

=EDATE(data_start; număr luni)

Exemplificare:

În cazul în care doriți să adăugați un număr de ani la o dată se folosește tot EDATE() cu parametrul 2: numărul de ani*12.

 

Funcția MONTH()

În cazul în care nu aveți versiunea de Office 365 trebuie să folosiți o combinație de funcții de descompunere a datei originale în ani (YEARS), luni (MONTH) și zile (DAY). La data se adaugă numărul de luni din perioadă și se recompune totul ca dată cu ajutorul funcției DATE().

Exemplu de formulă:

=DATE(YEAR(A2);MONTH(A2)+B2;DAY(A2))

 

 

Sper să fie util!

Zilele trecute am publicat articolul: Determinarea numelor unice dintr-un tabel #Excel dar, chiar dacă soluția propusă funcționează, ceva nu mi-a dat pace și revin cu un update în acest articol.

Soluția propusă de mine este oarecum greu de înțeles și replicat, plus că utilizează prea multe formule.

Mi-a plăcut soluția propusă de Ciprian în comentarii prin care propune utilizarea unui Power Query elegant de uniune a mai multor coloane. Soluția lui implică transformarea tabelului într-un Table (Table1) și apelarea coloanelor după denumirea lor, transformarea într-o listă de valori și combinarea coloanelor cu List.Combine, determinarea numelor unice cu List.Distinct, retransformarea în tabel, sortarea tabelului și eliminarea celulelor cu valoarea null.

let

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

#”Changed Type” = Table.TransformColumnTypes(Source,{{„Echipa 1”, type text}, {„Echipa 2”, type text}, {„Echipa 3”, type text}, {„Echipa 4”, type text}}),

#”Lista cu toti” = List.Combine({Table.Column(#”Changed Type”, „Echipa 1″),Table.Column(#”Changed Type”, „Echipa 2″),Table.Column(#”Changed Type”, „Echipa 3″),Table.Column(#”Changed Type”, „Echipa 4”)}),

#”Lista cu unici” = List.Distinct(#”Lista cu toti”),

#”Tabel cu unici” = Table.FromList(#”Lista cu unici”, null, {„Unici”}),

#”Unici sortati” = Table.Sort(#”Tabel cu unici”,{{„Unici”, Order.Ascending}}),

#”Unici reali” = Table.SelectRows(#”Unici sortati”, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {„”, null})))

in

#”Unici reali”

O mică problemă cu soluția lui Ciprian este că de fiecare dată când ai o sursă de date nouă, trebuie să refaci mulți din pași și trebuie mult edit manual în Advanced Editorul din Power Query.

 

Funcția vStack()

Funcția este o funcție nouă de tip dynamic array care permite unirea mai multor coloane sau linii într-una singură. Funcția are ca parametrii coloanele, blocurile de celule sau tabelele pe care dorim să le consolidăm într-o singură coloană.

În exemplu nostru ca să aducem numele unice din tabelul cu date, fără nici un cap de tabel, formula foarte simplă ar fi:

L1: =SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8)))

În care vstack() adduce datele de pe cele 4 coloane, unique extrage valorile unice iar sort le aranjează în ordine alfabetică.

Din considerente „artistice” nu putem permite valori 0 în rezultatul unei formule de tip dynamic array.

Pentru a elimina rezultatul cu 0, în celula I1 am utilizat formula:

=VSTACK({„Nume Unic”};LET(vNume;SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8)));FILTER(vNume;vNume<>””)))

În care:

• VSTACK({„Nume unic”}; – îmi permite definirea „capului de tabel”. Nu poate fi folosit în următorul VSTACK pentru că sortarea din SORT mi-ar pune capul de tabel la mijlocul șirului.
• LET(vNume;SORT(UNIQUE(VSTACK(A2:A8;B2:B8;C2:C8;D2:D8))); – Îl folosesc pentru definirea variabilei vZero ca rezultat al unicității de VSTACK. Variabila o folosesc ulterior în partea de filtrare pentru eliminarea valorilor zero.
• FILTER(vNume;vNume<>””) – este folosit pentru eliminarea din rezultatul execuției a valorilor rezultat 0 corespondente celulelor goale din blocul de valori.

De menționat că toate funcțiile sunt disponibile și în Google Spreadsheets. Pur și simplu am copiat formula și tabelul din Excel și le-am introdus într-o foaie nouă.

 

Simplificarea operațiunilor de Consolidare

 

Ceea ce văd eu cu adevărat puternic în utilizarea VStack este reprezentat de simplificarea operațiunilor de consolidare. Avem mai multe tabele în mai multe foi de calcul. Pentru a centraliza toate datele într-un singur tabel, avem destul de multe variante. Dintre toate acum Vstack devine mai simplu chiar decât operațiunile cu Power Query

Exemplificare vizuală:

În centralizatorul toate aducem toate datele din toate foile de calcul cu o singură formulă:

=VSTACK(Iasi!A2:E25;Vaslui!A3:E17;Bacau!A3:E17;Neamt!A3:E17;Suceava!A3:E17)

Formula ar arăta și mai bine dacă am defini tabelele ca Tables. Doar că în acest caz trebuie să definim capul de tabel înainte de realizarea formulei.

 

Sper să fie util cuiva!

 

Referințe suplimentare:

https://exceljet.net/functions/vstack-function

https://www.ablebits.com/office-addins-blog/combine-ranges-arrays-excel-vstack-hstack/