CE.CA.T.I. No. 89 Celaya, Gto. (Secreatría de Educación Pública)

Agrupamiento de datos Esquemas, vistas y tablas dinámicas, consolidación de datos

Un esquema podríamos definirlo como un resumen preciso que refleja los conceptos más importantes o de mayor trascendencia del documento esquematizado.

Así pues, un esquema puede ser perfectamente un índice de un libro, donde vemos todos los puntos tratados en el libro, también podemos ver como ejemplo de esquema el índice de este curso, el cual contiene los puntos más importantes que se tratan en él y además está estructurado por niveles de profundización sobre un tema en concreto, vamos desplegando el esquema de los puntos contenidos en el tema.

Antes de ponernos a crear un esquema debemos tener en cuenta algunos aspectos.

- Debemos asegurarnos de que los datos sean apropiados para crear un esquema.

Los datos apropiados para crear un esquema debe tener una jerarquía o disponer de una estructura por niveles, por ejemplo si tenemos datos sobre las precipitaciones ocurridas a lo largo del año con las precipitaciones mensuales de todas las provincias, estos datos son buenos candidatos a formar un esquema.

Pero si únicamente tenemos datos sobre los gastos efectuados en una compra, con una estructura compuesta por Producto---Precio, no disponemos de niveles suficientes para hacer un esquema.

- En una hoja solo podemos incluir un esquema, para tener más de un esquema sobre los mismos datos, debemos copiar los datos a otra hoja.

Para crear esquemas automáticamente debemos preparar la hoja con un formato adecuado como veremos más adelante.

Existen dos formas de crear un esquema en Excel: Manual y Automática.

Creación automática de esquemas

La mejor opción para crear esquemas es que lo haga Excel automáticamente, puesto que tarda mucho menos tiempo que haciéndolo manualmente.

Existen unos requisitos previos para que Excel pueda crear automáticamente el esquema:

- Las filas sumario deben estar por encima o por debajo de los datos, nunca entremezclados.

- Las columnas sumario deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.

Si la disposición de los datos no se corresponde con estas características nos veremos obligados a definir el esquema manualmente.

En la imagen podemos ver el ejemplo de datos bien estructurados:

Podemos ver que existen subtotales en las celdas B6, C6, D6, B11, C11, D11, B20, C20, D20 y la columna E está llena de subtotales correspondientes a las filas donde se encuentran. En las celdas B21, C21, D21 y E21 son los totales de los subtotales.

En este ejemplo podemos hacer un esquema tanto de filas como de columnas, puesto que se ha organizado los subtotales de cada comunidad autónoma (filas) y se ha calculado los subtotales de cada trimestre (columnas),  es necesario hacer una aclaración al final de las ciudades se encuentra al estado que corresponde al contrario de lo usual de poner el estado al inicio, esto es para permitir tener un resumen de cada uno de ellos. 

Por tanto, como ya tenemos los datos vamos a realizar el esquema.

Para ello nos situamos sobre una celda cualquiera y seleccionamos la opción Autoesquema del menú Agrupar que se encuentra en la pestaña Datos.

Automáticamente, Excel nos genera los niveles del esquema como podemos ver a continuación:

En la imagen anterior podemos ver que ahora se aprecian unas líneas en la zona izquierda de las celdas y otra línea sobre las columnas de la tabla.

Además, vemos en la esquina superior izquierda unos números que nos indican cuántos niveles tiene el esquema.

Por columnas podemos ver que existen dos niveles:

- La tabla desplegada por completo

- y la tabla con los datos trimestrales.

Por filas tenemos tres niveles:

- La tabla desplegada completamente

- Por autonomías

- y solo por MÉXICO.

Para comprimir y expandir el esquema sólo tenemos que hacer clic en los símbolos - y + de cada nivel.

Ejercicio paso a paso.

Crear un esquema automáticamente. Objetivo.

Practicar cómo crear un esquema automáticamente en Excel.
 

1.- Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2.- Empieza un nuevo libro de trabajo. Desarrolla una hoja de trabajo a la mostrada en la imagen, el formato no es necesario, lo que nos interesa es el contenido de los datos.

Incluso si lo deseas puedes ampliar los datos.

Los valores resaltados en negrita son fórmulas de sumas, por ejemplo la celda B6 es =SUMA(B3:B5), así sucesivamente con el resto de las celdas sumario.

Las celdas sumarios de la columna E son sumatorias de las columnas B, C y D de su correspondiente fila, por ejemplo la celda E3 es =SUMA(B3:D3).

