Excel (Hojas de Cálculo)

 Publicado el 19-01-2019  |  8 minutos (1687 palabras)  |  0 comment  •  Otros idiomas: EN

Las aplicaciones de hojas de cálculo como Microsoft Excel, Hojas de cálculo de Google, Apache OpenOffice Calc (ver otras alternativas en workana), podrían considerarse una herramienta básica o de iniciación para el análisis de datos.

En esta entrada, vamos a centrarnos en la herramienta MS Excel 2016 para introducir de manera básica varias de sus funcionalidades más interesantes desde el punto de vista del análisis de datos (realizables de manera análoga en el resto de herramientas de hojas de cálculo). En posteriores entradas de la sección de proyectos, se irán viendo ejemplos prácticos de análisis desarrollados mediante algunas de estas herramientas.

Introducción

MS Excel es probablemente la herramienta más utilizada para el análisis y la visualización de datos en el ámbito empresarial y educativo.

A continuación vamos a ver como utilizar esta herramienta para realizar análisis de estadística descriptiva, así como realizar análisis y visualización de datos más avanzados mediante las novedades del Modelo de datos de Excel y centrándonos principalmente en el uso de tablas y gráficos dinámicos.

La información expuesta se basa en una pequeña parte de los aspectos recogidos en los cursos del Microsoft Data Science Program:

Análisis estadístico descriptivo

Análisis mediante fórmulas

Como análisis inicial exploratorio de los valores de una variable, y partiendo de la información indicada en la entrada estadística descriptiva, mediante MS Excel se pueden calcular fácilmente las medidas de tendencia central y variabilidad aplicando las correspondientes fórmulas:

Medidas de tendencia central

El cálculo de las medidas de tendencia central se realiza mediante las siguientes fórmulas aplicadas sobre todos los valores de la variable bajo análisis:

Medida Función en Excel
Media Aritmética PROMEDIO()
Mediana MEDIANA()
Moda MODA.UNO()


Ejemplo:

Excel_tendencia_central

Medidas de variabilidad

El cálculo de las medidas de variabilidad se realiza mediante las siguientes fórmulas aplicadas sobre todos los valores de la variable bajo análisis:

Medida Función en Excel
Rango MAX()-MIN()
Varianza VAR.S()
Desviación estándar o típica DESVEST.M()
Error estándar DESVEST.M()/RAIZ(CONTAR())


Ejemplo:

Excel_variabilidad

Complemento Herramientas de Análisis

MS Excel dispone del complemento “Herramientas de Análisis” la cual es de gran utilidad para la realización de cálculos estadísticos de una manera rápida y sencilla.

Una vez habilitado siguiendo las indicaciones del anterior enlace, se puede hacer uso de este complemento desde la pestaña de datos en la barra de menús como bien se indica en exceltotal - Estadística Descriptiva.

Ejemplo - Resumen de estadísticas descriptivas mediante la herramienta de análisis:

Herramientas de Análisis

Como podemos observar, se obtienen automáticamente todas las medidas de tendencia central y variabilidad, además de algunas otras medidas como los valores mínimos y máximos de la muestra, la suma de todos sus valores, la cuenta de registros de la muestra y los valores de curtosis y coeficiente de asimetría, los cuales sirven para medir la característica de forma y asimetría de la distribución como se verá en más detalle en la entrada que se hará para sobre la visualización de distribuciones.

Modelo de datos de Excel

El modelo de datos de Excel comenzó en la versión MS Excel 2010 mediante el complemento Power Query y ha sido integrado directamente en Excel en la versión MS Excel 2016. El modelo de datos permite establecer un modelo relacional entre diferentes tablas y explorar y analizar sus datos mediante tablas y gráficos dinámicos. Este modelo supone 3 grandes ventajas respecto a la gestión tradicional de relación entre diferentes tablas basado en el uso de la fórmula BUSCARV():

  • Rapidez: La actualización de datos relacionados entre diferentes tablas mediante BUSCARV() puede llegar a ser muy lenta dado que se ejecuta un cálculo de búsqueda por cada una de las celdas de nuestras tablas relacionadas.
  • Procesamiento de los datos: A la hora de analizar los datos, es muy probable que sea necesario realizar varias operaciones de manipulación y transformación de los mismos, las cuales no es sencillo de abordar mediante el uso relaciones con BUSCARV().
  • Escala: El tamaño máximo de Excel es de 1 millón de celdas (lo cual es inmantenible mediante relaciones con BUSCARV() por su lentitud). Sin embargo, mediante este nuevo modelo de datos se permite trabajar con órdenes de magnitud de varios millones de datos.

Importación de datos

