TABLAS DINÁMICAS

INTRODUCCIÓN

Una de las características más potentes de Excel se llama tablas dinámicas, que permiten una gran flexibilidad a la hora de buscar varios elementos relacionados con datos. La función de tabla dinámica proporciona un método rápido y fácil para cambiar la forma de ver sus datos.

QUE ES UNA TABLA DINÁMICA

Las tablas dinámicas, también llamadas pivot tables, son una herramienta para análisis de bases de datos (BD). Se encargan de resumir y ordenar la información contenida en la BD.
Esta clase de tablas permiten analizar sólo una porción de la BD, es decir, con una BD con gran cantidad de campos o columnas, ayudan a visualizar únicamente la información relevante, con lo que el análisis se torna más sencillo.

QUE NOS PERMITE HACER LA TABLA DINÁMICA

Una tabla dinámica en Excel permite hacer resúmenes de una Base de Datos, utilizándose para,
promediar, o totalizar datos.Para que su uso esté justificado, la cantidad de información con la que
se trabaja en la tabla debe ser relativa mente grande.

QUE NOS PROPORCIONA
Las tablas dinámicas proporcionan un modo de resumir y analizar fácilmente grandes volúmenes de datos. Se llaman tablas dinámicas porque se puede cambiar su disposición reordenando o cambiando de posición los encabezados de fila y columna, rápida y fácilmente.Las tablas dinámicas se utilizan para resumir, consolidar, analizar, filtrar e informar:

• Resumir bases de datos grandes: se pueden utilizar las tablas dinámicas para resumir grandes cantidades de datos que pueden estar en una hoja de trabajo o en una base de datos externa.

• Preparar datos para su representación gráfica : Los gráficos basados en tablas dinámicas cambiarán automáticamente cuando cambie la tabla dinámica. Muchos problemas vienen disponiendo los datos de un modo que permita crear gráficos, las tablas dinámicas son ideales para este propósito.
• Análisis de datos adecuado: Se analizarán de forma adecuada las bases de datos mediante las tablas dinámicas, buscando tendencias, excepciones y problemas. Se puede organizar rápidamente el modo en que se resumen y presentan los datos y acceder a un mayor nivel de detalle cuando se desee.
• Crear Informes: Es uno de los usos más habituales de Excel, ya que se puede interactuar en la pantalla o imprimir.

COMO CREAR UNA TABLA DINÁMICA
Para crear una tabla dinámica, Excel nos proporciona el Asistente para tablas y gráficos dinámicos.
Supongamos que tenemos una colección de datos de los artículos del almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

Para acceder al asistente accedemos al menú Datos - Informe de tablas y gráficos dinámicos.

Aparece la primera pantalla de tres de las que se compone el asistente para tablas dinámicas, donde nos solicita que indiquemos la situación de los datos a analizar y qué tipo de informe vamos a crear.

En nuestro caso indicamos que vamos a seleccionar los de la Lista de Excel y que vamos a crear una Tabla dinámica. En la segunda pantalla seleccionamos el rango dónde tenemos los datos a utilizar en la tabla dinámica. Debemos seleccionar todas las celdas incluyendo las cabeceras.

En esta última pantalla debemos indicar dónde vamos a situar el resultado de la tabla dinámica. Hemos escogido situarla en una Hoja de cálculo nueva. Si pulsamos sobre Diseño... accedemos a un cuadro de diálogo donde podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

En la imagen podemos apreciar que nos aparece una estructura formada por las filas, columnas, datos y además un campo para el nº de página. Y en el lateral derecho tenemos los campos disponibles de la tabla, los cuales podemos arrastrar a las diferentes partes del diagrama de la izquierda. Por ejemplo podríamos contruir una tabla dinámica con la siguiente estructura: - En el campo Fila arrastramos REF, las referencias de nuestros artículos. - En el campo Columna arrastramos MES. - Y en Datos arrastramos IMPORTE Nos quedaría una estructura como podemos ver en la imagen de abajo.

Tras realizar la tabla dinámica este sería el resultado obtenido. Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes. Con esta estructura es mucho más fácil analizar los resultados. Una vez creada la tabla dinámica nos aparece en la hoja, la barra de herramientas Tabla dinámica: (La podemos visualizar y ocultar con la opción de menú Ver -- Barras de Herramientas.) En esta barra nos encontramos botones útiles como el botón para darle a la tabla un formato predefinido, y otros que veremos a lo largo del tema.. También aparece el cuadro Lista de campos de tabla dinámica que nos permite modificar la estructura de la tabla añadiendo campos a las distintas zonas, o bien utilizando el botón Agregar a en combinación con el cuadro de lista que aparece al lado del botón, o simplemente arrastrándo el campo sobre la zona deseada. De igual forma podemos quitar un campo de un zona arrastrándolo fuera de la tabla. Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos. Esta lista de campos se puede mostrar u ocultar con el botón de la barra Tabla dinámica.

