Unul dintre prietenii mei lucrează într-o companie de analiză medicală. De multe ori se conectează cu mine la unele dintre problemele din viața reală cu care se confruntă în timp ce lucrează cu date în Excel.
De multe ori, îi convertesc întrebările în tutoriale Excel pe acest site, deoarece ar putea fi util și pentru ceilalți cititori ai mei.
Acesta este, de asemenea, un astfel de tutorial.
Prietenul meu m-a sunat săptămâna trecută cu următoarea problemă:
Există date despre adrese într-o coloană din Excel și vreau să identific / filtrați celulele în care adresa are șiruri de text duplicat (cuvinte) în ea.
Iată setul de date similar în care a dorit să filtreze celulele care au un șir de text duplicat (cele cu săgeți roșii):
Acum, ceea ce face acest lucru dificil este că nu există coerență în aceste date. Deoarece aceasta este o compilație a setului de date care a fost creat manual de reprezentanții vânzărilor, pot exista variații în setul de date.
Gandeste-te la asta:
- Orice șir de text se poate repeta în acest set de date. De exemplu, ar putea fi numele zonei sau numele orașului sau ambele.
- Cuvintele sunt separate de un caracter spațial și nu există nicio consecvență dacă numele orașului ar fi acolo după șase cuvinte sau opt cuvinte.
- Există mii de înregistrări de acest gen, iar nevoia este de a filtra acele înregistrări în care există șiruri de text duplicat.
După ce am analizat multe opțiuni (cum ar fi textul în coloane și formule), am decis în cele din urmă să folosesc VBA pentru a face acest lucru.
Așa că am creat o funcție VBA personalizată („IdDuplicate”) pentru a analiza aceste celule și a-mi da ADEVĂRAT dacă există un cuvânt duplicat în șirul de text și FALS în cazul în care nu există repetări (așa cum se arată mai jos):
Această funcție personalizată analizează fiecare cuvânt din șirul de text și verifică de câte ori apare în text. Dacă numărul este mai mare de 1, acesta returnează TRUE; altfel se întoarce FALS.
De asemenea, a fost creat pentru a număra doar cuvinte de peste trei caractere.
Odată ce am datele ADEVĂRATE / FALSE, pot filtra cu ușurință toate înregistrările care sunt ADEVĂRATE.
Acum permiteți-mi să vă arăt cum să faceți acest lucru în Excel.
Cod VBA pentru funcția personalizată
Acest lucru se face prin crearea unei funcții personalizate în VBA. Această funcție poate fi apoi utilizată ca orice altă funcție de foaie de lucru în Excel.
Iată codul VBA pentru acesta:
Funcția IdDuplicate (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Pasul -1 Dacă Len (StringtoAnalyze (i)) <minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALS" SkipB: Funcție de sfârșit
Mulțumim Walter pentru sugerarea unei abordări mai bune a acestui cod!
Cum se folosește acest cod VBA
Acum că aveți codul VBA, trebuie să îl plasați în backend-ul Excel, astfel încât să poată funcționa ca o funcție obișnuită de foaie de lucru.
Mai jos sunt pașii pentru a pune codul VBA pe backend:
- Accesați fila Dezvoltator.
- Faceți clic pe Visual Basic (puteți utiliza și comanda rapidă de la tastatură ALT + F11)
- În back-end-ul VB Editor care se deschide, faceți clic dreapta pe oricare dintre obiectele din registrul de lucru.
- Accesați „Inserați” și faceți clic pe „Modul”. Aceasta va insera obiectul modulului pentru registrul de lucru.
- În fereastra Codului modulului, copiați și lipiți codul VBA menționat mai sus.
După ce aveți codul VBA în partea din spate, puteți utiliza funcția - „IdDuplicates” ca orice altă funcție obișnuită a foii de lucru.
Această funcție acceptă un singur argument, care este referința celulei celulei în care aveți textul.
Rezultatul funcției este ADEVĂRAT (dacă există cuvinte duplicat în ea) sau FALS (dacă nu există duplicate). Odată ce aveți această listă de ADEVĂRAT / FALS, puteți să le filtrați pe cele cu ADEVĂRAT pentru a obține toate celulele care au șiruri de text duplicate în ea.
Notă: Am creat codul numai pentru a lua în considerare acele cuvinte care au mai mult de trei caractere. Acest lucru asigură că, dacă există 1, 2 sau 3 cuvinte de caractere (cum ar fi 12 A, K G M sau L D A) în șirul de text, acestea sunt ignorate în timp ce se numără duplicatele. Dacă doriți, puteți schimba cu ușurință acest lucru în cod.
Această funcție va fi disponibilă numai în registrul de lucru în care ați copiat codul din modul. În cazul în care doriți ca acest lucru să fie disponibil și în alte registre de lucru, trebuie să copiați și să lipiți acest cod în acele registre de lucru. Alternativ, puteți crea și un program de completare (activare care ar face această funcție disponibilă în toate registrele de lucru din sistemul dvs.).
De asemenea, nu uitați să salvați acest registru de lucru în extensia .xlsm (deoarece are un cod macro în el).
