Por David Ringstrom, CPA
A los contadores se les suele asignar la tarea mundana de identificar las variaciones que superan las tolerancias específicas. En lugar de intentar ver las columnas de números, se puede utilizar la función de formato condicional de Excel para hacer que dichas variaciones salten en la pantalla. Demostraré cómo puedes usar el formato condicional de Excel para identificar variaciones que son tanto de más de 2.000 dólares como de más del 10% del presupuesto.
El formato condicional es una característica poderosa, pero tiene algunos matices que pueden frustrar a los usuarios no iniciados. Dependiendo de las condiciones que intente aplicar, a menudo es mejor aplicar el formato a una sola celda y luego utilizar la función Pegar formato especial de Excel para transferir el formato al resto de las celdas relacionadas. El Formato condicional basado en fórmulas, que demostraré en un momento, a menudo no devuelve los resultados deseados cuando intentas aplicarlo a varias filas o columnas a la vez.
La figura 1 muestra una comparación representativa entre el presupuesto y el real. Nuestro objetivo es aplicar el resaltado amarillo a cualquier variación de +/- 2.000 dólares y +/- 10%. Específicamente, una fila sólo debe ser resaltada cuando se cumplen ambas pruebas. Primero describiré los pasos a seguir para su uso en el Excel 2007 y posteriores, y luego ofreceré un breve resumen en el Excel 2003. Para implementar tal regla, lleve a cabo estos pasos:
Figura 1: Usaremos el Formato Condicional para identificar las variaciones que son tanto de +/- 2.000 dólares como de +/- 10%.
1. Excel 2007 y posteriores: Haz clic en la celda D2, elige Formato condicional en la pestaña Inicio y, a continuación, haz clic en Nueva regla, como se muestra en la Figura 1.
Excel 2003 y anteriores: Haz clic en la celda D2, elige Formato y luego Formato condicional.
2. Excel 2007 y posteriores: Elija Usar una fórmula para determinar qué celdas formatear, como se muestra en la Figura 2.
Excel 2003 y anteriores: Cambiar el valor de la celda es a valor de la fórmula es.
Figura 2: El formato condicional ofrece una variedad de opciones, incluyendo la elaboración de sus propias fórmulas.
3. Todas las versiones de Excel: Introduce esta fórmula:
=AND(ABS($D2)>2000,ABS($E2)>0.1)
4. Todas las versiones de Excel: Haz clic en el botón Formato, elige la pestaña Rellenar, haz clic en el color deseado, como el amarillo que se muestra en la Figura 3, y luego haz clic dos veces en OK.
Figura 3: El color es uno de los muchos tipos de formato que se pueden aplicar mediante el formato condicional.
5. Todas las versiones de Excel: Selecciona la celda D2, haz clic en el Pintor de Formatos como se muestra en la Figura 4, y aplica el formato a las celdas D2 a la E10. El Pintor de formato aparece en la barra de herramientas Estándar de Excel 2003.
Figura 4: Utiliza el icono del Pintor de Formatos para transferir el formato de la celda D2 a las celdas D2 a la E10.
6. Todas las versiones de Excel: Vuelva a aplicar los formatos de números de porcentaje en las celdas E2 a E10, como se muestra en la Figura 5. Excel no ofrece una forma de transferir el formato condicional por sí mismo sin sobrescribir los formatos de números existentes. Sin embargo, es mucho más fácil acertar con el formato condicional aplicándolo primero a una sola celda, y luego volver a aplicar cualquier formato de números según sea necesario.
Figura 5: Es posible que tenga que restaurar algunos formatos de números después de transferir el formato condicional de una celda a muchas.
Si utiliza Excel 2007 o posterior, ahora puede hacer clic con el botón derecho del ratón en una de las celdas amarillas, elegir Filtro y, a continuación, Filtrar por el color de la celda seleccionada, como se muestra en la Figura 6. También puedes ordenar por color en Excel 2007 y posteriores. La Figura 7 muestra la lista filtrada. Para quitar el filtro, haz clic en el icono Filtro de la ficha Datos del menú de Excel conocido como Cinta.
Figura 6: Puedes filtrar (así como ordenar) en base al color en Excel 2007 y posteriores.
Figura 7: Se identifican y aíslan las variaciones.
En cuanto a la fórmula que usé:
- La función ABS devuelve el valor absoluto de un número – en otras palabras ($17,396) se convierte en $17,396.
- La función AND le permite probar hasta 255 condiciones a la vez. En este caso, estoy probando si el valor absoluto de la varianza en la columna D es mayor de 2.000 dólares, y el valor absoluto del porcentaje de varianza en la columna E es mayor del 10%. Si ambas pruebas son verdaderas, entonces se aplicará el formato condicional.
- Los signos de dólar antes de las letras de la columna son críticos cuando se aplica el formato condicional de esta manera, ya que en efecto estamos copiando y pegando el formato a una segunda columna. Si se omiten los signos de dólar antes de las referencias de columna, Excel ajustará las referencias de columna, lo que significa que su Formato Condicional no devolverá el resultado deseado.
Lea más artículos de David Ringstrom.
Sobre el autor:
David H. Ringstrom, Contador Público Certificado, dirige Accounting Advisors, Inc. una empresa consultora de programas informáticos y bases de datos con sede en Atlanta que ofrece servicios de capacitación y consultoría en todo el país. Contacte con David en [correo electrónico protegido] o sígale en Twitter . David habla en conferencias sobre Microsoft Excel, y presenta webcasts para varios proveedores de CPE, incluyendo el socio de AccountingWEB CPE Link .