Promedio ponderado en tablas con filas ocultas

Ya hemos tocado el tema de cómo calcular el promedio ponderado con Excel, si bien en la prehistoria de este blog, allá por el año 2006.

A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.

Por ejemplo, en este caso

El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula

=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)

Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas

Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).

Veamos este ejemplo

El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas

Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.

Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna  auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)

Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO

=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)

Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF

=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)

La expresión

SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))

da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado

Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.

JLD Excel – balance del sexto año y un gráfico interesante

Este blog cumple su sexto año de existencia. A lo largo de los años el número de lectores ha ido creciendo lo cual me llena de satisfacción. 2012 ha sido el año record en visitas y páginas vistas, más de 1,5 millón y más de 2.3 millones respectivamente.

Buscando como representar las estadísticas encontré en el excelente blog de Chandoo una nota sobre sobre la representación gráfica del precipicio fiscal de los Estados Unidos. Basándome en esa nota y en una posterior sobre un tema similar, he desarrollado este gráfico que muestra dinámicamente el número de páginas vistas en JLD Excel en los años 2007-2012

Este tipo de gráfico, permite una rápida comparación entre los años y ver cómo se ha ido desarrollando la popularidad del blog. La barra de desplazamiento permite señalar cada mes en particular, mostrando al mismo tiempo el número de páginas vistas.

Para crear este gráfico empecé por descargar la información sobre el número de visitantes y páginas vistas en el blog que llevo en el sitio StatCounter

Como puede apreciarse se tratan de datos diarios. Para poder crear nuestro gráfico tenemos que transformar estos datos a datos mensuales y anuales. Como ya habrán intuido la forma más eficiente de hacerlo es con tablas dinámicas

Ahora copiamos todo el contenido de la hoja y usamos Pegar Especial-Valores, para cancelar la tabla dinámica dejando sólo los valores.

El próximo paso es separar con una fila en blanco los años (para crear la separación en el gráfico) y agregar una serie de datos adicional en la columna D con esta fórmula

=SI(B2=$G$1,C2,NOD())

