in

Cómo devolver la primera y la última vez desde las marcas de tiempo en Microsoft Excel

menú de la aplicación excel de microsoft office

menú de la aplicación excel de microsoft office
Imagen: PixieMe/Adobe Stock

Si tiene un centro de llamadas y realiza un seguimiento de las llamadas entrantes y sus tiempos de llamada, puede almacenar toda esa información en Microsoft Excel. La hoja almacenaría la identificación de la persona que llama y los valores de marca de tiempo.

Sin embargo, informarlos no será tan simple como imprimir una lista todos los días porque las personas que llaman pueden llamar varias veces durante un solo día. Eso es lo que será significativo para aquellos que necesitan la información.

En este tutorial, aprenderá qué es una marca de tiempo y luego cómo usar las funciones de Excel MIN() y MAX() para devolver la primera y la última llamada del día desde las marcas de tiempo. Luego, creará un conjunto agrupado de registros que devuelva la primera y la última llamada de cada persona que llama.

Uso Microsoft 365 en un sistema Windows 10 de 64 bits. Algunas de las funciones utilizadas solo están disponibles en Microsoft 365 y Excel para la web. Descarga la demostración de este tutorial de Excel.

VER: Comandos de Windows, Linux y Mac que todos deben conocer (PDF gratuito) (TechRepublic)

Cómo devolver el valor de la primera vez en Excel

Una marca de tiempo es una combinación de fecha y hora que marca un punto específico en el tiempo. Si cambia el formato de la celda a General o Número, verá un número en lugar de una fecha. El entero del número representa la fecha y el valor decimal representa el tiempo dentro de esa fecha.

Por ejemplo, Figura A muestra una columna de marcas de tiempo formateadas para mostrarse como fechas y horas. La columna al lado muestra los valores subyacentes para cada marca de tiempo.

Figura A

Datos de prueba de Excel que incluyen varias fechas y números organizados en dos columnas
Figura A: el número entero es la fecha y el valor decimal es la hora.

Ahora, supongamos que su centro de llamadas de la mesa de ayuda realiza un seguimiento de las llamadas por persona que llama y la hora en que ingresa la llamada. Al final del día, desea saber la primera y la última llamada del día. La sencilla hoja de demostración que se muestra en Figura B enumera las llamadas en orden, por lo que es fácil ver la primera y la última llamada, pero ese no siempre será el caso, dependiendo de cómo el personal ingrese los registros de llamadas.

Figura B

Fecha de prueba de Excel con la celda 3F resaltada
Figura B: El personal ingresa registros de llamadas para cada llamada en el centro de la mesa de ayuda.

Sería fácil para un operador ingresar un registro de llamadas unos minutos más tarde de lo recibido, y luego sus registros ya no estarían en orden, por tiempo. Entonces, no contaremos con eso en nuestra solución.

Afortunadamente, la función MIN() de Excel devolverá la llamada más temprana (valor de tiempo más pequeño) del día. Esta función simple requiere solo un argumento y ese es el rango o referencia estructurada que contiene los valores que estamos evaluando.

La función

=MIN(Llamadas de tabla[Call Date])

usa una referencia estructurada porque el rango de datos es un objeto de tabla de Excel llamado TableCalls. Si está evaluando un rango de datos normal, usaría la referencia

=MIN(C3:C12).

La función

=BUSCARV(F3,LlamadasTabla,1)

devuelve el cliente que hizo esa primera llamada, como se muestra en Figura C. La referencia estructurada, TableCalls es el nombre de la tabla. F3 hace referencia al tiempo de la primera llamada (a la derecha) y el argumento 1 devuelve el valor correspondiente en la primera columna de TableCalls.

Figura C

Datos de prueba de Excel con la celda 3E resaltada
Figura C: La función BUSCARX() devuelve el cliente que realizó la primera llamada del día.

Ahora devolvamos la última llamada del día.

Cómo devolver el último valor de tiempo en Excel

Una vez que haya trabajado con las funciones para devolver la primera llamada y el cliente que realiza esa llamada, hacer lo mismo para la última llamada del día es simple. Usaremos MAX() para devolver la última llamada y otra función XLOOKUP() para devolver el cliente que realiza esa llamada.

Figura D muestra ambas funciones:

E5: =BUSCARV(F5,LlamadasTabla,1)

F5: =MAX(TableCalls[Call Date])

Figura D

Datos de prueba de Excel que tienen seleccionada la celda 5E
Figura D: Se utilizan las funciones BUSCARX() y MAX().

La función XLOOKUP() devuelve el cliente que realizó la última llamada buscando el valor de tiempo en F5 y devolviendo el valor correspondiente de la columna ID de cliente. La función MAX() devuelve la llamada más reciente (el valor de tiempo más grande) de los valores de tiempo en la columna Fecha de llamada, C5:C12.

