excel分析方法.doc

从不同来源获取数据数据 导入外部数据导入数据 对数据进行排序，查找，有条件筛选，分类汇总 函数1、求和函数SUM格式：SUM(number1,number2,)功能：返回参数所对应数值的和。例如：在示例工作薄Sheet2表的A1:A4中分别存放着数据1114，如果在A5中输入=SUM(A1:A4,50)，则A5中的值为100，输入公式的具体操作步骤可以是：操作步骤一：（1） 单击A5单元格：输入：“=SUM(，”（2） 选择单元格A1:A4（用鼠标A1单元格拖动到A4单元格）；继续输入：“, 50)”。（3） 按Enter键。具体操作步骤也可以是操作步骤二：（1）单击A5单元格：点插入函数，会弹出插入函数选项板，在“搜索函数”对话框里输入“求和”，点“转到”或“回车”，弹出如图3.7所示的结果，找到相应的函数后再点确定。（也可以在分类里找，这一方法对不太熟悉函数的同学来说是比较有用的）（2）在函数参数Number1对话框里输入需要的单元格区域，系统会自动把所选单元格上面的区域或左侧的区域选中，也可以用步骤一的第二步来实现选中。在Number2里可以直接输入数值，见图3.8。（3）按Enter键。上例中，使用了两个参数，一个是单元格区域引用，一个是常数。其中第（2）步也可以直接输入A1:A4。输入公式时，若用鼠标选择单元格，则对应的相对引用形式就会出现在公式中。又如，如图3.3(工资表)所示的工作表中，其包含了15位教师的数据，若要将他们的应发工资总和存入E17单元格，则可以在E17输入公式：=SUM(E2:E16)。2、求平均值函数AVERAGE格式：AVERAGE (number1,number2,)功能：返回参数所对应数值的算术平均数。说明：该函数只对参数中的数值求平均数，如区域引用中包含了非数值的数据，则AVERAGE不把它包含在内。例：Sheet2表的A1:A4中分别存放着数据1114，如果在A5中输入=AVERAGE(A1:A4,50)，则A5中的值为20，即为(11+12+13+14+50)/5。又如，在上例中的A2单元格中输入文本：“郭靖”，则A5单元的值就变成了22，即为(11+13+14+50)/4，A2虽然包含在区域引用内，但并没有参与平均值计算。3、计数函数COUNT 格式：COUNT(number1,number2,)功能：统计参数列表中的数和包含数字的的单元格个数。同样用图3.3工资表这个例子，求出“工资表”表中A2:K2的11个单元格中的包含数值的单元格个数，结果就是10个。M2单元格“=COUNT(A2:K2)”。这一类函数还有COUNTIF（计算某个区域中符合给定条件的单元格数目），COUNTA（计算参数列表所包含的非空单元格数目），COUNTBLANK（计算某个区域中空单元格数目）。4、条件函数IF 格式： IF (logical-test,value-if-true,value-if-false)功能：根据条件logical-test的真假值，返回不同的结果。若logical-test的值为真，则返回value-if-true，否则，返回value-if-false。例如：如图3.3所示的工作表数据中，重新计算个人所得税，当实发数大于1600时，扣税为=(E2-1600)*$J$2,否则扣税为0。这时，H2单元格可以输入为：=IF(E21600,(E2-1600)*$J$2,0)。然后再利用填充柄向下将公式填充到各单元格中，这样，当应发数小于1600时，就不会被扣税。又如在如图3.2的工作表中，在总分旁增加一列“总评”，总评标准为：当总分大于等于270分时为“优”，若大于等于180且小于270时为“合格”，若小于180分时为“不合格”。其操作过程为：（1）先单击I1单元格，输入文字：总评。（2）再在I2中输入公式：=IF(H2=270,优,IF(H2=180,合格,不合格)。（3）最后利用填充功能，将I2中的公式复制到I2以下的单元格中。该例中使用了IF的嵌套功能，函数IF最多可以嵌套7层。5、逻辑与函数AND 格式：AND(logical1,logical2,)功能：所有条件参数logical1,logical2,（最多30个）的逻辑值均为真时返回TRUE；否则返回FALSE。如：“=AND(35,71,8=4+4)”，结果返回FALSE，因为35是假的。其中，参数必须是逻辑值，或者包含逻辑值的引用。如果引用的参数包含文字或空单元格，那么Excel将忽略它的值。如果指定的单元格区域内包含了其它值，那么AND将返回错误值#VALUE!（使用错误的参数或运算对象类型错误）。如图3.2所示的工作表中，对总分旁增加的“总评”更改标准，总评标准为：当三门功课的成绩都大于等于60时为“合格”，否则为“不合格”。操作过程为：在J2中输入公式：=IF(AND(E2=60,F2=60,G2=60),合格,不合格)；然后利用填充功能，将J2中的公式复制到J2以下的单元格中。6、逻辑或函数OR格式：OR（logical1,logical2,)功能：所有条件参数logical1,logical2,（最多30个）的逻辑值只要一个参数的逻辑值为真时返回TRUE；否则返回FALSE。其中，参数用法与AND函数相同。7、VLOOKUP一 语法VLOOKUP函数的语法结构如下：VLOOKUP (查找值,数据区域,返回列数,逻辑值)查找值为需要在数据区域的第一列中查找的数值，可以为数值、文本、引用、或者是公式计算出的结果。数据区域是包括查找值和返回值在内的区域，一般表示为$A$1:$B$10的形式，也可以将区域定义为一个名称。当逻辑值为TRUE的时候，数据区域的第一列中的必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，不能返回正确的数值。如果逻辑值为 FALSE，那么数据区域不必进行排序。返回列数在数据区域的第一列找到查找值后，返回查找值对应的那一行的第几列的单元格的内容。逻辑值指明函数返回时是精确匹配还是近似匹配。如果逻辑值为 TRUE 或1或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于查找值的最大数值。如果逻辑值为FALSE或0，将返回精确匹配值。如果找不到，则返回错误值 #N/A。二 结合例子讲解1 基本查找原理有这样一个数据区域A1:C10，当A12=6时，VLOOKUP(A12,$A$1:$B$10,2,TRUE)就表示在区域的第一列，查找等于6的单元格，即A6，然后在A6所在的第六行，返回第二列的值，即返回B6的值：FF。过程如下图：公式也可以直接写成=VLOOKUP(6,$B$1:$C$10,2,TRUE)，A12的地方直接填写6。当第一列没有6的数字的时候，将返回小于6的最大数值，即5所对应的第二列的内容：EE。由于是用的逻辑值为TRUE，因此第一列中如果没有按升序排列的话，结果将是错误的。大家可以看看讲义（ECXEL版）中的例子，例子在第二个工作表。看起来用TRUE的用处似乎不大，实际上在引用分段的数据的时候还是很方便的，如所得税、提成、成绩等。我们来看讲义中的“TRUE应用实例”工作表中的例子。请看讲义中的第三个工作表：可以看到评分表部分，第一列是已经升序排列的。注意看看，李一的50米是8.12秒，在评分表中是没有这个数据的，那么他对应的分数是表中8.11秒的分数，这时候直接用=VLOOKUP(E2,$A$3:$B$18,2,TRUE)就可以得出了。在例子旁尝试把逻辑值改成false试试：=VLOOKUP(E2,$A$3:$B$18,2,false)，则返回的是错误值#N/A。下面我们看看使用FALSE精确查找的情况，这是VLOOKUP最基本也是最常用的功能。对于数据量大的查找，其速度比菜单中的查找功能还快。精确查找适用于文本，也适用于数值；但对数值查找时须注意格式一致，否则会出错。请看下图：我们利用逻辑值false对号码进行精确查找，只有号码完全一致的时候，才返回对应的值，否则返回错误值#N/A。 其中逻辑值false也可以简写成0。以上讲的是VLOOKUP的最基本应用情况。2 容易产生的错误写法刚开始学习VLOOKUP这个函数的时候，对其语法理解有误，容易造成错误。常见的有这些情况： =VLOOKUP（A1:C10,A1:C10,3,0）这是将第一个参数写成一个区域了，实际上应该是一个单元格。严格的讲，并不是说第一个参数不能写成区域，写成区域属于数组运算，要用Ctrl+Shift+enter来确认，这将在以后有机会讲到数组的时候再详述。 =VLOOKUP（A12,C1:C10,3,0）这是将第二个参数误写成了单一列。在实际应用中，我们会发现，第二参数数据区域的列数，是必须大于或等于第三个参数的。也就是或，上面的公式第三个参数要求返回的是第3列，那么数据区域就必须有3列或以上。3 常见问题分析和变通使用首先我们来看看错误的分析。在使用VLOOKUP返回#N/A的时候，就表示在数据区域的第一列没有找到对应的值。有时候我们会很迷惑，在数据区域明明看到有这个值，为什么返回是错误的？这时候在数据区域用菜单的查找也确实是能找到的。这时首先注意一个情况，VLOOKUP查找时，查找的数据和区域第一列的数据必须格式是一致的，否则返回的也是错误值。我们来看看讲义中第一个表的23、24行的例子：由于A23、A24的格式是文本，而数据区域第一列的格式是数值，由于格式不一致不能精确查找，因此返回的是错误值。对于2003及以上的版本，我们通过单元格上的绿三角还比较容易区分格式是否不同，但在2000及以下版本中，只能通过看其属性了。没注意的话还比较难发现。知道问题就容易解决了。最简单的就是将A23、A24转换成数值，方法很多，不一一讲了。这里提一个直接在VLOOKUP中使用的方法，请看讲义中的第27、28行：=VLOOKUP(-A27,$A$1:$D$9,4,FALSE)这里的两个减号是直接将其转换的常用方法。假如是反过来的，数据区域的第一列是文本的，查找的是数值的，那么公式中可以这么用：=VLOOKUP(A27&,$A$1:$D$9,4,FALSE)即在A27后加