Come categorizzare le transazioni bancarie in Excel?
Gestire le finanze personali o aziendali richiede spesso di esaminare un elenco dettagliato delle transazioni bancarie mensili. Questi registri possono includere una vasta gamma di descrizioni: da ristoranti e negozi a bollette e servizi. Il monitoraggio e l’analisi delle spese diventano molto più chiari quando ogni transazione viene assegnata a una categoria specifica, come ad esempio «Takeout», «Groceries», «Utilities» o «Family fee». Automatizzando in Excel la classificazione in base a determinate parole chiave presenti nelle descrizioni delle transazioni, è possibile ottenere informazioni precise sui costi e sugli schemi di spesa mensili.
Come illustrato nello screenshot seguente, si supponga di avere dati grezzi con i dettagli del fornitore o del servizio nella colonna B e di voler assegnare categorie semplificate (ad esempio, qualsiasi transazione contenente ")Mc Donalds« deve essere identificata come »Takeout«, mentre »Walmart« viene classificata come »Family fee") secondo regole personali o organizzative. Questa guida passo-passo presenta diverse soluzioni pratiche, tra cui metodi basati su formule e automazione avanzata.
Indice:
Categorizzare le transazioni bancarie con una formula in Excel
Codice VBA – Automatizzare la categorizzazione con una macro basata su un elenco predefinito
Altri metodi integrati di Excel – Utilizzare Power Query con logica basata su colonne condizionali
Categorizzare le transazioni bancarie con una formula in Excel
Per un approccio semplice e basato su formule per categorizzare le transazioni bancarie in Excel, segui questi passaggi. Questo metodo è perfetto per chi cerca risultati rapidi ed è a proprio agio nel gestire una piccola tabella di ricerca con parole chiave e categorie.
Innanzitutto, crea due “colonne di supporto” al di fuori dell’elenco principale delle transazioni: una colonna contenente le parole chiave (corrispondenti ai possibili contenuti nella descrizione della transazione) e una seconda colonna con la categoria da associare a ciascuna parola chiave.
1. In questo esempio, inserisci tutte le parole chiave da abbinare (come «Mc Donalds», «Walmart», ecc.) nella colonna A, dalla riga 30 alla riga 41, e i relativi nomi delle categorie («Takeout», «Family fee», ecc.) nella colonna B, sempre tra la riga 30 e la riga 41.
Se il set di parole chiave o di categorie è più ampio o cambia frequentemente, basta adeguare gli intervalli in modo da coprire tutte le tue esigenze. Guarda lo screenshot:
2. Successivamente, fai clic sulla prima cella della colonna di destinazione (ad esempio, F3 accanto all’ultima colonna dei tuoi dati), quindi inserisci questa formula matriciale. Premi Ctrl + Maiusc + Invio (non solo Invio) per confermare, poiché si tratta di una formula matriciale. Questa cercherà la prima parola chiave trovata nella descrizione e restituirà la categoria corrispondente.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),«Other»)
Una volta verificato il corretto funzionamento della formula per la prima riga, trascina la maniglia di riempimento automatico lungo la colonna per applicare la formula di categorizzazione a tutte le righe rimanenti delle transazioni.
Spiegazione dei parametri:
Analisi pro e contro:Questo approccio basato su formule garantisce un’impostazione rapida e una manutenzione minima, a condizione che le regole di categorizzazione non subiscano cambiamenti frequenti. Tuttavia, se l’elenco delle parole chiave si complica o richiede aggiornamenti ricorrenti delle categorie, gestire le colonne di supporto e le formule può diventare tedioso; in tal caso, potresti prendere in considerazione l’automazione tramite VBA o Power Query.
Suggerimento pratico: Se più parole chiave corrispondono a una descrizione, verrà considerata soltanto la prima presente nell'elenco. Per dare priorità a una parola chiave specifica, posizionala più in alto nella colonna di ricerca.
Risoluzione comune dei problemi:In caso di risultati imprevisti, ricontrollare l'intervallo di ricerca e assicurarsi che le parole chiave siano scritte in modo coerente e completo. Verificare anche la presenza di spazi aggiuntivi o differenze di formattazione nelle descrizioni delle transazioni.
Codice VBA – Automatizzare la categorizzazione con una macro che abbina le descrizioni delle transazioni alle categorie in base a un elenco predefinito
Questa soluzione sfrutta una macro VBA per automatizzare l’abbinamento delle transazioni alle categorie, garantendo maggiore controllo e scalabilità. È ideale per chi gestisce grandi volumi di transazioni o desidera rendere dinamico il collegamento tra parole chiave e categorie, riducendo al minimo la gestione manuale delle formule.
Scenario applicabile: Quando l'elenco delle transazioni è lungo, gli aggiornamenti sono frequenti o si desidera evitare la gestione manuale delle formule, le macro VBA possono elaborare automaticamente ogni descrizione e assegnare la categoria più adatta in modo efficiente, grazie a logiche personalizzabili e avvisi mirati.
Procedura operativa:
- Prepara un elenco di parole chiave con le relative categorie, analogo alle colonne di supporto impiegate nel metodo basato su formule (ad esempio, nelle colonne A e B a partire dalla riga 30).
- Premere Alt + F11 per aprire l'editor di Visual Basic for Applications. Nella finestra VBA, fare clic su Inserisci > Modulo per aggiungere un nuovo modulo.
Copiare e incollare il seguente codice nel modulo:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubModalità d'uso:
- Fai clic sul pulsante
Esegui oppure premi F5nell'editor VBA per avviare la macro. La macro elaborerà ogni descrizione di transazione nella colonna B, la confronterà con l'elenco delle parole chiave e inserirà la categoria corrispondente (o «Other» se non trova alcuna corrispondenza) nella colonna F della stessa riga. - È possibile modificare Imposta numero di righe/intervalli all'interno della macro in base al layout dei propri dati, ad esempio cambiando la posizione iniziale delle descrizioni delle transazioni o quella dell'elenco di supporto. Assicurarsi che l'elenco delle parole chiave non contenga celle vuote e che le categorie siano chiare e univoche per un facile riferimento.
Esegui oppure premi F5nell'editor VBA per avviare la macro. La macro elaborerà ogni descrizione di transazione nella colonna B, la confronterà con l'elenco delle parole chiave e inserirà la categoria corrispondente (o «Other» se non trova alcuna corrispondenza) nella colonna F della stessa riga.Analisi pro e contro:La soluzione VBA si adatta con facilità a regole più complesse, può essere rieseguita in qualsiasi momento dopo aver aggiornato l’elenco di parole chiave o categorie ed elimina la necessità di utilizzare formule matriciali. Tuttavia, le macro richiedono l’abilitazione delle operazioni automatizzate in Excel, il che potrebbe non essere adatto a tutti gli utenti o a determinati ambienti.
Suggerimento pratico: Salva la macro VBA in un file riutilizzabile ed esegui sempre un backup dei dati prima di lanciare la macro, nel caso si verifichino sovrascritture accidentali.
Suggerimenti per la risoluzione dei problemi:Se le categorie non vengono aggiornate come previsto, verificare che gli elenchi di parole chiave e categorie siano allineati e che non vi siano caratteri nascosti nelle celle. VBA non distingue tra maiuscole e minuscole se si utilizza «vbTextCompare», ma le discrepanze possono comunque verificarsi a causa della formattazione.
Altri metodi integrati di Excel – Utilizzare Power Query per impostare una categorizzazione basata su regole tramite logica di colonne condizionali
Per chi preferisce un approccio moderno e senza codice, Power Query offre un metodo efficace per categorizzare le transazioni bancarie. È la soluzione ideale per importare dati da file CSV o fonti online e per gestire regole di categorizzazione dinamiche ed evolutive: centralizza la logica e permette aggiornamenti rapidi, senza ricorrere a formule complesse o script VBA.
Procedura operativa:
- Innanzitutto, assicurati che le transazioni siano formattate come tabella o come intervallo dati strutturato. Seleziona una qualsiasi cella della tabella, quindi vai su Dati > Da tabella/intervallo per caricare i dati in Power Query.
- Nell'editor di Power Query, fai clic su Aggiungi colonna > Colonna condizionale per creare una nuova colonna basata su regole per le categorie.
- Definisci le tue regole di corrispondenza, ad esempio:
- Se Descrizionecontiene «Mc Donalds», restituisci «Takeout»
- Se Descrizionecontiene «Walmart», restituisci «Family fee»
- Altrimenti, restituisci «Other»
- Fai clic su OK per creare la colonna condizionale, quindi seleziona Chiudi e carica per restituire i risultati categorizzati in Excel.
Analisi pro e contro:Power Query offre un’eccezionale flessibilità per modificare le regole, si integra perfettamente con l’importazione di dati esterni e aggiorna istantaneamente i risultati non appena vengono apportate modifiche a dati o regole. Inoltre, gestisce set di dati molto grandi in modo decisamente più efficiente rispetto alle formule create manualmente. Tuttavia, richiede una configurazione iniziale e una certa familiarità con la sua interfaccia, che potrebbe risultare nuova per alcuni utenti.
Suggerimenti pratici:Disponi le tue regole in ordine di priorità (dall’alto verso il basso) all’interno della finestra di dialogo Colonna condizionale; verrà applicata la prima regola corrispondente. Puoi modificare, aggiungere o eliminare facilmente le regole in Power Query senza alterare le formule principali del foglio di lavoro Excel.
Avvisi sugli errori: Se importi nuove transazioni e il risultato non viene aggiornato come previsto, fai clic su Aggiorna nella scheda Dati di Excel. Presta attenzione all’ortografia e alle corrispondenze esatte delle parole chiave in Power Query, poiché anche lievi discrepanze potrebbero impedire l’assegnazione della categoria corretta.
Suggerimento per la risoluzione dei problemi:Se le tue categorie non vengono visualizzate correttamente, rivedi le regole della colonna condizionale per individuare eventuali conflitti o casi mancanti. È utile testare alcune voci di esempio prima di applicare le modifiche all’intero set di dati.
In sintesi, che tu opti per le formule matriciali, la flessibilità delle macro VBA o l’automazione semplificata di Power Query, Excel ti offre diversi metodi pratici per categorizzare in modo efficiente le tue transazioni bancarie. Rivedi sempre le tue regole di categorizzazione e la coerenza dei risultati man mano che i dati delle transazioni o i criteri di categorizzazione si evolvono.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi