EXCEL高级应用版课件

Excel高级应用Excel高级应用学习目的熟悉Excel软件的更多功能；掌握Excel数据处理技巧；提高办公效率。4学习目的熟悉Excel软件的更多功能；4目录0、Excel2007简介1、基本技巧2、单元格引用3、名称框4、数组5、数据处理6、函数7、D函数8、表与动态报表9、数据透视表10、宏与VBA0-4基础5-6重点高级目录0、Excel2007简介0-4基础5-6重点高级0Excel2007简介0.1新功能界面友好使用功能区取代了传统菜单。数据处理能力更大、更安全、更快速

工作表最大：100万行X16384列；带宏的文件使用单独格式，xlsx/xlsm）强大的表功能。“表”概念，可实现结构化引用，构造动态报表。0Excel2007简介0.1新功能1Excel2007简介0.2新界面Office按钮唯一的菜单，相当于传统“文件”菜单。功能区

快速访问工具栏（始终显示，可定制）

选项卡（有开始、插入、公式、数据等；控件分组；功能区可动态按需显示；功能区可以最小化。插入工作表按钮查看方式显示比例。1Excel2007简介0.2新界面1基本技巧1.1快速输入相同的数据——Ctrl+回车，F5

如何快速输入性别一列数据？方法：

1）选中“男”性单元格（配合Ctrl选定单格）—输入“男”—按Ctrl+回车；

2）选中性别区域，然后按F5键，定位条件输入“空值”，输入“女”—Ctrl+回车。

普通的方法：连续的字符数据，拖动填充柄；连续的数值数据，按Ctrl键拖动填充柄；当然，复制—粘贴也是可以的。1基本技巧1.1快速输入相同的数据——Ctrl+回1基本技巧1.2重复最近的1次操作——F4键如何在某行上面插入1行或多行？方法：

1）定位光标，开始/插入，然后光标分别定位，选中若干行，按F4键；

2）类似的删除操作，格式操作（如设置字体颜色等）均可以使用F4重复；

思考：什么“操作”，可以使用F4键重复？

1基本技巧1.2重复最近的1次操作——F4键1基本技巧1.3为什么数据无法复制——选择性粘贴如何将某些计算生成的列数据复制到其他位置？方法：选中数据列--复制，定位光标，开始/粘贴--选择性粘贴，选择“数值”。

或者：Ctrl+c,Ctrl+v后，单击右下角的“粘贴选项”

类似的操作：

计算式的粘贴（加减乘除等）；转置操作粘贴链接

等。

1基本技巧1.3为什么数据无法复制——选择性粘贴1基本技巧1.4显示人民币格式——自定义单元格格式如何将数据以中文大写格式显示？

方法：

选中某数据列（或区域），开始/数字—设置单元格格式—特殊—中文大写数字；设置单元格格式—自定义—在“类型”后输入“元整”，实现数据的人民币格式显示。

例如：工资工资大写

3538叁仟伍佰叁拾捌元整

1000壹仟元整1基本技巧1.4显示人民币格式——自定义单元格格1基本技巧1.5为什么造成数据不平？——数据显示有问题下面成绩表中，有几人不格？1人还是2人？原因：“分数显示”列的数据不精确。表面数据与内部数据有误差。对策：使用int、round等函数，让数据做四舍五入后再显示。类似问题：求所有人平均分数。姓名数学语文英语分数显示分数值张三8071617170.67李四9047426059.67王五3127634040.331基本技巧1.5为什么造成数据不平？——数据显示1基本技巧1.6利用下拉列表实现选择式输入——数据有效性如何实现性别（男女）或职称等的选择式输入。方法：选中某数据区域，数据/数据有效性—设置-允许-系列，来源：选择预先输入好序列数据的区域，或直接输入，如:男,女（注意用逗号分隔）思考：如何扩展这样的数据区？（下拖填充柄）如何取消这个下拉区域？（选区域，数据有效性—全部删除）1基本技巧1.6利用下拉列表实现选择式输入——数2单元格引用问题：每人的奖金额公式怎么写？方法：使用单元格绝对引用，用奖金基数乘以奖金系数；类似问题：求多个的圆面积，制作九九表等。操作：输入公式时，在单元格地址上（或选中）按F4键可在相对/绝对/混合引用方式间转换。相对引用：A1绝对引用：$A$1混合引用：A$1,$A1公式换位置后会怎样？2单元格引用问题：每人的奖金额公式怎么写？相对引用：A12单元格引用问题：如何跨工作表或跨工作薄写公式？1、跨表引用：工作表名称！单元格地址；

