Gestión del tiempo con Excel Diseñe planificaciones, horarios, tabla de ausencias

(1)

Capítulo 1: Introducción

A. ¿A quién se dirige este libro? 10

B. Presentación 10

C. ¿Cómo leer este libro? 10

1. Información general 10

2. Proyectos individuales 11

3. Detalles sobre los proyectos propuestos 11

D. Descarga 12

Capítulo 2: La fase de diseño

A. Introducción 14

B. ¿En qué consiste la fase de diseño? 14

C. Proyecto ejemplo 14

1. Enunciado 14

2. ¿Cuál es el objetivo principal que queremos conseguir? ¿Cuáles son las condiciones? 15 a. Formulación de los objetivos y condicionantes 15

b. Análisis de las condiciones 16

3. ¿Qué datos se deben introducir? ¿Cómo organizarlos? ¿Cuál será el tamaño de la tabla?

17

a. Definición de los datos 17

b. Tipo de datos 18

c. Tamaño de la tabla 19

(2)

a. Destinatario de la tabla 19

b. El soporte final 20

5. ¿El creador es el usuario? ¿Quién deberá introducir o utilizar los datos? 20 6. ¿Esta tabla se utilizará una o más veces? ¿Es una plantilla? 20

D. Elección de las herramientas de Excel 21

1. Indicaciones - Comentarios - Listas desplegables 21

a. Introducir una indicación en una celda 21

b. Insertar un comentario 22

c. Mostrar un pop-up 22

d. Listas desplegables 22

e. Prohibir valores 22

2. Escribir varias líneas en la misma celda 23

a. Salto de línea forzado 23

b. Ajustar texto 23

3. Fórmula de cálculo 23

4. El diseño de página 24

a. La orientación del documento 24

b. Los márgenes y/o el ajuste de la escala 24

c. Repetición de las filas o columnas de título 25

5. Reproducir la misma tabla 25

a. Copiar-pegar 25

b. Copiar y pegado especial 25

c. Copiar la hoja entera 25

d. Grupo de trabajo 25

6. Proteger la tabla 26

a. Proteger el fichero para lectura 26

b. Proteger el fichero para escritura 26

c. Proteger el libro 26

d. Proteger la hoja 26

E. Conclusión 27

(3)

A. ¿Qué significa gestionar el tiempo en Excel? 30

B. ¿Cómo introducir una fecha? 30

C. ¿Cómo introducir una hora? 35

D. La codificación real de las fechas y horas 35

1. Las fechas 35

2. Las horas 36

E. Los formatos 36

1. La configuración regional 36

2. Inconvenientes de los formatos fecha y hora 38

a. Reconocimiento de los formatos introducidos 38

b. Reconocimiento de los formatos importados 38

3. Los formatos personalizados 44

F. Trabajar con horas 44

1. Convertir una hora en fracción de horas, de minutos o segundos 44

2. Cálculos con tiempo 45

a. Sumar tiempo 45

b. Restar tiempo 46

G. Los cálculos con fechas 47

H. Un poco de historia 50 1. Astronomía 50 2. Los calendarios 51 a. El calendario romano 51 b. El calendario juliano 51 c. El calendario gregoriano 52 d. El resto de calendarios 52

(4)

Capítulo 4: Construir rápidamente el esqueleto de una planificación anual

A. Enunciado del proyecto 54

B. Fase 1: análisis del proyecto 54

1. Las preguntas y respuestas 54

2. El resultado que se desea obtener 55

C. Fase 2: elección de los comandos Excel 56

1. La introducción de las fechas 56

2. Mostrar el día en letras y en cifras 56

3. Evitar los sábados y domingos 57

a. La función DIASEM 58

b. La función SI 59

4. Una planificación por sala 62

5. Un año entero por planificación 62

6. Reiniciar cada año con una planificación vacía y con las fechas actualizadas 63

7. Proteger la hoja 63

D. Fase 3: creación de la planificación paso a paso 64

Capítulo 5: Gestión de ausencias

A. Enunciado del proyecto 78

B. Fase 1: análisis del proyecto 78

2. El resultado a obtener 79

C. Fase 2: elección de los comandos Excel 80

1. Ganar tiempo en la introducción de las ausencias 80

(5)

3. Agrupamiento de datos 84

a. Ocultar columnas: método básico 84

b. Ocultar/mostrar columnas con un método avanzado: el esquema 85 4. Identificación rápida de los datos mediante colores 86

a. Los colores de relleno 86

b. El formato condicional 86

c. Visibilidad de los encabezados de filas y columnas 86

5. Aviso en caso de pocos empleados en un equipo 88

6. Los cálculos estadísticos 89

a. La función CONTAR.BLANCO 89

b. La función CONTAR.SI 89

c. La función CONTARA 90

D. Fase 3: gestión de ausencias paso a paso 91

1. Los elementos básicos: empleados y fechas 91

2. Inmovilizar paneles 93

3. Listas desplegables 93

4. Formato condicional 95

5. El esquema 96

6. Cálculo del número de empleados presentes por equipo 97 7. Fórmula de aviso en caso de que falten personas 99 8. Cálculo del número de ausencias por mes y empleado 101 9. Rectificar rápidamente las referencias en varias fórmulas 102

10. Agrupamiento de los datos por mes 103

11. Número de ausencias por tipo por cada equipo 104

12. Años bisiestos 107

Capítulo 6: Gestión de vencimientos

A. Enunciado del proyecto 110

B. Fase 1: análisis del proyecto 111

(6)

a. Lista de facturas 112

b. Tesorería 114

c. IVA 114

d. Volumen de negocio por proveedor 115

e. Lista de las facturas a pagar 115

