QUE ES CONSOLIDAR

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.

Volver al principio

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

Volver al principio

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.

Volver al principio

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 d

MACROS

Primeramente debemos de saber que es una Macro y a continuación se explica el termino:
Una Macro son una serie de pasos que se almacenan y se pueden activar con alguna tecla de control y una letra. Por ejemplo, todos los días empleo frecuentemente en mis celdas los mismos pasos: Tamaño de Fuente(Tamaño de la letra), Negrita, Fuente(Tipo de letra) y Color de Fuente(Color de Letra), para no estar repitiendo estos pasos los puedo almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se ejecutaran las veces que yo lo desee. A continuación te muestro como grabar una macro y ejecutarla:

Trasládate a la celda A1 y escribe tu Nombre. Por ejemplo, Ramón y presiona Enter Regrésate a la celda A1, porque cuando diste Enter bajo de celda o cambio el rumbo. Da clic en el Menú Ver, seguido por la Opción Barra de Herramientas y elija Visual Basic. Se activara la barra de herramientas Visual Basic.

Da clic en el botón Guardar Macro, el que tiene la ruedita Azul.

Windows activa el cuadro de dialogo Grabar Macro, el cual permitirá darle el nombre a la macro y cual será el método abreviado para ejecutarla. El método Abreviado se refiere con que letra se va activar la macro, obviamente se activara con la tecla Control y la letra que usted quiera, de preferencia en minúscula, porque si activa las mayúsculas la macro se activara presionando la tecla Shift + Control + la letra que usted indico.

Donde dice Nombre de la macro ya aparece el nombre que llevara la macro en este caso Macro1. si desea cambiar el nombre escriba uno nuevo, pero yo le recomiendo que así lo deje. En la opción Método Abreviado aparece que se activara con la tecla Control(CTRL) + la letra que usted indica, de clic en el cuadrito y ponga una letra, por ejemplo ponga la letra a (en minúsculas). La macro se activara cuando este lista con la tecla Control + a De clic en el Botón Aceptar.

Windows empezara a grabar todos los pasos en la Macro1.y el botón de la ruedita azul cambiara de forma ahora será un cuadrito Azul, se llamara Detener grabación. Lo utilizaremos cuando terminemos de indicarle los pasos para detener la grabación. Cambie el Tipo de Letra en el Botón Fuente de la barra de herramientas Formato Cambie el tamaño de la letra en el Botón Tamaño de Fuente de la barra de herramientas Formato Presione el Botón Negrita de la barra de herramientas Formato Cambie el color de la letra en el Botón Color de Fuente de la barra de herramientas Formato.

Recuerde que todos estos pasos están siendo almacenados en la macro que estamos grabando y también recuerde que estos pasos se están efectuando en la celda A1.

Presione el Botón Detener Grabación de la barra de Herramientas de Visual Basic. El que tiene el cuadrito azul presionado.

Listo Excel guardo los pasos en la Macro1 que se activara presionado la tecla

- Control + a
- Escribe otro nombre en la celda C1

- presiona Enter

- después regresa a la celda C1. Presiona la tecla Control + a.

Windows efectuara todos los pasos grabados sobre la celda C1, esto quiere decir que el nombre que esta en C1 tendrá las características del que esta en A1. Tipo de letra, tamaño, negrita y el color que indicaste al grabar la macro.

Nota . Cada vez que presiones Control + a Excel ejecutara la macro y efectuara los pasos en la celda que te encuentres. Puedes grabar todas las macros que desees.

Ahora te recomiendo que domines estos pasos antes de pasar a la siguiente fase. Trata de crear macros que almacenen pasos como estos, recuerda los pasos los vas a indicar tu, que no se te olvide detener la grabación después de que indicaste los pasos, repite este ejercicio las veces que sea necesario para aprendértelo bien.

FUNCION BUSCAR "V"

Presentaremos la función BUSCARV e veremos la importancia de las referencias relativas y absolutas. Usted es responsable del pago del sueldo en una agencia de ventas.

