Excel高级应用技巧

1、Excel培训 高级篇，杰出效率 版权所属版权所属 1 Excel Excel 高级使用技巧高级使用技巧 Excel Excel 函数使用技巧函数使用技巧 Excel Excel 数据分析技巧数据分析技巧 Excel Excel 图表图表技巧技巧 2 Excel Excel 高级使用技巧高级使用技巧 构思和设置表结构构思和设置表结构 建立和输入数据建立和输入数据 编辑和修饰表格编辑和修饰表格 简单使用和分析简单使用和分析 保存表格数据保存表格数据 打印输出数据打印输出数据 增加增加 3 Excel 高级使用技巧高级使用技巧 -构思及表结构设置构思及表结构设置 4 构思及表结构设置构思及表结构设

2、置 表结构设置指北针表结构设置指北针 l凡须独立管理的项目，必须单独设置项目名称，独占一列，避免混合设置 l管理项目的独立性必须与扩展性结合考虑，以方便统计分析 l表格结构设计必须考虑后期统计分析的应用状态。 控制控制原始数据输入准确性的解决方法原始数据输入准确性的解决方法 l用调用法控制原始数据的准确性 l用计算法控制加数据的准确性 l使用校对工具 5 其他技巧其他技巧- -录入技巧下拉菜单录入技巧下拉菜单 一、直接输入： 1.选择要设置的单元格，譬如A1单元格； 2.选择菜单栏的“数据”“有效性”出现“数据有效性”弹出窗口； 3.在“设置”选项中“有效性条件”“允许”中选择“序列”右边的

3、“忽略空值”和“提供下拉菜单”全部打勾在“来源”下面输入数据，譬如 “1,2,3,4,5,6,7,8,9”(不包括双引号，分割符号“,”必须为半角模式)按“确定” 就OK了，再次选择该A1单元格，就出现了下拉菜单。 6 其他技巧其他技巧- -录入技巧下拉菜单录入技巧下拉菜单 如果同一工作表的某列就是下拉菜单想要的数据，譬如引用工作表Sheet1的B2： B5，B2：B5分别有以下数据：1、2、3、4，操作如下： 1.选择要设置的单元格，譬如A1单元格； 2.选择菜单栏的“数据”“有效性”出现“数据有效性”弹出窗口； 3.在“设置”选项中“有效性条件”“允许”中选择“序列”右边的“忽 略空值”和