Las únicas que cambian un poco son las de totales de MEXICO, por ejemplo la celda B21 que se corresponde con las precipitaciones en México en el mes de enero es =Suma(B6,B11,B20) que son los subtotales correspondientes a las comunidades autónomas que disponemos.

3.- Selecciona la pestaña Datos.

4.- Elige la opción Agrupar.

Se abrirá otro submenú.

5.- Elige la opción Autoesquema.

6.- Pliega y despliega los niveles para ver cómo funcionan.

7.- Por último y guarda el libro en la carpeta Mis Documentos o Documentos en el disco duro, con el nombre de ejemplo_esquemas.xlsx y cierra el libro de trabajo..

Creación manual de esquemas

La segunda opción es la de crear el esquema manualmente.

Para crear un esquema manualmente debemos crear grupos de filas o de columnas dependiendo del tipo de esquema que queramos hacer.

Para crear un grupo debemos seleccionar las filas (seleccionando los números de las filas) o columnas (seleccionando las letras de las columnas) de las cuales vayamos a crear un grupo, pero no debemos incluir las filas o columnas que contengan fórmulas de sumario.

Una vez seleccionadas las filas o columnas vamos al menú Agrupar de la pestaña Datos y seleccionamos Agrupar.

Para trabajar un poco más rápido podemos utilizar las teclas para agrupar y desagrupar.

Para agrupar, una vez tengamos el grupo seleccionado presionamos Alt+Shift+Flecha derecha.

Para desagrupar, una vez tengamos el grupo seleccionado presionamos Alt+Shift+Flecha izquierda.

BORRAR Y OCULTAR UN ESQUEMA

Borrar un esquema.

Para borrar un esquema debemos acceder a la opción Desagrupar y seleccionar la opción Borrar esquema.

Antes de eliminar un esquema, debemos estar seguros de que es lo que realmente deseamos, pues una vez dada la orden, Excel no nos ofrece la posibilidad de deshacer.

Así pues si no estamos seguros de querer eliminar el esquema es mejor ocultarlo.

OCULTAR UN ESQUEMA.

Al ocultarlo lo que hacemos es ocultar las marcas que nos ayudan a expandir/contraer un esquema, pero el esquema sigue estando activo, para ocultar un esquema basta con presionar las teclas Ctrl+8 y automáticamente desaparecen las marcas del esquema, para volverlas a mostrar volvemos a pulsar la combinación de teclas Ctrl+8.

Guarda la hoja de trabajo en Mis Documentos o Documentos según sea el caso, como : esquema.xlsx.

Ejercicio paso a paso.

Crear un esquema manualmente.

Objetivo. Practicar cómo crear un esquema manualmente en Excel.

1.- Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2.- Abre el libro de trabajo creado en el anterior paso a paso llamado Ejemplo_esquemas.xlsx Primero debemos quitar el esquema automático que creamos.

3.- Selecciona la pestaña Datos.

4.- Elige la opción Desagrupar. Se abrirá otro submenú.

5.- Elige la opción Borrar esquema.

6.- Ahora selecciona los números de las filas 3 a la 5.

7.- Selecciona la pestaña Datos.

8.- Elige la opción Agrupar. Se abrirá otro submenú.

9.- Elige la opción Agrupar. NOTA: Se abrirá un pequeño cuadro de diálogo porque en este caso la agrupación puede ser un poco ambigua, deberás indicar si quieres agrupar filas o columnas. Selecciona filas y pulsa Aceptar, si fuera necesario.

10.- Selecciona los números de las filas 7 a la 10.

11.- Presiona las teclas Alt + Shift + Flecha derecha.

12.- Selecciona los números de las filas 12 a la 19.

13.- Presiona las teclas Alt + Shift + Flecha derecha.

14.- Comprime los tres grupos creados.

15.- Selecciona los números de las filas 6, 11 y 20.

16.- Presiona las teclas Alt + Shift + Flecha derecha. Si has ampliado los datos continúa con la misma estrategia. Ya tenemos el esquema por filas. Ahora vamos construir el de las columnas.

17.- Selecciona las letras de las columnas B,C y D.

18.- Presiona las teclas Alt + Shift + Flecha derecha.

19.- Si has ampliado los datos continúa con la misma estrategia. Ya tenemos el esquema terminado.

Por último guarda el libro de trabajo en Mis Documentos o Documentos del disco duro C: como ejemplo_esquemas_1.xlsx.

