Uno dei miei amici lavora in un'azienda di analisi sanitaria. Si connette spesso con me su alcuni dei problemi della vita reale che affronta mentre lavora con i dati in Excel.
Molte volte, converto le sue domande in tutorial di Excel su questo sito, poiché potrebbe essere utile anche per gli altri miei lettori.
Questo è anche uno di questi tutorial.
Il mio amico mi ha chiamato la scorsa settimana con il seguente problema:
Ci sono dati sull'indirizzo in una colonna in Excel e voglio identificare/filtrare le celle in cui l'indirizzo contiene stringhe di testo (parole) duplicate.
Ecco il set di dati simile in cui voleva filtrare le celle che contengono una stringa di testo duplicata (quelle con le frecce rosse):
Ora, ciò che rende tutto questo difficile è che non c'è coerenza in questi dati. Poiché si tratta di una raccolta di set di dati che è stata creata manualmente dai rappresentanti di vendita, possono esserci variazioni nel set di dati.
Considera questo:
- Qualsiasi stringa di testo può essere ripetuta in questo set di dati. Ad esempio, potrebbe essere il nome dell'area o il nome della città o entrambi.
- Le parole sono separate da uno spazio e non c'è coerenza nel fatto che il nome della città sia presente dopo sei parole o otto parole.
- Esistono migliaia di record come questo e la necessità è filtrare quei record in cui sono presenti stringhe di testo duplicate.
Dopo aver considerato molte opzioni (come testo in colonne e formule), ho finalmente deciso di utilizzare VBA per farlo.
Quindi ho creato una funzione VBA personalizzata ("IdDuplicate") per analizzare queste celle e darmi VERO se c'è una parola duplicata nella stringa di testo e FALSO nel caso non ci siano ripetizioni (come mostrato di seguito):
Questa funzione personalizzata analizza ogni parola nella stringa di testo e controlla quante volte si verifica nel testo. Se il conteggio è maggiore di 1, restituisce TRUE; altrimenti restituisce FALSE.
Inoltre, è stato creato per contare solo le parole che superano i tre caratteri.
Una volta che ho i dati VERO/FALSO, posso facilmente filtrare tutti i record che sono VERI.
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 codice VBA per questo:
Funzione 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 If 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: End Function
Grazie Walter per aver suggerito un approccio migliore a questo codice!
Come utilizzare questo codice VBA
Ora che hai il codice VBA, devi inserirlo nel backend di Excel, in modo che possa funzionare come una normale funzione del foglio di lavoro.
Di seguito sono riportati i passaggi per inserire il codice VBA nel backend:
- Vai alla scheda Sviluppatore.
- Fai clic su Visual Basic (puoi anche utilizzare la scorciatoia da tastiera ALT + F11)
- Nel back-end di VB Editor che si apre, fai clic con il pulsante destro del mouse su uno qualsiasi degli oggetti della cartella di lavoro.
- Vai su "Inserisci" e fai clic su "Modulo". Questo inserirà l'oggetto modulo per la cartella di lavoro.
- Nella finestra Codice 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 singolo argomento, che è il riferimento di cella della cella in cui hai il testo.
Il risultato della funzione è TRUE (se ci sono parole duplicate) o FALSE (se non ci sono duplicati). Una volta che hai questo elenco di VERO/FALSO, puoi filtrare quelli con VERO per ottenere tutte le celle che contengono stringhe di testo duplicate.
Nota: ho creato il codice solo per considerare quelle parole che sono lunghe più di tre caratteri. Ciò garantisce che se nella stringa di testo sono presenti parole lunghe 1, 2 o 3 caratteri (come 12 A, K G M o L D A), queste vengano ignorate durante il conteggio dei duplicati. Se lo desideri, puoi facilmente modificarlo nel codice.
Questa funzione sarà disponibile solo nella cartella di lavoro in cui hai copiato il codice nel modulo. Nel caso in cui desideri che sia disponibile anche in altre cartelle di lavoro, devi copiare e incollare questo codice in quelle cartelle di lavoro. In alternativa, puoi anche creare un componente aggiuntivo (abilitazione che renderebbe disponibile questa funzione in tutte le cartelle di lavoro del tuo sistema).
Inoltre, ricorda di salvare questa cartella di lavoro con l'estensione .xlsm (poiché contiene un codice macro).