如：=sheet1!A1、=sheet1!$A$1、=sheet1!A$12、跨工作薄文件引用：

引用打开的工作薄：如：=[book1.xlsx]sheet1!A1引用未打开的工作薄：如：

=‘c:\dir\[book1.xlsx]sheet1’!A1输入技巧：在公式输入中直接切换工作表并单击单元格。2单元格引用问题：如何跨工作表或跨工作薄写公式？输3名称框使用名称框的好处：1、选择数据区域。（单击左上角—在名称框输入右下单元地址—按Shift+回车。2、把某个区域定义为名称，然后通过名称框选择区域；3、在公式中使用名称框，可简化区域选择，尤其是跨表引用时。如何在公式中使用名称？直接输入，如：=sum(sx)或光标在括号中时：公式/用于名称。如何使用名称框快速选择区域？如何在公式中使用名称？3名称框使用名称框的好处：如何使用名称框快速选择区域？3名称框删除名称方法：公式/名称管理器定义名称的方法方法1：用名称框。选定区域，在名称框输入名称的名字。方法2：公式—定义名称或名称管理器—新建。方法3：公式—根据所选内容创建。可以一次定义多个名称。3名称框删除名称方法：公式/名称管理器定义名称的方法3名称框在省份下输入可选择省，在地市下输入可选择省内地市。在这里制作二级下拉列表通过名称框产生二级联动下拉列表。3名称框在这里制作二级下拉列表通过名称框产生二级联动3名称框操作要点1、以省名为名定义名称。选定含标题的各省及下级地市，公式/根据所选内容创建名称。2、选定省份下区域，开始/数据—有效性—允许—系列—来源—选择各个省名。3、选定地市下区域，开始/数据—有效性—允许—系列—来源—=indirect(省份下面区域的相对引用)。3名称框操作要点4数组什么是数组公式？

数组公式可以对两组及以上的单元格区域进行计算。形式是{}；好处是简洁，节省内存。如何使用数组公式？选择目标区域；

键入=，在公式中选择区域，键入运算符号，再选择区域，按Ctrl+Shift+回车如何修改数组公式？单击数组公式的任一单元格，在编辑栏修改公式，按Ctrl+Shift+回车三个键。实例见项目4

4数组什么是数组公式？5数据处理：排序如何按职务对“职工情况表”排序？操作要点1、Office按钮—Excel选项—常用—自定义序列—从低到高输入各职务，一行一个；2、选定区域—开始/排序和筛选—自定义排序—次序—选自定义的职务系列。3、注意排序区域的选择方法，注意扩展区域的提示，防止数据混乱。思考：按姓氏笔画排序防止数据混乱的方法。5数据处理：排序如何按职务对“职工情况表”排序？5数据处理：筛选1、自动筛选（1）查看总评60-69的同学；（2）查看总评60-69的男同学。（3）查看总评成绩最高的前3名。操作要点选择区域；数据/筛选注意：

多个条件是“与”关系。取消自动筛选：数据/筛选5数据处理：筛选1、自动筛选5数据处理：高级筛选

2、高级筛选1）筛选“期末”或“总评”成绩在80（含）以上的男同学。2）期末70-90，或者男生。操作要点数据/高级

1、条件的写法（同一行为“与”，不同行为“或”）；

2、文本型数据不必写=号；

3、日期条件中用的是日期的数值格式；见项目6-4

4、条件区要与数据区分开，条件字段占一行。多字段“或”条件时使用5数据处理：高级筛选2、高级筛选多字段“或”条件时使用5数据处理：高级筛选3、高级筛选中使用计算条件

如何筛选出销售额高于销售额平均值的记录？操作要点数据/高级

1、条件区列标题可以是任何文本，但不能与现有列标题重名；

2、用绝对引用方式引用数据表外单元格；

3、用相对引用方式引用数据表外单元格。

见项目6-4：使用计算条件5数据处理：高级筛选3、高级筛选中使用计算条件5数据处理：条件格式

