EXCEL常用技巧201112

《EXCEL常用技巧201112》由会员分享，可在线阅读，更多相关《EXCEL常用技巧201112（32页珍藏版）》请在金锄头文库上搜索。

1、EXCEL 常用技巧2011.121. EXCEL学习指南l EXCEL2003基础课程（郭安定）EXCEL基本操作、菜单、功能介绍，讲解比较清晰，建议初学者学习 l EXCEL2007新特性初探（郭安定）EXCEL2007新增功能介绍 l EXCEL2007视频基础教程网友制作，可以对EXCLE2007了解和学习，从基础概念开始 l 微软EXCEL2003实用技巧微软教程，EXCEL技巧提高篇，适合具有一定基础的学习者 l EXCEL2003提高实战技巧精粹（EXCEL HOME）实战技巧应用提高，比较实用，讲解清晰，适合有一定基础的学习者所附实例表有参考价值 l EXCEL图表实战技巧精粹

2、（EXCEL HOME）Excel图表提高篇，适合需要稍复杂图表应用的人员 l EXCEL VBA技巧精粹（EXCEL HOME）Excel VBA应用的技巧，附电子书、范例和部分视频演示 l EXCEL2007 VBA办公范例应用Excel 2007 VBA应用，附范例EXCEL视频操作教程介绍（光盘）EXCEL学习方法提示EXCEL 概述 n 优秀的数据分析软件（行业标准，基本技能，知识一定时期不会淘汰） n 功能十分强大，可以组合扩展（常用的只有5%功能） n 不仅是办公软件（科学计算）EXCEL学习建议 全面了解功能，知之后方有用之的可能 形成数据字段规范习惯，能够更好的发挥EXCEL

3、的功能 建议遇到问题随时在网上搜索解决问题的办法，资源很丰富 建议使用首选EXCEL自带基本功能和函数，VBA能不用尽量不用(编程) 建立使用数据表和统计图体现问题的思维（多看） 用EXCEL解决一类问题途径很多，熟练应用基础上会游刃有余2. EXCEL应知基础概念工作表、单元格、公式输入工作簿与工作表 了解概念和基本设置（字体、表数设置、标签颜色等） 单元格 EXCEL2003单元格的范围：256列，65536行 默认单元格名称：A1，B5等； R1C1单元格名称：R1C1，R5C6等； 自定义单元格名称 单元格属性，可以自定义 公式输入 单元格用等于号（=）输入公式或点击 fx 输入公式

4、常用公式求和、平均、计数等直接点击 即时统计显示求和、计数、平均值等（右键定制） 输入公式用点选单元格的方式 要善于使用公式的帮助菜单了解函数功能工作表表的复制与移动点击右键弹出菜单 选择本工作簿， 则可以在本工簿中 复制一个本工作表 是拷贝还是移动 ，如果是拷贝，打 勾 选择新工作簿， 则可以将本工作表 生成一个新的工作 簿图表生成功能图表功能是EXCEL的重要功能， 必须掌握，教程均涉及，建议按教 程自学，本处不再详述。 图表多练习设置如何设置属性 如果数据能动态更新或计算，图 表也能成为动态图表打印设置页面设置的方法： 选取“文件”页面设置 ，进入页面设置界面； 顶端表头设置 整页打印设

5、置 显示页设置$ 符号 绝对引用和相对引用批量输入公式，在一个单元格的公式录入完成后，拖动单元格可以 快速批量输入公式，应掌握引用单元格的变化规律，观察是绝对引用 还是相对引用。一般只研究1个单元格中的公式即可。在公式中： A3 - 行、列均可随着单元格的拖动相对变化 $A3 - 钉住列，行随着单元格的拖动相对变化 A$3 - 钉住行，列随着单元格的拖动相对变化 $A$3 - 行、列均钉住，单元格拖动不发生变化快捷转换键：鼠标放在单元格F4 举例常用功能键 F2 进入单元格编辑状态，ESC退出- 减少使用鼠标的频次，提高编辑效率 F4 相对引用与绝对引用变换- 快速设置相对引用、绝对引用、混合

6、引用 F5 定位- 查找功能的扩展（举例：复制分类汇总结果） F9 公式调试配合公式的帮助，解读公式功能和结果（学习EXCEL掌握）3. EXCEL常用函数与技巧对明细表进行汇总计数、求和函数一般（主要用于明细表）计数：COUNT(C2:C100) 对数值计数 COUNTA 筛选后的序号求和：SUM(C2:C100) 筛选（主要用于明细表）计数：SUBTOTAL(9,C2:C100 ) 求和：SUBTOTAL(3, C2:C100 ) 条件（主要用于统计表）计数: COUNTIF(C2:C100,“22) 求和： SUMPRODUCT(C2:C10022),C2:C100) 举例扩展：多条件匹

