Descargar el fichero: suma_con_errores.xlsx
Es frecuente que tengamos que sumar unos datos entre los que hay una celda que tiene un error, o un #N/A (no aplicable). Al efectuar la suma de todos los datos el error se arrastra a la suma, obteniendo un valor también con error. Vamos a ver un método que evita que esos errores se transmitan a la suma, o al promedio, o a la función que estemos manejando.
Disponemos de unos datos sobre los habitantes de una serie de ciudades en el año 2010. Esta tabla se nombra con el nombre de rango 'datos'.
Deseamos crear una tabla donde indicando el nombre de la ciudad a su derecha se tome el número de habitantes de la tabla 'datos'. Si todas las ciudades que deseamos totalizar se encuentran en la tabla 'datos' no tendremos problema en obtener la población con la función BUSCARV y luego poder sumar los habitantes.
La función empleada en la celda C5 es:
=BUSCARV(B5;datos;2;0)
Si ahora hacemos otra tabla similar pero donde añadimos Sevilla que es una ciudad que no existe en la tabla 'datos' el resultado será #N/A que significa "No Aplicable", e indica que Sevilla no se encuentra en la tabla 'datos'. Observar que en la función BUSCARV se pidió búsqueda exacta.
Al no existir Sevilla y obtener #N/A el error se transmite al Total y él también da #N/A por lo que no hemos podido sumar los habitantes de las tres ciudades de las que si disponemos de datos.
Para resolver este problema acudimos a la creación de una fórmula matricial. En la celda C23 calculamos el Total con la función SUMA:
=SUMA(SI(ESERROR(valores);"";valores))
En la celda C24 calculamos la media con la función PROMEDIO:
=PROMEDIO(SI(ESERROR(valores);"";valores))
Ambas expresiones son fórmulas matriciales. Observar que van entre llaves {}. Para validarlas no se ha de pulsar ENTER. Las fórmulas matriciales se validan pulsando simultáneamente estas tres teclas:
CONTROL + MAYÚSCULAS + ENTER
Práctica
En la Hoja2 dispone de un ejercicio propuesto.
Una empresa comercializa 9 tipos de productos prefabricados.
No se dispone del precio unitario del Prefabricado 5.
Deseamos calcular las unidades vendidas en total, salvo del prefabricado 5 ya que de éste no disponemos de datos para calcularlo adecuadamente.
También necesitamos determinar las unidades medias vendidas sin considerar las del Prefabricado 5
La pisada de Suma con error muy muy buena! Gracias por la ayuda.
ResponderEliminarMarcelo (desde Montevideo Uruguay)
Muy bueno verlo de forma matricial, me evitó hacerme una tabla auxiliar.
ResponderEliminarMuchas gracias por la ayuda!
Vaya mierda lo del SUMA(SI(ESERROR(A6:A12);"";(A6:A:12)). Excel se pasa por el forro de los cojones tu condicional, y con razón, porque si por ejemplo la celda A9 es un #N/A le estás diciendo que si HAY un PUTO ERROR que te de "", y si no, que te de la suma desde A6:A12, pero al haberlo, la primera condición del silogismo es verdadera. Es decir, que si hay UN SOLO PUTO ERROR, q te de 0, y NO ME VA A SUMAR de A6:A12. Y lógicamente, aunque te dejara, al intentar sumar desde A6 hasta A12, volvería a encontrar el puto #N/A, y no podría sumártelo, porque el factor que excluye el error no es el SI, es el ESERROR, y ya lo has cerrado previamente. A ver cómo sales de ésta Adolfo
ResponderEliminarDemasiado trabajo..... para esto está la función AGREGAR
ResponderEliminarHola Javier.
EliminarGracias por tu valiosa aportación. La función AGREGAR ha comenzado a estar disponible desde la versión Excel 2010, en Excel 2007 y anteriores no existía.
Lo que hacíamos antes para sumar con errores era usar la expresión matricial que se explica en este post del blog. Desde que se tiene la función AGREGAR ya no es necesario usar la compleja fórmula matricial explicada, aunque siempre es un reto intelectual conocer este tipo de funciones.
Lo dicho, que se agradece tu comentario como actualización para resolver este tipo de casos de suma, promedios, y otras fórmulas que incluyen errores en el rango.
Un saludo.
LA FUNCION AGREGAR NO ME SIRVE PARA PROMEDIAR UNA SUMA DETERMINADA DE VALORES
ResponderEliminar