Ver una hoja en varias ventanas

(Usaremos el ejemplo anterior para esta actividad)

Suele suceder de forma muy común que al estar realizando hojas de cálculo vayamos ampliando cada vez más el campo de visión de la pantalla, llegando a ocupar más de una página por hoja, y cuando se trata de estar cotejando datos resulta bastante incómodo tener que desplazarse cada vez de arriba hacia abajo o de un lado al otro.

Vamos a ver las distintas opciones que Excel nos ofrece para minimizar el problema y así trabajar de una manera más cómoda con los datos introducidos.

Podemos utilizar la opción de ver la misma hoja en varias ventanas, o utilizar la opción de ver la misma hoja en varios paneles.

Para ver la misma hoja en varias ventanas, debemos acceder a la pestaña Vista y pulsar el botón Nueva ventana, si la ventana que teníamos estaba maximizada no nos daremos cuenta de que haya ocurrido algún cambio en la ventana, pero si nos fijamos en la barra de título podemos ver que ahora el nombre del documento además tiene añadido al final ":2" indicando que es la segunda ventana del mismo documento.

Para ver las dos ventanas al mismo tiempo hacemos clic en Organizar todo y seleccionamos Vertical, Horizontal, Mosaico o Cascada, dependiendo de como estén situados los datos en la hoja.

Ahora podemos desplazarnos sobre una ventana independientemente de la otra.

Usando el libro anterior de esquemas se vería parecido a esto:

Dividir una hoja en paneles Podemos utilizar también la opción de dividir la hoja por paneles. Si no nos gusta lo de tener varias ventanas abiertas en la pantalla, podemos utilizar la opción de dividir la hoja en 2 ó 4 paneles.

Para dividir la hoja en paneles podemos hacer clic en el botón Dividir    n la pestaña Vista y automáticamente nos presenta dos barras, una vertical y otra horizontal las cuales podemos desplazar para ajustar el tamaño de las porciones de ventana a nuestro gusto.

En la intersección de las divisiones si colocamos el cursor cambiara a una forma forma cursor_vert, indicando el desplazamiento de la barra hacia la derecha y hacia la izquierda.

Si ponemos el cursor en el centro de la división al arrastrarlo hacia abajo y soltándolo se verá algo así:

INMOVILIZAR PANELES

Podemos utilizar la opción de inmovilizar los paneles.

Si lo que nos interesa es dejar inmóviles las cabeceras de los datos y así desplazarnos únicamente sobre los datos teniendo siempre disponible la vista de las cabeceras, podemos utilizar la opción de inmovilizar los paneles.

Para realizar esto, simplemente despliega el menú Inmovilizar paneles que se encuentra en la pestaña Vista.

Si te interesa mantener la primera fila (como cabecera) o la primera columna (para que ejerza la misma función) selecciona la opción correspondiente.

En el caso de que lo que quisieras inmovilizar no se encontrarse en esas posiciones selecciona el rango de celdas y pulsa la opción Inmovilizar paneles.

La zona seleccionada podrá desplazarse, el resto permanecerá inmovilizado.

Para desactivar la inmovilización de los paneles vuelve a seleccionar esta opción y haz clic sobre Movilizar paneles.

Ejercicio paso a paso.

Ver distintas vistas Objetivo.

Practicar cómo gestionar las vistas en Excel.

1.- Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2.- Abre el libro de trabajo creado en el primer paso a paso de este tema llamado esquemas. Primero debemos quitar el esquema automático que creamos.

3.- Selecciona la pestaña Vista.

4.- Elige la opción Nueva ventana.

5.- Selecciona la pestaña Vista.

6.- Elige la opción Organizar todo. Se abrirá un cuadro de diálogo.

7.- Elige la opción Vertical y pulsa sobre Aceptar. Ahora tienes dos ventanas y te puedes desplazar independientemente por ellas.

8.- Cierra la ventana que acabamos de crear, en la barra de título debe mostrarse Microsoft Excel - ejemplo_esquemas:2.

9.- Maximiza la ventana del ejemplo.

10.- Selecciona la pestaña Vista.

11.- Elige la opción Dividir.

12.- Selecciona la pestaña Vista.

13.- Elige de nuevo la opción División para quitarla.

14.- Desplaza con el ratón la barra de desplazamiento de paneles horizontal hasta la mitad de la pantalla.

