Excel公式应用初步.doc

Excel公式能帮我们做什么？一、初步了解Excel公式 1. 什么是公式 前面已经介绍过，我们把“公式”列为不同于“数值”和“文本”之外的第三种数据类型。公式的共同特点是以“=”号开头，它可以是简单的数学式，也可以是包含各种Excel函数的式子。 2. 公式由哪些元素组成 输入到单元格中的公式均由等号开头，等号后面由如下五种元素组成： 运算符：例如“+”或者“*”号。 单元格引用：它包括单个的单元格或多个单元格组成的范围，以及命名的单元格区域。这些单元格或范围可以是同一工作表中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。 数值或文本：前面介绍过的两种数据类型。例如，“100”或“新悦国际教育集团”。 工作表函数：可以是Excel内置的函数，如SUM或MAX，也可以是自定义的函数。 括号：即“(”和“)”。它们用来控制公式中各表达式被处理的优先权。 了解公式的组成是创建公式必备的基础。另外，Excel工作表中的公式最多可以由1024个字符组成，我们日常所创建的公式，超过100个字符的已经非常少，因此这个长度足够我们使用了。 看看公式长什么样子Excel公式能帮我们做什么？二、公式能做什么? 简单地说，我们建立一个公式，提供给它相关的数据信息，目的是希望公式给我们一个答案或计算结果。从这一点上看，Excel公式和我们在数学中学过的公式的功能是一样的。所以，在以后的工作中，可以看到很多公式都是我们所熟知的数学式。 除此之外，由于Excel的公式还可以包含Excel的各种函数。因此，Excel公式除了可以实现传统的数学式具有的计算功能外，还可以执行很多特殊的任务。比如，结合IF函数，可以让公式具有选择性，根据不同的条件，得出不同的结果。后面的“计算业绩奖金”实际任务中，将会详细介绍在公式中嵌套IF函数解决问题。 Excel的内置函数有300多个，在公式中灵活使用这些函数，可以极大地提高公式解决问题的能力，轻松胜任各种复杂的任务。 （参阅：Excel常用函数功能、用法及实例剖析）比如下图所示的三个公式功能举例：第一个公式自动计算出单价与数量的乘积得到金额，第二个公式自动计算所有销售额的和，第三个公式将姓和名合并在一起得到全名。公式功能举例下面我们通过一些简单的具体实例，加强对公式的感性认识。Excel公式能帮我们做什么？三、公式的简单应用实例下面我们通过一些简单的实例，加强对公式的感性认识。 1. 计算商品金额数值 下图所示的工作表中的A2和B2单元格分别存放着某种商品的“单价”和“数量”数值，为了计算“金额”数值，可以在D2单元格中输入公式“=250*8”(图 1)，通过后面的学习我们会知道，像这样使用具体数值建立的公式，适应性很差，因此用得也很少。图1在D3单元格中输入公式“=A2*B2”，也可计算出“金额”数值(图 2)，这个公式使用了引用，使得公式得适应性加强。关于在公式中使用引用的知识后面会详细介绍。图22. 计算销售额总和 下图所示的工作表中的B2和B11单元格分别存放10天的销售额，为了计算这些天的销售额总和，可以在D2单元格中输入公式“=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11”(图 3)，这个公式和数学中的求和公式没什么两样，不过由于需要求和的项目太多，所以公式看起来比较长。图3为了简化公式，我们也可以在公式中使用函数实现同样的功能。 选中D3单元格，输入公式“=SUM(B2:B11)”，可以看到得出的计算结果是一样的(图 4)，其中SUM为Excel的内置函数，功能为求出各项数值之和。图43. 用公式合并字符 下图所示的工作表中的A2和B2单元格分别存放着“姓”和“名”字符，为了得出“姓名”字符串，可以在D2单元格中输入公式“=A2&B2”(图 5)，其中“&”是一个运算符号，我们称它为连接符号，它的功能为把两个文本字符或文本字符串连接起来。图5除了这种方法外，我们也可以使用函数达到同样的目的。选中D3单元格，输入公式“=CONCATENATE(A2,B2)”，回车后得到的结果和前面的公式是一致的(图 6)。图6其中函数CONCATENATE的功能为将几个文本字符串合并为一个文本字符串。 通过上面的三个简单实例，大家对公式已经有了初步的了解。在实际工作中，公式可以解决问题非常多，它和函数结合在一起，极大的提升了Excel对数据的分析与处理能力。下面我们还将学习一些与公式相关的基础知识与技能，在最后的实际任务部分就可以利用它们一展身手了，后面的内容更加精彩，期待你的继续关注。Excel公式应用：“引用”详解一、在公式中使用引用(1)我们在往单元格中输入公式回车以后，单元格中会显示公式计算的结果，如果要查看公式，可以选中该单元格，单元格中的公式则会显示在编辑栏中。 我们先来看看如果不使用引用将会有什么弊端。 1. 不使用引用的弊端 在图1所示的工作表中，A列和B列单元格分别存放着“单价”和“数量”数据，显然它们都是“数值”类型的，为了求出“FX82MS”的“金额”数值，可以在D2单元格中输入公式“=250.5*12”，回车后D2单元格显示该公式的计算结果“3006”。 图 1 这个公式中并没使用引用，而是使用的具体数值。下面我们来分析一下不使用引用的两个缺点： 首先，假设“单价”或“数量”的值改变了，我们必须对公式进行修改，这让人觉得这个公式相当死板，毫无灵活性。 其次，假设我们想求出图1中后面的各种商品的“金额”数值，必须重新逐个输入公式，这样不仅费时费力，而且还容易出错。 如果在公式中使用引用则可以很好地克服上述的两个缺点，下面我们仍利用图1的表格进行说明。Excel公式应用：“引用”详解一、在公式中使用引用(2)     2. 在公式中使用引用的方法和技巧 前面我们说过，所谓“引用”，指的是引用相应单元格或区域中的数据，而不是具体的数值。也就是说，如果公式中引用了单元格B2，当B2中的数值改变以后，公式会自动根据改变后的数值重新进行计算，而不必重新修改公式。如此一来，公式的灵活性大大加强。下面我们看看如何在前面的例子中使用引用。 选中D2单元格，输入公式“=B2*C2”，回车后可以看到D2单元格中显示公式计算的结果“3006”(图 2)。图 2 下面先介绍该公式的输入技巧，然后再改变B2或C2单元格中的值，测试该公式的适应性。 * 公式的输入技巧 也许大部分朋友是直接通过键盘输入上述公式的每个字符的，其实还有更简便的方法：选中D2单元格输入“”号后，用鼠标左单击一下B2单击单元格，可以看到D2单元格等号后显示了蓝色的“B2”字符，上方的编辑栏中的等号后面也显示“B2”字符(图 3)。图 3 接着通过键盘输入符号“*”，再用鼠标单击一下C2单元格，回车后即可完成公式的输入，D2单元格中显示公式计算的结果。 说明：用这种方法输入公式不仅快速，而且由于是通过直观的点击，所以也不容易出错，建议初学的朋友多加使用。如果要输入单元格区域，则用鼠标选择相应的区域即可。 * 测试公式的适应性 选中C2单元格，把数值改为“120”，回车后可以看到D2单元格已根据更改后的数值进行计算并显示出新的计算结果(图 4)。图 4 由此可见，在公式中使用引用比使用具体的数值，更具适应性。下面我们来看一下使用引用的另一个优越性，那就是便于快速复制公式，以适应其它的项目。Excel公式应用：“引用”详解一、在公式中使用引用(3)     3. 使用公式填充柄轻松复制公式 下面我们的任务是求出后面商品的“金额”数值。可能有朋友说，这很简单，只要选中单元格D3输入公式“=B3*C3”，后面以此类推即可。用手动输入的方法的确可以解决问题。不过，由于后面所有公式的规律性是一致的，所以可以使用更加简便的方法复制公式，即公式填充柄。 先选中D2单元格，将鼠标指针移至右下方的小黑方块处，指针变为黑色实心的十字形(图 5)，这时按住鼠标左键，向下拖拉至恰当位置，放开鼠标左键即可(图 6)。图 5 图 6 放开鼠标左键后，相应单元格中显示出计算结果(图 7)。图 7 回车后，可以选中D列相应单元格，在编辑栏中查看其中的公式(图 8)。可以发现，当我们用公式填充柄复制公式时，单元格的列字母不变，行数字依次在原来的数字上加“1”，这样一来正好得到我们需要的公式。Excel的公式填充柄的确是一个聪明而方便的特性，在日常的应用中使用非常多，在后面的实例中我们还会多次使用，因此请大家注意掌握这项基本技能。图 8 本部分的主要目的是介绍在公式中使用引用，我们可以回想一下，如果在公式还是中使用固定的数值，而不使用引用，那么使用公式填充柄得到的，只是死板地把第一个公式复制(图 9)，而不能计算出后面商品的“金额”数值。图 9 所以，在这里我们可以体会到使用引用的第二个好处，那就是可以使用公式填充柄快速得出公式规律一致的其他项目的公式。Excel公式应用：“引用”详解二、三种不同的引用(1)     上面我们初步介绍了在公式中使用引用的基本方法，这里介绍一下不同引用的区别。 在公式中引用单元格或者区域时，引用的类型可分为以下三种： 相对引用：当把公式复制到其它单元格中时，行或列的引用会改变。所谓行或列的引用会改变，即指代表行的数字和代表列的字母会根据实际的偏移量相应改变。 绝对引用：当把公式复制到其它单元格中时，行和列的引用不会改变。 混合引用：行或列中有一个是相对引用，另一个是绝对引用。 在实际工作中，常常需要对公式进行复制操作。从上面不同类型的引用的定义中可以了解，当复制公式时，不同的引用会将对公式产生不同的影响，从而对计算结果产生不同的影响。所以，在创建公式之前，我们必须要搞清不同引用类型的区别，下面通过一个简单的实例进行说明。 1. 相对引用 在图10所示的工作表中的D2单元格中输入公式“=B2*C2”，回车后，再选中D2单元格，执行复制命令。图 10 选中D3单元格，执行粘贴命令。回车后，D3单元格立即显示计算的结果，我们选中D3，通过编辑栏可以查看复制到D3单元格中的公式为“=B3*C3”(图 11)。图 11 和原来的公式“=B2*C2”相比，列的引用未改变，行的引用改变，由原来的2变为3。由此可见，当我们在公式中使用引用时，Excel默认建立为相对引用。 还有一点，前面曾讲到：所谓行或列的引用会改变，即指代表行的数字和代表列的字母会根据实际的偏移量相应改变。 有的朋友可能对“根据实际的偏移量相应改变”的含义不大理解。我们结合刚才的操作说明一下。把D2单元格的公式复制到D3单元格，和公式原本所在的单元格D2相比，列并未发生改变(都在D列，所以公式中代表列的字母不发生改变)，而行发生了改变(下移了一行，所以公式中代表行的数字应增加1)，所以D3中得到的公式为“=B3*C3”。 根据这个规律，我们把包含相对引用的公式复制到其它单元格时，自己都可以推出将得到的公式了。 说明：前面介绍过使用公式填充柄复制公式，使用公式填充柄和单个复制公式的操作，所遵循的规律都是一致的。Excel公式应用：“引用”详解二、三种不同的引用(2)     2. 绝对引用 可能有朋友读到这里有一个问题：既然Excel默认创建的引用为相对引用，那么，如果想创建含绝对引用的公式，该如何通知Excel呢？ 用一个例子说明：如果在公式中相对引用单元格B2，使用符号“B2”即可；如果在公式中绝对引用单元格B2，则使用符号“$B$2”。可以看到，使用绝对引用时，列字母和行号前都加了一个美元符号“$”。 下面来看一下如果公式中使用绝对引用，对复制公式时的影响。 在图11所工作表中的D2单元格中输入公式“=B2*C2”，现在把公式中的相对引用改为绝对引用，修改方法有两种。 方法一：先选中D2单元格，再把光标插入点定位于编辑栏的“”符号后，通过键盘输入美元符号“$”。然后用同样的方法输入其余三个美元符号“$”，输入完成后如下图所示。 图 12 方法二：这是一种更简单的方法。还是先选中D2单元格，然后把光标插入点定位于编辑栏的“B”和“2”字符之间，按一下键盘“F4”键，字符“B”和“2”之前分别被添加了“$”符号(图 13)。  如果这时再按一下“F4”键，字符“B”之前的“$”将会去掉，保留字符“2”前的“$”(图 14)，此时表示对单元格B2进行混合引用，其中列为相对引用，行为绝对引用。  再按几次“F4”键，注意仔细观察，可以看到在几种引用之间进行切换，所以大家可以体会到，使用这种方法设置引用类型是相当方便的。 用同样的方法将修改单元格“C2”为绝对引用，完成后单击回车确定。 接下来用公式填充柄，将公式复制到D11单元格，得到的效果如下图。 回车后查看一下D列各单元格的公式，发现都是“=$B$2*$C$2”(图 16)，公式并没有发生改变，因为我们采用的是绝对引用，引用的只是单元格的绝对地址，不会因为偏移量发生任何改变。 3. 混合引用 相信经过前面两个测试，大家对相对引用和绝对应用的区别已经有了初步了解，而混合引用的道理和它们是相通的，因此请大家自行测试一下混合引用即可。走近Excel函数一、函数基础(1)     1. 什么是函数 我们这里所说的函数，是Excel内置函数的简称。到底什么是函数呢？我们可以把它理解为预先建立好的公式，通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算，用于执行简单或复杂的计算。例如，ROUND 函数可将指定单元格中的数字四舍五入。 说明：除了Excel的内置函数之外，我们还可以定义自己的函数，在后面的课程中将会介绍。函数应用举例：在下图所示的工作表中，选中E2单元格格输入“=SUM(B2:B23)”，即可得到相应的计算结果单元格B2直到B23中的数值之和。走近Excel函数一、函数基础(2)     2. 函数的结构和组成 函数的结构：函数以等号 (=) 开始，后面紧跟函数名称和左括号，然后以逗号分隔输入参数，最后是右括号。 函数的组成： 下面我们通过一个具体的实例来介绍一下函数的组成。如下图所示，B2到B23单元格分别存放着每天的销售额数据，下面我们想得出三个答案：总销售额、平均每天销售额和最大销售额。 假设我们已经知道，为了求出上述三个答案，应该分别使用函数：SUM、AVERAGE和MAX。 接下来在如图18所示的工作表中，选中E2单元格格输入“=SUM(B2:B23)”，选中E3单元格输入“=AVERAGE(B2:B23)”，选中E4单元格输入“=MAX(B2:B23)”。即可得到相应的计算结果。 通过这个实例，可以看到，函数由两部分组成：一是函数的名称。如果要查看可用函数的列表，可单击一个单元格并按 Shift+F3。如SUM、AVERAGE、MAX都是函数名称，其实我们从它的名称就可以估计到这个函数的功能。二是函数的参数。参数可以是数字、文本、逻辑值（例如 TRUE 或 FALSE）、数组、错误值（例如 #N/A）或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或其他函数。 参数工具提示：在键入函数时，会出现一个带有语法和参数的工具提示。例如，键入 =ROUND( 时，工具提示就会出现。工具提示只在使用内置函数时出现。  3.使用函数注意事项上面介绍了关于函数最基本的知识，那么，怎样才能提高我们使用函数的水平和能力呢？下面两点是最基本的：一是搞清楚具体函数实现什么功能、返回什么值。二是明白具体函数使用什么样的参数。 学习函数时首要一点，是搞清楚这个函数实现什么功能、返回什么值。通过上面的实例，我们可以发现，使用函数得出答案和自己建立公式得出答案是有些差别的。自已建立公式，必须搞清楚每个细节，而使用函数则不必。Excel的帮助文档也是根据这一点来解释函数的功能的。 至于函数是如何计算返回数值我们则可以完全不必关心，有很多人把函数比作可以执行特殊任务的“黑盒子”，这个比喻很好，我们只需选择正确的“黑盒子”通过它外表的功能说明(也就是函数的功能说明)，而不用管它里面的细节如何。 另一个重点是函数的参数，经过实际的示例和运用，这是很好解决的。走近Excel函数二、输入函数的方法(1)     Excel内置了300多个包括“财务”、“日期与时间”等9个类别的函数，了解函数的功能之后，在公式中使用函数必须先输入它，因此下面介绍一下函数输入的方法，通常有如下两种。 1. 手动输入法输入函数的方法和技巧 如果我们熟悉函数名称和参数，那么可以手动输入。在图18所示的表格中，选中E2单元格，输入“=SUM(”后，会出现关于函数参数的提示(图 19)，其中的“number1，number2”，可以是单个具体的数值或单元格引用，也可以是多个单元格组成的区域，初学的朋友注意体会这一点。 然后用鼠标选中需要作为参数的B2到B23单元格区域(图 20)，回车即可。剩余那个关闭括号“)”，Excel会自动输入。   走近Excel函数二、输入函数的方法(2)     2. 插入法输入函数的方法和技巧 如果对函数的拼写或参数不太清楚，那么可以用插入法输入函数。如果清楚函数是属于九大类别中的那一类，那么使用插入法将会得心应手。 选中图20中的E3单元格，选择菜单“插入函数”命令，打开“插入函数”对话框，由于需要插入的函数AVERAGE，在“统计”类别里，因此单击“选择类别”下拉箭头，在弹出的下拉菜单里选择“统计”(图 21)，然后选中“选择函数”列表框里的“AVERAGE”，再单击“确定”按钮(图 22)。 接着弹出“函数参数”对话框，单击“Number1”文本框的选取按钮(图 23)，“函数参数”对话框缩小为一行，用鼠标选中工作表中作为参数的相应区域即可(图 24)。     回车后，“函数参数”对话框变为原状，确认无误后单击“确定”即可完成函数的输入(图 25)。 有关Excel公式基本知识与技能的学习就先进行到这里，通常这部分的学习是比较枯燥无味的，所以笔者尽量通过用简单的实际例子说明，希望能够减轻大家的乏味感。下次我们会带大家进入让人期待的实际任务部分。