[工作]Excel公式能帮我们做什么

[工作]Excel公式能帮我们做什么 Excel公式能帮我们做什么, 一、初步了解Excel公式 1. 什么是公式 前面已经介绍过，我们把“公式”列为不同于“数值”和“文本”之外的第三种数据类型。公式的共同特点是以“=”号开头，它可以是简单的数学式，也可以是包含各种Excel函数的式子。 2. 公式由哪些元素组成 输入到单元格中的公式均由等号开头，等号后面由如下五种元素组成: 运算符:例如“+”或者“*”号。 单元格引用:它包括单个的单元格或多个单元格组成的范围，以及命名的单元格区域。这些单元格或范围可以是同一工作中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。 数值或文本:前面介绍过的两种数据类型。例如，“100”或“新悦国际教育集团”。 工作表函数:可以是Excel内置的函数，如SUM或MAX，也可以是自定义的函数。 括号:即“(”和“)”。它们用来控制公式中各表达式被处理的优先权。 了解公式的组成是创建公式必备的基础。另外，Excel工作表中的公式最多可以由1024个字符组成，我们日常所创建的公式，超过100个字符的已经非常少，因此这个长度足够我们使用了。 看看公式长什么样子 二、公式能做什么? 简单地说，我们建立一个公式，提供给它相关的数据信息，目的是希望公式给我们一个或计算结果。从这一点上看，Excel公式和我们在数学中学过的公式的功能是一样的。所以，在以后的工作中，可以看到很多公式都是我们所熟知的数学式。 除此之外，由于Excel的公式还可以包含Excel的各种函数。因此，Excel公式除了可以实现传统的数学式具有的计算功能外，还可以执行很多特殊的任务。比如，结合IF函数，可以让公式具有选择性，根据不同的条件，得出不同的结果。后面的“计算业绩奖金”实际任务中，将会详细介绍在公式中嵌套IF函数解决问题。 Excel的内置函数有300多个，在公式中灵活使用这些函数，可以极大地提高公式解决问题的能力，轻松胜任各种复杂的任务。 (参阅:《Excel常用函数功能、用法及实例剖析》) 比如下图所示的三个公式功能举例:第一个公式自动计算出单价与数量的乘积得到金额，第二个公式自动计算所有销售额的和，第三个公式将姓和名合并在一起得到全名。 公式功能举例 下面我们通过一些简单的具体实例，加强对公式的感性认识。 三、公式的简单应用实例 下面我们通过一些简单的实例，加强对公式的感性认识。 1. 计算商品金额数值 下图所示的工作表中的A2和B2单元格分别存放着某种商品的“单价”和“数量”数值，为了计算“金额”数值，可以在D2单元格中输入公式“=250*8”(图 1)，通过后面的学习我们会知道，像这样使用具体数值建立的公式，适应性很差，因此用得也很少。 图 1 在D3单元格中输入公式“=A2*B2”，也可计算出“金额”数值(图 2)，这个公式使用了引用，使得公式得适应性加强。关于在公式中使用引用的知识后面会详细介绍。 图 2 2. 计算销售额总和 下图所示的工作表中的B2和B11单元格分别存放10天的销售额，为了计算这些天的销售额总和，可以在D2单元格中输入公式“=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11”(图 3)，这个公式和数学中的求和公式没什么两样，不过由于需要求和的项目太多，所以公式看起来比较长。 图 3 为了简化公式，我们也可以在公式中使用函数实现同样的功能。 选中D3单元格，输入公式“=SUM(B2:B11)”，可以看到得出的计算结果是一样的(图 4)，其中SUM为Excel的内置函数，功能为求出各项数值之和。 图 4 3. 用公式合并字符 下图所示的工作表中的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”。 根据这个规律，我们把包含相对引用的公式复制到其它单元格时，自己都可以推出将得到的公式了。 说明:前面介绍过使用公式填充柄复制公式，使用公式填充柄和单个复制公式的操作，所遵循的规律都是一致的。 二、三种不同的引用(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中的数值之和。 一、函数基础(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的帮助文档也是根据这一点来解释函数的功能的。 至于函数是如何计算返回数值我们则可以完全不必关心，有很多人把函数比作可以执行特殊任务的“黑盒子”，这个比喻很好，我们只需选择正确的“黑盒子”——通过它外表的功能说明(也就是函数的功能说明)，而不用管它里面的细节如何。 另一个重点是函数的参数，经过实际的示例和运用，这是很好解决的。 二、输入函数的方法(1) Excel内置了300多个包括“财务”、“日期与时间”等9个类别的函数，了解函数的功能之后，在公式中使用函数必须先输入它，因此下面介绍一下函数输入的方法，通常有如下两种。 1. 手动输入法输入函数的方法和技巧 如果我们熟悉函数名称和参数，那么可以手动输入。在图18所示的表格中，选中E2单元格，输入“=SUM(”后，会出现关于函数参数的提示(图 19)，其中的“number1，number2”，可以是单个具体的数值或单元格引用，也可以是多个单元格组成的区域，初学的朋友注意体会这一点。 然后用鼠标选中需要作为参数的B2到B23单元格区域(图 20)，回车即可。剩余那个关闭括号“)”，Excel会自动输入。 二、输入函数的方法(2) 2. 插入法输入函数的方法和技巧 如果对函数的拼写或参数不太清楚，那么可以用插入法输入函数。如果清楚函数是属于九大类别中的那一类，那么使用插入法将会得心应手。 选中图20中的E3单元格，选择菜单“插入?函数”命令，打开“插入函数”对话框，由于需要插入的函数AVERAGE，在“统计”类别里，因此单击“选择类别”下拉箭头，在弹出的下拉菜单里选择“统计”(图 21)，然后选中“选择函数”列表框里的“AVERAGE”，再单击“确定”按钮(图 22)。 接着弹出“函数参数”对话框，单击“Number1”文本框的选取按钮(图 23)，“函数参 数”对话框缩小为一行，用鼠标选中工作表中作为参数的相应区域即可(图 24)。 回车后，“函数参数”对话框变为原状，确认无误后单击“确定”即可完成函数的输入(图 25)。 有关Excel公式基本知识与技能的学习就先进行到这里，通常这部分的学习是比较枯燥无味的，所以笔者尽量通过用简单的实际例子说明，希望能够减轻大家的乏味感。下次我们会带大家进入让人期待的实际任务部分。