如何在电子表格中输出最常见的值和该值的出现次数? [英] How to output the most common value and the number of occurrences of that value in spreadsheet?
问题描述
有一列值
,数字字符串
,然后显示最常见的值和数字该值的出现(即 Strings
的模式)。这是一个例子。
+ -------- +
|值|
+ -------- +
| AA |
+ -------- +
| BB |
+ -------- +
| AA |
+ -------- +
| AA |
+ -------- +
| GG |
+ -------- +
| DD |
+ -------- +
| DD |
+ -------- +
| |
+ ----------------- + -------- +
|最常见的| AA |
+ ----------------- + -------- +
|次数| 03 |
+ ----------------- + -------- +
这将在Google Spreadsheets中完成!
任何提示?
对于您的具体示例,让它成为列A,因此您有A1 ='AA ',A2 ='BB',...,A7 ='DD'。
要查找最大元素发生次数,我们要计算每个唯一的元素,然后返回最大计数,因此在单元格中使用公式
= MAX(COUNTIF(A1:A7,A1: A7))
这是一个ARRAY公式,所以在excel中你必须按Ctrl + Shift + Enter用它。要在Google电子表格中使用,请使用ARRAYFORMULA将其包围,以使其成为
= ARRAYFORMULA(MAX(COUNTIF(A1:A7,A1: A7)))
说明:内部countif计算A1:A7的单元格,如果它们相等到A1:A7中的每个值,并将它们放在列表中。 Max返回该列表中的最大值。
现在,要获取实际元素,我们有另一个ARRAY公式。我们可以做一个索引/匹配查找来计算出这个值,所以在函数的内部,max找到最大计数的值,然后被传递给一个索引+匹配函数来找到原始列表中的值< (A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7))),COUNTIF(A1:A7) A7,A1:A7),0))
所以谷歌电子表格
= ARRAYFORMULA(INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7))),COUNTIF(A1:A7,A1 :A7),0)))
您将A1:A7的每个实例替换为你的资料
这篇文章是有帮助的:
http://www.mrexcel.com/forum/excel-questions/34530-mode-text-strings.html
There is a column Values
with 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 |
+--------+
| AA |
+--------+
| BB |
+--------+
| AA |
+--------+
| AA |
+--------+
| GG |
+--------+
| DD |
+--------+
| DD |
+--------+
| |
+-----------------+--------+
| Most Common | AA |
+-----------------+--------+
| Number of times | 03 |
+-----------------+--------+
This will be done in Google Spreadsheets! Any tips?
For your specific example, let that be column A, so you have 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
=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.
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
=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.
This post was helpful: http://www.mrexcel.com/forum/excel-questions/34530-mode-text-strings.html
这篇关于如何在电子表格中输出最常见的值和该值的出现次数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!