☰

Buscar fecha del tercer evento anterior

tag-icon • tag-icon microsoft-excel worksheet-function
Buscar fecha del tercer evento anterior

Básicamente, la fórmula necesita encontrar las 3 terminaciones más recientes de una tarea y luego generar la fecha de finalización más antigua de esas 3. Es decir, la fecha de la tercera finalización más reciente de cada tarea.

Aquí hay un ejemplo si mi explicación no es clara:

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

La fórmula debe devolver lo siguiente:

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

Tarea A: hubo 2 finalizaciones el 5/7 (la más reciente), por lo que la tercera más reciente es el 1/7.
Tarea B: hubo 2 finalizaciones el 3/7 (la más reciente), por lo que la tercera más reciente es el 2/7.
Tarea C: todas estaban en la misma fecha, por lo que la tercera más reciente es el 9/7.

La fórmula debe poder soportar tener 2 filas separadas en la misma fecha con la misma tarea, ya que el conjunto de datos incluye dichas entradas.

Creo que la solución implicará una combinación de Vlookup y sumif, pero esto va más allá de mis habilidades actuales.

Respuesta1

ingrese la descripción de la imagen aquí

En Cell, C10escriba esta fórmula de matriz, termine con Ctrl+Shift+Entery complete.

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

Respuesta2

  1. Cree la tabla dinámica.

    • Resalte los datos.
    • Vaya a Insertar > Tablas > Tabla dinámica.
    • Elige dónde colocar la mesa.
    • Verifique la fecha, la tarea y el recuento.
    • Arrastre "Fecha" a Filas, "Tarea" a Columnas y "Contar" a Valores. Y elija Suma de recuento (si aún no lo ha hecho).

  2. Ordene las fechas (etiquetas de fila) de forma descendente.

  3. Cree sumas acumulativas: en F3, escriba la fórmula =SUM(B$3:B3). Y extender hasta H8.
  4. Cree valores booleanos para indicar cuando la suma es al menos 3: en I3, escriba la fórmula =F3>=3. Y ampliar hasta K8.
  5. Repita las fechas, porque BUSCARV requiere que la búsqueda se realice hacia la derecha: en L3, escriba la fórmula =$A3. Y ampliar hasta N8.
  6. Crea las BUSCARV. En I9, escriba la fórmula =VLOOKUP(TRUE, I3:L8,4,FALSE). Ampliar a K9

La respuesta está en I9 a K9.

Esta solución ocupa muchas celdas, pero es una solución fácil de configurar. Tenga en cuenta que en lugar de extender la fórmula del paso 5 a N8, se podría simplemente cambiar BUSCARV en J9 para hacer referencia a la tercera columna y BUSCARV en K9 para hacer referencia a la segunda columna.

Hoja de cálculo con valores:

Hoja de cálculo con valores

Hoja de cálculo con fórmulas:

Hoja de cálculo con fórmulas

Respuesta3

Una combinación de mi respuesta anterior y la respuesta de Rajesh S.

  1. Crea una suma acumulada con respecto a cada tarea: en Cell D2escribe =SUMIF($B2:B$9, B2, $C2:C$9) >= 3y completa hasta Cell C9.

  2. Encuentre la fecha máxima para cada tarea que se marcó como TRUEen el Paso 1: en Celda D11escriba la siguiente fórmula matricial (y presione Ctrl+Shift+Enter):

    =MAX(SI(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

  3. Llénelo hasta D13.

Observación: Las tareas deben estar en orden ascendente por fecha para que la solución funcione.

Hoja de cálculo con fórmulas

información relacionada