Excel高级应用基础-办公应用教程.pdf

《Excel高级应用基础-办公应用教程.pdf》由会员分享，可在线阅读，更多相关《Excel高级应用基础-办公应用教程.pdf（61页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、AExcel高级应用基础AExcel函数高级应用AExcel图表高级应用A数据透视表与数据透视图 Excel与数据库应用第一讲Excel高级应用基础 Excel数据录入 Excel数据处理 Excel安全设置与数据保护 Excel与其它应用程序的数据共享第一讲Excel高级应用基础一、Excel数据录入1、数据输入技巧相关知识：Excel支持的主要数据类型：N、T、D、L单元格格式自动更正据有效性键盘快捷键第一讲Excel高级应用基础实例练习1：对绩效考核表快速录入数据工作表要求：某车间进行绩效考核，需要每天统计甲、乙、丙、丁四个班组的实绩产量和计划产量，并要标注是否完成任务。工作表包括：曰期

2、、班组、实产量、计划产量、”是否完成任务五列。第一讲Excel高级应用基础（1）选定单元格区域，右击二二展望鬣需i寤式”单元格格式 0）国为剪切上复制心粘贴9选择性粘贴6）.插入复）删除也）清除内容设置单元格格式电）.列宽隐藏国）取消隐藏也）数对齐II字体|边框|图窠|保护分类:示例日期用 数 专产比记义 规值而计鬻分数学本殊定 常数货金辞百分科文特自类型复）：*2 001-3-1 4*2 001年3月14日 二。一年三月十四日 二OO一年三月三月十四日_i2 00T区域设置（国家/地区）1）：中文（中国）日期格式把日期和时间系列数显示为日期值。除了带星号（*）的项目，应用的格式不会按照操作系

3、统来改变日期顺序。确定 取消 第一讲Excel高级应用基础(2)简化重复录入文本：“工具”一“自动更正 选项”第一讲Excel高级应用基础(3)设置数据看薮豆：“薮爵;二工看薮荏；厂日期列有效性班组列有效性第二进.巨度级座用建硬N(4)遁至“是完施警窝,氮连冠S浣落;茬,编辑栏输入公式。如:“二工F(C3D3,“是“否)”注意“文本型数据在工F函数中使用时要加双引号定第一讲Excel高级应用基础一、Excel数据录入2、数据导入技巧相关知识：Access数据库.数据表数据源导入外部数据据刷新第二进旦理匮级座里至碘、实例练习2:将Access数据库表导入Excel中:(1)启动Access,建立

4、“产品库.mdb，并在 其中利用向导建立“产品明细表(2)在Excel中，数据 一”导入外部数据 一入数据第一讲Excel高级应用基础导入数据数据的放置位置现有工作表鱼）：1 _确定_ 1k$A$10取消O新建工作表国）罚创建数据透视表电）.雇性外部数据区域属性名称但）：产品库 产品明细表一2查询定X.0 保存查询定义）保存密码 化）;i序后桧性_叼允许后台刷新图编辑查询）一.国定位数据源厂1刷新频率 国）I分钟打开工作应时，自动刷新（x）lI I保存工作表之目11删除 数据格式及布局0包含字段名电）包含行号QJ）E 调整列宽3）0 保留列排序/筛选/布局（L）0 保留单元格格式6）数据区域中

5、的行数随刷新而更改的方式：G）为新数据插入单元格，并册J除没有使用的单元格（C）o为新数据插入整行，清除没有使用的单元格）O用新数据强差现有单元格，并清除没有使用的单元格内容）在与数据相邻的列向下埴充公式电）E二二确定二二一 取消第一讲Excel高级应用基础X二、Excel数据处理1、数据高级筛选技巧相关知识：高级筛选 条件区域 条件区域的构造规则 数据 一筛选 一高级筛选”.第二进Ere值级座用超碘N实例练习3:或条件筛选练习在员工通讯簿按下列条件筛选数据。(1)筛选条件是“部门列中包括人事部 或营销部”的数据行。(2)筛选条件是“部门为开发部，或 到职日期为2003年的数据行。第一讲Exc

