在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屋！