英文:
get best round score ( max score and min second) in excel
问题
我有一个表格包括7列：（姓名，得分1，时间1，得分2，时间2，得分3，时间3）。
我想选择最佳回合得分，首先找到最佳得分（最高分），如果有2轮包括最高分，则选择包括最佳时间（最短时间）的轮次。
如下表（获取最终得分和最终时间）：
| 姓名 | 得分1 | 时间1 | 得分2 | 时间2 | 得分3 | 时间3 | 最终得分 | 最终时间 |
|---|---|---|---|---|---|---|---|---|
| 姓名1 | 100 | 20 | 90 | 10 | 100 | 19 | 100 | 19 |
| 姓名2 | 10 | 40 | 50 | 15 | 25 | 2 | 50 | 15 |
在“max”公式中，我们有一个条件，我不知道如何做到这一点...
英文:
I have a sheet that include 7 columns: ( name , score1 , time1, score2 , time2 , score3 , time3).
I want to select best round score provided that first find best score( max score), if there are 2 rounds include max score , then select the rounds that include best time ( min time).
like below table ( get final score and final time):
| name | score1 | time1 | score2 | time2 | score3 | time3 | final_score | final_time |
|---|---|---|---|---|---|---|---|---|
| name 1 | 100 | 20 | 90 | 10 | 100 | 19 | 100 | 19 |
| name 2 | 10 | 40 | 50 | 15 | 25 | 2 | 50 | 15 |
In max formula we have one condition and I don't know how can I do this...
答案1
得分: 2
欢迎来到SO。
Best Score 很容易，因为它总是对应列的最大分数。棘手的部分是与特定最高分相关的时间。我已经使用标题值来定位基于您的输入的时间列
最佳分数的公式是：=MAX(B2;D2;F2)
最佳时间的公式是：=MIN(IF(B2:F2=$I2;IF(LEFT($C$1:$G$1;4)="time";C2:G2)))
只需向下拖动。
注意：最佳时间的公式是数组公式，因此必须使用<kbd>ENTER</kbd>+<kbd>CTRL</kbd>+<kbd>SHIFT</kbd>进行输入，否则不会起作用！
另外，请注意我的参数分隔符是;，所以您可能需要调整这部分。
英文:
Welcome to SO.
Best Score it's easy because it will always be the max score of the corresponding columns. The tricky part is the time associated to a specific max score. I've used the headers value to locate the time columns based on your input
Formula for best score is: =MAX(B2;D2;F2)
Formula for best time is: =MIN(IF(B2:F2=$I2;IF(LEFT($C$1:$G$1;4)="time";C2:G2)))
Just drag down.
NOTE: The formula for best time is an array formula so it must be entered using <kbd>ENTER</kbd>+<kbd>CTRL</kbd>+<kbd>SHIFT</kbd> or it won't work!
Also, notice my argument separator is ; so you may need adapt that part
答案2
得分: 1
=MAX(FILTER(A2:G2,LEFT($A$1:$G$1, 5) ="score" ))
=SMALL(FILTER(A2:G2,LEFT($A$1:$G$1, 4) ="time" ),2)
英文:
If you have Excel 365 you can use these formulas:
=MAX(FILTER(A2:G2,LEFT($A$1:$G$1, 5) ="score" ))
=SMALL(FILTER(A2:G2,LEFT($A$1:$G$1, 4) ="time" ),2)
FILTER returns the according values for score or time.
Then MAX and SMALL are applied to achieve the results you need.
If you don't have Excel 365 you will have to select each column individually for the first range:
=MAX(B2,D2,F2)
=SMALL((C3,E3,G3),2)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论