Cómo agrupar valores de tiempo por hora en Excel

En Microsoft Excel, aprenda cómo informar valores de tiempo por hora utilizando el valor de serie de fecha subyacente.

Agrupar registros por fechas u horas es fácil en Microsoft Excel, pero no es un gran informe. Por ejemplo, una simple ordenación por columna de fecha u hora agrupará registros, pero será difícil de leer.

El artículo Cómo usar un borde para distinguir grupos más fácilmente en Microsoft Excel ofrece un método rápido y fácil para colorear bordes entre grupos. Es fácil y sin duda mejorará la legibilidad mientras trabaja con los datos, pero aún no es una gran herramienta de generación de informes. En este tutorial de Excel, le mostraré cómo crear una tabla dinámica que hará casi todo por usted cuando necesite agrupar registros por hora para generar informes.

Uso Microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar una versión anterior a través de Excel 2007. Para su comodidad, puede descargar el archivo .xlsx de demostración. No debería tener problemas para completar las instrucciones en Excel para la web.

Acerca de la aritmética de fechas de Excel

Excel simplifica el trabajo con fechas y horas. Si ingresa una fecha, Excel la reconoce y la formatea como una fecha. Lo mismo sucede si ingresas la fecha y la hora juntas. Lo que debe recordar es que lo que ve y lo que Excel está evaluando son dos cosas diferentes. Verá una fecha/hora formateada. Lo que evalúa Excel es un valor de serie de fecha.

Un valor de serie de fecha es un valor numérico que representa una fecha donde el 1 de enero de 1900 es 1 y todos los días posteriores son secuenciales con un incremento de 1. Por ejemplo, si el 1 de enero de 1900 es 1, entonces el 2 de enero de 1900, es 2, 3 de enero de 1900 es 3, y así sucesivamente. Como se mencionó, una vez formateada como una fecha, eso no es lo que verá. Si desea ver esto en funcionamiento, ingrese la fecha 1 de enero de 1900 y aplique el formato General. Hacerlo le permitirá ver el valor de la fecha de serie subyacente, 1. Ingrese tantas fechas como desee usando el formato General para tener una idea de esta configuración hasta que se sienta cómodo. Una vez que esté listo, continúe.

Cómo utilizar los datos de fecha de Excel

Supongamos que administra un centro de llamadas y desea saber cuántas llamadas se reciben cada hora. Esta es una solicitud razonable porque debe asegurarse de contar con el personal adecuado.

Trabajaremos con una hoja simple de valores de fecha y hora que se almacenan en la misma celda (Figura A).

Figura A

A la derecha de los valores de fecha/hora formateados, se encuentran los mismos valores formateados como General, solo para información visual. No querrá incluir esta columna en su trabajo. Cada registro de llamada comprende la identificación del personal, la hora de entrada, la hora de salida y la fecha y hora resueltas.

Lo primero que debe notar en las columnas General es que cada valor de fecha de serie consta de dos componentes: el valor total representa la fecha y el decimal representa la hora. El valor de la hora funciona igual que el valor del día, pero el valor decimal representa la hora del día dentro de un período de 24 horas que comienza a la medianoche. Por ejemplo, el valor decimal .00 representa la medianoche, el comienzo de un nuevo período de 24 horas, y .50 representa el mediodía porque está a la mitad de ese período de 24 horas.

Es posible que no sepas cómo ingresar una fecha y hora en la misma celda. Es simple, pero no algo que adivinarías de la cabeza. En la misma celda, haga lo siguiente:

1. Presione Ctrl + ; para ingresar la fecha actual.
2. Presione la barra espaciadora para ingresar un carácter de espacio.
3. Presione Ctrl + Mayús + ; y presione Entrar.

Si Excel no muestra la entrada como una fecha, aplique un formato de fecha de la siguiente manera:

1. Seleccione la celda que contiene su valor combinado de fecha y hora.
2. Haga clic en el iniciador de diálogo del grupo Número.
3. En el cuadro de diálogo resultante, elija Personalizado en la lista Categoría.
4. Seleccione el formato m/d/yyyy h:mm (Figura B). Si no está disponible, introdúzcalo.
5. Haga clic en Aceptar.

Figura B

La forma más sencilla de agrupar es mediante una ordenación simple por los valores de tiempo de llamada entrante, pero el problema con una ordenación es que no cuenta nada, y queremos un conteo por hora. Una tabla dinámica puede agrupar y mostrar todos los registros en un informe razonable.

Cómo usar una tabla dinámica de Excel

Los objetos de tabla dinámica de Excel han existido por un tiempo y son una excelente herramienta de generación de informes, ya sea al imprimir o mostrar información a través de un tablero. En nuestro caso, queremos un recuento simple de llamadas por hora. No necesitamos saber el personal que los fabricó o qué tan rápido se resolvió el problema.

Para insertar una tabla dinámica de Excel que muestre los recuentos de llamadas por hora, haga lo siguiente:

1. Haga clic en cualquier lugar dentro del rango de datos o del objeto Tabla.
2. Haga clic en la pestaña Insertar.
3. Elija Tabla dinámica del grupo Tablas. Si se le solicita, elija Tabla/Rango. Figura C muestra la configuración de Excel. Son justo lo que queremos, así que haga clic en Aceptar.

Figura C

En este punto, tiene un marco de tabla dinámica en blanco. Utilizando Figura D como guía, arrastre los campos hacia abajo a las listas apropiadas. Si el panel Campos de la tabla dinámica no está visible, haga clic dentro del marco vacío. Si eso no funciona, haga clic en la pestaña Analizar tabla dinámica contextual y luego elija Lista de campos del grupo Mostrar. La figura D también muestra la tabla dinámica resultante. Si desea ver la hora de cada llamada, expanda los valores de hora (Figura E).

Figura D

Figura E

Cuando arrastra valores de fecha/hora a una lista, Excel realiza un poco de magia para usted al anticipar sus necesidades y dividir esos valores en campos más apropiados, como día, hora, etc. Entonces, de ahí es de donde vinieron. Excel también cuenta el número de valores de llamada entrante por el campo de hora que agregó. Realmente es una herramienta poderosa y flexible. El informe resultante requirió muy poco trabajo o conocimiento experto.

Por ahora, hemos terminado. Tenemos una tabla dinámica con un recuento por horas; la información es significativa. Debido a que Excel agrega los campos de días y horas por nosotros, no tenemos que trabajar mucho. Excel agregará campos de fecha y hora cuando incluya dicho campo en la tabla dinámica. Sin embargo, debe recordar actualizar para obtener la información más actualizada.