如何突出显示最大10%销售利润的记录？

如何用数据条、色阶、图标突出显示记录？

如何突出显示最好10%、最差20%及的记录？操作要点

1、选择区域；

2、开始/条件格式。

思考：如何去除条件格式？5数据处理：条件格式5数据处理：

分类汇总任务描述对学生成绩表操作1、按班级求期末之和、总评之和（1）按班级排序，数据—分类汇总（2）分类汇总后还可再排序（如按总评降序）。2、高级分类汇总按“班级”分类汇总总评总和、期末平均。操作要点高级分类汇总就是按同一类别进行不同字段或不同汇总方式的汇总。按“班级”排序，两次分类汇总，第2次不要勾选“替换当前分类汇总”。如要删除分类汇总，选“全部删除”。5数据处理：分类汇总任务描述对学生成绩表操作5数据处理：

分类汇总任务描述

3、嵌套分类汇总按“班级”分类汇总总评总和，以及各班不同“性别”的期末之和。

操作要点嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总。按“班级+性别”排序，按班级、性别两次分类汇总，第2次不要勾选“替换当前分类汇总”。分类汇总结果可复制到别处：选择区域—按F5键—定位—可见单元格—复制—粘贴。5数据处理：分类汇总任务描述6-1函数：

基本知识语法格式：

函数名（参数1，参数2，…)

括号是必须的。如:=pi()

参数可以是常数、公式、其他函数。示例：

以sum为例，多种方式求三个数之和。如何输入函数？

1）=直接输入函数名称和地址等参数；2）=输入名称和（，通过选择区域输入地址，手工输入其他符号和参数；

3）

=输入名称和（，使用fx函数向导输入地址等参数；

4）单击fx使用函数向导输入函数。

6-1函数：基本知识语法格式：6-2函数：分类汇总Subtotal通过帮助学习函数通过查询Subtotal

函数的帮助信息，了解其功能和用法，不使用“数据—分类汇总”，直接使用该函数按班级对学生成绩进行分类汇总。操作要点单击fx获得帮助。Subtotal6-2函数：分类汇总Subtotal通过帮助学习函数6-3函数：

逻辑函数IF

如何判断“总评”成绩是否及格或有效？操作要点1、=IF（G151>=60，“及格”,“不及格”)2、=IF(AND(G151>=60,G151<=100),“及格”,“不及格”)

AND是“与”函数。3、=IF(OR(G151>100,G151<0),“无效”,“有效”)注意：

IF函数的输入技巧。=IF（,单击fx,对话框内输入IF和AND等嵌套函数的输入技巧。6-3函数：逻辑函数IF6-3函数：

逻辑函数IF

如何根据“总评”成绩计算成绩5个等级。操作要点1、IF(G87>=90,"优",IF(G87>=80,"良",IF(G87>=70,"中",IF(G87>=60,"及格","不及格"))))

2、成绩分5等级的另一种方法：=choose(if(G87<60),1,int((G87-50)/10)+1,”不及格”,”及格”,”中”,”良”,”优”)

思考：按博硕本科、专科学历计算学历分，如分别为5、4、3、2分。F6-3函数：逻辑函数IF如何根据“总评”成绩计算成6-4函数：日期函数如何根据生日计算年龄（有两种方法）

1、使用Datedif,Today函数求年龄；

2、使用Year,Today求年龄；操作要点注意两个方法的差别

=Datedif(F98,Today(),“Y”)=Year(Today())-Year(F98)此方法算出的年龄“虚”F注意：此函数没有输入提示。思考：计算存款到期日6-4函数：日期函数如何根据生日计算年龄（有两种方法）6-5函数：求和如何用Sumif分别统计各班的总评成绩汇总值。操作要点

=Sumif($E$110:$E$116,I110,$G$110:$G$116)

求和区域条件区域条件值EFGI6-5函数：求和如何用Sumif分别统计各班的总评成绩汇总值6-5函数：求和如何用Sumifs分别统计各班各性别学生的总评成绩汇总值。操作示例:(可以有多个条件)

=SUMIFS($G$181:$G$187,$D$181:$D$187,D190,$E$181:$E$187,C191)

求和区域条件区域1条件值1条件值2条件区域26-5函数：求和如何用Sumifs分别统计各班各性别学生的总6-6函数计数函数如何用Countif分别统计各等级人数。操作要点：

