consolidacion y tablas dinamicas

CONSOLIDACION DE DATOS

La herramienta de Consolidar Datos permite combinar los valores de varios rangos de celdas. Por ejemplo, si tiene una hoja de cálculo con calificaciones de un mismo grupo en  diferentes  periodos  o  meses,  puede  utilizar  una  consolidación  para  reunir  estas calificaciones en una hoja de cálculo de promedio finales para todo el grupo.

 Excel permite consolidar datos de varios modos los cuales se describen a continuación.
CONSOLIDACIÓN

Esta función permite combinar valores de varios rangos de datos ubicados en diferentes hojas de cálculo.

La consolidación de datos es un proceso por el cual se analiza información obtenida de sistemas heredados o lícitamente intercambiada con otras entidades, para obtener una organización de datos legible e interpretable a los efectos de su uso o evaluación desde un punto de vista determinado.

En algunos casos el proceso se encuentra dirigido a la simple "traducción", es decir, a la recuperación de datos concretos y su formateo para luego importarlo en un sistema vigente.
En otros se busca conformar tablas reducidas con valores calculados a partir del agrupamiento de registros en cosideración de uno o más parámetros.

Si bien en general estos procesos brindan en principio un resultado estático, es decir, una foto de los datos existentes desde un punto de vista determinado; pueden desarrollarse múltiples interfaces de bajo costo que permitan diferentes criterios de agrupamiento o interpretación e incluso conformar nuevas tablas consolidadas a través de consultas automatizadas, llegando al punto de convertirse en interfaces de consulta totalmente dinámicas. 

La consolidación de datos implica también la revisión de coherencia e isomorfismos, la cual puede llevar a al descarte de algunos registros, los cuales deben ser tenidos en cuenta para definir estadísticamente la precisión de la muestra resultante.

Imagina que después de recopilar la información sobre las ventas de nuestros productos en diferentes países tenemos un libro de Excel donde cada hoja tiene la información de un mes en específico.

Necesito consolidar la información en una sola hoja para poder crear mi reporte, pero ¿cómo puedo hacerlo de una manera sencilla? Para consolidar datos en Excel tenemos el comando Consolidar que nos ayuda a obtener el resultado deseado de una manera fácil. Supongamos que la información de las 3 hojas de Excel es la siguiente.

Comando Consolidar en Excel

Para consolidar los datos crearé una nueva hoja y haré clic en el botón Consolidar que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.

Se mostrará el cuadro de diálogo Consolidar en donde se deberá especificar cada uno de los rangos de las diferentes hojas. Puedes hacer clic en el botón de selección de referencia para seleccionar los rangos fácilmente.

Una vez especificada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar cada una de las hojas hasta tener todas las referencias enlistadas. Finalmente debo marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de manera que los datos sean presentados adecuadamente.

Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la nueva hoja de Excel.

De esta manera la información que antes estaba en diferentes hojas de Excel ha sido consolidada correctamente en una sola hoja.

Consolidar datos en varias hojas de cálculo

Para resumir los resultados de hojas de cálculo independientes y elaborar informes con ellos, puede consolidar datos de cada una de ellas en una hoja de cálculo maestra. Las hojas de cálculo pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, une datos de modo que puede actualizar y agregar según sea necesario con mayor facilidad.
Por ejemplo, si tiene una hoja de cálculo de gastos para cada una de las oficinas regionales, puede usar la consolidación para reunir estas cifras en una hoja de cálculo maestra de gastos corporativos. Esta hoja de cálculo maestra también puede contener las ventas totales y los promedios, los niveles de inventario actuales y los productos más vendidos de toda la empresa.

Sugerencia: Si suele consolidar datos, puede serle útil basar las hojas de cálculo en una plantilla de hoja de cálculo que use un diseño coherente. Para obtener más información sobre las plantillas, consulte: Crear una plantilla. Este es también un momento ideal para configurar la plantilla con tablas de Excel.

Hay dos formas de consolidar datos: por categoría o por posición.
Consolidación por posición: Cuando los datos en las áreas de origen se organizan en el mismo orden y usan las mismas etiquetas. Use este método para consolidar datos de una serie de hojas de cálculo, como hojas de cálculo de presupuestos de departamento que se han creado a partir de la misma plantilla.
Consolidación por categoría: Cuando los datos en las áreas de origen no se organizan en el mismo orden pero usan las mismas etiquetas. Use este método para consolidar datos de una serie de hojas de cálculo que tienen diferentes diseños pero tienen las mismas etiquetas de datos.

·         Consolidar datos por categoría es similar a crear una tabla dinámica. En cambio, con una tabla dinámica, puede reorganizar fácilmente las categorías. Si quiere una consolidación más flexible por categoría, considere la posibilidad de crear una tabla dinámica en su lugar.

