动态领导板在Excel中排序 [英] Dynamic Leader Board Sorting in Excel

问题描述

我想创建一个公式来动态地将列AI作为新的AJ分数由用户输入。我只想要TOTAL/AG列作为驱动程序。我在excel中使用了小功能，但这并不能处理重复的分数。

I would like to create a formula to dynamically sort columns "AI" an "AJ" as new scores are inputted by the user. I simply want the "TOTAL" / "AG" column to be the driver of the sort. I have used the "small" function in excel, but this does not handle duplicate scores.

通过在AI和AJ列中以升序显示名称和分数，动态排序TOTAL列的最佳方式是什么？

What is the best way to dynamically sort the "TOTAL" column by displaying name and score in ascending order in columns AI and AJ?

推荐答案

我想你使用这样的公式来获得排序的分数

I guess you're using a formula like this to get the sorted scores

=SMALL(AG$11:AG$23,ROWS(AG10:AG$10))

如果您在AG中有未分类的分数，AH中的字母和AI中的排序分数，请在AJ中尝试此操作

If you have the unsorted scores in AG, letters in AH and sorted scores in AI, try this in AJ

=INDEX(AH$11:AH$23,SMALL(IF(AG$11:AG$23=AI11,ROW(AG$11:AG$23)-ROW(AG$10)),1+COUNTIF(AI$10:AI10,AI11)))

这是假设分数从第11行开始，在第23行完成，需要调整（如从屏幕截图所示），分数在第三行。

This is assuming scores start in row 11 and finish in row 23, but would need adjusting if (as appears from your screenshot) the scores are in every third row.

必须使用 Ctrl Shift 输入

以下是AI的修改公式，假设分数为第三行

Here is the modified formula for AI assuming scores are every third row

=IF(MOD(ROWS(AG$8:AG10),3),"",SMALL(AG$11:AG$47,ROWS(AG$8:AG10)/3))

这是修改后的公式AJ

and this is the modified formula for AJ

=IF(AI11="","",INDEX(AH$11:AH$47,SMALL(IF(AG$11:AG$47=AI11,ROW(AG$11:AG$47)-ROW(AG$10)),1+COUNTIF(AI$10:AI10,AI11))))

这篇关于动态领导板在Excel中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！