La celda $G$1 contiene el nombre del mes vinculado al valor de la barra de desplazamiento e identifica el mes a señalar (en la imagen el mes elegido es diciembre, por eso la celda D13 exhibe el valor de ese mes y el resto aparece con el valor #N#A; este valor de error no es representado en los gráficos).

Creamos un gráfico de área donde los valores del eje X (categorías) están en las columnas Ay B; los valores del área de cada año en la columna C y los valores del mes elegido en la barra de desplazamiento en la columna D.

Los valores de la serie de la columna D están representados con un gráfico de líneas.

La línea vertical que une el punto al eje de la X es una barra de error definida de esta manera

La barra de desplazamiento está ligada a la a la celda F2 en la hoja “motor”. Esta hoja contiene todos los datos del gráfico y todos los cálculos necesarios

Para calcular los acumulados en forma dinámica, tal como aparecen en el rango G5:G10 de la hoja “motor”, usamos esta fórmula

=SUMA(DESREF(INDICE($C$2:$C$78,COINCIDIR(F5,$A$2:$A$78,0)),,0,$F$2))

Finalmente creamos un cuadro de texto para cada año en el gráfico y lo ligamos a la celda con el acumulado

Este tipo de gráfico puede aplicarse a un sinnúmero de situaciones (ventas, eficiencia de servicios, etc.).

El archivo puede descargarse aquí.

Mis mejores de deseos de salud, paz y prosperidad en este nuevo año para todos mis lectores.

Gráficos animados con Excel

En este blog ya hemos mostrado cómo crear gráficos interactivos (pueden ver las notas apretando el enlace Gráficos en la nube de etiquetas). Entendemos por gráficos interactivos aquellos donde el usuario puede cambiar el aspecto, las series y/o puntos de las series sin necesidad de acceder a la base de datos que alimentan el gráfico.

Podemos dar otro paso adelante y crear un gráfico animado, como éste



En este gráfico mostramos los cambios en la relación entre el real brasileño (BRL) y el dólar estadounidense (USD).

No me entusiasman particularmente los gráficos animados, pero en casos como éste, nos ayudan a ver o descubrir tendencias.

En esta nota vamos a mostrar cómo construirlo (el modelo se puede descargar aquí).

EL primer paso, por supuesto, es obtener los datos. En este ejemplo he obtenido los datos del sitio OANDA (datos históricos)

El segundo paso es construir el gráfico que en esta etapa será estático.

El próximo paso es agregar una barra de desplazamiento que nos permita ir cambiando los datos de la serie en el gráfico. En este caso usamos la barra de desplazamiento (scrollbar) de la colección de comandos ActiveX

En el cuadro de propiedades del control, definimos la celda B9 de la hoja “dinamico” como la celda ligada

A la celda B9 le hemos asignado el nombre “chrtCounter”.

En la celda B6 de la hoja “dinamico” ponemos el número de puntos que queremos que aparezcan en el gráfico. En nuestro caso hemos definido 30

A la celda le hemos asignado el nombre “chrtStep”.

Un último parámetro a definir para la barra de desplazamiento es el valor máximo. Nuestra serie tiene 366 puntos (valores); 30 aparecen en el gráfico así que el valor máximo de la barra de deslazamiento será 336 (para evitar que aparezcan puntos sin valor). Si la cantidad de puntos de la serie y el número de puntos a exhibir en el gráfico no varían, podemos poder una constante en la celda B7 (a la que le hemos asignado el nombre definido “chrtMaxScrollBar”).
Pero si queremos tener más control de las definiciones, usamos una fórmula para determinar dinámicamente el valor máximo. En la celda B7 ponemos:

=CONTAR(datos!$B$2:$B$367)-chrtStep

Excel no nos permite usar una referencia a la celda para definir el valor máximo de la barra de desplazamiento. Para hacerlo programamos el evento ScrollBar1_Change()

Private Sub ScrollBar1_Change()
    ScrollBar1.Max = Range("chrtMaxScrollBar")
End Sub

Ahora tenemos que definir nombres que se refieran dinámicamente a los puntos de la serie de datos y a la categoría (los valores del eje de las X). Creamos dos nombres:

- Para los puntos de la serie de datos

chrtSeries =DESREF(datos!$C$2,chrtCounter,0,12+chrtStep,1)

- Para los puntos del eje de las X

chrtCategory =DESREF(datos!$B$2,chrtCounter,0,1+chrtStep,1)

Ambos nombres se refieren a fórmulas que usan la función DESREF para determinar el rango de valores a mostrar de acuerdo a los valores que el usuario haya asignado a la barra de desplazamiento.
Cada vez que el usuario pulsa la barra de desplazamiento, el valor de la celda ligada (chrtCounter) cambia.



Para que estos cambios se reflejen en el gráfico remplazamos los rangos de la función SERIES del gráfico con los nombres definidos. Seleccionamos el gráfico y usamos el menú “seleccionar origen de datos-editar” para remplazar la referencia fija al rango por el nombre que se refiere al rango dinámico

A esta altura de los acontecimientos podemos lograr la animación sencillamente arrastrando el marcador de la barra de desplazamiento (como se ve en el video). Pero en nuestro caso usamos una macro para disparar la animación y otra para volver el gráfico al punto de partida. Estas macros están ligadas a los botones con los símbolos “>>” y “<<” grafAnim07 Todo lo que hace la macro para animar el gráfico es cambiar el valor de “chrtCounter”


Sub animate_Chart()
    Dim iX As Integer, Delay As Single, Start As Single
   
    'fijar valores del eje Y del grafico
    Call fix_Y_Values
   
    Range("chrtCounter") = 0
   
    For iX = 0 To Range("chrtMaxScrollBar")
        Range("chrtCounter") = Range("chrtCounter") + 1
            Delay = 0.1
            Start = Timer
            Do While Timer < Start + Delay
                DoEvents
            Loop
    Next iX
   
End Sub


Para volver el gráfico a la situación inicial sencillamente fijamos el valor de “chrtCounter” a 0

Sub backTo0()
    Range("chrtCounter") = 0
End Sub


El ultimo detalle es fijar el valor mínimo y máximo del eje de las Y. Podemos usar constantes, pero mejor es determinar estos valores en forma dinámica usando estas fórmulas

Para el valor mínimo (chrtXmin): =MULTIPLO.INFERIOR(MIN(datos!$C$2:$C$367),0.1)

Para el valor máximo(chrtXmax): =MULTIPLO.SUPERIOR(MAX(datos!$C$2:$C$367),0.1)

Estos valores se fijan al empezar la macro de la animación con la rutina Call fix_Y_Values

Private Sub fix_Y_Values()
   
    ActiveSheet.ChartObjects("chrtUSDBRL").Activate
    With ActiveChart
        .Axes(xlValue).MinimumScale = Range("chrtXmin")
        .Axes(xlValue).MaximumScale = Range("chrtXmax")
    End With
   
    Range("A1").Select
   
End Sub

El cuaderno con el ejemplo se puede descargar aquí.

Cálculos con los últimos n valores de una serie en Excel

Un compañero de trabajo quería calcular el promedio de los últimos 12 valores de una serie. En su caso, el precio promedio de una serie de productos

Para calcular el promedio de los últimos doce meses usaremos esta fórmula

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-12,0,12,1))

