Excel常用功能与函数应用及表格设计

Excel常用功能与函数应用及表格设计2017年7月19日培训目的设计简洁美观的数据表格更加准确而高效地工作学习使用常用公式及函数提高数据处理技能发挥个人创造能力培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理EXCEL表格设计培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理其它应用EXCEL表格设计快捷键使用—如何提高工作效率中+E打开资源管理器+D(或M)清屏+F搜索Alt+F4关闭windows任务（当前操作）Alt+Tab清屏CTRL+F或SHIFT+F5显示“查找”对话框ALT+ENTER在单元格中折行F4或CTRL+Y重复最后一次操作F2(SHIFT+F2)编辑单元格(编辑单元格批注)快捷键使用—如何提高工作效率CTRL+1显示“单元格格式”对话框CTRL+B应用或取消字体加粗格式CTRL+I应用或取消字体倾斜格式CTRL+U应用或取消下划线格式CTRL+C复制选定区域CTRL+X剪切选定区域CTRL+V粘贴选定区域CTRL+Z撤消最后一次操作培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理其它应用EXCEL表格设计单元格格式对任何选定的单元格区域设置格式，只需对选定区域按鼠标右键或键盘快捷菜单：设置单元格格式，包括：单元格格式→概述单元格格式→数字单元格格式→对齐单元格格式→字体单元格格式→边框单元格格式→保护单元格格式→页面设置单元格格式→数据有效性单元格格式→条件格式功能键单元格格式--概述所有设置单元格格式只能对选定的范围起作用，即Excel的任何操作都必须有指定的对象对选定的区域按鼠标右键或键盘菜单键→设置单元格格式(F)→即可设置单元格格式记住：不要选定整个表、整行、整列来设置格式，否则会无谓加大文件长度单元格格式—数字此处只对“自定义数字格式”进行说明：设定常用数字格式：Crtl+Shift+!，如：-12,345对于七位发票号码654可以设置自定义格式：0000000显示：0000654自定义格式顺序：正数格式;负数格式;零格式;文本格式如：#,##0;[红色]-#,##0;[黄色]0;[蓝色]@＃和0为数字占位符，＠为文本占位符，“，”（逗号）为千分位符号d→天，m→月，y→年，如：将2003-3-15显示成：今天是3月15日，用函数：=TEXT(单元格,“今天是m月d日”)或者自定义格式：“今天是”m“月”d“日”*表示重复，如：**#,##0.00，将数字前加满*号为在不同工作表中使用自定义格式，只需把含自定义格式的单元格复制到其他工作表中就可以了。单元格格式—数字（日期录入）2017.5.82017.05.82017-5.82017-05-0817-5-817-05-08你的日期录入对了吗单元格格式—对齐对横向合并，一般不用“合并单元格”，用“水平对齐”中的“跨列居中”对竖向合并，可以改变文字方向对备注单元格，可以使用“缩小字体填充”对大单元格应在“垂直对齐”上选“居中”单元格格式—字体只有一句建议对表格正文部分，大多数时候都不需要--改变字体大小只需要--按缩放打印即可实现Ctrl+鼠标滚动轮（滚动→前大后小）单元格格式—边框框内一般用→单细线外框一般用→加粗线表头与内容连接处一般→单双线或单细线当然也可以选择任意线型单元格格式—保护•对单元格内容进行锁定，不允许修改（只有工作表被保护时才会生效）锁定•在保护工作表时，隐藏工作表中的公式隐藏单元格格式—页面设置可以在工具栏添加页面设置快捷键任何页面设置，首先必须确定打印机和纸张类型页边距：一般应选择水平居中，左右边距大多数情况下相等垂直居中视情况选择页眉/页脚：可以自定义页眉/页脚工作表：顶端标题行和左端标题列选项：视打印机是否支持，如：每张纸打印页数双面打印等单元格格式—数据有效性可以任意定义序列或范围并通过菜单选择单元格格式—条件格式可以输入公式或数值最多可以设三种条件格式，加无条件时的格式，即根据一个单元格的内容最多可自动设置四种格式单元格格式—条件格式标出2015年产值前2的月份单元格格式—条件格式2014年12月生产产值序号生产部姓名吊杆1一组李明982一组张军993二组田心1004二组陈起1005三组王五956一组黄七887一组赵六928二组王成989二组周玉8910三组李有942014年12月生产产值序号生产部姓名吊杆1一组李明982一组张军993二组田心1004二组陈起1005三组王五956一组黄七887一组赵六928二组王成989二组周玉8910三组李有94标出最后2名的数值培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理其它应用EXCEL表格设计函数应用—基本公式原则上输入任何公式，仅使用键盘，才能提高速度使用公式，除录入文字名，必须全部采用半角方式，包括标点符号在单元格开始输入，按“+”、“-”、“=”时，EXCEL视同输入公式对任一单元格，按F2进入编辑状态，再按F2进入选择单元格公式中可以使用名称，名称必须与半角引号一起使用才正确单元格的引用：绝对引用和相对引用，使用F4切换，注意切换顺序对同一表、不同表、不同工作簿均以键盘方式引用函数应用—工作表函数IFSUMSUMIFCOUNTCOUNTIFVLOOKUPLEFTRIGHTMID&ABSMAXMINRANDDATEYEARMONTHDAYTODAYOFFSETTEXTRANKLENUPPERLOWERISERRORANDOR函数应用—if函数设定每门分数≥95分的则评定为“优秀”公式1IF(COUNTIF(D18:F18,“95)0,-,优秀)公式2IF(OR(D1595,E1595,F1595),-,优秀)公式3IF(AND(D15=95,E15=95,F15=95),优秀,-)序号姓名语文数学英语总分评定1李明98100100298优秀2张军9998100297优秀3田心1008592277-4陈起1009999298优秀5王五959694285-函数应用—sumif函数2014年12月生产产值（件）序号生产部姓名吊杆绳轮轴1一组李明981001002一组张军99981003二组田心10085924二组陈起10099995三组王五959694按组统计当月生产产值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函数应用—vlookup函数从下表分别提取个别员工当月绩效公式VLOOKUP(J23,$L$9:$O$18,2,0)姓名吊杆绳轮轴李明98100100李有949296赵六929598周玉899598田心1008592序号生产部姓名吊杆绳轮轴1一组李明981001002一组张军99981003二组田心10085924二组陈起10099995三组王五9596946一组黄七8899997一组赵六9295988二组王成9890949二组周玉89959810三组李有949296培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理其它应用EXCEL表格设计数据处理—排序仅当单一表格，表头连续时，才能用工具条快速排序对于有较复杂表头的表格，一定要选定范围后才能排序用选定范围后排序，可针对任一局部排序，如华南区域客户可以按行排序对于不想改变最终顺序的表格，可以加入序号列后排序序号姓名语文数学英语总分1李明981001002982张军99981002973田心10085922774陈起10099992985王五959694285序号姓名语文数学英语总分1李明981001002984陈起10099992982张军99981002973田心10085922775王五959694285数据处理—筛选选定一定范围后，可在相应的地方加入筛选按钮使用自动筛选关于高级筛选：数据区、条件区与条件、或条件使用高级筛选获取不重复记录序号姓名语文数学英语总分1李明981001002982张军99981002973田心10085922774陈起10099992985王五959694285数据处理—筛选示例数（数学）按单元格颜色序号姓名语文数学英语总分1李明981001002983田心10085922775王五9596942852张军99981002974陈起1009999298序号姓名语文数学英语总分1李明981001002982张军99981002974陈起10099992985王五959694285序号姓名语文数学英语总分1李明981001002982张军99981002973田心10085922774陈起10099992985王五959694285按数字筛选（数学95)数据处理—分类汇总坚持习惯：对选定范围进行分类汇总如何将未汇总的内容如型号复制下来如何删除分类汇总如何将分类汇总的结果复制到一个新表中将汇总结果中的“汇总”样全部删除数据处理—数据透视表数据透视表一定要有全部的列名称关于页、行、列选择展开其中某项数据关于表头合并：两个表头合成一个关于时间的组合关于数据更新：从数据源更新（如果仅调整透视表结构，不涉及数据源）数据处理—数据透视表应用序号生产部姓名吊杆绳轮轴1一组李明981001002一组张军99981003二组田心10085924二组陈起10099995三组王五9596946一组李明8899997一组张军9295988二组田心9890949二组陈起89959810三组王五949296生产部吊杆绳轮轴二组387369383三组189188190一组377392397总计953949970计算各组当月生产产值计算各人当月生产产值姓名吊杆绳轮轴陈起189194197李明186199199田心198175186王五189188190张军191193198总计953949970数据处理—文本处理测试长度：=len(b2)截取左边三个字：=left(b2,3)截取右边三个字：=right(b2,3)截取第X个字开始起N个字：=MID(b2,X,N)连接三个单元格：=b2&c2&e2数值结果测试长度4截取左边三个字截取左截取右边三个字三个字截取第X个字开始起N个字取第X连接以下三个单元格座右铭座右铭培训目录快捷键使用—如何提高工作效率格式设计函数应用数据处理其它应用EXCEL表格设计其它应用—自定义个性化工具栏功能区最小化其它应用—自定义个性化工具栏自定义快速访问工具栏：鼠标在工具栏按右键，选“自定义\命令”即可修改常用按钮，包括修改所有菜单条：增加、减少、重命名打开(文件)打印预览数据透视表和数据透视图向导控件照相其它应用—自定义个性化工具栏自定义添加“打开”快速访问工具条其它应用—给表单找个好归宿巧用模板自建个性化名称填充和冻结表头套用表格格式通用表格多用模板，复制文件名（非文件内容）后立即更改名称并另存→另存→另存，防止原文件及数据丢失(设置自动保存)新建表单后即刻确认表格名称，特别是要取一个个性化的名称，以便于日后查询，重点设计时注意记录编号填充并对表头做冻结处理，填充以浅色为限（深色复印会遮盖内容），冻结位置根据内容而定充当标尺检查数值，防止数据错位（看错）；自动填充格式，包括公式适当时，可以插入批注其它应用—给表单找个好归宿公式与函数应用表格一致性设置页眉页脚隐藏网络线确保统计数据准确性和自动化，减少人为计算错误和重复性数字（文本与数值和字符）、对齐（上中下左右、自动换行、缩小字体填充、合并）、字体（字体、字形、字号）、填充(颜色)、边框、行与列距重点设置页码（多页便于装订排序和检查是否错漏）