Cómo regresar la primera y la última vez desde la marca de tiempo en Microsoft Excel
Si tiene un centro de llamadas y rastrea a las personas que llaman y cuando llaman puede almacenar toda esta información en Microsoft ExcelEsta tabla almacenará los valores de identificación de llamadas y marca de tiempo.
Sin embargo, reportarlos no es tan simple como imprimir una lista todos los días, ya que las personas que llaman pueden llamar varias veces durante el día. Esto tiene sentido para aquellos que necesitan información.
En este tutorial, aprenderá qué es una marca de tiempo y cómo usar las funciones de Excel MIN() y MAX() para devolver la primera y la última llamada del día desde una marca de tiempo. Luego, creará un conjunto de registros que devuelvan la primera y la última llamada de cada persona que llama.
Estoy usando Microsoft 365 en 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) (Tecnopedia)
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. Un entero numérico representa una fecha y un valor decimal representa el tiempo dentro de esa fecha.
P.ej, Figura A Muestra una columna de marca de tiempo formateada para que aparezca como fecha y hora. La columna al lado muestra el valor subyacente para cada marca de tiempo.
Figura A
Ahora, supongamos que su centro de llamadas de la mesa de ayuda rastrea las llamadas de las personas que llaman y cuándo entran. Al final del día, desea saber la primera y la última llamada del día.Tabla de demostración simple como se muestra Figura B Las llamadas se enumeran en orden, por lo que es fácil ver la primera y la última llamada, pero no siempre es así, dependiendo de cómo ingresó la persona en el registro de llamadas.
Figura B
Es fácil para un operador ingresar el registro de llamadas unos minutos después de recibirlo, y luego su registro ya no estará ordenado cronológicamente. Por lo tanto, no contamos con él en nuestra solución.
Afortunadamente, la función MIN() de Excel devolverá la primera llamada (valor de tiempo mínimo) del día. Esta sencilla función toma solo un parámetro, un rango o referencia estructurada que contiene el valor que estamos evaluando.
Función
=MIN(Llamadas de tabla[Call Date])
Utilice referencias estructuradas porque el rango de datos es un objeto de tabla de Excel llamado TableCalls.Si está evaluando el rango de datos normal, utilizará la referencia
=MIN(C3:C12).
Función
=BUSCARV(F3,LlamadasTabla,1)
Devuelve el cliente que realizó la primera llamada, como en Figura CLa referencia estructurada TableCalls es el nombre de la tabla. F3 se refiere al tiempo de la primera llamada (a la derecha), y el parámetro 1 devuelve el valor correspondiente a la primera columna de TableCalls.
Figura C
Ahora volvamos a la última llamada del día.
Cómo devolver el último valor de tiempo en Excel
Una vez que la función devuelve la llamada más antigua y el cliente que realizó esa llamada, es trivial hacer lo mismo con la última llamada del día. Usaremos MAX() para volver a la última llamada y otra función BUSCARX() para volver al cliente que realiza la llamada.
Figura D Se muestran dos funciones:
E5: =BUSCARV(F5,LlamadasTabla,1)
F5: =MAX(Número de llamadas de tabla[Call Date])
Figura D
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 (valor de tiempo máximo) desde el valor de tiempo en las columnas de fecha de llamada C5:C12.
Si muestra la fecha y la hora como el valor de la hora en F3 y F5, puede reformatear las celdas para mostrar solo la hora.
Primero, seleccione F3, luego en la pestaña Inicio, haga clic en el menú desplegable Formato en el grupo Números. Seleccione una hora de la lista desplegable. Repita estos pasos para la celda F5.
Esto es fácil y funciona si solo necesita la primera y la última llamada del día. Suponga que desea crear un registro para cada cliente que devuelva la primera y la última llamada del día si el cliente realiza varias llamadas. Este requisito es más complicado.
Cómo devolver las llamadas y sus llamadas en un registro en Excel
Si un cliente realiza varias llamadas, es posible que la administración desee ver una lista de la primera y la última llamada de todos los clientes. No cumplirá con ese requisito con alguna función simple, pero puede (Figura E).
Figura E
El primer paso es devolver una lista de ID de cliente únicos.Para hacer esto, ingrese la siguiente función de matriz dinámica en H3
= ordenar (único (llamada de tabla[Customer ID]))
Esta función devuelve una lista única ordenada de valores de ID de cliente como una matriz dinámica. Esto significa que solo hay una expresión, y está en H3. El resto de la columna es el rango de desbordamiento, el resultado requerido para satisfacer 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]llamada 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]llamada de mesa[Call Date],»Sin resultado»,,-1)
El último parámetro -1 realiza una búsqueda ascendente, por lo que puede devolver la última llamada. Si ordena las llamadas en orden descendente, debe modificar ambas funciones, eliminándola de la de J3 y agregándola a la de I3.
Esta configuración funciona, pero solo se llama a un cliente varias veces, por lo que la función duplica la primera llamada como la última. El resultado es peor que distraer y confundir, así que agreguemos un formato condicional que ocultará los tiempos de repetición en la columna de la última llamada.
Primero, seleccione J3:J9, luego en la pestaña Inicio, haga clic en Formato condicional en el grupo Estilos y seleccione Nueva regla de la lista desplegable. En el cuadro de diálogo que aparece, haga clic en la última opción en el panel superior para usar una fórmula para determinar qué celdas formatear.
En el control de fórmula, ingrese =$J3=$I3 (Figura F). Haga clic en Formato, haga clic en la pestaña Fuentes, elija blanco de la paleta y haga clic en Aceptar dos veces para volver a la hoja de trabajo.
Figura F
como puedes ver Figura G, solo es visible el último valor de tiempo para el ID de cliente 101. Los demás valores están ahí, pero no los puedes ver porque la fuente es del mismo color que el fondo. No me gusta ocultar cosas, pero dado que esto se actualiza regularmente, parece una solución conveniente.
Figura G
Manténganse al tanto
Parece mucho trabajo, pero todas las funciones que usamos son fáciles de implementar. Un pequeño inconveniente es que no puede usar funciones de matriz dinámica en objetos de tabla. Esto significa que debe actualizar las funciones y las referencias de formato condicional en las columnas I y J según sea necesario. Por esta razón, le mostraré cómo lograr lo mismo con las tablas dinámicas 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.