查找包含多行和多列的范围内最频繁出现的文本 [英] Find most frequently occuring text in range that consist of multiple rows and colums
问题描述
我的问题与以下主题密切相关:
My question is closely related to the following topics:
- Excel: Find most frequent occurring value in a range?
- https://exceljet.net/formula/most-frequently-occurring-text
我想检索在我的范围内最频繁出现的文本.问题在于该范围由多个行和/或列组成.对于单行/单列，Excel公式为
I want to retrieve the text that occurs the most frequent within my range. The problem is that the range consists of multiple rows and/or columns. In the case of a single row / single column, the Excel formula is
=INDEX(D2:D9,MODE(MATCH(D2:D9,D2:D9,0)))
我知道这个公式是正确的，因为我在单元格N2中获得"Inter"作为输出.如果我想最频繁地使用 Range("D2:D9").
I know that this formula is correct because I get "Inter" as output in cell N2. This is indeed the correct output if I want the most frequent term of Range("D2:D9").
但是，我想找到 Range("D2:E3")的术语#1.在公式中包含此范围会产生错误.请参阅附件.
However, I want to find the term #1 of Range("D2:E3"). Incorporating this range in the formula, gives an error. See attachment.
总结一下，正确的Excel公式是什么，该公式向我显示由多个列和行组成的区域中最频繁出现的文本? 谢谢
Summarized, what is the correct Excel-formula that shows me the most frequent text of a range that consists of multiple columns and rows? Thank you
推荐答案
=INDIRECT(TEXT(MMULT(CHOOSE({1,2},ROW(MyRange),COLUMN(MyRange))+MOD(ROUNDUP(MATCH(1,0/FREQUENCY(0,1/(1+COUNTIF(MyRange,MyRange))))/COLUMNS(MyRange)^{1,0},0)-1,COLUMNS(MyRange)*ROWS(MyRange)^{1,0}),10^{5;0}),"R0C00000"),0)
根据需要替换MyRange.
请注意，如果您未使用英语版本的Excel，则上述部分可能需要修改(数组常量中的分隔符-{1,2}，{1,0}和{5;0}-以及部分"R0C00000"是两个这样的例子.
Note that, if you are not using an English-language version of Excel, parts of the above may require amending (the separators within the array constants - {1,2}, {1,0} and {5;0} - and the part "R0C00000" being two such examples).
以上内容为过大；我们可以简单地使用它(通过CTRL + SHIFT + ENTER):
=INDIRECT(TEXT(MIN(IF(COUNTIF(Rng,Rng)=MAX(COUNTIF(Rng,Rng)),10^5*ROW(Rng)+COLUMN(Rng))),"R0C00000"),0)
致谢
这篇关于查找包含多行和多列的范围内最频繁出现的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
