二维码 购物车
部落窝在线教育欢迎您!

Excel教程:汇总数据时,统计名称和实际名称不一致怎么办?

 

作者:柳之来源:部落窝教育发布时间:2022-06-24 11:31:18点击:376

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽,大家好,今天来给大家讲一个数据汇总的问题,这个问题有点特别,即实际名称与统计名称并不一致,我们应该如何处理呢?本文将给大家分享3种方法,感兴趣的同学赶紧来看一看吧!

如图所示,我们要将原始的数据表汇总统计后,形成右边的统计表。

有个头疼的问题是:两个表中的名称是不一致的,比如原始销售区域中的上海大区,对应的是统计表中的华中大区;原始物料名称的燃气灶具,对应的是统计表中的灶具。

1、使用辅助列

这个问题的关键在于,我们需要形成一个对照表。

根据需求,分别是区域对照表,和物料对照表,我们来看一下效果。

整理出这两个表以后,大家是不是有点头绪了呢?

首先,我们用两个辅助列,先用VLOOKUP函数,把原始地区,和原始物料转化为统计的地区名称和统计的物料名称。

D2输入公式:=VLOOKUP(A2,对照表!B:C,2,0)

E2输入公式:=VLOOKUP(B2,对照表!E:F,2,0)

有了辅助列,现在我们就可以在统计表愉快地使用公式了!

H3输入公式:

=SUMIFS($C:$C,$D:$D,H$2,$E:$E,$G3)

这是一个sumifs多条件求和的公式,需要注意的是相对引用和绝对引用,不然拖拽公式后的结果会不正确。

如下图所示,我们再在合计处,分别输入sum公式,向下向右填充,就可以得到最终的结果。

二、SUMPRODUCT函数一步到位

如果你不想用透视表,也不想用辅助列,可以用这个方法。

F2输入公式:

=SUMPRODUCT((VLOOKUP(T(IF({1},$A$2:$A$593)),区域对照[#全部],2,0)=F$1)*
(VLOOKUP(T(IF({1},$B$2:$B$593)),物料对照[#全部],2,0)=$E2)*$C$2:$C$593)

数组公式,输完后记得Shift+Ctrl+Enter三键结束。

这个公式有点长,VLOOKUP的第一参数是数组的时候,需要用T函数来降维(T(IF({1},$A$2:$A$593))

VLOOKUP查找的结果分别和品类,和物料名称进行比对形成两列数组,进行相乘后,再乘发货数量,最后用sumproduct求和,得到多条件求和的结果。

三、没有什么是VBA解决不了的

如果你喜欢用VBA,也可以参考一下下面的VBA代码。当然,如果你有更好的思路也欢迎和我们交流分享。

Sub test2()

 

t = Timer           '计时开始

Dim d As Object, dic As Object, rarr()

Dim cr, i, j, k, dr, er, fr, prngs, qrngs As Range

 

Set qrngs = Range("H2:L2") '标准区域名称

Set prngs = Range("G3:G14") '标准物料名称

cr = Range("a2", Cells(Rows.Count, "c").End(xlUp)) '把数据源写入数组

Set d = CreateObject("scripting.dictionary")

Set dic = CreateObject("scripting.dictionary")

 

With Sheets("对照表")

    dr = .Range("b2", .Cells(Rows.Count, "c").End(xlUp))

    er = .Range("e2", .Cells(Rows.Count, "f").End(xlUp))

End With

 

For i = 1 To UBound(dr)

    d(dr(i, 1)) = dr(i, 2) '把标准的区域对照表写入字典,方便取值

Next i

For j = 1 To UBound(er)

    dic(er(j, 1)) = er(j, 2)  '把标准的物料名称对照表写入字典,方便取值

 

m = prngs.Count: n = qrngs.Count

Range("h3").Resize(m, n).ClearContents

ReDim rarr(1 To m, 1 To n)  '重新定义字典的大大小,使起刚好可以容纳统计值

 For k = 1 To UBound(cr)

       coln = WorksheetFunction.Match(d(cr(k, 1)), qrngs, 0)     '获取需要累加的值,在数组的行的位置

       rown = WorksheetFunction.Match(dic(cr(k, 2)), prngs, 0)  '获取需要累加的值,在数组的列的位置

      

       If rarr(rown, coln) = "" Then

       rarr(rown, coln) = cr(k, 3)

       Else

       rarr(rown, coln) = rarr(rown, coln) + cr(k, 3)

       End If

 Next k

 

Range("h3").Resize(m, n) = rarr   '把计算完毕的数组写入单元格中

MsgBox Timer - t                        '计时结束

End Sub

好的,以上就是今天的分享,3种方法。

不知道在这次Excel大考中,你能读个什么大学呢?

有什么问题,欢迎给我们留言,说说你的想法。

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何按数据区间汇总求和?介绍5个实用的数据分析公式

你会累计求和吗?这5个技巧简直太好用了!

别怕,VBA入门级教程来了,条件语句很简单!

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者柳之;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。