5等级人数=Countif($G$110:$G$116,">=90")

4等级人数=Countif($G$110:$G$116,">=80")-Countif($G$110:$G$116,">=90")G1101111121131141151166-6函数计数函数如何用Countif分别统计各等级人6-6函数计数函数

主要计数函数的比较：Count(区域)数值单元格个数CountA(区域)非空单元格个数CountBlank(区域)空白单元格个数CountIF(区域，条件)CountIFS(区域1，条件1，区域2，条件2…)

去掉重复值后统计个数：A1A=sum(if(a1:a4<>””,1/countif(a1:a4),a1:a4)))2B3A4C6-6函数计数函数主要计数函数的比较：6-7排名函数使用Rank函数按总评排名。操作要点

Rank（要排位的数值，排位区域，选项）选项：非0则升序排位0或省略则降序排位=Rank(G133,$G$133:$G$139)数值区域G1336-7排名函数使用Rank函数按总评排名。数值区域G136-8乘积函数用乘积函数Sumproduct用作交叉统计

使用Sumproduct按班级、性别统计人数DEGH6-8乘积函数用乘积函数Sumproduct用作交叉统6-9垂直查找Vlookup函数如何根据某对应字段从另一表中查找数据并输入到本表？如：根据职称查询教师的课时费/节操作要点：

使用Vloopup函数。本函数的功能是：在表格或数组的首列查找指定的值，并返回表格或数组中其它列的值。6-9垂直查找Vlookup函数如何根据某对应字段从Vloopup函数应用一K列公式=VLOOKUP

($K2,$A$2:$B$6,2,0)

要找的值查找区域返回第2列精确比较ABC职称课时费/节教授60副教授55高工55讲师50助教45JKL姓名职称课时费/节张三高工

李四教授原始表结果表公式0或FALSE：精确匹配，若找不到返回#N/ATRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。Vloopup函数应用一要找的值查找区域返回第2列精确比较AVloopup函数应用二K列公式=VLOOKUP

(D2,$A$2:$B$6,2)

要找的值查找区域返回第2列近似比较近似查找阈值级别备注分数级别0不及格0<=X<6018不及格60及格60<=X<7082良好70中等70<=X<8076中等80良好80<=X<9089良好90优秀90<=X70中等44不及格90优秀66及格58不及格46不及格100优秀数据分组：查找最接近某数据，而不大于某数据的值。Vloopup函数应用二要找的值查找区域返回第2列近似比较近7D函数什么是D函数？

D函数即数据库函数，均以D打头，共12个，其条件类似高级筛选的格式。如使用数组参数，要以Ctrl+Shift+回车输入。D函数的格式：Dname(区域，要计算的列字段，条件区)D函数：

Daverage,Dcount,DcountA,Dsum,Dmax,Dmin等。Excel把每列都有列标题的数据表称为数据库。7D函数7D函数用D函数统计指定年龄段（20至50岁）的人数。条件区G

=Dcount($D$1:$G$9,“年龄”,J1:L3)

DJKL7D函数用D函数统计指定年龄段（20至50岁）的人数。8表与动态报表请看下面问题：汇总表中的公式如何引用明细表中的行地址？日期品名数量金额A220A330B150B2100序号品名数量金额A550B3150…….销售明细表销售汇总表这里的公式怎么写？8表与动态报表日期品名数量金额A220A330B1508表与动态报表什么是表？表也称表格，是Excel07的特殊对象。比03中的列表有更多功能。如何创建表？

选择区域，插入/表。可以修改表名称。表的主要内容是哪些？

表（整个区域）；表名称；标题行（第一行）；汇总行（最下方）；数据区域（标题行和汇总行之外的区域）；计算列（由表右侧的计算公式生成）表的作用：排序筛选；设置样式；使用计算列；自动扩展；可删重复；结构化引用（比单元格引用更灵活）见：“表与D函数”工作表8表与动态报表8表与动态报表什么是结构化引用？表中公式可以使用传统的单元格引用；还可以使用表中的行、列、数据区域、汇总行、标题行等表结构。这就是结构化引用。结构化引用有何优点？

