SUBTOTALES

                                                 Introduccion

La función de subtotales en Excel, nos permite tener de manera casi inmediata filas con los totales de la información que tenemos en nuestra hoja de cálculo. Por ejemplo, suponiendo que tenemos una hoja con los gastos personal que hemos realizado podemos tener el subtotal de lo gastado por cada área de nuestro gasto. 

 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.

Los subtotales constituyen una manera rápida y sencilla de resumir datos en listado. No es necesario crear fórmulas. Excel crea la fórmula, inserta las filas correspondientes al subtotal y al total y efectúa un esquema de los datos, automáticamente. Así, los datos resultantes son fáciles de formatear, colocar en un gráfico e imprimir. En resumen, añade líneas de subtotal a columnas de la lista.

En Excel, los subtotales se utilizan cuando trabaja con listas de datos ordenadas. Sirven para realizar cálculos totales y parciales de dichas listas.

Realiza el siguiente ejemplo:

Empresa	País	Ingresos	Gastos		Beneficios
Los conquistadores	España	2554555		565533		1989022
Los conquistadores	Francia	2456895		65656		2391239
Los conquistadores	USA	6546588		565566		5981022
Americanino	USA	1234567		1561685		-327118
Americanino	México	7654321		758621		6895700
Americanino	España	8945612		5697874		3247738
Americanino	Francia	1223344		256487		966857
Jaybell	USA	5664879		455645		5209234
Jaybell	Chile	6546588		5897466		649122
Jaybell	Alemania	2456895		458976		1997919

Debe crear una lista, con los subtotales por empresas acumulando las ganancias debajo de la columna de Beneficios, de forma que pueda ver los totales parciales:

- Seleccione todo el rango de datos (A1:E11)

- Active la opción Subtotales del Menú Datos

- Active las opciones necesarias:

Para cada cambio en EMPRESA (con esto agrupara por empresas):

Usa función SUMA

Agregue subtotal a BENEFICIOS

Active la opción Aceptar.

Observe que han aparecido los subtotales bajo la columna de Beneficios agrupados por países. A la izquierda, aparecen unos signos que controlan el nivel de desglose del subtotal. Puede aumentar o disminuir el nivel del subtotal pulsando en los signos + y – o, bien, en los números que aparecen sobre estos signos.

Para añadir los promedios de gastos, aparte de los subtotales anteriores de los Beneficios, realiza las siguientes instrucciones:

• Vuelva a seleccionar la lista de datos y active la opción Subtotales del Menú Datos
• Cambia la opción Usar función y escoja la función Promedio.
• Active la casilla Agregar subtotal a GASTOS
• Desactive la casilla Reemplazar subtotales actuales (si no la desactiva, perdera los subtotales conseguidos).
•  Active la opción Aceptar.
•  

el funcionamiento de la función SUBTOTALES, muy útil cuando trabajamos con filtros, y necesitamos operar con algunos de sus campos.Si tenemos el siguiente ejemplo:

Aplicaremos una función SUBTOTALES al campo 'Precio compra'

=SUBTOTALES(9;$D$2:$D$11)

sabiendo que los argumentos de la función son

=SUBTOTALES(núm función; valor1, valor2, ...)

El argumento Número de función responde a la tabla:

Es importante recordar que la función SUBTOTALES pasa por alto las filas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm función que utilice; además esta función sólo evaluaría un resultado sobre filas ocultas y nunca sobre columnas ocultas. Los números de funciones del 101 al 111 únicamente funcionan cuando hablamos de listas.

Sabiendo esto, cobra sentido la función aplicada anteriormente para nuestro pequeño ejemplo, puesto que queríamos saber cuál era el sumatorio del campo 'Precio compra' cada vez que tuvieramos aplicado un filtro, es decir, filas ocultas. Al no estar trabajando con Listas tomamos como número de función el valor 9 (también funciona exactamente igual con el valor 109).

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

