在Excel中连续选择有多个获奖者的得分获奖者 [英] Pick a winner of scores from a row in Excel with multiple winners
问题描述
我有以下数据,希望自动找到中奖者.
A B C D E1 2 3 4 5
我已经使用了 INDEX
函数:
INDEX($ A $ 1:$ E $ 1,1,MATCH(LARGE($ A $ 2:$ E $ 2,1),$ A $ 2:$ E $ 2,0))
这确实告诉我赢家是E.
但是,说下一个数据集表明A和E得分相同:
A B C D E5 2 3 4 5
使用上述公式,我无法同时获得A和E作为获奖者...
是否可以嵌套IF函数并使其简洁,以便在我的获奖者栏中可以说:A,E?
即兴数组公式
复制下来的数组公式如何(例如从F2单元格开始):
= SUM(LARGE(IF(A2:E2 = MAX(A2:E2),COLUMN(A2:E2),0),COLUMN(A2:E2))* 10 ^(COLUMN(A2:E2))-1)
(请注意用大括号{}表示正确输入数组公式!)
结果返回例如 15
,即根据您的示例数据 5 2 3 4 5
(在A:E列中),第一个数字显示第一列的第一位,第二个数字显示第五列./p>
如果发生,例如A:E列中的 1 5 5 4 5
将显示► 235
,显示第二,第三和第五列为获胜.
*注意:在此示例中,由于它使用十进制数字系统,因此只能使用10个数字(10列)(可以修改).
编辑1-显示用逗号分隔的带有列字母的字符串
使用重复的 SUBSTITUTE
函数并不是最优雅的方法,但是它可以在有限的列数内完成工作:
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUM(LARGE(IF(A2:E2 = MAX(A2:E2),COLUMN(A2:E2),0)E2))* 10 ^(COLUMN(A2:E2)-1)),1,"A,"),2,"B,"),3,"C,"),4,"D,"),5,"E,")&#",,#",")
例如在 A:E
列中的 1 5 5 4 5
将在► B,C,E
中显示结果获胜的列字母为逗号分隔的字符串
编辑2-使用十六进制数的苗条替代方式(不带逗号)
这种方法利用了这样的事实:十六进制系统代码将数字> 9与字母数字字符A到F并简单地将9添加到找到的列值中:
= DEC2HEX(SUM(LARGE(IF(A2:E2 = MAX(A2:E2),COLUMN(A2:E2)+9,0),COLUMN(A2:E2)))* 16 ^(COLUMN(A2:E2)-1)))
例如通过巧妙地使用十六进制,在 A:E
列中的 1 5 5 4 5
将在► BCE
中产生数字转换.
享受它:-)
I have a set of data as per the below, want want to automatically find the winner.
A B C D E
1 2 3 4 5
I have used the INDEX
function:
INDEX($A$1:$E$1,1,MATCH(LARGE($A$2:$E$2,1),$A$2:$E$2,0))
This does indeed tell me that the winner is E.
However, say the next dataset suggests that A and E have the same score:
A B C D E
5 2 3 4 5
Using the above formula, I am unable to get both A and E as winners...
Is it possible to nest an IF function and concact, such that in my winners column it could say: A, E?
Improvised array formula
What about an array formula copied down (starting e.g. in cell F2):
=SUM(LARGE(IF(A2:E2=MAX(A2:E2),COLUMN(A2:E2),0),COLUMN(A2:E2))*10^(COLUMN(A2:E2)-1)
(Take care of the curly brackets {} indicating correct Input of an array formula!)
The result returns e.g. 15
, i.e. digit one shows the 1st and the next digit the 5th column as winning based on your example data 5 2 3 4 5
(in columns A:E).
If there occur e.g. 1 5 5 4 5
in columns A:E the result would be ► 235
revealing the 2nd, 3rd and 5th column as winning.
*Note: restricted to 10 single digits (10 columns) as it uses the decimal number system in this example (could be modified).
Edit 1 - display a comma separated string with column letters
It's not of utmost elegance to use repeated SUBSTITUTE
functions, but it does the job for this restricted number of columns:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUM(LARGE(IF(A2:E2=MAX(A2:E2),COLUMN(A2:E2),0),COLUMN(A2:E2))*10^(COLUMN(A2:E2)-1)),1,"A,"),2,"B,"),3,"C,"),4,"D,"),5,"E,")&"#",",#","")
The occurance of e.g. 1 5 5 4 5
in columns A:E
would result here in ► B,C,E
displaying the winning column letters as comma separated string.
Edit 2 - slim alternative using hexadecimal number system (without commas)
This approach uses the fact that the hexadecimal system codes numbers > 9 with alphanum characters A to F and simply adds 9 to the found column values:
=DEC2HEX(SUM(LARGE(IF(A2:E2=MAX(A2:E2),COLUMN(A2:E2)+9,0),COLUMN(A2:E2))*16^(COLUMN(A2:E2)-1)))
The occurance of e.g. 1 5 5 4 5
in columns A:E
would result here in ► BCE
via a tricky use of hexadecimal number conversion.
Enjoy it :-)
这篇关于在Excel中连续选择有多个获奖者的得分获奖者的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!