第7章Excel公式与函数

Excel公式与函数Excel公式名称的定义与引用Excel函数1主要内容21、公式的输入与编辑公式的输入Excel中输入公式时，首先单击存放计算结果的单元格，然后先输入等号“=”，向系统表示正在输入公式，接着输入表示公式的表达式，最后按Enter键完成输入。例如：在A3单元格输入A1和A2单元格数据求和的计算结果，则在A3单元格中输入公式“=A1+A2”。注意：在公式中输入的运算符都必须是西文的半角字符。公式的编辑（1）公式的修改。（2）公式的删除。Excel公式32、公式的复制与填充公式的复制首先单击公式所在单元格，然后单击鼠标右键，在快捷菜单中选择“复制”命令，接着右击目标单元格，在快捷菜单中选择“粘贴”命令，完成公式的复制。公式的填充首先单击公式所在单元格，然后通过拖动单元格的填充柄进行公式的填充。注意：公式的复制与填充，复制与填充的都不是单元格数据本身，而是单元格的公式。默认情况下，公式复制或填充时对单元格的引用采用的是相对引用。Excel公式43、单元格引用单元格引用是指在公式中引用指定工作表中的单元格或单元格区域。相对引用相对引用与包含公式的单元格位置相关，引用的单元格地址是相对于公式所在单元格的相对位置，不是固定不变的地址。默认情况下，公式中单元格都是使用相对引用。单元格相对引用时，以“列标行号”表示，例如：A3、B1:B2等。公式在填充复制时，公式中相对引用的单元格地址会自动调整，例如：在A3单元格中输入公式“=A1+A2”，并使用填充柄向右填充复制公式，B3单元格中显示的公式就变为了“=B1+B2”。Excel公式5绝对引用绝对引用与包含公式的单元格位置无关，引用的单元格地址是固定不变的地址。公式在填充复制时，如果希望引用的单元格地址保持不变，那么这时就需要使用绝对引用。单元格绝对引用时，单元格的列标与行号前都必须加上“$”符号，以“$列标$行号”表示，例如：$A$3、$B$1:$B$2等。公式在填充复制时，公式中绝对引用的单元格地址保持不变，例如：在A3单元格中输入公式“=$A$1+$A$2”，并使用填充柄向右填充复制公式，B3单元格中显示的公式仍然是“=$A$1+$A$2”。混合引用混合引用是指在单元格引用时希望单元格的行保持不变而列相对变化或者单元格的列保持不变而行相对变化。当单元格的行不变而列变化时，以“列标$行号”表示，例如：A$3；当单元格的列不变而行变化时，以“$列标行号”表示，例如：$A3。Excel公式6跨工作表引用跨工作表引用是指引用其他工作表的单元格数据，以“工作表名！单元格地址”表示，例如：Sheet3！A3表示引用Sheet3工作表的A3单元格数据。跨工作簿引用跨工作簿引用是指引用其他工作簿中工作表的单元格数据，以“[工作簿名]工作表名！单元格地址”表示，例如：[工作簿1]Sheet3！A3表示引用工作簿1中Sheet3工作表的A3单元格数据。Excel公式7Excel中名称是一种特殊的简略表示法，名称可以表示单元格、单元格区域、函数、常量等。Excel中如果需要反复引用某个单元格区域，每次引用都重新输入单元格区域，操作过程烦琐且容易出错，这时候可以通过创建名称的方式来提高引用的效率。1、名称的定义名称的语法规则（1）唯一性：名称在其适用范围内必须始终唯一。（2）有效字符：名称必须以字母、下划线“-”、或反斜杠“\”

