Tablas dinámicas y análisis de datos e hipótesis con Excel

Sabemos que Excel es capaz de actualizar millones de celdas tras cualquier cambio en una de ellas. En múltiples ocasiones, sin embargo, nos gustaría no tener que aplicar diferentes valores de entrada a una celda para ver cómo afectan al resto del libro de cálculo, sino poder ver los resultados de forma simultánea para varios valores de entrada. Así, por ejemplo, puedo usar una fórmula para saber cuánto tendré que pagar a un banco para amortizar un crédito si solicito una determinada cantidad de dinero y me comprometo a devolverlo en un cierto número de plazos, pero tal vez preferiría ver cómo el solicitar diferentes cantidades o devolverlo en diferentes plazos afecta a la mensualidad a devolver. Las tablas de datos de Excel solucionan este problema.

En esta lección veremos el concepto de tabla de datos y presentaremos los dos tipos de tablas de datos que Excel ofrece: de una y de dos variables.

Las tablas de datos de una variable nos permiten visualizar cómodamente cómo el resultado de un cálculo se ve afectado por las modificaciones de una celda de la que se alimenta. En esta lección aprenderemos a crear este tipo de tablas de datos de una variable.

Las tablas de datos de dos variables nos permiten visualizar cómodamente cómo el resultado de un cálculo se ve afectado por las modificaciones de dos celdas de las que se alimenta. En esta lección aprenderemos a crear este tipo de tablas de datos de dos variables.

Cuando trabajamos con un libro de cálculo, frecuentemente tenemos celdas que contienen valores a partir de los cuales se construye posteriormente el resto de las hojas. Cuando rellenamos dichas celdas de entrada estamos planteando unas “condiciones”, lo que podríamos llamar un “escenario”. Así, por ejemplo, si estamos estimando las ventas del próximo año, podemos plantearnos un conjunto de valores más conservadores, más probables, más favorables, etc.

Para evitar tener que escribir dichos valores (correspondientes a unas condiciones dadas) una y otra vez, Excel nos permite guardar dichos valores como “escenario”, y poder mostrar el libro en un escenario o en otro rápidamente.

En esta lección aprenderemos a usar esta herramienta de Excel.

En un análisis de hipótesis típico modificamos unos valores de entrada para ver cómo impactan en unos valores de salida. Pero podemos también encontrarnos con el escenario contrario, en el que conozcamos cuál es el valor de salida que queremos obtener y necesitemos averiguar los valores de entrada que llevan a él. Así, por ejemplo, podemos encontrarnos en la necesidad de pedir un préstamo y sabemos el importe a solicitar, la cantidad máxima que podemos pagar por mes y el tiempo máximo que queremos estar devolviéndolo, y queremos saber cuál es la tasa de interés máxima que nos lo permitirá solicitar en estas condiciones.

Excel ofrece dos herramientas con este objetivo, aplicables a dos grados de complejidad distintos: Cuando necesitamos encontrar el valor de entrada que, aplicado a una fórmula, nos genera un valor de salida concreto, usaremos el comando buscar objetivo. Cuando nos enfrentemos a problemas de mayor complejidad, usaremos el complemento Solver.

En esta lección presentaremos estas dos herramientas.

El comando “Buscar objetivo” nos permite encontrar el valor de entrada que, aplicado a una fórmula, nos da un valor de salida. En esta lección aprenderemos cómo usarlo correctamente.

Las tablas de datos, uso de escenarios y la herramienta de búsqueda objetivo son perfectas para problemas relativamente sencillos o que involucran pocas variables. Para problemas más complejos Excel ofrece el complemento Solver que nos permite encontrar la mejor solución en circunstancias en las que el número de variables es elevado y necesitamos imponer restricciones a los valores de entrada y de salida.

En esta lección aprenderemos a usarlo aplicándolo a un problema típico: la maximización de un resultado financiero considerando límites que nos impone el entorno.

Los informes de tablas dinámicas –normalmente denominados simplemente “tablas dinámicas”- son una de las herramientas más potentes de Excel pues nos permiten analizar y resumir fácilmente grandes conjuntos de datos con una notable simplicidad.

Su necesidad surge cuando nos encontramos con listas de datos, sea cual sea su naturaleza. Cuando éstos son pocos, resulta sencillo extraer conclusiones o calcular totales o subtotales, pero cuando se trata de grandes cantidades de datos o implican numerosos campos, resulta prácticamente imposible extraer conclusiones sin la ayuda de las herramientas adecuadas.

En esta lección presentamos las tablas dinámica, analizando su necesidad y justificando su uso.

