第一讲Excel高级应用基础

1、第一讲第一讲Excel高级应用基础高级应用基础 Excel 高级应用高级应用 计算机系：计算机系： 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 Excel高手高手 VBA 函函 数数 分分 析析 图图 表表 操操 作作 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 Excel 高级应用高级应用 Excel高级应用基础高级应用基础 Excel函数高级应用函数高级应用 Excel图表高级应用图表高级应用 数据透视表与数据透视图数据透视表与数据透视图 Excel与数据库应用与数据库应用 第一讲第

2、一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 Excel数据录入数据录入 Excel数据处理数据处理 Excel安全设置与数据保护安全设置与数据保护 Excel与其它应用程序的数据共享与其它应用程序的数据共享 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 一、一、 Excel数据录入数据录入 1、数据输入技巧、数据输入技巧 相关知识：相关知识： Excel支持的主要数据类型：支持的主要数据类型：N、C、T、D、L 单元格格式单元格格式 自动更正自动更正 数据有效性数据有效性 键盘快捷键键盘快捷键 第

3、一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习1：对：对“绩效考核绩效考核”表快速录入数据表快速录入数据 工作表要求：某车间进行绩效考核，需要每天统工作表要求：某车间进行绩效考核，需要每天统 计甲、乙、丙、丁四个班组的计甲、乙、丙、丁四个班组的实际实际产量和计划产量和计划 产量，并要标注是否完成任务。产量，并要标注是否完成任务。 工作表包括：工作表包括：“日期日期”、“班组班组”、“实产量实产量”、“计划计划 产量产量”、“是否完成任务是否完成任务”五列。五列。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Ex

4、cel高级应用基础高级应用基础 （1）选定单元格区域，右击）选定单元格区域，右击“设置单元格格式设置单元格格式” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2）简化重复录入文本：）简化重复录入文本：“工具工具”“自动更正自动更正 选项选项” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （3）设置数据有效性：）设置数据有效性：“数据数据”“有效性有效性” 日期列有效性日期列有效性 班组列有效性班组列有效性 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用

5、基础高级应用基础 （4）填充）填充“是否完成任务是否完成任务”列：选定单元格，在列：选定单元格，在 编辑栏输入公式。编辑栏输入公式。 如如:“=IF(C3D3,”是是”,”否否”)” 注意注意“文本型数据在文本型数据在IF函数中使用时要加双引号定界函数中使用时要加双引号定界 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 一、一、 Excel数据录入数据录入 2、数据导入技巧、数据导入技巧 相关知识：相关知识： Access数据库、数据表数据库、数据表 数据源数据源 导入外部数据导入外部数据 数据刷新数据刷新 第一讲第一讲 Excel高级应用基

6、础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习2：将：将Access数据库表导入数据库表导入Excel中。中。 (1)启动启动Access,建立建立“产品库产品库.mdb”，并在，并在 其中利用向导建立其中利用向导建立“产品明细表产品明细表” (2)在在Excel中，中，“数据数据”“导入外部数导入外部数 据据” “导入数据导入数据” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 定位数据源定位数据源 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 二、二、

7、Excel数据处理数据处理 1、数据高级筛选技巧、数据高级筛选技巧 相关知识：相关知识： 高级筛选高级筛选 条件区域条件区域 条件区域的构造规则条件区域的构造规则 “数据数据”“筛选筛选” “高级筛选高级筛选” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习3：“或或”条件筛选练习条件筛选练习 在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。 (1)筛选条件是筛选条件是“部门部门”列中包括列中包括“人事部人事部” 或或“营销部营销部”的数据行。的数据行。 (2)筛选条件是筛选条件是“部门部门”为为“开发部开

8、发部”，或，或 “到职日期到职日期”为为2003年的数据行。年的数据行。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 构造条件区域构造条件区域 “数据数据”“筛选筛选” “高级筛选高级筛选” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （1） 同一列中的同一列中的“或或”条件条件 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2） 多列中的多列中的“或或”条件（条条件（条 件必须在不同行）件必须在不同行） 第一讲第一讲 Excel高级

9、应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习4：“与与”条件筛选练习条件筛选练习 在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。 筛选条件是筛选条件是“部门部门”列中包括列中包括 “营销部营销部”， “性别性别”为为“男男”且且“到职日期到职日期”为为2003 年年1月月1日以后的数据行。日以后的数据行。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 同一行的多个同一行的多个“与与”条件条件 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级

