domingo, octubre 15, 2006

Tablas Dinámicas en Excel – Eliminar elementos caducos

Si examinan mi entrada de ayer sobre campos y elementos calculados en tablas dinámicas, notarán que ciertos elementos siguen apareciendo en las listas desplegables a pesar de que los hemos borrado de la base de datos. Estos elementos persisten aún después de haber actualizado la tabla.

En el ejemplo de la nota anterior

Excel tablas dinamicas


los meses de mayo y junio siguen apareciendo a pesar de haber sido borrados de la lista de datos.

Para que los elementos que han sido borrados de la lista de datos (los elementos "caducos") desaparezcan hacemos lo siguiente:

1 - Eliminamos los elementos calculados que hubiéramos definido (en nuestro caso Bimestre 1, Bimestre 2 y Total). Para eso abrimos el menú Formulas del asistente de tablas dinámicas, elegimos el elemento calculado que queremos borrar y pulsamos el botón Eliminar.


Excel tablas dinamicas

2 - Quitamos el campo que contiene los elementos arrastrándolo fuera del rango de la tabla dinámica

Excel tablas dinamicas

3 - Actualizamos la tabla dinámica con el botón de actualizar

Excel tablas dinamicas

4 - Agregamos de nuevo el campo a la tabla. Sólo los elementos existentes en la base de datos aparecen ahora en la lista desplegable de elementos del campo.

Excel tablas dinamicas




Categorìas: Manejo de Datos_

Technorati Tags: ,

sábado, octubre 14, 2006

Tablas Dinámicas en Excel – Campos y Elementos calculados.

Las tablas dinámicas son un tipo de objeto que "reside" en la hoja de cálculos pero el rango ocupado por la tabla tiene un comportamiento distinto a los rangos normales de Excel. El rango ocupado por la tabla dinámica no puede ser modificado directamente en la hoja. Por ejemplo, no podemos agregar líneas o columnas, o fórmulas en las distintas celdas.

Cuando queremos hacer este tipo de modificaciones en una tabla dinámica tenemos dos posibilidades:

1 - seleccionar la tabla y copiarla a otra ubicación (ya sea en la misma hoja, en otra o en otro cuaderno) usando Edición – Pegado Especial – Valores. Este método tiene la ventaja de dar mucha flexibilidad en el manejo de los datos (formatos, fórmulas, etc); pero tiene la gran desventaja de romper el vínculo dinámico entre la tabla y los datos originales.

2 – Crear campos y/o elementos calculados. De esto nos ocuparemos en esta nota.

Para crear los ejemplos hemos modificado la tabla de datos de la nota anterior, agregándole los campos "mes" y "unidades".

Basándonos en nuestra tabla de datos hemos creado esta tabla dinámica





En el área de Página hemos puesto los meses, de manera que podemos ver los datos de cada mes con un clic.

Ahora digamos que queremos calcular el precio promedio de las unidades vendidas (ventas / unidades = precio promedio) y también agrupar los meses por bimestres (enero + febrero = bimestre 1).

El precio promedio consiste en crear un nuevo campo, dividiendo los elementos de un campo por los de otro. Esto es un campo calculado. Los pasos a dar son los siguientes:

1 – cliqueamos en algún lugar de la tabla y en el asistente de Tablas Dinámicas activamos Fórmulas – Campos Calculados




2 - En la ventanilla "nombre" anotamos "Precio Promedio" y en la ventanilla "Fórmula" =Ventas/Unidades (con la ventanilla activada, hacemos doble clic a Ventas, luego anotamos el símbolo "/" y luego doble clic a Unidades)



3 - Después de pulsar "Aceptar" y realizar algunos ajustes al formato, obtenemos esta tabla dinámica



Como ven, hemos agregado un nuevo campo a la tabla: "Precio Promedio".


Para demostrar el uso de elementos calculados, reorganizamos nuestra tabla dinámica poniendo los meses como campos de fila y los departamentos en el área de Página




Para crear el elemento calculado "Bimestre 1" procedemos de la siguiente manera:

1 - Seleccionamos la celda A4 (donde aparece "Mes", el nombre del campo con cuyos elementos crearemos un elemento calculado), en el asistente de Tablas Dinámicas activamos el menú Fórmulas – Elementos Calculados

