DESARROLLADOR

Cómo regresar la primera y la última vez desde la marca de tiempo en Microsoft Excel

Menú de la aplicación de Microsoft Office Excel
Imagen: PixieMe/Adobe Stock

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

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

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

Fecha de prueba de Excel resaltada en la celda 3F
Figura B: un trabajador ingresa un registro de llamadas para cada llamada en el centro de la mesa de ayuda.

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

Datos de prueba de Excel resaltando la celda 3E
Figura C: La función BUSCARX() devuelve el cliente al que se llamó por primera vez en el día.

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

Datos de prueba de Excel con la celda 5E seleccionada
Figura D: Las funciones BUSCARX() y MAX() se usan juntas.

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

Datos de prueba de Excel con datos de prueba organizados resaltados en la columna H
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 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

Seleccione el menú Tipo de regla que se abre en Excel, que utiliza una fórmula para determinar a qué celdas dar formato a las celdas seleccionadas
Figura F: Ingrese una fórmula de formato condicional.

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

Datos de Excel con formato condicional para que los duplicados 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. 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.

LEER  Los aspirantes a arquitectos de la nube de AWS tienen una nueva opción de aprendizaje gratuita en Twitch

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Botón volver arriba