SUMMESLE的功能,以及SUMMESLES的两个标准

  1. 按标签搜索
隐形

»2011年6月11日 梅德 243582观看次数

想象一下一个表,其中部门(或帐户或其他)的名称在一行中列出。
想象一下一个表,其中部门(或帐户或其他)的名称在一行中列出。

按标准对细胞求和
有必要计算每个部门的总金额。 许多人使用过滤器和用笔在细胞中书写。
虽然只需一个功能即可轻松简单地完成 - SUMMESLI
SUMMESLES(SUMIF) - 减少满足给定条件的单元格(只能指定一个条件)。 如果按周期(每月,每月,三列 - 收入|费用|差异)将表分成列,则还可以使用此函数,并且您只需按收入,费用和差异计算所有期间的总金额。

SUMMESLI共有三个参数: RangeCriterionRange_Summing
= SUMMESLE(A1:A20000; A1; B1:B20000)
= SUMIF(A1:A20000,A1,B1:B20000)

  • 范围 (A1:A20000) - 表示符合标准的范围。 即 要在其中搜索Criterion参数指示的值的列。
  • 标准 (A1)是必须在范围中找到的值(文本或数字,以及日期)。 可能包含通配符“*”和“?”。 即 指定标准 “*质量*”以概括出现“质量”一词的值。 同时,“质量”一词可以出现在文本的任何地方,也可以在单元格中只有一个这个词。 并指定“质量*”,所有以“质量”开头的值将被总结。 “?” - 只替换一个字符,即 指定“mas?a”你可以用值“mass”和值“mask”等来总结这些行。
    如果标准是在单元格中写入的,并且您仍然需要使用通配符,则可以通过添加必需的单元格来链接到此单元格。 假设您需要总结包含“total”一词的值。 单词“total”写在单元格A1中,而在A列中可能有各种拼写值包含单词“total”:“totalals for June”,“totalals for July”,“totalals for March”。 那么公式应如下所示:
    =夏季(A1:A20000;“*”&A1&“*”; B1:B20000)
    “*”&A1&“*” - &符号(&符号)将多个值合并为一个。 即 结果是“*结果*”。
    为了更好地理解公式如何工作的原理,最好使用“ 计算公式”工具: 如何查看计算公式的步骤
    所有具有逻辑和数学符号的文本标准和标准必须用双引号括起来(= SUMMESLI(A1:A20000;“total”; B1:B20000))。 如果标准是数字,则不需要引号。 如果要直接找到问号或星号,则需要在其前面加上波浪号(〜)。
    关于波浪号及其功能可以在本文中找到: 如何更换/删除/查找星号?
  • Sum_Range(B1:B20000)(可选参数) - 指定要求和的和或数值范围。

工作原理:该函数在Range中搜索Criterion参数指定值,并在找到匹配时,对Range_Amount参数指示的数据求和。 即 如果我们在A列中有一个部门名称,在B列中有一个数量,那么指定开发部门作为标准将导致B列的所有值的总和,在A列中找到开发部门。 实际上,SumArrangement可能与Range参数的大小不同,这不会导致函数本身的错误。 但是,在定义求和单元格时,Range_Amount参数的左上角单元格将用作求和的起始单元格,然后将对Range参数的大小和形状对应的单元格求和。

一些功能
函数的最后一个参数(Sum_And_Band:B1:B20000)是可选的。 这意味着它无法指定。 如果未指定,则函数将添加Range参数指定的值。 它是什么。 例如,您只需要获得大于零的数字的总和。 在金额的A列。 然后该函数将如下所示:
=夏天(A1:A20000;“> 0”)

应该考虑的是: range_summing范围应该在行数上相等。 否则,您可能会得到错误的结果。 最佳地,如果它看起来像我给出的公式:求和的范围范围从一行开始并具有相同的行数:A1:A20000; B1:B20000

