【EXCEL】函数公式基础

公式概述 、1. 公式定义：对工作表中的数值执行计算的等式； 2. 必须遵循的原则：        以“=”作为开始（自动前置“=”号：只有使用加减号作为公式开始时才可以）;“=”号后紧接运算数和运算符。 3. 什么是运算数：           即参加运算的参数：如数值常量（30），单元格引用（A1或A1：E10这样的区域）,自己定义的名称（定义名称的方法：Ctrl+F3）或工作表函数Sum、Average等。  举例：我写了个公式：=IF(LEN(A3)<4,X,IF((LEN(A3)>4)*(LEN(A3)<7),XX,XXX)。在此公式中：4,7均为数值常量，A3为单元格引用，X，XX，XXX为定义的名称（目的是突破2003版Excel函数的参数不能超过30个的限制，同时可以使简化公式、梳理思路），LEN为工作表函数； 4.什么是运算符：           运算符分为几大类：            （1）算术运算符；“-”（负号）、“%”、“^”、“*”、“/”、“+”、“-”（减号）                （2）比较运算符；“=”、“<>”、“>”、“<”、“>=”、“<=”                （3）文本运算符；“&”                （4）引用运算符：                                  “：”是连续区域运算符，如A1：B5 表示的就是:                                  “，”是联合运算符，函数中是用于函数参数或不相邻区域的连接，如SUM(A1，B3，C5)表示的就是求如下区域的和:         _(空格)是交叉运算符：用来表示选定的多个单元格的交叉区域：         关于对运算符的进一步理解与应用，请参见偶录制的“运算符的理解与使用”的动画。          实现方法请参见成果3 运算符的理解与使用。 5. 通过作业题，又发现了一些心得：           一般情况（非数组）下，运算符还可以替代逻辑函数: 即“+ ”与 OR函数，“*” 与AND函数可互换。           如在运用IF求等级时，这个公式是用AND函数表示的逻辑与的关系： =IF(L2<60,"不及格",IF(AND(L2<70,L2>=60),"及格",IF(AND(L2<80,L2>=70),"一般",IF(AND(L2<90,L2>=80),"良好","优秀"))))                    实际上，可以用*代替AND函数，即为 =IF(L2<60,"不及格",IF((L2<70)*(L2>=60),"及格",IF((L2<80)*(L2>=70),"一般",IF((L2<90)*(L2>=80),"良好","优秀"))))                    同理，OR函数可以用+号代替。                    另外，*和+可以替换一些需要使用IF函数判断返回数值的问题，如： =IF(B5="组长",200,100)                    这个公式可以替换为 =(B5="组长")*100+100                    还有 =IF((E5>=$P$5)*(E5<$P$4),$Q$5,IF((E5>=$P$4)*(E5<$P$3),$Q$4,(E5>$P$3)*$Q$3))                    这个公式可以用 =(E5>=$P$5)*(E5<$P$4)*$Q$5+(E5>=$P$4)*(E5<$P$3)*$Q$4+(E5>$P$3)*$Q$3 代替。 6. 补充知识：运算符优先级：引用运算符>算术运算符>文本运算符>比较运算符 公式的输入、编辑、复制 7.  公式的输入：        （1） 鼠标点击单元格实现引用；         （2） 手写。 8.   公式的编辑：         （1）首先选中有公式的单元格：双击；         （2） 按F2；         （3） 在编辑栏编辑；           （4） 在公式编辑时，文本常量一定要放在一对双引用""之间，否则会出现#Name？错误：这是因为Excel不能识别此文本。 如果定义了单元格或单元格区域的“名称”后，引用时，不是文本，那么就不用添加引号。 9. 公式的复制：          （1）鼠标拖动法：选中欲复制公式的单元格右下角，当出现十字后，左键按住向欲复制公式的方向拖动即可，一次只能一个方向          （2）选择性粘贴法：在对不连续的区域复制公式时尤为有效！          （3）鼠标双击自动填充法：（如果欲往下方复制公式，只要该列左边列或右边列有连续的数据区域，即可双击完成快速的公式填充，此方法在公式需要往下大量复制时尤为方便！）；           （4） 如果自动填充法不能很好的解决问题，那就用菜单中的“编辑—填充”命令来解决；这个填充命令有很大的好处，可以往上下左右四个方向填充，非常方便！            （5）当然，最强大的输入方式是：选中欲复制公式的区域，然后在编辑栏输入公式，Ctrl+Enter批量输入！！！！ 10. 输入、编辑，复制公式的一些快捷的方式：请见偶录制的“如何在8秒内对中国历年累计金牌数进行汇总”的动画。 丰富的引用方式 11. 引用：指的是在公式中，当用户输入单元格地址作为参与运算的参数时，就是对单元格进行引用； 12. 单元格的引用样式分为A1样式与R1C1样式；      首先来个直观的对比：       A1样式与R1C1样式实例对比图； 13.  A1样式详释:      （1） 在A1样式的引用中，用字母和数字标识单元格地址。其中字母表示列标，数字表示行号。对于引用单元格区域，使用区域左上角的单元格的标识符、“：”，区域右下角的单元格的标识符来共同标识。如： F2   第F列和第二行交叉处的单元格 D1:D3        第D列中第1~3行的单元格 D2:G2        第二行中的D列到第G列的单元格 B2:F7         B列第2行到F列第7行的单元格 （2）省略了表示行号的数字时，表示全选列；同理，省略了表示列标的字母时，表示全选行；如：                      “H:H”表示H列全部单元格；                      “A:C”表示A列到C列的全部单元格；                      “3:3”表示第3行的全部单元格；                      “2:10”表示第2行到第10行的全部单元格 （3）单元格的引用类型：相对引用、绝对引用和混合引用；              相对引用：复制公式时，Excel会根据目标单元格与源公式所在单元格的相对位置，相应地公式的引用标识。（A1）       绝对引用：复制公式时，不论目标单元格的所在位置如何改变，绝对引用所指向的单元格区域都不会改变。绝对引用符为“$”。（$A$1）        混合引用：混合运用了以上两种引用方法。分为：行相对、列绝对；列绝对、行相对（$A1,A$1）        不同引用的方式在于是否使用了“$”来锁定行号和列标。        关于混合引用的应用，请参见：成果1中：《自己学做的乘法表》中的方法1； 14. R1C1样式详释： （1）如何切换到R1C1样式：                            （2）R1C1样式以“R”+行号+“C”+列号来标记单元格位置；                      （3） R1C1样式必须是行号在前、列号在后，这与A1样式完全相反；                      （4） 如R1C6表示第1行与第6列交叉处的单元格，即A1样式中F1                      （5）R1C1样式的单元格的引用类型：相对引用、绝对引用和混合引用；                                    R1C1样式的绝对引用比较简单，如=R2C2表示的是不管此公式在哪里被输入，它引用的均是单元格$B$2;                                                                         R1C1样式的相对引用：                                      R1C1样式的混合引用：                                      这是一个99乘法表：                                         它的A1样式公式是这样的：                                          它的R1C1样式是这样的：                      6）        从上面的几个图对比看出，A1样式中“看起来的许多不同”公式在R1C1样式下其实就是同一个公式，这就是为什么下拉右拉能复制公式的原因：A1样式只是一个显示方法，而Excel真正的寻址方式是R1C1样式，复制公式时Excel是以R1C1样式来存储和复制公式，然后再以A1样式翻译过来便于我们理解。个人觉得R1C1样式非常好用，就像坐标系一样，当前单元格为RC，右和下为正，左和上为负。呵呵。。比如在用vlookup()函数时，当数据区域列数很多时，用R1C1样式第几列一目了然。又如在VBA编程及条件格式的设置中。                      7）        小结：单元格的4种引用样式：                      8）        关于R1C1样式的应用，请参见：                                       成果1：自己学做的乘法表中的方法3。                                      成果2：巧用R1C1样式实现随机出题系统； 15.        工作表、工作簿的引用；                      1）        工作表引用：=工作表名！（半角叹号）引用区域；如=Sheet2!A1                      在引用工作表时，若引用的工作表名称是以数字开头或包含空格及以下的字符时：~ ! @ # $ % ^ & ` ( ) - + = , | " ; { }  公式中引用的工作表名称将被一对半角单引号' '所包含。                      如果漏加了' '，会发生如下的错误：                      2）        工作簿引用：[文件名.扩展名]文件名！引用区域；如=[Book1.xls]Sheet1!$A$1；                       如果被引用的工作簿未被打开，则被引用工作簿名称前还要加上文件的路径:如= 'E:\Excelhome\ [Book2.xls]Sheet1'!$A$1；                      3）        所以，减少出错的方法就是点击引用工作表或工作薄的名称后再点击单元格实现引用。这样Excel会自动加上所需要的各种符号。呵呵。                      4）        Excel默认工作表引用为相对引用，工作簿引用为绝对引用，可以使用F4来切换状态； 16.        技巧深入思索：                      1）        按F4键可以在快速相对引用和绝对引用之间切换。如=A1,第一次按F4键是绝对引用，第二次为行绝对，第三次为列绝对，第四次为相对引用。通常，大家认为F4键必须得先抹黑选中表示单元格引用的字符后才能使用，其实F4键在另外三个地方都可以使用此功能：                      在单元格输入公式编辑时，光标在单元格引用的字符后方闪动时，如下动画--F4字符后方闪动.gif：（注意光标不能移到其他地方，否则可能出错）如下动画--F4光标不能随便移动.gif:                      在使用“插入函数”对话框中的“函数参数”对话框输入参数时，如下动画--F4“函数参数”对话框.gif：                      在进行工作表引用，工作簿引用时，如下动画--F4工作表、工作簿引用.gif                      2）        实际应用中当编辑公式时，当需要在某个单元格输入一个公式，然后下拉右拉得到正确结果的时候通常会用到F4来切换相对引用和绝对引用。如课前热身题2中的《车票售价与人数的关系的快速识别图》; 又如课前热身题1中有同学的Sumif函数中的区域引用全是相对引用，下拉右拉不能得到正确的结果，这时就需要F4出马了。。                      3）        混合引用时下拉右拉如何得到正确的公式：以编辑公式所在的单元格为坐标，行项行锁定，列项列锁定。  函数概述 17.        定义：函数是由Excel内部预先定义用来执行计算、分析的功能模块，作为公式的一部分来参与计算； 18.        函数的结构：通常是由函数名称、括号、参数、半角逗号（作为参数分隔符）组成；               函数结构示意图：                               1）        函数的参数必须为有效参数，如SUMIF中的第1个参数Range就必须是区域或区域名称而不能为其他类型；                 2）        必须输入的函数参数不能省略，即使参数缺省也必须使用分隔符“，”来占位；如=OFFSET(A1,2,,)返回的是A3单元格的值，其中移动2行，移动多少列缺省就为1，但要占位。                 3）        不需要参数的函数有Now(), Today(), Rand(), Row(), Column()等；                 4）        关于Rand()函数的应用请参见：成果2巧用R1C1样式实现随机出题系统； 19.        函数的输入：                 1）        使用插入函数向导（菜单—插入—函数，或者直接按编辑栏左边的fx按钮）                 2）        手动输入                 3）        输入函数过程中要善于使用F1帮助； 20.        其他技巧： 在单元格输入公式后，按【Enter】可得出计算结果；按【Tab】可在计算出结果的同时选中同行的下一单元格；按【Ctrl+Enter】键则在计算出公式结果后，当前单元格仍保持选中状态。同时，【Ctrl+Enter】也是批量输入公式的方法。