2 - En el diálogo que se abre seleccionamos "Mes" en la ventanilla "Campos"; en la ventanilla "elementos" vemos los meses (los elementos del campo). En la ventanilla nombre escribimos Bimestre 1; luego seleccionamos la ventanilla Fórmula y anotamos =enero+febrero (lo que se hace con un doble clic sobre el nombre del elemento). Luego apretamos Enter y volvemos a seleccionar el campo Mes




3 - Repetimos la operación para el Bimestre 2 (marzo+abril) y pulsamos "Aceptar".

Excel tiene dos problemas relacionados con los elementos calculados:
1 - Son agregados automáticamente al final de la lista de elementos del campo
2 – El total general incluye los elementos calculados, por lo tanto da como resultado el doble de lo que debería ser.




El primer problema lo resolvemos moviendo los elementos a la posición deseada. Una de las formas de hacer esto es copiar Bimestre 1 y pegarlo inmediatamente debajo de "febrero" (o escribir manualmente Bimestre 1). Excel reorganiza la tabla automáticamente



El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total general para las columnas en Opciones de Tablas.
La otra es crear un nuevo elemento, Total, que suma Bimestre 1 y Bimestre 2



Obteniendo



En resumen:

Un campo calculado es un nuevo campo creado realizando operaciones con campos existentes.

Un elemento calculado es un nuevo elemento en un campo, creado con los elementos existentes del campo.

Para eliminar los campos o elementos calculados tenemos que usar el asistente de Tablas Dinámicas. Por ejemplo, para eliminar el campo Precio Promedio abrimos el diálogo de Fórmulas en Campos Calculados




En la ventanilla Nombre elegimos el campo que queremos eliminar y luego pulsamos el botón Eliminar.



Categorìas: Manejo de Datos_


Technorati Tags: ,

Tablas Dinámicas en Excel – Agrupar datos

En el mes de marzo publiqué una serie de notas sobre el manejo de pequeñas bases de datos en Excel usando tablas dinámicas (pivot tables). En estas notas cubría aspectos funcionales de las tablas dinámicas como actualización de datos y referencias dinámicas
En esta nota veremos otras funcionalidades de las tablas dinámicas que ayudan a hacer el trabajo con ellas más eficiente.

Para nuestros ejemplos usaremos una tabla que contiene las ventas, día por día, de cuatro departamentos de una empresa imaginaria durante la primera mitad del año.

Nuestra tabla tiene 725 líneas (181 días * 4 departamentos + 1 línea para los encabezamientos).

Con facilidad creamos un informe que nos muestra el total de ventas del medio año por departamento




El campo Fechas lo hemos puesto en el área de páginas de manera que podemos elegir una fecha determinado y ver las ventas por departamento



El menú de Tablas Dinámicas ofrece la posibilidad de agrupar datos ligados a campos que Excel puede reconocer como fechas. Para usar esta funcionalidad el campo de fechas debe encontrarse en el área de las filas o de las columnas. Por lo tanto movemos el campo Fechas al área de columnas



Ahora seleccionamos el rótulo Fecha, abrimos el menú de tablas dinámicas y elegimos la opción "agrupar y mostrar detalle"



Al elegir esta opción se abre un diálogo donde podemos definir la forma de agrupar los datos. Las opciones van desde segundos a años. También podemos definir las fechas de comienzo y fin.



Por ejemplo, si elegimos "trimestres" obtenemos



También podemos elegir más de una opción simultáneamente, por ejemplo trimestre y mes



Con este resultado



Como habrán notado, no existe la opción de agrupar por semanas. Pero Excel nos permite hacer esto, si seleccionamos "días" y en la ventanilla "número de días" ponemos "7"



El resultado es



Nuestro ejemplo tiene una limitación potencial, ya que el número de columnas de una hoja de Excel es 256 y el número de días del año es 365 (o 366 si es bisiesto).
Es decir que si tuviéramos las ventas de todo el año, día por día, no podríamos poner el campo Fecha en el área de columnas, para luego agruparlo.
Lo que podemos hacer en este caso, es poner el campo Fecha en el área de filas,



agrupar, por ejemplo por mes



y luego arrastrar los campos agrupados al área de columnas






Categorías: Manejo de Datos_

Technorati Tags: ,