第一章EXCEL高级应用 1.2 公式与函数的高级应用

1.2 公式与函数的高级应用 公式的组成 常量 单元格的引用 以等号开始，由常量、单元格引用、函数和运算符组成。例如，假设A1单元格的内容为圆半径，则计算圆的周长的公式为：= 2 * PI() * A1 【例1-2-1】 常量 数值型、文本型、日期型和逻辑型的文本字符串。例如：123、"ABC"、True、False等 单元格的引用 “A1”引用样式、“R1C1”引用样式、单元格名称引用单元格 相对引用、绝对引用和混合引用 切换相对引用、绝对引用和混合引用的快捷键为<F4>

“A1”引用样式 Excel使用“A1”引用样式，即列标和行号的组合表示法。列标使用字母标识，从A到XFD，共16,384列；行号使用数字标识，从1到1,048,576。

“R1C1”引用样式 “R1C1”引用样式，即R行号和C列标的组合表示法。在“R1C1”样式中，行号在R（Row）后，从1到65,536；列标在C（Column）后，从1到256。使用“R1C1”引用样式，可以快速准确定位单元格，特别适用于Excel宏内的行和列的编程引用 单元格的名称引用 三维引用样式 引用同一工作簿中多张工作表上的同一单元格或单元格区域中的数据 三维引用的格式为：“工作表名称的范围！单元格或区域引用

运算符 算术、比较、文本连接和引用 算术运算符 比较运算符 文本连接运算符（&） 引用运算符 +、-、*、/、%、^ 比较运算符 =、>、<、>=、<=、<> 文本连接运算符（&） ="张三"&" 先生/女士"，结果：张三 先生/女士 引用运算符 : 区域运算符（例如B5:B15） ,（联合运算符，将多个引用合并为一个引用。例如：SUM(B5:B15,D5:D15) （空格）：交集运算符，生成一个对两个引用中共有单元格的引用。例如：SUM(B7:D7 C6:C8) Excel运算符的优先级为：引用运算符（:、,、（空格））>负数（-）>百分比（%）>乘和除（*、/）>加和减（+、-）>文本连接运算符（&）>比较运算符（=、>、<、<=、>=、<>）

数组公式 注意： （1）不要自己键入花括号，否则，Excel认为输入的是一个正文标签。 （2）不能单独更改数组公式中某个单元格的内容，否则系统报错，也就是说，不能更改数组的某一部分，必须选择整个单元格区域，然后更改数组公式。 （3）要删除数组公式，请选择整个公式，按<Delete>键。

【例1-2-2】“A1”引用样式、绝对引用、相对引用和数组公式应用示例 【例1-2-3】：“R1C1”引用样式、单元格的名称引用、三维引用和数组公式应用示例 【例1-2-3解答】 （1）切换到R1C1引用样式。执行“文件”选项卡中的“选项”命令，打开“Excel选项”对话框，在“公式”类别中的“使用公式”设置处，选中“R1C1引用样式”复选框 （2）利用数组公式计算Sheet1中的商品销售总金额。选择单元格R13C2，然后在编辑栏中输入以下公式：=SUM(R2C1:R11C1*R2C2:R11C2)，并使编辑栏仍处在编辑状态。按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}” （3）计算特价品平均价格。在Sheet1的单元格R14C2中输入以下公式：=AVERAGE(特价items) （4）计算新增折扣店总数量。在Sheet1的单元格R15C2中输入以下公式：=SUM(Sheet2:Sheet4!R13C2) （5）文件另存为：fl1-2-3商品信息-结果.xlsx。

公式审核和公式求解 公式审核： 公式-公式审核-追踪引用单元格 公式-公式审核-追踪从属单元格 公式-公式审核-显示公式 在单元格中使用公式和函数不当时，会产生错误信息。使用公式审核可以查找选定单元格公式的引用错误；使用公式求解，可以单步调试公式的运行过程和结果。 1. 使用公式和函数产生的常见错误信息 公式审核： 公式-公式审核-追踪引用单元格 公式-公式审核-追踪从属单元格 公式-公式审核-显示公式 公式-公式审核-错误检查 【例1-2-4】公式审核应用示例 【例1-2-5】公式求值应用示例

