EXL公式收集 总结

[日期：2012-01-12]来源：作者：admin [字体：大中

小]

恳请高手指导一下,怎样能做到只对同行的数字进行求和,其它字符忽略不计呢?

=sumif(区域,">0") 这个我试了一下，返回是一个负号，没有数值，也没有公式报错，不知什么原因

SUM应该不计算文本的，直接用SUM求和！真是高手啊，我试了一下，解决！

=IF(F11>60,IF(AND(F11>90),"优秀","合格"),"不合格") 可以仿生借贷平

=IF(某项>0,IF(AND(某项＜0),"借","贷"),"平")

在excel中最简单的求和公式就是用SUM进行求和。即：

=Sum(数据区域)

如对A1：A100区域进行求和

=Sum(a1:a100)

如果有N个工作表中有格式相同的表格，需要合并求和时，可以用SUM函数的多表合并功能。即：

=SUM(表1:表N！A1）就可以对表1至表N之间的所有表A1进行汇总求和

例：汇总日报表，在汇总表C5中设置公式：

=SUM('1日:空白'!C5)

这是统计"1日"工作表至“空白”工作表之间的所有C5的和，这是之所以用“空白”工作表，是因为方便在空白前插入新的工作表，这些新插入的也会计算在内，如果最后一个设置为5日，那么这个汇总也只能计算5天了。

一个表格中有很多小计，当设置对这些小计求和的总计求和公式时，常常需要一个个的加小计，这样一方面公式太复杂，另一方面当小计行删除时公式会出错，当插入新的小计行时还需要再更新求和公式。

大家想想，总和是不是小计和加明细和的1/2，因为小计等于明细，而总和等于小计和，所以我们可以这样简化公式：

=SUM（A1：A100）/2 这样就可以算出A1：A100区域的小计和了。

有时候数字后需要显示单位，如：“1元/斤”，而这些带单位的数字是无法求和的，那么我们该怎么进行求和呢？方法很多，这里说一个截取法

=LEFT（ A1,LEN(A1)-3)*B1

上述公式中，假设置A1是带单位的单价，B1是数量，那么可以从A1中截取出数字部分，然后再进行求和。

很多表格中都有计划数和实际数，这时候我们需要进行隔列求和。隔列求和有一个比较简单的方法，就是用SUMIF取巧。

如下图中设置公式：=SUMIF($A$2:$F$2,"计划",A3:F3) 就可以计算出计划数。

隔行求和同理，就不再举例

单条件求和的方法也有很多，最常用的就是SUMIF函数，下面给出几个小例子，大家就照着学习吧：

1 销售金额大于2000的数值进行求和=SUMIF(B2:B9,">3000")

2 商品名称为A1的商品相对应的销售金额进行求和。=SUMIF(A2:A9,"A1",B2:B9)

3 要求为销售金额大于D2单元格的数值进行求和。=SUMIF(B2:B9,"> "&D2)

4 求B列中大于平均数的销售金额求和 =SUMIF(B2:B9,"> "&AVERAGE(B2:B9))

5 求商品名称包含“A”的销售金额之和=SUMIF(A2:A9,"*A1*",B2:B9)

6 商品名称第四五个字符为“A2”且字符总长度为6个字符的的销售金额之

和 =SUMIF(A2:A9,"???A2?",B2:B9)

1、使用SUMIF多条件求和：

符合入库数量大于4小于10的商品，对其入库金额求和

=SUM(C2:C10)-SUMIF(C2:C9,"<=4")-SUMIF(C2:C9,">=10")

=SUM(C2:C10)-SUM(SUMIF(C2:C9,{"<=4",">=10"}))

2、使用SUMPRDUCT函数进行求和：

供应商A1的冰箱入库型号有多少种

=SUMPRODUCT((B3:B11="A1")*(C3:C11="冰箱"))

供应商A3的洗衣机入库数量是多少

=SUMPRODUCT((B3:B11="A3")*(C3:C11="洗衣机")*E3:E11)

示例图：

累计和可以用indirect函数来实现，具体给一个日报表累计求和示例吧，大家下载后看看：

=INDIRECT(DAY(C2)-1&"日!D13")+D12

upload/2012_01/12011223432387.rar

按颜色求和需要借用宏表函数和定义名称：

=GET.CELL(38,Sheet1!C12)&T(NOW())

具体的大家看附件中的求和示例吧。

upload/2012_01/12011223453660.rar

Excel 以其强大的计算功能为我们处理工作表中的数据提供了极大的方便。笔者在使用Excel 汇总报表数据时发现，在建立和复制求和公式时仅用鼠标辅以个别符号键即可快捷地建立和复制公式，大大地提高工作效率。现以附表为例介绍如下：

⒈在表内建立求和公式

如果要将单元格B2至B5的数据之和填入单元格B6中，操作如下：

先选定单元格B6，双击编辑栏中的“=”，再双击【常用】工具栏中的求和符号“∑” ；接着用鼠标单击单元格B2并一直拖拽至B5，选中整个B2_B5区域，这时在编辑栏和B6中可以看到公式“=sum(B2：B5)”，单击编辑栏中的“√”(或按Enter键)确认，公式即建立完毕。此时如果在B2到B5的单元格中任意输入数据，它们的和立刻就会显示在单元格B6中。

同样地，如果要将单元格B2至D2的数据之和填入单元格E2中，也是采用类似的操作，但横向操作时要注意：对建立公式的单元格(该例中的E2)一定要在【单元格格式】对话框中的【水平对齐】中选择“常规”方式 , 这样在单元格内显示的公式不会影响到旁边的单元格。

如果还要将C2至C5、D2至D5、E2至E5的数据之和分别填入C6、D6和E6中，则可以采取简捷的方法将公式复制到C6、D6和E6中：先选取已建立了公式的单元格B6，单击【常用】工具栏中的【复制】