Si está siguiendo y los valores de tiempo en F3 y F5 muestran tanto la fecha como la hora, puede reformatear esas celdas para mostrar solo la hora.

Primero, seleccione F3 y, en la pestaña Inicio, haga clic en el menú desplegable Formato en el grupo Número. Elija Hora de la lista desplegable. Repita estos pasos para la celda F5.

Eso fue fácil y funciona si todo lo que necesita es la primera y la última llamada del día. Supongamos que desea un registro para cada cliente que devuelva la primera y la última llamada del día si ese cliente realizó más de una llamada. Este requisito es más complejo.

Cómo devolver una llamada y sus llamadas en un registro en Excel

Es posible que la administración desee ver una lista de todos los clientes con su primera y última llamada si ese cliente realizó más de una llamada. No cumplirá con esa solicitud con algunas funciones simples, pero puede hacerlo (Figura E).

Figura E

Datos de prueba de Excel con los datos de prueba organizados en la columna H resaltados
Figura E: esta agrupación simple devuelve la primera y la última llamada para cada ID de cliente.

El primer paso es devolver una lista única de ID de cliente. Para hacerlo, ingrese la siguiente función de matriz dinámica en H3

=SORT(ÚNICO(TableCalls[Customer ID]))

Esta función devuelve una lista única ordenada de valores de ID de cliente como una matriz dinámica. Eso significa que solo hay una expresión, y está en H3. El resto de la columna es un rango de derrame: los resultados necesarios para cumplir con la expresión.

Para devolver la primera llamada para cada ID de cliente, ingrese la siguiente función en I3 y cópiela en las celdas restantes:

=BUSCARX($H3,LlamadasTabla[Customer ID],Llamadas de mesa[Call Date])

Esta función devuelve la primera llamada para el ID de cliente correspondiente en la columna H.

Para devolver la última llamada para cada ID de cliente, ingrese la siguiente función en J3 y cópiela en las celdas restantes:

=BUSCARX($H3,LlamadasTabla[Customer ID],Llamadas de mesa[Call Date],”Sin resultados”,-1)

El último argumento, -1, realiza la búsqueda de abajo hacia arriba, por lo que puede devolver la última llamada. Si ordena sus llamadas en orden descendente, deberá modificar ambas funciones eliminándolas de la de J3 y agregándolas a la de I3.

Esta configuración funciona, pero solo un cliente llamó más de una vez, por lo que las funciones duplican la primera llamada como última llamada. El resultado es peor que distraer, es confuso, así que agreguemos un formato condicional que oculte los tiempos duplicados en la columna de la última llamada.

Primero, seleccione J3:J9 y, en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y seleccione Nueva regla en el menú desplegable. En el cuadro de diálogo resultante, haga clic en la última opción del panel superior, Usar una fórmula para determinar a qué celdas dar formato.

En el control de fórmula, ingrese =$J3=$I3 (Figura F). Haga clic en Formato, haga clic en la pestaña Fuente, elija blanco de la paleta y haga clic en Aceptar dos veces para volver a la hoja.

Figura F

Seleccione un menú Tipo de regla abierto en Excel con Usar una fórmula para determinar a qué celdas dar formato seleccionadas
Figura F: Ingrese una fórmula de formato condicional.

Como puedes ver en Figura G, solo es visible el último valor de tiempo para el ID de cliente 101. Los otros valores están ahí, pero no puede verlos porque la fuente es del mismo color que el fondo. No me gusta ocultar cosas, pero debido a que esto se actualizará de forma rutinaria, parece una solución conveniente.

Figura G

Datos de Excel con formato condicional para que los datos repetidos no sean visibles
Figura G: el formato condicional muestra el valor de la última llamada cuando no coincide con el valor de la primera llamada.

Manténganse al tanto

Parece mucho trabajo, pero todas las funciones que usamos son fáciles de implementar. El único inconveniente es que no puede usar funciones de matriz dinámica en objetos de tabla. Eso significa que debe actualizar las funciones en las columnas I y J y la referencia de formato condicional, cuando sea necesario. Por ese motivo, le mostraré cómo lograr lo mismo con una tabla dinámica en un artículo futuro.

Si no está familiarizado con BUSCARX(), puede leer Cómo usar la nueva función de matriz dinámica BUSCARX() en Excel. Para obtener más información sobre UNIQUE(), lea Cómo usar la función UNIQUE() para devolver un recuento de valores únicos en Excel.

Fuente

Juez aprueba plan de Apple para pagar 30,5 millones de dólares para resolver demanda por cheque de equipaje

Juez aprueba plan de Apple para pagar 30,5 millones de dólares para resolver demanda por cheque de equipaje

Anuncian un nuevo juego de El señor de los anillos por Weta Workshop

Anuncian un nuevo juego de El señor de los anillos por Weta Workshop