6、el高级应用基础构造条件区域“数据”-“筛选”一高级筛选”第一讲Excel高级应用基础A11 I4?7I9等910002910004910013910016910017910019B人事部 营销部C同一列中的“或”条件G部门营销部 醯律人事部 营销部 人事部 营销部910022重自部910023人事部910024人事部910026营销部910029营销部910031营销部910035人事部910037营销部910039营销部910042营销部姓张莫刘张江王王于于丰目周王吴孟名蕙雯美媚蔚韶 美 天小 兴华 乐天 纲尚 艇化杰小齐相小雨别到职日期联络电话2 002-4-5 2 2 01-2 000

7、2 002-8-5 2 2 03-1 0002 003-8-8 2 2 07-83492 003-8-1 1 2 2 07-83522 003-6-5 2 2 07-83532 003-6-7 2 2 07-83552 003-6-8 2 2 07-83562 003-6-1 0 2 2 07-83582 003-6-1 1 2 2 07-83592 002-6-1 2 2 2 07-83602 003-6-1 4 2 2 07-83622 003-6-1 7 2 2 07-83652 002-6-1 9 2 2 07-83672 003-6-2 3 2 2 07-83712 003-6-2

8、5 2 2 07-83732 003-6-9 2 2 01-2 002住址北市大安区通化街3号 北市南港区正义北路100号 北县前进市中和路52号 北县三山出宿义路200号 北县中和市健康路99号 北市信义区信使路3号 北县新店市安族路6号瓒 北市大庆区通化街3号 北县永久市中和路343号 北市南港区正义北路134号北县三重市信义路200号 北市信义区信义路3号 北市前门区大平北路23号 北县中和市祥安路9号 北县中和市健身路99号嵋啊室第9号一第一讲Excel高级应用基础、ABCDE12都口到职曰用到职曰期、3开发部420 0 3-1-1=2 003-1-134同一行的多个“与”条件567V

9、89员工通讯簿10职员编号 部门23 910013营销部：30 910020营销部32 910022营销部39 910029营销部47 910037营销部52 910042营销部71 910061营销部92 910082营销部95 910085营销部告大副杰相莹春大亮 芥王于周孟黄杨吴王间亮别性男男男男男男男男到职日期 联络电话 住址2 003-8-82 2 07-8349北县前进市中和路52号2 003-6-82 2 07-8356北县新店市安然路6号9楼2 003-6-1 02 2 07-8358北市大庆区通化街3号2 003-6-1 72 2 07-8365北市信义区信义路3号2 003

10、-6-2 52 2 07-8373北县中和市健身路99号2 003-6-92 2 01-2 002北县中和市安祥路9号2 003-6-52 2 01-2 02 1北市北港区正北路100号2 003-6-1 82 2 06-7676北市中正区中山南路522号2 003-6-2 1 2 2 06-7679 北市前门区大平北路23号纲 雨男第一讲Excel高级应用基础实例练习5:与、或复合条件筛选练 习在员工通讯簿按下列条件筛选数据。筛选条件是“部门为“营销部且倒职日期为2003年1月1日以后，“部门为 开发3T且“至!1职日期为2003年1月1 前的数据行。第一讲Excel高级应用基础A工224.

11、6 7_89DBc部门营销部到职日期-2 003-1-1开发部2 003-1-1|员工通讯簿EG条件区域职员军号 部I J910003开发部910005开发部910013营销部910017营销部910020营销部910022营销部910026营销部910029营销部910037营销部910039营销部910042营销部910044营销部910051营销部910054开发部910061营销部910064营销部由慧美美大副兴杰相仪莹莉国亮春乐纲化雨力一元910070营销部910077营销部910082营销部910085营销部910089营销部梅爱性男女男女男男女男男女男女女男男女女女女到职日期联络

12、电话2 002-6-52 2 05-45782 002-1 0-5 2 2 06-76632 003-8-82 2 07-83492 003-6-52 2 07-83532 003-6-82 2 07-83562 003-6-1 0 2 2 07-83582 003-6-1 4 2 2 07-83622 003-6-1 7 2 2 07-83652 003-6-2 5 2 2 07-83732 003-4-52 2 07-83752 003-6-92 2 01-2 0022 003-6-2 12 2 01-2 0042 003-1-12 2 01-2 01 12 002-4-52 2 01-2

