¿Cómo categorizar transacciones bancarias en Excel?
Gestionar sus finanzas personales o empresariales normalmente implica revisar una lista detallada de transacciones bancarias mensuales. Estos registros pueden incluir una amplia variedad de descripciones: desde restaurantes y tiendas hasta servicios públicos u otros gastos. El seguimiento y análisis de sus gastos resulta mucho más claro cuando puede asignar cada transacción a una categoría específica, como «Takeout», «Groceries», «Utilities» o «Family fee». Al automatizar la clasificación en Excel según palabras clave presentes en las descripciones de las transacciones, obtendrá información precisa sobre sus costes y patrones de gasto mensuales.
Tal como se muestra en la siguiente captura de pantalla, imagine que dispone de datos brutos con detalles del proveedor o servicio en la columna B y desea aplicar un conjunto simplificado de categorías (por ejemplo, cualquier transacción que contenga ")Mc Donalds« debe identificarse como »Takeout«, mientras que »Walmart« se clasifica como »Family fee") según sus propias reglas personales u organizativas. Este tutorial paso a paso explora diversas soluciones prácticas, incluidos métodos basados en fórmulas y enfoques avanzados de automatización.
Contenido:
Categorizar transacciones bancarias con fórmula en Excel
Código VBA: Automatice la categorización con una macro basada en una lista predefinida
Otros métodos integrados en Excel: Utilice Power Query con lógica de columnas condicionales
Categorizar transacciones bancarias con fórmula en Excel
Si busca un enfoque sencillo basado en fórmulas para categorizar transacciones bancarias en Excel, siga estos pasos. Este método es ideal para usuarios que necesitan resultados rápidos y se sienten cómodos gestionando una pequeña tabla de búsqueda con palabras clave y sus categorías correspondientes.
En primer lugar, cree dos «columnas auxiliares» fuera de su lista principal de transacciones: una con palabras clave (que correspondan al contenido que podría aparecer en la descripción de la transacción) y otra con la categoría que desea asociar a cada palabra clave.
1. En este ejemplo, incluya todas las palabras clave que desee buscar (como «McDonald's», «Walmart», etc.) en la columna A, desde la fila 30 hasta la 41, y los nombres de las categorías correspondientes («Takeout», «Family fee», etc.) en la columna B, del mismo rango (filas 30 a 41).
Si su lista de palabras clave o categorías es más extensa o cambia con frecuencia, simplemente ajuste los rangos para cubrir todas sus necesidades. Vea la captura de pantalla:
2. A continuación, haga clic en la primera celda de su columna de resultados deseada (por ejemplo, F3 junto a la última columna de sus datos) e introduzca esta fórmula matricial. Pulse Ctrl + Mayús + Entrar (no solo Entrar) para confirmarla, ya que se trata de una fórmula matricial. Así buscará la primera palabra clave encontrada en la descripción y devolverá la categoría coincidente.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),«Other»)
Una vez que la fórmula funcione en la primera fila, arrastre el controlador de autorrelleno hacia abajo por la columna para aplicar la fórmula de categorización al resto de las filas de transacciones.
Explicación de parámetros:
Análisis de ventajas e inconvenientes:Este enfoque basado en fórmulas permite una configuración rápida y un mantenimiento reducido siempre que sus reglas de categorización no cambien con frecuencia. No obstante, si su lista de palabras clave se vuelve más compleja o necesita actualizar las categorías de forma habitual, gestionar columnas auxiliares y fórmulas puede resultar tedioso, por lo que podría plantearse automatizar el proceso mediante VBA o Power Query.
Consejo práctico: Si varias palabras clave coinciden en una descripción, solo la primera que aparezca en su lista determinará la categoría. Para priorizar una palabra clave concreta, colóquela al principio de su columna de búsqueda.
Resolución de problemas habituales:Si obtiene resultados inesperados, revise el rango de búsqueda y asegúrese de que las palabras clave estén escritas de forma coherente y completa. Compruebe también si hay espacios adicionales o diferencias de formato en las descripciones de sus transacciones.
Código VBA: Automatice la categorización con una macro que asocie descripciones de transacciones a categorías basándose en una lista predefinida
Esta solución emplea una macro de VBA para automatizar la asignación de transacciones a categorías, ofreciendo un mayor control y escalabilidad. Es ideal para usuarios con grandes volúmenes de transacciones o para quienes deseen vincular dinámicamente palabras clave a categorías y reducir al mínimo la gestión manual de fórmulas.
Escenario de aplicación: Cuando la lista de transacciones es larga, las actualizaciones son frecuentes o desea evitar mantener fórmulas manuales, las macros de VBA pueden procesar cada descripción y asignar la categoría adecuada de forma más eficiente, con lógica personalizable y avisos.
Pasos operativos:
- Prepare una lista de palabras clave organizadas por categorías, similar a las columnas auxiliares empleadas en el método basado en fórmulas (por ejemplo, en las columnas A y B, a partir de la fila 30).
- Pulse Alt + F11 para abrir el editor de Visual Basic para Aplicaciones. En la ventana de VBA, haga clic en Insertar > Módulo para añadir un nuevo módulo.
Copie y pegue el siguiente código en el módulo:
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 SubCómo utilizarlo:
- Haga clic en el botón
Ejecutar o pulse F5 en el editor de VBA para ejecutar la macro. La macro procesará cada descripción de transacción en la columna B, la comparará con la lista de palabras clave y escribirá la categoría coincidente (o «Other» si no se encuentra ninguna coincidencia) en la columna F de la misma fila. - Puede ajustar el número de filas o rangos dentro de la macro para adaptarlos al diseño de sus datos; por ejemplo, modificando dónde comienzan las descripciones de las transacciones o la ubicación de la lista auxiliar. Asegúrese de que su lista de palabras clave no contenga celdas vacías y de que las categorías sean claras y únicas, lo que facilitará su referencia.
Ejecutar o pulse F5 en el editor de VBA para ejecutar la macro. La macro procesará cada descripción de transacción en la columna B, la comparará con la lista de palabras clave y escribirá la categoría coincidente (o «Other» si no se encuentra ninguna coincidencia) en la columna F de la misma fila.Análisis de ventajas e inconvenientes:La solución con VBA se adapta fácilmente a reglas más complejas, puede ejecutarse en cualquier momento tras actualizar su lista de palabras clave o categorías y elimina la necesidad de fórmulas matriciales. No obstante, las macros requieren habilitar operaciones automatizadas en Excel, lo que podría no ser adecuado para todos los usuarios ni entornos.
Consejo práctico: Guarde su macro de VBA en un archivo reutilizable y haga siempre una copia de seguridad de los datos antes de ejecutar macros, por si se produjeran sobrescrituras accidentales.
Sugerencias para la resolución de problemas:Si las categorías no se actualizan como se espera, compruebe que sus listas de palabras clave y categorías coincidan y que no haya caracteres ocultos en las celdas. VBA no distingue mayúsculas y minúsculas si utiliza «vbTextCompare», pero aún pueden producirse discrepancias debido al formato.
Otros métodos integrados en Excel: Utilice Power Query para configurar una categorización basada en reglas mediante lógica de columnas condicionales
Si prefiere un enfoque moderno de automatización sin código, Power Query le ofrece una forma sólida de categorizar sus transacciones bancarias. Este método es ideal cuando importa datos de transacciones desde archivos CSV o fuentes en línea, o cuando gestiona reglas de categorización dinámicas y en evolución, ya que centraliza la lógica y permite actualizaciones sencillas sin necesidad de fórmulas complejas ni scripts de VBA.
Pasos operativos:
- En primer lugar, asegúrese de que sus transacciones estén formateadas como una tabla o un rango de datos claro. Seleccione cualquier celda de su tabla y, a continuación, vaya a Datos > Desde tabla o rango para cargar los datos en Power Query.
- En el Editor de Power Query, haga clic en Agregar columna > Columna condicional para crear una nueva columna basada en reglas para las categorías.
- Defina sus reglas de coincidencia, por ejemplo:
- Si Descripcióncontiene «Mc Donalds», entonces devuelve «Takeout»
- Si Descripcióncontiene «Walmart», entonces devuelve «Family fee»
- De lo contrario, devuelve «Other»
- Haga clic en Aceptar para crear la columna condicional y, a continuación, seleccione Cerrar y cargar para devolver los resultados categorizados a Excel.
Análisis de ventajas e inconvenientes:Power Query ofrece una gran flexibilidad para ajustar reglas, se integra perfectamente con fuentes de datos externas y actualiza los resultados al instante en cuanto cambian los datos de transacciones o las reglas. Además, es mucho más eficiente que las fórmulas manuales cuando se trabaja con conjuntos de datos muy grandes. No obstante, requiere una configuración inicial y cierta familiaridad con su interfaz, lo que podría resultar novedoso para algunos usuarios.
Consejos prácticos:Ordene sus reglas por orden de prioridad (de arriba abajo) dentro del cuadro de diálogo Columna condicional; se aplicará la primera regla que coincida. Puede editar, añadir o eliminar reglas fácilmente en Power Query sin modificar las fórmulas de su hoja principal de Excel.
Recordatorios sobre errores: Si importa nuevas transacciones y el resultado no se actualiza como esperaba, haga clic en Actualizar en la pestaña Datos de Excel. Preste especial atención a la ortografía y a las coincidencias exactas de las palabras clave en Power Query, ya que las coincidencias parciales podrían impedir que se asigne correctamente la categoría.
Sugerencia para solucionar problemas:Si sus categorías no aparecen correctamente, revise las reglas de su columna condicional en busca de conflictos o casos omitidos. Es útil probar con algunas entradas de ejemplo antes de aplicar los cambios al conjunto completo de datos.
En resumen, ya sea que opte por fórmulas matriciales, la flexibilidad de una macro de VBA o la automatización simplificada de Power Query, Excel le ofrece diversas formas prácticas para categorizar sus transacciones bancarias de manera eficaz. Revise siempre la coherencia de sus reglas de categorización y de los resultados, especialmente cuando evolucionen los datos de transacciones o los criterios de clasificación.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos