CONSOLIDACION DE 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.Excel permite consolidar datos de varios modos. El método más flexible es crear fórmulas que hagan referencia a las celdas de cada rango de datos que se vaya a combinar. Las fórmulas que hacen referencia a las celdas de varias hojas de cálculo se denominan fórmulas 3D.

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 datosEn 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 3DCuando 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 datosPuede 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.

Fórmulas: Puede utilizar referencias 3D en fórmulas para cualquier tipo o distribución de datos. Es el método recomendado.
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.

Siga uno de estos procedimientos

Consolidar los datos con referencias 3D o fórmulas

1. En la hoja de cálculo de consolidación, copie o introduzca los rótulos que desee para los datos de consolidación.
2. Haga clic en la celda en que desea incluir los datos de consolidación.
3. 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.

Consolidar por posición o categoríaConfigure los datos que va a consolidar. ¿Cómo?

Asegúrese de que cada rango de datos está en formato de lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.): cada columna tiene un rótulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.

• Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.

Si realiza la consolidación por posición, asegúrese de que cada rango tiene el mismo diseño. Si realiza la consolidación por categoría, asegúrese de que los rótulos de las columnas o filas que desea combinar tienen idéntica ortografía y coincidencia de mayúsculas y minúsculas.

• Asigne un nombre a cada rango: seleccione todo el rango, elija Nombre en el menú Insertar, haga clic en Definir y escriba un nombre para el rango.

Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados.

En el menú Datos, haga clic en Consolidar.

En el cuadro 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 desea que utilice Microsoft Excel para consolidar los datos.

Haga clic en el cuadro Referencia, elija la etiqueta de hoja del primer rango que va a consolidar, escriba el nombre que asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.

Si desea actualizar la tabla de consolidación automáticamente cada vez que cambien los datos en cualquiera de los rangos de origen y no está seguro de si más tarde deseará incluir rangos diferentes o adicionales en la consolidación, active la casilla de verificación Crear vínculos con los datos de origen.

Si realiza la consolidación por posición, deje en blanco las casillas bajo Usar rótulos en. Microsoft Excel no copia los rótulos de fila o columna de los rangos de origen a la consolidación. Si desea rótulos para los datos consolidados, cópielos de uno de los rangos de origen o introdúzcalos manualmente.Si realiza la consolidación por categoría, active las casillas de verificación bajo Usar rótulos en que indican dónde están localizados los rótulos en los rangos de origen: en la fila superior, la columna izquierda o ambas. Los rótulos que no coincidan con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.

Consolidación de datos con Tablas Dinámicas (Pivot Tables).

domingo, abril 02, 2006

En anteriores entradas ya he tratado el tema de consolidación de datos en Excel:

1 – Consolidar Datos con Excel

2 – Consolidar datos con Subtotales

3 - Comparar listas con consolidación de datos

Otro método de consolidar datos en Excel es usando tablas dinámicas (pivot tables).

Consideremos el siguiente ejemplo (para descargar el archivo con el ejemplo apretar aquí). Tenemos un cuaderno Excel con una hoja por cada agente de ventas. En cada una de estas hojas anotamos las ventas por mes y por producto de cada agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una sola hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:

1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el menú Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre elegimos la opción "rangos de consolidación múltiples"

2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos. Para nuestro ejemplo elegimos "crear un solo campo de página"

3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que contienen los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1 y agente2) y apretando el botón "agregar"

4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida los datos)- En nuestro ejemplo la ubicamos en la celda A6

Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página 1" y elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

Dado que los encabezamientos de las columnas tienen un formato de fecha, podemos agrupar los datos en trimestres en forma automática. Para hacer esto damos los siguientes pasos:

1 – Hacemos clic con el botón derecho del mouse en el encabezamiento del campo "columna", y apretamos "agrupar"

2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).

3 – Apretamos "aceptar" y la tabla se convierte en un reporte trimestral

 

     

 

 

 

 

 

 

 

 REFLEXION :

la consolidasion de datos  nos ayuda  a  poder poner en orden  listas costos etc  por ejemplo si tenemos  un lista de  vendedores y productros esto nos ayuda aconoser  cuantos productos hay o llevar acabo  la rcopilasion presisa de cada persona esto es muy bueno ya que asi podemos tener un control presiso  de  cada mobiento o paso k  sede  esto o  para conocer el total  lo aremos la opsion auto suma  ..

 

SUBTOTALES EN EXCEL

Subtotales

Microsoft Excel puede calcular automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal.

Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Después pueden calcularse los subtotales de cualquier columna que contenga números.

Si los datos no están organizados en una lista, o si sólo necesita un total, puede utilizar Autosuma en lugar de subtotales automáticos.

Cómo 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.

Totales generales

Los valores del total general se obtienen a partir de los datos de detalle y no de los valores de las filas de subtotales. Por ejemplo, si se utiliza la función de resumen Promedio, la fila Total general mostrará el promedio de todas las filas de detalle de la lista y no el promedio de los valores de las filas de subtotales.

Actualización automática: Excel actualizará automáticamente los subtotales y el total general cuando se modifiquen los datos de detalle.

Subtotales anidados

Puede insertar subtotales de grupos más pequeños en los grupos de subtotales existentes. En el ejemplo a continuación, los subtotales de cada deporte están en una lista que ya tiene subtotales para cada región.

Subtotales exteriores

Antes de insertar los subtotales anidados, compruebe que ha ordenado la lista por todas las columnas para las que desea los valores subtotales de manera que las filas cuyos subtotales desea estén agrupadas.

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 subtotales desee calcular. En el ejemplo anterior, haría clic 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.

Nota  Puede utilizar de nuevo el comando Subtotales para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales

Sugerencia

Para mostrar un resumen solamente de los subtotales y de los totales generales, haga clic en los símbolos de esquema que aparecen junto a los números de fila. Utilice los símbolos y par a mostrar u ocultar las filas de detalle de los subtotales individuales.

Insertar subtotales anidados

1. Subtotales exteriores
2. Subtotales anidados

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. Ordene el rango por varias columnas, ordenando primero por la columna de subtotales exteriores, después por la siguiente columna interior de los subtotales anidados, etc. En el ejemplo anterior, ordenaría el rango primero por la columna Región y después por la columna Deporte. ¿Cómo?
   Para obtener resultados óptimos, el rango que se ordene deberá tener rótulos de columna.
   
   1. Haga clic en el rango que desee ordenar.
   2. En el menú Datos, haga clic en Ordenar.
   3. En los cuadros Ordenar por y Luego por, haga clic en las columnas que desee ordenar.
   4. Seleccione otras opciones de ordenación que desee y, a continuación, haga clic en Aceptar.
3. Inserte los subtotales exteriores. ¿Cómo?
   
   1. En el menú Datos, haga clic en Subtotales.
   2. En el cuadro Para cada cambio en, haga clic en la columna de los subtotales exteriores. En el ejemplo anterior, haría clic en Región.
   3. 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.
   4. En el cuadro Agregar subtotal a, active la casilla de verificación correspondiente a cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, esa columna sería Ventas.
   5. 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.
   6. 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.
4. Inserte los subtotales anidados. ¿Cómo?
   
   1. En el menú Datos, haga clic en Subtotales.
   2. En el cuadro Para cada cambio en, haga clic en la columna de subtotales anidados. En el ejemplo anterior, esa columna sería Deporte.
   3. Seleccione 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.) y otras opciones.
   4. Desactive la casilla de verificación Reemplazar subtotales actuales.
5. Repita el paso anterior para más subtotales anidados, empezando desde los más exteriores.

Insertar una fila total en una lista

1. Asegúrese de que esté activada la lista (lista: serie de filas que contienen datos relacionados o serie de filas que designa para que funcionen como hojas de datos mediante el comando Crear lista.) seleccionando una celda de la lista.
2. Haga clic en Alternar fila Total en la barra de herramientas Lista.

REFLEXION: excel puede  crear  listas  o  graficas de los suptotales   esto quiere decir que los suptotales  son  cantidades  que  nos ayudan atener  un costo de algua cantidad eso para el caso del total  y para el suptotal es para tener  un aprox de la cantidad sin tomar en cuenta  otros factores  del total  es muy interesante   ya que  nos ayuda atener  un control aserca de nustros movimientos .