Excel常用函数及数据透视表的应用
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
从字符串”This is an apple” 分别取出字符“This”， “apple”，“is”
18
05-文本函数TRIM
清洗函数：TRIM
/ 应用情景 / 在处理某系统中导出的数据时，你是否因为数据首尾存在空格、 又或者存在换行符，而心生烦恼？而手动清除这些特殊格式真 的是太麻烦了！TRIM函数可以很轻松的将单元格中数据首尾的 空格去除；结合CLEAN函数，还可以消除换行符。 函数语法： =TRIM(text) 函数功能： 1、 可以去除引用目标的首尾空格，但对存在于字符间的空格无效； （注：要去除字符之间的空格可用SUBSTITUTE函数） 2、使用该函数后，数据类型会变为“文本”。
影响：合并单元格除第1个格外，其他均作为空值处理。 整理：取消合并单元格，填充完整。
源表的整理
• 4、存在非法日期。
影响：生成的数据透视表中，无法按日期格式进行年、月、日格式的 筛选和组合。
整理：转换成excel认可的日期格式。方法：选取列 - 分列 - 第3步选日 期
15
03-排位函数RANK
案例1：对成绩进行排序（从大到小，降序）。 因为要下拉进行公式的复制，所以要添加绝对引用。输入公式 =RANK(D2,$D$2:$D$8,0)。
16
源自文库
03-排位函数RANK
案例2：rank函数对不连续单元格排名：不连续的单元格，第二个参数 需要用括号和逗号形式连接起来。 输入公式=RANK(B5,(B5,B9,B13,B17),0)
•
什么时候需要用数据透视表
• 6、当需要数据报表中的某个数字进一步的分析，将其拆分为更详 细的数据记录时。
• 7、当原始数据经常更新，而统计报表也需要同步更新时。
• 8、当查看数据报表时，需要多组条件筛选器，要求根据不同的筛 选条件动态更新表格中的数据时。 • 9、当需要把数据按照某个维度拆分为多个分表，放置到多个工作 表中展示时 。 • 10、当需要统计同比、环比增长额和百分比时。
源表的整理
• 制作成数据透视表之前，需要看看明细表是否存在下面的问题： 1、存在空列或没有行标题字段。
影响：明细表如果有标题为空，将无法制作数据透视表。 整理: 把标题补充完整。
源表的整理
• 2、存在相同的标题。
影响：数据透视表会自动添加序号以区分 整理：尽量不要存在相同的列标题。
源表的整理
• 3、存在合并单元格
3
重要概念：绝对引用与相对引用
绝对引用： 还是这个表格，在C6单元格中输入“=A1”后按 F4,会出现绝对 引用的符号“=$A$1”，无论向下向右拖动填充单元格，填充的 内容都与A1单元格相同，这就是绝对引用。
4
重要概念：绝对引用与相对引用
混合引用（绝对引用与相对引用结合使用）： 绝对引用中$相当于一把锁，我们都知道单元格地址是由行和列 组成的，在绝对引用中，当在C6单元格中输入“=A1”按F4后， 会变成=$A$1,即锁定行与列，如果我们只需要绝对引用行或者 绝对引用列的时候，我们需要怎么办呢？很简单，只需要再次 按F4，就会出现=A$1或者=$A1。 应用技巧： 神奇的F4
9
02-求和有关的函数的应用
2、SUMIF函数 SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、 文本或表达式。
常规用法：=SUMIF(条件区域,指定的条件,求和区域）
要计算某企业某贷款余额情况。公式输入为 =SUMIF（A2:A16,企业代码,D2:D16) 其中“A2:A16”为提供逻辑判断依据的单元格区域，“企业代码”为 判断条件即只统计A2:A16区域中编号为该“企业代码”的单元格， D2:D16为实际求和的单元格区域。
02-求和有关的函数的应用
Sumif：3：不同列求和。 举例：计算出“地区”总的销售额
方法：在目标单元格中输入公式：=SUMIF(D:L,O3,C:K)。 解读：从公式中我们可以看出，使用的函数为单条件求和 Sumif函数。 13
02-求和有关的函数的应用
2、SUMIF函数
提示：
1、指定条件时，可以使用通配符。 2、求和区域和条件区域要大小一致，并且要注意两者的起始位置需保持 一致。
01-求和有关的函数的应用
1、SUM函数 1）行或列求和：=SUM(H3:H12) 2）区域求和：=SUM(D3:D12,F3:F12) 注意： 1、SUM函数中的参数，即被求和的单元格或单元格区域不能超过 30个。否则Excel就会提示参数太多。 2、对需要参与求和的某个常数，可用“=SUM（单元格区域，常 数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。
创建数据透视表
制作数据透视表 原材料：以一张销售明细表为例
创建数据透视表
• 第1步 选取明细表，鼠标点在明细表内任何地方，以Excel2010版为例： 插入选项卡 - 数据透视表
创建数据透视表
• 第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。在 向导的第一步一般是默认选项，不需要设置，直接点确定即可。
19
05-文本函数TRIM
/ 函数应用举例 / 1、仅消除数据首尾的空白 C4 =TRIM（B4） 原始数据及修正（删除空格）后数据展示：
20
05-文本函数TRIM
/ 函数应用举例 / 2、删除空格的同时删除换行符。这个操作的完成光靠TRIM函数是 不行的，需要CLEAN函数的助攻； 实现该功能的公式如下： =CLEAN(TRIM(text)) 原始数据及修正（删除空格）后数据展示：
2）合并写成2000year =CONCATENATE(A2,B2)
22
07-查找和引用函数VLOOKUP
大众情人Vlookup函数语法： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数 lookup_value table_array col_index_num range_lookup 简单说明 要查找的值 要查找的区域 返回数据在查找区域 的第几列数 模糊匹配/精确匹配 输入数据类型 数值、引用或文本字 符串 数据表区域 正整数 FALSE/TRUE（0/1）
创建数据透视表
• 第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一 个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体 的房间位置。下面是添加数据透视表项目 把地区拖动到行标签 把销售量和销售金额拖动到数值区域。
创建数据透视表
• 拖动完成后，一个数据透视表的雏形展现在我们的面前，如下图所示。
Excel常用函数 及透视表的应用
EXCEL几个常用函数
重要概念：绝对引用与相对引用 求和有关的函数的应用 排位函数RANK 文本函数（取出字符串中的部分字符） 文本函数TRIM 文本函数(CONCATENATE)及连缀字符“&” 查找和引用函数VLOOKUP
2
重要概念：绝对引用与相对引用
Excel公式中一个很重要的概念就是绝对引用与相对引用 下面通过几幅图来讲解一下 相对引用： 如图，在C6单元格输入“=A1”，引用了A1单元格的内容；向下向右 拖动填充，随着单元格的变化，拖动填充的单元格内容也会变 化，这就是相对引用。
21
06-文本函数CONCATENATE及连缀字符 “&”
连接函数：CONCATENATE(text1,text2 · · · · · ·) 将若干个字符合并至一个字符项中 1）2001,12,21合并写成 2001年12月21日 =CONCATENATE(B3,"年",C3,"月 ",D3,"日")
5
重要概念：绝对引用与相对引用
【混合引用】相对引用A列，绝对引用第1行:=A$1
6
重要概念：绝对引用与相对引用
【混合引用】绝对引用A列，相对引用第1行:=$A1
7
01-求和有关的函数的应用
1、Alt+=：快速批量求和（一键求和）
8
方法： 1、选中数据源。 2、快捷键：Alt+=。其实Alt+=就是Sum函数的快捷键而已。
简单来说： VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
23
07-查找和引用函数VLOOKUP
应用实例
方法：在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。 解读： 1、查找值为H3单元格的值，查找范围为B3:D9，返回B3:D9范 围中第3列的值，即D列的值，匹配模式为精准匹配。 24
14
03-排位函数RANK
3、RANK(number,ref,[order]) Number：表示需要排位的数值 ref：可以是数组或也可以是单元格的引用，表示排名次的范围。 Order：其值是可选的，即可以填也可以为空。 如果order为0或省略,Excel 对数字的排名是基于ref降序排列而来。 如果order为1，Excel 对数字的排位是基于ref 升序排列而来。 简单来说： RANK(用来排序的数值，整个数值序列，0(降序)/1(升序))
17
04-文本函数（取出字符串中的部分字符）
取字符串函数
MID：=MID(text,start_num,num_chars) text是包含要提取字符的文本串，start_num是文本中要提取的 第一个字符的位置， num_chars指定要提取的字符数 LEFT：=LEFT(text,num_chars) text是包含要提取字符的文本串，num_chars指定要由LEFT所 提取的字符数 RIGHT：=RIGHT(text,num_chars) text是包含要提取字符的文本串，num_chars指定希望要 RIGHT提取的字符数
10
02-求和有关的函数的应用
Sumif：1、单条件求和。举例：计算男、女生的成绩之和。
方法：在目标单元格中输入公式：=SUMIF(C3:C9,N3,K3:K9)。 解读：单条件求和函数为Sumif，其语法结构为：=Sumif（条件范围， 求和条件，求和范围）。当条件范围和求和范围相同时，求和范围 11 课省略。
数据透视表的优势
• • 1、计算速度快，计算上万条记录也仅需1秒搞定。 2、交互式动态演示，集数据透视表和数据透视图浑然一体，动态 图表展示。
•
• • •
3、计算维度丰富，求和项、计数项、平均值、百分比、标准差等 全部支持。
4、报表布局变换灵活，仅需拖曳字段到对应位置即可同步更新报 表布局。 5、数据透视能力强，支持指定数据向下钻取。 6、最后一个重要的优势就是简单易学。
什么样的格式才能做出数据透视表
在制作数据透视表时，需要注意以下几点：
• 1 空字段名包括空列，无法制作数据透视表 • 2 相同的字段名，会自动添加序号，以示区别 • 3 字段所在行有合并单元格，等同于空字段，也无法创建 数据透视表 • 4 如果有空行，会当成空值处理 • 5 是否存在非法日期和文本型数值
数据透视表应用
什么是数据透视表
• • 在学习excel数据透视表之前，我们有必要了解它的概念。 什么是数据透视表呢？
通俗地讲：
数据透视表是一种可以把明细表分类汇总，而且可 以随意改变汇总模式的一种工具。
什么时候需要用数据透视表
• • • • 1、待统计数据条数很多，成千上万条记录，甚至几十万条记录 需要统计，这时用函数公式计算的速度缓慢，导致表格卡顿。 2、待统计的数据包含很多类别，需要分类统计，合并同类项等 操作时。 3、待统计的数据需要按照不同的维度整理成多种格式的报表， 还可能要根据领导的指示变换行列布局，增加新的统计维度。 4、原始数据中的字段分裂过于繁杂，需要把属性相近的一些数 据归类为大类别数据再进行统计和分析，以便用户更清洗直观的查看 数据趋势。 5、当数据不但要形成报表，还需要配套的动态图表辅助演示时。
02-求和有关的函数的应用
Sumif：2：隔列求和。举例：计算全年计划数与实际数
方法：在目标单元格中输入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。 解读：1、条件范围为D3:K3，而且不会变化，所以采用绝对引用的形式。 条件为“计划”和“实际”，要根据不同的情况进行变化，而“计划”和 “实际”在不同列，同一行，故列采用相对引用的形式，而行采用绝对 引用的形式。求和范围为数据区D4：K4，列的范围不会发生变化，而行 12 要变化，所以采用绝对和相对引用相结合的形式。
18
05-文本函数TRIM
清洗函数：TRIM
/ 应用情景 / 在处理某系统中导出的数据时，你是否因为数据首尾存在空格、 又或者存在换行符，而心生烦恼？而手动清除这些特殊格式真 的是太麻烦了！TRIM函数可以很轻松的将单元格中数据首尾的 空格去除；结合CLEAN函数，还可以消除换行符。 函数语法： =TRIM(text) 函数功能： 1、 可以去除引用目标的首尾空格，但对存在于字符间的空格无效； （注：要去除字符之间的空格可用SUBSTITUTE函数） 2、使用该函数后，数据类型会变为“文本”。
影响：合并单元格除第1个格外，其他均作为空值处理。 整理：取消合并单元格，填充完整。
源表的整理
• 4、存在非法日期。
影响：生成的数据透视表中，无法按日期格式进行年、月、日格式的 筛选和组合。
整理：转换成excel认可的日期格式。方法：选取列 - 分列 - 第3步选日 期
15
03-排位函数RANK
案例1：对成绩进行排序（从大到小，降序）。 因为要下拉进行公式的复制，所以要添加绝对引用。输入公式 =RANK(D2,$D$2:$D$8,0)。
16
源自文库
03-排位函数RANK
案例2：rank函数对不连续单元格排名：不连续的单元格，第二个参数 需要用括号和逗号形式连接起来。 输入公式=RANK(B5,(B5,B9,B13,B17),0)
•
什么时候需要用数据透视表
• 6、当需要数据报表中的某个数字进一步的分析，将其拆分为更详 细的数据记录时。
• 7、当原始数据经常更新，而统计报表也需要同步更新时。
• 8、当查看数据报表时，需要多组条件筛选器，要求根据不同的筛 选条件动态更新表格中的数据时。 • 9、当需要把数据按照某个维度拆分为多个分表，放置到多个工作 表中展示时 。 • 10、当需要统计同比、环比增长额和百分比时。
源表的整理
• 制作成数据透视表之前，需要看看明细表是否存在下面的问题： 1、存在空列或没有行标题字段。
影响：明细表如果有标题为空，将无法制作数据透视表。 整理: 把标题补充完整。
源表的整理
• 2、存在相同的标题。
影响：数据透视表会自动添加序号以区分 整理：尽量不要存在相同的列标题。
源表的整理
• 3、存在合并单元格
3
重要概念：绝对引用与相对引用
绝对引用： 还是这个表格，在C6单元格中输入“=A1”后按 F4,会出现绝对 引用的符号“=$A$1”，无论向下向右拖动填充单元格，填充的 内容都与A1单元格相同，这就是绝对引用。
4
重要概念：绝对引用与相对引用
混合引用（绝对引用与相对引用结合使用）： 绝对引用中$相当于一把锁，我们都知道单元格地址是由行和列 组成的，在绝对引用中，当在C6单元格中输入“=A1”按F4后， 会变成=$A$1,即锁定行与列，如果我们只需要绝对引用行或者 绝对引用列的时候，我们需要怎么办呢？很简单，只需要再次 按F4，就会出现=A$1或者=$A1。 应用技巧： 神奇的F4
9
02-求和有关的函数的应用
2、SUMIF函数 SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、 文本或表达式。
常规用法：=SUMIF(条件区域,指定的条件,求和区域）
要计算某企业某贷款余额情况。公式输入为 =SUMIF（A2:A16,企业代码,D2:D16) 其中“A2:A16”为提供逻辑判断依据的单元格区域，“企业代码”为 判断条件即只统计A2:A16区域中编号为该“企业代码”的单元格， D2:D16为实际求和的单元格区域。
02-求和有关的函数的应用
Sumif：3：不同列求和。 举例：计算出“地区”总的销售额
方法：在目标单元格中输入公式：=SUMIF(D:L,O3,C:K)。 解读：从公式中我们可以看出，使用的函数为单条件求和 Sumif函数。 13
02-求和有关的函数的应用
2、SUMIF函数
提示：
1、指定条件时，可以使用通配符。 2、求和区域和条件区域要大小一致，并且要注意两者的起始位置需保持 一致。
01-求和有关的函数的应用
1、SUM函数 1）行或列求和：=SUM(H3:H12) 2）区域求和：=SUM(D3:D12,F3:F12) 注意： 1、SUM函数中的参数，即被求和的单元格或单元格区域不能超过 30个。否则Excel就会提示参数太多。 2、对需要参与求和的某个常数，可用“=SUM（单元格区域，常 数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。
创建数据透视表
制作数据透视表 原材料：以一张销售明细表为例
创建数据透视表
• 第1步 选取明细表，鼠标点在明细表内任何地方，以Excel2010版为例： 插入选项卡 - 数据透视表
创建数据透视表
• 第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。在 向导的第一步一般是默认选项，不需要设置，直接点确定即可。
19
05-文本函数TRIM
/ 函数应用举例 / 1、仅消除数据首尾的空白 C4 =TRIM（B4） 原始数据及修正（删除空格）后数据展示：
20
05-文本函数TRIM
/ 函数应用举例 / 2、删除空格的同时删除换行符。这个操作的完成光靠TRIM函数是 不行的，需要CLEAN函数的助攻； 实现该功能的公式如下： =CLEAN(TRIM(text)) 原始数据及修正（删除空格）后数据展示：
2）合并写成2000year =CONCATENATE(A2,B2)
22
07-查找和引用函数VLOOKUP
大众情人Vlookup函数语法： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数 lookup_value table_array col_index_num range_lookup 简单说明 要查找的值 要查找的区域 返回数据在查找区域 的第几列数 模糊匹配/精确匹配 输入数据类型 数值、引用或文本字 符串 数据表区域 正整数 FALSE/TRUE（0/1）
创建数据透视表
• 第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一 个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体 的房间位置。下面是添加数据透视表项目 把地区拖动到行标签 把销售量和销售金额拖动到数值区域。
创建数据透视表
• 拖动完成后，一个数据透视表的雏形展现在我们的面前，如下图所示。
Excel常用函数 及透视表的应用
EXCEL几个常用函数
重要概念：绝对引用与相对引用 求和有关的函数的应用 排位函数RANK 文本函数（取出字符串中的部分字符） 文本函数TRIM 文本函数(CONCATENATE)及连缀字符“&” 查找和引用函数VLOOKUP
2
重要概念：绝对引用与相对引用
Excel公式中一个很重要的概念就是绝对引用与相对引用 下面通过几幅图来讲解一下 相对引用： 如图，在C6单元格输入“=A1”，引用了A1单元格的内容；向下向右 拖动填充，随着单元格的变化，拖动填充的单元格内容也会变 化，这就是相对引用。
21
06-文本函数CONCATENATE及连缀字符 “&”
连接函数：CONCATENATE(text1,text2 · · · · · ·) 将若干个字符合并至一个字符项中 1）2001,12,21合并写成 2001年12月21日 =CONCATENATE(B3,"年",C3,"月 ",D3,"日")
5
重要概念：绝对引用与相对引用
【混合引用】相对引用A列，绝对引用第1行:=A$1
6
重要概念：绝对引用与相对引用
【混合引用】绝对引用A列，相对引用第1行:=$A1
7
01-求和有关的函数的应用
1、Alt+=：快速批量求和（一键求和）
8
方法： 1、选中数据源。 2、快捷键：Alt+=。其实Alt+=就是Sum函数的快捷键而已。
简单来说： VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
23
07-查找和引用函数VLOOKUP
应用实例
方法：在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。 解读： 1、查找值为H3单元格的值，查找范围为B3:D9，返回B3:D9范 围中第3列的值，即D列的值，匹配模式为精准匹配。 24
14
03-排位函数RANK
3、RANK(number,ref,[order]) Number：表示需要排位的数值 ref：可以是数组或也可以是单元格的引用，表示排名次的范围。 Order：其值是可选的，即可以填也可以为空。 如果order为0或省略,Excel 对数字的排名是基于ref降序排列而来。 如果order为1，Excel 对数字的排位是基于ref 升序排列而来。 简单来说： RANK(用来排序的数值，整个数值序列，0(降序)/1(升序))
17
04-文本函数（取出字符串中的部分字符）
取字符串函数
MID：=MID(text,start_num,num_chars) text是包含要提取字符的文本串，start_num是文本中要提取的 第一个字符的位置， num_chars指定要提取的字符数 LEFT：=LEFT(text,num_chars) text是包含要提取字符的文本串，num_chars指定要由LEFT所 提取的字符数 RIGHT：=RIGHT(text,num_chars) text是包含要提取字符的文本串，num_chars指定希望要 RIGHT提取的字符数
10
02-求和有关的函数的应用
Sumif：1、单条件求和。举例：计算男、女生的成绩之和。
方法：在目标单元格中输入公式：=SUMIF(C3:C9,N3,K3:K9)。 解读：单条件求和函数为Sumif，其语法结构为：=Sumif（条件范围， 求和条件，求和范围）。当条件范围和求和范围相同时，求和范围 11 课省略。
数据透视表的优势
• • 1、计算速度快，计算上万条记录也仅需1秒搞定。 2、交互式动态演示，集数据透视表和数据透视图浑然一体，动态 图表展示。
•
• • •
3、计算维度丰富，求和项、计数项、平均值、百分比、标准差等 全部支持。
4、报表布局变换灵活，仅需拖曳字段到对应位置即可同步更新报 表布局。 5、数据透视能力强，支持指定数据向下钻取。 6、最后一个重要的优势就是简单易学。
什么样的格式才能做出数据透视表
在制作数据透视表时，需要注意以下几点：
• 1 空字段名包括空列，无法制作数据透视表 • 2 相同的字段名，会自动添加序号，以示区别 • 3 字段所在行有合并单元格，等同于空字段，也无法创建 数据透视表 • 4 如果有空行，会当成空值处理 • 5 是否存在非法日期和文本型数值
数据透视表应用
什么是数据透视表
• • 在学习excel数据透视表之前，我们有必要了解它的概念。 什么是数据透视表呢？
通俗地讲：
数据透视表是一种可以把明细表分类汇总，而且可 以随意改变汇总模式的一种工具。
什么时候需要用数据透视表
• • • • 1、待统计数据条数很多，成千上万条记录，甚至几十万条记录 需要统计，这时用函数公式计算的速度缓慢，导致表格卡顿。 2、待统计的数据包含很多类别，需要分类统计，合并同类项等 操作时。 3、待统计的数据需要按照不同的维度整理成多种格式的报表， 还可能要根据领导的指示变换行列布局，增加新的统计维度。 4、原始数据中的字段分裂过于繁杂，需要把属性相近的一些数 据归类为大类别数据再进行统计和分析，以便用户更清洗直观的查看 数据趋势。 5、当数据不但要形成报表，还需要配套的动态图表辅助演示时。
02-求和有关的函数的应用
Sumif：2：隔列求和。举例：计算全年计划数与实际数
方法：在目标单元格中输入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。 解读：1、条件范围为D3:K3，而且不会变化，所以采用绝对引用的形式。 条件为“计划”和“实际”，要根据不同的情况进行变化，而“计划”和 “实际”在不同列，同一行，故列采用相对引用的形式，而行采用绝对 引用的形式。求和范围为数据区D4：K4，列的范围不会发生变化，而行 12 要变化，所以采用绝对和相对引用相结合的形式。