了解如何构造公式是学习使用 Excel 的(learning to use Excel)基本步骤。以下是MS Excel(MS Excel)中函数和公式语法的入门知识。
虽然您只需从其他电子表格复制公式或从专用菜单插入 Excel 函数即可取得很大成果，但至少获得有关语法实际工作原理的基本知识是个好主意。这样，您将能够修复任何错误或调整公式，以充分利用电子表格。
语法的含义是什么？
编程语言的设计目的是让你用结构化的句子来指导计算机。语法(Syntax)只是指这种结构化语言的规则，因为计算机不够智能，无法解析任何偏离预期标准的文本。
Microsoft Excel虽然不是编程语言，但能够使用公式来指导程序。就像任何其他形式的机器指令一样，它有自己的语法。了解此语法是利用电子表格应用程序功能的关键。
Excel 中的公式是什么？
Excel公式是可以代替实际数据输入到单元格中的表达式，使用数学运算或其他函数来生成结果。例如，可以编写一个公式来将列中的所有数字相加并显示结果。
为什么要使用公式呢？毕竟，您可以自己手动计算结果并将其输入到必填字段中。
使用公式而不是硬值进行计算的优点是，这使得更新电子表格变得更加容易。每当您对计算中的基础数据进行任何更改时，Excel都会自动为您重新计算结果。
这意味着您可以轻松地将公式扩展到数百个和数千个单元格，而不会损失任何准确性。您甚至可以在工作簿中复制并粘贴这些公式，并让Excel自动调整参数以适应其新位置。
构建基本公式
您可以通过以等号开头然后键入表达式来在单元格中输入公式。例如，输入以下内容即可得出这两个数字的总和：
= 30 + 40
当然，对这样的静态数字执行计算并不是特别有用。要添加单元格中包含的数字，必须使用单元格引用。
就像棋盘上的正方形一样，电子表格的单元格可以通过字母和数字位置的组合来引用。当您在电子表格中向下移动时，数字行号会增加，而字母列号则向右移动。
您可能已经发现了一个明显的问题——字母表很快就会用完，那么 Z 之后的列呢？然后我们开始组合字母，得到 AA、AB、AC 等等。
使用此参考，您可以在公式中使用电子表格的任何单元格的值。像这样：
= A1 + A2
请注意，一旦按 Enter 键，公式将仅出现在公式栏中 - 单元格本身显示结果值。因此，您需要选择一个单元格来确定其值是来自公式还是手动输入。
这就是创建独立执行计算的基本公式所需的全部内容。最好的部分是，如果您最终修改了引用的任何单元格，结果会自行更新。
功能介绍
单元(Cell)格引用和基本算术可以让你走得很远，但它们仍然有很多不足之处。例如，即使您知道引用，编写用于添加数百个单元格的公式也可能很乏味。
这就是函数的用武之地。与任何编程语言一样，Excel包含大量内置函数，可以使用相对简单的语法执行复杂的任务。
函数通过其名称调用，后跟方括号（或圆括号，因为它们被严格调用）中包含的任何参数。例如，您可以使用SUM函数轻松添加大量单元格：
为了进一步缩短公式，我们可以指定一个范围，而不是输入每个单元格的引用。例如，您可以键入 A1:A4 以包含从 A1 到 A4 的整个单元格范围。这也可以扩展到一行，并包含任意数量的单元格。
SUM不是您唯一可用的函数。单击公式栏旁边的 f𝑥 符号可以获得完整列表，但以下是一些更常见的列表：
- AVERAGE – 另一个基本函数，AVERAGE函数返回其中输入的值范围的平均值。对于财务计算非常有用。
- CONCATENATE（连接）(CONCATENATE) ——此操作适用于文本而不是数字。使用CONCATENATE，您可以将两个字符串连接在一起并输出最终值。对于使用条件运算符生成文本并将它们连接起来以获得最终结果非常
有用。(Useful)
- LEN – Length的缩写形式，LEN只是计算给定字符串中的字符数，包括空格。它的使用非常(Very)适合情境（例如确定空白单元格是否确实有空格），但比手动计算字符要好。
- TRIM – 在某些单元格中意外输入额外空格的情况很常见，从而导致某些计算或(TRIM)CONCATENATE操作的结果丢失。TRIM函数可用于删除所有多余空格，仅在每个单词之间留下一个空格。
逻辑函数
到目前为止，我们已经看到了许多不同的函数，它们执行许多有用的任务，例如SUM、AVERAGE或TRIM。但是逻辑比较呢？
就像任何其他编程语言一样，Excel 的语法包括逻辑运算符，例如 IF、AND以及 OR。最有用的是 IF 函数，另外两个函数经常在逻辑表达式中使用。
IF 的语法很简单(The syntax of IF is simple)：一个计算结果为TRUE或FALSE的逻辑表达式，后跟按顺序列出的任一条件中要遵循的值。当然，所有这些都放在括号内并用逗号分隔。像这样的东西：
请注意，最后一个参数可以省略，在这种情况下，该函数仅在条件为 true 时返回一个值。这可用于通过有条件地将值包含在计算中来构造复杂的公式。
另一方面，第一个论点可以采取多种形式。逻辑表达式可以简单地是使用“<”或“=”等符号对两个值进行比较，但也可以是使用AND或OR等多个此类语句的组合。
这是因为AND和 OR 都只返回TRUE或FALSE，而不返回任何文本字符串或数值。您可以通过包含两个逻辑表达式作为参数来使用这些函数；如果两个参数的计算结果均为TRUE，则 AND返回(AND)TRUE ；(TRUE)而即使其中一个参数为TRUE ，OR(TRUE)也(TRUE)返回 TRUE 。否则(Otherwise)，返回
FALSE 。
使用 IF、AND和 OR，可以构建非常复杂的嵌套条件，在最终输出结果之前评估一堆不同的值。尽管由于涉及所有括号和逗号，此类公式通常容易出错。最好将计算分开并让事情变得简单。
为了进一步简化事情，Excel 包含了一些将 IF 与其他有用运算（例如 SUM 或 AVERAGE ）结合起来的函数(Excel includes a few functions that combine IF with other useful operations like SUM or AVERAGE)。
复制公式
通常，您需要对工作表中的不同单元格区域执行相同类型的计算，并在单独的单元格中显示结果。您是否必须一次又一次手动输入相同的公式，每次都更改单元格引用？
不，你可以直接复制它们。
当您尝试在Excel(Excel)中粘贴复制的单元格（通过右键单击目标单元格）时，您会看到多个粘贴(Paste)选项。您可以完全粘贴单元格、仅复制值，甚至仅粘贴公式。
更好的(Better)是，粘贴的公式经过修改以反映根据其新位置的相对引用。如果您复制汇总 A 列值的公式并将其粘贴到不同的单元格中，它将使用新的列号更新引用。
关于Excel 函数语法，(Excel Function Syntax)您需要了解什么？
作为初学者，不必费心去记住每个Excel函数。它们中的大多数很少有用，您可以通过单击公式栏旁边的 ?? 按钮找到它们的语法。
只需记住公式的基本Excel语法 - 等号后跟可以包含多个函数的表达式。每个函数都接受一些参数（尽管有些函数不接受）并返回一个可以在另一个函数或计算中使用的值。
尽管像这样的嵌套函数很快就会变得复杂，让你陷入一堆缺少括号的混乱之中。我们建议保持每个公式简单明了，将复杂的计算扩展到多个单元格以保持事情的可管理性。
Function Syntax in Excel: All You Need to Know
Understanding how to construct formulаs is a fυndamental step in learning to use Excel. Here is a primer on the syntax of the functions and formulas in MS Excel.
While you can get far by simply copying formulas from other spreadsheets or inserting Excel functions from the dedicated menu, it’s a good idea to gain at least a basic knowledge of how the syntax actually works. That way, you will be able to fix any errors or tweak the formulas to get the most out of your spreadsheet.
What Is the Meaning of Syntax?
Programming languages are designed to let you instruct the computer with structured sentences. Syntax simply refers to the rules of such a structured language, since the computer isn’t smart enough to parse any text that deviates from the expected standard.
Microsoft Excel, while not a programming language, nevertheless has the ability to instruct the program using formulas. And just like any other form of machine instruction, this has its own syntax. Understanding this syntax is key to taking advantage of the capabilities of the spreadsheet application.
What Is a Formula in Excel?
An Excel formula is an expression that can be entered into a cell in lieu of actual data, using mathematical operations or other functions to generate a result. For example, a formula may be written to add all numbers in a column and display the result.
Why use a formula for this? After all, you can manually calculate the result yourself and just enter it in the required field.
The advantage of using a formula instead of a hard value for calculations is that this makes updating the spreadsheet easier. Any time you make any changes in the underlying data that goes into a calculation, Excel will automatically recalculate the results for you.
This means you can easily scale up a formula across hundreds and thousands of cells without losing any accuracy. You can even copy and paste these formulas across workbooks and have Excel automatically adjust the parameters to fit its new location.
Constructing a Basic Formula
You enter a formula in a cell by starting with an equal sign and then typing out the expression. For example, typing this gives you the sum of these two numbers:
= 30 + 40
Of course, performing calculations on static numbers like this isn’t particularly useful. To add the numbers contained in a cell, you must use cell references.
Like a square on a chessboard, the cells of a spreadsheet can be referenced by a combination of their alphabetical and numeric positions. The numerical row number increases as you go down the spreadsheet, while the alphabetical column numbers advance toward the right.
You might have spotted the obvious problem with this – alphabets run out fairly quickly, so what about columns after Z? Then we just start combining alphabets, giving us AA, AB, AC, and so on.
Using this reference you can use the value of any cell of the spreadsheet in a formula. Like this:
= A1 + A2
Note that once you hit Enter, the formula will only appear in the formula bar – the cell itself sports the resulting value. So you need to select a cell to determine if its value is derived from a formula or manually entered into it.
That’s all you need to craft basic formulas that perform calculations on their own. The best part is that if you end up modifying any of the cells being referenced, the result updates on its own.
Introduction to Functions
Cell references and basic arithmetic can take you far, but they still leave much to be desired. It can be tedious writing formulas for adding up hundreds of cells, for example, even if you know the references.
That’s where functions come in. Like any programming language, Excel includes a bunch of built-in functions that can perform complex tasks with a relatively simple syntax.
Functions are invoked by their name followed by any arguments enclosed in brackets (or parentheses, as they are strictly called). For example, you can use the SUM function to add a large number of cells easily:
To shorten the formula even further, we can specify a range instead of entering the reference of every cell. For example, you can type A1:A4 to include the entire range of cells from A1 to A4. This can be extended across a row as well, and include as many cells as you want.
SUM is not the only function you have available. A full list can be obtained by clicking on the ƒ𝑥 symbol next to the formula bar, but here are a few of the more common ones:
- AVERAGE – Another basic function, the AVERAGE function returns the average of the range of values entered in it. Very useful for fiscal calculations.
- CONCATENATE – This one works with text rather than numbers. With CONCATENATE, you can join two strings together and output the final value. Useful for generating text with conditional operators and joining them for the final result.
- LEN – The shortened form of Length, LEN simply counts the number of characters in a given string, including spaces. Very situational in its use, (like figuring out if a blank cell actually has spaces) but better than counting the characters manually.
- TRIM – It is quite common to accidentally enter extra spaces in some cells, throwing off the results of some calculations or CONCATENATE operations. The TRIM function can be used to remove all extra spaces, leaving just a single space between every word.
Logical Functions
So far we have seen a bunch of different functions that perform many useful tasks like SUM, AVERAGE, or TRIM. But what about logical comparisons?
Just like any other programming language, Excel’s syntax includes logical operators like IF, AND, as well as OR. The most useful is the IF function, with the other two often used within a logical expression.
The syntax of IF is simple: a logical expression that evaluates to either TRUE or FALSE, followed by the values to be followed in either condition set out in sequence. Of course, all of this goes inside the brackets and is separated by commas. Something like this:
Note that the final argument can be omitted, in which case the function will only return a value if the condition is true. This can be used to construct complex formulas by conditionally including values in a calculation.
The first argument, on the other hand, can take many forms. A logical expression can simply be a comparison of two values using symbols like “<” or “=”, but it can also be a combination of multiple such statements by using AND or OR.
This is because both AND and OR only return TRUE or FALSE, rather than any text string or numeric value. You use these functions by including two logical expressions as arguments; AND returns TRUE if both arguments evaluate to TRUE, while OR returns TRUE even if a single one of them is TRUE. Otherwise, FALSE is returned.
Using IF, AND, and OR, it is possible to build very complex nested conditions that evaluate a bunch of different values before finally spitting out a result. Though such formulas are often prone to errors due to all the brackets and commas involved. It is better to separate the calculations and keep things simple.
To simplify things further, Excel includes a few functions that combine IF with other useful operations like SUM or AVERAGE.
Copying Formulas
Often you need to perform the same type of calculations on different cell ranges in a worksheet, displaying the results in separate cells. Do you have to manually enter the same formula again and again, changing the cell references every time?
No, you can just copy them.
When you try pasting a copied cell in Excel (by right-clicking on the target cell), you are presented with multiple Paste options. You can paste the cells completely, copy over just the values, or even paste the formula only.
Better yet, the pasted formula is modified to reflect the relative reference according to its new location. If you copy a formula summing up the values of column A and pasted it in a different cell, it would update the references with the new column number.
What Do You Need to Know About Excel Function Syntax?
As a beginner, don’t bother with trying to memorize every Excel function out there. Most of them are rarely useful, and you can find their syntax by clicking the ƒ𝑥 button beside the formula bar.
Just keep in mind the basic Excel syntax of a formula – an equal to sign followed by an expression that can include multiple functions. Each function takes in some arguments (though some functions don’t) and returns a value that can then be used in another function or calculation.
Though nesting functions like this can get complicated pretty fast, losing you in a mess of missing brackets. We would recommend keeping every formula simple and straightforward, stretching out a complex calculation over multiple cells to keep things manageable.