公式审核和公式求解

（1）数学和三角函数

数学和三角函数

数学和三角函数

数学和三角函数

数学和三角函数

随机函数

⑤利用SUBTOTAL函数统计学生最长身高。在H2单元格中输入公式：=SUBTOTAL(4,B2:B201) p295 【例1-2-6】数学函数（RAND、RANDBETWEEN、ROUND、SQRT、SUBTOTAL、SUMIF、SUMIFS等）应用示例 【例1-2-6解答】 ①生成学生身高信息（保留1位小数）。在B2单元格中输入公式：=ROUND(RAND()*(240-150)+150,1)，回车确认后，拖曳该单元格的填充柄到B201 ②生成学生成绩信息。在C2单元格中输入公式：=RANDBETWEEN(0,100)，回车确认后，拖曳该单元格的填充柄到C201③生成学生月消费信息（保留1位小数）。在E2单元格中输入公式：= ROUND(RAND()*1000,1)，回车确认后，拖曳该单元格的填充柄到E201④调整学生的成绩（保留到整数部分）。在D2单元格中输入公式：=ROUND(SQRT(C2)*10,0)，回车确认后，拖曳该单元格的填充柄到D201 ⑤利用SUBTOTAL函数统计学生最长身高。在H2单元格中输入公式：=SUBTOTAL(4,B2:B201) p295 ⑥统计低月消费信息。在H3单元格中输入公式：=SUMIF(E2:E201,"<50")。 ⑦统计高水平运动员的月消费信息。在H4单元格中输入公式：=SUMIF(B2:B201,">=200",E2:E201)。 ⑧统计考试及格的高水平运动员的月消费信息。在H5单元格中输入公式：=SUMIFS(E2:E201,B2:B201,">=200",D2:D201,">=60")

数学和三角函数（7） 【例1-2-7】数学函数（SUM、SUMPRODUCT、ROUND）、逻辑函数（IF）、统计函数（COUNTIF、COUNTIFS）以及数组公式的应用示例

【例1-2-7解答】 ①利用数组公式计算总分。选择数据区域H3:H17，然后在编辑栏中输入以下公式：=E3:E17+F3:F17+G3:G17，并使编辑栏仍处在编辑状态。按<Ctrl>+<Shift>+<Enter>组合键 ②利用数组公式计算平均分。选择数据区域I3:I17，然后在编辑栏中输入以下公式：=ROUND(H3:H17/3,0)，并使编辑栏仍处在编辑状态。按<Ctrl>+<Shift>+<Enter>组合键 ③利用COUNTIF（方法1）统计各班学生总数。在B20单元格中输入公式：=COUNTIF($D$3:$D$17,"一班")，统计一班学生总数。将B20的公式复制到B24单元格中，将公式中的班级信息改为“二班”。 ④利用COUNTIFS（方法1）统计各班男女生人数。在B21单元格中输入公式：=COUNTIFS($C$3:$C$17,"男",$D$3:$D$17,"一班")，统计一班男生人数。复制公式到B22、B25、B26，并相应修改所需计算的班级和性别信息。

⑤利用数组公式、SUM和IF配合（方法2）统计各班学生总数。选择B29单元格，然后在编辑栏中输入以下公式：=SUM(IF(D3:D17=“一班”,1,0))，按<Ctrl>+<Shift>+<Enter>。在B33单元格，利用数组公式“{=SUM(IF(D3:D17="二班",1,0))}”，统计二班学生总数。 ⑥利用数组公式、SUM和IF配合（方法2）统计各班男女生人数。选择B30单元格，然后在编辑栏中输入以下公式：=SUM(IF(C3:C17="男",IF(D3:D17="一班",1,0)))，按<Ctrl>+<Shift>+<Enter>，统计一班男生人数。如法炮制，在单元格B31、B34、B35，分别利用相应的数组公式统计各班男女生人数。 ⑦利用数组公式、SUM和*配合（方法3）统计各班男女生人数。选择F21单元格，然后在编辑栏中输入以下公式：=SUM((D3:D17="一班")*(C3:C17="男"))，按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式，统计一班男生人数。如法炮制，在单元格F22、F25、F26，分别利用相应的数组公式统计各班男女生人数。 ⑧利用SUMPRODUCT（方法4）统计各班男女生人数。在F30单元格中输入公式：=SUMPRODUCT((C3:C17="男")*(D3:D17="一班"))，统计一班男生人数。如法炮制，分别在单元格F31、F34、F35，利用SUMPRODUCT统计各班男女生人数。

