un mio amicoSono spesso in contatto con lui per alcuni dei problemi del mondo reale che deve affrontare quando lavora con i dati in Excel.
Molte volte traduco la sua domanda in un tutorial di Excel su questo sito perché potrebbe essere utile anche per gli altri miei lettori.
Anche questo è uno di questi tutorial.
Il mio amico mi ha chiamato la scorsa settimana con le seguenti domande:
Ho i dati dell'indirizzo in una colonna in Excel e voglio identificare/filtrare le celle che contengono stringhe di testo (parole) ripetute nell'indirizzo.
Ecco un set di dati simile in cui vuole filtrare le celle (celle con frecce rosse) che contengono stringhe di testo ripetute:
Ciò che rende tutto questo difficile ora è che i dati non sono coerenti.Poiché si tratta di una raccolta di set di dati creati manualmente dai rappresentanti di vendita, potrebbero esserci discrepanze nei set di dati.
pensaci:
- Qualsiasi stringa di testo può essere ripetuta in questo set di dati.Ad esempio, può essere il nome di una regione o di una città o entrambi.
- Le parole sono separate da spazi e non c'è coerenza nel fatto che il nome della città sia dopo sei o otto caratteri.
- Esistono migliaia di tali record ed è necessario filtrare quelli che hanno stringhe di testo ripetute.
Dopo aver considerato molte opzioni come testo su colonne e formule, ho finalmente deciso di utilizzare VBA per fare il lavoro.
Quindi ho creato una funzione VBA personalizzata ("IdDuplicate") per analizzare queste celle e darmi VERO se ci sono parole duplicate nella stringa di testo e FALSO se non ci sono duplicati (come mostrato di seguito):
Questa funzione personalizzata analizza ogni parola nella stringa di testo e controlla quante volte si trova nel testo.Restituisce TRUE se il conteggio è maggiore di 1;Altrimenti restituisci FALSO.
Inoltre, è stato creato per contare solo le parole con più di tre caratteri.
Una volta che ho i dati VERO/FALSO, posso facilmente filtrare tutti i record VERO.
Ora lascia che ti mostri come farlo in Excel.
Codice VBA per la funzione personalizzata
Questo viene fatto creando una funzione personalizzata in VBA.Questa funzione può quindi essere utilizzata come qualsiasi altra funzione del foglio di lavoro in Excel.
Ecco il suo codice VBA:
Function IdDuplicates(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 Passaggio -1 Se 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 = "FALSE" SkipB: Fine della funzione
Come utilizzare questo codice VBA
Ora che hai il codice VBA, devi inserirlo nel back-end di Excel in modo che funzioni come una normale funzione del foglio di lavoro.
Ecco i passaggi per inserire il codice VBA nel backend:
- Vai alla scheda sviluppatore.
- Clicca su Visual Basic (puoi anche usare la scorciatoia da tastiera ALT+F11)
- Nel backend dell'editor VB aperto, fai clic con il pulsante destro del mouse su qualsiasi oggetto cartella di lavoro.
- Vai su "Inserisci" e fai clic su "Modulo".Questo inserirà l'oggetto modulo della cartella di lavoro.
- Nella finestra del codice del modulo, copia e incolla il codice VBA sopra menzionato.
Una volta che hai il codice VBA nel back-end, puoi utilizzare la funzione "IdDuplicates" come qualsiasi altra normale funzione del foglio di lavoro.
Questa funzione accetta un parametro, il riferimento di cella della cella in cui hai il testo.
Il risultato di questa funzione è VERO (se ci sono parole duplicate) o FALSO (se non ci sono duplicati).Una volta che hai questo elenco VERO/FALSO, puoi filtrare quelli con VERO per ottenere tutte le celle che contengono stringhe di testo ripetute.
Nota: il codice che ho creato serve solo a tenere conto delle parole più lunghe di tre caratteri.Ciò garantisce che se nella stringa di testo sono presenti parole lunghe 1, 2 o 3 caratteri (ad es. 12 A, KGM o LDA), tali parole vengano ignorate durante il conteggio dei duplicati.Puoi facilmente modificarlo nel codice, se necessario.
Questa funzionalità è disponibile solo nella cartella di lavoro in cui è stato copiato il codice nel modulo.Se desideri che questo codice sia disponibile anche in altre cartelle di lavoro, devi copiare e incollare questo codice in tali cartelle di lavoro.In alternativa, puoi creare un componente aggiuntivo (abilitando questo, la funzionalità sarà disponibile in tutte le cartelle di lavoro del tuo sistema).
Ricorda inoltre di salvare questa cartella di lavoro con un'estensione .xlsm (perché contiene codice macro).
