Office之Excel中筛选后求两列乘积之和

Office Excel 筛选后两列乘积之和

本文原作者：Champagne

扫码打开我的博客

前言

不论你是哪个行业，我相信，只要是隶属于制造业，BOM都是我们经常接触的东西。今天刚好需要做这方面的工作，但是如何对筛选后的两列乘积再求和，并没有太好的办法，庆幸的是，经过一上午的琢磨，这一问题最终被我解决。在这里分享出来，一方面给自己做个笔记，另一方面，希望大家以后不会再为这种类似的需求困扰。好了废话少说，下面进入正题。

需求分析

现在有如下BOM,需求如图所示：

原始数据

将表格需求拆分成图后，如下所示：

拆分需求

因此，我们需要做到以下几点：

1级总成为所有2级总成的单价×数量，之后再求和，即求层级为2的B列和C列乘积之和
在2级总成1下的3级零件1和3级零件2的单价为空时，2级总成1单价为500(为预算价格)，若后续项目进行更新，则按照实际价格来。要求能根据3级零件1和3级零件2的单价的有无自动判断是使用预算价格还是实际价格

求解过程

首先，我们需要知道两个知识点：

数组公式
嵌套

特别强调的一点是，Excel自带的SUMPRODUCT函数是无法对筛选后的两列求乘积之和的，其解释如下：

SUMPRODUCT函数释义

先说一下我自己对数组公式的理解，个人觉得网上解释的有些过于晦涩难懂，其实我们只需要记住一点，就是把其中的某一项作为判断条件来进行筛选即可。比如这里我把层级列作为判断条件，只计算层级为2的对应数据。那么数组公式就是={SUM((A3:A11=2)*(C3:C11*D3:D11))}。单元格内输入=SUM((A3:A11=2)*(C3:C11*D3:D11))，然后将光标定位到最右边，同时按Ctrl+Shift+Enter即可。C2=500*1+230*1+20*2=770。(A3:A11=2)为条件，多个条件之间用*连接。结果如下图所示。

下面再举一个例子，如C13结果所述，C13有两个筛选条件，层级为2且单价>100，然后再求两列乘积之和，也就是500*1+230*1=730。

筛选

说白了，嵌套就是大于等于2个判断条件。下面来解决2级总成1的单价问题，公式如下：
=IF(ISBLANK(C4),IF(ISBLANK(C5),500,SUMPRODUCT(C4:C5,D4:D5)),SUMPRODUCT(C4:C5,D4:D5))

其逻辑流程图如下：

流程图

ISBLANK(C4)：判断C4单元格是否为空
SUMPRODUCT(C4:C5,D4:D5)：C4单元格不为空，可以直接计算，因为C4有数据，故此时C3已经不可能为0
IF(ISBLANK(C5),500,SUMPRODUCT(C4:C5,D4:D5))：C4单元格为空
ISBLANK(C5)：判断C5单元格是否为空
SUMPRODUCT(C4:C5,D4:D5)：C5单元格不为空，此时代表C4单元格为空但C5单元格不为空，可以直接计算，因为C4无数据但C5有数据，故此时C3已经也不可能为0
500：表示C4单元格和C5单元格均为空，此时使用500代替预算价格
待以后补充数据后，如图所示：

补充数据

可以看出，不仅2级总成1会自动更新，1级总成也可以自动更新。完成！
点我下载实例文件