无论数据区域怎样变化，结构化引用的区域能自动调整，不需重写公式。例如：计算饮料库存的几种方法请见：

“表与D函数”工作表8表与动态报表9透视表与透视图什么是透视表？

它是一种可将数据表各字段进行快速分类汇总的分析工具，它是一种交互式报表。它有机地综合了排序、筛选、分类汇总等数据处理分析功能。主要概念：

数据源；字段；字段标题；刷新（重新计算）。创建方法：选择数据区域；（可以是几整列，增加列时可刷新）

插入/数据透视表，选位置（新表或当前表）。

拖动字段到页、行、列、数值标签区域。见透视表1，透视表2等工作表。9透视表与透视图什么是透视表？9透视表与透视图说明1、行标签。该字段的一个数据项占一行，相当于X轴。2、列标签。该字段的一个数据项占一列，相当于Y轴。3、页标签（报表筛选）。由该字段确定一个二维表，相当于Z轴。按该字段对透视表分页。4、可通过透视表工具栏，选择对字段数据的计算方式，如计数、求和、求平均等。

透视表可对数据进行交叉汇总、动态分析，还可根据透视表作出透视图

。

9透视表与透视图说明10宏与VBA

何为宏？

是由Excel命令组成的程序，可被多次使用。宏可以录制生成，也可在VBA环境中编写程序生成。创建宏

Office按钮—Excel选项—常用—在功能区显示开发工具。

开发工具—录制宏—起名—定义快捷键—按步骤操作—单击停止按钮。编辑宏：开发工具—宏—选定宏—编辑。运行宏：开发工具—宏—运行，或按快捷键。。修改宏。按AlT+F8，或按ALT+F11进行VBA环境。10宏与VBA何为宏？10宏与VBA录制并运行宏的例子：

新建隐藏工作表/显示工作表的2个宏，使用按钮启用宏。VBA编制宏的例子：

自定义函数；

通过按钮执行代码。10宏与VBA录制并运行宏的例子：谢谢大家！谢谢大家！Excel高级应用Excel高级应用学习目的熟悉Excel软件的更多功能；掌握Excel数据处理技巧；提高办公效率。4学习目的熟悉Excel软件的更多功能；4目录0、Excel2007简介1、基本技巧2、单元格引用3、名称框4、数组5、数据处理6、函数7、D函数8、表与动态报表9、数据透视表10、宏与VBA0-4基础5-6重点高级目录0、Excel2007简介0-4基础5-6重点高级0Excel2007简介0.1新功能界面友好使用功能区取代了传统菜单。数据处理能力更大、更安全、更快速

工作表最大：100万行X16384列；带宏的文件使用单独格式，xlsx/xlsm）强大的表功能。“表”概念，可实现结构化引用，构造动态报表。0Excel2007简介0.1新功能1Excel2007简介0.2新界面Office按钮唯一的菜单，相当于传统“文件”菜单。功能区

快速访问工具栏（始终显示，可定制）

选项卡（有开始、插入、公式、数据等；控件分组；功能区可动态按需显示；功能区可以最小化。插入工作表按钮查看方式显示比例。1Excel2007简介0.2新界面1基本技巧1.1快速输入相同的数据——Ctrl+回车，F5

如何快速输入性别一列数据？方法：

1）选中“男”性单元格（配合Ctrl选定单格）—输入“男”—按Ctrl+回车；

2）选中性别区域，然后按F5键，定位条件输入“空值”，输入“女”—Ctrl+回车。

普通的方法：连续的字符数据，拖动填充柄；连续的数值数据，按Ctrl键拖动填充柄；当然，复制—粘贴也是可以的。1基本技巧1.1快速输入相同的数据——Ctrl+回1基本技巧1.2重复最近的1次操作——F4键如何在某行上面插入1行或多行？方法：

1）定位光标，开始/插入，然后光标分别定位，选中若干行，按F4键；

2）类似的删除操作，格式操作（如设置字体颜色等）均可以使用F4重复；

思考：什么“操作”，可以使用F4键重复？

1基本技巧1.2重复最近的1次操作——F4键1基本技巧1.3为什么数据无法复制——选择性粘贴如何将某些计算生成的列数据复制到其他位置？方法：选中数据列--复制，定位光标，开始/粘贴--选择性粘贴，选择“数值”。