REFLEXIÓN
"estas tablas son muy útiles al momento de tener mucha información ya que estas nos permiten ver los resultados que deseamos de una lista sin embargo la información se debe colocar de una manera que tenga sentido esto de podría usar en las tiendas para saber lo que cada vendedor vende o la cantidad de mercancía vendida..."


ROSAS ALMENDRA JOSÉ DAVID

SUBTOTALES

INTRODUCCIÓN
Cuando analice una lista de números, el primer paso crucial es resumir los datos, mostrando totales y promedios, por ejemplo. En vez de hacer el travajo manualmente (ordenando la lista, insertando filas y encabezados extra y luego añadiendo cálculos para calcular totales de sub grupos) agrupe automaticamente los datos de la lista utilizando una columna que usted especifique, insertando cálculos de resumen en cada cambio de datos.Después de ordenar la lista por uno o mas campos, excel inserta una fila de subtotal para cada grupo y añade una formula que realiza el calculo que especifica.Es extremadamente fácil suprimir subtotales y volver a crearlos.

COMO SE CALCULAN LOS SUBTOTALES

Excel calcula los subtotales con una función de resumen, como Suma o Promedio. Puede mostrar subtotales en una lista con más de un tipo de cálculo a la vez.

QUE HACEN
Puede hacer que Excel calcule subtotales o totales de parte de los datos contenidos en hojas de cálculo. Por ejemplo, en una hoja de cálculo que contenga datos sobre ventas correspondientes a tres categorías de productos distintas, primero puede ordenar los productos por categoría y, a continuación, seleccionar todas las celdas que contienen datos y abrir el cuadro de diálogo Subtotales (menú Datos, comando Subtotales). 

EJEMPLOS
En el cuadro de diálogo Subtotales puede elegir la columna en la que desea basar los subtotales (como por ejemplo para cada uno de los valores distintos de la columna Semana), el cálculo de resumen que desea realizar y la columna o columnas que contienen los valores que deben resumirse. Por ejemplo (como se muestra en la imagen anterior), podría calcular los subtotales de la cantidad de unidades vendidas en cada categoría. Una vez definidos los subtotales, aparecen en la hoja de cálculo.




Como muestra la imagen anterior, cuando se agregan subtotales a una hoja de cálculo, Excel también define grupos tomando como base las filas usadas para calcular los subtotales. Las agrupaciones forman un esquema de su hoja de cálculo según los criterios usados para crear los subtotales. Todas las filas que contienen productos de mobiliario aparecen en un grupo, las filas que contienen herramientas en otro, etc. La sección de esquema de la parte izquierda de la hoja de cálculo contiene controles que puede utilizar para mostrar u ocultar grupos de filas.




En la sección de esquema se pueden encontrar tres tipos de controles:

• Botones Ocultar detalles    Cuando las filas de un grupo están visibles, aparece un botón para ocultar detalles junto a dicho grupo.
• Botones Mostrar detalles    Cuando se oculta un grupo de filas, el botón que aparece junto al grupo cambia y se transforma en un botón para mostrar detalles . Al hacer clic en un botón Mostrar detalles se restauran las filas de ese grupo y aparecen en la hoja de cálculo.
• Botones Nivel    Cada uno de los botones de nivel numerados representa un nivel de organización dentro de una hoja de cálculo; al hacer clic en un botón de nivel se ocultan todos los niveles de detalle situados debajo del botón en el que se hace clic.

Acontinuacion se identifican los tres niveles de organización del gráfico anterior.

Nivel	Descripción
1	Total general
2	Subtotales de cada grupo
3	Filas de la hoja de cálculo

En la hoja de cálculo presentada en la imagen anterior, al hacer clic en el botón de nivel 2 se ocultarían las filas que contuvieran datos sobre las ventas de productos concretos, pero quedarían visibles en la hoja de cálculo la fila del total general (nivel 1) y todas las filas que contienen los subtotales de cada producto (nivel 2).




