EXCEL公式SUMIFCOUNTVLOOKUP数据透视表.docx

前言：本文档中公式设置的基础是OFFICE07及以上版本，WPS或者OFFICE03大同小异，可能位置不一样，请自行百度解决版本及位置差异一、SUM函数SUM函数的语法是：=SUM(number1，number2，)，意思是求number1numberN的和(一) 其中number可以是数字，也可以是数组1、数字的情况是：在单元格内输入： =SUM(1，2，3) 然后点击Enter键结果就是：62、数组的情况是：按下表情况来讲，26的公式组成为：=SUM(B2:B5)42的公式组成为：=SUM(C2:C5) 68的公式组成为：=SUM(B2:B5, C2:C5)=SUM(B2:C5) 或者=SUM(B6:C6)如果要看到公式编制的具体情况，请双击上一个图表就能看到(二) SUM函数可以自己手动编制，也可以用EXCEL菜单直接生成1、手动编制就是自己手工输入，选中需要编制公式的单元格，直接输入公式2、EXCEL菜单直接生成就是利用EXCEL菜单栏预先设置的公式直接生成自己需要的公式在“开始”菜单右上角有图标选中需要求和的数字区域，然后点击该图标，这种方法的缺点是有时候会找不到求和的单元格在哪里；也可以先选中要生成求和数值的单元格，然后点击该图标，然后再选择需要求和的数字区域；即可自动生成求和数据。二、VLOOKUPVLOOKUP函数的表达公式为:=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)=VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）关于精确匹配或近似匹配，目前工作中遇到的所有的情况都用精确匹配来作为参数，实际表达为FALSE或者0如果作为逻辑值运算，1表示TRUE（所有非零值也作为TRUE），0表示FALSE所以实际工作中VLOOKUP函数简化为：=vlookup(查找值,查找范围,查找列数,0)（最后一个必须是0，不可以是其他数字）通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：以第一列为准，往后推数查找列数值的这一列所对应的值。以核对8月售后产值报表和收银报表为例首先从DMS导出结算清单，报表管理维修报表业务结算明细查询，日期选择8-18-31，点击查询，然后EXCEL导出。导出的数据界面是下面这样的找到两个表都有的，具有唯一性的参数作为查找值，本例中就是工单号。在任何位置插入一列，本例中在工单号后面插入一列，完成后，新插入的列就是E列，选中E列中的任何一个单元格，所显示的格式都是“文本”格式，在编写公式前，选中E列，将这一列的格式调整为“常规”，如下面第二个图所示处理完结算明细报表以后，打开收款报表。然后在结算明细报表里面编写公式将鼠标放在E6单元格上，单击单元格，该单元格上出现加粗的黑色框框输入=vlookup(这时就会出现公式提示，如下图所示，只做参考，不用管它点击D6单元格后，加“,”，现在公式就变成=vlookup(D6,现在点击收款报表，开始选中C:AI列，然后写“,”；在选中的过程中，请留意大概在右上方的一个位置，有一个数字+C的显示，这个就是选中了多少列的意思，在本例中从C列到AI列，合计33列，所以在公式中第三部分就是33写完33以后，写“,”然后是“0”，“）”点击ENTER键本公式就完成了如果两个表格在同一个文件夹，全部公式就是=VLOOKUP(D6,2016年收款统计表（丽江金鸿运）.xlsx明细表!$C:$AI,33,0)整个公式的意思就是我们从收款统计报表中，工单号那一列里面将ARO16070041这一工单号所对应的收款金额取出来，金额结果就是1500。这一个单元格的公式编写就完成了，下面是整列公式，很简单。双击右下角的黑色的小点，那么整列的公式都会出现，凡是出现乱码标志的，就是取不到数的，要么是反结算了，要么是我们漏更了，要么就是工单号错了，或者就是ARO的字母O写成数字0了。公式编写需要注意的地方：1、每个部分完成后，“,”一定不能忘记2、查找的标准值不要忘记点击，在本例中就是D6，三、COUNTCount函数就是计数的意思以收款报表R列为例，在R1单元格输入=COUNT(R3:R1016)，点击ENTER键结果就是565，表示选中的这些单元格中，有565个数字拓展：countif要计算收款报表中一共有多少个“索赔”在E1单元格输入=COUNTIF(E4:E1016,索赔) 结果就是78，代表E4:E1016中有78个索赔单子；之所以用”索赔”，而不是用索赔汉字，因为EXCEL无法识别直接的汉字，所以必须加”，将汉字转换为EXCEL能够识别的文本才能别写公式。四、IF函数以2016年收款统计表（丽江金鸿福）毛利率为例，讲解IF函数的使用方法这个AH5单元格所使用的是两个函数：iferror函数和if函数Iferror是排除错误的，就是当除数是0的时候，显示的是乱码，iferror的作用就是将乱码变成0，或者任何其他数字。此公式里面就是如果结果是乱码的话，就显示为0。下面重点讲解IF函数：1、使用IF函数的原因：销售收入毛利率的计算公式为：销售收入-销售成本销售收入其中D5代表“收款类型”，AG5代表“实收合计”，S5代表“配件成本”； 转换为本表中就代表：AG5-S5AG5 也就是（AG5-S5）/AG5 一般来讲毛利率的公式为：=（AG5-S5）/AG5 也就够了，但是由于本表中的数据不只有售后的数据，还有新车的数据，毛利率就只有售后数据才能使用到，新车不需要，如果是新车的数据，那么就不处理，显示为0；所以就需要用IF函数将售后的数据提出来。IF函数的语法是=IF(logical_test,value_if_true,value_if_false)=IF(条件,满足条件的结果,不满足条件的结果)公式里面if函数的语法是：IF(D5=新车,0,(AG5-S5)/AG5)公式分为3个部分，每个部分用英文状态下的“,”隔开1、D5=新车2、03、(AG5-S5)/AG5第1部分是条件，代表：如果该行是”新车”第2部分是满足条件的结果：如果是”新车”，那么本单元格显示为0第3部分是不满足条件的结果，如果不是”新车”，那么就显示毛利率 言外篇1： 这个公式还可以变形为：= IF(D5=售后,(AG5-S5)/AG5,0) 言外篇2： 使用IF公式编制个人所得税计算公式 个人所得税税率表为：级数全月应纳税所得额（含税级距）【税率资讯网提供】全月应纳税所得额（不含税级距）税率(%)速算扣除数1不超过1,500元不超过1455元的302超过1,500元至4,500元的部分超过1455元至4155元的部分101053超过4,500元至9,000元的部分超过4155元至7755元的部分205554超过9,000元至35,000元的部分超过7755元至27255元的部分251,0055超过35,000元至55,000元的部分超过27255元至41255元的部分302,7556超过55,000元至80,000元的部分超过41255元至57505元的部分355,5057超过80,000元的部分超过57505元的部分4513,505首先新建一个任意工作簿，在A1单元格输入应发工资，A2单元格输入任何数字在B1单元格输入免税金额，A2单元格输入3500在C1单元格输入应纳税所得额，C2单元格输入A1-B1在D1单元格输入应纳税额，D2单元格输入公式、下面编写第一级、第二级公式；1、首先排除3500以下的工资金额，公式为：=IF(C2=0,0)输入完毕后点击Enter键就会看见显示的是0或者”-”， 这两个显示的区别是设置的数字格式不一样，看自己需要显示哪一个2、然后在公式里面增加应纳税所得额不超过1500的级别=IF(C2=0,C2=0,C2=0,C2=1500) 代表的是: 0=C2=1500但是由于EXCEL无法识别0=C2=0,C2=1500) 来代替。下面在A2单元格输入4000，那么，应纳税额显示为153、现在在公式里面增加应纳税所得额超过1500，但是不超过4500 的级别 =IF(C2=0,C21500,C2=4500),C2*0.1-105)红字部分为本级公式增加的部分，在A2单元格输入6000，那么显示应纳税额为145。4、剩余的5个级别的公式依次增加就可以了，有兴趣就可以自己学习。需要注意的是最后面括号的数量要随着IF公式的增加而增加，最简单的方法是这个公式里面有几个IF，那么最后就必须有几个“）”五、数据透视表数据透视表是一种EXCEL自带的表格计算模板，可以进行分类、汇总、计数、均值等；例如：2016年收款统计表（丽江金鸿福）表中的日报表打印功能，就是使用了数据透视表。使用数据透视表前，要统一需要处理的数据的格式；不能有合并单元格，所有的标题必须在同一行，标题栏不能有空格。如果在操作中遇到不能插入数据透视表的情况，问题基本是出在这三项上。选中要处理的数据，在本例中就是选中明细表中的数据，从A3开始选择，一直到自己需要的位置为止。如上图所示，我选中的是A3:AH20146选择插入 数据透视表 数据透视表出现以下对话框，选择新工作表下面是新建的数据透视表的功能区及功能区标题，现在青色的方框框住的区域有两个，左边的方框是最后的成表区；右边的方框是字段区，所谓的字段就是数据源的标题区，每一个字段代表的是数据源的一列数据。蓝色的方框框住的就是数据源的字段，比如序号代表明细表的A列、“序号”列；然后依次类推红色的圈圈代表成表区的位置，报表筛选对应的就是日报表打印中的日期，将日期字段投入“报表筛选”4个字下面的白色区域就会在相应位置出现“日期”字段；“列标签”相当于EXCEL的A、B、C、D、E；行标签就相当于EXCEL的1、2、3、4、5行；数值区域放置自己需要的数据字段。以日报表打印为例：将日期字段拖入报表筛选，将收款类型、维修类型字段依次拖入行标签字段，将收款方式依次拖入数值字段，就会出现日报表打印的成表了。正常情况下出现的是以下两种成表格式会出现的问题：1、如果出现如下图的问题，那么就是字段的顺序出现问题了，调整字段顺序就可以了；将大的字段放在上面，明细字段放在下面，就正常了2、如果想要求和，但是出现的是这样的计数项修改方式为：点击需要修改的数值字段里面的向下的黑色箭头，如下图所示，在出现的菜单上点击“值字段设置（N）”在出现的对话框里面选择求和，点击确定就是求和了。3、如果觉得“计数项：XX” “求和项：XX”