Excel基础教程(函数应用).doc

目录目的2报表两大主要元素（文本、数值）解说2文本（字符串）2数值3常用功能3转置粘贴3数据透视5定位填充9常用公式函数14&14len14Trim14left14right15mid15value15min15max16sum16average16vlookup16功能扩展22与Access的报表衔接22宏编译22一些使用方法与技巧23将数值金额转换为大写金额23后语23目的在Excel报表大量存在的情况下，通过培训，使员工更加了解Excel表格的使用，同时了解Excel的某些特殊扩展功能，使得员工能发现自身操作过程中的“可优化环节”，为优化提出良好建议，提高工作效率。并为发现工作流中的真正瓶颈打下基础。信不信由你，在工作中，能灵活使用以下函数，你就会被认为是Excel高手。报表两大主要元素（文本、数值）解说在日常报表操作中，通常涉及到对以下两种元素的操作：文本（字符串）如表格抬头、产品型号、备注等其它一些内容，主要是一些中英文字符。对文本的操作主要是“标准化”，即通过各种手段，如连接（加长）、截断（提取部分字符）等，使得文本以统一的格式、特征存在于表格当中，以便于对报表中相关的数据进行其它的查找、透视、汇总等操作。（这段话写得有点牵强，写完后我都看不大懂了。在此可先不管它，在以后的操作当中会很有体会得。呵呵）文本通常是不能做加减等运算的。特例一：有些看上去像数值的也可能是文本。当使用求和【sum】等一些公式对某部分数值进行加减，得到错误的结果时，那些“数值”很可能就是文本。这时候，为了要对“数值”进行计算，就需要用函数将文本转为数值。转换函数为【value】，具体使用方法见常用函数部分。 特例二：有很多看上去一样的文本实际上是不一样的。 例如在型号中经常出现的括号和横杆，在中文输入法（全角）的情况下输入的括号和在英文输入法（半角）的情况下输入的括号是不一样的，但通常很难辨别。这时候，在要对型号或其它一些文本进行统一的时候，通常可以使用查找替换的功能，为了满足分析的要求，一般建议将全角（中文输入法）的字符替换为半角（英文输入法）的字符。 有部分文本因为两端包含空格而导致不一致。如“BCD209S/E”和“BCD209S/E ”在Excel表格中看上去完全一样，但实际上是不一样的。这种情况下，在要对文本进行统一的时候，通常可以使用【trim】函数，将两端带有空格的文本中的空格给去除，具体使用方法见常用函数部分。数值如表格中的收发存等数据，主要有“0”“9”十个数字组成，另外，金额实际也是一种数值，只是其表示方式（保留两位小数）和一般数值不大一致而已。数值通常要用来做加减等运算。 特例一：有些看上去像文本的也可能是数值。 主要出现在一些通过公式将数值转换为大写金额的情况下，发生得不多，而且在单纯使用Excel时不会造成什么错误。在这里只提一下，在与Access衔接部分再做介绍。 特例二：想输入文本的时候却是在输入数值。 目前见得最多的情况是在录入邮政编码或者电话号码的时候，有些地区的邮政编码第一个数字为“0”，这时候，如果直接在表格中输入时，“0”值将不能显示出来，因为Excel里会将数值前面的零值去掉。这样，邮政编码将被表示为一个5位的数值。解决办法有两种：一是在输入之前将单元格的属性（单元格格式）设置为文本格式，然后再输入；二是在数值前面加上“”一起输入，如要输入“013023”，则在单元格中输入“013023”，此时Excel便会自动将该单元格设置成文本格式，并且不显示“”字符。常用功能显示/隐藏零值功 能：将表格中的所有零值一次性显示或隐藏。主要用途：方便阅读与打印。2、点击“选项”1、点击菜单“工具”4、选择是否需要显示零值，前面打勾表示显示，点击去掉即不显示。确定后，效果如下图所示3、选择“视图”选项卡效果：所有零值已被隐藏转置粘贴功 能：复制区域的顶行数据将显示于粘贴区域的最左列，而复制区域的最左列将显示于粘贴区域的顶行。即行变列，列变行。主要用途：调整格式与方便其它功能的使用。使用方法：2、右键单击，复制数据1、选定要转置的区域4、点击“选择性粘贴”3、右键单击要粘贴数据的位置 5、点击选定“转置”复选框，然后点击确定即得结果，如下图所示。数据透视功 能：按一定格式将数据进行加总。主要用途：对数据进行汇总统计分析。使用方法：1、打开要进行汇总分析的表格，选择“数据透视表和图表报告” 2、点击下一步（在此仅介绍最常用的一种操作，做个引子，其它深入的操作读者按需要自己摸索）4、下一步，继续其它内容选择3、选择涉及分析的数据范围5、点击完成，进入后面的操作4、为了避免分析数据和目前表格数据混淆，可选“新建工作表”，在数据量较少的情况下，也可以选择“现有工作表”，使产生结果在当前的工作表中显示出来。6、字段列表。里面包含了前面所选择的数据所涉及到的所有列的内容。通过将相关的字段拖动到【数据透视结果表】中，即可产生相应的结果。如图将三个字段分别拖放至相应位置，即可得到下图中的结果7、在此，这部分内容简称为【数据透视结果表】8、明显，【结果表】中的数据表示某时某库实际发货的汇总数。此时，还可以调整字段的相对位置或者通过拖放加入其它字段，来实现具有不同含义的结果。如将“计划提货时间”和计划发货仓库调换，得到下表结果。9、这时，表格中的数据的含义为：某库某时实际发货数的汇总数。定位填充功 能：批量选定有相同特征的单元格，并按要求对这些单元格填充相应的内容。主要用途：通常用于对表格中存在的大量空格进行填充。使用方法：1、对空格填“0”1、选定待填充区域3、点击“定位条件”按钮弹出下面对话框2、按“Ctrl”“g”健，弹出“定位”窗口5、点击确定，即选定所有内容为空的单元格4、选择“空值”选项6、输入填充内容，这里是将空值填上“0”，所以输入公式“0”，然后“Ctrl”“Enter”键，即得结果。如下图所示。2、将某单元格的内容填充到紧接其下的空格当中去，如下面这种情况：为了分析方便，经常要将“广州”下面的空白单元格全部填上“广州”，“深圳”下面的单元格填上“深圳”等等 其前5个步骤和填充“0”值完全一致。第6步如下：6、输入“”“上箭头”，然后“Ctrl”“Enter”，即得结果。如下图所示。常用公式函数&这是一个连接符，相当于加号一样，不过其所加的不是数值，而是字符串。即将该符号两端的字符串给连接起来。使用方法：”tring1” & “string2”结果为：string1string2len该函数用于提取文本的长度。 使用方法：len（“学好Excel”） 结果：7Trim除了单词之间的单个空格外，清除文本中所有的空格。主要用于清除文本两端的空格。 使用方法：trim（“ hello”） 结果：helloleft基于所指定的字符数截取文本字符串中的第一个或前几个字符，即从文本的左端截取一定数量的字符。使用方法：1、截取字符串左端第一个字符left（“BCD209S/E”）结果：B2、截取字符串左端3个字符left（“BCD209S/E”，3）结果：BCD3、结合使用【&】的一个例子 说明：由于科龙产品中出现同型号不同品牌的情况，这样，在查询（尤其是使用vlookup函数进行垂直查找）的时候就会出现错误的结果。比如康拜恩209S/E和容声209S/E等。=left(“康拜恩”) & “209S/E”结果：康209S/E=left(“容声”) & “209S/E”结果：容209S/E具体例子参考Excel表格应用技能培训实例汇总right与【left】函数相对，该函数基于所指定的字符数返回文本字符串中最后一个或多个字符，即从文本的右端截取一定数量的字符。使用方法：1、 截取字符串右端第一个字符right（“BCD209S/E”）结果：E2、 截取字符串右端3个字符right（“BCD209S/E”，3）结果：S/Emid返回文本字符串中从指定位置开始的特定数目的字符。即从文本的中间截取一定数量的字符。 使用方法：1、 截取字符串中从第3个字符开始的1个字符mid（“BCD209S/E”，3，1）结果：D2、 截取字符串中从第5个字符开始的6个字符mid（“BCD209S/E”，5，6）结果：209S/Evalue将代表数字的文本字符串转换成数字。使用方法： 将文本转换为数值： =value(“09”) 结果：9min返回选定区域中所有数值中的最小值。这个函数很容易使用，但有个限制，即当数据或者数据区域超过29个的时候，该函数将不能直接得出结果。如在连续日报中，通常包含31天的数据，当要求31天的结存数的最小值时，就不能一次性的使用公式得到。这种情况的解决办法其实也很简单，“一次不行就用两次甚至三次”，即先用【min】函数对一部分数值求最小，然后再用【min】函数对剩余部分求最小，最后用【min】函数求两个最小中的最小。采用这种分而治之的办法，可以在不改变报表格式的情况下使问题得到解决。对下面介绍的【max】函数也有这种情况。max 与【min】函数相对，返回选定区域中所有数值中的最大值。sum 计算选定单元格区域中所有数字之和。average 计算选定单元格区域中的所有数字的平均值（算术平均值）。vlookup在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。（这个定义太难看懂了，但我也不知道该怎么说。还是通过例子来看吧。）在日常使用中，也有人把它叫做匹配函数，但实际上，这不是真正意义上的匹配函数，而仅是一个垂直查找函数。如果大家对匹配函数有兴趣，可以使用Excel的帮助，进行了解，这里不做介绍。这个函数比较难熟练掌握，但这个函数在报表制作当中经常用到。所以，大家在学习使用的过程中，一定要多尝试使用，坚定信心把它练熟。可以说，学习好这个函数，对Excel表格的应用是一个质的飞跃。另外，此培训手册中的其它一些文本函数如【&】、【trim】、【left】、【right】、【mid】等，主要就是为了配合这个函数使用的。使用方法：vlookup（查找标识，要查找内容所在的数据区域，内容所在第几列，0） 其中每一项的定义很难用一两句话通俗的表达出来。通过具体例子可以得到更好的理解。1、 固定标识查找从查找标识所在列起算，结存数在第5列。查找标识所在列公式：=VLOOKUP(XQB58-528VID,A1:E217,5,0)其中XQB58-528VID即为要查找的标识；A1:E217为要查找的内容所在的数据区域，注意查找标识必须在数据区域的第一列；5表示结存数在选定数据区域的第5列；0表示非排序查找，一般都用0即可。从左边的表格中查找型号为“XQB58-528VID”的结存数，填入单元格G4中2、 扩展公式批量查找存在如图表格，在此简称“数据源”表现在要将上面数据源表中的“收入”、“发出”和“结存”填到现在这个表格当中，在此简称为“填充表”4、由于列表中没有“vlookup”函数，所以点击“其它函数”。（当使用过该函数时，vlookup函数会被记录在清单当中的，用起来就方便一些）3、点击下三角按钮以选择函数1、点击C2格2、点击“”，开始编辑公式6、双击vlookup函数5、单击选中“查找与引用”类函数7、点击以选定查找标识9、点击以进入下一步选择内容8、点击要查找的标识所在单元格A211、点击以切换到要查找内容（收入数）所在的表格10、点击以选定要查找内容（收入数）所在的数据区域13、点击以进入下一步的输入12、选定数据所在的区域，这里选择全部，即从单元格A2到E21716、点击确定，完成公式设置15、填014、填入3，因为在数据源表选定的内容（A2：E217）中，从型号（查找标识所在列）开始数，发出数在第3列。14、将A2：E217设成绝对引用，即在A,2,E,217前分别加上$符号18、同样操作以填充发出数据和结存数据，亦可将C2中的公式向右拖动扩展填充，但要注意以下两个动作：A 扩展公式前，修改C2格中的公式，即在A2前加上“$”符号，表示绝对引用A列B 扩展公式后，修改D2格中的公式，即将公式中的第三个参数（表示第几列的）3改成4，因为发出数在选定的数据区域中，从型号开始数，处于第4列。同样，当该公式扩展到结存数时，这个参数要改为5。17、拖动以扩展填充公式 显然，使用这个函数有个前提条件，即涉及到的填充表和数据源表中的型号必须是一致的才能实现查找，否则，结果会显示为“N/A”。所以，在使用函数前先要对一些表示不一致的型号进行统一，通常就要应用到以上的几个函数【&】、【left】、【mid】和【right】等。功能扩展与Access的报表衔接由于Excel表格的易用性，所以对于数据的处理通常都通过Excel表格来处理，但在很多情况下，要根据Excel表格中记录的数据填写单据作为其它的用途。填写费用申领单就是一个很好的例子。由于费用的笔数很多，通常要填写大量的单据，这样，会很浪费人力物力。在这种情况下，如果将Excel与Access中的报表连接起来，通过报表的自由设计和打印功能直接批量的打印出来，则能省下大量的时间。这里只对此做简单介绍，有兴趣的朋友可以去详细了解一下。宏编译 Excel中提供了大量的函数，使得在利用Excel做数据分析时能够很方便的达到目的，但当要对数据进行比较复杂的分析时，通常就涉及到很多很多个步骤，即使能很熟练使用各种功能和函数，也需要花上大量的时间，才能完成数据的分析。由其当这样的复杂分析每月或者每周甚至每天都要进行时，花费这些时间就很不值得。为此，Excel中提供了宏汇编的功能，即通过编译一段程序，有序地组织数据分析时使用的各种功能和函数，使得分析中所涉及到的各个步骤自动执行，一步到位地生成分析结果。由于宏编译涉及到程序的编写，由于篇幅所限，在这里只作功能的介绍，使大家有个了解。一些使用方法与技巧将数值金额转换为大写金额在涉及商务或者财务的工作时，经常要将用阿拉伯数字表示的金额转换为大写金额的表示形式，但Excel表格中并没有提供具有直接转换功能的函数，在