​​​Tutorial MS Excel (Data Analysis)

​El tutorial se divide en tres módulos: 

  1. ​​Gráficas y Filtrado de datos
  2. Excel Tables, filtros y algunas funciones
  3. PivotTables y PivotCharts

El libro contiene más de 150 páginas con instrucciones detalladas para llevar a cabo las tareas básicas para el tratamiento y análisis de datos.  Los ejemplos del libro están realizados en la versión de Excel Office 365 64bit (US-English).  Las funciones, menús, botones, etc. están en inglés y las instrucciones escritas en español.  

Para descargar el libro, favor hacer click en este enlace.

Para descargar los datos para las prácticas, haga click en este enlace​.

Tabla de materias:  

Sobre este tutorial .......................................................................................................................... 9 

PRE-REQUISITOS: ..................................................................................................................... 9 

Resumen del tutorial ............................................................................................................... 9 

Módulo 1: Gráficas y Filtros .......................................................................................................... 10 

Resumen .................................................................................................................................... 10 

Objetivos ................................................................................................................................... 10 

Gráficas ...................................................................................................................................... 10 

Objetivos ................................................................................................................................ 10 

Crear gráficas ......................................................................................................................... 10 

Estilos y colores ..................................................................................................................... 12 

Estilos .............................................................................................................................................. 12 

Colores ............................................................................................................................................ 14 

Guardar como plantilla (template) ................................................................................................. 15 

Aplicar una plantilla ........................................................................................................................ 15 

Formateo ............................................................................................................................... 16 

Añadir una línea de tendencia (trend line) ..................................................................................... 16 

Escoger gráfica apropiada ..................................................................................................... 17 

Qué tipo de gráfica elegir ................................................................................................................ 17 

Algunas recomendaciones: ............................................................................................................. 20 

Pie chart: Mal aplicado y el menos apropiado… ......................................................................... 20 

Barra o columna: El más recomendado para representar magnitudes ...................................... 21 

Líneas .......................................................................................................................................... 21 

Mapa proporcional (treemap) .................................................................................................... 21 

Demo: Convertir datos en gráficas ........................................................................................ 22 

Formatear la gráfica ........................................................................................................................ 24 

Modificar el título ....................................................................................................................... 24 

Mover la gráfica a otra hoja ........................................................................................................ 24 

Filtros ......................................................................................................................................... 25 

Objetivos ................................................................................................................................ 25 

Filtrar datos ............................................................................................................................ 25 

Quitar filtros .................................................................................................................................... 26  

Esconder columnas ......................................................................................................................... 27 

Unhide (revelar columna) ............................................................................................................... 27 

Formateo de datos ................................................................................................................ 28 

Demo: Seleccionar datos relevantes ..................................................................................... 28 

Filtrar datos ..................................................................................................................................... 28 

Revelar una columna escondida ..................................................................................................... 30 

Esconder columna ........................................................................................................................... 31 

Formatear columna ......................................................................................................................... 31 

Quitar decimales ............................................................................................................................. 32 

Sparklines (mini-gráficas) .......................................................................................................... 33 

Objetivos ................................................................................................................................ 33 

Sparklines ............................................................................................................................... 33 

Qué es una mini-gráfica sparkline ................................................................................................... 33 

Añadir sparklines ............................................................................................................................. 33 

Remover sparklines ......................................................................................................................... 34 

Uso de sparklines ............................................................................................................................ 35 

Recomendaciones para sparklines:................................................................................................. 35 

Formateo de sparklines ......................................................................................................... 35 

Menú y herramientas Sparklines .................................................................................................... 35 

Tipos de sparklines ...................................................................................................................... 36 

Show ............................................................................................................................................ 36 

Style ............................................................................................................................................. 36 

Group .......................................................................................................................................... 37 

Axis: ............................................................................................................................................. 37 

Demo: Añadir sparklines........................................................................................................ 38 

Cambiar formato a Sparkline .......................................................................................................... 40 

Prácticas: ................................................................................................................................... 41 

Escenario ................................................................................................................................ 41 

Objetivos ................................................................................................................................ 41 

Ejercicio 1: Gráficas y exploración de datos .......................................................................... 41 

Hacer histograma ............................................................................................................................ 41 

Cambiar el histograma para mostrar otra columna ................................................................... 42 

Ejercicio 2: Formatear datos .................................................................................................. 44 

Ejercicio 3: Filtrar datos ......................................................................................................... 45 

Objetivos: ........................................................................................................................................ 45 

Preparar la hoja para hacer diagrama de dispersión ...................................................................... 45 

Cortar y pegar columna .............................................................................................................. 45 

Explorar si hay relación entre nivel de pobreza y desempeño alto en matemáticas ................. 46 

Añadir línea de tendencia ........................................................................................................... 47 

Cambiar el color de la línea de tendencia ................................................................................... 48 

Mover la gráfica a otra hoja ........................................................................................................ 48 

