Excel公式与函数

1、Excel系列培训之系列培训之- 函数与公式函数与公式 1:57:27目录2前言前言公式与函数简介公式与函数简介常用函数实战技巧常用函数实战技巧1:57:28前言31:57:284帮助解读按按F1进入帮助进入帮助1:57:285帮助解读用途用途语法语法说明说明示例示例函数名函数名参阅部分，了解相关函数参阅部分，了解相关函数/公式公式1:57:28公式与函数简介61:57:28公式与函数7什么是公式？ Excel中的公式是一种对工作表中的数值进行计算的等式，它可以帮助用户快速的完成各种复杂的运算。公式以“=”开始，其后是公式的表达式，如“=A1+A2”什么是函数？ 预先定义的预先定义的执行计算、

2、执行计算、分析等处理分析等处理数据任务的数据任务的特殊公式特殊公式1:57:28公式的组成要素8单元格引用单元格引用=(A2+67)/SUM(B2:F2)*单价单价除法除法运算符运算符函数函数加法运算符加法运算符常量常量单元格区域引用单元格区域引用名称名称特征特征公式的输入是公式的输入是以以“ =”开始开始，公式的计算，公式的计算结果显示在单元格结果显示在单元格中，中，公式本身显示在编辑栏公式本身显示在编辑栏中。中。1:57:28公式的操作9公式的输入与编辑公式的输入与编辑选中公式所在单元格，直接输入公式；选中公式所在单元格，并按下键输入公式；双击公式所在单元格输入公式选中公式所在单元格，单击

3、列标上方的编辑栏输入公式。公式的删除公式的删除使用键与键可清除单元格中的内容。公式的复制与填充公式的复制与填充5种方法实现公式的复制与填充方法一：拖拽填充；方法二：双击填充柄；方法三：快捷键填充，选中需要填充的区域，按；方法四：选择性粘贴；方法五：多单元格同时输入；1:57:28运算符10类型类型符符 号号说说 明明实实 例例算术运算符+,-,%, ,*,/负号,百分号,乘幂,乘和除加和减=8*-5 结果为-40=60*5% 结果为3=103 结果为1000=3*2/4 结果为1.5 比较运算符=，,=,=等于，不等于，大于，小于，大于等于和小于等于=（A1=A2） 判断A1与A2相等=（B1

4、“ABC”） 判断B1不等于ABC=（C1=5） 判断C1大于等于5文本运算符&连接文本=“CP”&“IC” 返回“CPIC”区域运算符:冒号=SUM（A1：A10） 引用A1至A10区域交叉运算符_(空格)单个空格=SUM（A1：B5 A4：D9） 引用交叉区域联合运算符，逗号=if（3+2=5，true，false） 分隔参数1:57:28常量参数115种常量数值常量，如：=（1+2）*3。日期常量，如：=DATEDIF（“2014-5-20”,NOW（），“m”）。文本常量，如：=“中南”&“机诚”。逻辑值常量，如：=VLOOKUP（“张三”，A:D，2，FALS

5、E）。错误值常量，如：=COUNTIF（A:A，#DIV/0!）1:57:28单元格引用12相对引用相对引用 从属单元格与引用单元格的相对位置从属单元格与引用单元格的相对位置不变，当复制或填充公式时，不变，当复制或填充公式时，引用单元格将自动调整。引用单元格将自动调整。=A1绝对引用绝对引用 公式所引用的单元格绝对位置不变。公式所引用的单元格绝对位置不变。=$A$1混合引用混合引用 所引用单元格的行或列方向之一的绝对位置不变，而另一方向位所引用单元格的行或列方向之一的绝对位置不变，而另一方向位置发生变化置发生变化。=$A1提示提示在引用单元格后可通过按一次或多次在引用单元格后可通过按一次或多次

6、“F4”快速切换引用类型。快速切换引用类型。1:57:28名称的定义与使用13增强公式的可读性；方便公式的统一修改；代替需要重复使用的公式，以简化操作，提高效率；代替单元格区域存储常量数据；解决数据有效性和条件格式中无法使用常量数组、交叉引用、跨表引用等问题。使用名称的好处使用名称的好处定义名称定义名称方法一：使用名称管理器定义名称方法二：使用名称框快速创建名称名称命名的限制名称命名的限制不能以数字开头、不能以R、C、r、c作为名称名、不能使用除下划线、点号、和反斜线以外的其它符号、字符不能超过255个、字母不区分大小写。1:57:29公式输入注意事项14公式中可以包括：数值和字符、单元格地址

