Excel常用功能和函数应用及表格设计实用教案

1、培训培训(pixn)(pixn)目的目的 设计简洁美观的数据表格 更加(gnji)准确而高效地工作 学习使用常用公式及函数 提高数据处理技能 发挥个人创造能力第1页/共56页第一页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用如何(rh)提高工作效率 格式设计 函数应用 数据处理 EXCEL表格设计第2页/共56页第二页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用如何提高工作效率 格式设计 函数应用(yngyng) 数据处理 其它应用(yngyng) EXCEL表格设计第3页/共56页第三页，共57页。快捷键使用快捷键使用(shyng)(shyng)如何提

2、高工作效率如何提高工作效率中 + E打开资源管理器 + D(或M) 清屏 + F搜索Alt + F4关闭windows任务（当前操作）Alt + Tab清屏CTRL+F或SHIFT+F5显示“查找”对话框ALT+ENTER在单元格中折行F4 或 CTRL+Y重复最后一次操作F2 (SHIFT+F2)编辑单元格 (编辑单元格批注)第4页/共56页第四页，共57页。快捷键使用快捷键使用(shyng)(shyng)如何提高工作效率如何提高工作效率CTRL+1显示“单元格格式”对话框CTRL+B应用或取消字体加粗格式CTRL+I应用或取消字体倾斜格式CTRL+U应用或取消下划线格式CTRL+C复制选定

3、区域CTRL+X剪切选定区域CTRL+V粘贴选定区域CTRL+Z撤消最后一次操作第5页/共56页第五页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用如何提高工作效率 格式(g shi)设计 函数应用 数据处理 其它应用 EXCEL表格设计第6页/共56页第六页，共57页。单元格格式单元格格式(g shi)(g shi)对任何选定(xun dn)的单元格区域设置格式，只需对选定(xun dn)区域按鼠标右键或键盘快捷菜单：设置单元格格式，包括：功能键第7页/共56页第七页，共57页。单元格格式单元格格式(g shi)-(g shi)-概述概述所有设置单元格格式只能对选定的范围起

4、作用，即Excel的任何操作都必须有指定的对象对选定的区域按鼠标右键或键盘(jinpn)菜单键设置单元格格式(F)即可设置单元格格式记住：不要选定整个表、整行、整列来设置格式，否则会无谓加大文件长度第8页/共56页第八页，共57页。单元格格式单元格格式(g shi)(g shi)数字数字此处只对“自定义数字格式”进行说明：设定常用数字格式：Crtl+Shift+! ，如：-12,345对于七位发票号码654可以设置自定义格式：0000000显示：0000654自定义格式顺序：正数格式;负数格式;零格式;文本格式如：#,#0;红色-#,#0;黄色(hungs)0;蓝色和0为数字占位符，为文本占位

5、符，“，”（逗号）为千分位符号d天，m月，y年，如：将2003-3-15显示成：今天是3月15日，用函数：=TEXT(单元格,“今天是m月d日”)或者自定义格式：“今天是”m“月”d“日”*表示重复，如：*#,#0.00，将数字前加满*号为在不同工作表中使用自定义格式，只需把含自定义格式的单元格复制到其他工作表中就可以了。第9页/共56页第九页，共57页。单元格格式单元格格式数字（日期数字（日期(rq)(rq)录入）录入）你的日期(rq)录入对了吗第10页/共56页第十页，共57页。单元格格式单元格格式(g shi)(g shi)对齐对齐对横向合并，一般不用“合并单元格”，用“水平对齐”中的“

6、跨列居中”对竖向合并，可以改变文字(wnz)方向对备注单元格，可以使用“缩小字体填充”对大单元格应在“垂直对齐”上选“居中”第11页/共56页第十一页，共57页。单元格格式单元格格式(g shi)(g shi)字体字体只有一句建议对表格正文(zhngwn)部分， 大多数时候都不需要-改变字体大小 只需要-按缩放打印即可实现 Ctrl+鼠标滚动轮（滚动前大后小）第12页/共56页第十二页，共57页。单元格格式单元格格式(g shi)(g shi)边框边框框内一般用单细线外框一般用加粗线表头与内容连接处一般单双线或单细线 当然也可以(ky)选择任意线型第13页/共56页第十三页，共57页。单元格格

