Funciones que dan #VALOR en referencias a cuadernos cerrados

Cada tanto recibo consultas en relación a fórmulas que dan el resultado #VALOR cuando contienen referencias a cuadernos cerrados.
Lo que intriga a estos usuarios de Excel es que este comportamiento no se da con todas las funciones. Ciertas funciones, como BUSCARV funcionan correctamente también si el cuaderno remoto está cerrado; otras, como CONTAR.SI dan #VALOR si el cuaderno remoto está cerrado, error que se corrige al abrir el cuaderno.

Veamos este ejemplo: tenemos dos cuadernos con las ventas anuales de dos sucursales resumidas por mes, Sucursal 1.xlsx y Sucursal 2.xlsx. En un tercer cuaderno queremos crear un reporte que muestre el total de ventas de cada sucursal  y el número de meses en que las ventas fueron superiores a los 200,000

Como puede apreciarse, la fórmula en la celda C3 contiene una referencia explícita al cuaderno de donde extraemos los datos

=CONTAR.SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13,">200000")

Al cerrar los cuadernos Sucursal 1.xlsx y Sucursal 2.xlsx la referencia incluye la dirección completa del archivo remoto.

Ahora cerramos el cuaderno con el reporte. Al volver a abrirlo veremos:

Como vemos, la función SUMA sigue mostrando los resultados, pero la función CONTAR.SI no puede resolver la referencia y da el resultado de error #¡VALOR!

Hay varios remedios para este problema. En el caso específico de la función CONTAR.SI podemos usar en su lugar una combinación de SUMAR y SI de esta manera:

=SUMA(SI('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000,1,0))

Esta fórmula es matricial y debe ser introducida en la celda apretando simultáneamente Ctrl-Mayúsculas-Enter

También podemos usar la función SUMAPRODUCTO, que no debe ser introducida matricialmente, de esta manera

=SUMAPRODUCTO(--('[Sucursal 1.xlsx]Hoja1'!$B$2:$B$13>200000))

Nótese el doble símbolo "--"inmediatamente después del primer paréntesis. Su función es forzar a los valores VERDADERO y FALSO creados por la función SUMAPRODUCTO a tomar el valor 1 y 0 respectivamente.

Otras funciones que dan #¡VALOR! cuando se refieren a cuadernos cerrados son SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, CONTAR.BLANCO, PROMEDIO.SI, INDIRECTO (tema que trate en esta nota del año 2006), DESREF y varias de las funciones base de datos como BDCONTARA y BDPROMEDIO).

La función INDICE con áreas - segundo episodio

Unos días después de haber publicado el post sobre la función INDICE con areas, entra en mi oficina el mencionado compañero de trabajo:

Compañero - Hola Jorge. Leí el post. Muy bueno...

JLD - Hola. Muy bueno...pero?

Compañero - Mirá, no si será abusar pero quisiera que en el gráfico la columna del mes analizado aparezca con otro color y también sería bueno si hubiera una línea horizontal que muestre el promedio.

JLD - ¡Ah! Vos querés ésto:

Compañero - ¡Si! ¿Cómo se hace?

JLD - Sentate que te explico

El gráfico de la nota anterior contenía una única serie; éste contiene tres:

1. la serie de las ventas sin el mes elegido (las columnas que aparecen en celeste)
2. la serie que contiene el mes elegido (aparece en  verde)
3. la serie que representa el promedio.

Como mostramos en la nota anterior, la serie de las ventas la generamos dinámicamente con un nombre definido. Ahora tenemos que crear columnas auxiliares para definir las tres series requeridas. Esto lo haccemos agregando columnas a las izquierda de la tabla de datos de manera que luego podamos ocultarlas.
Estas son las columnas:

La celda B5 contiene la fórmula =COINCIDIR(F19,E4:E15,0). El resultado, el número de orden  del mes elegido, lo usamos como parámetro en las fórmulas de las columnas A y B.

El rango A4:A15 contiene la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)<>$B$2,INDICE(F4:H4,,$F$18),ND())

El objetivo de esta fórmula es dar las ventas del mes de la tabla de ventas a condición que no sea el mes elegido (mayo, 5, en nuestro ejemplo). Em caso de tratarse del mes elegido el resultado es #N/A, lo que logramos con la función ND().

