第一讲-Excel高级应用基础课件

Excel Excel 高级应用高级应用_办公应用教程办公应用教程计算机系：滕杰Excel高手高手VBA函函 数数分分 析析图图 表表操操 作作Excel 高级应用Excel高级应用基础Excel函数高级应用Excel图表高级应用数据透视表与数据透视图Excel与数据库应用Excel数据录入数据录入Excel数据处理数据处理Excel安全设置与数据保护安全设置与数据保护Excel与其它应用程序的数据共享与其它应用程序的数据共享一、一、Excel数据录入数据录入1、数据输入技巧、数据输入技巧相关知识：相关知识：Excel支持的主要数据类型：支持的主要数据类型：N、C、T、D、L单元格格式单元格格式自动更正自动更正数据有效性数据有效性键盘快捷键键盘快捷键实例练习实例练习1：对：对“绩效考核绩效考核”表快速录入数据表快速录入数据工作表要求：某车间进行绩效考核，需要工作表要求：某车间进行绩效考核，需要每天统计甲、乙、丙、丁四个班组的实每天统计甲、乙、丙、丁四个班组的实际产量和计划产量，并要标注是否完成际产量和计划产量，并要标注是否完成任务。任务。工作表包括：工作表包括：“日期日期”、“班组班组”、“实产量实产量”、“计划产量计划产量”、“是否完成任务是否完成任务”五列。五列。（1）选定单元格区域，右击“设置单元格格式”（2）简化重复录入文本：“工具”“自动更正选项”（3）设置数据有效性：“数据”“有效性”日期列有效性班组列有效性（4）填充“是否完成任务”列：选定单元格，在编辑栏输入公式。如:“=IF(C3D3,”是”,”否”)”注意“文本型数据在IF函数中使用时要加双引号定界一、一、Excel数据录入数据录入2、数据导入技巧、数据导入技巧相关知识：相关知识：Access数据库、数据表数据库、数据表数据源数据源导入外部数据导入外部数据数据刷新数据刷新实例练习实例练习2：将：将Access数据库表导入数据库表导入Excel中。中。(1)启动启动Access,建立建立“产品库产品库.mdb”，并在其，并在其中利用向导建立中利用向导建立“产品明细表产品明细表”(2)在在Excel中，中，“数据数据”“导入外部数据导入外部数据”“导入数据导入数据”定位数据源二、二、Excel数据处理数据处理1、数据高级筛选技巧、数据高级筛选技巧相关知识：相关知识：高级筛选高级筛选条件区域条件区域条件区域的构造规则条件区域的构造规则“数据数据”“筛选筛选”“高级筛选高级筛选”实例练习实例练习3：“或或”条件筛选练习条件筛选练习在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。(1)筛选条件是筛选条件是“部门部门”列中包括列中包括“人事部人事部”或或“营营销部销部”的数据行。的数据行。(2)筛选条件是筛选条件是“部门部门”为为“开发部开发部”，或，或“到职日到职日期期”为为2003年的数据行。年的数据行。构造条件区域“数据”“筛选”“高级筛选”（1）同一列中的“或”条件（2）多列中的“或”条件（条件必须在不同行）实例练习实例练习4：“与与”条件筛选练习条件筛选练习在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。筛选条件是筛选条件是“部门部门”列中包括列中包括“营销部营销部”，“性别性别”为为“男男”且且“到职日期到职日期”为为2003年年1月月1日以后日以后的数据行。的数据行。同一行的多个“与”条件实例练习实例练习5：“与与”、“或或”复合条件筛选练习复合条件筛选练习在在“员工通讯簿员工通讯簿”按下列条件筛选数据。按下列条件筛选数据。筛选条件是筛选条件是“部门部门”为为“营销部营销部”且且“到职日期到职日期”为为2003年年1月月1日以后，日以后，“部门部门”为为“开发部开发部”且且“到职日期到职日期”为为2003年年1月月1日以前的数据日以前的数据行。行。条 件 区 域实例练习实例练习6：将公式作为条件的高级筛选练习：将公式作为条件的高级筛选练习在在“员工信息簿员工信息簿”中按下列条件筛选数据。中按下列条件筛选数据。筛选条件筛选条件:“部门部门”为为“营销部营销部”或或”编辑部编辑部”工资值小于数据表中所有员工平均工资的工资值小于数据表中所有员工平均工资的员工信息。员工信息。（1）筛选条件，筛选出低于平均工资的员工信息:条件区域:$C$2:$D$3 在D3单元格中输入“=AVERAGE(F11:F110)”在C3单元格中输入“=F11$D$3”（2）“数据”“筛选”“高级筛选”,在高级筛选对话框中设置列表区域和条件区域.（3）将筛选结果复制到”输出表”中:数据区域:A11:G59。这时可以返回源数据表，执行“数据”“筛选”“全部显示”，将数据恢复全部显示状态。（4）筛选条件，在输出表中构建条件区域.“或”条件（5）“数据”“筛选”“高级筛选”,在高级筛选对话框中设置列表区域和条件区域.（6）筛选结果如下 二、二、Excel数据处理数据处理2、数据有效性设定与规则、数据有效性设定与规则相关知识：相关知识：设置数据类型和有效范围设置数据类型和有效范围有效性检验有效性检验:对已有数据审核对已有数据审核:圈释错误数据圈释错误数据实例练习实例练习7：某单位要统计岗位考试成绩：某单位要统计岗位考试成绩,为方为方便输入时更加准确便输入时更加准确,对表中的数据输入类型及对表中的数据输入类型及范围设置相应的有效性范围设置相应的有效性.具体要求如下具体要求如下:考号为考号为4位字符，不能多于或少于位字符，不能多于或少于4位位笔试和口试成绩为笔试和口试成绩为0100之间的数字，之间的数字，输入成绩时，显示输入提示信息输入成绩时，显示输入提示信息输入错误时显示出错警告输入错误时显示出错警告打开“实例表岗位考试成绩.xls”(1)在”实例练习7”工作表中，选中考号区域（A3:A14)“数据”“有效性”右击“设置单元格格式”“数字”（2）设定成绩的数据类型和有效性，选中(E3:F14)区域“数据”“有效性”，分别设置最大值、最小值、提示、出错等信息实例练习实例练习8：对：对“岗位考试成绩表岗位考试成绩表”利用设置有效利用设置有效性规则，利用数据审核工具找出错误的数据性规则，利用数据审核工具找出错误的数据并标明。并标明。设置笔试和口试成绩为设置笔试和口试成绩为0100之间的数字之间的数字找出已输入的错误数据找出已输入的错误数据打开“实例表岗位考试成绩.xls”（1）在”实例练习8”工作表，选中(E3:F14)区域,（2）圈定错误数据.“工具”“公式审核”“显示公式审核工具栏，单击”公式审核”工具栏中的”圈释无效数据按钮.二、二、Excel数据处理数据处理3、数据分类汇总、数据分类汇总相关知识：相关知识：排序排序求和、平均值、最大值、最小值求和、平均值、最大值、最小值分类汇总分类汇总合并计算合并计算实例练习实例练习9：对：对“3A公司员工基本信息表公司员工基本信息表”进行进行分类汇总以统计各部门的收入情况分类汇总以统计各部门的收入情况,具体要求具体要求如下如下:l统计各个部门员工的平均工资统计各个部门员工的平均工资注意:要先排序后汇总打开“实例表3A公司员工基本信息表.xls”(1)在”实例练习9”工作表，对各部门员工工资分类汇总：单击“部门”列任一单元格，再单击常用工具栏上“升序”按钮。单击数据表的任一单元格，“数据”“分类汇总”（2）单击 分级显示按钮实例练习实例练习10：对：对“3A公司员工基本信息表公司员工基本信息表”进行进行分类汇总以统计各部门的收入情况分类汇总以统计各部门的收入情况,具体要求具体要求如下如下:统计各部门男、女员工奖金的最大值统计各部门男、女员工奖金的最大值将分类汇总结果分页打印出来将分类汇总结果分页打印出来注意:要先对部门分类汇总,再对性别分类汇总打开“实例表3A公司员工基本信息表.xls”(1)在”实例练习10”工作表，统计各部门男、女奖金的最大值：单击“部门”列任一单元格，“数据”“排序”。（2）单击数据表的任一单元格，“数据”“分类汇总”先对“部门”汇总最大值部门汇总结果（3）再单击数据表的任一单元格，“数据”“分类汇总”再对“性别”汇总最大值“性别”汇总结果（4）分页显示汇总结果再单击数据表的任一非空单元格，“数据”“分类汇总”，选中“每组数据分页”，单击确定“按钮。（4）“文件”“页面设置”“工作表”打印预览删除分类汇总表的方法：删除分类汇总表的方法：“数据数据”“分类汇总分类汇总”，单击，单击“全部删除全部删除”按钮按钮二、二、Excel数据处理数据处理4、数据的合并计算、数据的合并计算相关知识：相关知识：链接链接“数据数据”“合并计算合并计算”实例练习实例练习11：对：对“R公司工资簿公司工资簿”通过合并计算通过合并计算得出一季度工资汇总表，要求：得出一季度工资汇总表，要求：当修改了某个月份某项工资数值时，工资汇当修改了某个月份某项工资数值时，工资汇总表的数据随之更新总表的数据随之更新打开“实例表R公司工资簿.xls”(1)将工资表的标记性文字复制到“工资汇总单”表中，并将表头更改为“R公司一季度工资单”（2）单击汇总表中（C3：I20）单元格区域，“数据”“合并计算”，确定要合并的数据源位置，并添加到列表中，选中“创建连至源数据的链接”复选框。“确定”通过折叠钮选择引用数据位置，单击“添加”按钮，依次处理三个月的数据。三、三、Excel安全设置与数据保护安全设置与数据保护对工作表、工作簿、单元格的保护对工作表、工作簿、单元格的保护相关知识：相关知识：文件保存文件保存加密加密保护单元格区域保护单元格区域实例练习实例练习12：对：对7高炉高炉8月硅数统计表进行月硅数统计表进行保护，具体要求：保护，具体要求：全部工作表中只有全部工作表中只有D列数据可以修改列数据可以修改对工作表设置密码（对工作表设置密码（123）对整个工作簿设置密码（对整个工作簿设置密码（456）打开“实例表7高炉8月硅数统计.xls”(1)设置单元格保护 选择D列数据，右击“设置单元格格式”“保护”，取消“锁定”复选框，“确定”(2)设置单元格保护“工具”“保护”“允许用户编辑区域”(3)设置工作簿保护“工具”“选项”“安全性”撤消对工作表的保护撤消对工作表的保护（2）“工具工具”“保护保护”“撤消工作表保护撤消工作表保护”隐藏工作表的数据隐藏工作表的数据右击工作表名称右击工作表名称“查看代码查看代码”四、四、Excel与其它应用程序的数据共享与其它应用程序的数据共享相关知识：相关知识：源数据、目标数据源数据、目标数据“选择性粘贴选择性粘贴”、粘贴链接、粘贴链接更新更新实例练习实例练习13：Excel与与Word数据共享数据共享Excel与与Powerpoint数据共享数据共享以绩效考核以绩效考核.xls为例为例