c++中frequency_如何在Excel中使用FREQUENCY函数

c++中frequency

Excel’s FREQUENCY function lets you count how many times values fall within specific ranges. For example, if you had the ages of a group of people in your spreadsheet, you could figure out how many people fall into different age ranges. Let’s take a look at how to calculate frequency distributions and, with a slight modification, frequency percentages.

Excel的FREQUENCY函数使您可以计算值落入特定范围的次数。 例如，如果您的电子表格中有一群人的年龄，则可以算出有多少人属于不同的年龄段。 让我们看一下如何计算频率分布，以及稍加修改后的频率百分比。

频率功能有什么作用？ (What Does the FREQUENCY Function Do?)

Excel’s FREQUENCY array function lets you calculate a dataset’s frequency distribution. You provide the numerical dataset (that’s the actual cells you use as your source), a list of bin thresholds (that’s the categories into which you’re sorting data), and then press Ctrl+Shift+Enter.

Excel的FREQUENCY数组函数使您可以计算数据集的频率分布。 您提供了数值数据集(这是用作源的实际单元格)，bin阈值列表(这是将数据排序到的类别)，然后按Ctrl + Shift + Enter。

So, how might you use it? Well, here’s a quick example. Say you’re a teacher with a spreadsheet that shows all your student’s numerical test scores. You could use the FREQUENCY function to figure out how many students got an A, B, C, D, or F. The numerical test scores are the dataset and the letter grades form your bin thresholds.

那么，您将如何使用它？ 好吧，这是一个简单的例子。 假设您是一位教师，他的电子表格可以显示您所有学生的数字考试成绩。 您可以使用FREQUENCY函数来计算有多少学生获得A，B，C，D或F。数字测试分数是数据集，而字母等级则构成bin阈值。

You would apply the FREQUENCY function to a list of student’s test scores, and the function would count how many students got which letter grade by comparing each test score to the range of values that define the different letter grades.

您可以将FREQUENCY函数应用于学生的考试成绩列表，并且该函数将通过将每个考试成绩与定义不同字母成绩的值范围进行比较来计算有多少学生获得哪个字母成绩。

If you round scores to the nearest tenth of a percent, these ranges would apply:

如果将分数四舍五入到最接近的百分之一，则适用以下范围：

F <= 59.9 < D <= 69.9 < C <= 79.9 < B <= 89.9 < A

F <= 59.9 < D <= 69.9 < C <= 79.9 < B <= 89.9 < A

Excel would assign a score of 79.9 to the C range while a score of 98.2 would fall into the A range.  Excel would go through the list of test scores, categorize each score, count the total number of scores that fall into each range, and return an array with five cells showing the total number of scores in each range.

Excel会将79.9的分数分配给C范围，而98.2的分数将属于A范围。 Excel将遍历测试分数列表，对每个分数进行分类，计算落入每个范围的分数总数，然后返回一个数组，其中包含五个单元格，显示每个范围的分数总数。

The FREQUENCY function requires two arrays as inputs: a “Data_array” and a “Bins_array.”  Arrays are simply lists of values. The “Data_array” needs to contain values—like the numerical grades for students—that Excel can compare to a series of thresholds defined in the “Bins_array”—like the letter grades in that same example.

FREQUENCY函数需要两个数组作为输入：“ Data_array”和“ Bins_array”。 数组只是值的列表。 “ Data_array”需要包含值(例如学生的数字成绩)，Excel可以将其与“ Bins_array”中定义的一系列阈值进行比较(例如同一示例中的字母成绩)。

让我们看一个例子 (Let’s Look at an Example)

For our example, we will calculate the frequency distribution and frequency percentages of a set of 18 numbers between 0 and 10. It’s just a simple exercise where we’re going to determine how many of those numbers fall between one and two, between two and three, and so on.

对于我们的示例，我们将计算一组介于0和10之间的18个数字的频率分布和频率百分比。这只是一个简单的练习，我们将确定其中有多少个数字介于1和2之间，2和3之间。三，依此类推。

In our simple example spreadsheet, we have two columns: Data_array and Bins_array.

在简单的示例电子表格中，我们有两列：Data_array和Bins_array。

The “Data_array” column contains the numbers, and the “Bins_array” column contains the thresholds of the bins we will use. Note that we’ve left a blank cell at the top of the “Bins_array” column to account for the number of values in the result array, which will always contain one more value than the “Bins_array.”

“ Data_array”列包含数字，“ Bins_array”列包含我们将使用的垃圾箱的阈值。 请注意，我们在“ Bins_array”列的顶部保留了一个空白单元格，以说明结果数组中的值数量，该值将始终包含比“ Bins_array”多的一个值。

We’re also going to create a third column where our results can go; we’re naming it “Results.”

我们还将创建第三列，我们的结果可以通过该列； 我们将其命名为“结果”。

First, select the cells where you want the results to go. Now switch to the “Formulas” menu and click the “More Functions” button. On the drop-down menu, point to the “Statistical” submenu, scroll down a bit, and then click the “FREQUENCY” function.

首先，选择您想要结果去的单元格。 现在切换到“公式”菜单，然后单击“更多功能”按钮。 在下拉菜单上，指向“统计”子菜单，向下滚动一点，然后单击“频率”功能。

