用Sumifs函数计算价格

哎,你们那些经常做表的表兄弟、表姐妹、表兄弟、小编,有没有遇到过下面这种单价的多条件、区间询价?图01

用Sumifs函数查找价格

为了方便起见,我把匹配区域(A1:D10)和结果区域(F2:K4)放在一起。

需求是查询一个品类在相应时间段的价格。

这个时间段对很多人来说真的很难。如果值是固定的,可以使用辅助列,将它们链接在一起,然后使用Vlookup函数来查找。

但是在那个时间范围内找有点难!不要怕,知道规则的话,方法会比难度大很多。我不是在图中列出了三个公式吗?

解决方案1:

常规逻辑,既然是求返回值,就可以用函数Vlookup,Hlookup,lookup。Hlookup在这里不适用,用Vlookup“也不行”,就用lookup。毕竟Lookup还是很强大的。看一级方程式,图02。

用Sumifs函数查找价格

公式如下:

=LOOKUP(,0/(($ A $ 3:$ A $ 10 = F3)*($ B $ 3:B $ 10 & lt;= G3)*($ c $ 3:$ c $ 10 & gt;=G3)),$D$3:$D$10)

说明:参数1没什么损失,参数2比较长。一步一步来,理顺了就明白了。

A3:A10和F3进行比较,要求相同,所以采用相等比较。

B3:B10与G3进行比较,B列是开始日期,因此它必须小于或等于G3。

C3:C10与G3相比。列C是结束日期,因此它必须大于或等于G3。

比较结果不是真就是假。

看一下测试图03。

用Sumifs函数查找价格

为了方便对比,我把它们包起来,这样比较方便。

这个结果是怎么出来的?勾选$A$3:$A$10=F3,然后按F9。最后两个是一样的。

这时候你可能会有点晕,不明白。简单解释一下。不是分三段对比吗?

这些都是比较结果,符合条件为真,不符合为假。

在有了比较结果之后,应该将三个段落的结果结合起来。这里,*用于运算,*是乘法。

只有当True * True时才会返回True(和effect),

所以我们来看看计算结果吧!图04

用Sumifs函数查找价格

哎,怎么变成1和0了?因为在Excel中,True可以用1表示,Fasle可以用0表示。做数学运算后,会自动转换成数字!

将这些内容除以0,以便将0转换成误差值。再看图05。

用Sumifs函数查找价格

为什么要转换成误差值呢?

我们的星女神很久以前就发了一个查找函数的详细解决方案。有兴趣的话,请搜出来了解一下。毕竟Lookup也很强大。

查找的思路就在这里。用它来求解,因为它是一个查找函数,可以返回任意值,同时可以帮助我们理清思路!

解决方案2:

在这里,我们要返回的值是一个数字,并且限定结果是唯一的,那么我们就可以使用求和函数了!

继续刚才的思路,改和函数继续测试,看公式2和图06。

用Sumifs函数查找价格

公式如下:

= SUM(($ A $ 3:$ A $ 10 = F3)*($ B $ 3:B $ 10 & lt;= G3)*($ c $ 3:$ c $ 10 & gt;=G3)*$D$3:$D$10)

Lookup和Lookup的区别在于,Lookup的D3:D10作为参数3,而Sum中仍然使用*进行计算。原理和查找参数2一样!

需要注意的是,这是一个数组公式。输入后要用Ctrl+Shift+Enter完成,否则结果可能不正确。

使用office365(版本12725.200006)的用户不需要三个键,它可以自动识别并计算出正确的值。

看到这里,你也会说:我不想求平安,我该去找吗?

解决方案3:

因为Sum函数可用于查找结果,所以我们可以使用Sumifs函数返回所需的值。

如果使用Sumifs函数,就简单多了。见公式图07。

用Sumifs函数查找价格

第五行用公式返回公式字符数,可以看出Sumifs字符最少(缺少行号和绝对引号)。

=LEN(公式文本(JBOY3乐队))

为什么这个搜索问题可以用求和函数解决?因为它满足以下两个条件,

第一,我们需要返回的结果是一个数字;

第二:合格结果是唯一的。

如果其中一个不满足,就不能使用Sumifs函数!

要解决问题,可以从特性入手解决,得到更好的解决方案。如果想通用(比如返回的结果可能有数字和字符串),那么公式会更复杂(使用Lookup)!