两个或多个标准的总和
但是当总和2和更多的标准时该怎么办? 假设您只需要总结那些属于一个部门的金额,而且只能用于特定日期。 Office 2007及以上版本的快乐所有者可以使用SUMMESLIMN功能:
= SUMMESLIMN($ C $ 2:$ C $ 50; $ A $ 2:$ A $ 50; $ I $ 3; $ B $ 2:$ B $ 50; $ H8)
$ C $ 2:$ C $ 50 - range_summing。 第一个参数指定包含将被收集到一个中的金额的单元格范围。
$ A $ 2:$ A $ 50,$ B $ 2:$ B $ 50 - Range_Criteria。 指定要按标准搜索匹配项的单元格范围。
$ I $ 3,$ H8 - 标准。 这里,和SUMMESLI一样,允许使用通配符*?。 他们以同样的方式工作。

指定参数的细节:首先,指定标准范围(它们被编号),然后直接用分号表示值(标准),在此范围内必须找到 - $ A $ 2:$ A $ 50; $ I $ 3。 没有别的。 您不应该首先指定所有范围,然后是它们的标准 - 函数将给出错误,或者它不会总结必要的内容。

根据原则I比较所有条件。这意味着如果满足所有列出的条件。 如果至少满足一个条件,则该函数会跳过该行并且不添加任何内容。
对于SUMMERS,求和和标准范围应该在行数上相等。

因为 SUMMESLIMN只出现在Excel版本中,从2007年开始,那么早期版本的不满用户在这种情况下怎么样呢? 很简单:使用另一个功能 - SUMPRODUCT。 我不会画这些论点,因为 它们中有很多,它们都是值的数组。 此函数将参数指示的数组相乘。 我将尝试描述使用此函数汇总几个条件的数据的一般原则。
要通过几个标准解决求和问题,函数将如下所示:
= SUMPRODUCT(($ A $ 2:$ A $ 50 = $ I $ 3)*($ B $ 2:$ B $ 50 = H5); $ C $ 2:$ C $ 50)
$ A $ 2:$ A $ 50 - 日期范围。 $ I $ 3是必须对数据求和的标准的日期。
$ B $ 2:$ B $ 50 - 各部门的名称。 H5 - 部门的名称,必须汇总的数据。
$ C $ 2:$ C $ 50 - 金额范围。

