excel 小技巧 第四十二集 定义和创建公式

在本课中，我们将向您介绍创建公式和使用函数的基本规则。我们认为最好的学习方法是通过实践，所以我们提供几个例子，并详细解释。我们将介绍的主题包括：

• 行和列

• 示例数学函数：SUM（）

• 运营商

• 运算符优先级

• 示例财务功能：PMT（），贷款支付

• 在公式和嵌套函数中使用“字符串”函数（“字符串”是“文本字符串”的缩写）

公式是“函数”，“运算符”和“操作数”的混合。在写几个公式之前，我们需要创建一个函数，但是在创建函数之前，我们首先需要了解行和列的符号。

行和列

要了解如何编写公式和函数，您需要了解有关行和列。

行水平运行，列垂直运行。要记住哪一个，认为一列举起一个屋顶 - 列上下，因此行左右。

列用字母标记; 行数字。电子表格中的第一个单元格是A1，表示A列，第1列。列标记为AZ。当字母表用完时，Excel在前面放置另一个字母：AA，AB，AC ... AZ，BA，BC，BC等。

示例：Function Sum（）

现在让我们演示如何使用一个函数。

您可以通过直接在功能向导中键入或使用功能向导来使用功能。当您从“函数库”的“公式”菜单中选择一个函数时，函数向导将打开。否则，可以在单元格中键入=，方便的下拉菜单将允许您选择一个函数。

向导会告诉您需要为每个函数提供哪些参数。它还提供了一个链接到在线说明，如果你需要帮助了解什么功能，以及如何使用它。例如，如果在单元格中键入= sum，则在线向导将显示SUM函数需要哪些参数。

当您键入功能时，向导会内联或在您的手指。从“公式”菜单中选择一个函数时，向导将是一个弹出框。下面是SUM（）函数的弹出向导。

对于我们的第一个函数，让我们使用SUM（），它添加一个数字列表。

假设我们有这个电子表格，包含您家庭度假预算的计划：

要计算总成本，您可以写成= b2 + b3 + b4 + b5，但它更容易使用SUM（）函数。

在Excel中，在Excel屏幕的左上角查找符号Σ，以找到AutoSum按钮（数学家使用希腊字母Σ添加一系列数字）。

如果光标位于家庭预算数字之下，Excel就足够聪明，可以知道您要将上面放置光标的数字列表求和，因此它会突出显示数字。

按“输入”接受Excel选择的范围，或使用光标更改选择的单元格。

如果你看看Excel放在电子表格中，你可以看到它写了这个功能：

在此公式中，Excel将从B2到B9的数字相加。注意，我们离开了第5行下面的一些房间，以便你可以添加到家庭度假预算 - 成本肯定会上升，因为孩子们的名单他们想做什么，他们想去的地方增长更长！

数学函数不能使用字母，因此如果将字母放入列中，结果将显示为“#NAME？”，如下所示。

＃名称？表示有某种错误。它可以是任何数量的事情，包括：

• 坏的单元格引用

• 在数学函数中使用字母

• 省略必需的参数

• 拼写函数名错误

• 非法数学运算，如除以0

在计算中选择参数的最简单方法是使用鼠标。您可以通过放大或缩小当您移动鼠标或单击另一个单元格时Excel绘制的框时，从函数的参数列表中添加或删除。

我们点击了Excel绘制的方格的顶部，从预算中取出“机票”。您可以看到可以绘制的十字符号，以使所选范围更大或更小。

按“Enter”确认结果。

计算运算符

有两种类型的运算符：数学和比较。

数学运算符	定义
+	加成
- -	减法或否定，例如，6 * -1 = -6
*：	乘法
/	师
％	百分
^	指数，例如2 4 = 2 ^ 4 = 2 * 2 * 2 * 2 = 16

还有其他运算符与数学无关，如“＆”，这意味着连接（连接端到端）两个字符串。例如，=“Excel”＆“is Fun”equals“Excel is Fun”。