或者：Ctrl+c,Ctrl+v后，单击右下角的“粘贴选项”

类似的操作：

计算式的粘贴（加减乘除等）；转置操作粘贴链接

等。

1基本技巧1.3为什么数据无法复制——选择性粘贴1基本技巧1.4显示人民币格式——自定义单元格格式如何将数据以中文大写格式显示？

方法：

选中某数据列（或区域），开始/数字—设置单元格格式—特殊—中文大写数字；设置单元格格式—自定义—在“类型”后输入“元整”，实现数据的人民币格式显示。

例如：工资工资大写

3538叁仟伍佰叁拾捌元整

1000壹仟元整1基本技巧1.4显示人民币格式——自定义单元格格1基本技巧1.5为什么造成数据不平？——数据显示有问题下面成绩表中，有几人不格？1人还是2人？原因：“分数显示”列的数据不精确。表面数据与内部数据有误差。对策：使用int、round等函数，让数据做四舍五入后再显示。类似问题：求所有人平均分数。姓名数学语文英语分数显示分数值张三8071617170.67李四9047426059.67王五3127634040.331基本技巧1.5为什么造成数据不平？——数据显示1基本技巧1.6利用下拉列表实现选择式输入——数据有效性如何实现性别（男女）或职称等的选择式输入。方法：选中某数据区域，数据/数据有效性—设置-允许-系列，来源：选择预先输入好序列数据的区域，或直接输入，如:男,女（注意用逗号分隔）思考：如何扩展这样的数据区？（下拖填充柄）如何取消这个下拉区域？（选区域，数据有效性—全部删除）1基本技巧1.6利用下拉列表实现选择式输入——数2单元格引用问题：每人的奖金额公式怎么写？方法：使用单元格绝对引用，用奖金基数乘以奖金系数；类似问题：求多个的圆面积，制作九九表等。操作：输入公式时，在单元格地址上（或选中）按F4键可在相对/绝对/混合引用方式间转换。相对引用：A1绝对引用：$A$1混合引用：A$1,$A1公式换位置后会怎样？2单元格引用问题：每人的奖金额公式怎么写？相对引用：A12单元格引用问题：如何跨工作表或跨工作薄写公式？1、跨表引用：工作表名称！单元格地址；

如：=sheet1!A1、=sheet1!$A$1、=sheet1!A$12、跨工作薄文件引用：

引用打开的工作薄：如：=[book1.xlsx]sheet1!A1引用未打开的工作薄：如：

=‘c:\dir\[book1.xlsx]sheet1’!A1输入技巧：在公式输入中直接切换工作表并单击单元格。2单元格引用问题：如何跨工作表或跨工作薄写公式？输3名称框使用名称框的好处：1、选择数据区域。（单击左上角—在名称框输入右下单元地址—按Shift+回车。2、把某个区域定义为名称，然后通过名称框选择区域；3、在公式中使用名称框，可简化区域选择，尤其是跨表引用时。如何在公式中使用名称？直接输入，如：=sum(sx)或光标在括号中时：公式/用于名称。如何使用名称框快速选择区域？如何在公式中使用名称？3名称框使用名称框的好处：如何使用名称框快速选择区域？3名称框删除名称方法：公式/名称管理器定义名称的方法方法1：用名称框。选定区域，在名称框输入名称的名字。方法2：公式—定义名称或名称管理器—新建。方法3：公式—根据所选内容创建。可以一次定义多个名称。3名称框删除名称方法：公式/名称管理器定义名称的方法3名称框在省份下输入可选择省，在地市下输入可选择省内地市。在这里制作二级下拉列表通过名称框产生二级联动下拉列表。3名称框在这里制作二级下拉列表通过名称框产生二级联动3名称框操作要点1、以省名为名定义名称。选定含标题的各省及下级地市，公式/根据所选内容创建名称。2、选定省份下区域，开始/数据—有效性—允许—系列—来源—选择各个省名。3、选定地市下区域，开始/数据—有效性—允许—系列—来源—=indirect(省份下面区域的相对引用)。3名称框操作要点4数组什么是数组公式？

数组公式可以对两组及以上的单元格区域进行计算。形式是{}；好处是简洁，节省内存。如何使用数组公式？选择目标区域；