En esta lección aprenderemos los diferentes métodos que Excel pone a nuestra disposición para la creación de tablas dinámicas, incluyendo el Asistente para tablas y gráficos dinámicos que, a pesar de no estar disponible por defecto ni en la cinta de opciones ni en la barra de herramientas de acceso rápido, será la única forma que tendremos para controlar la dependencia e independencia de las tablas dinámicas, tal y como veremos en una lección posterior.

El uso efectivo de una tabla dinámica incluye acciones básicas como la asociación de campos a las diferentes áreas de campos, movimiento de campos entre áreas, eliminación de estos campos o la ordenación de la lista de campos en la sección de campos. En esta lección aprenderemos las diferentes formas que tenemos de llevar a cabo estas acciones.

Una de las grandes ventajas del uso de las tablas dinámicas es la posibilidad de desglosar una cifra según otros criterios. Esto se consigue anidando campos en las áreas de campos, lo que analizaremos en esta lección.

Una tabla dinámica no solo nos permite analizar un conjunto de datos para darnos un informe sobre el número de datos o sus sumas. Podemos también mostrar en ella información sobre los máximos y mínimos de los datos analizados, promedios, recuento de números, desviaciones estándar y varianzas.

En esta lección veremos cómo pedir a Excel que utilice una u otra de estas funciones.

Además de poder mostrar información sobre el número de datos, sus sumas, máximos, varianzas, etc., podemos pedir a Excel que nos muestre esa información en relación a otro dato o a otro criterio. Así, podemos pedir a Excel que nos muestre las ventas de un producto por día de la semana y el porcentaje que éstas representan respecto del total, por poner un ejemplo sencillo. Excel ofrece una notable variedad de herramientas con este objetivo, herramientas que vemos una por una en esta lección.

Una tabla dinámica, por defecto, no se actualiza automáticamente cuando cambian los datos de los que se alimenta. En esta lección veremos cómo actualizarlas manualmente.

Sí es posible, sin embargo, programar una actualización automática en ciertas condiciones, por ejemplo cuando la tabla dinámica se alimenta de una conexión externa a una base de datos Microsoft Access, por ejemplo. En esta lección veremos también en qué condiciones puede programarse esta actualización y cómo programarla, incluyendo actualizaciones tras la apertura del libro de cálculo, actualizaciones en segundo plano, actualización y cancelación de actualizaciones y configuración de los cálculos que realiza Excel (de forma automática, parcialmente automática o manual), información muy útil cuando tratamos con fuentes de datos muy grandes.

En esta lección aprenderemos cómo indicar a una tabla dinámica que se alimente de una fuente de datos diferente de la original.

Podemos mover fácilmente una tabla dinámica a otro lugar en nuestro libro o a una nueva hoja y éste será el tema de esta lección.

Es posible copiar una tabla a otra posición de la hoja actual, a otra hoja existente o incluso a otro libro. En esta lección veremos cómo.

Excel nos permite copiar datos de la tabla dinámica incluyendo cabeceras y datos del interior de la tabla. Veremos en esta lección cómo podemos hacerlo.

En esta lección veremos cómo podemos borrar (vaciar de contenido, no eliminar) una tabla dinámica.

Además de borrar una tabla dinámica (vaciarla de contenido), también es posible eliminarla, cosa que se tratará en esta lección.

Cuando tenemos gran cantidad de datos en nuestra tabla dinámica puede resultar muy útil ordenarlos por orden alfabético o de mayor a menor valor (o viceversa), permitiéndonos, por ejemplo, encontrar con más facilidad los elementos que deseamos analizar o encontrar patrones y tendencias en nuestros datos.

En esta lección veremos las diferentes opciones que nos ofrece Excel para realizar esta ordenación.

Si deseamos centrar nuestro análisis en una selección del conjunto de datos, podemos filtrarlos. En un informe de tabla dinámica o de gráfico dinámico, los filtros son acumulativos, es decir, cada filtro adicional que se aplica se basa en los filtros que ya se estén aplicando, reduciendo aún más el subconjunto de datos mostrado en la tabla dinámica o en el gráfico dinámico. Pueden aplicarse hasta tres tipos de filtro simultáneos: filtros manuales, filtros de etiqueta y filtros de fecha y valor, que se evalúan por este orden, y que analizaremos en esta lección.

Otro método para filtrar nuestra tabla dinámica consiste en el uso de segmentaciones. La segmentación de un conjunto de datos consiste, tal y como su nombre indica, en segmentar o dividir los datos en grupos uniformes más pequeños con características comunes. Así, por ejemplo, si hablamos del comportamiento de compra de los consumidores, parece claro que las necesidades cambian en función de variables como la edad, el sexo o el nivel de estudios del comprador. Otro factor relevante es el momento en el que ocurre aquello que estamos analizando, de forma que el tiempo será otro de los elementos claves que podremos filtrar usando las herramientas ofrecidas por Excel. Analizar toda la población sin considerar estos grupos individualmente puede suponer llegar a conclusiones sencillamente erróneas.