El sueldo se establece en base a la categoría a la que pertenece cada vendedor. Todos los meses usted recibe los datos de la tabla de la derecha y debe obtener el sueldo de cada vendedor en base a los datos de ella. Para lograrlo construye una tabla como la de la izquierda. Calcule con una única función el sueldo de cada empleado.Resolución:

Emplearemos la función BUSCARV que buscará en forma vertical (V) un valor determinado por usted (puede indicarse una celda) en una columna indicada. Su sintaxis general es del siguiente tipo:

BUSCAR V(VALOR; RANGOBUSQUEDA; COLUMNA Nº; Ord)

VALOR: ¿El valor de que celda debo encontrar?

RANGO BUSQUEDA: ¿Dónde lo tengo que buscar? ¿En que matriz?

COLUMNA Nº: ¿En que columna de esa matriz?En nuestro ejemplo la solución para el primer vendedor podría ser:=BUSCAR V(B2;E1:F6;2), Sin embargo al estirar o copiar esa fórmula en las celdas del resto de los vendedores los valores no serán correctos.

Esto es porque E1 se transformará en E2 al copiar en la celda B3, y F6 en F7.

De la misma forma ocurrirá en el resto. La solución al problema sería que la sintaxis de la función en la celda del sueldo del primer vendedor fuera algo como esto: =BUSCAR V(B2;E$1:F$6;2)Note que se han colocado signos $ de forma que al estirar (copiar) a las otras celdas no cambie el número de la fila de E1 a E2, ni de F6 a F7.

FUNCION SI

La función SI sirve para cuando requerimos evaluar una condición y dependiendo de si se cumple o no tener uno u otro resultado, es decir, si se cumple la condición tendríamos un resultado y si no se cumple el resultado sería otro.
Esta función requiere de tres (3) argumentos:

Prueba_Lógica: En la expresión que queremos evaluar.

Valor_si_verdadero: Como su nombre lo indica, es el resultado si la prueba lógica resulta verdadera

Valor_si_falso: Indica es el resultado si la prueba lógica resulta falsa.

Ejemplo de operación: Si el numero que tenemos en la columna A es mayor que 5 la columna B debe decir “MAYOR”, de lo contrarioen B debe decir “NO ES MAYOR”
La función quedaría de la siguiente forma:

=SI(A1>5,"MAYOR","NO ES MAYOR")

Posteriormente la función se debe repetir para cada valor que tengamos en “A”

Finalmente tenemos el resultado.

ESTILOS

Para aplicar varios formatos en un solo paso y asegurarse de que las celdas presentan un formato coherente, se puede usar un estilo de celda. Los estilos de celda se definen como conjuntos definidos de características de formato, como fuentes (fuente: diseño gráfico aplicado a todos los números, símbolos y caracteres alfabéticos. También se denomina tipo de letra. Arial y Courier New son ejemplos de fuentes. Las fuentes suelen venir en diferentes tamaños, como 10 puntos, y varios estilos, como negrita.) y tamaños de fuente, formatos de número, bordes de celda y sombreado de celda. Para evitar que otras personas puedan realizar cambios en celdas específicas, también se puede usar un estilo de celda que bloquee las celdas.

Microsoft Office Excel ofrece varios estilos de celda integrados que se pueden aplicar o modificar. Asimismo, se puede modificar o duplicar un estilo de celda para crear uno propio personalizado.
Importante Los estilos de celda se basan en el tema de documento que se haya aplicado a todo el libro. Al cambiar a otro tema de documento, los estilos de celda se actualizan para que coincidan con el tema del nuevo documento

FILTROS

Los filtros Excel facilitan la busqueda de un subconjunto de datos dentro de un rango para poder trabajar con el mismo. Este subconjunto de datos o rango filtrado muestra las filas que cumplen el criterio que se especifique para una columna.
Al utilizar un filtro este no reorganiza el resultado mostrado, pero sí permite aplicar formato, representar en gráficos e imprimir dicho rango. El filtrado oculta temporalmente las filas que no se desean mostrar. Excel proporciona dos formas de filtrar celdas de datos:
Autofiltro, Filtro avanzado.