The Function Arguments window pops up. Click in the “Data_array” box and then highlight the cells in the “Data_array” column (you can also type the cell numbers if you prefer).

弹出功能参数窗口。 单击“ Data_array”框中，然后在“ Data_array”列中突出显示单元格(如果愿意，您也可以键入单元格编号)。

If you receive an error message saying you cannot edit only part of an array, it means you didn’t select all of the cells of the array. Click “OK” and then hit the Esc key.

如果收到错误消息，说您不能只编辑数组的一部分，则表示您没有选择数组的所有单元格。 单击“确定”，然后按Esc键。

To edit the formula of an array or delete the array, you must highlight all of the cells of the array first.

若要编辑数组的公式或删除该数组，必须首先突出显示该数组的所有单元格。

Now, click in the “Bins_array” box and then select the filled cells in the “Bins_array” column.

现在，在“ Bins_array”框中单击，然后在“ Bins_array”列中选择已填充的单元格。

Click the “OK” button.

点击“确定”按钮。

You will see that only the first cell of the “Results” column has a value, the rest are blank.

您将看到只有“结果”列的第一个单元格有一个值，其余为空白。

To see the other values, click inside the “Formula” bar and then press Ctrl+Shift+Enter.

要查看其他值，请在“公式”栏内单击，然后按Ctrl + Shift + Enter。

The Results column will now display the missing values.

结果列现在将显示缺少的值。

You can see that Excel found four values that were less than or equal to one (highlighted in red) and also found the counts of each of our other number ranges. We’ve added a “Result Description” column to our spreadsheet so that we can explain the logic Excel used to calculate each result.

您可以看到Excel找到了四个小于或等于1的值(以红色突出显示)，并且还找到了我们其他每个数字范围的计数。 我们在电子表格中添加了“结果描述”列，以便我们可以解释用于计算每个结果的Excel逻辑。

如何计算频率百分比 (How to Figure Out Frequency Percentages)

That’s all well and a good, but what if instead of raw counts in the results, we wanted to see percentages instead. What percentage of our numbers fell between one and two, for example.

一切都很好，但是如果结果中没有显示原始计数，而是希望看到百分比，该怎么办？ 例如，我们的人数百分比介于一到两个之间。

To calculate the frequency percentages of each bin, we can alter the array formula using Excel’s Function Bar. Highlight all of the cells in the “Results” column and then add the following to the end of the formula in the Function Bar:

要计算每个仓的频率百分比，我们可以使用Excel的功能栏更改数组公式。 突出显示“结果”列中的所有单元格，然后将以下内容添加到功能栏中公式的末尾：

/COUNT(B3:B20)

The final formula should look like this:

最终公式应如下所示：

=FREQUENCY(B3:B20,C3:C20)/COUNT(B3:B20)

Now, press Ctrl+Shift+Enter again.

现在，再次按Ctrl + Shift + Enter。

The new formula divides each element of the Results array by the total count of values in the “Data_array” column.

新公式将结果数组的每个元素除以“ Data_array”列中的值总数。

The results are not automatically formatted as percentages, but that’s easy enough to change. Switch to the “Home” menu and then press the “%” button.

结果不会自动格式化为百分比，但这很容易更改。 切换到“主页”菜单，然后按“％”按钮。

The values will now appear as percentages. So, for example, you can now see that 17% of the numbers in the “Data_array” column fell in the 1-2 range.

现在，这些值将显示为百分比。 因此，例如，您现在可以看到“ Data_array”列中的数字的17％落在1-2范围内。

Best of all, now that the formula is in place in the “Results” column, you can alter any of the values in the “Data_array” and “Bins_array” columns and Excel will automatically refresh the results with updated values.

最重要的是，既然公式已在“结果”列中到位，则可以更改“数据数组”和“绑定数组”列中的任何值，并且Excel将自动使用更新后的值刷新结果。

绕过公式菜单并使用功能栏 (Bypassing the Formulas Menu and Using the Function Bar)

If you prefer typing and know your way around naming columns and cells, you can always bypass digging through the “Formulas” menu by simply typing functions directly into Excel’s Function Bar and then pressing Ctrl+Shift+Enter.

如果您喜欢打字并且知道如何命名列和单元格，则始终可以通过直接在Excel的功能栏中直接键入函数，然后按Ctrl + Shift + Enter来跳过“公式”菜单中的挖掘。

To calculate frequency distribution, use the following syntax:

要计算频率分布，请使用以下语法：

{=FREQUENCY(Data_array,Bins_array)}

To calculate frequency percentages, use this syntax instead:

要计算频率百分比，请改用以下语法：

{=FREQUENCY(Data_array,Bins_array)/COUNT(Data_array)}

Just remember that this is an array formula, so you must press Ctrl+Shift+Enter instead of just Enter. The presence of {curly brackets} around the formula indicates that it has been entered as an array formula.

请记住，这是一个数组公式，因此必须按Ctrl + Shift + Enter而不是Enter。 公式周围的{括号}表示已将其输入为数组公式。

翻译自: https://www.howtogeek.com/398655/how-to-use-the-frequency-function-in-excel/

c++中frequency