Manejo de errores en cálculo Excel – Suma de rangos con celdas error

Hasta el momento hemos visto como utiliza las funciones de error de Excel ya sea para aplicar una regla en caso de presentarse un error en los cálculos que desarrollemos o ya sea aplicando un formato condicional sobre las celdas para que en caso de presentar un error en sus cálculos, dicho error no sea visible. Sin embargo, podemos tener algunos inconvenientes al momento de realizar otras operaciones que involucren las celdas con errores, por ejemplo al sumar un rango de celdas donde una de ellas tiene un error, veamos un ejemplo:

En la siguiente tabla, tenemos el listado de vendedores, con sus respetivas ventas para los años 2012 y 2013, la Meta de ventas 2013 que tiene cada vendedor y los respectivos cumplimientos de ventas y de crecimientos:

Al final en la fila 8 contamos con los totales para cada columna. Las celdas G4 y B7 tienen un formato condicional en el cual, si el valor de la celda es cualquiera de los tipos de error de Excel, el color de la fuente debe ser blanco, de esta manera no es visible el error. Las celdas E7 (Meta Ventas 2013) y F7 (Cumplimiento Ventas), tienen el error #N/A lo cual afecta la suma total de la fila 8 para cada columna:

Las celdas G4 y B7 tienen un formato condicional en el cual, si el valor de la celda es cualquiera de los tipos de error de Excel, el color de la fuente debe ser blanco, de esta manera no es visible el error. Las celdas E7 (Meta Ventas 2013) y F7 (Cumplimiento Ventas), tienen el error #N/A lo cual afecta la suma total de la fila 8 para cada columna. Podemos aplicar el mismo formato condicional a las celdas E7 y F7 para que no sea visible el error pero si aplicamos el mismo formato a la fila 8 en las columnas respectivas, no sería apropiado ya que aún con los errores de las celdas E7 y F7, es necesario calcular el total de las demás filas que si tienen valores El inconveniente para el total de la celda E8 donde se utiliza la función SUMA, es que el rango de dicha operación (E3:E7) tienen una celda con un error, en este caso la celda E7; no podemos simplemente modificar el rango en la función SUMA para que no tenga en cuenta la celda E7 ya que en caso de no presentarse el  error luego de una actualización de datos, el total no tendría en cuenta el valor de la celda E7 y  por tanto el total de la tabla no sería correcto. Veamos el caso:

Si ajustamos el rango de la función SUMA para que el TOTAL de la columna E no tenga en cuenta la celda E7 que tienen el error el resultado nos da efectivamente el total; esto es válido siempre y cuando el valor de la celda E7 siempre sea un error, pero en caso contrario, tendríamos que estar siempre atentos a ajustar la formula SUMA del total para que cuando el valor de la celda E7 no sea un error, debemos incluir esa celda en la SUMA.

Observemos que si actualizamos la celda E7 con el valor 400, el total no cambia ya que el rango de la función SUMA no tiene en cuenta dicha celda por lo que tenemos que ajustar nuevamente el rango para que se incluya la celda E7:

Como vemos esto no es muy práctico, lo ideal es que independiente de si el valor de una celda sea un error, podamos realizar la suma sin tener que hacer modificaciones en el rango.

Para solucionar esta situación, contamos con la función AGREGAR, la cual realiza la operación indicada en uno de sus parámetros pero no toma en cuenta las celdas con error del rango indicado:

Esta función tiene dos estilos de uso, como una función referencial, es decir como la mayoría de las funciones de Excel, y como una función de estilo matricial que es usada para casos especiales donde el estilo referencial no es suficiente. Para los casos como el que planteamos en este capítulo así como para la mayoría de escenarios que se les puesta presentar, es suficiente utilizar el estilo referencial. Tiene 3 parámetros:

  1. núm_funcion: Es un valor entre 1 y 19 que representa las operaciones básicas que se pueden aplicar a un rango de celdas. Veamos las opciones que nos presenta este parámetro:

    Para nuestro ejemplo indicaríamos el valor 9 que corresponde a la función SUMA.

  2. opciones: es un valor entero entre 1 y 7 que indica que tipo de omisiones vamos a tener en cuenta para la operación indicada en el parámetro anterior ya sea omitir Filas ocultas, Errores, Subtotales anidados, entre otros. Veamos el listado completo de estas opciones:<

    Para nuestro ejercicio podemos indicar el valor 6, omitir valores de error/li>

  3. ref: En este parámetro debemos indicar el rango de celdas donde vamos a aplicar la operación que elegimos en el parámetro 1. En nuestro caso la matriz para realizar la suma de las metas de ventas de todos los vendedores es E3:E7.

Bien, veamos ahora como queda esta función AGREGAR sobre la celda E8 donde se totalizan las metas 2013 de todos los vendedores:

Observemos que ahora, el resultado no da un error a pesar de tener en el rango de suma una celda con error. De esta manera podemos estar tranquilos de no obtener errores en nuestras operaciones.

Descargar archivo de trabajo.

MÁS SOBRE

Gerencie.com en su correo.

Suscríbase y nosotros colocaremos en su bandeja de entrada la mejor información que generamos diariamente.

Siéntase libre de opinar

En Gerencie.com está permitido opinar, criticar, discutir, controvertir, disentir, etc. Lo que no está permitido es insultar o escribir palabras ofensivas o soeces. Si lo hace, su comentario será rechazado por el sistema o será eliminado por el administrador. Por último, trate de no escribir en mayúscula sostenida, resulta muy difícil leerle.