Para mayor flexibilidad, puede agregar niveles de detalle al esquema creado por Excel, lo que le permitirá ocultar detalles concretos de vez en cuando. Por ejemplo, puede que desee ocultar las ventas de vallas, campanillas y estacas de bambú (que sabe vender bien) para ver el nivel de ventas de los demás productos y compararlos entre sí.

CREAR UN NUEVO GRUPO DE ESQUEMA DENTRO DE UN GRUPO EXISTENTE

1. Seleccione las filas que desea agrupar.
2. Elija el comando Agrupar y esquema del menú Datos y haga clic en Agrupar.

Excel creará un nuevo grupo en un nuevo nivel (nivel 4), como se puede ver en la imagen siguiente.

QUITAR UN GRUPO

1. Seleccione las filas incluidas en un grupo.
2. Elija el comando Agrupar y esquema del menú Datos y haga clic en Desagrupar.

 Sugerencia   Si desea quitar todos los subtotales de una hoja de cálculo, haga clic en la opción Subtotales del menú Datos y, después, en Quitar todos.

INSERTAR SUBTOTALES INDIVIDUALES

 Columna cuyos subtotales se desea calcular

Subtotales

1. Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni columnas en blanco en el rango.
2. Haga clic en una celda de la columna cuyos subtotales se desea calcular. En el ejemplo anterior, haría clic en una celda de la columna Deporte, columna B.
3. Haga clic en Orden ascendente o en Orden descendente.
4. En el menú Datos, haga clic en Subtotales.
5. En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotalesclic en la columna Deporte.
6. En el cuadro Usar función, haga clic en la función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desee utilizar para calcular los subtotales.
7. En el cuadro Agregar subtotal a, active la casilla de verificación de cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría la columna Ventas.
8. Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.
9. Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen debajo de los datos.
10. Haga clic en Aceptar.

REFLEXIÓN
"con los subtotales podemos ordenar de una mejor forma de información de una lista con esta forma de ver la información podemos esconder los datos que no se desean ver, cuando empiece a utilizar esto se me hacia un poco complicado por que se tiene que elegir la forma en que se mostraran los grupos de la información y aun así todavía tengo unos errores que debo mejorar pero con practica se puede mejorar la forma en que se utiliza esta herramienta..."
ROSAS ALMENDRA JOSÉ DAVID  

CONSOLIDACION

 INTRODUCCION

En ocaciones se tienen varias hojas que contienen datos similares, por ejemplo cuando se desean rerportar las ventas de equipo en una empresa, o los gastos en una serie de sucursales y se desea hacer un consolidado, o sea un resumen de la informacion o de la combinacion de la informacion de todas las empresas o sucursales.

Para consolidar datos, se combinan los valores de varios rangos de datos.Por ejemplo, si se tiene una hoja de calculo de cifras de gastos para cada una de sus funciones regionales, puede utilizar una consolidacion para reunir estas en una hoja de calculo de gastos para toda la organizacion.

  

CONSOLIDAR DATOS

Para consolidar datos, se combinan los valores de varios rangos de datos. Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puede utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización.

LO QUE PERMITE

La Consolidación de los Datos a menudo permite  facilitar la edición y visualizacin de información, ya que  puede ser visto en forma global como una hoja de Cálculo maestra.

 

OPCIONES DE CONSOLIDACION

 Consolidar por posición: La consolidación de la posición por la función funciona mejor cuando los datos en las tablas separadas es coherente en su posición y disposición.

Consolidar por categoría:Consolidar por categorías permite a las hojas de cálculo con diseños un poco diferente, pero las etiquetas concordantes, que se consolidarán en una hoja de cálculo maestra. 

Consolidar por la fórmula:De consolidación por fórmula utiliza una referencia del 3-D, que es "una referencia a un rango que abarca dos o más hojas de cálculo en un libro,"para ejecutar la consolidación.

 

EJEMPLOS

El comando Consolidar lo hayamos (para el caso de MS Excel 2007) en la Cinta de opciones dentro de la ficha Datos en la sección Herramientas de datos tal como se ve en la imagen anexa:

En MS Excel 2003 lo encontramos en la Barra de herramientas/ Datos/ Consolidar.

Ahora veamos de que manera aplicar el comando mediante un ejercicio, supongamos que tenemos una empresa que tiene sede en tres países distintos (Venezuela, España y Argentina) y la suma total de las ventas se calculan a partir de los datos de tres archivos diferentes donde cada uno contiene la totalidad de las ventas por cada país, es en esta parte donde entra la utilidad del comando Consolidar pues mediante el mismo vamos a obtener los totales de la suma de las ventas de todos los países en un solo libro.