13、 01 42 003-6-52 2 01-2 02 12 003-8-72 2 01-2 02 42 003-8-1 0 2 2 01-2 02 72 003-4-62 2 06-76642 003-6-1 3 2 2 06-76712 003-6-1 8 2 2 06-76762 003-6-2 12 2 06-7679list北县永和市中和路343号 北县中和市安和路9号 北县前进市中和路52号 北县中和市健康路99号 北县新店市安然路6号9楼 北市大庆区通化街3号 北县三重市信义路200号 北市信义区信义路3号 北县中和市健身路99号 北市信义区信使路37号 北县中和市安祥路9号 北县中

14、和市健康路9g号 北市南港区正义北路134号 北县太和市安康路99号 北市北港区正北路100号 北县中和市健身路99号 北县新店市安然路6号 北县三山市信义路200号 北县永久市中和路343号 北市中正区中山南路522号 北市前门区大平北路23号第一讲Excel高级应用基础实例练习6:将公式作为条件的高级筛选练习在“员工信息簿中按下列条件筛选数据。筛选条件:部门为营销部或编辑部 工资值小于数据表中所有员工平均工资的员工信第一讲Excel高级应用基础(1)筛选条件，筛选出低于平均工资的员工信息?条件区域:$C$2：$D$3D在D3单元格中输入“二 AVERAGE(FH:F110)”12工资肝平均

15、工资净工=F1K$D$3.0 6工资低于平均工资 FALSEC在C3单元格中输入“=F11$D$3”平均工5g契B第一讲Excel高级应用基础(2)“数据”一“筛选”一“高级筛选”，在 高级筛选对话框中设置列表区域和条件区域.高级筛选方式0在原有区域显示筛选结果0)O将筛选结果复制到其他位置(0)列表区域(1)：$a$io?$g$iio 民条件区域(C)：实例练习6i$c$2:$比 区I 复制到(T):|I同筵择不重复的记录第1 确定 I|取消第一讲Excel高级应用基础(3)将筛选结果复制到“输出表”中：数据区域:A11：G59。这时可以返回源数据表，执行“数据”一“筛 选，一“全部显示”，

16、将数据恢复全部显示状态。第一讲Excel高级应用基础(4)筛选条件，在输出表中构建条件区域.“或”条件第一讲Excel高级应用基础（5）“数据”一“筛选”一“高级筛选”，在 高级筛选对话框中设置列表区域和条件区域.高级筛选 区1方式 在原有区域显示筛选结果 也）O 将筛选结果复制到其地位:置（0）列表区域（L）：$A$1 lj$G$59 收口条件区域（C）：输出表，$D$2$D$4|171玉.选择不重复的记录 确定 取消 第一讲Excel高级应用基础(6)筛选结果如下111215nr19212万23242528293132333436373839404142910007910012910020

17、910021910026910027910029910030910031910040910041910045910047910049910050编辑部 编辑部 营销部编辑部营销部 编辑部营销部姓名 性别李寺观 王大纲 宋/卜才其 吕兴化 王华 周杰44编辑部 营销部 编辑部 编辑部 编辑部 编辑部 编辑部 编辑部910061营销部 910063编辑部 910064营销部 910065编辑部 910066编辑部 910068编褥部 910070营销部 910073编辑部 910075编辑部 910076编辑部 910078编辑部 910081编辑部 910084编辑部 910085营销部 910

18、090编辑部 910099编辑部周大涛 王小小 刘小霞 宋文彬 王志平 李国庆 刘小梅 孟青海 枝 齐东 孙夏 孙霰 杨排风 田利 李红爱 郑伟 黄莹莹 马山力 黄小三 孟夫为 王秋王亮亮肖克强武文学到职日期工资住址2 003-2-51119北县中和市安康路99号2 003-8-71702北市平安区通化街13号2 003-6-81043北县新店市安然路6号9楼2 003-6-93043北市中大区延平北路4号2 003-6-1 43458北县三重市信义路200号2 003-8-1 51541北县太和市安康路99号2 003-6-1 71127d匕市信义区信义路3毋2 003-6-1 85529d

