EXCEL常用技巧ppt课件.ppt

EXCEL常用技巧,1,本次培训内容安排,培训时间：2小时（18:00-20:00）内容安排如下：-EXCEL学习指南10分钟-EXCEL应知基础概念25分钟-EXCEL常用技巧与函数45分钟-EXCEL解决问题实例操作40分钟,2,1.EXCEL学习指南,3,1-EXCEL2003基础课程（郭安定）EXCEL基本操作、菜单、功能介绍，讲解比较清晰，建议初学者学习2-EXCEL2003即学即会多媒体教程微软OFFICE讲师，功能介绍比较全面，讲解中规中矩基础篇：EXCEL基本操作教学，适合初学者学习提高篇：图表、数据分析、高级应用简介，适合初学者提高3-EXCEL2003提高实战技巧精粹实战技巧应用提高，比较实用，讲解清晰，适合有一定基础的学习者所附实例表有参考价值4微软EXCEL2003实用技巧微软教程，EXCEL技巧提高篇，适合具有一定基础的学习者5-EXCEL2003参考课程网友搜集制作，内容比较丰富，建议作为学习参考资源，比如函数讲解等6-EXCEL2007视频基础教程网友制作，可以对EXCLE2007了解和学习，从基础概念开始,EXCEL视频操作教程介绍,4,EXCEL学习方法提示,EXCEL概述优秀的数据分析软件功能十分强大功能可以组合扩展EXCEL学习建议全面了解功能，知之后方有用之的可能为解决问题、提高效率学习应用，积累实战经验，提高会很快建议随时在网上搜索解决问题的办法，资源很丰富建议使用首选EXCEL自带基本功能和函数，VBA能不用尽量不用建立使用数据表和统计图体现问题的思维（多看）EXCEL是一个工具,解决一个问题的方法有多种，并非唯一途径,5,2.EXCEL应知基础概念,6,工作表、单元格、公式输入,工作簿与工作表了解概念和基本设置（字体、表数设置、标签颜色等）单元格EXCEL2003单元格的范围：256列，65536行默认单元格名称：A1，B5等；R1C1单元格名称：R1C1，R5C6等；自定义单元格名称单元格属性，可以自定义公式输入单元格用等于号（=）输入公式或点击fx输入公式常用公式求和、平均、计数等直接点击即时统计显示求和、计数、平均值等（右键定制）输入公式用点选单元格的方式要善于使用公式的帮助菜单了解函数功能,7,工作表表的复制与移动,点击右键弹出菜单,选择本工作簿，则可以在本工簿中复制一个本工作表,是拷贝还是移动，如果是拷贝，打勾,选择新工作簿，则可以将本工作表生成一个新的工作簿,8,图表生成功能,图表功能是EXCEL的重要功能，必须掌握，教程均涉及，建议按教程自学，本处不再详述。图表多练习设置如何设置属性如果数据能动态更新或计算，图表也能成为动态图表,9,打印设置,页面设置的方法：选取“文件”页面设置，进入页面设置界面；顶端表头设置整页打印设置显示页设置,10,$符号绝对引用和相对引用,批量输入公式，在一个单元格的公式录入完成后，拖动单元格可以快速批量输入公式，应掌握引用单元格的变化规律，观察是绝对引用还是相对引用。一般只研究1个单元格中的公式即可。在公式中：A3-行、列均可随着单元格的拖动相对变化$A3-钉住列，行随着单元格的拖动相对变化A$3-钉住行，列随着单元格的拖动相对变化$A$3-行、列均钉住，单元格拖动不发生变化快捷转换键：鼠标放在单元格F4举例,11,3.EXCEL常用函数与技巧,12,对明细表进行汇总计数、求和函数,一般（主要用于明细表）计数：COUNT(C2:C100)（对数值计数）求和：SUM(C2:C100)筛选（主要用于明细表）计数：SUBTOTAL(9,C2:C100)求和：SUBTOTAL(3,C2:C100)条件（主要用于统计表）计数:COUNTIF(C2:C100,22)求和：SUMPRODUCT(C2:C10022),C2:C100)举例,13,扩展：多条件匹配求和的应用,SUMPRODUCT(A3:A10=“条件1”)*(C2:I2=“条件2”)*(B3:B10=条件3)*C3:I9)在一个表的特定区域中查找引用同时满足多个条件数据之和。如果满足条件的必须为唯一值，可用于查找标准。如果表中符合条件的数不止一个，则会返回它们的和。实例,14,逻辑判断函数IF(判断条件，条件成立的结果，条件不成立的结果)文本连接符“文本1”（静态日期）取日期的年、月、日取年：year()取月：month()取日：day()年龄计算方法、用年、月、日计算、用出生日期到当前日期的天数差折算实例,其他常用函数,15,高级筛选,数据筛选：点击“数据”“筛选”“自动筛选”打勾取消筛选：点击“数据”“筛选”“自动筛选”去勾全部显示：点击“数据”“筛选”“全部显示”高级筛选：点击“数据”“筛选”“高级筛选”高级筛选的用处：筛选条件可以多个，条件可以事先写好，筛选的条例更加清晰，避免出错。利用“高级筛选”功能可以在一个明细表中提取不重复的值,举例,16,条件格式,条件格式用于数据表的辅助提示功能。选定要操作的单元格，点击“格式”“条件格式”，在“设置”界面进行设定条件格式。,注意：条件格式如果设定公式，也存在绝对引用和相对引用的关系（格式刷刷格式，即会产生相对引用）已经设定条件格式的单元格拖拽，条件格式会自动复制到拖拽的单元格中EXCEL2003版条件格式最多可以设置3个，第一个权限最高。实例,继续添加条件,删除已添加的条件,17,数据条件匹配引用,VLOOKUP(C8,表A1:A100,3,0)两张表有相同元素的列，为将一张表的数据关联到另一张表中，常用此函数。一般先实现一个单元格的内容引用，通过拖拽的方式实现批量引用。拖拽时注意绝对引用和相对引用。实例,填写0（或者FALSE）,填写被查找表自起点列后的第N列,被查找表的区域，第一列必须包含查找的关键字,本表中的关键字单元格,0为精确匹配查找，1为模糊匹配，一般不用1,18,工作表的保护,当进行“保护工作表”时所发生的结果,选定允许编辑的单元格,录入时，指定单元格可以填入，其他不能更改EXCEL工作表保护功能只能做一般性保护，设置了一道屏障，但仍比较容易破解，重要信息不能依靠此方式,19,数据有效性,数据有效性的使用主要在于约束数据规范格式，对后续的方便统计工作会很有帮助。选定要操作的单元格，点击“数据”有效性“，在“设置”界面“允许”框内选取对数据的约束类型。选择“序列”，设置填写的序列的内容，填写时可有下拉菜单。序列的内容以逗号（小写）分割，见右图。实例,小写逗号,默认已选择,20,选择单元格,快捷键Ctrl+F不仅可以选择数值、公式内容，而且可以选择格式选择格式完成后一定要“清除查找格式”,21,数据透视表,实现快速统计基础是内容规范的明细表因为此功能使对明细表的统计效率较高，建议EXCEL统计工作尽量从明细表做起，利用此方法生成统计结果。如果有相对稳定格式的明细表，可以利用公式实现统计表。实例,22,选择性粘贴,在固化数据表时，使用选择性粘贴-数值；在整理数据时，有时会用到运算型的选择性粘贴；在整理数据时有时候会用到选择性粘贴转置；举例,23,EXCEL表内容图片化复制,按住Shift键后，点击菜单栏的“编辑”，下拉菜单中会有“复制图片”，可以将选中的内容复制成图片，粘贴到其他地方。,不按Shift键显示,按Shift键显示,制作幻灯片时常用。一般用文档编写时，保护原始文件用。EXCEL列数过多时，图片复制粘贴更方便。举例,24,其他常用功能与技巧（演示）,长数字输入：输入前面加“”（比如输入身份证号）快速调整行宽、列宽：整行整列选中调整EXCEL文档保存加密码：保存界面工具-安全选项拖拽单元格，自动添加序列：右下角自动填充选项单元格内的文本强制换行：Alt+回车单元格显示公式：Ctrl+排序:筛选状态下升序、降序，排序界面选项（可以扩充排序功能）定位功能：编辑-定位-选择定位条件跨栏居中：右键设置单元格格式-对齐-水平居中大纲模式：Shift+Alt+左方向键，取消大纲：Shift+Alt+右方向键使用宏完成重复性工作,25,4.EXCEL解决问题实例操作,26,EXCEL解决问题实例（正向应用）,1、现有一个EXCEL名单，多于实际人员数，初步判断存在重复记录，请快速确定哪条记录重复？2、一个单位现两个名单人数对不上，请找出对不上的原因何在？3、现有工时统计标准见附表1，上级要求每月上报工时统计报表见附表2，请设计一个统计方案，高效实现统计工作。,27,EXCEL应用实例分析（逆向分析）,技巧184-1提取零件规格中的最后序号F9分段调试技巧215动态查询数据总表应用注意名称定义技巧26