第十三讲 EXCEL中的公式与函数

第十三

讲EXCEL中的公式和函数教学目标:掌握如何在工作表中创建公式,以及怎样在工作表中使用公式与函数进行数据计算.重点:

公式、函数的使用主要知识点：一、运算符 （掌握）二、公式创建 （掌握）三、函数应用 （掌握）四、单元格引用 （掌握）五、单元格命名和名称应用 （掌握）六、财务函数（Pmt、Ipmt、Pv、Fv、Nper）（了解）七：公式审核 （了解）一、运算符1、算术运算符：+、-、*、/、%、^（乘方）2、比较运算符：=、>、<、>=、<=、<>3、文本运算符：&（又称连接运算符,用于将两个文本值连接到一起）4、引用运算符：连续区域（：）例A1：B10不连续区域（，）例A1：A10，C1：C10交叉区域（空格）例B1:B10A1:B3（空格隔开）2、运算顺序：：空格，-（负号）%^*/+-&=、<、>、>=、<=、<>优先级从高到低二、在单元格中建立、编辑公式：1、创建公式：输入“=（20+30）/2”（直接输入数据）输入“=B3+C3+D3”（用地址表示其中的数据）输入“=sum（B3:D3）”（用函数计算）按“Enter”键之后，便可得出计算结果2、公式的复制：选中区域，输入公式后，按ctrl+Enter，则所有被选中的单元格自动被公式填充；输入公式，拉动填充句柄；三、函数应用基础

格式：=函数名（参数，……）1、输入函数：输入=函数名（参数）单击“fx”按钮执行“插入→函数”命令单击常用工具栏上“自动求和”按钮2、参数：表达式、单元格地址或名称3、常用函数：（1）求和：=SUM(number1,number2,……）注：Number参数最多为30个，此参数可是数字，公式及单元格区域；若参数中包含空白单元格、逻辑值、文本或错误值将被忽略；（2）求平均值：=Average(number1,number2,……）注：区域中有空格则忽略，而0值则参与计算（3）求最大、最小值：=Max(number1,number2,……）=min(number1,number2,……）注：若参数中不包含数字，则返回值为0（4）链接函数：=Hyperlink（link_location,friendly_name)创建链接，用以打开存储在磁盘、网络服务器、internet或intranet中的文件；例：Hyperlink（“”,“你们好”）注：可直接创建链接在单元格上右击——超链接插入——超链接（ctrl+k)——找到链接的文件——确定（5）IF

函数（难点）If(logical_test,value_if_true,value_if_false)执行真假判断IF嵌套IF（条件1，评价1，IF（条件2，评价2，IF（……）））IF函数函数名称：IF

主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。

使用格式：=IF(Logical,Value_if_true,Value_if_false)

参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。

应用举例：在C29单元格中输入公式：=IF(C26>=18,“符合要求”,“不符合要求”)，确定以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。6）min（参数1,参数2,参数3,……） 求最小值（对数字进行取最小值运算）7）power（x,y）表示X的Y次方8）count（参数1,参数2,参数3,……） 统计函数（对数字个数进行统计运算）9）sumif（条件区，条件，求和区） 条件求和函数（根据指定条件对若干单元格求和）当“求和区=条件区”时，求和区可省略等价于“条件求和向导”，执行“工具→向导→条件求和”命令10）countif（区域，条件） 条件统计函数（计算区域中满足给定条件的 单元格的个数）11）lookup（条件，条件区，结果区） 查询函数（查找满足某种条件的值）“条件列”数据必须要按升序排序4、数组计算与数据计算的差别就在于确认方式不同，按组合键“Ctrl+Alt+Enter”来确认四、单元格引用

（引用：指引用某一地址指向的单元格的值）

类型：相对A1、绝对$A$1、混合$A1、A$1转换：F4

1、相对引用：指用单元格所在的列标和行号为其引用例：“=SUM（A1：A4）”注：公式复制后，相对引用地址公自动随着位置的移动而相应变化；2、绝对引用：公式移动或复制后，单元格引用不会变化例：“=$A$1+$A$2”3、混合引用：例：$A2,A$24、单元格名称引用：选取区域——在编辑栏左框中输入名称，使用时可输入“=名称1+名称2”等Eg：乘法表：=列中的列绝对*行中的行绝对五、单元格命名及名称应用单元格命名：是给选定的单元格或单元格区域起一个名字命名规则：单元格名称中的字符只能包括字母、数字、下划线或小数点单元格名称要以字母开头单元格名称中不能有空格单元格名称不能使用单元格地址单元格名称不区分字母的大小写单元格名称的最大字符长度为255个字符1、命名方法1：

1）选中单元格或单元格区域

2）在名称框中输入“名称”

3）按“Enter”键确定方法2：

1）选中单元格或单元格区域

2）执行“插入→名称→定义”命令

3）输入名称，单击“确定”按钮“定义名称”对话框如下图所示。2、指定名称以“行标”或“列标”来作为该行或该列的名称方法：

1）选中单元格区域（包含行标题和列标题）

2）执行“插入→名称→指定”命令

3）选方式

4）单击“确定”

“指定名称”对话框如右图所示。3、名称应用

1）应用于计算中，相当于绝对引用，不会随着结果单元格变化而变化

2）用于选定单元格区域，只要在“名称框”选中该名称，按“Enter”键，即可选中该名称的区域。六、财务函数1、Pmt（Rate，Nper，Pv，fv，type）计算基于固定利率及等额分期付款方式，返回贷款每期付款额。

Type取值是0：年末或１：年初2、Ipmt（Rate，per，Nper，Pv，Fv，Type）

Per其取值为１～nper之间计算基于固定利率及等额分期付款方式下，返回给定期间内对投资的利息偿还额。3、Nper（rate，pmt，pv，fv，type）计算基于固定利率及等额分期付款方式下，

返回某项投资的总期数，即应当付多少期才能还

清贷款的本金和利息。4、Fv（rate，nper，pmt，pv，type）用于计算资金在固定利率及等额分期付款方式下将来某个时期的价值。

5、Pv（rate，nper，pmt，fv，type）可以计算资金当前的价值，是与未来值相对的，用ＰＶ函数可将未来的资金折算成现值，未来资金支付的形式，可以是一次，也可以是等额支付。6、财务函数应用实例七、公式审核错误问题：公式所有的括号必须成对出现在引用单元格区域时，第一个单元格与最后一个单元格之间用冒号隔开在应用工作薄中其他单元格区域时一定包含工作表名称和路径，如“[0804学生成绩表.xls]sheet1!$e$4”向公式中输入时，不能为数字设置特殊格式，如输入$25,000,也只能在公式中输入25000如果引用的工作表或工作簿的名字包含非字母字符，必须用单引号将这个字符括起来函数中输入的参数必须符合要求

错误检查移去引用单元格追踪箭头移去从属单元格追踪箭头新批注圈释无效数据清除无效数据标识圈错误检查移去引用单元格追踪箭头移去从属单元格追踪箭头新批注圈释无效数据清除无效数据标识圈审核方法

1）选中要审核的单元格（区域）

2）单击“公式审核”工具栏上的相应按钮或执行“工具→公式审核→追踪引用单元格”等相应命令。圈无效数据，必须是设置过有效性的数据区域。1、追踪引用单元格引用的单元格：指一个单元格中的数据是使用了其他单元格中的数据通过公式计算出来的。执行“工具—公式审核—追踪引用单元格”2、追踪从属单元格