Obtener un valor del registro anterior más reciente donde aparece texto en cualquiera de las dos columnas

Question

Estoy intentando automatizar más completamente el cálculo de valores y datos resumidos en una hoja de cálculo que guardo sobre los resultados de los partidos en una liga de grupo.

Tengo una tabla con mucha información sobre cada partido, con los campos relevantes: Fecha del partido, Ganador, Hándicap inicial del ganador, Hándicap final del ganador, Perdedor, Hándicap inicial del perdedor, Hándicap final del perdedor, Hora de inicio del partido.

Los handicaps se ajustan al final de cada partido y antes del siguiente. Es complicado encontrar el récord anterior más reciente de un jugador (podría haber sido ganador o perdedor) y copiar su hándicap final de ese registro al hándicap inicial (ganador o perdedor) del que estoy ingresando ahora.

Me gustaría una fórmula que encontrara el récord más reciente (fecha y hora de inicio más altas en caso de que jugara dos veces en un día) donde fue ganador o perdedor, y luego obtener el hándicap final (del respectivo ganador o perdedor). ).

Según la sugerencia de Teylyn, aquí hay un enlace de Dropbox al archivo. La pestaña correspondiente es Resultados del partido:https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0

Agregué una columna L en blanco para probar cosas, comparando los resultados con lo que hay en K para ver si estaban funcionando, por eso está ahí. Olvidé eliminarlo cuando lo puse en Dropbox.

Answer 1

Planteamiento del problema

Una hoja de trabajo tiene nombres en Columnas Ey X. Para cada filan, ≠ . Hay números en Column correspondientes a los nombres en Column y números en Column correspondientes a los nombres en Column . Para cualquier fila después de la primera (digamos la fila 42), queremos obtener valores para y de las filas anteriores, si es posible.EnXnMEAGXK42AF42

• Si E42es "John", busque la fila más reciente que contenga "John" (en la columna Eo X). llama a esa filan. Si = “John”, igual a . Si = “John”, igual a .EnK42MnXnK42AGn

• Si X42es "Scott", busque la fila más reciente que contenga "Scott" (en la columna Eo X). llama a esa filan. Si = “Scott”, igual a . Si = “Scott”, igual a .EnAF42MnXnAF42AGn

                 

Solución

Con la esperanza de preservar algo de cordura, usemos columnas auxiliares; digamos ARy AS. Supongamos que (como en el archivo de ejemplo), los datos comienzan en la fila 2. Ingrese

=MAX(($E$2:$E2=$E3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$E3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

en AR3(saltar AR2). Termine con Ctrl+ Shift+ Enter, para convertirla en una fórmula matricial. Asimismo, establezca AS3en

=MAX(($E$2:$E2=$X3)*(100*ROW($E$2:$E2)+COLUMN($M:$M)), ($X$2:$X2=$X3)*(100*ROW($X$2:$X2)+COLUMN($AG:$AG)))

como una fórmula matricial. (Esto es lo mismo AR3 excepto que las dos apariciones de $E3han sido reemplazadas por $X3.)

Ajustado K3a

=IF($AR3=0, "?", INDEX($A$1:$BG$999, INT($AR3/100), MOD($AR3,100)))

y AF3para

=IF($AS3=0, "?", INDEX($A$1:$BG$999, INT($AS3/100), MOD($AS3,100)))

(no como fórmulas matriciales). Son iguales excepto que las tres apariciones de $AR3han sido reemplazadas por $AS3.

Y, por supuesto, arrastrar/rellenar.

Las columnas auxiliares encuentran las apariciones anteriores más recientes de los nombres ( busca la aparición anterior más reciente de y encuentra la aparición anterior más reciente de ) básicamente encontrando el máximo, en las filas anteriores, deARnEnASnXn

(valor anterior=este valor) * FILA())

es decir, el número de fila más alto donde el nombre coincide. Luego codifica la ubicación donde se encontró el nombre como

100*FILA() + COLUMNA(datos_que_queremos_copiar)

Ambas fórmulas buscan tanto en Columnas Ecomo en y devuelven las coordenadas codificadas de las Columnas o celdas Xcorrespondientes . Luego, las fórmulas y simplemente decodifican la dirección de la celda y recuperan el valor.MAGKAF

               

También lo AR6es 213 porque "John" ( E6) fue visto por última vez en la fila 2 y, dado que fue visto en E2(en lugar de X2), queremos copiar el valor de la columna 13 (columna M).