Comment trouver les plus grandes valeurs de cellules dans plusieurs colonnes dans Excel ?

L'utilisation d'un critères stratégie de combinaison de pondération des statistiques les plus significatives (@fixer1234), donne les choix de formules suivants.

Cette formule fonctionne lorsque

• L'égalité est impossible après l'application du troisième critère.

  =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, 0))

• Les noms sont uniques (ou les noms correspondants ne correspondent pas aux trois critères).

  =INDEX( $C$2:$C$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $D$23:$D23, $C$2:$C$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))

• Toujours, en renvoyant les résultats sous forme d'ID d'utilisateur (colonne d'aide commençant par E24 ).

  =INDEX( $B$2:$B$20, MATCH( LARGE( $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, $B24), IF( NOT( COUNTIF( $E$23:$E23, $B$2:$B$20)), $K$2:$K$20*10^6+$J$2:$J$20*10^3+$I$2:$I$20, ""), 0))

• Pour le nom, utilisez =VLOOKUP($E24,$B$2:$C$20,2) donde $E24 est l'identifiant de l'utilisateur renvoyé par la formule.

Comment l'utiliser

• Quelle que soit la formule principale choisie, collez-la sur la première ligne des résultats. Comme il s'agit de formules CSE, après avoir saisi la formule ou apporté des modifications, appuyez sur Ctrl - Shift - Enter .
• Glisser Copier la formule vers le bas. Ces formules ne renvoient pas de tableau/plage et ne fonctionneront pas si elles sont saisies en tant que formule de tableau avec les 5 cellules sélectionnées.
• Les deux $E$23:$E23 dans cette formule et $D$23:$D23 dans la formule des noms uniques ; sont des plages d'expansion qui commencent dans la cellule située au-dessus de la première ligne de la formule. Cette première cellule référencée de l'intervalle d'expansion ne doit correspondre à aucune ligne de la colonne ids ou de la colonne names, respectivement.

Je l'utiliserais :

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),2)

pour les noms et :

=INDEX(FILTER(SORTBY(A1:K20,K1:K20,-1,J1:J20,-1,I1:I20,-1),SORT(K1:K20,,-1)>=MIN(LARGE(K1:K20,SEQUENCE(5)))),SEQUENCE(5),11)

pour les scores de la colonne K.

Trier les données à l'aide d'une formule en utilisant SORTBY() et utiliser cet ensemble de données triées pour FILTER() La plage d'entrée de l Cela permet aux données de suivre vos égalités sans effort réel et, bizarrement (toutes les choses bizarres avec FILTER() obtient "bizarrement" comme descripteur), en utilisant MIN() ne fonctionnera pas (comme je l'ai écrit) sans le tri de configuration. C'est pourquoi le SORTBY() avant de transmettre la plage de données à FILTER() .

Pour les critères de la FILTER() pour que la fonction fonctionne, la plage de critères doit également être triée avant d'être paramétrée. >= aux cinq premiers résultats de la colonne K.

Pour une raison que je n'ai pas cherché à comprendre, mais plutôt à surmonter, la comparaison directe des scores de la colonne K avec les cinq plus grandes valeurs de la colonne K n'a pas fonctionné. Mais en utilisant LARGE() pour FIND les cinq premiers, puis MIN() pour sélectionner la plus petite d'entre elles, puis en comparant les valeurs de la colonne K triée comme suit FILTER() Le critérium de l'Union européenne a fonctionné. Vous voyez donc.

Cela a produit, en interne dans Excel, et non comme résultat, un tableau des valeurs du tableau dans l'ordre souhaité et coupé en cinq lignes. L'utilisation de INDEX() a permis de sélectionner la colonne d'intérêt, la colonne 2 d'obtenir les noms, et la colonne 11 d'obtenir la colonne K des scores. D'où les deux formules, qui ne diffèrent que par la colonne qui se trouve à leur extrémité et qui est transmise à INDEX() pour obtenir les noms d'une colonne de votre tableau de sortie et les scores de la colonne K pour l'autre.

Gardez à l'esprit que si quelqu'un d'autre correspondait à Joe dans les trois colonnes, si cette personne était au-dessus de Joe dans l'éventail des sources, elle aurait été en position 5 et Joe aurait été en position 6. Et vice versa si cette personne avait été en dessous de Joe dans l'intervalle des sources. La formule ne gère pas du tout cette situation, et je dis bien PAS DU TOUT.

Il fait cependant ce qui est littéralement demandé.

Cela montre également qu'il n'est pas nécessaire de résoudre les difficultés en étant capable de faire ce qui a été essayé. On peut souvent se contenter de les surmonter et de passer à autre chose sans jamais savoir ce qui aurait permis à l'approche logique de fonctionner. J'aime toujours résoudre chaque chose, mais il s'agit d'un monde pratique et il est généralement plus souhaitable d'obtenir un résultat fonctionnel qu'un résultat parfait au prix d'une tonne de temps. Il faut donc souvent faire des compromis et remettre la compréhension à plus tard. Ce qui fonctionne ainsi...