Teniendo en cuenta que son 3 libros cada uno llamado de la siguiente manera:

• Ventas_Venezuela
• Ventas_España
• Ventas_Argentina

Debemos crear un nuevo libro donde vamos a consolidar los totales de las ventas realizadas en esos países, ese archivo lo vamos a nombrar Ventas_Totales. Antes de realizar la consolidación de los valores debemos tener abiertos tanto los libros que contienen las ventas realizadas en los tres países como también el libro de consolidación. Preferiblemente el archivo donde vamos a consolidar la información (Ventas_Totales) debe tener el mismo encabezado que el resto de los archivos.

El primer paso que debemos realizar es posicionarnos en la celda a partir de donde queremos que se consoliden los datos en el libro Ventas_Totales para este caso seleccionamos la celda B2 ya que es donde inicia el rango que vamos a tomar en cuenta en el resto de los libros, lo vemos mediante la siguiente imagen:

En seguida  vamos a la Cinta de opciones y en la ficha Datos sección Herramientas de datos seleccionamos el comando Consolidar en donde se desplegara el siguiente cuadro de dialogo:

Explicamos algunos detalles que debemos conocer de las opciones disponibles en el cuadro de dialogo:

Por defecto en Función dentro de la lista desplegable aparece la función Suma para este caso se deja tal cual ya que se está buscando una sumatoria total de ventas realizadas en tres archivos no obstante adicionalmente disponemos de 10 funciones mas con las que podemos trabajar.

En el Campo Referencia es a partir de donde vamos a seleccionar el rango celdas de los otros libros que deseamos consolidar.

En Todas las referencias vamos a visualizar la totalidad de las referencias que tenga el libro.

El Botón Agregar permite agregar una referencia y el botón Eliminar permite suprimir una referencia seleccionada.

En Usar rótulos: Aparecen dos casillas de verificación en este cuadro de diálogo, indicativos de la inclusión de rótulos en la fila superior y/o en la columna izquierda, ya que se pueden dar los dos casos de manera simultánea. Al marcar estas casillas estamos informando al programa de que no deseamos que los datos incluidos en la fila superior y/o en la columna izquierda se tengan en consideración a la hora de realizar las operaciones correspondientes.

En Crear vínculos con los datos de origen: Es importante activar esta casilla si queremos que los datos originales queden vinculados con los datos consolidados, de manera que si en las hojas que contienen los datos de origen se realiza algún tipo de modificación, la hoja resumen actualizará los datos consolidados en la medida que sea necesario. También esta opción crea en la hoja consolidada un esquema que permite identificar el origen de cada uno de los datos consolidados, es decir, de dónde proviene ese dato de resumen en concreto.

Una vez revisados estos puntos procedemos a realizar la consolidación de los totales, con el cuadro de dialogo abierto en el campo Referencia vamos a seleccionar el rango de las celdas que deseamos consolidar del primer libro (Ventas_Venezuela) a partir de la celda B2 hasta la celda G4 tal como se ve en la imagen anexa:

Luego seleccionamos el botón Agregar dentro del cuadro de dialogo, muy bien este paso lo vamos a repetir exactamente pero haciendo referencia a los dos libros restantes (Ventas_España) y (Ventas_Argentina) quedando el campo Todas las referencias tal como lo mostrado en la siguiente imagen:

"

Las casillas Fila Superior, Columna izquierda y Crear vínculos con los datos de origen no la vamos a seleccionar para la consolidación de estos valores.

Por último hacemos clic en el botón Aceptar y los resultados se consolidarán de manera exitosa en el libro Ventas_Totales.

El comando Consolidar es de suma utilidad ya que si tenemos un gran número de libros y queremos consolidarlos todos en uno solo el mismo nos ofrece la posibilidad de hacer esta operación.

REFLEXION
 "esto es una buena opcion para obtener un resumen de la informacion en las hojas de calculo, esto nos evita estear haciendo muchas operaciones, esto es algo que no sabia hacer en excel por lo que tenia que estar haciendo cuentas y me demoraba mas tiempo en obtener el resultado aparte la informacion no la organizaba de una manera entendible lo que aveces la hacia confusa, ahora usando consolidacion de datos puedo ver de una mejor forma el resumen de la informacion en las hojas de excel..."

ROSAS ALMENDRA JOSE DAVID