Funcția SUBSTITUTE() poate foarte utilă pentru corectarea textului în operațiunile de import sau după caz.

În multe cazuri am văzut oameni (inclusiv pe mine) care fac operațiunile de înlocuire a caracterelor cu ajutorul operațiunii Office Find and Replace (Ctrl+H).

Conform articolului citat mai sus Substitute() se folosește pentru a înlocui toate caracterele de un anumit fel cu alte caractere sau chiar a le elimina.

În exemplul de mai jos avem un caz concret. Caracterele românești sunt din ce în ce mai des folosite (inclusiv în documentele oficiale :) ) dar există suficient de multe probleme legate de sistemele de e-mail de exemplu sau de autentificare. Poate greșesc și forțez puțin cazul de utilizare. Pentru corecturi/completări vă rog folosiți cu încredere secțiunea comentarii.

În exemplul nostru ne este solicitată crearea automată a unui nume de utilizator de maxim 20 de caractere care să fie de forma prenume.nume.

Pentru acest lucru avem nevoie de mai multe funcții SUBSTITUTE() imbricate.

Alternativele ca formule la substitute() sunt combinațiile de funcții FIND() sau SEARCH() și REPLACE(). Nu uitați că funcția FIND() este case sensitive. De asemenea, returnează eroare dacă nu găsește caracterul în textul de căutare.

Problema cu funcția Replace() este că înlocuiește doar primul caracter întâlnit. Pe când Substitute() le înlocuiește pe toate.

Iată un exemplu în care căutăm un caracter cu Find() și îl înlocuim cu replace() și unul cu o formula substitute()

 

În exemplul nostru de început, pentru a înlocui toate diacriticele, spațiul sau liniuța am imbricat mai multe funcții formula din C3 fiind:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(B3));”-„;””);”ă”;”a”);”î”;”i”);”ș”;”s”);”ț”;”t”);”â”;”a”);” „;””)&”.”&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A3));”-„;””);”ă”;”a”);”î”;”i”);”ș”;”s”);”ț”;”t”);”â”;”a”);” „;””);20)

Puteți descărca fișierul Excel cu acest exemplu de la adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq_pgh5GWrX7vs2A2QQ

Sper să fie util cuiva! Nu uitați de comentarii pentru întrebări.

Recomandare de citit: Cele mai eficiente zece metode de curățare a datelor

În viața reală apar tot felul de cazuri și metode de utilizare ale Excel-ului. Unele probleme cu datele vin din însăși modul eronat de utilizare a acestuia și anume colectarea de date. În acest proces de colectare, eventual centralizat din surse multiple este posibil să înregistrăm duplicate, care ne pot afecta rezultate finale ale unor rapoarte. Alteori duplicatele nu apar din eroarea de colectare ci pur și simplu ca excepții de proces.

Acest articol își propune să pună la dispoziție câteva metode de determinare a numărului de înregistrări unice folosind o singură formulă, fără coloane ajutătoare, macros sau pivoți.

Metodele manuale, aparent mai simple sunt cele de formatare condițională pe duplicate (aplicabile la nivel de coloană) și filtrarea tabelului cu date pe diferite criterii, sau eliminarea duplicatelor din sursa de date direct (Meniul Data, Remove Duplicates). Se poate! Dar ca orice operațiune manuală… oameni fiind, mai putem și greși.

În continuarea acestui articol voi exemplifica mai multe metode de determinare a numărului de înregistrări unice.

Pentru a descărca fișierul acestui demo vă rog să accesați adresa: https://1drv.ms/x/s!ApGubfWFh8Nuq_lhIVWEAtZqt8rz1g

Tabelul de date conține o listă de agenți cu numărul lor de marcă, numele și orașul pe care l-au vizitat sau în care au efectuat operațiuni. Mai multe înregistrări sunt duplicate intenționat.

Trebuie specificat faptul că aceste modele de probleme nu se pot rezolva corect prin folosirea tehnicii tabelelor pivot pentru că în acestea nu avem posibilitatea de a număra unicitățile pe diferite criterii.

Determinarea numărului unic de înregistrări pe o coloană

În celula F3 este specificat numărul de înregistrări totale din tabel prin folosirea funcției COUNTA() care numără celulele de tip text dintr-un bloc de căsuțe.

Pentru a determina numărul unic de numere de marcă vom folosi în celula F5 formula array:

=SUMPRODUCT(1/COUNTIF(A2:A20;A2:A20))

În care COUNTIF(A2:A20;A2:A20) realizează o vector (array) cu numărul de apariții a fiecărui element din lista A2:A20 cu condiția să fie egal cu oricare valoare a elementului din lista A2:20. Este un artificiu care determină numărul de apariții. Apoi 1/numărul de apariții determină un vector de valori care însumate dar numărul unic de înregistrări.

Formulele array se introduc cu ajutorul combinației de taste CTRL+SHIFT+ENTER. La fel putem calcula numele unice și numărul de localități unice.

Determinarea numărului unic de apariții pe o coloană în funcție de condițiile de pe altă coloană

Dacă dorim să știm câți agenți unici au fost în fiecare localitate atunci va trebui să numărăm numele unice ale agenților (mai corect ar fi numerele de marcă) care au fost într-o localitate specificată în condiție. Este cazul tabelului de la F10.

Formula array din G11 va fi:

=SUM(–(FREQUENCY(IF($C$2:$C$20=F11;MATCH($B$2:$B$20;$B$2:$B$20;0));ROW($B$2:$B$20)-ROW($B$2)+1)>0))

În care se folosește o combinație de formule din care cele mai importante sunt IF() pentru stabilirea condiției problemei: numărarea agenților unici care au fost în localitatea respectivă, și FREQUENCY care este o formulă prin natura sa Array care determină frecvența de apariție a unui număr pe baza unei liste de valori unice (bit). Bit-ul este determinat prin combinația de comenzi ROW(). O traducere a formulei: însumează valorile rezultate în vectorul de determinare a frecvenței de apariție a numelor unice de agenți (funcția MATCH()) specifice orașului dat (funcția IF) pentru numele unice de pe B2 (funcțiile ROW()).

Hint: Pentru a putea vedea cum funcționează formula, puteți reduce numărul de înregistrări la 5, apoi apelați opțiunea Evaluate formula din meniul Formulas.

 

Determinarea valorilor unicelor pe baza a două condiții

În cazul în care dorim să facem un raport de tip matrice (asemănător unui tabel pivot) în care dorim să numărăm doar înregistrările unice apărute va trebui să folosim aceeași formulă ca în exemplul precedent. Pentru a specifica două condiții trebuie să specificăm acestea cu (condiție)*(condiție) în partea de IF iar numărarea să o facem pe coloana de marca:

Formula din G24 pentru determinarea numărului unic de deplasări ale unui agent într-un oraș (în formatul logic: 0 nu a fost, 1 a fost) este:

=SUM(–(FREQUENCY(IF(($C$2:$C$20=$F24)*($B$2:$B$20=G$23);MATCH($A$2:$A$20;$A$2:$A$20;0));ROW($A$2:$A$20)-ROW($A$2)+1)>0))

Sper să vă fie util și vă rog să consultați pentru mai multe explicații articolele din referințele de mai jos.

Resurse:

• Articole asemănătoare: #Excel – Funcția OFFSET()
• Playlist Youtube – Excel

 

Referințe web:

• Count Unique Values – O colecție extraordinară de articole despre formule, funcții și metode foarte fain explicate și exemplificate.
• Count unique text values with criteria – O altă colecție de articole care merită atenție pentru fanii Excel.