A medida que agreguemos (o quitemos) valores, la fórmula se ajustará automáticamente.

Sobre el funcionamiento de la función DESREF ya hemos escrito en este blog. El “truco” aquí es que usamos CONTAR para encontrar la última celda no vacía (suponemos que contienen números) y luego “retrocedemos” 12 puntos atrás.
En nuestro ejemplo

CONTAR(B5:B160)-12

da 2 (14-12) lo que nos “lleva” a B7; luego nos extendemos hasta B18 usando 12 como argumento en DESREF.

Un detalle a tomar en cuenta es que el rango de valores debe ser continuo (sin celdas vacías entre dos o más valores); en caso contrario el resultado será incorrecto.

Podemos modificar la fórmula de manera que la cantidad de valores a considerar en el cálculo se determine de forma dinámica

Usamos el valor en la celda B1 como argumento para determinar la cantidad de meses a tomar en cuenta

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-B1,0,B1,1))

Apéndice: en esta nota muestro como resaltar las celdas comprendidas en el cálculo con formato condicional

Gráfico dinámico que muestra los últimos n puntos de la serie

El origen de esta nota es la consulta de un lector que me pregunta cómo hacer un gráfico que muestre siempre los 12 últimos puntos de una serie.

Como en casi todo gráfico dinámico, el truco está en usar rangos dinámicos definidos en nombres. Por lo general, y en esta nota, lo haremos con la ayuda de la función DESREF.

Supongamos esta tabla de ventas que muestra los 12 meses de año 2009 y que hemos representado con un gráfico.

Queremos que el gráfico muestre siempre los últimos n meses, siendo n un número que el usuario defina. Por ejemplo, si agregamos los enero y febrero del 2010 queremos ver los datos desde marzo del 2009 a febrero del 2010.

Como ya hemos mostrado, vamos a reemplazar los rangos estáticos de la serie de datos y de los valores del eje de las categorías, por rangos dinámicos

Antes de definir los rangos dinámicos, definimos un nombre que cuente cuántos períodos (datos) hay en la tabla. Lo hacemos basándonos en la columna de los períodos (la columna B en nuestro ejemplo) ya que puede haber períodos sin datos

cntPeriodos = =CONTARA(dinamico!$B:$B)-1

Restamos 1 del resultado de CONTARA para eliminar la cuenta del título de la columna.

El próximo paso es elegir una celda donde ponemos el número de meses que queremos mostrar (en nuestro ejemplo H2, y no por capricho como ser verá luego). También a H2 le ponemos nombre, pmtMeses

Ahora definimos nombres dinámicos para el eje de la Y, grfY y para el eje de la X, grfX

