Operaciones con rangos que contienen errores

miércoles, abril 09, 2008

Si una fórmula en nuestra hoja de Excel se refiere a un rango que contiene errores, como #N/A, el resultado será también error.
Si el rango contiene sólo algunas celdas, una solución es, sencillamente, borrar el contenido de las celdas con error.
Pero hay situaciones en las cuales no queremos borrar el contenido de las celdas ya sea porque se trata de una gran cantidad de datos o por cualquier otro motivo.
En esos casos necesitamos crear fórmulas que ignoren las celdas con un valor de error en el rango.
En esta nota veremos como sumar, contar e imitar la función SUMAPRODUCTO en estas situaciones.

Empezamos por considerar un rango en el cual hay vatios tipos de errores (de hecho, todos los errores que se pueden dar en Excel)




Si usamos la fórmula =SUMA(A2:A11), el resultado será #¡VALOR!.

Para superar este inconveniente usamos esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11),0,A2:A11))}

Como siempre recordamos que las fórmulas matriciales son introducidas en las celdas pulsando simultáneamente Ctrl+Mayúsculas+Enter.

Esta fórmula crea un vector donde si un valor del rango da resultado error al ser evaluado por la función ESERROR, este valor es convertido a 0. En caso contrario se mantiene el valor original. La función SUMA realiza la operación con los valores del vector creado por la función SI.

Si queremos contar cuantos valores que no son error hay en el rango usamos esta fórmula matricial

={SUMA((NO(ESERROR(A2:A11)))*1)}

Esta misma fórmula puede escribirse de esta forma más "académica"

={SUMA(--NO(ESERROR(A2:A11)))}

También podemos usar funciones base de datos para resolver este problema.
Si el tipo de error es el mismo en todas las celdas erróneas del rango, por ejemplo #N/A , como en esta situación



definimos el área de criterios en el rango C1:C2 como mostramos en la imagen arriba y creamos la fórmula

=BDSUMA(A1:A11,A1,C1:C2)

Para contar cuantos valores no erróneos hay en la lista usamos esta otra fórmula

=BDCONTAR(A1:A11,A1,C1:C2)

Si tenemos distintos tipos de valores en la lista, en lugar de crear varios criterios, podemos usar un único criterio, como se muestra aquí



Hay que prestar atención que el área de criterios sigue siendo el rango C1:C2, pero hemos dejado la celda C1 vacía y en la celda C2 tenemos una fórmula y no un valor. Las fórmulas para sumar y contar son idénticas a las del ejemplo anterior.



Finalmente tenemos que enfrentarnos con el caso que el rango con errores aparezca en una función SUMAPRODUCTO. En este caso tenemos que desistir de usar esta función e imitarla con una fórmula matricial.
Si queremos obtener la suma de los productos de los miembros de las listas 1 y 2 como en esta imagen



tendremos que usar esta fórmula matricial

={SUMA(SI(ESERROR(A2:A11*B2:B11),0,(A2:A11*B2:B11)))}

Si tenemos más de dos vectores, es conveniente usar nombres para señalar los rangos. Por ejemplo, en este caso creamos tres nombres Lista1, Lista2 y Lista3



El cuaderno con los ejemplos se puede descargar aquí



Technorati Tags:

2 comments:

Anónimo,  14 abril, 2008 22:42  

Hola

Muchas gracias por toda la ayuda que nos brindas. No te imaginas lo util que es tu pagina, ademas de ser genial. Tu pagina se ha convertido en mi Biblia y tu en mi Guru!!!

Tengo una consulta para ti, tengo una base de datos en excel, y necestito buscar por palabras. Es decir si tengo:
- Cajas de 20x10
- Cajas de 10x10
- Bolsas azules
- Sellos verdes
- Sellos negros

y en una celda determinada ingreso "Caja" y me entrege todos los criterios que coincidan con esto.

Agradeceria de sobremanera tu ayuda, y muchas gracias por tu blog.

Fernanda.

Jorge L. Dunkelman 15 abril, 2008 18:08  

Hola Fernanda

tendrías que ser un poco más explicíta. Si quieres ver las celdas donde aparece "Caja" te bastaría con usar Ctrl+B (búsqueda) y apretar el botón "buscar todo".
Si no es ésto, no entiendo que quieres decir con "me entrege todos los criterios que coincidan con esto".

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP