El tutorial se divide en tres módulos:
- Gráficas y Filtrado de datos
- Excel Tables, filtros y algunas funciones
- 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