【例1-2-8】三角函数应用示例（绘制函数图像）

逻辑函数(1)

逻辑函数(1) 【例1-2-9】逻辑函数（IF、IFERROR）以及数学函数（ROUND、SQRT）应用示例 初步调整学生的千分考成绩：成绩<=0显示“负分”，否则成绩调整为“开根号*3并取整”

【例1-2-9解答】 ①输入语文成绩等级评定公式。在C2单元格输入公式：=IF(B2>89,"优",IF(B2>79,"良", IF(B2>69,"中",IF(B2>59,"及格","不及格"))))，并填充至C31单元格。结果如图1-2-12所示。 ②输入千分考成绩调整公式。在G3单元格输入公式：=IFERROR(ROUND(SQRT(F3)*3,0),"负分")，并填充至G12单元格。 说明： （1）函数IFERROR(value, value_if_error)捕获和处理表达式中的错误。如果公式的计算结果正确，则返回公式的结果value；否则返回指定的值value_if_error。 （2）计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。具体参见表1-2-3使用公式和函数产生的常见错误信息。 思考 有没有更简洁的方法解决这个IF嵌套函数的问题？提示：请参见查找与引用函数VLOOKUP或LOOKUP或CHOOSE、统计函数FREQUENCY等。

逻辑函数（2） 【例1-2-10】逻辑函数（IF、AND和OR）以及数学和三角函数（ROUND、RAND、SIN、SQRT、EXP、LN、ABS、PI等） ①生成-10到10之间的随机实数（保留两位小数）。在A2单元格输入公式：=ROUND(RAND()*20-10,2)，并填充至A42单元格。 ②利用IF和AND函数计算分段函数y的值。在B2单元格输入公式：=IF(AND(A2>=-1,A2<2),SIN(A2)+2*SQRT(A2+EXP(4))-(A2+1)^3,LN(ABS(A2^2-A2))-2*PI()*(A2-1)/7/A2)，并填充至B42单元格。利用增加小数位数或减少小数位数使结果保留2位小数。 ③利用IF和OR函数计算分段函数y的值。在C2单元格输入公式：=IF(OR(A2<-1,A2>=2),LN(ABS(A2^2-A2))-2*PI()*(A2-1)/7/A2,SIN(A2)+2*SQRT(A2+EXP(4))-(A2+1)^3)，并填充至C42单元格。利用增加小数位数或减少小数位数使结果保留2位小数。

文本函数（1）

文本函数（2）

文本函数（3）

文本函数（4）

文本函数（5） （1）从Excel中提取字符串的常用函数有LEFT、RIGHT、MID、SUBSTITUTE、REPLACE等。 （2）函数LEN、LEFT、RIGHT、MID、FIND、SEARCH、REPLACE面向使用单字节字符集（Single-Byte Character Set，SBCS）的语言 无论默认语言如何设置，这些函数始终将每个字符（不管是单字节还是双字节）按1计数 例如，LEN("丰田car")返回5。

文本函数（5） （3）函数LENB、LEFTB、RIGHTB、MIDB、FINDB、SEARCHB、REPLACEB面向使用双字节字符集（Double-Byte Character Set，DBCS）的语言 当启用支持DBCS语言（日语、简体中文、繁体中文以及朝鲜语）的编辑并将其设置为默认语言时，这些函数会将每个双字节字符按2计数 例如，LENB("丰田car")返回7 （4）函数SUBSTITUTE用于在某一文本字符串中替换指定的文本；函数REPLACE则用于在某一文本字符串中替换指定位置处的指定字节数的文本。 本例用REPLACE函数将身份证号码的前3位510变更为320 如果使用公式“=SUBSTITUTE(D2,"510","320")”，则将身份证号码所有的510替换为320

