对于那些经常使用Excel的人来说，用于汇总和操作数据的内置公式和函数的数量是惊人的。Excel几乎被每个人使用：从金融课的学生到华尔街(Wall Street)的对冲基金经理。它非常强大，但同时非常简单。
对于刚开始使用Excel的人，您应该学习的第一组函数之一是汇总函数。这些包括SUM、 AVERAGE、MAX、 MIN、MODE、MEDIAN、COUNT、STDEV、LARGE、SMALL 和 AGGREGATE(SMALL and AGGREGATE)。这些函数最适合用于数值数据。
在本文中，我将向您展示如何创建公式并将函数插入Excel 电子表格(Excel spreadsheet)。Excel中的每个函数都接受arguments，这是函数计算输出所需的值。
了解公式和函数
例如，如果您需要将 2 和 2 相加，则函数将是SUM 并且参数将是数字2和2。我们通常将其写为 2 + 2，但在Excel 中(Excel)您将其写为=SUM(2+2)。在这里，您可以看到两个文字数字简单相加的结果。
即使这个公式没有任何问题，但它确实没有必要。您可以在Excel中键入(Excel) =2+2，这也可以。在Excel 中(Excel)，当您使用SUM之类的函数时，使用参数更有意义。使用SUM函数，Excel至少需要两个参数，它们是对电子表格中单元格的引用。
我们如何引用Excel 公式(Excel formula)中的单元格？嗯，这很容易。每一行都有一个数字，每一列都有一个字母。A1 是电子表格左上角的第一个单元格。B1 将是 A1 右侧的单元格。A2 是 A1 正下方的单元格。够容易吧？(Easy)
在我们编写新公式之前，让我们在A 列和 B 列(columns A and B)中添加一些数据以供使用。继续为我们的数据集键入从 A1 到A10 和 B1(A10 and B1)到B10的随机数。(B10)现在转到D1 并输入(D1 and type)= =SUM(A1,B1)。您应该看到结果只是A1 + B1的值。
在Excel中键入公式时需要注意一些事项。首先，您会注意到，当您在函数名称(function name)之后键入第一个左括号(opening parenthesis)( ( ) 时，Excel会自动告诉您该函数需要哪些参数。在我们的示例中，它显示number1、number2等。您用逗号分隔参数。这个特定函数可以采用无限数量的值，因为SUM 函数(SUM function)就是这样工作的。
其次，您可以手动输入单元格引用(cell reference)(A1)，也可以在输入左括号后单击单元格 A1。Excel还会以与单元格引用(cell reference)相同的颜色突出显示单元格，以便您可以准确地看到相应的值。所以我们将一行相加，但是我们如何在不再次输入公式或复制粘贴的情况下将所有其他行相加呢？幸运的是，Excel让这一切变得简单。
将鼠标光标移动(mouse cursor)到单元格 D1 的右下角，您会注意到它从白色十字变为黑色加号。
现在单击并按住鼠标按钮。将光标向下拖动到包含数据的最后一行，然后在最后放开。
Excel足够聪明，知道公式应该更改并反映其他行中的值，而不是一直显示相同的A1 + B1。相反，您会看到A2+B2、A3+B3等等。
还有另一种使用SUM的方法，它解释了Excel中参数背后的另一个概念。假设我们想总结从 A1 到A12的所有值，那么我们将如何去做呢？我们可以输入类似=SUM(A1, A2, A3, etc)的内容，但这非常耗时。更好的方法是使用Excel 范围(Excel range)。
要将 A1 与A12相加，我们所要做的就是输入=SUM(A1:A12)并用冒号而不是逗号分隔两个单元格引用。您甚至可以键入=SUM(A1:B12)A1 到 A12 和 B1 到 B12(A1 thru A12 and B1 thru B12)中的所有值相加。
这是关于如何在Excel中使用函数和公式的一个非常基本的概述，但这足以让您开始使用所有数据求和函数。
汇总函数
使用相同的数据集，我们将使用其余的汇总函数来查看我们可以计算出什么样的数字。让我们从COUNT和COUNTA函数开始。
在这里，我将COUNT函数输入到 D2 中，将COUNTA函数输入到 E2 中，使用A2:A12 范围作为两个函数的数据集(data set)。我还将 A9 中的值更改为文本字符串(text string) hello以显示差异。COUNT只计算包含数字的单元格，而COUNTA计算包含文本和数字的单元格。这两个函数都不计算空白单元格。如果要计算空白单元格，请使用COUNTBLANK函数。
接下来是AVERAGE、MEDIAN和MODE函数。平均值是不言自明的，中位数是一组数字中的中间数字，众数是一组数字中最常见的一个或多个(number or numbers)数字。在较新版本的Excel 中(Excel)，您有MODE.SNGL 和MODE.MULT，因为可能有多个数字是一组数字中最常见的数字。我在下面的示例中使用 B2:B12 作为范围。
继续，我们可以计算数字集 B2: B12的(B12)MIN、MAX 和 STDEV(MAX and STDEV)。STDEV 函数(STDEV function)将计算值与平均值的分散程度。在较新版本的Excel 中(Excel)，您有STDEV.P和STDEV.S，它们分别基于整个总体或基于样本进行计算。
Finally, another two useful functions are LARGE and SMALL. They take two arguments: the cell range and the k-th largest value you want to return. So if you want the second largest value in the set, you would use 2 for the second argument, 3 for the third largest number, etc. SMALL works the same way, but gives you the k-th smallest number.
Lastly, there is a function called AGGREGATE, which allows you to apply any of the other functions mentioned above, but also lets you do things like ignore hidden rows, ignore error values, etc. You probably won’t need to use it that often, but you can learn more about it here in case you do need it.
以上就是Excel(Excel)中一些最常见的汇总函数的基本概述。如果您有任何问题，请随时发表评论。享受！
Use Summary Functions to Summarize Data in Excel
For those who use Excel rеgularly, the number of built-in formulas and functions to summarize and manipulate data is staggering. Excel is literally used by everyone: from students in a finаncial class to hеdge fund managers on Wall Street. It’s extremely powerful, but at the same time very simple.
For those just getting started with Excel, one of the first group of functions you should learn are the summary functions. These include SUM, AVERAGE, MAX, MIN, MODE, MEDIAN, COUNT, STDEV, LARGE, SMALL and AGGREGATE. These functions are best used on numerical data.
In this article, I’ll show you how to create a formula and insert the function into an Excel spreadsheet. Each function in Excel takes arguments, which are the values the functions needs to calculate an output.
Understanding Formulas & Functions
For example, if you need to add 2 and 2 together, the function would be SUM and the arguments would be the numbers 2 and 2. We normally write this as 2 + 2, but in Excel you would write it as =SUM(2+2). Here you can see the results of this simple addition of two literal numbers.
Even though there is nothing wrong with this formula, it really isn’t necessary. You could just type =2+2 in Excel and that would work also. In Excel, when you use a function like SUM, it makes more sense to use arguments. With the SUM function, Excel is expecting at least two arguments, which would be references to cells on the spreadsheet.
How do we reference a cell inside the Excel formula? Well, that’s pretty easy. Every row has a number and every column has a letter. A1 is the first cell on the spreadsheet at the top left. B1 would be the cell to the right of A1. A2 is the cell directly below A1. Easy enough right?
Before we write our new formula, let’s add some data in columns A and B to work with. Go ahead and type random numbers from A1 to A10 and B1 to B10 for our data set. Now go to D1 and type in =SUM(A1,B1). You should see the result is simply the value of A1 + B1.
There are a couple of things to note while typing a formula in Excel. Firstly, you’ll notice that when you type the first opening parenthesis ( after the function name, Excel will automatically tell you what arguments that function takes. In our example, it shows number1, number2, etc. You separate arguments with commas. This particular function can take an infinite number of values since that is how the SUM function works.
Secondly, either you can type in the cell reference manually (A1) or you can click on the cell A1 after you typed the open parenthesis. Excel will also highlight the cell in the same color as the cell reference so you can see the corresponding values exactly. So we summed one row together, but how can we sum all the other rows without typing the formula again or copying and pasting? Luckily, Excel makes this easy.
Move your mouse cursor to the bottom right corner of cell D1 and you’ll notice it changes from a white cross to a black plus sign.
Now click and hold your mouse button down. Drag the cursor down to the last row with the data and then let go at the end.
Excel is smart enough to know that the formula should change and reflect the values in the other rows rather than just showing you the same A1 + B1 all the way down. Instead, you’ll see A2+B2, A3+B3 and so on.
There is also another way to use SUM that explains another concept behind arguments in Excel. Let’s say we wanted to sum up all the values from A1 to A12, then how would we go about it? We could type something like =SUM(A1, A2, A3, etc), but that is very time consuming. A better way is to use an Excel range.
To sum A1 to A12, all we have to do is type =SUM(A1:A12) with a colon separating the two cell references instead of a comma. You could even type something like =SUM(A1:B12) and it will sum all values in A1 thru A12 and B1 thru B12.
This was a very basic overview of how to use functions and formulas in Excel, but it’s enough so that you can start using all of the data summation functions.
Summary Functions
Using the same set of data, we’re going to use the rest of the summary functions to see what kind of numbers we can calculate. Let’s start with the COUNT and COUNTA functions.
Here I have entered the COUNT function into D2 and the COUNTA function into E2, using A2:A12 range as the data set for both functions. I also changed the value in A9 to the text string hello to show the difference. COUNT only counts the cells that have numbers whereas COUNTA counts cells that contain text and numbers. Both functions do not count blank cells. If you want to count blank cells, use the COUNTBLANK function.
Next up are the AVERAGE, MEDIAN and MODE functions. Average is self-explanatory, median is the middle number in a set of numbers and mode is the most common number or numbers in a set of numbers. In newer versions of Excel, you have MODE.SNGL and MODE.MULT because there could be more than one number that is the most common number in a set of numbers. I used B2:B12 for the range in the example below.
Moving on, we can calculate the MIN, MAX and STDEV for the set of numbers B2:B12. The STDEV function will calculate how widely values are dispersed from the average value. In newer versions of Excel, you have STDEV.P and STDEV.S, which calculates based on the entire population or based on a sample, respectively.
Finally, another two useful functions are LARGE and SMALL. They take two arguments: the cell range and the k-th largest value you want to return. So if you want the second largest value in the set, you would use 2 for the second argument, 3 for the third largest number, etc. SMALL works the same way, but gives you the k-th smallest number.
Lastly, there is a function called AGGREGATE, which allows you to apply any of the other functions mentioned above, but also lets you do things like ignore hidden rows, ignore error values, etc. You probably won’t need to use it that often, but you can learn more about it here in case you do need it.
So that’s a basic overview of some of the most common summary functions in Excel. If you have any questions, feel free to post a comment. Enjoy!