En esta lección veremos el concepto de segmentación y justificaremos la existencia de las segmentaciones de datos y de las escalas de tiempo.

Las segmentaciones de datos son pequeñas ventanas que, asociadas a una o más tablas dinámicas, nos permiten filtrarlas (segmentarlas) con suma facilidad, ayudándonos en el análisis de los datos. En esta lección veremos cómo se crean y se utilizan las segmentaciones.

En esta lección veremos cómo podemos configurar las segmentaciones para adaptar su funcionalidad a nuestras necesidades.

Excel nos permite aplicar estilos a las segmentaciones, así como modificar los estilos existentes y crear nuevos estilos. En esta lección cubriremos la primera parte dedicada a estos estilos.

Excel nos permite aplicar estilos a las segmentaciones, así como modificar los estilos existentes y crear nuevos estilos. En esta lección cubriremos la segunda y última parte dedicada a estos estilos.

Además de poder aplicar filtros para limitar las fechas de los datos que se muestran en la tabla dinámica, también podemos usar una escala de tiempo. Ésta consiste en un cuadro que podemos agregar a nuestra tabla dinámica y que nos permite filtrar por tiempo y mostrar así datos solo de los períodos que deseamos.

En esta lección veremos cómo se crean y cómo se usan las escalas de tiempo.

Una vez creada la escala de tiempo podemos adaptar su posición, dimensiones y estilo a nuestras necesidades. En esta lección veremos cómo hacerlo, incluyendo la adaptación de estilos existentes y la creación de nuevos estilos.

Por defecto una segmentación o una escala de tiempo está asociada únicamente a una tabla dinámica, aquella que estaba seleccionada cuando se creó. Sin embargo, en esta lección veremos cómo es posible asociar estas herramientas a más de una tabla dinámica, ofreciéndonos la posibilidad de centralizar la gestión de estas tablas dinámicas desde un único bloque de segmentaciones.

En ocasiones nos encontramos con que las etiquetas de los campos situados en filas o columnas no muestran el tipo de segmentación que querríamos realizar. Por ejemplo, si tenemos una lista de países tal vez preferiríamos analizarlos en bloques en función de su población, situación geográfica, económica, etc. El poder agruparlos según criterios arbitrarios nos permitiría extraer conclusiones acerca de dichos países considerados como bloques independientes.

En esta lección analizaremos las herramientas que Excel nos ofrece para realizar agrupaciones por fechas, números y por elementos seleccionados arbitrariamente.

Después de crear el informe de tabla dinámica, agregar campos, determinar el nivel de detalle que queremos, ordenar, filtrar y agrupar los datos, es posible que deseemos mejorar el diseño y el formato de la tabla dinámica para facilitar su lectura y hacerla más atractiva. Además, podemos también dar formato condicional al informe de tabla dinámica para resaltar aún más los valores que nos interesan.

En esta lección presentaremos estas herramientas que serán vistas con detalle a lo largo de las siguientes lecciones.

Cualquier tabla dinámica lleva asociada una ficha de opciones que nos permite configurar su comportamiento. En esta lección veremos estas herramientas con detalle.

Excel nos ofrece tres tipos de diseño diferentes para nuestros informes de tabla dinámica: diseño compacto, diseño en forma de esquema y diseño en formato tabular, diseños entre los que se muestran más diferencias cuando tenemos dos o más campos anidados en el área de filas.

En esta lección aprenderemos las diferencias y ventajas de cada uno de estos diseños.

Para adaptar más la información mostrada en la tabla dinámica a nuestras necesidades, podemos cambiar la forma en que muestran los subtotales, los totales generales y las posibles filas en blanco separando bloques en la tabla dinámica, y éste será el tema de esta interesante lección.

Una vez creada y configurada una tabla dinámica, podemos aplicarle uno de los muchos estilos predefinidos que nos ofrece Excel. Pero también podemos modificarlos y crearlos desde cero. En esta lección veremos en detalle cómo podemos modificar estos estilos para hacer las tablas dinámicas más atractivas y funcionales.

El formato condicional permite responder visualmente a preguntas específicas sobre los datos, así como identificar patrones y tendencias. Sin embargo, el formato condicional para un informe de tabla dinámica tiene algunas diferencias con respecto al formato condicional que se aplica a un rango de celdas o a una tabla de Excel, diferencias que debemos conocer si queremos aplicarlo correctamente.