【例1-2-11解答】 ①供应商姓名抽取公式。在B2单元格输入公式：=LEFT(A2,LENB(A2)-LEN(A2))，并填充至B15单元格。 ②供应商手机号码抽取公式。在C2单元格输入公式：=RIGHT(A2,2*LEN(A2)-LENB(A2))，并填充至C15单元格。 ③登录账号抽取公式。在F2单元格输入公式：=UPPER(LEFT(E2,FIND("@",E2)-1))，并填充至F15单元格。 ④登录密码抽取公式。在G2单元格输入公式：=RIGHT(D2,6)，并填充至G15单元格。 ⑤身份证号码变更公式。在H2单元格输入公式：=REPLACE(D2,1,3,"320")，并填充至H15单元格。 ⑥文件另存为：fl1-2-11供应商信息-结果.xlsx。

日期与时间函数（1）

日期与时间函数（2）

日期与时间函数（3） 【例1-2-12】日期与时间函数 “fl1-2-12职工加班出差信息.xlsx”中记录着4名员工的加班和出差情况，加班工资按照小时计算（加班时间不足一小时但超过半小时的按一小时计算，不足半小时的则忽略不计）。 （1）统计每位员工的加班时长。 （2）判断加班时间是星期几，并确定是否为双休日。 （3）统计每位员工总的加班时长、双休日加班时长，并根据表格中的支付标准计算加班工资。 （4）统计每位员工的出差天数。 （5）请利用数组公式计算该单位所支出的出差补助总费用。

日期与时间函数（4）

【例1-2-12解答】 ①统计每位员工的加班时长。在D3单元格输入公式：=ROUND(((C3-B3)*24),0)，并填充至D12单元格。 ②判断加班时间是星期几。在E3单元格输入公式：=WEEKDAY(B3,2)，并填充至D12单元格。 ③判断加班时间是否为双休日。在F3单元格输入公式：=IF(OR(E3=6,E3=7),"是","")，并填充至F12单元格。 ④统计每位员工加班总时长。选择数据区域I3:I6，然后在编辑栏中输入以下公式：=SUMIF(A3:A12,H3:H6,D3:D12)，按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。

【例1-2-12解答】 ⑤统计每位员工双休日总加班时长。选择数据区域J3:J6，然后在编辑栏中输入公式：=SUMIFS(D3:D12,A3:A12,H3:H6,F3:F12,"是")，按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式。 ⑥统计每位员工加班工资。选择数据区域K3:K6，然后在编辑栏中输入以下公式：=J3:J6*I10+(I3:I6-J3:J6)*I9，按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式。 ⑦统计每位员工出差总天数。在D16单元格输入公式：=DATEDIF(B16,C16,"D")，并填充至D19单元格。 ⑧利用数组公式计算出差补助总支出费用。选择单元格E20，然后在编辑栏中输入以下公式：=SUM(D16:D19*E16:E19)，按<Ctrl>+<Shift>+<Enter>组合键锁定数组公式。

统计函数（1）

统计函数（2）

统计函数（3）

统计函数（4）

统计函数（5）

财务函数（1）

财务函数（2）

财务函数（3）

财务函数（4）

查找与引用函数（1）

查找与引用函数（2）

查找与引用函数（3）

查找与引用函数（4）

查找与引用函数（5）

数组常量 在数组公式中也可输入数组引用，或包含在单元格中的数值数组，其中该数值数组和数组引用被称为数组常量

查找与引用函数（5）

查找与引用函数（6）

查找与引用函数（7）

信息函数(1)

信息函数(2)

习题与实验 选择题： 填充题： 思考题: 实验： 实验一 公式与函数的高级应用