En la entrada anterior se mostró lo necesario para conocer la interfaz de Calc y poder comenzar a generar pilotos de datos. Lo que se presentará en esta entrada es la interfaz de Excel, de forma que se pueda comenzar a trabajar lo que en esta aplicación se le llama "Tablas dinámicas".
Comenzaremos primero por entender los pasos a seguir para crear las tablas dinámicas, eliminarlas, actualizar sus datos fuente, y editarlas.
Trabajar con Tablas dinámicas en Excel
Crear tablas dinámicas
Lo primero es colocarse en la hoja de cálculo que contiene los registros de la base de datos (en nuestros ejemplos la hemos llamado BD), a fin de que se pueda seleccionar el área de datos fuente.- NOTA: es sumamente importante que el área de datos no tenga ningún renglón vacío (todos los registros deben estar uno después del otro), además, tampoco debe tener ninguna columna vacía, es decir, todas las columnas deben tener rótulo (nombre de campo). Si la base de datos está cortada (con renglón o columna vacía) a partir de ese punto no se podrá tomar en cuenta la información en el piloto de datos.
Hay dos formas de que la orden de crear la tabla dinámica pueda seleccionar los datos:
- Se puede optar por colocar el cursor sobre la celda A1 (donde está el nombre del primer campo), y dejar que al dar la orden se seleccione automáticamente la información contenida en la base de datos.
- O en su caso, puede optar por seleccionar manualmente toda el área que abarca la base de datos en la hoja de cálculo, (incluidos los títulos de columnas, en renglón 1, que son los nombres de los campos en cuestión).
Lo siguiente es ir al menú y elegir las opciones Insertar en la sección Tablas busque Tabla dinámica:
Al seleccionar la opción se mostrará el cuadro de diálogo “Crear tabla dinámica”, en donde en el espacio llamado “Seleccione los datos que desea analizar” se debe indicar el origen de los datos, es decir, el rango de la base de datos. Si al crear la tabla solo colocó el cursor en la celda A1, vea como automáticamente se selecciona el área de la base de datos, y como aparece ese rango en la opción “Seleccione una tabla o rango”; observe la imagen de ejemplo que el rango es: BD!$A$1:$T$26
Ponga atención en el mismo cuadro de diálogo, en donde aparece el espacio llamado “Elija dónde desea colocar el informe de tabla dinámica”, en este caso desde el principio se debe indicar en donde colocar la tabla dinámica que se producirá.
- NOTA: Tenga presente que por ninguna razón debe crearse la tabla dinámica sobre el espacio donde se encuentran los registros de la base de datos (hoja BD), pues esto provocaría que la base de datos se dañe, si comienza a crear a partir de celdas que contengan datos, o demasiado cerca de la base de datos y al crear otro piloto de datos, esos datos de la nueva tabla se pueden absorver como parte de la base de datos, o en su caso dificultar crear la tabla.
Por ello elija entre las dos opciones: crear una hoja nueva o usar una existente.
Si aún no define un espacio donde se va a crear la tabla dinámica, entonces elija la opción “Nueva hoja de cálculo”, y cuando le de aceptar al cuadro de diálogo automáticamente se creará una hoja de cálculo nueva, y una vez creada puede renombrarla como “TablaDinamica”.
Pero si ya ha creado una hoja para vaciar allí el contenido de la tabla dinámica, entonces elija la opción “Hoja de cálculo existente”, para lo cual se va a activar “Ubicación”. Para ello comprima la tabla dinámica (presione el botón que señalo con la flecha en rojo):
Luego, elija la ubicación, es decir, vaya a la hoja y haga clic en la celda a partir de donde se creará la tabla dinámica; de esa forma en el cuadro de diálogo comprimido aparecerá la celda seleccionada. En el ejemplo de la imagen siguiente observe como se ha seleccionado la celda, y aparece la referencia PilotoDatos!$B$2
Después de seleccionar la celda, haga clic nuevamente sobre el botón “comprimir”, de forma que se expanda nuevamente el cuadro de diálogo y pueda hacer clic en el botón “Aceptar”.
Al presionar el botón Aceptar, lo que se presenta en la ubicación elegida para crear la tabla dinámica es la interfaz para el diseño de la tabla dinámica:
Observe que se presentan dos espacios para el diseño: a la izquierda el área de construcción de la tabla y a la derecha la lista de campos. En el espacio de la lista de campos se observan 4 áreas en las cuales se podrán colocar campos: Filtros, Columnas, Filas y Valores.
Mientras su cursor se encuentre dentro del área de construcción de la tabla (parte izquierda), se presenta la lista de campo (a la derecha), si usted toca cualquier celda fuera del área de diseño de la tabla, entonces dicha lista de campos va a desaparecer, lo único que debe hacer es volver a colocar el cursor sobre el área de diseño de la tabla (a la derecha).
Si por alguna razón llega a cerrar la lista de campos (al hacer clic en el botón cerrar dentro de dicho panel), entonces lo que debe hacer es ir al menú principal y observar que se presentan dos opciones adicionales que en general se agrupan como “Herramientas de tabla dinámica”, elija la opción “Analizar”, y busque el bloque “Mostrar”, para que encuentre la opción “Lista de campos”:
O en su caso puede hacer clic con el botón derecho sobre el espacio de diseño y obtendrá el menú contextual, en donde debe elegir la opción “Mostrar lista de campos”:
A continuación habrá que construir la tabla dinámica. Lo que en esta ocasión vamos a hacer es crear la tabla dinámica más simple, es decir, una en la que solo se muestre el conteo del total de registros de la base de datos.
Ahora bien, lo primero que debe hacerse al trabajar cualquier tabla dinámica que vayamos a construir, es colocar el campo clave sobre el espacio designado como “Valores”, en nuestro ejemplo es campo clave es “Folio”. Para colocar el campo clave en el espacio para “Valores” haga clic con el botón izquierdo del ratón sobre el campo clave (Folio), y sin soltar el botón del ratón arrastre el campo hasta el área “Valores” de la derecha o si así lo desea directamente sobre el espacio de la izquierda que dice “Coloque los campos de valor aquí ”; una vez allí, suelte el botón del ratón para que el campo se deposite sobre dicha área, al hacerlo verá algo similar a lo señalado en la imagen siguiente en el circulo rojo:
Observe que el campo folio se encuentra en el área llamada “Valores” y que con eso la tabla dinámica (a la izquierda) al menos presenta el total de registros de la base de datos. Ponga atención en el ejemplo, observe el nombre del campo aparece como “Cuenta de folio”; para corroborar que la formula que se aplica sobre dicho campo sea “contar” (ya que lo que se espera es que se hagan los conteos de los datos) y no se arroje cualquier otro resultado inesperado, entonces, ahora haga clic sobre la punta de flecha, para que se muestre un pequeño menú:
Elija la opción “Configuración de campo de valor…” para que abra un cuadro de diálogo, en el que en la pestaña “Resumir valores por” deberá elegir la formula “Cuenta”, y haga clic en el botón Aceptar:
Con esto se asegura que al campo folio se le aplique la formula de conteo, y se verá como se muestra a continuación:
- NOTA: Cabe aclarar que esto es lo básico que deberá hacer para cualquier tabla dinámica que vaya a crear de ahora en adelante, el campo clave es el único campo que deberá colocar en dicho espacio, ese campo y solo ese campo se coloca en el área “Valores”.
Esto es lo mínimo que se puede hacer para generar la tabla dinámica más pequeña, la más básica, que mostrará únicamente la cantidad de registros de la base de datos. Observe el ejemplo la tabla dinámica en la imagen siguiente:
Por el momento será lo mínimo que haremos para poder comprender como se trabaja con la interfaz que permite crear las tablas dinámicas. Después se verá con mayor detalle como explotar su potencial al agregar más campos en las demás áreas.
Eliminar la tabla dinámica
En Excel no existe una opción de menú que permita eliminar la tabla dinámica, en todo caso lo que se puede hacer es borrar los renglones (o en su caso las columnas) en donde se ha insertado. Por ello no es recomendable insertar una tabla en una hoja donde haya otros datos.Lo que si puede hacer es eliminar todo el contenido que se ha configurado en la tabla dinámica, y dejarla tal cual aparecía cuando recién se insertó; para lograrlo vaya al menú principal, elija Analizar, luego en la opción Acciones, y verá finalmente la opción Borrar, la cual muestra un último menú en donde aparece “Borrar todo”.
Pero esto se logra igual, solo arrastrando los campos fuera de los espacios donde se han colocado.
Actualizar la tabla dinámica
Ahora bien, si por alguna razón hace alguna adecuación a algún contenido de la base de datos, es decir, si se han modificado los datos de la hoja fuente (hoja BD en nuestro caso), por ejemplo, si inserta una columna nueva en la que va a colocar una fórmula para poder calcular un nuevo dato a partir de los datos ya proporcionados por los encuestados, y ya tiene creado un piloto de datos, lo que debe hacer es actualizar o recalcular la tabla del piloto de datos.Para recalcular las tablas seleccione en el menú principal Analizar / Actualizar / Actualizar.
O en su caso en el menú contextual (clic con el botón derecho sobre la tabla) elija: Actualizar.
No olvide que para hacer cualquier actividad con la tabla, el cursor debe estar ubicado sobre ella, en cualquier celda de la tabla dinámica, y no en cualquier otra parte de la hoja de cálculo.
Editar tablas dinámicas
En el caso de Excel, en todo momento se está en la capacidad de editar la tabla dinámica para modificar los campos que se seleccionaron para obtener la tabla de resultados, o en su caso u organizar el orden de presentación de los mismos. Lo que debe tener visible es la “lista de campos” (ubicada generalmente a la derecha del espacio de trabajo).Vamos a hacer un pequeño ejemplo, editando la tabla dinámica, en primer lugar, coloquemos el campo “sexo” sobre el área “Filas”:
Con esto la tabla resultante se presentará como se muestra a continuación:
Observe en la imagen anterior, que debido a que el campo se colocó en las filas, los resultados obtenidos para cada uno de los valores almacenados en el campo “sexo” (“F”, “M”) se presentan en cada fila (observe dichos valores en la primera columna de la tabla y los resultados del conteo de cada valor en la segunda columna).
- NOTA: En este momento el objetivo es comprender como editar el piloto de datos, por lo que no nos detendremos en la interpretación de la información obtenida en la tabla resultante, y lo dejaremos para otra ocasión.
Ahora bien, observe que se muestra un total, para ello vamos a poner atención en el menú principal en la opción “Diseño”, en la sección llamada igualmente diseño, busque la opción “Totales generales”, la sugerencia es que inicialmente mantenga activados los totales para filas y columnas pero, ya el trabajo con las tablas le enseñará cuales totales le conviene mantener activos. Observe en la imagen siguiente las opciones de menú que se le presentan:
Igualmente en el menú Diseño es posible elegir entre las opciones de “Diseño de informe”, el ejemplo de la tabla presentada en la hoja anterior, es la selección de la opción “Mostrar en forma de esquema”, de esa forma presenta el nombre del campo, puede probar las diferentes opciones para encontrar la más adecuada para sus datos:
En esta ocasión solo vamos a ver ejemplos sencillos con un campo, sin embargo, en el menú “Diseño” también se presenta la opción llamada “Subtotales”, para caso en donde se combinan dos campos en un área (sea en filas o sea en columnas) y así si se desea se pueden incluir subtotales del campo interior. Las opciones que presenta el menú son:
Para lograr que en la tabla resultante se muestren los nombres de los campos, la opción “Encabezados de campos” debe estar seleccionada (la encuentra en el menú principal, en “Analizar”, y luego en el bloque de opciones “Mostrar”:
Veamos ahora cómo se muestra la tabla dinámica, si el campo “sexo” lo coloca en el área “Columnas”:
Como puede verse en la imagen siguiente, los resultados son los mismos que los obtenidos en el ejemplo anterior, sin embargo, se modificará la presentación de los resultados. Así que, debido a que el campo se colocó en el área “Columnas” los resultados obtenidos para cada uno de los valores almacenados en el campo “sexo” (“F”, “M”) se presentan en cada columna (vea como los valores del campo se presentan en el primer renglón de la tabla, y los resultados del conteo de cada valor en el segundo renglón).
Cabe aclarar que en cualquiera de las áreas (Columnas, Filas, Filtros) es posible colocar más de un campo, y por lo tanto, es posible modificar el orden de los resultados mostrados en la tabla resultante, con solo cambiar de posición los campos colocados en cada área; esto se logra arrastrando el botón del nombre de uno de los campo a una posición distinta dentro del área donde se hayan colocado los campos. Dejaremos para otro momento dichos ejemplos, para aprovechar y analizarlos con mayor detalle cuando comencemos a interpretar los datos obtenidos.
Para eliminar un campo de cualquiera de las áreas simplemente selecciónelo y arrástrelo fuera de la misma, cuando el puntero del ratón se convierta en una señal de prohibición dentro de la hoja, suelte el ratón y el campo se eliminará.
Otro aspecto que es importante mencionar es que al pulsar dos veces en el nombre de un campo colocado en alguna de las áreas de la tabla resultante, se mostrarán un nuevo cuadro de diálogo, titulado “Configuración de campo” en el cual se presentan algunas opciones de configuración. Veamos el ejemplo con el campo “sexo”, en este caso es posible seleccionar si se presentan o no subtotales, y en su caso elegir un subtotal automático, o aplicar una nueva fórmula.
Este mismo cuadro puede mostrarlo desde el panel Campos de tabla dinámica, al hacer clic en la punta de flecha del campo en cuestión, de forma que se sale el menú contextual y pueda elegir la opción “Configuración de campo…”
Por último vamos a mover el campo al área llamada “Filtro”, la cual sirve para presentar los resultados solo basados en uno de los valores contenidos en el campo colocado en dicha área.
Como puede verse en la imagen siguiente, es que se puede hacer uso de las opciones presentadas en la lista desplegable en la parte superior de la tabla generada del Piloto de datos, para filtrar los resultados en base a uno de los valores:
El campo que genera la lista despegable prácticamente se utiliza para filtrar los resultados a presentar en la tabla dinámica, basados en el contenido del elemento seleccionado, ya que al elegir uno de los elementos, se mostrará el contenido acorde a dicha selección y los valores que se presentan en la tabla son el resultado del conteo filtrado por ese valor seleccionado en la lista. Cabe resaltar que el uso de esta área solo es adecuado cuando se ha trabajado una combinación de campos en las áreas “Filas” y “Columnas”, y que luego se desee analizar los datos basados ahora en un nuevo campo, pero para evitar obtener un tabla demasiado grande al agregar este campo adicional, ya sea al área de “Filas” o a la de “Columnas”, se opte mejor por observar los resultados basado en un campo en concreto (sobre todo en base a cada valor de ese campo), se trata así, de aplicar un filtro particular a la tabla resultante.
- NOTA: Se comprenderán mejor estas ideas cuando se prueben las consultas más complejas a la base de datos, sin embargo trataré de darles una idea general con un ejemplo sencillo: se tiene una base de datos con un campo en el que se captura el matricula del alumno (que se coloca en “Valores”), el campo semestre (que se coloca en “Columnas “), otro campo preferencia (que se ubica en “Filas”) en el que se almacena la preferencia con respecto a las diversas opciones de desayuno propuestos por la cafetería. Si se hace un piloto de datos, en el que en el área “Filtro” se ubica el campo “especialidad” que cursa el estudiante, es posible ir observando los resultados de preferencia de tipos de desayunos elegidos por los estudiantes de cada especialidad. Resultando tablas más pequeñas que si dicho campo se hubiera incluido, ya sea en área de columnas o en área de filas.
Por el momento, con esto se podrá conocer a groso modo la interfaz de Excel, a fin de poder comenzar a trabajar los pilotos de datos que permitan dar respuesta a las todas las consultas que se consideren pertinentes para generar nuestro reporte de investigación.
Para descargar el contenido de la entrada en pdf, haga clic AQUÍ.
Saludos
M.C. Bertha López Azamar
P.D. Alumno UNPA, si ya leíste la entrada, pon la letra de tu grupo y las iniciales de tus apellidos primero y luego de tu nombre en un comentario de esta entrada.
