英文:
How to categorize data based on values in Excel?
问题
如果所有员工的得分都小于3，风险就很高；如果有一个员工的得分为3或4，风险就是中等的；如果有多个员工的得分为3或4，风险就很低。
例如：
表格：
| 员工 | 技术1 | 技术2 | 技术3 |
|---|---|---|---|
| 约翰 | 1 | 1 | 1 |
| 道格 | 3 | 3 | 2 |
| 亨利 | 4 | 2 | 2 |
| 乔 | 2 | 2 | 2 |
| 弗伦 | 1 | 1 | 1 |
| 夏绿蒂 | 1 | 1 | 1 |
| 玛丽 | 2 | 2 | 2 |
| 迈克尔 | 3 | 1 | 1 |
| 克林特 | 1 | 1 | 1 |
| 西尔维斯特 | 1 | 1 | 1 |
| 风险结果 | 低 | 中等 | 高 |
英文:
If all employees have scores less than 3, the risk is high, if I have one employee with a score of 3 or 4, the risk is medium, if we have several employees with a score of 3 or 4, the risk is low
for example:
Table:
| Employees | Technology 1 | Technology 2 | Technology 3 |
|---|---|---|---|
| John | 1 | 1 | 1 |
| Doug | 3 | 3 | 2 |
| Henry | 4 | 2 | 2 |
| Jo | 2 | 2 | 2 |
| Fren | 1 | 1 | 1 |
| Charlize | 1 | 1 | 1 |
| Mary | 2 | 2 | 2 |
| Michael | 3 | 1 | 1 |
| Klint | 1 | 1 | 1 |
| Silvester | 1 | 1 | 1 |
| Output Risk | Low | Medium | High |
答案1
得分: 2
• 用于单元格 B12 的公式：
=LET(a,B2:D11,BYCOL(a,LAMBDA(x,
LET(y,SUM(COUNTIFS(x,{3,4})),IFS(y=1,"中等",y>1,"低",y=0,"高")))))
非MS365 - 用户可能需要在退出编辑模式时按<kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd>。
=IF(
SUM(
COUNTIFS(
B2:B11, {3, 4}
)
) = 1,
"中等",
IF(
SUM(
COUNTIFS(
B2:B11, {3, 4}
)
) > 1,
"低",
"高"
)
)
替代方法，不使用<kbd>LAMBDA( )</kbd> --> 使用<kbd>MMULT( )</kbd>
• 用于单元格 B12 的公式：
=LET(
a,B2:D11,
b,MMULT(SEQUENCE(,ROWS(a),,0),(a=3)+(a=4)),
IFS(b=0,"高",b=1,"中等",b>1,"低"))
英文:
Another alternative approach, works with MS365
• Formula used in cell B12
=LET(a,B2:D11,BYCOL(a,LAMBDA(x,
LET(y,SUM(COUNTIFS(x,{3,4})),IFS(y=1,"Medium",y>1,"Low",y=0,"High")))))
Non-MS365 - Users might need to hit the <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> while exiting the edit mode.
=IF(
SUM(
COUNTIFS(
B2:B11, {3, 4}
)
) = 1,
"Medium",
IF(
SUM(
COUNTIFS(
B2:B11, {3, 4}
)
) > 1,
"Low",
"High"
)
)
Alternative approaches, without using <kbd>LAMBDA( )</kbd> --> Using <kbd>MMULT( )</kbd>
• Formula used in cell B12
=LET(
a,B2:D11,
b,MMULT(SEQUENCE(,ROWS(a),,0),(a=3)+(a=4)),
IFS(b=0,"High",b=1,"Medium",b>1,"Low"))
答案2
得分: 1
使用COUNTIF函数：
=LET(
rng, B2:B11,
fr, COUNTIF(rng, 4),
th, COUNTIF(rng, 3),
IF(fr + th = 1, "medium", IF(th + fr > 1, "low", "high"))
)
对于旧版本：
=IF(COUNTIF(B2:B11, 4) + COUNTIF(B2:B11, 3) = 1, "medium", IF(COUNTIF(B2:B11, 3) + COUNTIF(B2:B11, 4) > 1, "low", "high"))
请注意，这些公式中的引号已经被适当转义，以便在Excel中正常使用。
英文:
use COUNTIF:
=LET(
rng,B2:B11,
fr,COUNTIF(rng,4),
th,COUNTIF(rng,3),
IF(fr+th=1,"medium",IF(th+fr>1,"low","high")))
For older versions:
=IF(COUNTIF(B2:B11,4)+COUNTIF(B2:B11,3)=1,"medium",IF(COUNTIF(B2:B11,3)+COUNTIF(B2:B11,4)>1,"low","high"))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论