Nota: Los ejemplos de este artículo se crearon con Excel 2016, por lo que la vista puede diferir según qué versión use. En cambio, los pasos son los mismos.

Pasos de consolidación

1.    Si aún no lo ha hecho, en cada hoja de cálculo que contiene los datos que quiere consolidar, configure los datos del siguiente modo:

o    Asegúrese de que cada rango de datos está en formato de lista, de modo que cada columna tenga una etiqueta en la primera fila, contenga datos similares y no tenga filas o columnas en blanco.

o    Coloque cada rango en una hoja de cálculo independiente, pero no escriba nada en la hoja de cálculo maestra donde vaya a colocar la consolidación; Excel la rellenará.

o    Asegúrese de que cada rango tiene el mismo diseño.

2.    En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.

Nota: Para evitar sobrescribir datos existentes en la hoja de cálculo de destino que contiene los datos que está consolidando, asegúrese de dejar celdas suficientes a la derecha y debajo de esta celda para los datos consolidados.

3.    En la pestaña Datos, del grupo Herramientas de datos, haga clic en Consolidar.

4.    En el cuadro Función, haga clic en la función de resumen que quiere que use Excel para consolidar los datos. La función predeterminada es SUMA.

El siguiente es un ejemplo con tres rangos de hoja de cálculo seleccionados.

5.    Seleccionar los datos

o    Si la hoja de cálculo que contiene los datos que quiere consolidar está en otro libro, haga clic primero en Examinar para buscar ese libro y, después, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar. Excel introducirá la ruta del archivo en el cuadro Referencia seguida de un signo de exclamación y puede seguir seleccionando los datos.

Después, en el cuadro Referencia, haga clic en el botón Contraer diálogo para seleccionar los datos de la hoja de cálculo.

Haga clic en la hoja de cálculo que contiene los datos que quiere consolidar, seleccione los datos y después haga clic en el botón Expandir diálogo de la derecha para volver al cuadro de diálogo Consolidar.

6.    En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos para agregar todos los rangos que quiera.

7.    Actualizaciones automáticas frente a actualizaciones manuales: Si quiere que Excel actualice la tabla de consolidación automáticamente cuando cambie el origen de datos, active la casilla Crear vínculos con los datos de origen. Si está desactivada, puede actualizar la consolidación de forma manual.

Notas: 

o    No puede crear vínculos si las áreas de origen y destino están en la misma hoja.

o    Si necesita cambiar los rangos una vez los ha agregado, puede hacer clic en cada uno de ellos en el cuadro de diálogo Consolidar, actualizarlos cuando aparezcan en el cuadro Referencia y, después, hacer clic en Agregar. De este modo, se creará una nueva referencia de rango, por lo que tendrá que eliminar la anterior antes de volver a consolidar. Seleccione la referencia anterior y presione Suprimir.

8.    Haga clic en Aceptar y Excel generará la consolidación. No tendrá formato, tendrá que encargarse usted de él, pero solo tiene que hacerlo una vez, a menos que vuelva a ejecutar la consolidación.

o    Las etiquetas que no coincidan con las de las otras áreas de origen producirán filas o columnas independientes en la consolidación.

o    Asegúrese de que las categorías que no desea consolidar tienen rótulos únicos que aparecen solamente en un rango de origen.

Usar un informe de tabla dinámica para consolidar datos

·         Si los datos que se van a consolidar están en celdas diferentes de otras hojas de cálculo

Escriba una fórmula con referencias de celda a las otras hojas de cálculo, una por cada hoja de cálculo independiente. Por ejemplo, para consolidar datos de hojas de cálculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de cálculo maestra, tendría que escribir lo siguiente:

Sugerencia: Para introducir una referencia de celda, como Ventas!B4, en una fórmula sin escribir, escriba la fórmula hasta el punto en que necesite la referencia, haga clic en la pestaña de la hoja de cálculo y, después, haga clic en la celda; Excel rellena automáticamente el nombre de la hoja y la dirección de la celda. Tenga en cuenta que las fórmulas como esta pueden ser propensas a errores, porque es muy fácil seleccionar por accidente la celda incorrecta. También puede resultar difícil localizar un error una vez que se ha introducido la fórmula.

·         Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo

Escriba una fórmula con una referencia 3D que use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 desde Ventas hasta Marketing (ambos incluidos), tendría que escribir lo siguiente en la celda E5 de la hoja de cálculo maestra:

TABLAS DINAMICAS

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.

Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica.



Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

Crear una tabla dinámica