grfX =DESREF(dinamico!$B$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

grfY =DESREF(dinamico!$C$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

Una vez definidos los nombres reemplazamos los rangos estáticos en la fórmula SERIES, cuidándonos de dejar el nombre del cuaderno incluido el símbolo !

La explicación de estas fórmulas es la siguiente:

dinamico!$B$3;cntPeriodos-pmtMeses calcula desde donde comienza el rango. Por ejemplo si tenemos 14 períodos en la tabla (calculados por cntPeriodos) y queremos mostrar sólo los últimos 12 (pmtMeses), cntPeriodos – pmtMeses da 2; por lo tanto nuestro rango comienza en B5 y se entiende por 12 celdas hasta B16.

Ahora podemos aprovechar la ubicación de H2 para crear un título dinámico para el grafico

Cuando reemplazamos el valor de la celda H2, el título cambia de acuerdo al contenido del gráfico.

El archivo se puede descargar aquí.

Corrección automática de rangos en fórmulas

Supongamos esta sencilla tabla que muestra las ventas de una tienda por departamento



Vemos que faltan los datos del Departamento 3. Al insertar una línea entre los departamentos 2 y 4, el rango de la fórmula =SUMA(B2:B5) se corrige automáticamente



Veamos qué pasa si queremos agregar el Departamento 6, inmediatamente debajo del Departamento 5. Seleccionamos la fila 7 e insertamos una nueva fila. En este caso, Excel no corrige el rango de la fórmula



En ciertos casos, al agregar los datos del nuevo departamento, éstos no serán incluidos en la suma! Si tenemos habilitada la opción Extender formato de rangos y fórmulas en el menú Opciones y la tabla tiene más de cinco líneas, Excel modificará el rango de la fórmula automáticamente. Pero si pegamos los datos que hemos copiado de otra hoja o fuente, o no tenemos habilitada la opción, Excel no modificará el rango de la fórmula.

Cuando creamos cuadernos que serán usados por otros, no podemos estar seguros que al agregar líneas o hacer otras modificaciones los usuarios presten atención a este potencial problema.

La solución es usar la función DESREF para crear un rango que se adapte automáticamente cada vez que se agreguen líneas en cualquier ubicación del rango de la fórmula.

En nuestro caso la fórmula será

=SUMA(B2:DESREF(B8,-1,0))



Como ven, el primer argumento de la función, el "ancla", se refiera a la celda que contiene la fórmula. El segundo indica una referencia a una fila hacia arriba.

El mismo caso puede darse cuando insertamos una columna en un rango donde sumamos a los largo de una fila. Por ejemplo, si transponemos nuestra tabla, la fórmula será

=SUMA(B2:DESREF(G2,0,-1))

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

Gráficos dinámicos – Mostrar puntos en función de valores.

Ya hemos visto una técnica para determinar cuantos puntos de una serie mostrar en un gráfico.
En esta entrada veremos como determinar la cantidad de puntos a mostrar en función de un determinado valor.

Supongamos que queremos generar un gráfico de columnas a partir de esta tabla de ventas (el archivo con el ejemplo se puede descargar aquí)



Nuestro objetivo es determinar la cantidad de puntos a exhibir en función de un determinado valor. Digamos que queremos exhibir los 5 meses con más ventas.

Empezamos por crear una columna auxiliar, con la fórmula

=JERARQUIA(B2,$B$2:$B$13)+CONTAR.SI($B$2:B2,B2)-1



Esta fórmula la otorga a cada valor un número de orden. Usamos CONTAR.SI para "desempatar" en caso que dos meses tenga la misma suma de ventas.

Ahora creamos una tabla auxiliar donde ordenamos la tabla original de mayor a menor. Para lograr esto usamos las funciones INDICE y COINCIDIR



En el rango E2:E13 ponemos la serie del 1 al 12 que nos servirá como referencia para ordenar los valores. En el rango F2:F13 ponemos la fórmula

=INDICE($A$2:$A$13,COINCIDIR(E2,$C$2:$C$13,0))

Esta fórmula usa los valores del rango E2:E13 para obtener el mes adecuado. Lo mismo hacemos en el rango G2:G13 para poner la suma del mes.

Nuestro próximo paso es crear el gráfico. En esta etapa veremos todos los meses



Ahora tenemos que crear dos rangos dinámicos usando nombres (ver la nota del enlace más arriba).
Creamos dos nombres

mes =DESREF(Hoja1!$F$2,0,0,Hoja1!$I$1,1)
suma = DESREF(Hoja1!$G$2,0,0,Hoja1!$I$1,1)

Como ven, hemos ligado los nombres a la celda I1 de la hoja. En esta celda ponemos, en esta etapa, la cantidad de puntos de la serie que queremos mostrar. Como estos nombres se refieren a la tabla auxiliar, donde hemos ordenado los datos en forma decreciente, si ponemos 5 en la celda I1, los rangos dinámicos mostrarán los primeros cinco meses de la tabla, que son los primeros 5 meses con mayores ventas.

Nuestro próximo paso es reemplazar en la función SERIES del gráfico, los rangos de los valores por los nombres que acabamos de crear.

Seleccionamos el gráfico y abrimos el menú Datos de Origen



Y reemplazamos los rangos del gráfico por los nombres



Esto también se puede hacer seleccionando la serie de datos en el gráfico y reemplazando los valores en la función SERIES que aparece en la barra de fórmulas.

Cada vez que reemplacemos el valor en la celda I1, el gráfico mostrará los valores correspondientes



Nuestro último paso es que el gráfico dependa de un valor de ventas. Por ejemplo, ponemos 60000 en una celda y que el gráfico nos muestre todos los meses con ventas mayores a 60000. Para esto agregamos una celda auxiliar, I2, donde ponemos el monto de ventas a partir del cual queremos mostrar los meses. En la celda I1 ponemos esta fórmula

=CONTAR.SI(G2:G13,">"&I2)

Ahora nuestro gráfico muestra todos los meses con ventas mayores al monto introducido en la celda I2

Technorati Tags: MS Excel

Gráficos Dinámicos con Autofiltro

Una característica interesante de Autofiltro es que si creamos una gráfico a partir de una lista, al aplicar Autofiltro, el gráfico se irá adaptando a los datos visibles (filtrados).

El archivo con el ejemplo se puede descargar aqui


Por ejemplo, a partir de esta lista



creamos este gráfico (que no tiene mucho sentido, en esta etapa del ejemplo)



Si filtramos la lista para mostrar sólo las ventas de José, el gráfico se adaptará



En cuanto empezamos a trabajar con este gráfico descubrimos que hay un problema: el gráfico se "encoge" cuando navega sobre líneas ocultas. Por ejemplo, nuestro gráfico puede llegar a verse así



Podemos sobreponernos a este problema y, mejor aún, hacer que en el título de nuestro gráfico aparezca el nombre del agente cuyas ventas estamos mostrando. Todo esto puede hacerse sin macros y usando las técnicas que mostramos en la nota anterior sobre cómo ocultar gráficos en Excel.

Como en ese ejemplo, usaremos dos hojas. En la Hoja1 tendremos la tabla de datos y mostraremos el gráfico (de hecho, una imagen del gráfico).
En la hoja dos reside el "motor" del modelo: el gráfico y la lista de los agentes. En la celda contigua al agente ponemos una fórmula que totaliza las ventas de cada agente. Esto nos servirá para identificar el nombre del agente cuyo gráfico queremos exhibir en la Hoja1.

Empezamos por cortar y pegar el gráfico que acabamos de crear a otra hoja (en nuestro ejemplo a la Hoja2).

Seleccionamos el rango "sobre" el cual se encuentra el gráfico y lo definimos con un nombre. En nuestro caso definimos el nombre "grafico"

grafico: =Hoja2!$A$2:$G$17

En la Hoja2 elegimos una celda vacía, por ejemplo $I$1 y la llamamos sin_graf

sin_graf =Hoja2!$I$2

En el rango A20:A24 ponemos la lista de los agentes; en la celda B20 ponemos la fórmula =SUMAR.SI(agente,A20,ventas) y la copiamos al rango B20:B24.

Los nombres usados en la fórmula se refieren a rangos en la Hoja1

agente: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

ventas: =DESREF(Hoja1!$C$2,0,0,CONTARA(Hoja1!$C:$C)-1,1)

En la Hoja1 ponemos en la celda E1 la siguiente fórmula

=INDICE(Hoja2!A20:A24,COINCIDIR(Hoja1!F1,Hoja2!B20:B24,0))

En la celda F1 ponemos la fórmula

=SUBTOTALES(9,ventas)

Esta fórmula nos da el total de ventas del agente elegido con Autofiltro. La fórmula en la celda E1 nos da el nombre del agente, basado en el resultado de la celda F1.

Volvemos por un instante a la Hoja2 y ligamos el cuadro de texto del título del gráfico a la celda E1 en la Hoja1



Ahora tenemos que crear una imagen vinculada al gráfico en la Hoja3.
Elegimos una celda vacía, y la copiamos (Ctrl+C). Seleccionamos la celda E3, abrimos el menú Edición pulsando Mayúsculas (Shift) y elegimos Pegar--Vínculos Imagen. El resultado es una imagen vacía del tamaño de la celda.

Ahora creamos el nombre

mostrar_graf =SI(ESNOD(Hoja1!$E$1),sin_graf,grafico)

Esta fórmula nos permite mostrar u ocultar el gráfico basado en su resultado. Para qu esto sucede ligamos la imagen a la fórmula de la siguiente manera:

Seleccionamos la imagen que creamos con Pegar Vínculos—Imagen y en la barra de fórmulas reemplazamos la referencia por "=mostrar_graf"



Ahora podemos filtrarla lista con Autofiltro y veremos aparecer el gráfico. Por ejemplo, si elegimos Miguel



Este modelo tiene un serio problema potencial: si dos agentes tienen exactamente el mismo volumen de ventas, no se mostrará el gráfico.
Las soluciones posibles son:

1 – usar macros para determinar el nombre de la primera fila visible en la columna A a partir de la celda A2; ver el ejemplo desarrollado en el sitio Contextures;

2 – agregar una columna auxiliar que agregue a cada total de ventas del mes de cada agente un número identificatorio suficientemente pequeño como para no alterar los resultados. Por ejemplo, a Roberto le damos el número 1; a las ventas de Roberto le sumamos 1/1000000. Lo mismo con los restantes (Pedro 2/1000000, etc). Luego basamos las fórmulas y el gráfico en la columna auxiliar.

Technorati Tags: MS Excel