我们分析逻辑,因为 对许多人来说,仅通过查看此功能就完全不清楚了。 如果只是因为在帮助中没有描述这个应用程序。 为了提高可读性,请减小范围的大小:
= SUMPRODUCT(($ A $ 2:$ A $ 5 = $ I $ 3)*($ B $ 2:$ B $ 5 = H5); $ C $ 2:$ C $ 5)
因此,表达式($ A $ 2:$ A $ 5 = $ I $ 3)和($ B $ 2:$ B $ 5 = H5)是逻辑和返回逻辑FALSE和TRUE的数组。 如果范围$ A $ 2:$ A $ 5的单元格等于单元格$ I $ 3的值并且范围$ B $ 2:$ B $ 5的单元格等于单元格H5的值,则为TRUE。 即 我们有以下内容:
= SUMPRODUCT({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2:$ C $ 50)
如您所见,在第一个数组中,条件有两个匹配,第二个匹配。 此外,这两个数组相乘(乘法符号(*)对此负责)。 当发生乘法时,分别将数组FALSE和TRUE隐式转换为数字常量0和1({0; 1; 1; 0} * {0; 0; 1; 0})。 如您所知,当乘以零时,我们得到零。 结果是一个数组:
= SUMPRODUCT({0; 0; 1; 0}; $ C $ 2:$ C $ 50)
然后数组{0; 0; 1; 0}乘以$ C $ 2:$ C $ 50范围内的数字数组:
= SUMPRODUCT({0; 0; 1; 0}; {10; 20; 30; 40})
结果,我们得到了30.我们需要什么 - 我们只获得符合标准的金额。 如果有多个总和满足该标准,那么它们将被总结。

SUMMYROIZV的优势
如果参数有加号而不是乘号:
($ A $ 2:$ A $ 5 = $ I $ 3)+($ B $ 2:$ B $ 5 = H5)
那么条件将根据OR原则进行比较:即 如果满足至少一个条件,则总和将总计:$ A $ 2:$ A $ 5等于单元格值$ I $ 3或单元格$ B $ 2:$ B $ 5等于单元格值H5。
这是SUMMPRODUCT优于SUMMESLIMN的优势。 SUMMESLIMN不能根据OR原则总结值,只能根据AND原则(必须满足所有条件)。

缺点
SUMPRODUCT不能使用通配符*和?。 它可以更精确地使用,但它们不会被视为特殊字符,而是星号和问号。 我认为这是一个显着的劣势。 虽然这可以被绕过,但我在SUMPRODUCT中使用了其他功能 - 如果该功能可以某种方式使用通配符,它​​仍然会很棒。

在该示例中,您将找到几个函数示例,以便更好地理解上面所写的内容。

下载示例

金额按几个标准计算   (41 金额按几个标准计算 (41.5 KiB,10,477下载)

另见:
通过填充颜色求和单元格
按字体颜色求和单元格
按单元格格式对单元格求和
按填充颜色计算单元格数量
按字体颜色计算单元格数量
如何从多张表中总结数据,包括按条件

文章有帮助?与朋友分享链接!

视频教程

{“Bottom bar”:{“textstyle”:“static”,“textpositionstatic”:“bottom”,“textautohide”:true,“textpositionmarginstatic”:0,“textpositiondynamic”:“bottomleft”,“textpositionmarginleft”:24,“ textpositionmarginright“:24,”textpositionmargintop“:24,”textpositionmarginbottom“:24,”texteffect“:”slide“,”texteffecteasing“:”easeOutCubic“,”texteffectduration“:600,”texteffectslidedirection“:”left“,”texteffectslidedistance“ :30,“texteffectdelay”:500,“texteffectseparate”:false,“texteffect1”:“slide”,“texteffectslidedirection1”:“right”,“texteffectslidedistance1”:120,“texteffecteasing1”:“easeOutCubic”,“texteffectduration1”:600 ,“texteffectdelay1”:1000,“texteffect2”:“slide”,“texteffectslidedirection2”:“right”,“texteffectslidedistance2”:120,“texteffecteasing2”:“easeOutCubic”,“texteffectduration2”:600,“texteffectdelay2”:1500,“ textcss“:”display:block; padding:12px; text-align:left;“,”textbgcss“:”display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333;不透明度:0.6;过滤器:a lpha(opacity = 60);“,”titlecss“:”display:block; 位置:相对; font:bold 14px \“Lucida Sans Unicode \”,“Lucida Grande”,sans-serif,Arial; color:#fff;“,”descriptioncss“:”display:block; 位置:相对; 字体:12px \“Lucida Sans Unicode \”,“Lucida Grande”,sans-serif,Arial; 颜色:#fff; margin-top:8px;“,”buttoncss“:”display:block; 位置:相对; margin-top:8px;“,”texteffectresponsive“:true,”texteffectresponsivesize“:640,”titlecssresponsive“:”font-size:12px;“,”descriptioncssresponsive“:”display:none:important;“,”buttoncssresponsive“: “”,“addgooglefonts”:false,“googlefonts”:“”,“textleftrightpercentforstatic”:40}}

按标签搜索

访问 苹果手表 Multex公司 展望 Power Query和Power BI VBA在编辑器中工作 VBA代码管理 免费加载项 日期和时间 图表和图形 文件 数据保护 互联网 图片和对象 床单和书籍 宏和VBA 附加组件 调整 打印 搜索数据 隐私政策 邮件 节目 使用应用程序 处理文件 应用开发 汇总表 名单 培训和网络研讨会 金融 格式化 公式和功能 Excel功能 VBA功能 细胞和范围 MulTEx股票 数据分析 Excel中的错误和故障 引用