Si no tiene demasiada experiencia con tablas dinámicas o no sabe cómo empezar, las tablas dinámicas recomendadas son una buena elección. Cuando usa esta característica, Excel determina un diseño significativo haciendo coincidir los datos con las áreas más adecuadas de la tabla dinámica. Esto le ofrece un punto de partida para una experimentación adicional. Una vez creada la tabla dinámica recomendada, podrá explorar las distintas orientaciones y reorganizar los campos para conseguir resultados específicos. La característica de tablas dinámicas recomendadas se agregó en Excel 2013, por lo que si tiene una versión anterior, siga en lugar de ello las instrucciones indicadas a continuación para crear manualmente una tabla dinámica.






Tabla dinámica recomendada

Crear manualmente una tabla dinámica



Haga clic en una celda del rango de datos o de la tabla de origen.


Vaya a Insertar > Tablas > Tabla dinámica recomendada.

Excel analiza los datos y le presenta varias opciones, como en este ejemplo con los datos de gastos domésticos.

Seleccione la tabla dinámica que mejor le parezca y presione Aceptar. Excel creará una tabla dinámica en otra hoja y mostrará la lista Campos de tabla dinámica.




Haga clic en una celda del rango de datos o de la tabla de origen.


Vaya a Insertar > Tablas > Tabla dinámica

.

Si usa Excel para Mac 2011 y versiones anteriores, el botón Tabla dinámica se encuentra en el grupo Análisis de la pestaña Datos.

Excel mostrará el cuadro de diálogo Crear tabla dinámicacon el nombre de tabla o de rango seleccionado. En este caso, utilizamos una tabla denominada "tbl_HouseholdExpenses".

En la sección Elija dónde desea colocar el informe de tabla dinámica, seleccione Nueva hoja de cálculo u Hoja de cálculo existente. En el caso de Nueva hoja de cálculo, tendrá que seleccionar la hoja de cálculo y la celda donde quiera colocar la tabla dinámica.


Si quiere incluir varios orígenes de datos o de tabla en la tabla dinámica, active la casilla Agregar estos datos al Modelo de datos.


Haga clic en Aceptar. Excel creará una tabla dinámica en blanco y mostrará la lista Campos de tabla dinámica.



Valores de tabla dinámica


Resumir valores por

De forma predeterminada, los campos de tabla dinámica que se coloquen en el área Valores se mostrarán como SUMA. Si Excel interpreta los datos como texto, se mostrarán como CONTAR. Por eso es tan importante asegurarse de no mezclar tipos de datos en los campos de valor. Para cambiar el cálculo predeterminado, pulse primero en la flecha situada a la derecha del nombre de campo y seleccione después la opción Configuración de campo de valor.

Después, cambie el cálculo en la sección Resumir valores por. Tenga en cuenta que, cuando modifique el método de cálculo, Excel lo anexará automáticamente en la sección Nombre personalizado, como "Suma de Nombre de campo", pero puede cambiarlo. Si hace clic en el botón Formato de número, puede cambiar el formato de número de todo el campo.

Sugerencia: Puesto que al modificar el cálculo en la sección Resumir valores por cambiará el nombre del campo de tabla dinámica, es mejor no cambiar el nombre de los campos de tabla dinámica hasta que haya terminado de configurarla. Un truco consiste en usar Buscar y reemplazar (Ctrl+H) >Buscar > "Suma de" y luego Reemplazar por > dejar en blanco para reemplazar todo a la vez en lugar de tener que volver a escribirlo manualmente.


Mostrar valores como

En lugar de usar un cálculo para resumir los datos, también puede mostrarlos como porcentaje de un campo. En el siguiente ejemplo, hemos cambiado nuestros importes de gastos domésticos para que aparezcan como % del total general en lugar de la suma de los valores.

Cuando haya abierto el cuadro de diálogo Configuración de campo de valor, puede hacer las selecciones en la pestaña Mostrar valores como.

Muestre un valor como cálculo y como porcentaje.

Basta con arrastrar el elemento a la sección Valores dos veces y luego establecer las opciones Resumir valores por y Mostrar valores como para cada una de ellas.
Actualización de las tablas dinámicas

Si agrega nuevos datos al origen de datos de tabla dinámica, hay que actualizar las tablas dinámicas que se hayan integrado en ese origen de datos. Para actualizar una sola tabla dinámica, puede hacer clic con el botón derecho en algún lugar del rango de tabla dinámica y luego seleccionar Actualizar. Si tiene varias de las tablas dinámicas, seleccione primero una celda en cualquier tabla dinámica y luego, en la Cinta, vaya a Herramientas de tabla dinámica > Analizar > Datos > Haga clic en la flecha situada en el botón Actualizar y seleccione Actualizar todo.