☰

Elija los 3 valores principales y súmelos

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007 power-query
Elija los 3 valores principales y súmelos

Digamos que tengo algunos datos que se ven así:

PLAYER    |Team      |Points
Smith     |Suns      |25
Jones     |Suns      |15
Martin    |Suns      |23
Chen      |Suns      |3
Williams  |Suns      |17
Quill     |Marvel    |40
Banner    |Marvel    |1
Stark     |Marvel    |1
Odinson   |Marvel    |1
Parker    |Marvel    |3
Curly     |Spurs     |2
Franke    |Spurs     |5
Wayne     |Spurs     |23
Weasley   |Wizards   |21
Potter    |Wizards   |19
Granger   |Wizards   |15
Thompson  |Bobcats   |12
Boehme    |Bobcats   |13

Si quisiera escribir una fórmula para sumar los 3 mejores puntajes de cada equipo, ¿cómo lo haría?

Por ejemplo, devolvería estos datos:

Team   |Points
Suns   |65
Marvel |44
Spurs  |30
Wizards|55
Bobcats|25

He considerado usar un filtro para decir "si el valor es mayor que el valor medio de los miembros del equipo" o algo así, pero si hubiera menos de 3 miembros eso no funcionaría.

También lo he considerado sumif, pero no creo que sea apropiado por la misma razón.

También estoy buscando encontrar la suma de las puntuaciones de los 3 MEDIOS, pero estoy seguro de que si recibo consejos para encontrar los 3 primeros, puedo descubrir cómo adaptarlo para los 3 medios.

¿Es esto algo en lo que Power Query podría resultar útil? He incursionado, pero no soy un profesional. (Si fuera absolutamente necesario, probablemente también podría preparar algo en VBA, pero prefiero no seguir ese camino si es posible, ya que quiero ponerlo en una hoja de Google para compartirlo en la web eventualmente).

Respuesta1

Ordena tus datos por equipo y puntúa de forma descendente. Luego use esta fórmula que encuentra el primero de cada equipo y suma los siguientes tres puntajes o el número de puntajes de ese equipo si es menor:

=SUM(INDEX(C:C,MATCH(F2,B:B,0)):INDEX(C:C,MATCH(F2,B:B,0)+MIN(COUNTIF(B:B,F2)-1,2)))

ingrese la descripción de la imagen aquí

Respuesta2

Esta fórmula sumará los 3 mejores puntajes de cada equipo:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E2,C$2:C$19),{1,2,3}),""))

Es una fórmula matricial, por lo que se debe ingresar con CTRLShiftEnter, en lugar de solo Enter.

Los resultados se muestran a continuación:

ingrese la descripción de la imagen aquí

Cómo funciona: Devuelve IF()una lista de los puntos para el equipo enumerado en la columna E. Luego LARGE()toma solo los 3 puntajes principales. IFERROR()maneja los casos en los que hay menos de 3 puntuaciones. Finalmente, SUM()los suma.

EDITAR: Para obtener la suma de las 3 puntuaciones del medio, intenté usar la forma de referencia de INDEX() con matrices literales y una fórmula para elegir el "area_num":

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX(({1,2,3},{2,3,4}),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Pero no aceptaría las constantes de la matriz como referencia. Finalmente logré que funcionara usando una columna auxiliar para especificar las matrices:

=SUM(IFERROR(LARGE(IF(B$2:B$19=E11,C$2:C$19),INDEX((H$1:H$3,H$2:H$4),,,INT(COUNTIF(B2:B19,E11)/2))),""))

Respuesta3

Mi enfoque es sólo ligeramente diferente para obtener la suma de las 3 puntuaciones principales y las 3 intermedias.

ingrese la descripción de la imagen aquí

  1. Ordené los datos de origen en Nombre del equipo como campo principal en orden ascendente y en Puntos como campo secundario en orden descendente. (Para permitir calcular la suma de los 3 primeros puntajes manualmente).
  2. Utilicé una fórmula para generar la lista de equipos en orden ascendente y descendente.
  3. Finalmente, calculé la suma de las 3 puntuaciones superiores y las 3 intermedias, utilizando una fórmula de una de las respuestas anteriores.

Aquí están las fórmulas para:

Lista de equipos en orden ascendente:

{=INDEX($D$216:$D$233, MATCH(0, COUNTIF($J$215:J215, $D$216:$D$233), 0))}

Lista de equipos en orden descendente:

  =IFERROR(LOOKUP(2,1/(COUNTIF($G$215:G215,$D$216:$D$233)=0),$D$216:$D$233),"")

Suma de las 3 mejores puntuaciones de los equipos en orden ascendente:

{=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{1,2,3}),0))}

Suma de las 3 puntuaciones del medio para los equipos en orden ascendente:

=SUM(IFERROR(LARGE(IF(($D$216:$D$233=J216),$E$216:$E$233),{2,3,4}),0))

Consulte la Nota 2 a continuación para obtener una explicación de esta fórmula.

Notas:

  1. Reemplace J216 con G216 para obtener las 3 puntuaciones superiores e intermedias para los equipos en orden descendente.
  2. Dado que el recuento máximo de equipos es 5, he asumido que los 3 puntajes del medio son los números 2, 3 y 4.Esto elimina la puntuación más alta del total, incluso si solo hay 2 o 3 puntuaciones.. Si crees que un equipo con sólo 2 o 3 puntos debería tenertodoLos puntajes contribuyen al total, luego considere una de las respuestas anteriores.

información relacionada