Añadir otro diagrama de dispersión: Matrícula vs Competencia avanzada en matemáticas .... 49 

Aplicar filtros ................................................................................................................................... 50 

Módulo 2: Excel Tables y Funciones ............................................................................................. 53 

Resumen .................................................................................................................................... 53 

Objetivos ................................................................................................................................ 53 

Tablas de Excel (Excel Tables) ................................................................................................... 53 

Objetivos ................................................................................................................................ 53 

Beneficios de las tablas .......................................................................................................... 53 

Qué es una tabla .................................................................................................................... 53 

Formateo ............................................................................................................................... 53 

Utilidad de las tablas ............................................................................................................. 54 

Crear tablas ............................................................................................................................ 54 

Formateo de la tabla ............................................................................................................. 55 

Ordenar y filtrar ..................................................................................................................... 55 

Tipos de filtros ....................................................................................................................... 55 

Ordenar (Sorting) ............................................................................................................................ 55 

Demo: Uso de una tabla ........................................................................................................ 57 

Algunos conceptos antes de discutir sobre funciones .............................................................. 60 

Objetivos ................................................................................................................................ 60 

Named ranges: Nombrar grupos de celdas y columnas ....................................................... 60 

Cómo hacer un named range .......................................................................................................... 60 

Borrar o redefinir named ranges .................................................................................................... 61 

Referencias relativas y absolutas .......................................................................................... 61 

Referencias relativas ....................................................................................................................... 61 

Referencias absolutas: .................................................................................................................... 61 

Ejemplos de referencias absolutas: ............................................................................................ 61 

Ejemplo de referencia a una celda (fila y columna fija) .............................................................. 61 

Códigos de errores en Excel y qué significan ........................................................................ 62 

Funciones .................................................................................................................................. 62 

AGGREGATE ........................................................................................................................... 62 

Sintaxis ............................................................................................................................................ 62 

Opciones de AGGREGATE(function_num, options, array,[k]) ........................................................ 63 

Funciones que usan el parámetro k ................................................................................................ 63 

LOOKUP.................................................................................................................................. 65 

Sintaxis ............................................................................................................................................ 65 

VLOOKUP ............................................................................................................................... 66 

Sintaxis: ........................................................................................................................................... 66 

INDEX & MATCH .................................................................................................................... 67 

Index ................................................................................................................................................ 67 

Sintaxis ........................................................................................................................................ 67 

Match .............................................................................................................................................. 68 

Sintaxis ........................................................................................................................................ 68 

Notas ........................................................................................................................................... 69 

Uso de INDEX con MATCH .............................................................................................................. 69 

COUNT, COUNTA, COUNTIF, COUNTIFS ................................................................................ 72 

SUMIFS ................................................................................................................................... 73 

DATE FUNCTIONS: Funciones temporales, fechas y horas .................................................... 74 

Los datos temporales se registran como números ......................................................................... 74 

Dos sistemas de fechas ................................................................................................................... 74 

DATE Function ................................................................................................................................. 75 

Sintaxis ........................................................................................................................................ 75 

DATEDIF........................................................................................................................................... 76 

Sintaxis ........................................................................................................................................ 76 

DAYS ................................................................................................................................................ 77 

Sintaxis ........................................................................................................................................ 77 

NETWORKDAYS ............................................................................................................................... 78 

Sintaxis ........................................................................................................................................ 78 

Resumir datos ............................................................................................................................ 79 

Objetivos ................................................................................................................................ 79 

Propósito de resumir datos ................................................................................................... 79 

Total Row ............................................................................................................................... 79 

Funciones incluidas en comando SUBTOTAL: ....................................................................... 80 

Sintaxis ........................................................................................................................................ 80 

Demo: Añadir fila de totales .................................................................................................. 81 

Prácticas: ................................................................................................................................... 83 

Ejercicio 1: Crear tablas ......................................................................................................... 83 

Ejercicio 2: Trabajar con tablas .............................................................................................. 85 

Ejercicio 3: Análisis de datos.................................................................................................. 87 

Ejercicio 4: Uso de funciones SUMIF y COUNTIF ................................................................... 88 

Ejercicio 5: Funciones LOOKUP y VLOOKUP .......................................................................... 89 

LOOKUP ........................................................................................................................................... 89 

Tareas: ......................................................................................................................................... 89 

Insertar columna ......................................................................................................................... 89 

Escribir la función LOOKUP ......................................................................................................... 90 

VLOOKUP ......................................................................................................................................... 91 

Tareas: ......................................................................................................................................... 91 

Escribir y ejecutar función VLOOKUP .......................................................................................... 92 

Sparklines para visualizar fluctuación ......................................................................................... 93 

Módulo 3: PivotTables y PivotCharts ............................................................................................ 94 

Resumen .................................................................................................................................... 94 

Dimensiones y métricas en PivotTables .................................................................................... 94 

Nota de precaución: Celdas vacías en un data range o una tabla ........................................ 94 

