Tengo una tabla simple que se ve así:
Todo lo que necesito es SUMAR puntos para un jugador específico (John) en sus últimos 3 partidos.
Pude venir con esta fórmula:
SUMPRODUCT(LARGE((A2:B="John")*(C2:D);{1;2;3}))
El problema es que, en lugar de lo que estaba buscando, suma los 3 valores más altos, que pueden estar en cualquier lugar de ese rango.
¿Existe alguna fórmula similar que solo pueda hacer los últimos 3 partidos?
3 respuestas
Creo que un SUMPRODUCT puede llevarlo allí con algunos arreglos construidos usando COUNTIFS () y ROW () para obtener los 3 más recientes.
Esta formula:
=SUMPRODUCT((COUNTIFS(A:B,G2,ROW(A:B)*{1,1},">="&ROW(A:B)*{1,1})<=3)*(A:B=G2),C:D)
En esta hoja que hice parece funcionar.
Acercarse
Usaría la fórmula de consulta para obtener las celdas que necesita para que pueda aprovechar la instrucción limit.
Debe colocar una columna con los índices para que pueda ordenar las celdas en orden descendente y tomar los primeros 3.
Dado que sus encabezados de tabla son:
+-----------------------------------------------+
| INDEX | NAME 1 | NAME 2 | POINTS 1 | POINTS 2 |
+-----------------------------------------------+
Usaría esta consulta para obtener el resultado deseado:
=SUMPRODUCT(QUERY(A2:E, "Select D * E where B = 'John' or C = 'John'" order by A desc limit 3"))
Creo que tengo una fórmula que te da lo que quieres. No es bonito, y estoy seguro de que se puede simplificar, pero esto funciona:
=query( query(
{ arrayformula( {ROW(A1:A) } ),
query(A1:D,"select A, B, C, D",1)
} , "select * order by Col1 desc",1),
"select Col2, Col3, Col4, Col5
where (Col2 ='John' or Col3 = 'John')
order by Col1 desc limit 3",1)
Básicamente, agrega el número de fila como una columna adicional a los datos, para que podamos ordenar los datos en orden inverso por número de fila. Luego consultamos el resultado para encontrar las tres primeras ocurrencias de 'John', en la Col A o la Col B.
Aquí hay una hoja de muestra: https://docs.google.com/ hojas de cálculo / d / 1-mhTb5Cpp3D-1OltlmCfwlmM-vc2OknHxfJAyHD7BjI / edit? usp = sharing
Gracias a Erik Tyler por una respuesta previa a una pregunta diferente, sobre cómo agregar el número de fila a una consulta.
Editar: se actualizó la hoja para proporcionar la SUMA de los puntajes de John (o de cualquier jugador) de los últimos tres partidos. Esto se puede combinar con la fórmula anterior, si desea que se coloque una sola fórmula en algún lugar. ¿O tendrá una lista de todos los jugadores y querrá sus últimos tres puntajes al lado de cada uno de sus nombres?
Si puedo simplificar la fórmula, la actualizaré aquí.
Avíseme si necesita algo más que esto, o si esto ha respondido a su pregunta.