excel常用函数及数据整理技巧,相当全面
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Document number 13
九、财务函数
在此部分结合常用财务管理理论，EXCEL提供了16个具体财务函 数，主要分为投资理财类函数与折旧类函数。
折旧类函数
SLN(cost,salvage,life) 返回某项资产在一个期间中的线性折旧值。 SYD(cost,salvage,life,per) 返回某项资产按年限总和折旧法计算的指定期间的折旧值。 DDB(cost,salvage,life,period,factor) 使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。
Document number
21
四、分列
Document number
22
四、分列
步骤1
分列的两种类型：
1、按分隔符号分列
2、按固定列宽分列
操作时应具体按被分列项特征 ，选取不同分列方法。在本例 中，两种方法均可：可以以空 格为分列符号分列；也可以用 固定列宽人为添加分列线分列
Document number
18
三、数据清单的筛选
2、高级筛选
如果数据清单字段较多，筛选条件也较多，则可以使用高级筛选
注意： A、要首先建立条件区域 B、条件区域第一行是作为筛选条件的字 段名，必须与数据清单中字段名完全相同 C、条件区域其他行用于输入筛选条件
D、条件区域与数据清单不能连接，须有 一个空行或空列分离
2006.001 2006.004 2006.006 2006.002 2006.004 2006.006 2006.006 2006.001 2006.001 2006.002 2006.001 2006.002
一月 四月 六月 二月 四月 六月 六月 一月 一月 二月 一月 二月
2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006
Document number
10
六、查询与引用函数
1、绝对引用与相对引用 绝对引用—在复制公式时不希望EXCEL调整引用，须使用绝对引用 相对引用—在创建公式时，单元格或单元格区域的引用通常是包含公式的单 元格的相对位置。
2、引用的表示方法 :A1与R1C1两种类型引用
3、主要函数 LOOKUP(lookup_value,lookup_vector,result_vector)
在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
2、求单元格个数函数
COUNT(value1,value2,...)：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(value1,value2,...)：返回参数列表中非空值的单元格个数。
Document number
9
五、统计函数
3、求区域中数据分布频率函数 FREQUENCY(data_array,bins_array)： 以一列垂直数组返回某个区域中数据的频率分布。 4、线性回归分析函数 CORREL(array1,array2)：返回单元格区域 array1 和 array2 之间的相关系数。 FORECAST(x,known_y„s,known_x‟s)：根据已有的数值计算或预测未来值。 5、其他特定需求统计函数 最大最小值 MAX(number1,number2,...)/ MIN(number1,number2,...) 第K大与第K小 LARGE(array,k) / SMALL(array,k) 中位数 MEDIAN(number1,number2,...) 排名 RANK(number,ref,order)
Document number
19
三、数据清单的筛选
2、高级筛选
Document number
20
四、分列
如果某单元格或列包含数个可用信息，希望把其拆分在数个单元格内列示 时，则用到“分列”工具。 如下图中：开票期间项
销售订单 30763 32719 32720 31939 31941 31958 33459 30469 30470 31612 32025 31702 物料 A055 B063 B063 E055 E055 H039 E055 A055 C081 C081 E054 F133 描述 ZK6129H-2成品 ZK6118HA-3成品 ZK6118HA-3成品 ZK6831HE成品 ZK6831HE成品 ZK6896HG成品 ZK6831HE成品 ZK6129H-2成品 ZK6100HB成品 ZK6100HB成品 ZK6860HA成品 ZK6780D成品 售达方 105271 201267 201267 104594 104594 104594 111658 104834 104834 104834 104834 106648 国家 叙利亚 阿联酋 阿联酋 瑞士 瑞士 瑞士 毛里求斯 埃及 埃及 埃及 埃及 美国 订单类型 ZOR2 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR2 开票数 量 2 1 1 1 1 1 1 2 4 6 6 4 主营业务收入 1,940,539.86 721,994.98 721,994.98 482,243.18 485,914.99 490,271.69 347,204.44 1,378,882.65 2,167,909.94 3,739,029.35 2,742,313.28 1,168,198.43 开票期间
Document number
4
二、文本函数
通过文本函数，可以在公式中处理字符串。例如可以转换文本 格式、截取特定文本字符、查找替代特定文本等。
1、转换类 大小写转换类 upper(); lower(); proper() 数字、文本等格式间转换 dollar(); rmb(); text()；value() 2、截取/合并类 截取类 left()/leftb() ； right()/rightb() ； mid()/midb() ; len()/lenb() 合并类 concatenate() 3、文本整理类 trim() /clean() 4、字符查找、比较、替代类 查找类 find(); findb(); search(); searchb() 比较类 exact() 替代类 replace(); substitute()
Document number
5
三、日期与时间函数
数据表处理过程中，日期与时间函数是相当重要的处理依据。
1、取当前系统时间、日期信息函数 now() ; today() 2、取得日期、时间的部分字段值函数 year() ; month() ; day() ; hour() ;
minute();
OR(logical1,logical2,...)：在其参数组中，任何一个参数逻辑值为 TRUE，即返回
TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。
NOT(logical)：对参数值求反。当要确保不等于某一特定值时，可以使用此函数。
2、if()条件函数 IF(logical_test,value_if_true,value_if_false)
Document number
7
四、数学和三角函数
2、数字取舍类函数
ROUND(number,num_digits) ：返回某个数字按指定位数舍入后的数字
INT(number) ：返回小数舍入后的整数，即向下取整 用于四舍五入时需要技巧即：加0.5后取整
ABS(number)：返回数字的绝对值。
Document number
16
二、数据清单的排序
1、排序方式
简单排序与多重排序
2、排序方法
升序、降序（默认以拼音排序）
Document number
17
三、数据清单的筛选
1、自动筛选
适用于简单条件，通常是在一个数据清单的一列中查找相同的值。也可以用 自定义筛选方式快速查找出符合多重条件的记录。
second()
3、日期计算函数 datedif() ; days360()
Document number
6
四、数学和三角函数
1、与求和有关的函数
SUM(number1,number2, ...) 行列求和、区域求和 （参数最多30个） SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 SUBTOTAL(function_num, ref1, ref2, ...) 返回数据清单分类汇总
EXCEL常用函数及数据整理技巧
财务中心：李海全
Document number
1
内容概要
1、EXCEL函数应用 2、EXCEL数据整理方法与技巧 3、快捷键应用
Document number
2
第一部分
EXCEL常用函数
Document number
3
一、EXCEL函数类型
文本函数 日期和时间函数 数学和三角函数 统计函数 查询和引用函数 逻辑函数 信息函数 财务函数 工程函数 数据库函数 自定议函数
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值
Document number
11
七、逻辑函数
用于判断真假值，或者进行复合检验的EXCEL函数,称之为逻辑 函数。
1、表达“且”、“或”、“不”关系的逻辑函数 AND(logical1,logical2, ...)
所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。
Document number
14
第二部分
EXCEL数据整理技巧
ຫໍສະໝຸດ Baidu
Document number
15
一、EXCEL常用数据整理方法与技巧
1、排序 2、筛选
数据清单：指包含一组相关数据的一系列工作表数据行
3、分列
4、分类汇总 5、合并计算 6、数据透视表
数据清单应遵循一般准则： • 每张工作表使用一个数据清单 •使清单独立：数据清单与其他数据间至少留一个空行或空列 •数据清单内避免空行与空列
执行真假值判断，根据逻辑计算的真假值，返回不同结果。最多嵌套7层条件。
Document number
12
八、信息函数
专门用来返回某些指定单元格或区域等的信息，比如单元格的 内容、格式、个数等，这类函数统称为信息类函数。
主要介绍IS类函数，常与IF函数配合使用。
ISBLANK(value) 值为空白单元格时,返回true。 ISERR(value) 值为任意错误值（除去 #N/A）时,返回true。 ISERROR(value) 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、 #NUM!、#NAME? 或 #NULL!）时,返回true。 ISLOGICAL(value) 值为逻辑值时,返回true。 ISNA(value) 值为错误值 #N/A（值不存在）时,返回true。 ISNONTEXT(value) 值为不是文本的任意项时,返回true。 ISNUMBER(value) 值为数字时,返回true。 ISREF(value) 值为引用时,返回true。 ISTEXT(value) 值为文本时,返回true。
TRUNC(number,num_digits): 将数字的小数部分截去，返回整数。
Document number
8
五、统计函数
作为EXCEL函数中数量最为庞大的一类，统计类函数提供了丰 富的概率与统计函数功能。：
1、用于求平均值的函数
AVERAGE(number1,number2,...)：返回参数的平均值（算术平均值） AVERAGEA(value1,value2,...)：计算参数列表中数值的平均值（算数平均值） TRIMMEAN(array,percent)：返回数据集的内部平均值。
九、财务函数
在此部分结合常用财务管理理论，EXCEL提供了16个具体财务函 数，主要分为投资理财类函数与折旧类函数。
折旧类函数
SLN(cost,salvage,life) 返回某项资产在一个期间中的线性折旧值。 SYD(cost,salvage,life,per) 返回某项资产按年限总和折旧法计算的指定期间的折旧值。 DDB(cost,salvage,life,period,factor) 使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。
Document number
21
四、分列
Document number
22
四、分列
步骤1
分列的两种类型：
1、按分隔符号分列
2、按固定列宽分列
操作时应具体按被分列项特征 ，选取不同分列方法。在本例 中，两种方法均可：可以以空 格为分列符号分列；也可以用 固定列宽人为添加分列线分列
Document number
18
三、数据清单的筛选
2、高级筛选
如果数据清单字段较多，筛选条件也较多，则可以使用高级筛选
注意： A、要首先建立条件区域 B、条件区域第一行是作为筛选条件的字 段名，必须与数据清单中字段名完全相同 C、条件区域其他行用于输入筛选条件
D、条件区域与数据清单不能连接，须有 一个空行或空列分离
2006.001 2006.004 2006.006 2006.002 2006.004 2006.006 2006.006 2006.001 2006.001 2006.002 2006.001 2006.002
一月 四月 六月 二月 四月 六月 六月 一月 一月 二月 一月 二月
2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006
Document number
10
六、查询与引用函数
1、绝对引用与相对引用 绝对引用—在复制公式时不希望EXCEL调整引用，须使用绝对引用 相对引用—在创建公式时，单元格或单元格区域的引用通常是包含公式的单 元格的相对位置。
2、引用的表示方法 :A1与R1C1两种类型引用
3、主要函数 LOOKUP(lookup_value,lookup_vector,result_vector)
在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
2、求单元格个数函数
COUNT(value1,value2,...)：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(value1,value2,...)：返回参数列表中非空值的单元格个数。
Document number
9
五、统计函数
3、求区域中数据分布频率函数 FREQUENCY(data_array,bins_array)： 以一列垂直数组返回某个区域中数据的频率分布。 4、线性回归分析函数 CORREL(array1,array2)：返回单元格区域 array1 和 array2 之间的相关系数。 FORECAST(x,known_y„s,known_x‟s)：根据已有的数值计算或预测未来值。 5、其他特定需求统计函数 最大最小值 MAX(number1,number2,...)/ MIN(number1,number2,...) 第K大与第K小 LARGE(array,k) / SMALL(array,k) 中位数 MEDIAN(number1,number2,...) 排名 RANK(number,ref,order)
Document number
19
三、数据清单的筛选
2、高级筛选
Document number
20
四、分列
如果某单元格或列包含数个可用信息，希望把其拆分在数个单元格内列示 时，则用到“分列”工具。 如下图中：开票期间项
销售订单 30763 32719 32720 31939 31941 31958 33459 30469 30470 31612 32025 31702 物料 A055 B063 B063 E055 E055 H039 E055 A055 C081 C081 E054 F133 描述 ZK6129H-2成品 ZK6118HA-3成品 ZK6118HA-3成品 ZK6831HE成品 ZK6831HE成品 ZK6896HG成品 ZK6831HE成品 ZK6129H-2成品 ZK6100HB成品 ZK6100HB成品 ZK6860HA成品 ZK6780D成品 售达方 105271 201267 201267 104594 104594 104594 111658 104834 104834 104834 104834 106648 国家 叙利亚 阿联酋 阿联酋 瑞士 瑞士 瑞士 毛里求斯 埃及 埃及 埃及 埃及 美国 订单类型 ZOR2 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR1 ZOR2 开票数 量 2 1 1 1 1 1 1 2 4 6 6 4 主营业务收入 1,940,539.86 721,994.98 721,994.98 482,243.18 485,914.99 490,271.69 347,204.44 1,378,882.65 2,167,909.94 3,739,029.35 2,742,313.28 1,168,198.43 开票期间
Document number
4
二、文本函数
通过文本函数，可以在公式中处理字符串。例如可以转换文本 格式、截取特定文本字符、查找替代特定文本等。
1、转换类 大小写转换类 upper(); lower(); proper() 数字、文本等格式间转换 dollar(); rmb(); text()；value() 2、截取/合并类 截取类 left()/leftb() ； right()/rightb() ； mid()/midb() ; len()/lenb() 合并类 concatenate() 3、文本整理类 trim() /clean() 4、字符查找、比较、替代类 查找类 find(); findb(); search(); searchb() 比较类 exact() 替代类 replace(); substitute()
Document number
5
三、日期与时间函数
数据表处理过程中，日期与时间函数是相当重要的处理依据。
1、取当前系统时间、日期信息函数 now() ; today() 2、取得日期、时间的部分字段值函数 year() ; month() ; day() ; hour() ;
minute();
OR(logical1,logical2,...)：在其参数组中，任何一个参数逻辑值为 TRUE，即返回
TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。
NOT(logical)：对参数值求反。当要确保不等于某一特定值时，可以使用此函数。
2、if()条件函数 IF(logical_test,value_if_true,value_if_false)
Document number
7
四、数学和三角函数
2、数字取舍类函数
ROUND(number,num_digits) ：返回某个数字按指定位数舍入后的数字
INT(number) ：返回小数舍入后的整数，即向下取整 用于四舍五入时需要技巧即：加0.5后取整
ABS(number)：返回数字的绝对值。
Document number
16
二、数据清单的排序
1、排序方式
简单排序与多重排序
2、排序方法
升序、降序（默认以拼音排序）
Document number
17
三、数据清单的筛选
1、自动筛选
适用于简单条件，通常是在一个数据清单的一列中查找相同的值。也可以用 自定义筛选方式快速查找出符合多重条件的记录。
second()
3、日期计算函数 datedif() ; days360()
Document number
6
四、数学和三角函数
1、与求和有关的函数
SUM(number1,number2, ...) 行列求和、区域求和 （参数最多30个） SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 SUBTOTAL(function_num, ref1, ref2, ...) 返回数据清单分类汇总
EXCEL常用函数及数据整理技巧
财务中心：李海全
Document number
1
内容概要
1、EXCEL函数应用 2、EXCEL数据整理方法与技巧 3、快捷键应用
Document number
2
第一部分
EXCEL常用函数
Document number
3
一、EXCEL函数类型
文本函数 日期和时间函数 数学和三角函数 统计函数 查询和引用函数 逻辑函数 信息函数 财务函数 工程函数 数据库函数 自定议函数
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值
Document number
11
七、逻辑函数
用于判断真假值，或者进行复合检验的EXCEL函数,称之为逻辑 函数。
1、表达“且”、“或”、“不”关系的逻辑函数 AND(logical1,logical2, ...)
所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。
Document number
14
第二部分
EXCEL数据整理技巧
ຫໍສະໝຸດ Baidu
Document number
15
一、EXCEL常用数据整理方法与技巧
1、排序 2、筛选
数据清单：指包含一组相关数据的一系列工作表数据行
3、分列
4、分类汇总 5、合并计算 6、数据透视表
数据清单应遵循一般准则： • 每张工作表使用一个数据清单 •使清单独立：数据清单与其他数据间至少留一个空行或空列 •数据清单内避免空行与空列
执行真假值判断，根据逻辑计算的真假值，返回不同结果。最多嵌套7层条件。
Document number
12
八、信息函数
专门用来返回某些指定单元格或区域等的信息，比如单元格的 内容、格式、个数等，这类函数统称为信息类函数。
主要介绍IS类函数，常与IF函数配合使用。
ISBLANK(value) 值为空白单元格时,返回true。 ISERR(value) 值为任意错误值（除去 #N/A）时,返回true。 ISERROR(value) 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、 #NUM!、#NAME? 或 #NULL!）时,返回true。 ISLOGICAL(value) 值为逻辑值时,返回true。 ISNA(value) 值为错误值 #N/A（值不存在）时,返回true。 ISNONTEXT(value) 值为不是文本的任意项时,返回true。 ISNUMBER(value) 值为数字时,返回true。 ISREF(value) 值为引用时,返回true。 ISTEXT(value) 值为文本时,返回true。
TRUNC(number,num_digits): 将数字的小数部分截去，返回整数。
Document number
8
五、统计函数
作为EXCEL函数中数量最为庞大的一类，统计类函数提供了丰 富的概率与统计函数功能。：
1、用于求平均值的函数
AVERAGE(number1,number2,...)：返回参数的平均值（算术平均值） AVERAGEA(value1,value2,...)：计算参数列表中数值的平均值（算数平均值） TRIMMEAN(array,percent)：返回数据集的内部平均值。