Utilidad ...................................................................................................................................... 95 

Interfaz PivotTable Fields .......................................................................................................... 96 

Disposición de los datos para que funcionen las tablas Pivot .................................................. 96 

PivotTable Tools Group tabs ..................................................................................................... 97 

Herramientas del tab Analyze: .............................................................................................. 97 

Herramientas del tab Design ................................................................................................. 98 

Grupos: ............................................................................................................................................ 98 

Cómo funciona una tabla Pivot ................................................................................................. 99 

Formatear números ................................................................................................................ 100 

Estilos para tablas Pivot .......................................................................................................... 100 

Table Layouts: Organización de la tabla .................................................................................. 101 

Forma tabular ...................................................................................................................... 102 

Formateo condicional (Conditional Formatting) ................................................................. 103 

Ordenar y filtrar ................................................................................................................... 104 

Agrupar datos ...................................................................................................................... 104 

Slicers y Timelines ................................................................................................................ 105 

Cálculo de valores y de columnas ........................................................................................... 106 

Summarize values by… ........................................................................................................ 106 

Show values as ..................................................................................................................... 107 

Percent of Grand Total: ................................................................................................................. 107 

Percent of Parent Row Total ......................................................................................................... 108 

Otros ejemplos de Show values as ............................................................................................... 109 

Show values as… Index.................................................................................................................. 110 

Campos calculados .............................................................................................................. 111 

Cálculos basados en conteos ............................................................................................... 112 

Trasponer columnas a filas (Vectorización) ............................................................................ 113 

Trasponer usando Unpivot Columns ................................................................................... 114 

Proceso 1: Importar datos: ........................................................................................................... 114 

Proceso 2: Usar Query Editor ........................................................................................................ 115 

Transform | Unpivot Columns ............................................................................................. 116 

PivotCharts .............................................................................................................................. 117 

Cómo añadir una gráfica Pivot ............................................................................................ 118 

Opciones disponibles para formatear gráficas Pivot ........................................................... 119 

Field Buttons ........................................................................................................................ 119 

Slicers ................................................................................................................................... 120 

Timelines .............................................................................................................................. 121 

Prácticas: Crear PivotTables y PivotCharts .............................................................................. 122 

Ejercicio 1: Crear PivotTable ................................................................................................ 122 

Mostrar valores como porcentajes ............................................................................................... 123 

Ver participación de cada distrito dentro de la región ............................................................. 124 

Ranks (posición) ............................................................................................................................ 125 

Ejercicio 2: Vectorización (unpivot) de múltiples columnas ............................................... 126 

Vectorizar (unpivot) múltiples columnas ...................................................................................... 127 

Cambiar nombres de las columnas ............................................................................................... 127 

Aceptar cambios y cerrar la interfaz Query Editor ....................................................................... 127 

Generar PivotTable ....................................................................................................................... 128 

Generar reporte presupuestos por agencia .................................................................................. 129 

Cambiar el formato a Currency ..................................................................................................... 130 

Remover totales para las filas ....................................................................................................... 130 

Usar Conditional Formatting para resaltar valores extremos ...................................................... 131 

Mostrar valores como porcentaje del total .................................................................................. 132 

Otras opciones Agrupar por Jerarquía: ......................................................................................... 133 

Percent of Parent Row Total ..................................................................................................... 134 

Percent of Parent Total ............................................................................................................. 135 

Index .......................................................................................................................................... 135 

Difference from y % Difference from ........................................................................................ 136 

Esconder valores de error en PivotTable .................................................................................. 137 

Running Total In y % Running Total In ...................................................................................... 138 

Otros desgloses ......................................................................................................................... 139 

Ejercicio 3: Crear PivotChart ................................................................................................ 139 

Aplicar filtro Top 10................................................................................................................... 140 

Cambiar agencias a columnas y años a Rows ........................................................................... 141 

Generar gráfica Pivot ................................................................................................................ 142 

Aplicar filtro en la gráfica Pivot (Top 10, Opción Percent) ........................................................ 143 

Ejercicio 4: Campos calculados en PivotTables ................................................................... 145 

Añadir PivotTable .......................................................................................................................... 145 

Añadir campo calculado a la tabla Pivot ....................................................................................... 146 

Cambiar formato numérico ...................................................................................................... 147 

Ordenar datos de forma descendente ...................................................................................... 148 

Preparar PivotChart ...................................................................................................................... 148 

Quitar botones en la gráfica Pivot ............................................................................................ 149 

Fuentes ........................................................................................................................................ 150 

Funciones útiles, Excel ............................................................................................................ 150 

Programas Add-In Utilidades .................................................................................................. 151 

Datos temporales .................................................................................................................... 151 

DATEDIF function ................................................................................................................. 151 

Visualización, gráficas.............................................................................................................. 151 

Recomendaciones: .............................................................................................................. 151 

Otros temas: ............................................................................................................................ 152 

Quiebras............................................................................................................................... 152 

 

 ​