7、式单元格格式(g shi)(g shi)保护保护第14页/共56页第十四页，共57页。单元格格式单元格格式(g shi)(g shi)页面设置页面设置可以在工具栏添加页面设置快捷键任何页面设置，首先必须确定打印机和纸张类型页边距：一般应选择水平居中(jzhng)，左右边距大多数情况下相等垂直居中(jzhng)视情况选择页眉/页脚：可以自定义页眉/页脚工作表：顶端标题(biot)行和左端标题(biot)列选项：视打印机是否支持，如：每张纸打印页数双面打印等第15页/共56页第十五页，共57页。单元格格式单元格格式(g shi)(g shi)数据有效性数据有效性可以任意定义序列或范围并通过菜单(c

8、i dn)选择第16页/共56页第十六页，共57页。单元格格式单元格格式(g shi)(g shi)条件格式条件格式(g shi)(g shi)可以输入(shr)公式或数值最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容最多可自动设置四种格式第17页/共56页第十七页，共57页。单元格格式单元格格式(g shi)(g shi)条件格式条件格式(g shi)(g shi)标出2015年产值前2的月份(yufn)第18页/共56页第十八页，共57页。单元格格式单元格格式(g shi)(g shi)条件格式条件格式(g shi)(g shi)20142014年年1212月生产产值月生

9、产产值序号序号生产部生产部姓名姓名吊杆吊杆1一组李明982一组张军993二组田心1004二组陈起1005三组王五956一组 黄七 88 7一组 赵六 92 8二组 王成 98 9二组 周玉 89 10三组 李有 94 20142014年年1212月生产产值月生产产值序号序号生产部生产部姓名姓名吊杆吊杆1一组李明982一组张军993二组田心1004二组陈起1005三组王五956一组 黄七 88 7一组 赵六 92 8二组 王成 98 9二组 周玉 89 10三组 李有 94 标出最后(zuhu) 2名的数值第19页/共56页第十九页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用

10、如何(rh)提高工作效率 格式设计 函数应用 数据处理 其它应用 EXCEL表格设计第20页/共56页第二十页，共57页。函数应用函数应用(yngyng)(yngyng)基本公式基本公式原则上输入任何公式，仅使用键盘，才能提高速度使用公式，除录入文字名，必须全部采用半角方式，包括标点符号在单元格开始输入，按“+”、“-”、“=”时，EXCEL视同输入公式对任一单元格，按 F2进入编辑状态，再按 F2 进入选择单元格公式中可以使用名称，名称必须与半角引号一起使用才正确单元格的引用：绝对引用和相对引用，使用F4切换，注意(zh y)切换顺序对同一表、不同表、不同工作簿均以键盘方式引用第21页/共5

