欢迎转发和点一下“在看”,文末留言互动! 置顶公众号或设为星标及时接收更新不迷路 小伙伴们好,今天来和大家分享一道分层级汇总的题目。在生产性企业中工作过的朋友们都知道,每一款产品都有一个BOM表,在里面详细列明了所有零件的层级关系和数量。今天这道题目就是和BOM相关的。 原题是这样子的: 要求来计算每个层级的总和。如果当前层级有数值,则不用计算。 这个结构看起来是很熟悉的,但是公式你们熟悉吗? 01 MATCH函数定位、OFFSET函数偏移、SUMIF函数求和 我们一起来看看该如何书写这个公式。如果还不理解的朋友们,可以先收藏起来,待遇到实际问题时参考套用。 在单元格C2中输入公式“”,确认后向下拖曳即可。 =IF(B2,B2,SUMIF(OFFSET(A3,,,IFNA(MATCH(A2,A3:A99,),99)),A3,C3)) 这个题目的难点在于,如何找到分层点以及如何确认每一层的高度。 MATCH(A2,A3:A99,) 利用MATCH来确定当前层级的下一个位置。这样做的目的是要来确定求和的区域。 IFNA(MATCH(A2,A3:A99,),99) IFNA函数用来纠错,如果当前层级是最后一层且在源数据中是最后一位,那么就人为地创造一个区域。 OFFSET(A3,,,IFNA(MATCH(A2,A3:A99,),99) OFFSET函数部分,以单元格A3(即当前层级的下一位)为极点向下移动,同时规定移动后的区域高度是99层。 这个99是任意写的,只要足够大即可。 SUMIF(OFFSET(A3,,,IFNA(MATCH(A2,A3:A99,),99)),A3,C3) 接下来SUMIF函数出马按条件求和。这里SUMIF函数的第三个参数C3是简写形式,请注意!它代表的是以单元格C3为首的单元格区域,高度和第一个参数OFFSET函数的结果相同。 IF(B2,B2,SUMIF(OFFSET(A3,,,IFNA(MATCH(A2,A3:A99,),99)),A3,C3)) 最后,根据题意用IF函数来做一个逻辑判断,执行不同的操作 |
|