Excel基础教程函数应用.docx
《Excel基础教程函数应用.docx》由会员分享，可在线阅读，更多相关《Excel基础教程函数应用.docx（35页珍藏版）》请在冰豆网上搜索。
Excel基础教程函数应用
目的2
报表两大主要元素（文本、数值）解说2
文本（字符串）2
数值3
常用功能3
『转置粘贴』3
『数据透视』5
『定位填充』9
常用公式函数14
&14
len14
Trim14
left14
right15
mid15
value15
min15
max16
sum16
average16
vlookup16
功能扩展22
『与Access的报表衔接』22
『宏编译』22
一些使用方法与技巧23
将数值金额转换为大写金额23
后语23
目的
在Excel报表大量存在的情况下，通过培训，使员工更加了解Excel表格的使用，同时了解Excel的某些特殊扩展功能，使得员工能发现自身操作过程中的“可优化环节”，为优化提出良好建议，提高工作效率。
并为发现工作流中的真正瓶颈打下基础。
信不信由你，在工作中，能灵活使用以下函数，你就会被认为是Excel高手。
报表两大主要元素（文本、数值）解说
在日常报表操作中，通常涉及到对以下两种元素的操作：
文本（字符串）
如表格抬头、产品型号、备注等其它一些内容，主要是一些中英文字符。
对文本的操作主要是“标准化”，即通过各种手段，如连接（加长）、截断（提取部分字符）等，使得文本以统一的格式、特征存在于表格当中，以便于对报表中相关的数据进行其它的查找、透视、汇总等操作。
（这段话写得有点牵强，写完后我都看不大懂了。
在此可先不管它，在以后的操作当中会很有体会得。
呵呵）
文本通常是不能做加减等运算的。
特例一：
有些看上去像数值的也可能是文本。
当使用求和【sum】等一些公式对某部分数值进行加减，得到错误的结果时，那些“数值”很可能就是文本。
这时候，为了要对“数值”进行计算，就需要用函数将文本转为数值。
转换函数为【value】，具体使用方法见《常用函数》部分。
特例二：
有很多看上去一样的文本实际上是不一样的。
例如在型号中经常出现的括号和横杆，在中文输入法（全角）的情况下输入的括号和在英文输入法（半角）的情况下输入的括号是不一样的，但通常很难辨别。
这时候，在要对型号或其它一些文本进行统一的时候，通常可以使用『查找替换』的功能，为了满足分析的要求，一般建议将全角（中文输入法）的字符替换为半角（英文输入法）的字符。
有部分文本因为两端包含空格而导致不一致。
如“BCD－209S/E”和“BCD－209S/E”在Excel表格中看上去完全一样，但实际上是不一样的。
这种情况下，在要对文本进行统一的时候，通常可以使用【trim】函数，将两端带有空格的文本中的空格给去除，具体使用方法见《常用函数》部分。
数值
如表格中的收发存等数据，主要有“0”～“9”十个数字组成，另外，金额实际也是一种数值，只是其表示方式（保留两位小数）和一般数值不大一致而已。
数值通常要用来做加减等运算。
特例一：
有些看上去像文本的也可能是数值。
主要出现在一些通过公式将数值转换为大写金额的情况下，发生得不多，而且在单纯使用Excel时不会造成什么错误。
在这里只提一下，在『与Access衔接』部分再做介绍。
特例二：
想输入文本的时候却是在输入数值。
目前见得最多的情况是在录入邮政编码或者电话号码的时候，有些地区的邮政编码第一个数字为“0”，这时候，如果直接在表格中输入时，“0”值将不能显示出来，因为Excel里会将数值前面的零值去掉。
这样，邮政编码将被表示为一个5位的数值。
解决办法有两种：
一是在输入之前将单元格的属性（单元格格式）设置为文本格式，然后再输入；二是在数值前面加上“’”一起输入，如要输入“013023”，则在单元格中输入“’013023”，此时Excel便会自动将该单元格设置成文本格式，并且不显示“’”字符。
常用功能
『显示/隐藏零值』
功能：
将表格中的所有零值一次性显示或隐藏。
主要用途：
方便阅读与打印。
『转置粘贴』
功能：
复制区域的顶行数据将显示于粘贴区域的最左列，而复制区域的最左列将显示于粘贴区域的顶行。
即行变列，列变行。
主要用途：
调整格式与方便其它功能的使用。
使用方法：
5、点击选定“转置”复选框，然后点击确定即得结果，如下图所示。
『数据透视』
功能：
按一定格式将数据进行加总。
主要用途：
对数据进行汇总统计分析。
使用方法：
1、打开要进行汇总分析的表格，选择“数据透视表和图表报告”
9、这时，表格中的数据的含义为：
某库某时实际发货数的汇总数。
『定位填充』
功能：
批量选定有相同特征的单元格，并按要求对这些单元格填充相应的内容。
主要用途：
通常用于对表格中存在的大量空格进行填充。
使用方法：
1、对空格填“0”
6、输入填充内容，这里是将空值填上“0”，所以输入公式“＝0”，然后“Ctrl”—＋“Enter”键，即得结果。
如下图所示。
2、将某单元格的内容填充到紧接其下的空格当中去，如下面这种情况：
其前5个步骤和填充“0”值完全一致。
第6步如下：
6、输入“＝”＋“上箭头”，然后“Ctrl”＋“Enter”，即得结果。
如下图所示。
常用公式函数
&
这是一个连接符，相当于加号一样，不过其所加的不是数值，而是字符串。
即将该符号两端的字符串给连接起来。
使用方法：
＝”tring1”&“string2”
结果为：
string1string2
len
该函数用于提取文本的长度。
使用方法：
＝len（“学好Excel”）
结果：
7
Trim
除了单词之间的单个空格外，清除文本中所有的空格。
主要用于清除文本两端的空格。
使用方法：
＝trim（“hello”）
结果：
hello
left
基于所指定的字符数截取文本字符串中的第一个或前几个字符，即从文本的左端截取一定数量的字符。
使用方法：
1、截取字符串左端第一个字符
＝left（“BCD－209S/E”）
结果：
B
2、截取字符串左端3个字符
＝left（“BCD－209S/E”，3）
结果：
BCD
3、结合使用【&】的一个例子
说明：
由于科龙产品中出现同型号不同品牌的情况，这样，在查询（尤其是使用vlookup函数进行垂直查找）的时候就会出现错误的结果。
比如康拜恩209S/E和容声209S/E等。
=left（“康拜恩”）&“209S/E”
结果：
康209S/E
=left（“容声”）&“209S/E”
结果：
容209S/E
具体例子参考《Excel表格应用技能培训实例汇总》
right
与【left】函数相对，该函数基于所指定的字符数返回文本字符串中最后一个或多个字符，即从文本的右端截取一定数量的字符。
使用方法：
1、截取字符串右端第一个字符
＝right（“BCD－209S/E”）
结果：
E
2、截取字符串右端3个字符
＝right（“BCD－209S/E”，3）
结果：
S/E
mid
返回文本字符串中从指定位置开始的特定数目的字符。
即从文本的中间截取一定数量的字符。
使用方法：
1、截取字符串中从第3个字符开始的1个字符
＝mid（“BCD－209S/E”，3，1）
结果：
D
2、截取字符串中从第5个字符开始的6个字符
＝mid（“BCD－209S/E”，5，6）
结果：
209S/E
value
将代表数字的文本字符串转换成数字。
使用方法：
将文本转换为数值：
=value（“09”）
结果：
9
min
返回选定区域中所有数值中的最小值。
这个函数很容易使用，但有个限制，即当数据或者数据区域超过29个的时候，该函数将不能直接得出结果。
如在连续日报中，通常包含31天的数据，当要求31天的结存数的最小值时，就不能一次性的使用公式得到。
这种情况的解决办法其实也很简单，“一次不行就用两次甚至三次”，即先用【min】函数对一部分数值求最小，然后再用【min】函数对剩余部分求最小，最后用【min】函数求两个最小中的最小。
采用这种分而治之的办法，可以在不改变报表格式的情况下使问题得到解决。
对下面介绍的【max】函数也有这种情况。
max
与【min】函数相对，返回选定区域中所有数值中的最大值。
sum
计算选定单元格区域中所有数字之和。
average
计算选定单元格区域中的所有数字的平均值（算术平均值）。
vlookup
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
（这个定义太难看懂了，但我也不知道该怎么说。
还是通过例子来看吧。
）
在日常使用中，也有人把它叫做匹配函数，但实际上，这不是真正意义上的匹配函数，而仅是一个垂直查找函数。
如果大家对匹配函数有兴趣，可以使用Excel的帮助，进行了解，这里不做介绍。
这个函数比较难熟练掌握，但这个函数在报表制作当中经常用到。
所以，大家在学习使用的过程中，一定要多尝试使用，坚定信心把它练熟。
可以说，学习好这个函数，对Excel表格的应用是一个质的飞跃。
另外，此培训手册中的其它一些文本函数如【&】、【trim】、【left】、【right】、【mid】等，主要就是为了配合这个函数使用的。
使用方法：
＝vlookup（查找标识，要查找内容所在的数据区域，内容所在第几列，0）
其中每一项的定义很难用一两句话通俗的表达出来。
通过具体例子可以得到更好的理解。
1、固定标识查找
2、
扩展公式批量查找
显然，使用这个函数有个前提条件，即涉及到的填充表和数据源表中的型号必须是一致的才能实现查找，否则，结果会显示为“＃N/A”。
所以，在使用函数前先要对一些表示不一致的型号进行统一，通常就要应用到以上的几个函数【&】、【left】、【mid】和【right】等。
功能扩展
『与Access的报表衔接』
由于Excel表格的易用性，所以对于数据的处理通常都通过Excel表格来处理，但在很多情况下，要根据Excel表格中记录的数据填写单据作为其它的用途。
填写费用申领单就是一个很好的例子。
由于费用的笔数很多，通常要填写大量的单据，这样，会很浪费人力物力。
在这种情况下，如果将Excel与Access中的报表连接起来，通过报表的自由设计和打印功能直接批量的打印出来，则能省下大量的时间。
这里只对此做简单介绍，有兴趣的朋友可以去详细了解一下。
『宏编译』
Excel中提供了大量的函数，使得在利用Excel做数据分析时能够很方便的达到目的，但当要对数据进行比较复杂的分析时，通常就涉及到很多很多个步骤，即使能很熟练使用各种功能和函数，也需要花上大量的时间，才能完成数据的分析。
由其当这样的复杂分析每月或者每周甚至每天都要进行时，花费这些时间就很不值得。
为此，Excel中提供了宏汇编的功能，即通过编译一段程序，有序地组织数据分析时使用的各种功能和函数，使得分析中所涉及到的各个步骤自动执行，一步到位地生成分析结果。
由于宏编译涉及到程序的编写，由于篇幅所限，在这里只作功能的介绍，使大家有个了解。
一些使用方法与技巧
将数值金额转换为大写金额
在涉及商务或者财务的工作时，经常要将用阿拉伯数字表示的金额转换为大写金额的表示形式，但Excel表格中并没有提供具有直接转换功能的函数，在网上也有一些通过宏编出来的转换函数，但功能上似乎有所限制。
一般情况下，可以通过以下公式（估计没什么人用过这么复杂的公式了）达到转换目的：
=IF（ROUND（A1,2）<0,"无效数值",IF（ROUND（A1,2）=0,"零",IF（ROUND（A1,2）<1,"",TEXT（INT（ROUND（A1,2））,"[dbnum2]"）&"元"）&IF（INT（ROUND（A1,2）*10）-INT（ROUND（A1,2））*10=0,IF（INT（ROUND（A1,2））*（INT（ROUND（A1,2）*100）-INT（ROUND（A1,2）*10）*10）=0,"","零"）,TEXT（INT（ROUND（A1,2）*10）-INT（ROUND（A1,2））*10,"[dbnum2]"）&"角"）&IF（（INT（ROUND（A1,2）*100）-INT（ROUND（A1,2）*10）*10）=0,"整",TEXT（（INT（ROUND（A1,2）*100）-INT（ROUND（A1,2）*10）*10）,"[dbnum2]"）&"分"）））
这个公式中以对单元格A1中的数值的转换为例。
大家在复制使用的时候，可以通过替换的方式改为对其它单元格中的数值的转换。
后语
Excel表格在日常工作中的应用可以说是时时刻刻存在，希望大家主动认真地去学习其各种功能，掌握这些功能，往往能使工作效率成倍提高。
以上的一些功能与函数仅是我个人的一些使用心得，完全是按照个人的理解和表达习惯写出来的，如果有表达不清析的地方，请查阅相关权威资料。
让我们互相学习，共同进步。
升级会员 