11、6页第二十一页，共57页。函数函数(hnsh)(hnsh)应用应用工作表函数工作表函数(hnsh)(hnsh)IFSUMSUMIFCOUNTCOUNTIFVLOOKUPLEFTRIGHTMID&ABSMAXMINRANDDATEYEARMONTHDAYTODAYOFFSETTEXTRANKLENUPPERLOWERISERRORANDOR第22页/共56页第二十二页，共57页。函数函数(hnsh)(hnsh)应用应用ifif函数函数(hnsh)(hnsh)设定每门分数95分的则评定为“优秀”公式1 IF(COUNTIF(D18:F18,“0,-,优秀)公式2 IF(OR(D1595,E1595

12、,F15=95,E15=95,F15=95),优秀,-)序号序号 姓名姓名 语文语文 数学数学 英语英语 总分总分 评定评定1李明 98100100298优秀2张军 9998100297优秀3田心 1008592277-4陈起 1009999298优秀5王五959694285-第23页/共56页第二十三页，共57页。函数函数(hnsh)(hnsh)应用应用sumifsumif函数函数(hnsh)(hnsh)20142014年年1212月生产产值（件）月生产产值（件）序号 生产部姓名吊杆绳轮轴1一组李明 981001002一组张军 99981003二组田心 10085924二组陈起 100999

13、95三组王五959694按组统计当月生产(shngchn)产值sumif函数公式SUMIF($C$23:$C$27,C33, ($E$23:$E$27)序号序号 生产部生产部吊杆吊杆绳轮绳轮轴轴1一组1971982002二组2001841913三组959594sumifs函数公式SUMIFS($E$23:$E$27,$C$23:$C$27,C41)序号序号 生产部生产部吊杆吊杆绳轮绳轮轴轴1一组1971982002二组2001841913三组959694第24页/共56页第二十四页，共57页。函数函数(hnsh)(hnsh)应用应用vlookupvlookup函数函数(hnsh)(hnsh)从

14、下表分别提取(tq)个别员工当月绩效公式 VLOOKUP(J23,$L$9:$O$18,2,0)姓名姓名吊杆吊杆绳轮绳轮轴轴李明98 100 100 李有94 92 96 赵六92 95 98 周玉89 95 98 田心100 85 92 序号序号 生产部生产部姓名姓名吊杆吊杆绳轮绳轮轴轴1一组李明981001002一组张军99981003二组田心10085924二组陈起10099995三组王五9596946一组黄七8899997一组赵六9295988二组王成9890949二组周玉89959810三组李有949296第25页/共56页第二十五页，共57页。培训培训(pixn)(pixn)目录目

15、录 快捷键使用如何(rh)提高工作效率 格式设计 函数应用 数据处理 其它应用 EXCEL表格设计第26页/共56页第二十六页，共57页。数据处理数据处理排序排序(pi x)(pi x)仅当单一表格，表头连续(linx)时，才能用工具条快速排序对于有较复杂表头的表格，一定要选定范围后才能排序用选定范围后排序，可针对任一局部排序，如华南区域客户可以按行排序对于不想改变最终顺序的表格，可以加入序号列后排序序号序号姓名姓名语文语文数学数学英语英语总分总分1李明981001002982张军99981002973田心10085922774陈起10099992985王五959694285序号序号姓名姓名语

16、文语文数学数学英语英语总分总分1李明981001002984陈起10099992982张军99981002973田心10085922775王五959694285第27页/共56页第二十七页，共57页。数据处理数据处理筛选筛选(shixun)(shixun)选定一定范围后，可在相应的地方加入筛选(shixun)按钮使用自动筛选(shixun)关于高级筛选(shixun)：数据区、条件区与条件、或条件使用高级筛选(shixun)获取不重复记录序号序号姓名姓名语文语文数学数学英语英语总分总分1李明981001002982张军99981002973田心10085922774陈起10099992985王

17、五959694285第28页/共56页第二十八页，共57页。数据处理数据处理筛选筛选(shixun)(shixun)示例数（数学）示例数（数学）按单元格颜色(yns)序号序号姓名姓名语文语文数学数学英语英语总分总分1李明981001002983田心10085922775王五9596942852张军99981002974陈起1009999298序号序号姓名姓名语文语文数学数学英语英语总分总分1李明981001002982张军99981002974陈起10099992985王五959694285序号序号姓名姓名语文语文数学数学英语英语总分总分1李明981001002982张军99981002973

18、田心10085922774陈起10099992985王五959694285 按数字筛选(shixun)（数学95)第29页/共56页第二十九页，共57页。数据处理数据处理分类分类(fn li)(fn li)汇总汇总坚持习惯：对选定范围进行分类(fn li)汇总如何将未汇总的内容如型号复制下来如何删除分类(fn li)汇总如何将分类(fn li)汇总的结果复制到一个新表中将汇总结果中的“汇总”样全部删除第30页/共56页第三十页，共57页。数据数据(shj)(shj)处理处理数据数据(shj)(shj)透视表透视表数据透视表一定要有全部的列名称关于页、行、列选择(xunz)展开其中某项数据关于表

19、头合并：两个表头合成一个关于时间的组合关于数据更新：从数据源更新（如果仅调整透视表结构，不涉及数据源）第31页/共56页第三十一页，共57页。数据处理数据处理数据透视数据透视(tush)(tush)表应用表应用序号序号生产部生产部姓名姓名吊杆吊杆绳轮绳轮轴轴1一组李明981001002一组张军99981003二组田心10085924二组陈起10099995三组王五9596946一组李明88 99 99 7一组张军92 95 98 8二组田心98 90 94 9二组陈起89 95 98 10三组王五94 92 96 生产部吊杆绳轮轴二组387369383三组189188190一组37739239

20、7总计953949970计算(j sun)各组当月生产产值计算各人当月(dngyu)生产产值姓名吊杆绳轮轴陈起189194197李明186199199田心198175186王五189188190张军191193198总计953949970第32页/共56页第三十二页，共57页。数据处理数据处理文本处理文本处理测试长度：=len(b2)截取左边三个字：=left(b2,3)截取右边三个字：=right(b2,3)截取第X个字开始(kish)起N个字：=MID(b2,X,N)连接三个单元格：=b2&c2&e2数值数值结果结果测试长度4截取左边三个字截取左截取右边三个字三个字截取第X个字开始起N个字

21、取第X连接以下三个单元格座右铭座右铭第33页/共56页第三十三页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用如何提高(t go)工作效率 格式设计 函数应用 数据处理 其它应用 EXCEL表格设计第34页/共56页第三十四页，共57页。其它其它(qt)(qt)应用应用自定义个性化工具栏自定义个性化工具栏功能区最小化第35页/共56页第三十五页，共57页。其它其它(qt)(qt)应用应用自定义个性化工具栏自定义个性化工具栏自定义快速访问工具栏：鼠标在工具栏按右键，选“自定义命令”即可修改常用按钮，包括修改所有菜单(ci dn)条：增加、减少、重命名打开(d ki)(文件)打印

22、预览数据透视表和数据透视图向导控件照相第36页/共56页第三十六页，共57页。其它其它(qt)(qt)应用应用自定义个性化工具栏自定义个性化工具栏自定义(dngy)添加“打开”快速访问工具条第37页/共56页第三十七页，共57页。其它应用其它应用(yngyng)(yngyng)给表单找个好归宿给表单找个好归宿通用表格多用模板(mbn)，复制文件名（非文件内容）后立即更改名称并另存另存另存，防止原文件及数据丢失(设置自动保存)新建表单后即刻确认表格名称，特别是要取一个(y )个性化的名称，以便于日后查询，重点设计时注意记录编号填充并对表头做冻结处理，填充以浅色为限（深色复印会遮盖内容），冻结位置

23、根据内容而定充当标尺检查数值，防止数据错位（看错）；自动填充格式，包括公式适当时，可以插入批注第38页/共56页第三十八页，共57页。其它应用其它应用(yngyng)(yngyng)给表单找个好归宿给表单找个好归宿确保统计数据准确性和自动化，减少(jinsho)人为计算错误和重复性数字（文本与数值(shz)和字符）、对齐（上中下左右、自动换行、缩小字体填充、合并）、字体（字体、字形、字号）、填充(颜色)、边框、行与列距重点设置页码（多页便于装订排序和检查是否错漏），包括表格编号以检查最终输出效果和减少审美疲劳，此步不可少第39页/共56页第三十九页，共57页。其它应用其它应用(yngyng)(

24、yngyng)给表单找个好归宿给表单找个好归宿将表头以上内容（含文件名称）选定(xun dn)“打印标题”“顶端标题行” 所见非所印（得），打印必预览，防止超页/超边/内容遮盖，尤其对挂角式页面(y min)的处理（利用页面(y min)设置打印缩放功能尽可能合并到1页）按文件类型、年月分类分层存放，任何时候不要放桌面，如一定要放，只能放快捷方式第40页/共56页第四十页，共57页。培训培训(pixn)(pixn)目录目录 快捷键使用如何提高工作效率 格式(g shi)设计 函数应用 数据处理 EXCEL表格设计第41页/共56页第四十一页，共57页。第42页/共56页第四十二页，共57页。第43页/共56页第四十三页，共57页。第44页/共56页第四十四页，共57页。第45页/共56页第四十五页，共57页。第46页/共56页第四十六页，共57页。第47页/共56页第四十七页，共57页。第48页/共56页第四十八页，共57页。20162016上半年生产部状况分析上半年生产部状况分析序号项目1月2月3月4月5月6月合计12015年产值124,28640,203164,5