En modo similar ponemos en el rango B4:B15 la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)=$B$2,INDICE(F4:H4,,$F$18),ND())

Esta complementa la anterior: si no es el mes elegido el resultado es #N/A.

Los valores #N/A son ignorados en los gráficos de Excel (no aparecen).

El rango C4:C15 contiene sencillamente una referencia a la celda F22 donde calculamos el promedio.

La celda B17 contiene la fórmula matricial (se ingresa apretando Ctrl-Mayúsculas-Enter):

=MAX(SI(ESERROR(A4:A15),"",A4:A15))

El objetivo de esta fórmula es garantizar que ambos ejes Y en  el gráfico (el principal y el secundario) tengan la misma escala (el mismo valor máximo) para evitar distorsiones en la presentación de los valores. La técnica está explicada en esta nota.

Una vez que hemos definido las series creamos el gráfico asegurándonos de usar el eje Y principal para las ventas y el eje Y secundario para el mes elegido. y para el valor máximo.

Las series de ventas son gráficos de columnas; el promedio es un gráfico de línea y el valor máximo, para volverlo invisible, es un grafico de línea sin marcadores y sin línea

El último detalle a tener en cuenta es, por defecto, Excel no muestra los datos de series que se encuentras en filas o columnas ocultas. Para volverlos visibles tenemos que assgurarnos de cambiar la definición en "Seleccionar Datos-Celdas Ocultas y Vacías"

El archivo con el ejemplo se puede descargar aquí.

La función INDICE con áreas

Transcripción más o menos real de un diálogo con un compañero de trabajo:

Compañero: Hace unos días atrás leí tu post sobre la función INDICE. Desde entonces la uso todo el tiempo; no me había dado cuenta de las posibilidades que tiene.

JLD: me alegro!

Compañero: pero, ¿qué es esa segunda sintaxis con las áreas, para qué sirve?

JLD: ah!, ¿te referís a esto?:

Aquí va la explicación.

Supongamos este cuadro de ventas por mes y sucursal

Habiendo definido nombres que se refieren a los rangos de ventas de las sucursales (Sucursal_1 se refiere al rango C3:C14, por ejemplo), podemos obtener las ventas de mes de abril de las sucursal 1 con esta fórmula

=INDICE(Sucursal_1,COINCIDIR("abril",B3:B14,0))

Si queremos obtener las ventas de otra sucursal tendríamos que cambiar el rango de referencia en la fórmula.

Si usamos la segunda sintaxis de la función INDICE, podemos crear una fórmula que se refiera a las distintas sucursales dinámicamente. Consideremos este ejemplo

La fórmulas en la celda C20 (Ventas del mes) es la siguiente:

=INDICE((Sucursal_1,Sucursal_2,Sucursal_3),COINCIDIR(C18,$B$3:$B$14,0),,C17)

donde (Sucursal_1,Sucursal_2,Sucursal_3) es la referencia a las distintas áreas, en nuestro caso el rango de las ventas de cada sucursal;

COINCIDIR(C18,$B$3:$B$14,0) calcula el número de fila en el rango correspondiente al mes buscado

y C17 se refiere a la celda que contiene el número de área de donde queremos extraer el dato..

Las áreas no deben tener necesariamente el mismo tamaño o ser adyacentes.

La celda C21 contiene la fórmula

=PROMEDIO(INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17))

La particularidad aquí es que INDICE usa sólo los argumentos "ref" y "num_area". Como hemos explicado, ésto hace que INDICE considere todo el rango de la referencia; así, al combinarla con la función PROMEDIO obtenemos el promedio de las ventas de la sucursal. De esta manera podemos comparar las ventas del mes con el promedio.

Como puede apreciarse, esta técnica es muy útil cuando creamos dashboards y reportes dinámicos.

Como bono adicional, podemos usar INDICE con áreas para crear gráficos dinámicos. Siguiendo con nuestro ejemplo, podemos crear un nombre definido "grfVentas" que se refiera a la fórmula

INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17)

Después de crear un gráfico con una de las sucursales, reemplazamos la referencia en "Modificar serie-valores  de la serie"

por el nombre definido "grfVentas"

A partir de este momento, cada vez que cambiemos la referencia a la sucursal en la celda C17, el gráfico mostrará los datos de la sucursal elegida



El cuaderno con el ejemplo puede descargarse aquí.