阅读是最好的习惯

【原】excel成本统计:如何进行区域筛选,多条件求和?

【原】excel成本统计:如何进行区域筛选,多条件求和?

编按:哈喽,大家好!最近有位小伙伴被一个计算产品成本的问题难住了,要求是根据配件成本核算出成品的成本。这个问题看上去似乎有点复杂,感觉一下子想不出好的解决办法,实际上,却非常简单,而且仅用常见的求和函数,就能轻松解决问题,话不多说,赶紧来看看吧!

【原】subtotal对筛选后的结果求不重复个数

小伙伴们,大家好!昨天群里一个同学问了一个问题,怎么样对筛选后的结果求不重复个数,这个问题把我难住了。不过群里的杨平老师给了一个公式,我也学习了一下。今天就来和小伙伴们分享一下。先来看下数据源和计算后的结果。A列是数据源,有生产部,销售部,财务部3个部门,但是有重复。所以未筛选前不重复的个数是3个。然后把财务部筛选出去,剩下生产部和销售部,所以筛选后不重复的个数是2。咱们先来说说未筛选前不重复个数的计算方法,其实之前说过很多次了。就是match=row的套路。公式为=SUMPRODUCT(N(M 更多

【原】Excel数据分析篇:数据分析必备的43个Excel函式

Excel是我们工作中经常使用的一种工具,对于资料分析来说,这也是处理数据最基础的工具。很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。对于初学者,有的时候并不需要急于苦学R语言等专业工具(当然会也是加分项),因为Excel涵盖的功能足够多,也有很多统计、分析、可视化的插件。只不过我们平时处理数据的时候很多函式都不知道怎么用。关于Excel的进阶学习,主要分为两块:一个是数据分析常用的Excel函数,另一个分享用Excel做一个简单完整的分析。这篇文章主要介绍资料分析常用的43个Ex 更多

*********

今天分享的这个问题来源于一位群友的求助,从平时的表现来看,这位朋友的技术还是不错的,经常为群里其他的小伙伴提供帮助,但是面对他自己的问题时,好像一下子没了办法。

其实他遇到的这个问题相信大家一看就能明白,问题并不是太难理解,但是解决起来好像不知道如何下手。问题如图所示:

就是生产企业核算产品成本的一类问题。

表中有很多种配件,不同的配件组合成一个个成品,相同配件在一个产品中只出现一次。现在的问题是根据配件成本核算出成品的成本,例如成品1的成本就是200.84+953.61+48.83=1203.28。

相信这个计算规则大家都能看明白,在实际环境中,产品和配件都远不止这9种,如果靠一个一个手工去算,效率低不说,还容易出错。

那么对于这样一个问题,有没有一个公式可以帮我们得到正确的结果呢?肯定有,而且不止一种方法。

今天和大家分享两个比较容易理解的公式。

公式1:SUM-SUMIF组合

具体公式为=SUM(SUMIF($J$2:$J$10,B2:G2,$K$2:$K$10)),来看一下操作方法。

这是个数组公式,输入完成后需要按Ctrl+Shift+Enter,会自动在公式两边添加大括号,得到结果。

公式的核心部分当然是SUMIF,关于这个函数的基础用法可以参考以前的教程《求和,我是认真的(Excel函数教程)》。

与基本用法不同之处在于,本例中SUMIF的第二参数,也就是求和的条件是一个区域:

当求和条件为多个值或者多个单元格时,SUMIF会得到一组数据,可以使用F9键看到这个结果。

通俗一点说,SUMIF在这里实现了将每个配件对应的成本计算出来,再由SUM完成合计成本的任务。

讲到这里,相信大家对于这个公式的套路应该明白了。 

可见一些看似麻烦的问题,只要找到正确的思路,用一些常用的函数就能搞定。

其实对于这个问题来说,用两个函数都有点多余,一个SUMPRODUCT就可以轻松解决。

公式2:SUMPRODUCT函数

具体公式为:=SUMPRODUCT((B2:G2=$J$2:$J$10)*$K$2:$K$10),来看一下操作方法。

这个公式看起来比第一个公式短,但是理解起来稍微有点难度,公式利用了逻辑值和数组的计算规则实现了最终的结果。

(B2:G2=$J$2:$J$10)这部分通过比较配置清单与配件名称列表中的每一个数据,得到一大堆逻辑值:

看起来密密麻麻的,其实仔细观察,还是有一定的规律性。例如六个逻辑值之间是一个分号,也就是说用B2:G2中的数据先与J2进行比较,没有相符的,得到一组FALSE,然后继续用B2:G2中的数据与J3进行比较,以此类推,直到与J10比较后才结束这个过程。

箭头所指的位置表示在第三轮比较中,匹配到配件3,因此得到一个TRUE,后面的都是如此。

虽然这一大堆逻辑值,看起来很多,但实际有用的只有TRUE。逻辑值有个特性,FALSE在计算时等于0,TRUE在计算时等于1。将比较结果与成本所在的区域相乘后,得到一组数字。

这样看起来就清楚了很多,SUMPRODUCT函数的功能只是对这一组数字求和。

是不是觉得SUMPRODUCT很厉害,关于这个函数,之前有好几篇教程,推荐大家再去看看,温故知新,一定会有所收获。

****部落窝教育-SUMPRODUCT函数区域筛选****

原创:老菜鸟/部落窝教育(未经同意,请勿转载)

【原】subtotal对筛选后的结果求不重复个数

小伙伴们,大家好!昨天群里一个同学问了一个问题,怎么样对筛选后的结果求不重复个数,这个问题把我难住了。不过群里的杨平老师给了一个公式,我也学习了一下。今天就来和小伙伴们分享一下。先来看下数据源和计算后的结果。A列是数据源,有生产部,销售部,财务部3个部门,但是有重复。所以未筛选前不重复的个数是3个。然后把财务部筛选出去,剩下生产部和销售部,所以筛选后不重复的个数是2。咱们先来说说未筛选前不重复个数的计算方法,其实之前说过很多次了。就是match=row的套路。公式为=SUMPRODUCT(N(M 更多

【原】Excel数据分析篇:数据分析必备的43个Excel函式

Excel是我们工作中经常使用的一种工具,对于资料分析来说,这也是处理数据最基础的工具。很多传统行业的数据分析师甚至只要掌握Excel和SQL即可。对于初学者,有的时候并不需要急于苦学R语言等专业工具(当然会也是加分项),因为Excel涵盖的功能足够多,也有很多统计、分析、可视化的插件。只不过我们平时处理数据的时候很多函式都不知道怎么用。关于Excel的进阶学习,主要分为两块:一个是数据分析常用的Excel函数,另一个分享用Excel做一个简单完整的分析。这篇文章主要介绍资料分析常用的43个Ex 更多

赞(0) 打赏
未经允许不得转载:微精选 » 【原】excel成本统计:如何进行区域筛选,多条件求和?
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