Las ventajas de llevar a cabo conexiones externas para la importación de datos es la posibilidad de actualización directamente desde el Excel cuando se produce un cambio en el origen de datos, en vez de tener que copiar y pegar todos los datos en una hoja Excel a la hora de trabajar con ellos.

En Excel, la importación de datos externos puede hacerse directamente en Excel, o bien desde el menú de Power Pivot, o desde el complemento Power Query integrado en el menú de Excel en la versión MS 2016 y el cual dispone de además de funcionalidades de limpieza y transformación de datos. En el siguiente enlace TrucosyCursos pueden consultarse las diferentes fuentes u orígenes de datos con los que se puede trabajar en cada una de estas opciones.

Más info sobre las posibilidades de transformación de datos en Power Query:

Tablas y gráficos dinámicas

Las tablas y los gráficos dinámicos en MS Excel son una forma de analizar grandes volúmenes de datos de manera sencilla e interactiva. Esta utilidad que está disponible desde la versión MS Excel 2007, es especialmente interesante en las últimas versiones de excel donde se permiten más posibilidades de análisis según los tipos de datos utilizados y se mejoran las opciones y tipos de visualización de los gráficos.

Las tablas dinámicas consisten en una manera de analizar rápidamente los datos presentados en una tabla mediante un resumen matricial de la información o medidas requeridas.

Los gráficos dinámicos representan visualmente la información de datos de una tabla. Como se puede ver en el video del enlace la creación de estos gráficos también es similar a la creación de una tabla dinámica.

Ejemplo Dataset train.csv Titanic:

A continuación vamos a representar un ejemplo de tabla y gráfico dinámico de los datos recogidos en train.csv:

tabla fichero train.csv

Ejemplo de Tabla dinámica

Mediante una tabla dinámica vamos a visualizar los datos del número de supervivientes por lugar de embarque (variable recogida en las filas) por género (variable recogida en las columnas):

tabla dinámica fichero train.csv

Ejemplo de Gráfico dinámico

Del mismo modo visualizamos mediante un gráfico dinámico los mismos datos del número de supervivientes por lugar de embarque (variable recogida en las filas) por género (variable recogida en las columnas): gráfico dinámico fichero train.csv

Power Pivot

Power Pivot es un complemento de Excel que proporciona características avanzadas para realizar análisis de datos y crear sofisticados modelos de datos. PowerPivot, puede mezclar grandes volúmenes de datos de varios orígenes, realizar análisis de información rápidamente y compartir información con facilidad.

Tanto en Excel y Powerpivot se pueden crear modelos de datos como una colección de tablas con relaciones. Los datos de un modelo en Excel o PowerPivot están disponibles en ambos casos. La gran diferencia entre ambos modos de trabajo es que mediante Power Pivot se pueden crear modelos de datos más sofisticados que permiten llevar a cabo análisis más avanzados.

DAX

Data Analysis Expressions (DAX) es un lenguaje de análisis de datos que consta de fórmulas y expresiones para el cálculo de medidas a partir de datos tabulados en las herramientas Microsoft PowerPivot, Power BI Desktop y SQL Server Analysis Services (SSAS).

Mediante la utilización de este lenguaje se pueden definir Columnas Calculadas y Medidas las cuales se irán calculando dinámicamente en nuestras tablas dinámicas en función del contexto definido en éstas mediante las columnas, filas, filtros o datos segmentados que se vayan seleccionando.

En DAX las funciones referencian a una columna o una tabla completa, pudiéndose añadir filtros para recoger valores específicos.

Más info sobre DAX:

Compatibilidad con Power BI

Power BI es un servicio de herramientas de análisis empresarial de Microsoft. Mediante este servicio se puede generar paneles de información visual personalizados y con diferentes niveles de accesibilidad que estén al alcance de todos mediante consultas online ya sea a través de un equipo informático o de un dispositivo móvil. Su objetivo es proporcionar información analítica de valor para la empresa de manera simplificada y muy visual para poder agilizar las tomas de decisiones.

La compatibilidad de Excel con Power BI puede realizarse de 2:

  • Importación directa del modelo de fundamentos: Se importa el modelo de datos a Power BI y se trabaja de manera independiente al modelo de datos original en Excel.
  • Conexión, Gestión y Visualización de Excel en Power BI: Se puede trabajar sobre el modelo de datos original mediante la función Excel Online de Power BI que permite trabajar en Excel sobre un navegador y visualizaremos los gráficos dinámicos generados en Excel originalmente sobre dicho modelo de datos.


Fuentes de interés:

excel  hojas  cálculo  tablas  VBA  datos  análisis 

No comment