C. Fase 2: elección de los comandos Excel 116

1. Validación de datos 116

2. Función SI 116

a. Automatizar el cálculo de las fechas de vencimiento 117 b. Automatizar el cálculo del importe de los vencimientos 117 c. Comprobación de la introducción manual de los vencimientos 117

3. Fórmulas matriciales 117

a. ¿Qué es una fórmula matricial? 118

b. Fórmula matricial que devuelve varios valores 118 c. Fórmula matricial que devuelve un solo valor 119

d. Fórmula matricial con condición 119

e. Pasar por cálculos intermedios 120

f. Limitaciones de la fórmula matricial 122

g. Ventajas e inconvenientes de una fórmula matricial 122

4. Los filtros 123

a. Los filtros simples o automáticos 123

b. Los filtros avanzados 123

5. Tabla dinámica 123

a. ¿Qué otras soluciones tenemos? 123

b. ¿Por qué elegir la tabla dinámica? 125

c. Limitaciones de los filtros y las tablas dinámicas 125

6. Impedir la modificación de los datos 126

7. ¿Hay que proteger las fórmulas? 126

D. Fase 3: gestión de los vencimientos paso a paso 126

1. Limitar la introducción a dos valores para la columna Tipo relación 127 2. Creación de listas desplegables Porcentaje de IVA y Forma de pago 129

3. Cálculo de las fechas de vencimiento 131

(7)

5. Control de los plazos introducidos manualmente 136

6. Cálculo del IVA en cada pago 136

7. Creación de la tabla de tesorería 137

8. Cálculo del IVA por meses 143

9. Cálculo del volumen de negocio por proveedor 145

a. Creación de la tabla dinámica 145

b. Actualización de la tabla dinámica 148

c. Modificación de las referencias del rango de datos 150 10. Mostrar la lista de facturas a pagar en una fecha en concreto 152

11. Protección 160

a. Protección del fichero 160

b. Protección parcial de la hoja 161

12. Conclusión 161

Capítulo 7: Gestión de horas extra

1. Enunciado 164

2. Información legal 164

a. Seguimiento diario de las horas extra 166

b. Resumen semanal de las horas extra 166

c. Resumen mensual de todos los empleados 167

1. Hoja de seguimiento de las horas extra 167

a. Determinar el número de semana 167

b. Cálculo del mes 169

c. Cálculo del mes de pago 169

d. Cálculo de las horas de ausencia 170

(8)

f. Cálculo de las horas extra 171

2. Tabla de resumen mensual 172

a. Vincular los libros 172

b. ¿Qué método utilizar para calcular los resultados del resumen? 174

D. Fase 3: gestión de horas extra paso a paso 175

1. Creación de la plantilla de seguimiento de las horas 175

a. Cálculo del número de la semana 176

b. Cálculo de las horas trabajadas 176

c. Cálculo de las horas extra 176

d. Cálculo de las horas de ausencia 177

e. Cálculo del mes natural 177

f. Cálculo del mes de pago de las horas extra 177 g. Cálculo de las horas extra por semana convertidas en fracción de horas 178 h. Cálculo de las horas extra por porcentaje de aumento 179 i. Informar el mes de pago en el resumen semanal 179 j. Últimos retoques antes de guardar como plantilla 181

k. Guardar como plantilla 182

2. Tabla de resumen mensual 182

a. Preparación de los criterios 182

b. Implementación de cálculos con vínculos 183

Capítulo 8: Calculadora de fechas de entrega

1. Cálculo de la fecha de entrega 190

(9)

c. DIASEM 197

d. SI 198

2. Administrar los nombres de celda 198

a. Nombrar una celda 198

b. Eliminar un nombre 199

c. Modificar la o las referencias de un nombre 200

3. Inicializar la tabla 200

4. La validación de datos y la protección de las fórmulas 205

D. Fase 3: nuestra calculadora paso a paso 206

1. Creación de los identificadores de cada celda 207

2. Nuestras fórmulas 208

3. Creación de la macro y del botón de inicialización 211 4. Limitar la introducción de datos a datos concretos: validación de datos 216

5. Protección de su calculadora 219

6. Guardar un fichero que contiene una macro 220

Capítulo 9: Planificación y seguimiento de las tareas de un proyecto

1. Proyecto con fecha de inicio o fecha de fin obligatoria 227 2. El calendario: formato personalizado, SI, O, DIASEM 228 3. Calcular la duración total del proyecto: SI y SUMA 228 4. Incidencia de las tareas dependientes: BUSCARV 228 5. Incidencia de los fines de semana en el cálculo de la fecha fin: DIA.LAB.INTL 228 6. Ilustración del proyecto en la parte del calendario: SI, Y, formato condicional 229 7. Numerosas columnas de información: fijar los paneles 229 8. Evitar que se modifiquen las fórmulas: protección parcial de la hoja 229

(10)

9. Particularidad de la gestión de proyecto a partir de una fecha de fin 229 10. Seguimiento del proyecto: vínculo, DIAS.LAB.INTL 229

D. Fase 3: gestión de las tareas de un proyecto paso a paso 230

1. Automatización de la elección del tipo de proyecto: inicio o fin 230 2. Automatización de la tabla de planificación del proyecto 236 a. Calendario adaptado a la fecha de inicio o de fin de proyecto 236 b. Lista desplegable para elegir el responsable asignado a la tarea 238 c. Automatización del cálculo de inicio y fin de tarea sea cual sea el tipo de proyecto

239

d. Efecto Diagrama de GANTT 240

e. Bloquear las primeras columnas para una mejor lectura 242

3. Seguimiento del proyecto 243

4. Comparativa Real-Planificado 244

5. Protección parcial de las hojas y guardar como plantilla 246