Gráficos en Excel – Actualización automática de datos.

Supongamos un gráfico basado en una tabla de datos como esta:




Excel nos permite generar con facilidad un gráfico que represente el desarrollo de las ventas:




Ahora supongamos que queremos que el gráfico se actualice cada vez que agregamos datos a nuestra tabla.
Una posibilidad es hacerlo manualmente, para lo cual tendremos que activar el gráfico y en el menú Gráfico---Datos de origen y actualizar al rango de los datos



Pero existe la posibilidad de forzar a Excel a actualizar el gráfico en forma automática cada vez que agreguemos datos a nuestra tabla.
Para lograr esto definimos nombres que contienen fórmulas con la función DESREF para crear rangos automáticos, como ya vimos en la entrada sobre referencias dinámicas.
Para crear un gráfico que se actualice automáticamente seguimos estos pasos:
1 – creamos un nombre llamado Mes (Insertar---Nombres---Definir) con esta fórmula: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
2 – creamos un segundo nombre que contendrá esta fórmula: =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1)
3 – Contrariamente a lo que parecería lógico, no aplicamos el nombre con el rango dinámico en lugar de el rango definido en la ventana Rango de Datos (ver arriba), sino en una fórmula que define el gráfico, llamada SERIES. Activamos el gráfico y seleccionamos una de las barras. Al hacer esto veremos la fórmula "SERIES" en la barra de fórmulas



En esta fórmula reemplazamos las referencias a los rangos por los nombres que acabamos de definir:

=SERIES(Hoja1!$B$1,grafico_aut_sp.xls!mes,grafico_aut_sp.xls!ventas,1)

Como se puede ver, Excel agrega automáticamente el nombre del cuaderno donde están definidos los nombres.

A partir de este momento, el gráfico se actualizará automáticamente cada vez que agreguemos los datos de un mes de ventas.

Y antes de finalizar la entrada, unas palabras sobre la fórmula SERIES. Esta fórmula es distinta de las que conocemos en Excel. No podemos utilizarla en celdas de una hoja y tampoco podemos combinar en ella funciones que usamos en las hojas de cálculo. La tarea de esta fórmula es definir los datos que se utilizan en un gráfico. Su sintaxis es la siguiente:
=SERIES(título, rango de la categoría, valores, orden)

Como hemos dicho, no podemos usar funciones regulares de Excel en esta fórmula, pero como hemos visto, si podemos usar nombres que contienen fórmulas comunes.


Categorías: Graficos_

Technorati Tags: Gráficos en Excel

Excel - Controlando el orden de entrada de datos en una hoja

En una entrada anterior mostré como limitar el área de trabajo en una hoja de Excel. Para hacer esto usamos la propiedad Scroll Area de la hoja, como explico en esa entrada.
Pero ahora uno de mis lectores me pregunta como controlar la secuencia de entrada de datos en una hoja de Excel. Esta situación se da en especial en formularios, donde queremos facilitar la entrada de datos. Por ejemplo, que cada vez que apretamos "enter" Excel sepa elegir la celda adecuada en la secuencia.
Esto se puede hacer con macros especiales llamadas "eventos". Estas macros están ligadas a una hoja específica y entran en acción cuando sucede algún evento determinado, por ejemplo, cambia el valor de una celda o elegimos una celda en una rango determinado. De ahí su nombre.
Daré aquí un ejemplo sencillo. Un formulario para recuento de inventario donde en la columna A se introduce el número de catálogo, en la columna B la cantidad, en la columna C el precio y el la columna D calculamos el valor total (cantidad X precio).




Con la ayuda de una macro del tipo eventos, queremos que después que el usuario ingrese el número de catálogo y apriete "enter", Excel pase automáticamente a la celda de la columna B de la misma fila (cantidad); después de ingresar la cantidad Excel pase directamente a la celda del precio y calcule automáticamente el total en la columna D.

Los pasos a seguir son los siguientes:

1 – apuntamos con el mouse a la pestaña de la hoja y pulsamos el botón derecho; en el diálogo que se abre seleccionamos "ver código"




2 – en el editor del Vba (prestar atención que la Hoja1 aparece seleccionada) copiamos este código

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngA As Range, rngB As Range, rngC As Range

Set rngA = [A:A]
Set rngB = [B:B]
Set rngC = [C:C]

If Union(Target, rngA).Address = rngA.Address Then _
Target.Offset(0, 1).Select

If Union(Target, rngB).Address = rngB.Address Then _
Target.Offset(0, 1).Select

If Union(Target, rngC).Address = rngC.Address Then
Target.Offset(0, 1).Value = Target * Target.Offset(0, -1)
Target.Offset(1, -2).Select
End If
End Sub




3 – A partir de este momento cuando se produce un cambio en la hoja, la macro evalúa si el cambio ocurrió en alguno de los rangos definidos (rngA, rngB, rngC). De ser así procede de acuerdo al código.


Esta macro supone que los datos son introducidos siguiendo el orden establecido (número de catálogo, cantidad, precio). Si variamos el orden, el total calculado (columna D) puede ser incorrecto.


Categorías:Varios_

Technorati Tags: Vba, Eventos en Excel

Excel - Como importar archivos texto a con más de 65536 filas.

Ya hemos visto algunas técnicas para importar archivos texto a Excel.
Si usamos alguna de las versiones a partir de Excel 97 y hasta Excel 2003, el máximo de filas que podemos importar es 65536.
En versiones anteriores a Excel 97 el máximo era 16384. A partir de Excel 2007 dispondremos de más de 1 millón de filas.
Pero como la mayoría de los usuarios seguimos trabajando con alguna de las versiones entre Excel 97 y Excel 2003, veremos como solucionar el problema de importar archivos de mas de 65536 filas.
La base de datos de conocimientos de Microsoft propone una macro para solucionar el problema. La solución consiste en dividir automáticamente el archivo a importar, si excede el máximos de filas permitido, en varias hojas.
En esta entrada reproduzco la macro propuesta por Microsoft, con los mensajes traducidos al español.
Además he reemplazado el método Inputbox del original por GetOpenFilename, que permite elegir el archivo desde un diálogo, en lugar de tener que escribir el nombre y la ubicación del archivo que queremos importar.

El archivo con la macro se puede descargar aquí



Esta macro no divide las líneas del archivo importado en columnas. Esto debe hacerse usando el comando Datos---Texto en Columnas.


Categorías: Varios_





Technorati Tags: Vba, Importar archivos a Excel