excel经验的归纳总结.docx

错误值的提示与修正#DIV/0!错误原因：在公式中有除数为零，或者有除数为空白的单元格(Excel把空白单元格也当作0)。解决办法：把除数改为非零的数值，或者用IF函数进行控制。 #N/A错误原因：在公式使用查找功能的函数(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。解决办法：检查被查找的值，使之的确存在于查找的数据表中的第一列。 #NAME?错误原因：在公式中使用了Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。解决办法：根据具体的公式，逐步分析出现该错误的可能，并加以改正。#NUM!错误原因：当公式需要数字型参数时，我们却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。解决办法：根据公式的具体情况，逐一分析可能的原因并修正。 #VALUE错误原因：文本类型的数据参与了数值运算，函数参数的数值类型不正确；解决办法：函数的参数本应该是单一值，却提供了一个区域作为参数；输入一个数组公式时，忘记按CtrlShiftEnter键。 更正相关的数据类型或参数类型；提供正确的参数；输入数组公式时，记得使用CtrlShiftEnter键确定。 #REF!错误原因：公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单元格中。解决办法：避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。#NULL!错误原因：使用了不正确的区域运算符或引用的单元格区域的交集为空。改正区域运算符使之正确；更改引用使之相交。 ！是表示单元格与工作表的从属关系的，当跨表引用时，为了表明引用的单元格属于哪个工作表的，就在单元格前用其工作表加上！连接，如：sheet1!A1:D3，表示同一工作簿中名称为sheet1的工作表中的A1:D3单元格区域.。在公式中采用$表示对数据区域的绝对引用。相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。 具体情况举例说明：1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1 当将公式复制到C2单元格时变为：=A2+B2 当将公式复制到D1单元格时变为：=B1+C1 2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1 当将公式复制到C2单元格时仍为：=$A$1+$B$1 当将公式复制到D1单元格时仍为：=$A$1+$B$1 3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1 当将公式复制到C2单元格时变为：=$A2+B$1 当将公式复制到D1单元格时变为：=$A1+C$1sum与sumif的一般用法在excel函数中sum是大多数人学公式的第一个。许多人用了几年，对于sum的用法还是在A1+A2的高度。当然，sum与其它函数结合套用时会产生奇妙面强大的功能，而在这里，我只想说说它比较简单常用的一些用法。Sumif呢，则是常规的条件求和的最常用函数了，简单而易用。 Sum最简单的用法就是=sum(A1:A10)这个形式了。这是sum的最简单的格式。 Sum的格式是：SUM(number1,number2, .) Number1, number2, . 为 1 到 30 个需要求和的参数。 （帮助里的） =sum(A1：A10)的意思就是：汇总本表里A1到A10里的数值。当其中有数值以外的数据时，它会自动忽略。A1：A10是在一列里的，这叫一维引用。Sum(A1:C10)则是一个面的汇总了，这叫二维引用。Sum还支持三维引用：=sum(sheet1:sheet3!A1:A10)，这公式是汇总sheet1到sheet3三个表的A1：A10。 Sum还有条件求和的功能，如图： 用公式=SUM(A1:A10=苹果)*B1:B10)可以求得A1：A10里“苹果”项的B1：B10列的总数。这是一个数组公式，输入公式入得按Ctrl+Shift+Enter三键结束，这样就会自动加上两边的花括号了。（注：此结果也可以用sumif,或sumproduct等函数来实现） 初学函数的人对这公式的计算机理可能会有点晕：sum不就是汇总函数吗，怎么可以实现sumif的功能？公式里的“*”又是怎么回事？这不是相乘吗？干嘛不用“+”？ 在这公式里，A1:A10=苹果就是一个条件判断，在此，它会产生TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE这个结果，一级逻辑值 。逻辑值参于运算时，TRUE会自动转化为“1”来计算，FALSE则为“0”。这样TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE* B1:B1就得到32;0;0;26;0;45;0;40;0;47，用sum汇总就得到了最后结果。 运用这个原理，sum还可以达到countif的功能，就像=SUM(-(A1:A10=苹果)就等同于=COUNTIF(A1:A10,苹果)。里面有个两个减号，这叫做减负运算。因为逻辑值只有参于运算时才会转化为1与0，所以用这个运算来实现这个转化。当然，也可以这样：=SUM(A1:A10=苹果)*1)。注意，这两个sum的公式，都是数组公式。 Sumif函数是单条件求和的常用函数。它的格式为：SUMIF(range,criteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。 Sum_range 是需要求和的实际单元格。 =SUM(A1:A10=苹果)*B1:B10)这个结果与可以用：=sumif(A1:A120,”苹果”,B1:B10) 在公式里,第三个参数可以简写,公式可以写成：sumif(A1:A120,”苹果”,B1)，这是完全等同于=sumif(A1:A120,”苹果”,B1:B10)的。 Sumif函数支持通配符，比如：=SUMIF(A1:A10,*果,B1)，则是求得A1：A10里最后一个字为“果”的相对应项的B列的汇总。= =SUMIF(A1:A10,?果,B1)，就是只有两个字，而且第二个字为“果”字的B列的汇总。 Sumif函数还有一个特点：即条件在同列并保持与Sum_range水平距离一致，这里的“同列”指的如下图的A列、C列、E列、G列。 那么，公式：=SUMIF(A1:H10,苹果,B1)就会求得在A1：G10里所有“苹果“对应的数值的总和。 =VLOOKUP（查找目标判断的条件，查找区域，返回第几列的数据，FALSE或者0）=精确OR模糊查找。括号里有四个参数，是必需的。最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。两者有什么区别呢？前者表示的是完整寻找，找不到就传回错误值#N/A；后者先是找一模一样的，找不到再去找很接近的值，还找不到也只好传回错误值#N/A。一、入门级VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为：VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)下面以一个实例来介绍一下这四个参数的使用 例1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄。 公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0) 参数说明： 1 查找目标：就是你指定的查找的内容或单元格引用。本例中表二A列的姓名就是查找目标。我们要根据表二的“姓名”在表一中A列进行查找。 公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)2 查找范围（VLOOKUP(A13,$B$2:$D$8,3,0)）：指定了查找目标，如果没有说从哪里查找，EXCEL肯定会很为难。所以下一步我们就要指定从哪个范围中进行查找。VLOOKUP的这第二个参数可以从一个单元格区域中查找，也可以从一个常量数组或内存数组中查找。本例中要从表一中进行查找，那么范围我们要怎么指定呢？这里也是极易出错的地方。大家一定要注意，给定的第二个参数查找范围要符合以下条件才不会出错：A 查找目标一定要在该区域的第一列。本例中查找表二的姓名，那么姓名所对应的表一的姓名列，那么表一的姓名列（列）一定要是查找区域的第一列。象本例中，给定的区域要从第二列开始，即$B$2:$D$8，而不能是$A$2:$D$8。因为查找的“姓名”不在$A$2:$D$8区域的第一列。 B该区域中一定要包含要返回值所在的列，本例中要返回的值是年龄。年龄列（表一的D列）一定要包括在这个范围内，即：$B$2:$D$8，如果写成$B$2:$C$8就是错的。 3 返回值的列数（B13 =VLOOKUP(A13,$B$2:$D$8,3,0)）。这是VLOOKUP第3个参数。它是一个整数值。它怎么得来的呢。它是“返回值”在第二个参数给定的区域中的列数。本例中我们要返回的是“年龄”，它是第二个参数查找范围$B$2:$D$8的第3列。这里一定要注意，列数不是在工作表中的列数（不是第4列），而是在查找范围区域的第几列。如果本例中要是查找姓名所对应的性别，第3个参数的值应该设置为多少呢。答案是2。因为性别在$B$2:$D$8的第2列中。4 精确OR模糊查找（VLOOKUP(A13,$B$2:$D$8,3,0)），最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。第4个参数如果指定值是0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊。这里兰色提醒大家切记切记，在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了。 好了，关于VLOOKUP函数的入门级应用就说到这里，VLOOKUP函数可不只是这么简单的查找，我们讲的还只是1/10的用法。其他的没法在一篇文章中说明。敬请期待“VLOOKUP的使用方法-进阶篇”吧。