En esta lección veremos el concepto de ámbito de aplicación del formato condicional, y lo aplicaremos en diferentes ejemplos.

En ocasiones necesitamos mostrar en nuestro informe de tabla dinámica campos o datos que no existen en nuestros datos fuente pero que son calculables a partir de ellos. Excel nos permite crear y agregar a nuestra tabla dinámica este tipo de campos, cosa que veremos en esta lección.

Por defecto, cuando pedimos a Excel que cree una tabla dinámica a partir de una fuente de datos, Excel carga los datos leídos en memoria, y crea la tabla dinámica a partir de esa copia de datos. Cuando creamos nuevas tablas dinámicas a partir de dicha fuente de datos, Excel, por defecto, las crea a partir de los datos que tiene en memoria. De esta forma se consumen menos recursos del ordenador y se agilizan los cálculos. Además esto nos permite utilizar segmentaciones o escalas de tiempo y asociarlas a todas esas tablas si así lo deseamos.

Esto, sin embargo, tiene consecuencias adicionales negativas que debemos conocer para decidir en cada momento si nos conviene crear una tabla dinámica dependiente de alguna que ya exista o si, por el contrario, resulta preferible crearla independiente a pesar del consumo extra de recursos que supone.

En esta lección veremos las diferencias entre tablas dependientes e independientes, ventajas e inconvenientes de unas y otras, y cómo utilizar el Asistente para tablas y gráficos dinámicos para su creación.

La consolidación de datos constituye una forma eficaz de combinar datos de distintos orígenes en un mismo informe de datos dinámicos. Por ejemplo, si tenemos tablas con cifras de ventas de las sucursales de nuestra empresa indicando qué modelos se venden a qué países, podemos usar una consolidación de datos para resumir esas cifras en un informe de ventas corporativo.

En esta lección veremos cómo realizar esta consolidación y cómo utilizar campos de página, herramienta que nos permitirá filtrar nuestro rango de datos consolidado.

Hasta ahora hemos utilizado una lista de datos como fuente de datos de nuestras tablas dinámicas. Sin embargo ésta no es la única opción posible. Excel puede acceder y analizar datos contenidos en una base de datos de Microsoft Access, Microsoft SQL Server o en un archivo de cubo de procesamiento analítico en línea (OLAP), por poner algunos ejemplos. En estos casos Excel se conecta al origen de datos externo y después crea la tabla dinámica para resumir, analizar, explorar y presentar estos datos. En esta lección presentamos estos conceptos que serán desarrollados a lo largo de las próximas lecciones.

En esta lección veremos cómo podemos crear una tabla dinámica a partir de una conexión de datos ya existente.

En esta lección veremos cómo podemos crear una tabla dinámica creando previamente una conexión de datos externa. En el ejemplo que veremos crearemos una conexión de datos con una base de datos Microsoft Access.

Los modelos de datos en Excel son un nuevo y potente método de integración de datos procedentes de varias tablas que nos permiten poder generar de forma efectiva un origen de datos para alimentar a herramientas como las tablas y los gráficos dinámicos, así como a los informes PowerView.

En esta lección introduciremos el concepto de modelo de datos y aprenderemos los requisitos necesarios para convertir un conjunto de tablas en un modelo de datos.

En esta lección veremos cómo crear un modelo de datos a partir de una fuente de datos externa. Concretamente utilizaremos una conexión a una base de datos Microsoft Access para explicar este procedimiento.

En esta lección veremos cómo podemos crear un modelo de datos a partir de un conjunto de tablas de datos contenidas en nuestro libro de cálculo.

Una vez creado un modelo de datos, podemos crear tablas dinámicas a partir de éste con todas las ventajas que esto supone, pues podremos mezclar campos en nuestra tabla aún cuando pertenezcan a diferentes listados de datos.

Aun cuando un informe de tabla dinámica es una herramienta sencilla y potente para resumir y analizar datos, si la tabla dinámica es grande o los datos son complejos, no siempre es fácil obtener una visión global de la información mostrada. Con un gráfico dinámico resulta mucho más sencillo dar sentido a los datos y encontrar patrones en los mismos. En esta lección introduciremos y justificaremos la necesidad de los gráficos dinámicos como complemento a nuestras tablas dinámicas.

En esta lección veremos cómo podemos crear fácilmente un gráfico dinámico a partir de una tabla dinámica ya existente.

Con independencia de cómo hayamos creado el gráfico dinámico, éste nos ofrece controles interactivos para ordenar y filtros los datos iguales a los que encontramos en las tablas dinámicas. En esta lección aprenderemos a usar estos controles.

Resumen del curso y despedida.