Autofiltro

Ayuda a administrar fácilmente hojas de cálculo de gran tamaño, que requiran de filtros con criterios simples.

Filtro avanzado

Utilizado para realizar filtros con criterios más complejos. Se diferencia del Autofiltro al tener que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre el rango.

CREACION TABLAS DINAMICAS

Son tablas interactivas que permiten resumir y analizar los datos procedentes de listas y tablas existentes.

Una tabla dinámica permite modificar el aspecto de una lista de elementos con mayor facilidad, de manera cómoda y resumida. Además, se puede modificar su aspecto y cambiar de lugar los campos.

Para crear tablas dinámicas, utilice el Asistente para tablas dinámicas del menú Datos y siga los pasos para Lista o base de datos de Microsoft Excel.

Una tabla dinámica nos permite modificar el aspecto de una lista de elementos de una forma más fácil, cómoda y resumida. Además, podemos modificar su aspecto y mover campos de lugar.

Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y posteriormente acceder a Datos – Asistente para tablas dinámicas.
- Para comenzar a practicar, crea la siguiente tabla de datos:
- Selecciona toda la tabla y accede a Datos – Asistente para tablas dinámicas.
En primer lugar aparece una pantalla que representa el primer paso en el asistente para tablas dinámicas. Aceptaremos la tabla que hay en pantalla.
- Pulsa en Siguiente.
- Acepta el rango pulsando en Siguiente.

En el siguiente paso, Excel nos muestra la pantalla de diseño de la tabla. Los campos del origen de los datos están situados en la parte derecha del cuadro de diálogo. Aquí veremos la estructura final que tendrá la tabla. Lo que hay que hacer es "arrastrar" los campos de la derecha hacia la posición deseada en el interior de la tabla.
- Arrastra los campos de la parte derecha según se ve en la ilustración:

- Pulsa en Siguiente.

Como último paso, Excel nos propone crear la tabla en la misma hoja de trabajo a partir de una celda determinada, o bien en una hoja completamente nueva (opción elegida por defecto).

- Asegúrate de que está activada esta última opción y pulsa en Terminar.

Observa el resultado de la tabla dinámica. Hemos diseñado la estructura para que nos muestre los productos en su parte izquierda, los meses en columnas, y además, el precio de cada producto en la intersección de la columna.

Observa también que se han calculado los totales por productos y por meses.
Si modificamos algún dato de la tabla original, podemos actualizar la tabla dinámica desde la opción Datos – Actualizar datos siempre que el cursor esté en el interior de la tabla dinámica.
Al actualizar una tabla, Excel compara los datos originales. Pero si se han añadido nuevas filas, tendremos que indicar el nuevo rango accediendo al paso 2 del Asistente. Esto podemos hacerlo accediendo nuevamente a Datos – Asistente para tablas dinámicas y volviendo atrás un paso.
Es posible que al terminar de diseñar la tabla dinámica nos interese ocultar algún subtotal calculado. Si es así, debemos pulsar doble click en el campo gris que representa el nombre de algún campo, y en el cuadro de diálogo que aparece, elegir la opción Ninguno. Desde este mismo cuadro podemos también cambiar el tipo de cálculo.

Es posible también mover los campos de sitio simplemente arrastrando su botón gris hacia otra posición. Por ejemplo, puede ser que queramos ver la tabla con la disposición de los campos al revés, es decir, los productos en columnas y los meses en filas.

Si no está al crear la tabla, podemos activar la visualización de la barra de herramientas para tablas dinámicas (Ver- Barras de herramientas – Tablas dinámicas).

Desde aquí podemos realizar operaciones de actualización, selección de campos, ocultar, resumir, agrupar, etc. Puedes practicar sin miedo los diferentes botones de la barra.