7、配求和的应用SUMPRODUCT(A3:A10=“条件1”)*(C2:I2=“条件2”)*(B3:B10=条件3)*C3:I9)在一个表的特定区域中查找引用同时满足多个条件数据之和。 如果满足条件的必须为唯一值，可用于查找标准。 如果表中符合条件的数不止一个，则会返回它们的和。 实例 逻辑判断函数IF(判断条件，条件成立的结果，条件不成立的结果) 文本连接符“文本1” Ctrl+shift+; （ 静态日期） 取日期的年、月、日取年：year() 取月：month() 取日：day() 组合日期：DATE(YEAR(A1),MONTH(A1),DAY(A1) 年龄计算方法、用年、月、日计算、用

8、出生日期到当前日期的天数差折算实例日期等常用函数高级筛选 数据筛选：点击“数据”“筛选” “自动筛选” 打勾 取消筛选：点击“数据”“筛选” “自动筛选” 去勾 全部显示：点击“数据”“筛选” “全部显示” 高级筛选：点击“数据”“筛选” “高级筛选” 高级筛选的用处：筛选条件可以多个，条件可以事先写好，筛选的条 例更加清晰，避免出错。 利用“高级筛选”功能可以在一个明细表中提取不重复的值 举例条件格式 条件格式用于数据表的辅助提示功能。 选定要操作的单元格，点击“格式” “条件格式”，在“设置” 界面进行设定条件格式。注意：条件格式如果设定公式，也存在绝对引用和相对引用的关系（格式刷刷 格式

9、，即会产生相对引用） 已经设定条件格式的单元格拖拽，条件格式会自动复制到拖拽的单元格中 EXCEL2003版条件格式最多可以设置3个，第一个权限最高。 实例继续添加条件 删除已添加的条件工作表的保护当进行“保护工作表”时所发生的结果选定允许编辑的单元格录入时，指定单元格可 以填入，其他不能更改 EXCEL工作表保护功能 只能做一般性保护，设置 了一道屏障，但仍比较容 易破解，重要信息不能依 靠此方式数据有效性 数据有效性的使用主要在于约束数 据规范格式，对后续的方便统计工作 会很有帮助。 选定要操作的单元格，点击“数据 ” ”有效性“，在“设置” 界 面“允许”框内选取对数据的约束类 型。 选

10、择“序列”，设置填写的序列的 内容，填写时可有下拉菜单。序列的 内容以逗号（小写）分割，见右图。 实例小写逗号默认已选择数组公式 数组公式对一组或多组值执行多重计算，并返回一个或多 个结果。 可以是一个或多个数值，或是一组或多组数值，并在编辑 栏可以看到以“”括起来的公式 按Ctrl+Shift+Enter键录入公式选择单元格 快捷键 Ctrl+F 不仅可以选择数值、 公式内容，而且可以选 择格式 选择格式完成后一定 要“清除查找格式”MACH、INDEX函数的使用多条件引用文本型数据范例多条件引用文本型数据范例MATCH(value,array,type) 返回在指定方式下与指定数值匹配的数

11、组中元素的相应位置 value 为需要在 array 中查找的数值。 array 可能包含所要查找的数值的连续单元格区域。 type 为数字 -1、0 或 1。1:查找小于或等于value 的最大数值。0:查找等于value 的 第一个数值。-1:查找大于或等于value 的最小数值。如果省略type，则假设为 1。INDEX(reference,row_num,column_num,area_num) 返回(多个或单个)指定数据区域中的某行某列的值 reference指的是要检索的范围； row_num指的是指定返回的行序号 column_num指的是指定返回的列序号（多列区域时，单列可以取

12、消） area_num指的是返回该区域中行和列的交叉域。可省略，默认1。OFFSET函数OFFSET(参数1,参数2,参数3,参数4height,参数5width) 返回参数1单元格（左上角）往下数参数2行往右数参数3列 的单元格的值。 按偏移后单元格为起点选定下参数4 height行，右参数5 width列的一个区域）如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。 如果省略 height 或 width，则假设其高度或宽度与 参数参数11相同。 函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个 引用。 函数 OFFSET 可用于任何

13、需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。数据条件匹配引用 VLOOKUP(C8,表A1:A100,3,0)两张表有相同元素的列，为将一张表的数据关联到另一张表中，常用 此函数。一般先实现一个单元格的内容引用，通过拖拽的方式实现批量引用。 拖拽时注意绝对引用和相对引用。批量匹配 实例填写填写0 0（或者（或者FALSEFALSE）填写被查找表自起点列后的第填写被查找表自起点列后的第N N列列被查找表的区域，第一列必须包含查被查找表的区域，第一列必须包含查 找的关键字找的关键

14、字本表中的关键字单元格本表中的关键字单元格0为精确匹配查找，1为模糊 匹配，一般不用1选择性粘贴 在固化数据表时，使用选择性粘贴- 数 值 ； 在整理数据时，有时会用到运算型的选 择性粘贴； 在整理数据时有时候会用到选择性粘贴 转置； 举例EXCEL表内容图片化复制 按住Shift键后，点击菜单栏的“编辑”，下拉菜单中会有“复制图片 ” ，可以将选中的内容复制成图片，粘贴到其他地方。不按Shift键显示按Shift键显示 制作幻灯片时常用。 一般用文档编写时， 保护原始文件用。 EXCEL列数过多时， 图片复制粘贴更方便。 举例数据透视表 实现快速统计 基础是内容规范的明细表 因为此功能使对明

15、细表的统计效率较高，建 议EXCEL统计工作尽量从明细表做起，利用此 方法生成统计结果。 如果有相对稳定格式的明细表，可以利用公 式实现统计表。实例其他常用功能与技巧（演示） 长数字输入：输入前面加“ ” （比如输入身份证号） 快速调整行宽、列宽：整行整列选中调整 EXCEL文档保存加密码：保存界面工具-安全选项 拖拽单元格，自动添加序列：右下角自动填充选项 单元格内的文本强制换行：Alt+回车 单元格显示公式：Ctrl+ 排序: 筛选状态下升序、降序，排序界面选项（可以扩充排序功能） 跨栏居中：右键设置单元格格式-对齐-水平居中 下 大纲模式：Shift+Alt+左方向键，取消大纲：Shift+Alt+右方向键4. EXCEL解决问题 实例操作EXCEL解决问题实例（正向应用） 1、现有一个EXCEL名单，多于实际人员数，初步判断 存在重复记录，请快速确定哪条记录重复？2、一个单位现两个名单人数对不上，请找出对不上的原 因何在？3、现有工时统计标准见附表1，上级要求每月上报工时 统计报表见附表2，请设计一个统计方案，高效实现统计 工作。使用数据透视表使用数据透视表利用公