19、匕县新店市安康路5号2 002-6-1 91992北市前门区天平北路23号2 003-8-1 04060北县新店市安然路6号2 003-6-81563北市北港区正义北路10 0号2 003-6-2 43569北市中正区中山北路10号2 004-2-15281北县新店市安然路6号9楼2 002-7-83453北市大庆区通化街3号2 002-9-32803北县永久市 中和路343号2 003-6-51533北市北港区正北路100号2 003-8-63619北县三山市信路400号2 003-8-71864北县 中和市 健身路99号2 003-8-85596北市中区中山北路10号2 003-8-914

20、07北市信义区信使路37号2 003-4-63702北县三山市信义路200号2 003-6-94688北市信义区信使路3号2 003-6-1 11439北市中大区延平北路4号2 002-6-1 21330北市大庆区通化街3号2 003-6-1 45344北市南港区正义北路134号2 003-6-1 71520北县太和市安康路99号2 003-6-2 02126d匕县新店市安康路5号2 003-6-2 12806北市前 门区 大平北路23号2 002-4-53441北县三山市信路400号2 004-1-12197d匕市前门区大平d匕路23号第一讲Excel高级应用基础二、Excel数据处理2、数

21、据有效性设定与规则相关知识：设置数据类型和有效范围 有效性检验：对已有数据审核:圈释错误数据第二进.以.匾级座用暹地实例练习7:某单位要统计岗位考试成绩，为方便输入,时更加准确，对表中的数据输入类型及范围设置相应 的有效性.具体要求如下：考号为4位字符，不能多于或少于4位笔试和口试成绩为0 100之间的数字，输入成绩时，显示输入提示信息4输入错误时显示出错警告第一讲Excel高级应用基础打开“实例表岗位考试成绩.xls”(1)在”实例练习7”工作表中，选中考号区域(A3：A14右击一“设置单元格格式”一“数“数据”“有效性”单元格格式数字：对齐 字体 边框 图案 保护规值而计期间分数学因 常数

22、货会日时百分科腌在文本单元格格式中，数字作为 文本处理。示例0I 确定 I 取消数据有效性第一讲Excel高级应用基础(2)设定成绩的数据类型和有效性，选中(E3：F14)区“数据”出错等信息数据有效性设置 输入信息出错警告输入法模式有效性条件.许 整数数据(D)：介于芦：)最大值3)|100|I“有效性”，分别设置最大值、最小值、提示、忽略空值也)I确定 I B8数据有效性冈设置I输入信息I出错警告 输入法模式0选定单元格时显示输入信息)选定单元格时显示下列输入信息：标题(X)：|输入成绩输入信息：成绩不能为空,必须在。100之间,请认真检查II全部清除位)第二进.Ek直级座甩堇碰。实例练习

23、8:对岗位考试成绩表利用设置有效性 规则，利用数据审核工具找出错误的数据并标明。设置笔试和口试成绩为。100之间的数字找出已输入的错误数据第一讲Excel高级应用基础打开“实例表岗位考试成绩.xls”在“实例练习8”工作表，选中（E3；F14）区域,第一讲Excel高级应用基础(2)圈定错误数据.“工具”一“公式审核”一“显示公式审核工具栏,单击”公式审核”工具栏中的”圈释无效数据按钮.公式审核 X玲史坦里三1/|国力第一讲Excel高级应用基础二、Excel数据处理3、数据分类汇总 相关知识：排序求和、平均值、最大值、最小值分类汇总合并计算第一讲Excel高级应用基础实例练习9:对3A公司员

24、工基本信息表进行分类汇总以统计各部门的收入情况，具体要求如下:统计各个部门员工的平均工资注意:要先排序后汇总第一讲Excel高级应用基础打开“实例表3A公司员工基本信息表.xls”(1)在”实例练习9”工作表，对各部门员工工资分类汇,y单击“部门”列任一单 元格，再单击常用工具 栏上“升序”按钮。单击数据表的任一单元格,“数据”一“分类汇总”第一讲Excel高级应用基础(2)单击ITT7分级显示按钮ITRT71ABCDEFGHJI2职员编号姓名0711 0H H H Hft!3A公司员工基本信息表部门 到职日期总计平均值5240奖金 住址总计平均值ABCDEFGH13A公司员工基本信息表2职员