15.- Quita la barra de panel horizontal desplazándola hacia arriba del todo.

16.- Desplaza con el ratón la barra de desplazamiento de paneles vertical hasta la mitad de la pantalla.

17.- Quita la barra de panel vertical desplazándola hacia la derecha del todo.

18.- Por último cierra el libro de trabajo sin guardar los cambiar realizados.

LAS TABLAS DINÁMICAS

Comprende una serie de Datos agrupados en forma de resumen que tienen aspectos concretos de una información global.

Es decir con una Tabla Dinámica se pueden hacer resúmenes de una Base de Datos, utilizándose para, promediar, o totalizar datos.

Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique.

Partiendo de una Planilla confeccionada, tomaremos como ejemplo la circulación de vehículos a través de una estación de peaje, se trata de aplicar sobre la misma un principio de ordenamiento a través de una herramienta de Excel, Tablas Dinámicas.

CREAR UNA TABLA DINÁMICA

La creación de una tabla dinámica se realiza a través del asistente y en varios pasos.

1- Poner el cursor en cualquier celda de la Tabla

2- Tomar la opción Insertar, Tabla Dinámica

Paso 1:

a.- Dónde están los datos que desea analizar? Marcar opción: Seleccione una tabla o rango, como ya estaba el cursor colocado en la tabla a analizar adquiere el rango de esta, A1:D21

b.- En donde se colocara la tabla dinámica? Del titulo Elija donde desea colocar el informe de tabla dinámica Marcar la opción: Hoja de cálculo existente

c) clic en Ubicación podrá ser una celda cercana a los datos a analizar, o bien en otra hoja o libro de trabajo, lo dejaremos en la celda F17

d) Hacer clic en Aceptar, y se formara la estructura para la tabla dinámica:

Paso 2:

Seleccionar los campos que se agregarán a la tabla dinámica.

a) De la lista que se encuentra del lado derecho hacer clic en los campos que se desea se integren a la tabla dinámica.

b) Lo que ira formándose la tabla dinámica, conforme se vayan agregando los campos, usaremos los campos: vehículo, y cantidad, ya que mes, y semana serian objetos de suma, y no ayudarían al resultado del análisis, ejemplo el mes seria 1 y Excel lo tomaría como Enero, lo cual no se indica si es el mes de Enero, por lo cual se omite, y semana, el sistema en tablas dinámicas hace es una suma , tendríamos que el resumen de semana seria 10 porque la suma de los datos que forman semana, y por eso se omite.

Una vez creada la tabla dinámica la pestaña Opciones, se activan unas opciones interesantes:

El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.

Lista de campos .- oculta la barra lateral de los campos./ o la activa Botones +/- Activa/Desactiva los botones que se presentan en la tabla dinámica para ver detalles

Encabezados de campo.- Muestra / Oculta los encabezados de la tabla dinámica. Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr

Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado.

Esto se emplea sobre todo cuando el volumen de datos es importante.

Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable:

Si retiramos las marcas de Ómnibus, Moto, Camioneta y hacemos clic en Aceptar, solo se hará el resumen de Auto, Camión.

Obtener promedios en una tabla dinámica

Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatoria, puede interesarnos modificar esas fórmulas por otras como puedan sacar el máximo o el mínimo, el promedio, etc.

Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos presenta un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos activa un cuadro de diálogo como el que vemos en la imagen.

En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.

GRAFICOS CON TABLAS DINAMICAS

Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la pestaña Opciones.

Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos.

Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga.

Luego, la mecánica a seguir para trabajar con el gráfico es la misma que se vio en el tema de gráficos

Practica tablas dinámicas en Excel

1.- Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2.- Descarga el libro de trabajo en el directorio Mis Documentos o Documentos tablas_dinamicas.xlsx, realiza la tabla dinámica de los datos contenidos ahí, y forme un gráfico de barras de 3D.

3.- Guarda este libro con el mismo nombre  tablas_dinamicas.xlsx.

CONSOLIDAR HOJAS DE TRABAJO.

El concepto de consolidar hojas de trabajo viene muy ligado al concepto de enlace que acabamos de ver.

Cuando hablamos de consolidar hojas de trabajo estamos entablando una relación entre varias hojas de trabajo, por tanto es muy posible que existan enlaces entre esas hojas de trabajo.

Estos son los pasos en forma resumida para crear una consolidación despúes viene la práctica a realizar.

