編按：

哈嘍，大家好，今天來給大家講一個數(shù)據(jù)匯總的問題，這個問題有點特別，即實際名稱與統(tǒng)計名稱并不一致，我們應(yīng)該如何處理呢？本文將給大家分享3種方法，感興趣的同學(xué)趕緊來看一看吧！

如圖所示，我們要將原始的數(shù)據(jù)表匯總統(tǒng)計后，形成右邊的統(tǒng)計表。 有個頭疼的問題是：兩個表中的名稱是不一致的，比如原始銷售區(qū)域中的上海大區(qū)，對應(yīng)的是統(tǒng)計表中的華中大區(qū)；原始物料名稱的燃氣灶具，對應(yīng)的是統(tǒng)計表中的灶具。

1、使用輔助列

這個問題的關(guān)鍵在于，我們需要形成一個對照表。 根據(jù)需求，分別是區(qū)域?qū)φ毡恚臀锪蠈φ毡?，我們來看一下效果?

整理出這兩個表以后，大家是不是有點頭緒了呢？ 首先，我們用兩個輔助列，先用VLOOKUP函數(shù)，把原始地區(qū)，和原始物料轉(zhuǎn)化為統(tǒng)計的地區(qū)名稱和統(tǒng)計的物料名稱。 在D2輸入公式：=VLOOKUP(A2,對照表!B:C,2,0)

在E2輸入公式：=VLOOKUP(B2,對照表!E:F,2,0)

有了輔助列，現(xiàn)在我們就可以在統(tǒng)計表愉快地使用公式了！ 在H3輸入公式： =SUMIFS($C:$C,$D:$D,H$2,$E:$E,$G3)

這是一個sumifs多條件求和的公式，需要注意的是相對引用和絕對引用，不然拖拽公式后的結(jié)果會不正確。 如下圖所示，我們再在合計處，分別輸入sum公式，向下向右填充，就可以得到最終的結(jié)果。

二、SUMPRODUCT函數(shù)一步到位

如果你不想用透視表，也不想用輔助列，可以用這個方法。

在F2輸入公式：

=SUMPRODUCT((VLOOKUP(T(IF({1},$A$2:$A$593)),區(qū)域?qū)φ誟#全部],2,0)=F$1)*

(VLOOKUP(T(IF({1},$B$2:$B$593)),物料對照[#全部],2,0)=$E2)*$C$2:$C$593)

數(shù)組公式，輸完后記得Shift+Ctrl+Enter三鍵結(jié)束。

這個公式有點長，VLOOKUP的第一參數(shù)是數(shù)組的時候，需要用T函數(shù)來降維(T(IF({1},$A$2:$A$593)） 用VLOOKUP查找的結(jié)果分別和品類，和物料名稱進行比對形成兩列數(shù)組,進行相乘后,再乘發(fā)貨數(shù)量，最后用sumproduct求和，得到多條件求和的結(jié)果。

三、沒有什么是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") '標準區(qū)域名稱

Set prngs = Range("G3:G14") '標準物料名稱

cr = Range("a2", Cells(Rows.Count, "c").End(xlUp)) '把數(shù)據(jù)源寫入數(shù)組

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) '把標準的區(qū)域?qū)φ毡韺懭胱值?，方便取?/p>

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)?'重新定義字典的大大小，使起剛好可以容納統(tǒng)計值

?For k = 1 To UBound(cr)

??????coln = WorksheetFunction.Match(d(cr(k, 1)), qrngs, 0)????'獲取需要累加的值，在數(shù)組的行的位置

??????rown = WorksheetFunction.Match(dic(cr(k, 2)), prngs, 0)?'獲取需要累加的值，在數(shù)組的列的位置

??????

??????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??'把計算完畢的數(shù)組寫入單元格中

MsgBox Timer - t???????????????????????'計時結(jié)束

End Sub

好的，以上就是今天的分享，3種方法。

做Excel高手，快速提升工作效率，部落窩教育

《一周Excel直通車》

視頻和

《Excel極速貫通班》

直播課全心為你！

學(xué)習交流請加微信：hclhclsc進微信學(xué)習群。

相關(guān)推薦：

如何按數(shù)據(jù)區(qū)間匯總求和？介紹5個實用的數(shù)據(jù)分析公式

你會累計求和嗎？這5個技巧簡直太好用了！

別怕，VBA入門級教程來了，條件語句很簡單！

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者柳之；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。