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

Algo más sobre nombres en Excel

Supongamos una hoja de un cuaderno Excel con la siguiente tabla



Si abrimos el diálogo de nombres (Insertar-Nombres-Definir), veremos que no hay ningún nombre definido



Empezamos por definir un nombre que contiene el rango B2:B13 (que llamamos "ventas")



Si abrimos ahora el diálogo Insertar-Nombres-Definir veremos el nombre que acabamos de crear



Digamos que queremos ver en qué meses las ventas fueron inferiores a 70.000, para lo cual aplicamos Autofiltro



Ahora queremos imprimir nuestro reporte, para lo cual definimos algunos parámetros con el menú Configurar Página-Hoja



Ahora volvemos a abrir el menú Insertar-Nombres-Definir



Vemos ahora que Excel a creado dos nombres que corresponden a las definiciones de impresión que hemos establecido: Área_de_impresión y Títulos_a_imprimir.

Estos nombres han sido creados como nombres "locales", es decir, son válidos sólo para la Hoja1 (en nuestro caso). Si abrimos el diálogo Insertar-Nombes-Definir en la Hoja2, sólo veremos "ventas".

¿Son estos todos los nombres en nuestro cuadernos? Excel no tiene un método nativo para exhibir todos los nombres definidos en el cuaderno. Para ver todos los nombres podemos usar una macro como la siguiente (sugerida por John Walkenbach)

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.names
Cells(Row, 4) = n.Name
Cells(Row, 5) = " " & n.RefersTo
Row = Row + 1
Next n
End Sub

o usar el excelente complemento Name Manager, creado por Jan Karle Pieterse,.



Como vemos, Excel ha creado el nombre Hoja1!_FilterDatabase que contiene el rango de la tabla a la que hemos aplicado Autofiltro. Este es un nombre "oculto", es decir, no parece en la lista de nombres (Insertar-Nombres-Pegar). Excel crea nombres ocultos en todo tipo de tareas, por ejemplo impresión como hemos visto, cuando usamos el Solver, etc.

Volviendo a Hoja1!_FilterDatabase, si agregamos datos a la tabla y volvemos a aplicar Autofiltro, veremos que Excel cambia el rango del nombre para adecuarlo a la nueva situación.

Un uso interesante, pero que hay que ejercer con cautela, consiste en reemplazar manualmente el rango de estos nombres. Esto es relevante, por supuesto, a los nombres que aparecen en el diálogo de Nombres-Definir.

Por ejemplo, el lector Carlos Clemente me envía una hoja para calcular tablas de amortización (que les recomiendo descargar y analizar). En esta hoja, Carlos reemplaza el rango del nombre Área_de_impresión por una fórmula, lo que le permite definir el área de impresión en forma dinámica de acuerdo a la cantidad de pagos del préstamo. Esto es necesario, ya que si reducimos el número de pagos y por ende el número de filas a imprimir, Excel seguirá imprimiendo el área mayor que haya sido impresa con anterioridad.
Otro característica interesante en el modelo del amigo Carlos es el uso de Formato Condicional para ocultar las filas sin datos.

Como vemos, el uso de nombres no sólo nos permite simplificar la interpretación de las fórmulas y crear rangos dinámicos, sino también afectar el comportamiento de Excel, como en el caso del área de impresión (o de Títulos a Imprimir).




Technorati Tags: MS Excel

La función DESREF (OFFSET) de Excel – Una explicación

La función DESREF de Excel (OFFSET en la versión inglesa) es una de esas funciones que causan cierta confusión a los usuarios novicios. Tal vez esta confusión esté relacionada al hecho que DESREF puede referirse tanto a una celda específica como a un rango de celdas. La sintaxis de la función difiere en cada uno de los casos.

En mis notas sobre actualización automática de datos y sobre manejo de pequeñas bases de datos con Excel, mostraba como usar DESREF para construir rangos dinámicos.

En la más reciente nota sobre bases de datos de imágenes en Excel mostraba también el otro uso de DESREF, crear una referencia a una única celda.

Tres hechos hay que tener en cuenta cuando queremos usar DESREF:

1 – DESREF puede crear una referencia tanto a una celda única como a un rango de celdas;

2 – La sintaxis en cada caso es distinta;

3 – Como definir el primer argumento de la función, al que llamaremos el "ancla"

La sintaxis de DESREF es

El "ancla" es la celda que es nuestro punto de partida. Por ejemplo, si escribimos esta fórmula: DESREF(A1,2,1), estamos estableciendo una referencia a la celda B3, como pueden ver en este ejemplo



Puesto en palabras, la fórmula dice: empezamos en A1 (el "ancla"), nos movemos 2 filas hacia abajo y una columna a la derecha y así llegamos a B3.

Con la ayuda de la función COINCIDIR y Validación de Datos, podemos construir un modelo dinámico a partir de nuestro ejemplo.
Definimos esta validación de datos en la celda A9




En la celda B9 sustituimos el segundo argumento (Filas) con la función COINCIDIR
=DESREF(A1,COINCIDIR(A9,A2:A6,0),1), para definir el número de filas en función del número de catalogo elegido. De esta manera DESREF nos dará el nombre del producto en forma dinámica.




Como pueden ver, cuando usamos DESREF para referirnos a una celda, omitimos los argumentos "alto" y "ancho".

Cuando usamos DESREF para referirnos a un rango, usamos cero como valor para los argumentos "filas" y "columnas". Por ejemplo, si queremos establecer una referencia a la tabla de productos en el ejemplo (el rango A1:B6) usamos la fórmula: =DESREF(A1,0,0,6,2).

En la nota manejo de pequeñas bases de datos con Excel hay un ejemplo de rango dinámico usando DESREF.


Categorías: Funciones&Formulas_

Technorati Tags: Excel, DESREF