25、编号姓名性别年龄部门|到职日期工资奖金46编辑部平均值545362开发部平均值607281人事部平均值4595106营销部平均值48205240)7第一讲Excel高级应用基础实例练习10：对 3A公司员工基本信息表”进行分类汇总以统计各部门的收入情况，具体要求如下:统计各部门男、女员工奖金的最大值将分类汇总结果分页打印出来注意:要先对部门分类汇总,再对性别分类汇总第二进巨09直级座用堇碘R打开“实例表3A公司员工基本信息表.xls”(1)在”实例练习10”工作表，统计各部门男、女奖金 的最大值：单击“部门”列任一单元格,“数据”一“排序”O第一讲Excel高级应用基础单击数据表的任一单元格，

26、“数据”一“分类汇总”先对“部门”汇总最大优区分类汇总0替换当前分类汇总(Q)每组数据分页化)0汇总结果显示在数据下方)全部册除与)|确定 取消M 108ABCDE 1 FGHI13A颂虹基样僦2御加m触46输热W362删熟i相81人酬耿i956曹解牖m部门汇总结果再对“性别”汇总最大优EEE分类汇总X分类字段(A)总方式QD:展大值选定汇总项也)：到职日期 口工资|0|奖金回替换当前分类汇总C)每蛆数据分页也)0汇总结果显示在数据下方6)全部删除国)确定 取消 一12_A-BCDEF I G I H 1_ I3A公司员工基本信息表职员编号姓名性别年龄部门到职日期工资奖金住址25 1T4849

27、男最大61973女最大6930910003章学由男36开发部2002Y-57245724北县永和市中和路343号910009陈逸讯里37开发部2003-6-56328632北市信义区信义路1号5051910014吴小东男19开发部2003-8-98311831北市北港区正义北路10居910054赵亮亮男29开发部2002-4-58418841北县太和市安康路99号52910086田间男24开发部2003-6-225140514北市平安区通=街13号53910094牒佳利男45开发部2003692737273北县新店市安然路6号910096111男34开发部2003-6-219734973北市中

28、正区中山南路52再9101001李历史与45开发部2003-12-14627462北市平安区通=街13号男最大973910005邹慧女45开发部2002-10-57745774北县中和市安和路9号91000g|桎惠埼女41开发部2003-4-53244324北市中正区中山南路1号910010江桂洱19开发部2003-8-57032703北县新店市安庄路5号9楼910033张落距37开发部2003-6-216930693北县前进市中路5泻910034快小杉37开发部2003-6-221596159北市北港区正北路100号910072钱胜利-4-r 又qq开发部2003-6-84079407北市中