4、“提供下拉菜单”全部打勾在“来源”下面输入数据 “=$B$2:$B$5”，也可以按右边带红色箭头的直接选择B2：B5区域按“确定” 就OK了，再次选择该A1单元格，就出现了下拉菜单。 7 其他技巧其他技巧- -定义工作表名称定义工作表名称 1.CTRL+F3 2. =OFFSET =OFFSET( (费用明细费用明细!$!$A$1,COUNTAA$1,COUNTA( (费用明细费用明细!$!$A:$A),COUNTAA:$A),COUNTA( (费用明费用明 细细!$!$1:$1)1:$1) 8 其他其他高级高级技巧技巧 功能命令 选择整个单元格范围Ctrl+Shift+ * 快速关闭多个文件

5、按住Shift键，打开“文件”菜单，单击“全部关闭” 选定多个工作表 一组相邻的工作表，可先选第一个表，按住Shift键，再单击最后一个表的 标签；选不相邻的工作表，要按住Ctrl键 对多个工作表快速编辑 选定多个工作表，可在相同位置设置 1.页面设置 2.输入相同的数据 3.快 速设置 4.输入公式 快速浏览长工作簿 按下“Ctri+Home”键可以回到当前工作表的左上角(即A1单元格)，按下 “Ctrl+End”键可以跳到工作表含有数据部分的右下角。 同一个单元格换行Alt+回车键 巧变文本为数字 在空白的单元格中填人数字1，然后选中这个单元格，执行“复制”命令， 然后再选中所要转换的范围

6、，选择“选择性粘贴”中的“乘” 在单元格中输入0值 设置单元格格式”，在接着出现的界面中选“数字”标签页，在列表框中 选择“文本” 分散对齐 在Excel表格中输入人名时为了美观，我们一般要在两个字的人名中间空 出一个字的间距,选中该列，点击“格式单元格对齐”，在“水平对齐” 中选择“分散对齐 文件加密另存新档/工具/一般选项 部分加密 选取整篇文档/右键储存格格式/保护/解锁/选区需要加密区域/右键储存格格 式/保护/加锁/菜单工具/保护文档9 Excel函数使用技巧函数使用技巧 Excel数学函数数学函数 Excel逻辑函数逻辑函数 Excel文本函数文本函数 Excel日期时间函数日期时

7、间函数 Excel查询与引用查询与引用 10 函数类函数函数名称常用否 Excel的 数学函数 ABS 返回参数的绝对值N COUNTIF 计算给定区域内满足特定条件的单元格的数 目 Y INT 返回实数舍入后的整数值N ROUND 返回某个数字按指定位数舍入后的数字Y SUBTOTAL 返回数据清单或数据库中的分类汇总N SUM 返回某一单元格区域中所有数字之和N SUMIF 根据指定条件对若干单元格求和Y Excel 函数使用技巧函数使用技巧 -数学函数数学函数 函数函数函数名称显示常用 Excel逻 辑函数 COUNTIF根据指定条件对若干单元格次数 COUNTIF(B4:B1 0,90

8、) Y AND 其中Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可能为 TRUE，可能为 FALSE。 参数必须是逻 辑值，或者包含逻辑值的数组或引用 AND(logical1,log ical2, .)， Y OR OR函数指在其参数组中，任何一个参 数逻辑值为 TRUE，即返回 TRUE OR(B1:B3)Y IF IF函数用于执行真假值判断后，根据逻 辑测试的真假值返回不同的结果，因此 If函数也称之为条件函数。它的应用很 广泛，可以使用函数 IF 对数值和公式 进行条件检测。 IF(logical_test,val ue_if_true,v

9、alue_i f_false)。 Y Excel 函数使用技巧函数使用技巧 -逻辑逻辑函数函数 函数类函数函数名称显示常用 Excel 函数引 用之文 本函数 CONCA TENATE 将若干文字串合并到一个文字串中。 CONCATENATE (text1,text2,.) Y DOLLA R 依照货币格式将小数四舍五入到指定的位数并转换成文字。 D O L L A R 或 RMB(number,deci mals) EXACT 该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格 式上的差异。利用函数 EXAC

10、T 可以测试输入文档内的文字。 EXACT(text1,text 2) LEFTLEFT 基于所指定的字符数返回文本串中的第一个或前几个字符。 LEFT(text,num_ch ars) Y MID MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户 指定。 MID(text,start_nu m,num_chars) Y RIGHTRIGHT 根据所指定的字符数返回文本串中最后一个或多个字符。 RIGHT(text,num_c hars) Y TRIM 除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用 程序中获取带有不规则空格的文本时，可以使用函数 TRIM。 TR

11、IM(text)Y VALUE将代表数字的文字串转换成数字。VALUE(text) Excel 函数使用技巧函数使用技巧 -引用及文本引用及文本函数函数 函数类函数函数名称显示 常 用 否 Excel时 间函数 MONTH 返回以系列数表示的日期中的月份。 月份是介于 1（一月）和 12（十二 月）之间的整数。 MONTH(serial_n umber) Y TODAY 返回当前日期的系列数，系列数是 Microsoft Excel 用于日期和时间计 算的日期-时间代码。 TODAY( ) YEAR 返回某日期的年份。返回值为 1900 到 9999 之间的整数。 YEAR(serial_nu

12、 mber) Y Excel 函数使用技巧函数使用技巧 -时间时间函数函数 函数类函数函数名称显示 常 用 否 Excel查询 与引用函 数 VLOOKUP 用于在表格或数值数组的首列查找指定的数 值，并由此返回表格或数组当前行中指定列 处的数值。 VLOOKUP(lookup_value,ta ble_array,col_index_num,ra nge_lookup) Y HLOOKUP 用于在表格或数值数组的首行查找指定的数 值，并由此返回表格或数组当前列中指定行 处的数值。 HLOOKUP(lookup_value,ta ble_array,row_index_num,ra nge_l

13、ookup) Y TRANSPOSE TRANSPOSE用于返回区域的转置。函数 TRANSPOSE 必须在某个区域中以数组公式 的形式输入，该区域的行数和列数分别与 array 的列数和行数相同。使用函数 TRANSPOSE 可以改变工作表或宏表中数组 的垂直或水平走向。由于此处是以数组公式 输入，因此需要按 CRTL+SHIFT+ENTER 组合键来确定为数组公式，此时会在公式中 显示。随即转置成功 TRANSPOSE(array) Excel 函数使用技巧函数使用技巧 -查询及引用查询及引用函数函数 Excel 函数使用技巧函数使用技巧 -函数案例讲解函数案例讲解1 案例详见插入案例详见

14、插入EXCEL文档文档 函数类函数应用常用否 语法格式 隐藏 函数 DATEDIF返回两个日期之间的年月日YDATEDIF(起始日期,终止日期,Y/M/D) NUMBERS TRING 返回数字的大小写NNUMBERSTRING(数字,1/2) DATESTRI NG 返回日期的中文NDATESTRING(日期) 数学 函数 ABS 返回参数的绝对值NABA(数字) COUNTIF 计算给定区域内满足特定条件的单元格 的数目 YCOUNTIF(选择列,筛选规则) ROUND 返回某个数字按指定位数舍入后的数字 YROUND(数字,保留位数) SUMIF 根据指定条件对若干单元格求和YSUMIF

15、(选择列,筛选规则,汇总计算列) 逻辑引 用 AND同时设定条件，需满足其中所有条件YAND(logical1,logical2, .)， OR 同时设定条件，只满足其中一项条件即 可 Y IF条件函数Y IF(条件,符合条件返回值,不符合条件返回 值)。 VLOOKUP 用于在表格或数值数组的首列查找指定 的数值，并由此返回表格或数组当前行 中指定列处的数值。 Y VLOOKUP(参照列,选择区域,返回第几列 的值，0/FALSE) 16 Excel 函数使用技巧函数使用技巧 -函数案例讲解函数案例讲解2案例详见插入案例详见插入EXCEL文档文档 函数类函数应用常用否 语法格式 逻辑 引用

16、HLOOKUP 用于在表格或数值数组的首行查找指定的数 值，并由此返回表格或数组当前列中指定行 处的数值。 Y HLOOKUP(参照行,选择区域,返回第几行的值， 1/FALSE) RANK 返回某一数值在一列数值中的相对于其他数 值的排位 Y RANK（Number,ref,order）(ORDER0升序， order1降序） 文本 函数 CONCATEN ATE 将若干文字串合并到一个文字串中。NCONCATENATE (text1,text2,.) LEFT LEFT 基于所指定的字符数返回文本串中的 第一个或前几个字符。 YLEFT(text,num_chars) MID MID 返回

17、文本串中从指定位置开始的特定数 目的字符，该数目由用户指定。 YMID(text,start_num,num_chars) RIGHT RIGHT 根据所指定的字符数返回文本串中最 后一个或多个字符。 YRIGHT(text,num_chars) TRIM 除了单词之间的单个空格外，清除文本中所 有的空格。在从其他应用程序中获取带有不 规则空格的文本时，可以使用函数 TRIM。 YTRIM(text) 时间 函数 MONTH 返回以系列数表示的日期中的月份。月份是 介于 1（一月）和 12（十二月）之间的整数。 YMONTH(serial_number) TODAY 返回当前日期的系列数，系列

18、数是 Microsoft Excel 用于日期和时间计算的日期-时间代码。 TODAY( ) YEAR 返回某日期的年份。返回值为 1900 到 9999 之间的整数。 YYEAR(serial_number) 17 Excel 数据分析技巧数据分析技巧 Excel分级显示及分类汇总分级显示及分类汇总 Excel数据透析表数据透析表 18 Excel 数据分析技巧数据分析技巧 -分级显示及分类汇总分级显示及分类汇总 部门工号身份证号码 姓名 应纳税所得额 税率 速算 个人所得税 DSA00011AAA 10,000.00 0.20 325 1,675.00 DSA00012BBB 500.00

19、 0.05 0 25.00 DSA00013CCC 1,500.00 0.10 25 125.00 DSA00014DDD 3,800.00 0.15 125 445.00 DSA0 汇总汇总 15,800.00 475 2,270.00 GSA00015EEE 4,000.00 0.15 125 475.00 GSA00016FFF 800.00 0.10 25 55.00 GSA00017GGG 1,200.00 0.10 25 95.00 GSA00018HHH 650.00 0.10 25 40.00 GSA0 汇总汇总 6,650.00 200 665.00 总计总计 22,450.

20、00 675 2,935.00 19 数据透视表是Excel中最具技术性的复杂组件之一。 数据透视表的本质是将表格数据库变为动态数据汇总 报告。 数据透视表的优势： 人性化：方便的操作和界面 灵活性：可以方便的得到任何类别的数据 Excel 数据分析数据分析 -数据数据透析透析表表-使用使用数据透视表分析数据数据透视表分析数据 20 如何生成数据透视表 下面我们以此数据来进行数据透视表的分析。 21 为数据透视表选择合适的数据 虽然Excel可以对任何数据生成数据透视表，但并不是 所有的数据资料都可以从中受益。 一般而言，数据资料应为数据库格式，包含下面两种 类型的字段： 数据（包括数字或文本

21、） 类别：对数据的描述 数据的第一行要求为项目标题。 当数据列不包含数字时数据透视表默认为以计数的方 式使用数据；当数据列为数字时默认为使用求和方式。 22 如何生成数据透视表 Step 1:选择工具栏中的：数据数据透视表和数据透 视图。 23 如何生成数据透视表 Step 2: 一般情况按照默认的选项即可，单击下一步。 上部分选项选择数据来自本Excel文件，或其它数据类型，或 多个Excel数据文件。 24 如何生成数据透视表 Step 3: 弹出对话框，选择将要生成数据分析表的数据源。 当活动单元格的位置在数据区域时，透视表会自动选择数据区域。 25 如何生成数据透视表 Step 4:选

22、择新建工作表，使数据透视表生成在一张新的工作表中， 点击“完成”。 选择“布局”和“选项”可以设置一些数据透视表的模式， 但不是必需的，所以如果是新手入门就当作没有这些键好了， 但是我们也分别解释一下。 26 在“布局”对话框里，可以将透视表的各字段安排在不同的位置。 但是这个步骤也可以在数据透视表建立后进行。 27 选项 数据透视表提供了丰富的“选项”。 列总计：以列汇总； 行总计：以行汇总； 打开时刷新：打开文件时自动进行数据刷新； 显示明细数据：可以显示每个数字对应的数据库中的数据； 28 如何生成数据透视表 Step 5: 生成新的一页工作表，并会弹出包含所有项目标题的对 话框。 29

23、 如何生成数据透视表 Step 6:将项目标题拖到左侧表格的对应位置，一张数据透视表就完 成了。 30 如何读取数据透视表 项目选项：选择不同组项目选项：选择不同组 合下的数据。合下的数据。 点击右键，对交叉表进点击右键，对交叉表进 行字段分析，如求和，行字段分析，如求和， 计数，最大值等计数，最大值等 31 数据透视表的组成部分 1.列字段。如除主详 细特征； 2.行字段。如time； 3.数据区域。中间的 数字； 4.总数求和。如合计； 5.元素。如个人； 6.条件。如city; 7.字段列表。 32 移动 在数据透视表中无法进行普通的操作，例如插入或删除一行或一列，也 不能对数字进行硬性

24、修改。 如何移动数据透视表中的项目？ 将鼠标移到想要移动的项目，点右键，选择“顺序”，然后选择想要移动方 向即可。 注意只能移动项目，而不能仅仅移动数据。 33 手工组合 组合项目是数据透视表的一个有用特性。 如何将选项进行组合？ 选择要组合的元素，点击右键，选择“组合显示明细数据” “组 合”，出现新的项目名称“city2”，将生成的“数据组1”改为“重点 城市”。 这样就可以得到分“重点城市”和“其他城市”的数据。 34 手工组合 35 自动组合 当被组合项为数字时，可以进行自动组合。 例如日期可以自动选择按照月、季度、年等组合； 收入，年龄等都可以按照自己指定的区间段进行组合； 当进行手

25、工组合时会生成新的元素，如对“收入”进行组合会生 成“收入2”的元素；在进行自动组合时没有新的元素生成，组合 数据替代了详细数据；因此建议在原数据库中复制一列数据。 36 插入计算字段 数据透视表有自我进行计算的功能，这种功能叫做“插入计算字 段”。 例如在数据透视表中有销售额和销量的数据，则可以通过计 算来得到单价的数据。 步骤：“数据透视表工具栏”公式”计算字段” 在“名称”写入新建数据的名称； 在“公式”内键入计算公式； 确定。 37 数据透视表的其他 阅读详细数据 对于数据透视表产生的数据可以查看详细的数据产生来源，具体的方式 是双击此数。 例如数据透视表计算出库中有3个样本符合某条件

26、，那么通过双击此数可 以得到具体是哪3个样本符合这个条件，并在新的工作表中显示。 在双击前请保证“表格选项”中的“显示明细数据”已被选择。 设置报告格式 如果需要直接把数据透视表作为报告提交，那么一个正规的格式是必须 的，请在“数据透视表工作栏”中选择“设置报告格式”，选择合适的 个是显示数据透视表 选择显示元素 点击数据透视表中的任意列字段和行字段的右下方的小三角，弹出这个 字段的所有元素，可以把你不需要的元素进行隐藏。 例如您只对2005年的数据感兴趣，则点击数据透视表中年份的一栏，将 其他年份去掉，则表中仅剩下2005年的数据。 38 数据透视图 除制作数据透视表外，我们还可以制作数据透

27、视图. 数据透视图兼有数据透视表和图表的功能。既可以随意选择数 据组合，又可以随意改变图表类型。 39 应注意的方面 仅当活动单元格处在数据透视表中的位置时，才能激活数据透视 表。 数据透视表有一个小缺点，当用户更改数据源时，他不能自动更 新。但只需单击工具栏中“刷新”按钮或右键的“更新数据”， 强制数据透视表使用最新的数据。当然你也可以在表格选项中选 择每隔固定时间刷新数据。 若在操作中将“字段列表”关掉，有两种方法可以再显示： 点右键选择“数据透视表向导” 在“数据透视表工作栏”种选择“显示字段列表” 40 Excel 图表图表技巧技巧 高级图表应用高级图表应用 41 Excel 图表图表

28、技巧技巧-高级图表应用双轴曲线高级图表应用双轴曲线 - 2 4 6 8 10 12 14 16 18 0 2000 4000 6000 8000 10000 12000 14000 16000 18000 20000 Jan-07Feb-07Mar-07Apr-07May-07Jun-07 加班費用 10000 產量（箱） 月份 加班費Trend A产品产量B产品产量加班费 42 Excel 图表图表技巧技巧-高级图表应用高级图表应用-断层图断层图 43 Excel 图表图表技巧技巧-高级图表应用高级图表应用-双色柱形图双色柱形图 0 1 2 3 4 5 6 7 8 9 华东地区华南地区华中地

29、区东北地区西北地区西南地区 各区域销售情况表各区域销售情况表 低于平均数 4高于平均数 44 Excel 图表图表技巧技巧-高级图表应用高级图表应用-瀑布图瀑布图 0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000 营业收入营业成本销售费用管理费用财务费用投资收益营业利润营业外收入营业外支出利润总额所得税净利润 45 Excel 图表图表技巧技巧-高级图表应用高级图表应用-绘图区分割绘图区分割 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% 1月2月3月4月5月6月7月8月9月10月11月12月 46 Excel 图表图表技

30、巧技巧-高级图表应用高级图表应用-添加水平线添加水平线 0 20 40 60 80 100 120 ABCDEFGHIJ 47 Excel 图表图表技巧技巧-高级图表应用高级图表应用-标注最大小值标注最大小值 0 10 20 30 40 50 60 70 80 90 1月2月3月4月5月6月7月8月9月 48 Excel 图表图表技巧技巧-高级图表应用高级图表应用-涨跌图涨跌图 15 16 17 18 19 20 21 5/8 5/9 5/10 5/11 5/12 5/13 5/14 5/15 5/16 5/17 5/18 5/19 5/20 5/21 5/22 5/23 5/24 5/25 5/26 5/27 5/28 5/29 5/30 5/31 上涨下跌平盘 49 Excel 图表图表技巧技巧-高级图表应用高级图表应用-步进图步进图 0 10 20 30 40 50 60 70 80 90 100 024681012 销量步进图销量步进图 50 Excel 图表图表技巧技巧-高级图表应用高级图表应用-甘特图甘特图 2011/5/18 5/85/115/145/175/205/235/265/296/16/46/7 项目确定 问卷设计 试访 问卷确定 实地执行 数据录入 数据分析 报告提交 51 Exce