"Las funciones que maneja  Excel son muy útiles y eficaces dentro de las lista  podemos manejar la función subtotal  y por eso ahora facilita las tareas que realizamos día con día yo como estudiante estoy aprendiendo nuevas herramientas de trabajo que permiten una mayor comprensión  esto permite un mayor nivel de profesiónabilidad en las personas ya que son herramientas de trabajo nos ayudan a crear subtotales agrupándolos por grupos solo hay que saber cuales datos manejar y eso es fácil  y así es como trabaja los subtotales  si nos damos cuenta los subtotales se encuentran el las facturas de los negocios  por eso es de gran utilidad."

ivan uscanga cruz

CONSOLIDAR DATOS

                    INTRODUCCION

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.

Consolidar datos consiste en combinar los valores de varios rangos de datos en uno solo. Por ejemplo, si tienes una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puedes utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización; o si tienes las ventas de cada uno de los vendedores puedes agruparlas en una sola hoja para calcular el total de ventas de toda la compañía.

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

Con la función Consolidación del Menú Datos puedes:

• Reunir información de hasta 255 hojas de cálculo.
• Vincular los datos consolidados a los datos fuente, de tal forma que una vez modificados estos últimos, se alteren los resultados de la consolidación.
• Consolidar por posición o por categoría.
• Consolidar hojas de cálculo utilizando funciones como Promedio, Máx, Mín, Producto, Cuenta, Desvest, etc.
• Consolidar hojas de cálculo en libros abiertos o almacenados en disco.

1.  En primer lugar, examina los datos y decide si deseas consolidarlos por posición o por categoría.

Posición   Si vas a combinar datos que están en la misma celda en varios rangos, puedes consolidar por posición.

Categoría   Si tienes varios rangos con diseños diferentes y vas a combinar datos de filas o columnas que tengan rótulos (nombres de fila y/o columna) coincidentes, puedes consolidar por categoría.

2.  Configura los datos que va a consolidar.

• Asegúrate de que cada rango de datos está en formato de lista: cada columna tiene un rótulo en la primera fila, contiene datos similares y no tiene filas o columnas en blanco.
• Coloca cada rango en una hoja de cálculo diferente. Ej: una hoja de cálculo diferente para cada vendedor.
• No pongas ningún dato en la hoja de cálculo donde vayas a colocar la consolidación.
• Si realizas la consolidación por posición, asegúrate de que cada rango tiene el mismo diseño.
• Si realizas la consolidación por categoría, asegúrate de que los rótulos de las columnas o filas que deseas combinar tienen idéntica ortografía y coincidencia de mayúsculas y minúsculas.
• Si deseas, puedes asignar un nombre a cada rango.

3.  Haz clic en la celda superior izquierda del área donde desees que aparezcan los datos consolidados.

4.  En el menú Datos, haz clic en Consolidar.  Aparece el siguiente cuadro:

5.  En el cuadro Función, haz clic en la función de resumen que deseas que utilice Microsoft Excel para consolidar los datos. Ej: sumar, contar o promediar.

6.  Haz clic en el cuadro Referencia, elige la etiqueta de hoja del primer rango que vas a consolidar, escribe el nombre que asignaste al rango o selecciona el rango y, a continuación, haz clic en Agregar. Repite este paso para cada rango.

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

8.  Activa 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.

En el ejemplo, el cuadro quedará más o menos así:

9.  Haz clic en aceptar.  En nuestro ejemplo:  la hoja de consolidación quedará así:

TIPOS DE CONSOLIDACION DE DATOS

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.

• Configure los datos que va a consolidar.

• 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.

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.

                                                          REFLEXION

"LA  consolidación de datos es una mezcla de datos con formulas y funciones los cuales proporcionan una ayuda muy eficaz al usuario permitiendo  la rapidez la cual el la hora de trabajo estemos preparados en forma correcta  hay varios tipos de consolidar los cuales puede ser CONSOLIDAR POR POSICION,CONSOLIDAR POR CATEGORIAS, Y CONSOLIDAR DATOS,  esto lleva a cabo un proceso el cual de manera fácil trabaja y hace  mas fácil nuestro trabajo ya sea de manera profesional o practica. Una de las o mas usada es la de CONSOLIDAR POR POSION así podrás poner los datos en las misma  celda en varias hojas y realizara  la  por nosotros ya sea suma , promedio"

IVAN USCANGA CRUZ