Preparación de Datos y Extracción de Componentes Temporales en Excel

Este procedimiento detalla la configuración inicial de los datos, incluyendo la separación de jerarquías y la extracción de componentes de fecha para su posterior uso en métricas y visualizaciones.

1. Configuración de Jerarquía y Separación de Texto

• Jerarquía de Datos: Utiliza la función Texto en Columnas. Configura la separación por espacio y aplica el formato de fecha (AMD) a la columna de fecha.
• Resultado Esperado: Columnas separadas para Fecha, AMD, Ubicación y otros datos relevantes (Fecha y Hora).

2. Extracción de Componentes de Fecha

Utiliza las siguientes fórmulas para extraer el año, mes y día de la columna de fecha:

• Año: =AÑO(fecha)
• Mes (Texto abreviado): =TEXTO(MES(fecha);"mmm")
• Día (Texto abreviado): =TEXTO(DIA(fecha);"ddd")

Configuración de Hojas Auxiliares y Limpieza de Datos

3. Creación de la Hoja ‘Auxiliares’

Crea una nueva hoja de Excel y nómbrala Auxiliares. En esta hoja, copia las columnas completas de Año, Mes y Día.

4. Eliminación de Duplicados y Ordenación

• Ve a la pestaña Datos y selecciona Eliminar Duplicados. Marca las columnas Año, Mes y Día (marcando hacia abajo).
• Ordenación Personalizada de Días: Para ordenar los días de la semana correctamente:
  1. En la marca para filtrar, selecciona Orden Personalizado.
  2. Selecciona todos los días.
  3. Configura la opción Ordenar por «Día», Valores de celda, y utiliza una Vista Personalizada para colocar los días en el orden deseado.

Construcción del Dashboard CMI y Cálculo de Métricas

5. Configuración de la Hoja ‘CMI’ (Cuadro de Mando Integral)

• Crea una nueva hoja de Excel y nómbrala CMI.
• Validación de Datos: Configura la validación de datos para seleccionar los años (en formato numérico) y los días (en formato de texto) obtenidos de la hoja Auxiliares.

6. Cálculo de Cantidad de Registros Condicionales

Para calcular la cantidad de registros que cumplen con los criterios de Año y Día seleccionados en la hoja CMI:

• Etiqueta una celda como Cantidad de Registros.
• Utiliza la función =CONTAR.SI.CONJUNTO:
  1. Selecciona la matriz completa de la columna Año (base de datos).
  2. Regresa a la hoja CMI y selecciona la celda del Año numérico.
  3. Repite el proceso con la matriz completa de la columna Día y la celda del Día en la hoja CMI.
• Visualización del Conteo: Ve a Insertar, selecciona Formas y elige Cuadro de Texto. Vincula este cuadro de texto al resultado de la fórmula CONTAR.SI.CONJUNTO.

7. Cálculo del Total y Porcentaje Complementario

• Total de Registros: Etiqueta una celda como Total. Cuenta el total de registros en la base de datos utilizando cualquier columna completa.
• Cálculo de Porcentaje Complementario: Si ya tienes el porcentaje de registros calculados (%), el complemento se calcula como: =1 - %.

Visualización de Datos y Formato Avanzado

8. Creación del Gráfico de Donas (Doughnut Chart)

Crea un gráfico de donas para visualizar el porcentaje de registros:

• Selecciona los valores del porcentaje (%) y su complemento (el valor calculado con =1 - %).
• Configura el Ancho del anillo a 10.
• En Formato, asegúrate de que el gráfico no tenga relleno (Sin Relleno) para una apariencia normal.

9. Cálculo de Temperatura Promedio Condicional

Utiliza la función =PROMEDIO.SI.CONJUNTO para calcular la temperatura promedio basada en los criterios de Año y Día:

• Rango de Promedio: Columna de Temperatura (matriz).
• Criterio 1 (Año): Columna Año (base de datos) y el Año en número (hoja CMI).
• Criterio 2 (Día): Columna Día completa (base de datos) y el Día en palabras (hoja CMI).

Visualización: Copia el formato de inserción de formas y coloca el número grande de la temperatura promedio.

Configuración de Matriz y Formato Condicional

10. Preparación de la Matriz de Datos

La matriz debe contener solo números.

• Accede a Análisis de Datos.
• Coloca los datos iniciales.
• Marca los Rótulos.
• Define el Rango de Salida.

11. Aplicación de Escalas de Color

Para aplicar formato condicional a la matriz:

• Selecciona los números de la tabla.
• Ve a Formato Condicional y elige Escalas de Color.
• Accede a Administrar Reglas y selecciona Editar Regla.
• Configura los valores de la escala de color para que sean: Número -1, Número 0, y Número 1.