SUBTOTALES

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.

Si quiere desaparecer los subtotales, debe activar la opción Subtotales del Menú Datos, y seleccionar Quitar todos.

Nota: Para realizar subtotales de otro campo, por ejemplo, por países, deberá primero ordenar la tabla por esa columna.

Crear grupos dentro de sus datos, generalmente es hecho de manera que pueda tener subtotales u otros cálculos sobre los registros, justo en ese grupo.

Excel provee ayuda, permitiéndole usar sus características de Ordenar y después usando su comando Subtotal. Con esos comandos puede ordenar una lista de ventas y después crear automáticamente los subtotales. Estas pueden ser funciones agregadas además de Sumar, Promedio, Max o Min.

Podría querer ver las ventas de cada vendedor, o en un área geográfica, o para cada producto vendido, o durante un solo día, semana, o mes, en los que haya tenido lugar la venta.

El ejemplo de abajo fue ordenado por Vendedor y por fecha y después sacados los subtotales de los Montos de Ventas.

Datos originales

Ordenados por Vendedor, y después por Fecha
Subtotales de Cantidades de Ventas

"REFLEXION"

CONSOLIDACION

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. Las hojas pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o específica.

Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de las oficinas regionales, podría utilizar una consolidación para resumir estas cifras en una hoja de cálculo de gastos corporativa. Esta hoja de cálculo maestra podría contener totales de ventas y promedios, niveles de inventario actuales y los productos más vendidos de toda la organización.

Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la ficha Datos.

¿Qué desea hacer?

Si desea...	Entonces…
Organizar los datos de todas las hojas de cálculo en orden y ubicación idénticos	Consolide por posición
Organizar los datos de forma diferente en las hojas de cálculo independientes pero utilizar los mismos rótulos de fila y de columna para que la hoja de cálculo maestra pueda hacer coincidir los datos	Consolide por categorías
Utilizar fórmulas con referencias de celdas o referencias 3D (referencia 3D: referencia a un rango que ocupa dos o más hojas de cálculo en un libro.) a otras hojas de cálculo que esté combinando porque no tiene una posición o categoría coherente en la que basarse	Consolide por fórmula
Usar un informe de tabla dinámica en lugar de una consolidación	Usar un informe de tabla dinámica para consolidar datos

Consolide por posición

1. Configure los datos que se van a consolidar en cada una de las hojas de cálculo independientes.

Cómo configurar los datos

• 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.
• Asegúrese de que cada rango tiene el mismo diseño.
• Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre.

2. Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados en la hoja de cálculo maestra.

 Nota    Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

3. En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.

4. 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 Office Excel para consolidar los datos.
5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.
7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:
• Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.

 Importante   Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

• Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

8. Deje en blanco las casillas de Usar rótulos en. 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.

Consolide por categorías

1. Configure los datos que se van a consolidar en cada una de las hojas de cálculo independientes.

Cómo configurar los datos

• 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.
• Asegúrese de especificar rótulos de categoría exactamente iguales, incluidas mayúsculas y minúsculas, en todas las áreas de origen. Por ejemplo, los rótulos Prom. anual y Promedio anual son diferentes y no se consolidarán.
• Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre.

2. Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados en la hoja de cálculo maestra.

 Nota    Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

3. En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.

4. 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 Excel para consolidar los datos.
5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.
7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:
• Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.

 Importante   Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

• Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

8. Active las casillas de verificación de Usar rótulos en que indican donde se encuentran los rótulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas.

 Notas 

• Los rótulos que no coincidan con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.
• Asegúrese de que las categorías que no desee consolidar tienen rótulos únicos que aparecen solamente en un rango de originalidad.

Consolide por fórmula

1. En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que desee para los datos de consolidación.
2. Haga clic en la celda en que desea incluir los datos de consolidación.
3. Escriba una fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de cálculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes:

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 especificar una referencia de celda como Ventas3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.

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 utilice una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta Marketing inclusive, en la celda A2 de la hoja de cálculo maestra tendría que escribir lo siguiente:

 Nota   Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.

Usar un informe de tabla dinámica para 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.) para rangos de consolidación múltiples. Este método es similar a consolidar por categoría, pero ofrece más flexibilidad para reorganizar las categorías. Para obtener más información, vea el tema sobre cómo consolidar hojas de cálculo múltiples en un informe de tabla dinámica.

"REFLEXION"