29、正区中山北路10号910074郑利娜女34开发部2003-6-107921792北县新店市安然路6号9楼女最大792910019王天天男47人事部2003-6-72179217北市信义区信使路3号91002引于天尚男34人事部2003-6-118417841北县永久市中和路343号6768910055吴厚森男人事部2002-6-54498449北市中正区中山南路52潟910069|李红男45人事部2003-6-52a2g282北县中和市安祥路9号6991(083|杨夏天男90人事部2002-6-195089508北市信义区信义路3号7091(092唐里山男50人事部2003-8-1016541

30、65北市中区中山北路10号717291(0971金雨和男QQ人事部2003-6-242197219北市信义区信义路3号男最大6841“性别”汇总结果第一讲Excel高级应用基础(4)分页显示汇总结果再单击数据表的任一非空单元格，“数据”一“分类汇总”，选中“每组数据分页，单击确定按钮。第一讲Excel高级应用基础(4)“文件”一“页面设置”一“工作表”3 A公司员工基本信息表姓名性别年塞n列取日期工诙遥910001刘幡嚓男45焉同如2002-2-59025902910006周杰会男45潴簿如2002-12-51241554310007陶哲招40编信那2003-2-51724172310011张

31、三男25笫话部2003-8-63333939910012卒寺双男23编无如2003-8-72802180910015季大雨男21编造部2003-8-1056 07560910021末小模男45编馆如2003-6-98146814910036管帆男31编馆部2003-6-248201820910038陈海男33编造即2002-4-52336233910047幸国庆重28笳值如2004-2-16013601910049刘小梅男30捌谓部2002-7-88379837310060室开发男37编造部2003-4-53368336910062肖小司扇38焉诸如2003-8-54909490310063齐

32、东男36独辕邻2003-8-63378337910075苗W烹勇35编造部2003-6-111388138910081盂大为男32潴簿如200A6T7926692S310084王男37捌馆邻2003-6-207693769910087田及男32编造邻200k6-234755475910088魏平平男45编痢邠2003-6-246857685310090肖克强男43箱岗邠20027-57300730910098率母男36编信部2002*17748774910099武文学男35潴铸邠2004-1-19738973男星大973打印预览第一讲Excel高级应用基础删除分类汇总表的方法：“数据 一 按钮

33、”分类汇总，单击“全部删除第一讲Excel高级应用基础二.Excel数据处理4、数据的合并计算相关知识：链接 数据 一 合并计算第一讲Excel高级应用基础实例练习11：对“R公司工资簿通过合并计算得出 一季度工资汇总表，要求：当修改了某个月份某项工资数值时，工资汇总表的 数据随之更新第一讲Excel高级应用基础、打开“实例表R公司工资簿.xls”(1)将工资表的标记性文字复制到“工资汇总单”表中,并将表头更改为“R公司一季度工资单”第一讲Excel高级应用基础单击汇总表中（C3：120）单元格区域，“数 据”f“合并计算”，确定要合并的数据源位置，并 添加到列表中，选中“创建连至源数据的链接

34、”复选通过折叠钮选择引用数 据位置，单击“添加”按钮，依次处理三个月 的数据。第一讲Excel高级应用基础三、Excel安全设置与数据保护 对工作表.工作簿、单元格的保护 相关知识：文件保存 加密 保护单元格区域第一讲Excel高级应用基础实例练习12:对7高炉8月硅数统计表进行 保护，具体要求：全部工作表中只有D列数据可以修改对工作表设置密码（123）对整个工作簿设置密码（456）第一讲Excel高级应用基础打开“实例表7高炉8月硅数统计.xls”(1)设置单元格保护选择D列数据，右击一“设置单元格格式”一“保 护”，取消“锁定”复选框，“确定”第一讲Excel高级应用基础(2)设置单元格保

35、护“工具”一“保护”一允许用户编辑区域”第一讲Excel高级应用基础X确认密码冈重新输入密码但）:*警告：丢失或忘记密码后将永远无法打开该文件？建 议将密码及其相应工作簿和工作表名称的列表保存在 安全的地方G主意，密码区分大小写）。确定取消第二进了次g.圆级座里整硼。(3)设置工作簿保护“工具”一“选项”一“安全性”I确定I取消确认密码重新输入密码国):*警告：丢失或忘记密码后将永远无法打开该文件，建 议将密码及其相应工作簿和工作表名称的列表保存在 安全的地方G主意，密码区分大小写)。确定 I 取消 第一讲Excel高级应用基础撤消对工作表的保护(2)工具-“保护一撤消工作 表保护第一讲Exc

36、el高级应用基础隐藏工作表的数据右击工作表名称一“查看代码Herosoft Visual Basic 7高炉8月硅效统计.x 1,：遍:蜴（工）泡困恒 植入（i）株xm mu mi 运行出】Tfl（T）川椿仔京Q】宙口值）常助onu J U I a _3亨重命名（R）口寸巨-OAPr.i.ct 1)-，曰 Microsoft Excl 对象 Sheetl供例燎习12)ffl)Sht2 CSht2)8 sh“t3(Sht3)Thi sWorkbookM 7高炉8月磋效统it.Sheet 1(代码)插入9删除Q）3期)I 0，)移动或复制工作表也选定全部工作表）工作表标签颜色（!）.ScrollA

37、rShee3亘Sheel3 FlsS4“，3 Work&bt 按手毋序|掖分类序|年查看代码W）U-xl5h*tMidln2-x】Sh”，VyyHi dd.n|(名称)Di spl yP ac*Br ks Displ*yRichtToLft EnblAutoFilter EnablClculftti on EntblOutlinincEn bl P i vo(Tabie EnbleSl*cti onFal0-xlMoR*strletionsSheet3第一讲Excel高级应用基础四、Excel与其它应用程序的数据共享相关知识：源数据.I标数据“选择性粘贴、粘贴链接 更新.第二进E国左.直级座用基地、实例练习13:Excel与Word数据共享 Excel 与 Powerpoint 数据共享以绩效考核.xls为例