La consolidación de hojas de trabajo puede ser una compañía que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearíamos una hoja dependiente y los libros fuentes serían los libros de los departamentos con sus prepuestos. Si pulsamos sobre la pestaña Datos y pulsando el botón Consolidar...nos mostrara el cuadro de diálogo Consolidar como veremos mas adelante donde podemos escoger: La Función: a utilizar.

En Referencia: vamos seleccionando las celdas de uno de los libros fuentes.

- pulsamos sobre Agregar para añadir las celdas seleccionadas a la lista de todas las referencias,

- repetimos los dos últimos pasos para seleccionar las celdas de los diferentes libros fuentes,

- finalmente pulsamos en Aceptar para finalizar la fórmula.

Si marcamos la casilla Crear vínculos con los datos de origen, los datos de los libros fuente se incluirán en el libro consolidado a modo de esquema, y si cambiamos un valor en el origen, ese cambio quedará automáticamente reflejado en el libro consolidado.

Ahora veremos un ejemplo de hacer una consolidación.

Descargue este archivo del servidor consolidar.xlsx

Este libro de Excel contiene 4 hojas con datos de ventas de diferentes Vendedores: Andres, Paco, Ana Maria, y Yolanda, observe que los datos para hacer la consolidación deberán estar en la misma posición en todos los casos para simplificar la relación de los datos, todos los vendedores están ubicado desde la celda A3 a la celda B10.

Los títulos y distribución deberán ser iguales en todas las hojas. (Esto es un requisito para que se efectué correctamente el proceso de consolidación)

Pasa a la hoja que tiene por nombre Totales, tendrás la siguiente tabla que está ubicada de igual forma en el mismo rango de celdas que los datos a consolidar, de la celda A3 a la celda B10.

Donde la tabla está vacía el área de datos numéricos que serán donde la usaremos para hacer la consolidación, Coloca el cursor sobre la primer celda vacía para lo datos numéricos, celda B4. Ahí activamos el menú de consolidar, que esta en el menú DATOS:

Al hacer clic sobre consolidar: Lo primero es establecer qué tipo de función realizaremos en este caso estamos solicitando una suma (vea la casilla Función,) pero podemos hacer otro tipo de función, esto dependerá de lo que queremos obtener en la consolidación.

Hacer clic sobre el cuadro  para seleccionar las celdas (ovalo de color negro) de datos de cada hoja Lo cual se contraerá teniendo esta forma:

Con esto activo seleccionar con el ratón la hoja llamada: Andres, y seleccionar los datos de las celdas de la B4 a la B9, Y hacemos un clic en el botón del lado derecho Agregar, mostrándose de esa forma:

Ahora para seguir seleccionado con las demás hojas hay que hacer clic en el botón de referencia, con lo cual se regresa al inicio del cuadro de consolidación, hacemos clic sobre el botón del lado derecho Agregar para que sea usado para la consolidación.

Se repetirá el proceso con las hojas restantes de : Paco, Ana Maria y Yolanda.

Al terminar la selección de los datos de las hojas de consolidación, será:

Asegurarnos que este marcado el botón Crear vínculos con los datos de origen para que cuando exista un cambio de datos en una de las hojas seleccionadas, el cambio se refleje en los datos finales Se puede observar que del lado izquierdo se agregó una sección que contiene signos +

Esto es para realizar un análisis de varios niveles de detalle esto se observa en la parte superior con los numero 1 y 2.

Al hacer clic en el primer signo más (+) nos presenta el detalle de estos datos:

Como podemos ver, nos da un detalle de cada concentrado de datos el signo menos (-) es para contraer el consolidado y el signo más (+) es para expandirlo.

Expanda todo lo relacionado con los meses, y contraiga el resumen mes: Cerrar el libro de trabajo no guardar.

Ejercicio de Consolidar:

Abra descargue el documento consolidación_ejercicio.xlsx, que se encuentra en el servidor.

Tenemos las ventas realizadas por las personas: Martha Elvira, Rogelio, Josué Emmanuel, Juan José, Fátima Leticia, María del Carmen, Luís Juan, Silvestre Vidal, María Isabel, durante 3 meses, y queremos saber cuál es su promedio de ventas.

Realizar la consolidación de las hojas llamadas Mes 1, Mes 2, Mes 3, en la hoja llamada Total, cambiando la función de Suma a Promedio, use la opción de Crear vínculos con los datos de origen Al terminar Guardar el libro de trabajo como: consolidación_ejercicio_final.xlsx, en el directorio Mis Documentos o Documentos.