7、、区域、区域名字、函数等不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同 如：=“ab”+25 出错 #VALUE1:57:29公式的常见错误15错误值类型错误值类型原因原因/含义含义解决方法解决方法/说明说明#当列宽不够显示数字，或者使用了负的日期、时间时，出现错误将鼠标指针移动到该列的列标右侧，双击或拖动鼠标，调整列宽即可#VALUE当使用的参数或对象类型错误或当自动更改公式功能不能更正公式时，出现错误通过公式的错误检查发现问题，修改错误值#DIV/0！当数字被零（0）除时，出现错误检查错误公式，修改公式中的单元格引用，或者在用作除数的单元格中输入不为零的值。

8、#NAME？当未识别公式中的文本时，如未加载宏或定义名称，出现错误出现这种错误的具体原因也有很多，当出现该情况时，一定要仔细地检查公式和函数，以找出具体的解决方法。 #N/A当数值对函数或公式不可用时，出现错误修改数值#REF!当单元格引用无效时，出现错误重新引用单元格#NUM!公式或函数中使用无效数字值时，出现错误修改数值#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误修改单元格区域1:57:29函数16=IF（A10,”正数正数”,IF（A1=18,符合要求,不符合要求)，确信以后，如果C26单元格中的数值大于或等于18，则C29单元格显示

9、“符合要求”字样，反之显示“不符合要求”字样。 特别提醒：特别提醒：本文中类似“在C29单元格中输入公式”中指定的单元格，在使用时，并不需要受其约束。 IF1:57:29条件函数SUMIF35主要功能：主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。 应用举例：应用举例：在c40单元格中输入公式：=SUMIF(D3:D22,男,F3:F22)，确认后即可求出“男”生

10、的语文成绩和。d3性别计算：=IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女,男),IF(MOD(MID(C3,15,1),2)=0,女,男)特别提醒：特别提醒：如果把上述公式修改为： =SUMIF(D3:D22,”女,F3:F22)，即可求出“女”生的语文成绩和； “男”和“女” 是文本型。 SUMIF1:57:29条件函数COUNTIF36主要功能：主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：使用格式：COUNTIF(Range,Criteria) 参数说明：参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件

11、表达式。 应用举例：应用举例：在C17单元格中输入公式：=COUNTIF(f3:f22,”=80”)，确认后，即可统计出f3至f22单元格区域中，数值大于等于80的单元格数目。 假如d3:d22区域内存放着员工的性别，则公式“=COUNTIF(d3:d22，”女”)”统计其中的女职工数量 特别提醒：特别提醒：允许引用的单元格区域中有空白单元格出现 COUNTIF1:57:29查找函数行列号37主要功能：主要功能：显示所引用单元格的行/列标号值。 使用格式：使用格式：ROW/COLUMN(reference) 参数说明：参数说明：reference为引用的单元格。 应用举例：应用举例：在C11单

12、元格中输入公式：=COLUMN(B11)，确认后显示为2（即B列）。 特别提醒：特别提醒：如果在B11单元格中输入公式：=COLUMN()，也显示出2ROW/COLUMN1:57:29查找函数元素返回38主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。 使用格式：INDEX(array,row_num,column_num) 参数说明：Array代表单元格区域或数组常量；Row_num表示指定的行序号（如果省略row_num，则必须有 column_num）；Column_num表示指定的列序号（如果省略column_num，则必须有 row_num）。 应用举例：

13、如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格（即C4）中的内容。 特别提醒：此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。 INDEX1:57:29查找函数位置查找39功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。 格式：MATCH(lookup_value,lookup_array,match_type) 参数说明：Lookup_value代表需要在数据表中查找的数值；Lookup_arr

14、ay表示可能包含所要查找的数值的连续单元格区域；Match_type表示查找方式的值（-1、0或1）。如果match_type为-1，查找大于或等于 lookup_value的最小数值。Lookup_array 必须按降序排列；如果match_type为1，查找小于或等于 lookup_value 的最大数值，Lookup_array 必须按升序排列；如果match_type为0，查找等于lookup_value 的第一个数值，Lookup_array 可以按任何顺序排列；如果省略match_type，则默认为1。 应用举例：如图4所示，在F2单元格中输入公式：=MATCH(E2,B1:B11

15、,0)，确认后则返回查找的结果“9”。 特别提醒：Lookup_array只能为一列或一行。 MATCH1:57:30查找函数列查找40功能：在数据表的首列首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。 格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；