10、应用基础 实例练习实例练习5：“与与”、“或或”复合条件筛选练复合条件筛选练 习习 在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。 筛选条件是筛选条件是“部门部门”为为 “营销部营销部”且且“到职到职 日期日期”为为2003年年1月月1日以后，日以后，“部门部门”为为 “开发部开发部”且且“到职日期到职日期”为为2003年年1月月1 日以前的数据行。日以前的数据行。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 条条 件件 区区 域域 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高

11、级应用基础 实例练习实例练习6：将公式作为条件的高级筛选练习：将公式作为条件的高级筛选练习 在在“员工信息簿员工信息簿”中按下列条件筛选数据。中按下列条件筛选数据。 筛选条件筛选条件: “部门部门”为为 “营销部营销部”或或”编辑部编辑部” 工资值小于数据表中所有员工平均工资的员工信息。工资值小于数据表中所有员工平均工资的员工信息。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （1）筛选条件，筛选出低于平均工资的员工信息）筛选条件，筛选出低于平均工资的员工信息: 条件区域条件区域:$C$2:$D$3 在在D3D3单元格中输入单元格中输入 “

12、=AVERAGE(F11:F110)=AVERAGE(F11:F110)” 在在C3C3单元格中输入单元格中输入 “=F11$D$3=F11$D$3” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2）“数据数据”“筛选筛选” “高级筛选高级筛选”,在在 高级筛选对话框中设置列表区域和条件区域高级筛选对话框中设置列表区域和条件区域. 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （3）将筛选结果复制到）将筛选结果复制到”输出表输出表”中中: 数据区域数据区域:A11:G59。 这时可以返回

13、源数据表，执行这时可以返回源数据表，执行“数据数据”“筛筛 选选” “全部显示全部显示”，将数据恢复全部显示状态。，将数据恢复全部显示状态。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （4）筛选条件，在输出表中构建条件区域）筛选条件，在输出表中构建条件区域. “或或”条件条件 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （5）“数据数据”“筛选筛选” “高级筛选高级筛选”,在在 高级筛选对话框中设置列表区域和条件区域高级筛选对话框中设置列表区域和条件区域. . 第一讲第一讲 Excel

14、高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （6）筛选结果如下）筛选结果如下 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 二、二、 Excel数据处理数据处理 2、数据有效性设定与规则、数据有效性设定与规则 相关知识：相关知识： 设置数据类型和有效范围设置数据类型和有效范围 有效性检验有效性检验: 对已有数据审核对已有数据审核:圈释错误数据圈释错误数据 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习7：某单位要统计岗位考试成绩：某单位要统计岗位

15、考试成绩,为方便输入为方便输入 时更加准确时更加准确,对表中的数据输入类型及范围设置相应对表中的数据输入类型及范围设置相应 的有效性的有效性.具体要求如下具体要求如下: 考号为考号为4位字符，不能多于或少于位字符，不能多于或少于4位位 笔试和口试成绩为笔试和口试成绩为0100之间的数字，之间的数字， 输入成绩时，显示输入提示信息输入成绩时，显示输入提示信息 输入错误时显示出错警告输入错误时显示出错警告 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 打开打开“实例表实例表岗位考试成绩岗位考试成绩.xls” (1)在在”实例练习实例练习7”工作表

16、中，选中考号区域（工作表中，选中考号区域（A3:A14) “数据数据” “有效性有效性” 右击右击 “设置单元格格式设置单元格格式”“数字数字” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2）设定成绩的数据类型和有效性，选中）设定成绩的数据类型和有效性，选中(E3:F14)区域区域 “数据数据” “有效性有效性”，分别设置最大值、最小值、提示、，分别设置最大值、最小值、提示、 出错等信息出错等信息 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习8：对：对“岗位考试成绩表

17、岗位考试成绩表”利用设置有效性利用设置有效性 规则，利用数据审核工具找出错误的数据并标明。规则，利用数据审核工具找出错误的数据并标明。 设置笔试和口试成绩为设置笔试和口试成绩为0100之间的数字之间的数字 找出已输入的错误数据找出已输入的错误数据 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 打开打开“实例表实例表岗位考试成绩岗位考试成绩.xls” （1）在）在”实例练习实例练习8”工作表，选中工作表，选中(E3:F14)区域区域, 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2）圈定错

18、误数据）圈定错误数据. “工具工具” “公式审核公式审核” “显示显示公式审核公式审核工具栏，工具栏， 单击单击”公式审核公式审核”工具栏中的工具栏中的”圈释无效数据圈释无效数据按钮按钮. 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 二、二、 Excel数据处理数据处理 3、数据分类汇总、数据分类汇总 相关知识：相关知识： 排序排序 求和、平均值、最大值、最小值求和、平均值、最大值、最小值 分类汇总分类汇总 合并计算合并计算 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习9