现在我们来看一下比较运算符。

比较运算符	定义
=	等于例如2 = 4或“b”=“b”
>	大于例如4> 2或“b”>“a”
	小于例如2 <><>
> =	大于或等于-另一种方式来想这是> =意味着要么 > 或 =。
<>	小于或等于。
<>	不等于例如4 6

正如你可以看到，比较运算符使用数字和文本。

注意，如果在单元格中输入=“a”>“b”，它将会说“FALSE”，因为“a”不大于“b” >“b” 或 “b”>“a”。

运营商订单优先级

顺序优先是数学的一个想法。Excel必须遵循与数学相同的规则。这个话题比较复杂，所以请抽气，让我们来看看。

顺序优先级是指计算机计算答案的顺序。正如我们在课程1所解释的，圆的面积为πR 2，这是相同的π* R * R。它不是（πr）2。

因此，在编写公式时，您必须了解顺序优先级。

一般来说，你可以这样说：

1. Excel首先评估括号内的项目。

2. 然后使用数学的顺序优先规则。

3. 当两个项目具有相同的优先级时，Excel从左到右工作。

数学运算符的优先级如下所示，按降序排列。

（和）	当使用括号时，它们会覆盖正常的优先级规则。这意味着Excel将首先执行此计算。我们在下面进一步解释。
- -	否定，例如-1。这与将数字乘以-1相同。-4 = 4 *（ - 1）
％	百分比，乘以100.例如，0.003 = 0.3％。
^	指数，例如10 ^ 2 = 100
*和/	乘法和除法。两个运算符如何具有相同的优先级？它只是意味着如果公式有两个具有相同优先级的运算符，则计算从左到右进行。
+和 -	加减。

还有与字符串和引用运算符相关的其他优先规则。目前，我们将坚持我们刚刚提到的内容。现在，让我们来看一些例子。

示例：计算圆的面积

圆的面积为= PI（）* radius ^ 2。

看看上面的表，我们看到指数在乘法之前。所以计算机首先计算半径^ 2，然后它乘以Pi的结果。

示例：计算工资加薪

假设你的老板决定你做得很好，他或她会给你10％的加薪！你如何计算你的新工资？

首先，记住乘法在加法之前。

是=薪水+薪水* 10％还是它=薪水+（薪水* 10％）？

假设你的薪水是100美元。加薪10％，你的新工资将是：

= 100 + 100 * 10％= 100 + 10 = 110

你也可以这样写：

= 100 +（100×10％）= 100 + 10 = 110

在第二种情况下，我们通过使用括号来使优先级的顺序显式。请记住，在任何其他操作之前评估括号。

顺便说一句，写这个更简单的方法是= salary * 110％

括号可以嵌套在一起。所以，当我们写（3 +（4 * 2）），从内到外工作时，首先计算4 * 2 = 8，然后加3 + 8得到11。

还有几个例子

这里是另一个例子：= 4 * 3 / 2.什么是答案？

我们从上表中的规则看到，*和/具有相等的优先级。所以Excel从左到右，4 * 3 = 12首先，然后除以2得到6。

再次，你可以通过写作=（4 * 3）/ 2明确

那么= 4 + 3 * 2？

计算机同时查看*和+运算符。所以遵循优先级规则（乘法在加法之前）首先计算3 * 2 = 6，然后加4以得到10。

如果你想改变优先顺序，你可以写成=（4 + 3）* 2 = 14。

那么这一个= -1 ^ 3？

那么答案是-3，因为计算机计算=（-1）^ 3 = -1 * -1 * -1 = -1。

记住负时间负数是正数，负数乘以正数是负数。你可以看到这样（-1 * -1）* -1 = 1 * -1 = -1。

因此，有几个数学顺序和优先级的例子，我们希望有助于清除一些关于Excel如何执行计算（这可能是足够的数学，为你的一些人持续一生）。

