Operaciones con rangos que contienen errores

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: MS Excel

Convertir datos de matriz a columna o fila en Excel

Hace unos días atrás me dejaron en un comentario esta consulta:

COMO PUEDO PASAR LOS DATOS DE UNA MATRIZ A SOLO UNA COLUMNA??

En esta nota veremos como hacerlo, no sólo cómo poner todos los datos de una matriz en una sola columna, sino también en una sola fila.

Actualización: mucho más fácil y eficiente hacerlo con Power Query; ver este post

Empecemos por plantearnos una matriz, que por comodidad, será de tres fila por tres columnas



Nuestro objetivo es crear una columna que contenga todos los elementos de la matriz



Mi primer paso fue decidir si hacerlo con fórmulas o con macros. Decidí hacerlo con fórmulas.

El archivo con las fórmulas puede descargarse aquí.

De todas las funciones la que me pareció más apropiada es DESREF. Como ya hemos explicado en el pasado, esta función tiene cinco argumentos




En este caso, nuestro "ancla" será toda la matriz, el rango A1:C3. Este rango lo definimos en un nombre, "matriz", para mayor comodidad.

Para obtener el primer elemento de la matriz usamos la fórmula

=DESREF(matriz;0;0;1;1)



Para poner el segundo elemento de la matriz en la columna usamos la fórmula

=DESREF(matriz;1;0;1;1)



A esta altura de los acontecimientos está claro que nuestra fórmula tiene que cambiar en forma dinámica el argumento de la fila y de la columna. Es decir, una fórmula que a medida que la copiamos a lo largo de una columna, vaya generando en forma dinámica los elementos de la matriz.

Para resolver el número de fila usaremos esta fórmula

=RESIDUO(FILA()-FILA($E$2);FILAS(matriz))

Esta fórmula produce estos resultados



Usamos $E$2 como argumento ya que en esa celda empezará nuestra columna.

Ahora tenemos que lograr que el argumento de la columna cambie de 1 a 3, en nuestro caso, cada tres filas. Para esto usaremos la fórmula

=TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz))

Los resultados de esta fórmula son



A los argumentos de alto y ancho de DESREF les damos un valor de 1. Finalmente, nuestra fórmula será

=DESREF(matriz;RESIDUO(FILA()-FILA($E$2);FILAS(matriz));TRUNCAR((FILA()-FILA($E$2))/FILAS(matriz));1;1)

En nuestro ejemplo empezamos la columna en la celda E2 y obtenemos este resultado



Esta fórmula copia los elementos de la matriz primero por columna y luego por fila. Si queremos copias los elementos primero por fila y luego por columna, usamos la fórmula anterior modificada de la siguiente manera

=DESREF(matriz;TRUNCAR((FILA()-FILA($E$2))/COLUMNAS(matriz));RESIDUO(FILA()-FILA($E$2);COLUMNAS(matriz));1;1)

Si queremos copiar los elementos de la matriz a lo largo de una fila tendremos que usar esta fórmula

=DESREF(matriz;TRUNCAR((COLUMNA()-COLUMNA($B$13))/COLUMNAS(matriz));RESIDUO((COLUMNA()-COLUMNA($B$13));COLUMNAS(matriz));1;1)

para copiar por columna y luego por fila, o ésta para copiar primero por fila y luego por columna

=DESREF(matriz;RESIDUO((COLUMNA()-COLUMNA($B$14));FILAS(matriz));TRUNCAR((COLUMNA()-COLUMNA($B$14))/(FILAS(matriz)));1;1)




Esta nota está basada en las fórmulas desarrolladas por Chip Pearson.

Technorati Tags: MS Excel

Calcular Porcentaje de Subtotales en tablas dinámicas de Excel

Un lector me pregunta como podemos calcular porcentajes de subtotales en una tabla dinámica de Excel.
Excel permite mostrar porcentajes del total de una tabla con facilidad, pero no tiene ningún método incorporado para calcular los porcentajes de subtotales. Veamos a qué me refiero.

Supongamos esta lista de datos



Con facilidad podemos crear una tabla dinámica que totalice las ventas por región y agente



Luego podemos usar el menú de opciones de campo para mostrar los resultados como porcentaje del total



Todo esto con algunos clics del Mouse!

Pero qué pasa si queremos calcular el porcentaje de cada agente sobre el total de cada región? Para hacer esto tendremos que dar un rodeo.

Empezamos por crear un campo auxiliar en nuestra lista de datos, que llamamos "% de Región"



Los valores de este campo los calculamos con la fórmula:

=C2/SUMAR.SI($B$2:$B$13,B2,$C$2:$C$13)

que calcula el porcentaje de cada valor sobre el total de los valores de la región. Este nuevo campo lo arrastramos al área de datos



Para evitar que Excel totalice los subtotales y muestre un total del 400%, cancelamos la opción Total de Columnas en el menú de opciones de tabla.

Technorati Tags: MS Excel