19、：对：对“3A公司员工基本信息表公司员工基本信息表”进行分类进行分类 汇总以统计各部门的收入情况汇总以统计各部门的收入情况,具体要求如下具体要求如下: l统计各个部门员工的平均工资统计各个部门员工的平均工资 注意注意:要先排序后汇总要先排序后汇总 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 打开打开“实例表实例表3A公司员工基本信息表公司员工基本信息表.xls” (1)在在”实例练习实例练习9”工作表，对各部门员工工资分类汇总：工作表，对各部门员工工资分类汇总： 单击单击“部门部门”列任一单列任一单 元格，再单击常用工具元格，再单击常用工具

20、 栏上栏上“升序升序”按钮按钮 。 单击数据表的任一单元格，单击数据表的任一单元格， “数据数据”“分类汇总分类汇总” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2）单击）单击 分级显示按钮分级显示按钮 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习10：对：对“3A公司员工基本信息表公司员工基本信息表”进行分进行分 类汇总以统计各部门的收入情况类汇总以统计各部门的收入情况,具体要求如下具体要求如下: 统计各部门男、女员工奖金的最大值统计各部门男、女员工奖金的最大值 将

21、分类汇总结果分页打印出来将分类汇总结果分页打印出来 注意注意:要先对部门分类汇总要先对部门分类汇总,再对性别分类汇总再对性别分类汇总 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 打开打开“实例表实例表3A公司员工基本信息表公司员工基本信息表.xls” (1)在在”实例练习实例练习10”工作表，统计各部门男、女奖金工作表，统计各部门男、女奖金 的最大值：的最大值： 单击单击“部门部门”列任一单元格，列任一单元格， “数据数据” “排序排序” 。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础

22、（2） 单击数据表的任一单元格，单击数据表的任一单元格，“数据数据”“分类汇总分类汇总” 先对先对“部门部门”汇总最大值汇总最大值 部门汇总结果部门汇总结果 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （3） 再单击数据表的任一单元格，再单击数据表的任一单元格，“数据数据”“分类汇总分类汇总” 再对再对“性别性别”汇总最大值汇总最大值 “性别性别”汇总结果汇总结果 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （4）分页显示汇总结果）分页显示汇总结果 再单击数据表的任一非空单元格，再单击数

23、据表的任一非空单元格，“数据数据”“分类汇总分类汇总”， 选中选中 “每组数据分页每组数据分页”，单击，单击确定确定“按钮。按钮。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （4）“文件文件”“页面设置页面设置” “工作表工作表” 打印预览打印预览 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 删除分类汇总表的方法：删除分类汇总表的方法： “数据数据”“分类汇总分类汇总”，单击，单击“全部删除全部删除” 按钮按钮 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高

24、级应用基础高级应用基础 二、二、 Excel数据处理数据处理 4、数据的合并计算、数据的合并计算 相关知识：相关知识： 链接链接 “数据数据”“合并计算合并计算” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习11：对：对“R公司工资簿公司工资簿”通过合并计算得出通过合并计算得出 一季度工资汇总表，要求：一季度工资汇总表，要求： 当修改了某个月份某项工资数值时，工资汇总表的当修改了某个月份某项工资数值时，工资汇总表的 数据随之更新数据随之更新 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础

25、高级应用基础 打开打开“实例表实例表R公司工资簿公司工资簿.xls” (1)将工资表的标记性文字复制到将工资表的标记性文字复制到“工资汇总单工资汇总单”表中，表中， 并将表头更改为并将表头更改为“R公司一季度工资单公司一季度工资单 ” 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 （2） 单击汇总表中（单击汇总表中（C3：I20）单元格区域，）单元格区域，“数数 据据”“合并计算合并计算”，确定要合并的数据源位置，并，确定要合并的数据源位置，并 添加到列表中，选中添加到列表中，选中“创建连至源数据的链接创建连至源数据的链接”复选复选 框。框。“确定确定” 通过折叠钮选择引用数通过折叠钮选择引用数 据位置，单击据位置，单击“添加添加” 按钮，依次处理三个月按钮，依次处理三个月 的数据。的数据。 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 三、三、 Excel安全设置与数据保护安全设置与数据保护 对工作表、工作簿、单元格的保护对工作表、工作簿、单元格的保护 相关知识：相关知识： 文件保存文件保存 加密加密 保护单元格区域保护单元格区域 第一讲第一讲 Excel高级应用基础高级应用基础 第一讲第一讲Excel高级应用基础高级应用基础 实例练习实例练习