示例：功能贷款支付（PMT）

让我们来看一个计算贷款支付的例子。

首先创建一个新的工作表。

使用美元符号格式化数字，并使用零小数位，因为我们现在对美分不感兴趣，因为当谈论美元时，它们没什么关系（在下一章中，我们将详细介绍如何格式化数字）。例如，要格式化利率，右键单击单元格，然后单击“格式化单元格”。选择百分比并使用2个小数位。

同样，格式化其他单元格为“货币”，而不是百分比，并选择“数字”为贷款期限。

现在我们有：

将SUM（）函数添加到“总计”每月费用。

注意，抵押单元不包括在总额中。Excel不知道您要包括该数字，因为没有值。因此，请小心地通过使用光标或键入E2将其中的SUM（）函数扩展到顶部，其中说E3包括抵押在总和中。

将光标放在付款单元格（B4）中。

在公式菜单上，选择下拉“财务”，然后选择PMT函数。向导弹出：

使用光标选择“费率”，“nper”（贷款期限），“Pv”（“现值”或贷款金额）。请注意，您必须将利率除以12，因为利率每月计算一次。此外，您需要将贷款期限乘以12年以获得贷款期限在几个月。按“确定”将结果保存在电子表格中。

请注意，付款显示为负数：-1013.37062。为了使其积极并将其加到每月支出，指向抵押贷款单元（E2）。键入“= - ”，然后使用光标指向付款字段。得到的式为= -B4。

现在电子表格看起来像这样：

你的每月费用是$ 1,863 - Ouch！

示例：文本函数

这里我们演示如何使用公式和文本函数内的函数。

假设你有一个学生列表，如下所示。第一个和最后一个名称在一个字段中，用逗号分隔。我们需要将最后一个和公司名称放在不同的单元格中。我们如何做到这一点？

为了解决这个问题，你需要使用一个算法 - 即一个逐步的程序来做这个。

例如，看看“华盛顿，乔治”。将它分成两个词的过程将是：

1. 计算字符串的长度。

2. 查找逗号的位置（这显示一个字结束，另一个开始）。

3. 复制字符串的左侧，直到逗号。

4. 将字符串的右侧从逗号复制到结束。

让我们来讨论如何在Excel中的“George Washington”一步一步做到这一点。

1. 使用函数= LEN（A3）计算字符串的长度 - 结果为18。

2. 现在通过输入这个函数= FIND（“，”，A3“）找到逗号的位置 - 结果是11。

3. 现在将字符串的左侧直到逗号并使用步骤1的结果创建此嵌套公式：= LEFT（A3，FIND（“，”，A3）-1）。注意，我们必须从长度减1，因为FIND给出逗号的位置。

这里是所有的看起来像所有的功能都放在一起在一个公式。在单元格B3中，您可以看到此公式从单元格A3获取所有信息，并输入“华盛顿”。

所以我们有“华盛顿”，现在我们需要得到“乔治”。我们怎么做呢？

注意，我们可以将单元格中的步骤1的结果保存在单元格中，例如B6，然后写一个更简单的公式= LEFT（A3，B6-1）。但是，使用一个单元格的间歇步骤。

1. 记住逗号的位置或再次计算。

2. 计算字符串的长度。

3. 计算从字符串末尾到逗号的字符。

取从步骤3的字符数，减去一个以省略逗号和空格。

让我们一步一步做。

1. 从上面，这是= FIND（“，”，A3“）

2. 字符串的长度为= LEN（A3）

3. 您将需要使用一些数学来找到要采取的字符数：= LEN（A3） - FIND（“，”，A3） - 1

4. 我们想要的字符串的右侧是= RIGHT（A3，LEN（A3） - FIND（“，”，A3）-1）

您的电子表格现在应该类似于下面的屏幕截图。我们将公式作为文本复制到电子表格的底部，以便于阅读和查看。

那一个有点困难，但你只需要写这些公式一次。