微软excel 2010中文官方培训课件-了解 excel 2010：创建公式-2

有关单元格引用和公式的更多信息,了解 Excel 2010：创建公式,指向视频可以显示视频控件。沿进度栏拖动或单击进度栏可以向前或向后移动。,我在单元格 B7 中创建了一个公式，以获得 B 列中所有数字的总和。,我还想获得 C 列中数字的总和。,为此，我可以创建另一个公式，也可以将现有公式复制到 C 列。,在单元格 B7 中单击，移动光标直至其变为一个黑色加号，,按下鼠标左键并拖动到单元格 C7，释放鼠标按钮，此时 C 列中将显示总和。,看一下这两个公式，以确定复制公式时发生的事情。,在 B7 中单击并查看编辑栏，您可以看到单元格引用为“B2:B6”，,它表示包括 B 列中的所有数字。,在单元格 C7 中单击并查看编辑栏，您可以看到 B2 变成了 C2，而 B6 变成了 C6。,现在，公式包含 C 列中的所有数字。,在将公式从该单元格复制到该单元格时，,单元格引用会自动发生更改以反映公式的新位置。,复制公式时，如果单元格引用发生更改， 则这种单元格引用称为相对单元格引用。,您不必了解该术语，这只是它的名称。,在继续下一部分之前， 我还想获得每名销售人员在第一周和第二周的总销售额。,在 D2 中单击，上移到“编辑” 区域，并单击“自动求和” 按钮，,然后按 Enter，此时将显示总和， 移动光标直至其变为黑色加号并向下拖动，,现在即会得到每个人在这两周的总工作业绩。,顺便说一下，拖放公式非常有用，,但您应该知道，还可以将公式从一个位置复制并粘贴到其他位置。,如果要将公式从一个单元格复制到并非紧邻的单元格， 则这样做会很方便。,我将举例说明。删除此公式，在此处单击，单击右键，并单击“复制”。,然后，在希望应用公式的位置单击，并单击右键，,将显示一些粘贴选项，这是我需要的“公式” 选项，因为我要粘贴公式。,单击此选项，将再次显示 C 列的总和，要消除单元格 B7 四周的点线框，可以按 Esc。,接下来，您将看到一个公式， 在将该公式从一个单元格复制到其他单元格时， 公式中的单元格引用不发生更改。,每名销售人员每两周就可以从其两周的工作业绩中获得 3% 的佣金。,我将创建一个公式，该公式可用于计算每个人的佣金。,在单元格 F2 中键入比率 3%，它可以位于任何单元格中，这只是我随便选择的单元格，,在此处键入公式，“=”，我希望雅玲的总和显示在公式中，,并且我希望总和乘以佣金比率，这是佣金比率，所以单击此处，,然后按 Enter，佣金额为“43.45”。,现在，我要复制该公式，得到的结果可能会让您大吃一惊。,向下复制，您会看到其他每个人的佣金额为零。 这显然不正确。他们应该得到佣金。,我们看一下公式，找出发生这种情况的原因。,如果在表示雅玲的佣金的 E2 中单击，我们会看到“D2*F2”，这非常正确，这是 D2，这是乘数 F2。,但是，如果我单击此处并查看编辑栏，可以看到它显示“D3*F3”。,单元格 F3 中没有任何内容，这就是我们得到零结果的原因。,当然，因为复制时单元格引用会更改，所以向下复制时此处的 F2 会变为 F3，,接着再往下看，会发现它已变为 F4。,接下来是一个示例，它与单元格引用可以自动更改同样重要，,在该示例中我们不希望单元格引用发生更改。 我将演示如何实现这种情况。,删除这些内容。实际上是删除整个公式，并重新开始。,键入等号，单击此处以再次引用 D2，并键入乘号。,单击此处，引用佣金比率。,但是， 这次我要将单元格引用从复制时会发生更改的相对单元格引用更改为,始终指向单元格 F2 的绝对单元格引用。,绝对单元格引用看起来不同于相对单元格引用，可以按 F4 键进行这种更改。,现在，可能看起来有点奇怪， 至少对我来说确实如此。将显示“$F$2”。,这意味着该公式将始终指向单元格 F2。,顺便说一下，如果忘记了键盘快捷方式 F4，则始终可以自己键入美元符号。,美元符号始终位于列前面，即“$F”； 并且始终位于行前面，即“$2”。,按 Enter，显示与先前相同的佣金额，但这次当我向下复制公式时，,每个人都会拥有自己的销售佣金。,如果我在此单元格中单击并查看，我们可以看到“D3”，,还有绝对引用，在这里它指向单元格“F2”。,单击此处，同样，相对引用从“D3”更改为“D4”，,但绝对引用仍指向单元格“F2”。,接下来，我要介绍另一公式示例，在该示例中， 单元格引用是相对引用和绝对引用的组合。,我希望生成一张表，该表将显示以销售人员的销售额为基础的一些内容，,并显示这些人员的销售佣金，所得结果取决于佣金比率。,为此，我需要创建一个公式，该公式包含称为混合引用的单元格引用，,即部分单元格引用为绝对引用，部分引用为相对引用。,理解这一点对我来说有点困难，,但我最终弄明白了，我希望通过我的讲解您可以更好地理解它。,在类似于佣金比率表或税率表之类的表中，通常使用混合单元格引用。,单击此处，我要在该位置生成第一个公式，,您要考虑的问题是您是否要生成这样一张表，,您是否希望公式始终指向的内容是绝对引用？,我始终希望指向 B 列，这一列是销售额，,我始终希望公式指向第 2 行，这一行是佣金比率。,继续操作，键入公式，等号，单击此处以在公式中引用 100,000。,现在，我希望使 B 列成为绝对引用，因此按 F4 键，,这会使 B3 成为绝对引用，这并不是我要的结果，我只希望列成为绝对引用。,再次按 F4 键，这次变为“$3”，,这意味着行将成为绝对引用，这也不是我要的结果，因此我最后一次按 F4，,我获得了指向 B 列的绝对引用和指向第 3 行的相对引用，这正是我要的结果。,公式始终指向 B 列，但是当我向下复制时相对部分 3 将更改为显示第 4 行和第 5 行，依此类推。,现在，我将通过乘法运算获得销售佣金，我不能立即看到销售佣金， 它尚未清晰地显示出来，,继续操作，键入“C2”。,因为我希望始终引用第 2 行，所以按 F4 键，所有内容都成为绝对引用，这不是我要的结果，,再次按 F4，获得的正是我要的结果，即指向 C 列的相对引用，,复制到 D 列和 E 列，依此类推，但绝对引用始终指向第 2 行。,按 Enter。如果销售额为 100,000，佣金比率为 1%，则获得的结果为 1,000。,将此公式复制到邻近的单元格，将获得正确的结果。如果佣金比率为 5%，则获得的结果为 5,000。,选择这些单元格，因为我要在表中将公式从头至尾向下复制， 释放鼠标按钮，,现在，我完成了这张表。,如果在任一单元格中单击，比如在 F10 中。,如果我销售了 800,000 并且佣金比率为 4%，则佣金额为 32,000，如果查看编辑栏，,我们可以看到，绝对引用指向 B 列，并且我们位于第 10 行中，乘以，我们正好位于 F 列中，,而我们仍使公式指向第 2 行。,单击此处，这里的销售额为 400,000，佣金比率为 1%，佣金额为 4,000。,查看编辑栏，我们可以看到，我们指向 B 列，正好位于第 6 行中，乘以，,又正好位于 C 列中，并且绝对引用指向第 2 行。,接下来，您将了解其他一些函数。,有关函数的更多信息,了解 Excel 2010：创建公式,指向视频可以显示视频控件。沿进度栏拖动或单击进度栏可以向前或向后移动。,Excel 具有很多函数或预定义公式，您可以使用它们执行各种计算。,在本课程前面的内容中，我演示了如何使用“开始” 选项卡上的“编辑” 组中的“求和” 函数。,这是“自动求和” 按钮，如果单击该按钮，您可以看到“求和” 和“平均值” 函数。,此外，还有用于计数以及定义最大值或最小值的函数，,您还可以使用其他很多函数。,我准备单击“公式” 选项卡，这是“函数库”，,请注意，此处也提供“自动求和” 按钮，,还包含“财务”、“逻辑”、“文本”、“日期和时间” 等函数。,这些种类繁多的函数使您能够了解可以使用函数执行的一些操作。,如果您知道函数的名称，则可以在库中查找该函数，,但更重要的是，如果您不知道函数的名称， 则可以单击这个“插入函数” 按钮。,我要查找这样的函数：我想计算购买的复印机的还款额。,将在此处打开“插入函数” 框，我在其中键入“付款”，,然后单击“转到”。,此处列表中的第一个函数是“PMT”，其描述显示在函数列表的下方：,计算在固定利率下，贷款的等额分期偿还额。,这看起来就是我需要的函数，因此我单击“确定”。,将打开“函数参数” 对话框，,需要在此对话框中输入公式的“参数”。,对于“PMT”函数，有三个必需的参数，,分别为“Rate”、“Nper”和“Pv”。,可能很难理解这些参数到底表示什么，但很快您就会明白它们的含义。,这两个参数“Fv”和“Type”，它们不是必需的。,在“Rate”框中单击，下面显示：,“Rate 各期利率。例如，当利率为 6% 时，使用 6%/4 计算一个季度的还款额”。,购买复印机的利率为“3.5%/”，因为我每月还款一次， 所以键入“12”。,然后，将插入点移到“Nper”框中。,“Nper”的描述为“总投资期或贷款期， 即该项投资或贷款的付款期总数”。,我要在 3 年内还清，因此这段时间内的付款期为“36”。,然后，将插入点置于“Pv”框中。,“Pv”是“从该项投资(或贷款)开始计算时已经入账的款项， 或一系列未来付款当前值的累积和”。,因此贷款总额为“3500”元，键入该值，然后单击“确定”。,结果为“(-102.56)”，即每月还款额。,还款额的格式设置为红色并且括在圆括号内， 因为这是设置财务函数格式的方式。,接下来，我将演示如何使用另一种方式输入同一函数。,如果您知道函数的名称，则无需使用“插入函数” 按钮。,我将再次使用“PMT”函数，但这次我要计算住房抵押的每月还款额。,在空白单元格中单击，键入等号，然后键入“P”表示还款，,您将看到公式记忆式键入。,此处显示一个以“P”开头的函数列表，这是“PMT”，,如果不确定它是否是所要查找的函数，只需查看此处的描述,计算在固定利率下，贷款的等额分期偿还额。,这正是我要查找的函数。可以继续键入该函数， 但也可以仅仅双击它就自动键入该函数和起始圆括号，,参数在下面列出。,现在，如果我不确定，实在想不起来有哪些参数，我可以单击此处的,“PMT”，这样会获得一个描述参数的帮助主题。,现在，第一个参数是“rate”，即利率，,它为“5%”，我用该值除以“12”，因为我将分 12 次还款，每月还款一次。,自己键入此公式时，您需要自己分隔参数，,因此，我键入逗号进行分隔。,“Nper”是下一个参数（它是 30 年内的付款期数）。,键入“30”表示 30 年，乘以“12”，因为我将分 12 次还款，每月还款一次。,键入逗号以分隔“Nper”和下一个参数“Pv”，即当前值。,它是贷款额的当前值，即“180000”。,您会发现后两个参数“fv”和“type”括在方括号内，,这意味着它们不是公式必需的。输入结束圆括号并按 Enter，,获得的结果是每月抵押还款额，即“(966.28)”。,接下来，我将演示一个可用于处理文本的函数。,您不仅能够使用函数处理数字，还可以使用函数处理文本。,例如，这是一张员工姓名列表，它们的格式非常混乱；,大写字母遍及各个位置，,我希望只有每个姓名的第一个字母大写，而其余字母均为小写。,将插入点置于单元格 B2 中，我希望公式位于 Nancy 的姓名旁边。,然后，单击“插入函数” 来搜索函数。,在“搜索函数” 列表中，我键入“proper names”，然后单击“转到”，返回这样的内容。,列表中的第一个函数称为“PROPER”，我看了看它的描述：,“将一个文本字符串中各英文单词的第一个字母转换成大写， 将其他字符转换成小写”。,这正是我需要的函数，因此我单击“确定”。 这样会在电子表格中的此位置输入“PROPER”，,“函数参数” 对话框仅包含一个参数“Text”。,我要在公式中使用 Nancy 的姓名，所以我在单元格 A2 中单击，,框中将显示 A2，然后单击“确定”。,这是 Nancy 的姓名，它已经从所有字母都大写更改为正确的姓名大小写。,我要复制公式以便重复应用它。,只需选择公式，按住鼠标左键并向下拖动，然后释放鼠标按钮， 即会得到大小写正确的姓名列表。,现在，有两列，而我只需要一列。,您会注意到，如果在 Nancy 的姓名中单击，然后查看编辑栏，,可以看到姓名后面是公式，我实际上不需要公式。,就像此处一样，我希望单击时在编辑栏中看到的是 Andrew 的姓名，而不是公式。,为此，可选择列表，单击右键并单击“复制”， 以便复制格式正确的姓名列表。,然后，选择格式混乱的姓名列表，并单击右键，在“粘贴选项” 框中，,此处有一个用于仅粘贴值的选项，这正是我需要的选项， 因为我不希望粘贴公式。,单击此选项，并按 Esc 以消除 B 列中所有这些姓名周围的点线框。,单击此处并查看编辑栏，我们可以看到 Nancy 的姓名，而不是公式。,在这里，您仍然可以看到公式。只需删除此列，,因为我不再需要它了。,现在只剩下一列。本课程到此结束。,有关练习的建议,键入一些简单公式进行加、减、乘、除运算。在同一公式中使用多个数学运算符。使用 sum 函数将数字相加。使用 sum 函数将列中的两个数字相加。使用相对单元格引用将公式从一个单元格复制到另一个单元格。,了解 Excel 2010：创建公式,有关练习的建议，续,使用绝对单元格引用创建公式。使用混合单元格引用创建公式。使用 PMT 函数创建公式。使用 PROPER 函数创建公式。,了解 Excel 2010：创建公式,联机练习（需要 Excel 2010）,测验问题 1,Excel 中的每个公式都以等号开头。（选择一个答案。）,了解 Excel 2010：创建公式,正确。错误。,测验问题 1,无论是简单还是复杂的公式，都以等号开头。,Excel 中的每个公式都以等号开头。,答案：,正确。,了解 Excel 2010：创建公式,测验问题 2,数学运算符优先级的第一个规则是什么？（选择一个答案。）,了解 Excel 2010：创建公式,先计算指数（根和幂）。先计算除法，再计算加法。先计算括号中的内容。,测验问题 2,首先计算括号中的内容。然后依次计算指数、乘除（从左到右），最后是计算加减（从左到右）。,数学运算符优先级的第一个规则是什么？,答案：,先计算括号中的内容。,了解 Excel 2010：创建公式,测验问题 3,Excel 将首先计算此算术问题的哪一部分：=30/5*3？（选择一个答案。）,了解 Excel 2010：创建公式,先计算 30/5。先计算乘法 5*3。,测验问题 3,先计算 30/5，将得到 6*3，最终值为 18。,Excel 将首先计算此算术问题的哪一部分：=30/5*3？,答案：,先计算 30/5。,了解 Excel 2010：创建公式,测验问题 4,如果您要将位于单元格 C1 和 C2 中的值（93 和 14）相加，为何在公式中使用单元格引用 (=C1+C2) 而不是直接将公式写为：=93+14？（选择一个答案。）,了解 Excel 2010：创建公式,公式看起来更为专业。公式更加准确。公式结果将在单元格值更改时自动更新。,测验问题 4,您可更新单元格值而无需更改公式。当每个单元格值发生更改时，公式结果将自动更新。如果您在公式中键入值自身，则必须在每次更改值时修改公式。,如果您要将位于单元格 C1 和 C2 中的值（93 和 14）相加，为何在公式中使用单元格引用 (=C1+C2) 而不是直接将公式写为：=93+14？,答案：,公式结果将在单元格值更改时自动更新。,了解 Excel 2010：创建公式,