问题描述
IFERROR(INDEX($I$7:$I,MODE(IF($I$7:$I<>"",MATCH($I$7:$I,$I$7:$I,0)))),"No data")
使用此公式来计算最常见的文本值，我需要拥有第二最常见的值.
With this formula, which calculates the most common text value, I need to have the 2nd most common.
第一列内容:
橙色
苹果
苹果
橙色
在此示例中，我需要获取橙色.那怎么可能?我不知道怎么办.
In this example, I need to get Orange. How is that possible? I can't figure how.
推荐答案
您可以使用数组公式提取列表中最频繁使用的项目.
You can extract the most frequent item in the list with an array formula.
=INDEX(MyList,MATCH(MAX(COUNTIF(MyList,MyList)),COUNTIF(MyList,MyList),0))
请注意，必须使用Shift + Ctl + Enter确认数组公式，而不是正常公式所需的习惯性单数Enter.如果输入错误，它将显示一个#NUM！错误.
Note that an array formula must be confirmed with Shift+Ctl+Enter instead of the customary singular Enter required for normal formulas. When entered wrongly it will display a #NUM! error.
为简单起见，我在公式中使用了命名范围MyList.但是，如果愿意，可以将名称替换为$ I $ 7:$ I $ 1000.
For simplicity's sake I have used a named range MyList in the formula. However, if you prefer, you can replace the name with something like $I$7:$I$1000.
要提取列表中第二频繁出现的表达式，可以使用与上述类似的公式构建.
To extract the second-most frequent expression in the list you could use a formula constructed analogue to the above.
=INDEX(MyList,MATCH(LARGE(COUNTIF(MyList,MyList),MAX(COUNTIF(MyList,MyList))+1),COUNTIF(MyList,MyList),0))
此公式基于n等于最高出现次数的逻辑.因此，第二高必须为n + 1，即上式中的MAX(COUNTIF(MyList,MyList))+1).用同样的方法可以提取第三名.
This formula is built on the logic that n equals the highest number of occurrences. Therefore the second highest must rank as n + 1, being MAX(COUNTIF(MyList,MyList))+1) in the above formula. By the same method the third ranked could be extracted.
您可以将这些公式嵌入IFERROR()函数中.
You can embed these formulas in an IFERROR() function.
这篇关于模式文字第二个最常用的文字值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！