Introduccion:
Como sabemos cada entrada era un tema distinto pues este es el ultima entrada sobre Excel espero que les ahiga gustado y les alla echo util la informacion yo digo que si pero les vuelvo a repetir practiquen mucho para haci ya sean expertos con las herramientas que te presta Excel.
En esta entrada veremos lo que es Consolidacion de datos esta es otro herrmaienta de Excel qvaue en simple contexto es para combinar datos de varias hojas con una formula.
Definicion:
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.
La consolidación de datos, es una operación que permite resumir datos procedentes de varias hojas del mismo libro o de libros diferentes, en una hoja que recoge a modo de resumen, la suma, promedio, desviación típica, producto u otra operación aplicable sobre rangos de celdas iguales en dimensión ubicados normalmente en otras hojas del mismo libro.
Los resultados de una operación de consolidación, tienen la posibilidad de quedar vinculados a los datos originales o datos fuente, es decir, que si los datos de las hojas a consolidar (en la imagen siguiente Hoja1, Hoja2 y Hoja3) se modifican, el resultado consolidado también se actualizará.
De cada una de las hojas que se utilizan en la consolidación podemos elegir una sola celda, o bien un rango de celdas.
Por ejemplo, podemos tener en un rango de celdas dentro de una hoja, las ventas de Enero, en otra hoja las de Febrero... y así sucesivamente para todos los meses de año. En este caso, podríamos consolidar las ventas de esos doce meses en una hoja resumen. La idea de consolidar es aplicable a otras situaciones de trabajo tales como ventas de vendedores en diferentes hojas, ventas por familias de productos, notas de alumnos por asignaturas, resúmenes comparados ventas-objetivos por delegaciones, por departamentos...
Además, la presentación de esas ventas globales, puede realizarse utilizando distintas operaciones que serán aplicadas sobre los datos a consolidar. Esto es, los datos se pueden sumar obteniendo las ventas globales, o bien promediando y obteniendo las ventas medias anuales...
Posteriormente, también podremos generar gráficos explicativos a partir de esa información de resumen, es decir, de esta información consolidada.
También es posible consolidar hojas de diferentes libros. En este caso, los libros intervinientes deberán estar abiertos en el momento de realizar la consolidación.
Por ejemplo, podemos encontrarnos con varios libros que recojan las ventas de cada uno de los vendedores de una empresa, María.xlsx y Miguel.xlsx, y deseamos consolidarlos en un libro nuevo llamado ventasglobales.xlsx.
Las consolidaciones más habituales son las que se realizan sobre rangos de datos ubicados geográficamente en las mismas celdas de las diferentes hojas, es decir cuando se ha mantenido una estructura homogénea en la disposición de datos. En este caso la consolidación se aplicará por posición.
Utilizar fórmulas 3D
Si utiliza referencias 3D (referencia 3D: referencia a un rango que ocupa dos o más hojas de cálculo en un libro.) en las fórmulas no habrá restricciones en el diseño de los rangos de datos independientes. Puede cambiar la consolidación del modo que necesite. La consolidación se actualiza automáticamente cuando cambian los datos de los rangos de origen.
Utilizar fórmulas para combinar datos
En el siguiente ejemplo, la fórmula de la celda A2 agrega tres números de tres posiciones diferentes en tres hojas de cálculo distintas.
Agregar datos a una consolidación con referencias 3D
Cuando todas las hojas de cálculo de origen tengan el mismo diseño, podrá utilizar un rango de nombres de hojas en fórmulas 3D. Para agregar otra hoja de cálculo a la consolidación, sólo tendrá que mover la hoja al rango al que haga referencia la fórmula.
Consolidar por posición
Consolide los datos por posición cuando los datos de todas las áreas de origen se organicen en un orden y una ubicación idénticos; por ejemplo, si cuenta con datos de una serie de hojas de cálculo creadas desde la misma plantilla, puede consolidar los datos por posición.
Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación. También puede actualizar la consolidación manualmente, lo que le permitirá cambiar las celdas y los rangos incluidos en ella.
Consolidar por categorías
Consolide los datos por categorías si desea resumir un conjunto de hojas de cálculo que tienen los mismos rótulos de filas y columnas pero organizan los datos de forma diferente. Este método combina los datos que tienen rótulos coincidentes en cada hoja de cálculo.
Puede configurar la consolidación de modo que se actualice automáticamente cuando cambien los datos de origen, pero no podrá modificar qué celdas y rangos se incluyen en la consolidación. Como alternativa, puede actualizar la consolidación manualmente, lo que le permitirá cambiar las celdas y los rangos incluidos en ella.
Cómo consolidar datos
Puede crear un informe de tabla dinámica (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes incluidos los externos a Excel.) a partir de varios rangos de consolidación. Este método es similar al de consolidación por categorías, pero ofrece una mayor flexibilidad para reorganizar las categorías.
Examine los datos y decida si desea consolidarlos utilizando referencias 3D (referencia 3D: referencia a un rango que ocupa dos o más hojas de cálculo en un libro.) en fórmulas, por posición o por categoría.
Posición Si va a combinar datos que están en la misma celda en varios rangos, puede consolidar por posición.
Categoría: Si tiene varios rangos con diseños diferentes y va a combinar datos de filas o columnas que tengan rótulos coincidentes, puede consolidar por categoría.
Consolidar los datos con referencias 3D o fórmulas
- En la hoja de cálculo de consolidación, copie o introduzca los rótulos que desee para los datos de consolidación.
- Haga clic en la celda en que desea incluir los datos de consolidación.
- Introduzca una fórmula que incluya referencias a las celdas de origen en cada hoja de cálculo que contenga los datos que se desea consolidar. Por ejemplo, para combinar los datos de la celda B3 de las hojas de cálculo Hoja 2 a Hoja 7 ambas inclusive, podría escribir =SUMA(Hoja2:Hoja7!B3). Si los datos que va a consolidar están en celdas diferentes de hojas de cálculo distintas, escriba una fórmula como esta: =SUMA(Hoja3!B4, Hoja4!A7, Hoja5!C5). Para introducir una referencia como Hoja3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesita la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.
Consolidación por posición.
Hablamos de consolidación por posición, cuando las hojas a consolidar poseen la misma estructura en dimensiones planteamiento y organización de los datos, y además, en la hoja consolidada queremos que los datos resumen, tengan la misma estructura.
Veamos este tipo de consolidación con un ejemplo.
Supongamos que disponemos de un libro llamado Primertrimestre.xlsx en el que se desean consolidar los datos de las ventas de varios vendedores de una empresa tanto en venta directa como indirecta de los tres primeros meses del año. Los datos de estos tres meses serán los siguientes:
- Las ventas de Febrero:
- Las ventas de Marzo:
- El cuadro resumen tendrán la misma estructura que los que recogen los datos mensuales:
Una vez que contamos con estos datos, para realizar una consolidación es preciso seguir los siguientes pasos:
- Situar el cursor dentro de la hoja en la que se desea el resultado de la consolidación, en nuestro caso la hoja Resumen, y de la celda a partir de la cual, se resolverán y quedarán los cálculos de la consolidación, en nuestro caso la celda B2.
- Elegir la opción Consolidar
, que aparece en el grupo de opciones Herramientas de datos dentro de la ficha Datos. De esa forma entraremos en el cuadro de diálogo Consolidar.
- En el panel de Función elegiremos la función que deseamos realizar con los datos a consolidar, en nuestro caso elegiremos la función Suma.
- Dentro del cuadro de texto Referencia, indicamos la celda o rango de celdas de las otras hojas o libros cuyos datos se desean resumir en la hoja consolidada. El botón de contraer cuadro de diálogo
, nos permite seleccionar de manera más cómoda cada uno de los rangos de celdas a seleccionar, una vez señalado pulsamos el botón Agregar, con lo cual ese rango se añade al cuadro donde se van a incluir las referencias de todos los rangos de celdas a consolidar.
En el caso de que algunos de los rangos de celdas a consolidar no se encuentre en las hojas del libro actual, utilizaremos el botón Examinar, que nos permite realizar una búsqueda del archivo en el que se encuentran los datos que deseamos consolidar.
- Otras opciones que debemos tener en cuenta a la hora de realizar una consolidación por posición son:
- El uso de los 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 nuestro ejemplo no las marcamos, ya que hemos seleccionado los rangos de datos sin incluir las celdas correspondientes a los rótulos.
- 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.
- Esquema plegado:
NOTA: También es posible consolidar por categorías, de modo que aunque los rangos a consolidar no sean iguales en el orden en el que están dispuestos en sus hojas originales, sus rótulos sí que coinciden en cada hoja, son los mismos rótulos como en el ejemplo anterior, de manera que seleccionando esas celdas de rótulos en las hojas a consolidar y marcando las casillas de Fila superior y Columna izquierda en el cuadro de diálogo de Consolidar, los datos serán consolidados y operados según les corresponda al rótulo, no a la posición. Este tipo de consolidación es menos claro que la consolidación por posición porque no exige una igualdad en estructuras de datos, pero funciona de igual manera.
Tutorial de Consolidacion de datos:
Refleccion:
"Esta herramienta nos sirve para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de cálculo maestra con una formula determinada se puede hacer por ejemplo la suma que es las mas utilizada en todos los casos o se puede configurar a un modo determinado; de un u otra forma esta herramienta nos servira en un futuro y es "
Conclusion
