string 如何在电子表格中输出最常见的值和该值的出现次数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18070201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to output the most common value and the number of occurrences of that value in spreadsheet?
提问by ricardocaldeira
There is a column Valueswith a number of Strings, then show below the most common value and the number of occurrences of that value (i.e. mode of Strings). Here's an example.
有一列Values有Strings，然后在下面显示最常见的值和该值的出现次数（即模式Strings）。这是一个例子。
+--------+
| Values |
+--------+
| AA |
+--------+
| BB |
+--------+
| AA |
+--------+
| AA |
+--------+
| GG |
+--------+
| DD |
+--------+
| DD |
+--------+
| |
+-----------------+--------+
| Most Common | AA |
+-----------------+--------+
| Number of times | 03 |
+-----------------+--------+
This will be done in Google Spreadsheets! Any tips?
这将在 Google 电子表格中完成！有小费吗？
回答by chiliNUT
For your specific example, let that be column A, so you have A1='AA', A2='BB',...,A7='DD'.
对于您的具体示例，将其设为 A 列，因此您有 A1='AA', A2='BB',...,A7='DD'。
To find the number of times the max element occurs, we want to count each unique element, then return the max count, so in a cell use the formula
要找到最大元素出现的次数，我们要对每个唯一元素进行计数，然后返回最大计数，因此在单元格中使用公式
=MAX(COUNTIF(A1:A7,A1:A7))
This is an ARRAY formula, so in excel you must hit Ctrl+Shift+Enter to use it. To use in google spreadsheets, surround it with ARRAYFORMULA so it becomes
这是一个数组公式，因此在 Excel 中您必须按 Ctrl+Shift+Enter 才能使用它。要在 google 电子表格中使用，请用 ARRAYFORMULA 将其括起来，使其变成
=ARRAYFORMULA(MAX(COUNTIF(A1:A7,A1:A7)))
Explanation: the inside countif counts the cells of A1:A7, if they are equal to each value in A1:A7, and puts them in a list. Max returns the max value in that list.
说明：内部countif对A1：A7的单元格进行计数，如果它们等于A1：A7中的每个值，则将它们放入一个列表中。Max 返回该列表中的最大值。
Now, to get the actual element, we have another ARRAY formula. We can do an index/match lookup to figure out the value, so on the inside of the function, max finds the value with the greatest count, then that gets passed to an index+match function to find the value in the original list
现在，为了获得实际元素，我们有另一个 ARRAY 公式。我们可以进行索引/匹配查找来找出值，因此在函数内部，max 找到计数最大的值，然后将其传递给 index+match 函数以查找原始列表中的值
=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))
and so for google spreadsheets
谷歌电子表格也是如此
=ARRAYFORMULA(INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0)))
you replace each instance of A1:A7 with the actual range of your data.
您用数据的实际范围替换 A1:A7 的每个实例。
This post was helpful: http://www.mrexcel.com/forum/excel-questions/34530-mode-text-strings.html
这篇文章很有帮助：http: //www.mrexcel.com/forum/excel-questions/34530-mode-text-strings.html
回答by Pat Mustard
You could create a map with a string and a counter and increment the counter on each occurrence of the string. I don't know java script but something like the following sudocode should work to count the number of occurrences:
您可以创建一个带有字符串和计数器的映射，并在每次出现字符串时增加计数器。我不知道 java 脚本，但像下面的 sudocode 应该可以计算出现的次数：
Dictionary<string, int> _map;
foreach(cell in sheet.cells)
{
if(_map.contains(cell.value) == FALSE)
{
_map.add(cell.value)
{
_map.item(cell.value) += 1 // increment number of occurrences
}
After this you should loop to find the largest number, store its associated string and find the number associated with the string of the largest number.
在此之后，您应该循环查找最大数，存储其关联的字符串并找到与最大数的字符串关联的数。
回答by plmorgan
To get this working in Google Spreadsheet the above didn't work, returning an Out of Range Error instead. I had to modify the formatting a little. This is what worked;
要使其在 Google 电子表格中工作，上述方法不起作用，而是返回超出范围错误。我不得不稍微修改格式。这是有效的；
=index(G14:ZZ14;;(MATCH(MAX(COUNTIF(G14:ZZ14,G14:ZZ14)),COUNTIF(G14:ZZ14,G14:ZZ14),0)))
Replace the 5 references to G14:ZZ14 with your range.
用您的范围替换对 G14:ZZ14 的 5 个引用。
