Lento a Rápido 1

Esta es la primera publicación de la serie Lento a Rápido , donde mejoraremos gradualmente la tarea de comparar dos listas (comúnmente llamada conciliación). El ejemplo que usaré es una conciliación bancaria. Queremos comparar la lista de cheques en nuestro sistema de contabilidad con la lista de cheques de la descarga de nuestro banco.

La forma manual a menudo se realiza buscando el primer elemento en el registro de cheques. Una vez que tomamos nota mental del número de cheque, escaneamos visualmente la lista de bancos para ver si está allí. Si es así, a menudo lo notaremos de alguna manera, tal vez escribiendo un valor en una celda o usando formato de celda. Este sería el camino lento.

En esta serie, veremos formas cada vez más eficientes de realizar esta tarea. Exploraremos el formato condicional, las funciones de búsqueda y Power Query. En esta publicación, comenzamos con el formato condicional. Empecemos.

Objetivo

Antes de llegar demasiado lejos, echemos un vistazo a nuestra tarea. Las dos listas que usaré para la ilustración son un registro de cheques y una actividad bancaria. Dependiendo de en qué estés trabajando, puedes imaginar otros tipos de listas que deben compararse. Por ejemplo, la lista de artículos de su sistema de comercio electrónico y su sistema de inventario. O su lista de cuentas de su sistema de contabilidad y su sistema de presupuesto.

Para esta ilustración, la Lista 1 representa el registro de cheques (la lista de cheques emitidos):

Y aquí está la Lista 2, la actividad bancaria (una lista de cheques que han sido procesados ​​por el banco):

Ahora, nuestro objetivo es comparar estas dos listas. Específicamente, queremos saber qué números de cheque aparecen en ambas listas (qué cheques han sido compensados ​​por el banco) y qué cheques están en el registro de cheques pero no la actividad bancaria (los cheques pendientes).

Si hiciéramos esto manualmente, buscaríamos el primer número de cheque en el registro de cheques (1001) en la actividad bancaria. Si lo encontramos allí, podríamos escribir un valor en una celda adyacente o usar formato de celda. Una vez que hayamos terminado, podemos terminar con algo como esto:

En lugar de hacer esto manualmente, nos gustaría que Excel lo hiciera por nosotros. Entonces, nuestro viaje comienza con una característica llamada formato condicional.

Video

Detalle

El formato condicional es una característica de Excel que da formato a una celda automáticamente según su regla (o reglas) de formato.

Podemos usar el formato condicional para ayudar con la comparación de nuestras listas cuando las listas son relativamente pequeñas, en la misma hoja de trabajo, y solo necesitamos mirar una columna.

Nota: futuras publicaciones de la serie abordarán estas limitaciones.

Para comenzar simplemente seleccionamos las dos listas de números de cheque manteniendo presionada la tecla CTRL, así:

Una vez seleccionado, nos dirigimos a Inicio Formato condicional Resaltar reglas de celda Duplicar valores .

Aparece el cuadro de diálogo Valores duplicados, donde podemos elegir Duplicar o Único y el formato de celda relacionado. Seleccionemos Duplicado y Relleno amarillo, así:

Le damos a OK y bam… nuestro registro de cheques se ve así:

¡¡¡¡GUAU!!!! ¡¡Eso fue MUCHO más rápido que hacerlo manualmente!!

Otra cosa muy interesante es que podemos identificar fácilmente qué cheques no se han liquidado en el banco (que están en el registro de cheques pero no en la lista de actividades bancarias) seleccionando Único en el cuadro de diálogo en lugar de Duplicar, así:

Le damos a OK y bam… se identifican los cheques pendientes:

Nota: en realidad puedes aplicar varias reglas a las mismas celdas, por lo que, si estás intentando esto y todas las celdas están formateadas, primero querrás borrar la regla existente seleccionando Inicio Formato condicional Borrar reglas Borrar reglas de toda la hoja .

Ah, y otra cosa muy interesante es que podemos aplicar un filtro basado en el formato de la celda. Esto proporciona una manera rápida de ver solo los cheques pendientes. Para hacerlo, simplemente seleccione cualquier celda del rango y haga clic en Datos Filtrar . Verá los pequeños controles de filtro en la fila del encabezado. Simplemente haga clic en el menú desplegable de la columna Verificar y seleccione Filtrar Por color . Bam:

Si desea calcular el total de los cheques pendientes después de aplicar el filtro anterior, seleccione la celda debajo del último monto y presione Alt = . Esto insertará la función SUBTOTAL y mostrará el total de las filas visibles, así:

Este enfoque de formato condicional funciona bien cuando las listas están en la misma hoja, cuando son relativamente pequeñas y cuando solo nos importa una columna (la columna Número de verificación). De lo contrario, necesitamos algo más poderoso. Y lo cubriremos en la próxima publicación

Conclusión

Esta es la primera de tres formas de ayudar a automatizar las conciliaciones. En la segunda publicación, veremos cómo las funciones de búsqueda brindan capacidades adicionales.

Archivo de muestra

Formato-condicional.xlsxDescargar