键入=，在公式中选择区域，键入运算符号，再选择区域，按Ctrl+Shift+回车如何修改数组公式？单击数组公式的任一单元格，在编辑栏修改公式，按Ctrl+Shift+回车三个键。实例见项目4

4数组什么是数组公式？5数据处理：排序如何按职务对“职工情况表”排序？操作要点1、Office按钮—Excel选项—常用—自定义序列—从低到高输入各职务，一行一个；2、选定区域—开始/排序和筛选—自定义排序—次序—选自定义的职务系列。3、注意排序区域的选择方法，注意扩展区域的提示，防止数据混乱。思考：按姓氏笔画排序防止数据混乱的方法。5数据处理：排序如何按职务对“职工情况表”排序？5数据处理：筛选1、自动筛选（1）查看总评60-69的同学；（2）查看总评60-69的男同学。（3）查看总评成绩最高的前3名。操作要点选择区域；数据/筛选注意：

多个条件是“与”关系。取消自动筛选：数据/筛选5数据处理：筛选1、自动筛选5数据处理：高级筛选

2、高级筛选1）筛选“期末”或“总评”成绩在80（含）以上的男同学。2）期末70-90，或者男生。操作要点数据/高级

1、条件的写法（同一行为“与”，不同行为“或”）；

2、文本型数据不必写=号；

3、日期条件中用的是日期的数值格式；见项目6-4

4、条件区要与数据区分开，条件字段占一行。多字段“或”条件时使用5数据处理：高级筛选2、高级筛选多字段“或”条件时使用5数据处理：高级筛选3、高级筛选中使用计算条件

如何筛选出销售额高于销售额平均值的记录？操作要点数据/高级

1、条件区列标题可以是任何文本，但不能与现有列标题重名；

2、用绝对引用方式引用数据表外单元格；

3、用相对引用方式引用数据表外单元格。

见项目6-4：使用计算条件5数据处理：高级筛选3、高级筛选中使用计算条件5数据处理：条件格式

如何突出显示最大10%销售利润的记录？

如何用数据条、色阶、图标突出显示记录？

如何突出显示最好10%、最差20%及的记录？操作要点

1、选择区域；

2、开始/条件格式。

思考：如何去除条件格式？5数据处理：条件格式5数据处理：

分类汇总任务描述对学生成绩表操作1、按班级求期末之和、总评之和（1）按班级排序，数据—分类汇总（2）分类汇总后还可再排序（如按总评降序）。2、高级分类汇总按“班级”分类汇总总评总和、期末平均。操作要点高级分类汇总就是按同一类别进行不同字段或不同汇总方式的汇总。按“班级”排序，两次分类汇总，第2次不要勾选“替换当前分类汇总”。如要删除分类汇总，选“全部删除”。5数据处理：分类汇总任务描述对学生成绩表操作5数据处理：

分类汇总任务描述

3、嵌套分类汇总按“班级”分类汇总总评总和，以及各班不同“性别”的期末之和。

操作要点嵌套分类汇总就是在一种分类汇总基础上再进行不同类别的分类汇总。按“班级+性别”排序，按班级、性别两次分类汇总，第2次不要勾选“替换当前分类汇总”。分类汇总结果可复制到别处：选择区域—按F5键—定位—可见单元格—复制—粘贴。5数据处理：分类汇总任务描述6-1函数：

基本知识语法格式：

函数名（参数1，参数2，…)

括号是必须的。如:=pi()

参数可以是常数、公式、其他函数。示例：

以sum为例，多种方式求三个数之和。如何输入函数？

1）=直接输入函数名称和地址等参数；2）=输入名称和（，通过选择区域输入地址，手工输入其他符号和参数；

3）

=输入名称和（，使用fx函数向导输入地址等参数；

4）单击fx使用函数向导输入函数。

6-1函数：基本知识语法格式：6-2函数：分类汇总Subtotal通过帮助学习函数通过查询Subtotal

函数的帮助信息，了解其功能和用法，不使用“数据—分类汇总”，直接使用该函数按班级对学生成绩进行分类汇总。操作要点单击fx获得帮助。Subtotal6-2函数：分类汇总Subtotal通过帮助学习函数6-3函数：

逻辑函数IF

如何判断“总评”成绩是否及格或有效？操作要点1、=IF（G151>=60，“及格”,“不及格”)2、=IF(AND(G151>=60,G151<=100),“及格”,“不及格”)