作为第一个字符，其余字符可以是数字、字母、句点“.”和下划线。注意：大小写字母“C”、“c”、“R”、“r”不能使用。（3）空格无效：名称中不能使用空格。（4）单元格地址不能作为名称。例如：名称不能是A3、B$2、$C3等。（5）名称长度：一个名称最多不能超过255个西文字符。（6）不区分大小写：名称中可以出现大、小写字母，但同一个字母的大小写会被认为是同一个字符。例如：定义了名称Class，如果在同一工作簿中再定义名称class、CLASS，则均会被认定为与Class重名而不被允许。名称的定义与引用8名称的定义名称必须先定义后使用。方法一：通过“名称框”定义名称。首先选择需要命名的单元格或单元格区域，然后单击编辑栏左侧的“名称框”并输入一个名称，最后按Enter键创建名称。方法二：通过“新建名称”对话框定义名称。首先选择需要命名的单元格区域，然后单击“公式”选项卡—>“定义的名称”组—>“定义名称”按钮，弹出“新建名称”对话框，如图所示，根据需要进行相应设置即可。名称的定义与引用92、名称的引用通过“名称框”引用首先单击“名称框”右侧的箭头，打开下拉列表，列表中将显示所有已经被命名过的单元格或单元格区域名称，然后单击选择名称，名称对应的单元格或单元格区域即被选中。名称的定义与引用通过公式和函数引用首选单击公式所在的单元格，然后单击“公式”选项卡—>“定义的名称”组—>“用于公式”按钮，接着在下拉列表中选择需要引用的名称，引用的名称在当前单元格的公式中显示，最后按Enter键。103、名称的编辑和删除名称的编辑首先单击“公式”选项卡—>“定义的名称”组—>“名称管理器”按钮，弹出“名称管理器”对话框，如图所示，然后选择需要修改的名称，单击“编辑”按钮，弹出“编辑名称”对话框，如图所示，对名称、备注、引用位置等项进行修改即可。名称的删除先打开“名称管理器”对话框，然后选择需要删除的名称，单击“删除”按钮即可。名称的定义与引用11Excel函数是为了满足更复杂的计算需求而预置的算法，主要用于处理简单四则运算无法解决的计算问题。Excel中的函数是Excel预先编辑好的公式，用户可以不需定义直接使用。1、函数的分类Excel中预置了大量不同类型的函数，为用户在日常生活和工作中进行数据计算提供了强有力的支持。根据功能的不同，Excel2016的函数主要分为财务函数、数学与三角函数、日期与时间函数、统计函数、查找与引用函数、数据库函数、文本函数等。Excel函数122、函数的输入与编辑函数的输入（1）通过单元格直接输入与输入公式类似，输入函数可以在单元格中直接输入“=函数名（参数列表）”，在输入的过程中Excel会提示输入的参数，如右上图所示。（2）通过“插入函数”按钮插入。首先选择需要输入函数的单元格，然后单击“公式”选项卡—>“函数库”组—>“插入函数”按钮或者单击编辑栏左侧的“插入函数”按钮

，弹出“插入函数”对话框，如右下图所示，接着选择需要的函数，单击“确定”按钮，最后在弹出相应函数的“函数参数”对话框，设置函数参数。Excel函数132、函数的输入与编辑函数的输入（3）通过“函数库”组插入首选选择需要输入函数的单元格，然后单击“公式”选项卡—>“函数库”组—>相应函数类别按钮下方的箭头，弹出该类型的下拉列表，如图所示，接着在打开的函数列表中选择所需的函数，弹出相应函数的“函数参数”对话框，如图所示（以LEFT函数为例），最后在“函数参数”对话框中设置函数参数。Excel函数142、函数的输入与编辑函数的编辑方法一：首先双击函数所在单元格，进入编辑状态，然后通过单元格对函数及其参数进行修改，最后按Enter键。方法二：首先选择函数所在单元格，然后单击编辑栏，在编辑栏中对函数及其参数进行修改，最后单击编辑栏左侧的按钮

。Excel函数153、常用函数条件求和函数SUMIF格式：SUMIF(range,criteria,[sum_range])功能：返回指定单元格区域中满足给定条件的所有数值的和。例：在如图所示工作表中的F2单元格计算交通费的总额。Excel函数16多条件求和函数SUMIFS格式：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：返回指定区域内满足一组给定条件的所有单元格的和。例：在如图所示工作表中的F5单元格计算田华的交通费总额。Excel函数17条件判断函数IF格式：IF(logical_test,[value_if_true],[value_if_false])功能：判断指定条件是否为TRUE，如果为TRUE，函数则将返回一个值；如果为FALSE，函数将返回另一个值。例：如图所示的工作表中，如果语文成绩大等于90，则“等级”显示“优秀”，如果语文成绩大等于80且小于90，显示“良好”，如果语文成绩小于80，显示“及格”。Excel函数18垂直查询函数VLOOKUP格式：VLOOKUP(