☰

Extrayendo fechas con un punto común en Excel

tag-icon • tag-icon microsoft-excel
Extrayendo fechas con un punto común en Excel

Imagine un montón de datos registrando cuándo llovió y en qué estado,

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

¿Cómo escribo una fórmula que me proporcione una lista de fechas que llovieron en un estado en particular? Por ejemplo, quiero saber cuándo llovió en Nevada. ¿Podría recibir la respuesta 02Jan, 04Janen un celular?

Respuesta1

El siguiente método de presentación resuelve el problema:

ingrese la descripción de la imagen aquí

Cómo funciona:

  • Para obtener resultados más rápidos, cree un menú desplegable para estados en la celda A28.
  • Presione la pestaña Datos y luego Validación de datos.
  • Para Fuente de lista, seleccione D28:D31.

ingrese la descripción de la imagen aquí

  • Ahora, para Lluvia o No Lluvia, cree el menú desplegable en la celda B28, usando la Validación de datos y, para Fuente de lista, agregue Sí y No.

ingrese la descripción de la imagen aquí

  • Ahora la fórmula final de una matriz (CSE) en la celda A35:

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

NÓTESE BIEN

  • Termina la fórmula conCtrl+Mayús+Entrar& llenar hacia abajo.

  • Ajuste las referencias de celda en la fórmula según sea necesario.

Respuesta2

A decir verdad, simplemente crear una tabla dinámica a partir de sus datos probablemente sea su mejor enfoque. Pero si quieres un enfoque de fórmula:

Digamos que sus datos están en las celdas A1:F5, incluidos los encabezados. Luego, si su valor de búsqueda está en la celda G8, lo siguiente:

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

te da una salida que se parece a:

02-ene, 04-ene

si se busca "Nevada".

Etcétera.

Básicamente, utiliza el primero XLOOKUP()para encontrar la fila de datos SÍ y NO que se aplican al estado. El IF()envoltorio que da 0 para los NO y la fecha en la fila del encabezado para los SÍ. Eso está envuelto por un IF()que usa el resultado anterior dividido por sí mismo (lo que produce ERROR y 1) en una verificación para ver si el resultado es >0, que son los 1. Esos ERRORES seguirán adelante, pero el segundo IF()reemplaza los 1 con las fechas nuevamente. Luego, IFERROR()ajusta eso para reducir el resultado solo a aquellos elementos que eran fechas, no ERRORES. Son valores de fecha enteros de Excel (43563, digamos) en este punto y parecería que se desean en la forma en que aparecen en la fila del encabezado, por lo que TEXT()les da formato. El hecho de que pierdan su "número" al hacerlo no es un problema, ya que lo harían en cualquier caso si estuvieran en la lista (bueno, a menos que solo se devolviera una fecha... si uno quiere "ser técnico"). ..). Finalmente, TEXTJOIN()proporciona un separador "coma-espacio" para facilitar la lectura (por lo que tienen un separador que se puede usar para extraer uno o todos, si es necesario en trabajos posteriores).

Probablemente hay otros enfoques, la XLOOKUP()versión de la antigua técnica de "doble ÍNDICE/COINCIDENCIA" podría ser una, aunque obtener múltiples respuestas XLOOKUP()me dio múltiples respuestas (5, como se esperaba), pero fueron tres del 01 de enero y dos del 02 de enero. El de Jan, no, por ejemplo, el 2 de enero y el 4 de enero que quería. Aquí son las 3 de la mañana, así que tal vez me perdí algo allí. FILTER()Estaba actuando demasiado rápido, perdiéndome la información posicional para obtener las dos fechas para Nevada (inmediatamente solo los dos resultados, pero ahora 1-2, no 2-4, así que no es una buena manera de sacar las fechas del encabezado). Podrían ser otras ideas. De nuevo, a las 3 de la madrugada...

Sin embargo, probablemente no importe, ya que cada uno sería un montón de elementos, como el anterior, por lo que reducirlo a algo simple probablemente no pueda suceder.

información relacionada