AND是“与”函数。3、=IF(OR(G151>100,G151<0),“无效”,“有效”)注意：

IF函数的输入技巧。=IF（,单击fx,对话框内输入IF和AND等嵌套函数的输入技巧。6-3函数：逻辑函数IF6-3函数：

逻辑函数IF

如何根据“总评”成绩计算成绩5个等级。操作要点1、IF(G87>=90,"优",IF(G87>=80,"良",IF(G87>=70,"中",IF(G87>=60,"及格","不及格"))))

2、成绩分5等级的另一种方法：=choose(if(G87<60),1,int((G87-50)/10)+1,”不及格”,”及格”,”中”,”良”,”优”)

思考：按博硕本科、专科学历计算学历分，如分别为5、4、3、2分。F6-3函数：逻辑函数IF如何根据“总评”成绩计算成6-4函数：日期函数如何根据生日计算年龄（有两种方法）

1、使用Datedif,Today函数求年龄；

2、使用Year,Today求年龄；操作要点注意两个方法的差别

=Datedif(F98,Today(),“Y”)=Year(Today())-Year(F98)此方法算出的年龄“虚”F注意：此函数没有输入提示。思考：计算存款到期日6-4函数：日期函数如何根据生日计算年龄（有两种方法）6-5函数：求和如何用Sumif分别统计各班的总评成绩汇总值。操作要点

=Sumif($E$110:$E$116,I110,$G$110:$G$116)

求和区域条件区域条件值EFGI6-5函数：求和如何用Sumif分别统计各班的总评成绩汇总值6-5函数：求和如何用Sumifs分别统计各班各性别学生的总评成绩汇总值。操作示例:(可以有多个条件)

=SUMIFS($G$181:$G$187,$D$181:$D$187,D190,$E$181:$E$187,C191)

求和区域条件区域1条件值1条件值2条件区域26-5函数：求和如何用Sumifs分别统计各班各性别学生的总6-6函数计数函数如何用Countif分别统计各等级人数。操作要点：

5等级人数=Countif($G$110:$G$116,">=90")

4等级人数=Countif($G$110:$G$116,">=80")-Countif($G$110:$G$116,">=90")G1101111121131141151166-6函数计数函数如何用Countif分别统计各等级人6-6函数计数函数

主要计数函数的比较：Count(区域)数值单元格个数CountA(区域)非空单元格个数CountBlank(区域)空白单元格个数CountIF(区域，条件)CountIFS(区域1，条件1，区域2，条件2…)

去掉重复值后统计个数：A1A=sum(if(a1:a4<>””,1/countif(a1:a4),a1:a4)))2B3A4C6-6函数计数函数主要计数函数的比较：6-7排名函数使用Rank函数按总评排名。操作要点

Rank（要排位的数值，排位区域，选项）选项：非0则升序排位0或省略则降序排位=Rank(G133,$G$133:$G$139)数值区域G1336-7排名函数使用Rank函数按总评排名。数值区域G136-8乘积函数用乘积函数Sumproduct用作交叉统计

使用Sumproduct按班级、性别统计人数DEGH6-8乘积函数用乘积函数Sumproduct用作交叉统6-9垂直查找Vlookup函数如何根据某对应字段从另一表中查找数据并输入到本表？如：根据职称查询教师的课时费/节操作要点：

使用Vloopup函数。本函数的功能是：在表格或数组的首列查找指定的值，并返回表格或数组中其它列的值。6-9垂直查找Vlookup函数如何根据某对应字段从Vloopup函数应用一K列公式=VLOOKUP

($K2,$A$2:$B$6,2,0)

要找的值查找区域返回第2列精确比较ABC职称课时费/节教授60副教授55高工55讲师50助教45JKL姓名职称课时费/节张三高工

李四教授原始表结果表公式0或FALSE：精确匹配，若找不到返回#N/ATRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。Vloopup函数应用一要找的值查找区域返回第2列精确比较AVloopup函数应用二K列公式=VLOOKUP

(D2,$A$2:$B$6,2)

要找的值查找区域返回第2列近似比较近似查找阈值级别备注分数级别0不及格0<=X<6018不及格60及格60<=X<7082良好70