導(dǎo)讀大多數(shù)情況下，我們對(duì)表格統(tǒng)計(jì)都會(huì)在同一張工作簿進(jìn)行，這樣操作起來(lái)非常方便，不過(guò)有時(shí)也會(huì)進(jìn)行跨工作簿統(tǒng)計(jì)。跨工作簿條件求和最常見(jiàn)的兩個(gè)問(wèn)題：01跨工作簿區(qū)域應(yīng)....

大多數(shù)情況下，我們對(duì)表格統(tǒng)計(jì)都會(huì)在同一張工作簿進(jìn)行，這樣操作起來(lái)非常方便，不過(guò)有時(shí)也會(huì)進(jìn)行跨工作簿統(tǒng)計(jì)。

跨工作簿條件求和最常見(jiàn)的兩個(gè)問(wèn)題：

01 跨工作簿區(qū)域應(yīng)該如何寫？

02 關(guān)閉工作簿后，統(tǒng)計(jì)出來(lái)的結(jié)果變成錯(cuò)誤值怎么回事？

條件求和，首先想到的是用SUMIF函數(shù)進(jìn)行統(tǒng)計(jì)。

函數(shù)語(yǔ)法：

=SUMIF(條件區(qū)域,條件,求和區(qū)域)

其實(shí)跨工作簿的情況下，區(qū)域的選取跟在同一個(gè)工作簿一樣，都是用鼠標(biāo)選取，而不是手寫。有一點(diǎn)必須要記住：兩個(gè)工作簿必須同時(shí)打開(kāi)。

鼠標(biāo)選取區(qū)域詳見(jiàn)動(dòng)畫

最終公式為：

=SUMIF([跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B:$B,A2,[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D:$D)

一旦將跨工作簿統(tǒng)計(jì)1.xlsx關(guān)閉，修改統(tǒng)計(jì)月份，金額就變成錯(cuò)誤值。

在Excel中，并不是所有函數(shù)都支持跨工作簿，如SUMIF、COUNTIF函數(shù)就不支持，而VLOOKUP、SUMPRODUCT函數(shù)就支持。這里可以借助SUMPRODUCT函數(shù)實(shí)現(xiàn)跨工作簿統(tǒng)計(jì)。

函數(shù)語(yǔ)法：

=SUMPRODUCT((條件區(qū)域=條件)*求和區(qū)域)

我們重新看一下出錯(cuò)的單元格，公式變成：

=SUMIF(C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B:$B,A2,C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D:$D)

C:UserschenxiluDesktop這個(gè)是路徑，意思就是說(shuō)這個(gè)表格存在盧子電腦的桌面。

[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1這個(gè)是工作簿名稱跟工作表名稱。

不要看公式很長(zhǎng)，其實(shí)拆分開(kāi)真的沒(méi)什么，都是很簡(jiǎn)單的東西。

SUMPRODUCT函數(shù)不支持引用整列，這里只要將原來(lái)的區(qū)域改小，稍作變動(dòng)就完成了最終的統(tǒng)計(jì)。

=SUMPRODUCT((C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B$2:$B$100=A2)*C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D$2:$D$100)

這樣即使工作簿不打開(kāi)的情況下，也能正確統(tǒng)計(jì)。

SUMPRODUCT函數(shù)也可以換成SUM函數(shù)，不過(guò)需要按Ctrl Shift Enter三鍵結(jié)束。

=SUM((C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$B$2:$B$100=A2)*C:UserschenxiluDesktop[跨工作簿統(tǒng)計(jì)1.xlsx]Sheet1!$D$2:$D$100)

這2個(gè)函數(shù)90%的情況下可以互相代替，用SUMPRODUCT函數(shù)的好處就是支持?jǐn)?shù)組公式，不用按三鍵。

再說(shuō)一個(gè)特殊案例，就是在輸入數(shù)據(jù)的時(shí)候，可能會(huì)中間出入文本，多敲個(gè)空格之類的，如果直接求和會(huì)出錯(cuò)。

用*這種方法，文本*數(shù)字就是錯(cuò)誤值，不管用SUMPRODUCT函數(shù)還是SUM函數(shù)都無(wú)法避免出錯(cuò)。

不過(guò)SUMPRODUCT函數(shù)還隱藏了另外一種用法，參數(shù)用,（逗號(hào)）隔開(kāi)，可以將文本當(dāng)做0處理。

=SUMPRODUCT(--(MONTH($A$2:$A$26)=F2),$D$2:$D$26)

語(yǔ)法：

=SUMPRODUCT(數(shù)字區(qū)域1,數(shù)字區(qū)域2,數(shù)字區(qū)域3)

這里的(MONTH($A$2:$A$26)=F2)得到的是邏輯值，并不是數(shù)字，所以用--轉(zhuǎn)換成數(shù)字1、0，從而可以正確求和。

關(guān)于條件計(jì)數(shù)，可以看看COUNTIF與SUMPRODUCT這倆函數(shù)過(guò)招！

1.下面請(qǐng)看第一場(chǎng)比賽：如何統(tǒng)計(jì)值班經(jīng)理的值班次數(shù)？

COUNTIF函數(shù)首先應(yīng)戰(zhàn)，在H2單元格輸入公式，并向下填充。

=COUNTIF(A:A,G2)

COUNTIF函數(shù)語(yǔ)法：

=COUNTIF(條件區(qū)域,條件)

SUMPRODUCT函數(shù)也不甘示弱，在I2單元格輸入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*1)

SUMPRODUCT函數(shù)單條件計(jì)數(shù)語(yǔ)法：

=SUMPRODUCT((條件1)*1)

或者

=SUMPRODUCT(--(條件1))

2.第一場(chǎng)比賽可謂勢(shì)均力敵，不分勝負(fù)。下面請(qǐng)看第二場(chǎng)比賽：統(tǒng)計(jì)值班經(jīng)理在中午時(shí)間段的值班次數(shù)。

兩個(gè)條件？COUNTIF函數(shù)頓時(shí)傻眼了，多條件計(jì)數(shù)是COUNTIF函數(shù)心里永遠(yuǎn)的痛。然而，SUMPRODUCT函數(shù)卻氣定神閑，在H2單元格輸入公式，并向下填充。

=SUMPRODUCT(($A$2:$A$10=G2)*($B$2:$B$10=$H$1))

SUMPRODUCT函數(shù)多條件計(jì)數(shù)語(yǔ)法：

=SUMPRODUCT((條件1)*(條件2)*(條件n))

“打虎親兄弟，上陣父子兵”，看到兄弟COUNTIF函數(shù)有難，擅長(zhǎng)多條件計(jì)數(shù)的COUNTIFS函數(shù)果斷出手了，在I2單元格輸入公式，并向下填充。

=COUNTIFS(A:A,G2,B:B,$H$1)

COUNTIFS函數(shù)語(yǔ)法：

=COUNTIFS(條件區(qū)域1,條件1,條件區(qū)域2,條件2,條件區(qū)域n,條件n)

3.第二場(chǎng)比賽的結(jié)果大家有目共睹，勝利屬于SUMPRODUCT函數(shù)。下面請(qǐng)看第三場(chǎng)比賽：值班經(jīng)理都用了一個(gè)字作為自己的簡(jiǎn)稱，如何根據(jù)簡(jiǎn)稱統(tǒng)計(jì)值班次數(shù)？

SUMPRODUCT函數(shù)沒(méi)有了之前的淡定從容，陷入了沉思中。而COUNTIF函數(shù)卻露出了久違的笑容，它拿出了自己的絕活，在H2單元格輸入公式，并向下填充。

=COUNTIF(A:A,"*"&G2&"*")

在這里，“*”代表通配符，表示任意一個(gè)或者多個(gè)字符。在Excel函數(shù)中，能與通配符配合使用的函數(shù)并不多，COUNTIF函數(shù)是其中的一個(gè)，當(dāng)然也包括了COUNTIFS函數(shù)，SUMIF函數(shù)，SUMIFS函數(shù)，VLOOKUP函數(shù)，MATCH函數(shù)等等。

SUMPRODUCT函數(shù)想破了腦袋，借助其他函數(shù)，終于也統(tǒng)計(jì)出來(lái)了。

=SUMPRODUCT(--ISNUMBER(FIND(G2,$A$2:$A$10)))

這個(gè)公式比較復(fù)雜，下面我們按步驟來(lái)分析。

Step 01 先看最里層的FIND函數(shù)， FIND函數(shù)的語(yǔ)法：

=FIND(查找的字符,查找的地方)

在I2，I3單元格分別輸入公式:

=FIND("風(fēng)","風(fēng)清揚(yáng)")

=FIND("風(fēng)","東方不敗")

在第一個(gè)公式中，因?yàn)樽址帮L(fēng)”在字符串“風(fēng)清揚(yáng)”的第一個(gè)位置，所以結(jié)果返回1。而第二個(gè)公式中，因?yàn)樽址帮L(fēng)”沒(méi)有在字符串“東方不敗”中，所以結(jié)果返回錯(cuò)誤值。

Step 02 熟悉了FIND函數(shù)的基本運(yùn)用后，我們?cè)贗2單元格輸入公式：

=FIND(G2,$A$2:$A$10)

我們知道，在“A2:A10”區(qū)域中，存在了兩個(gè)“風(fēng)清揚(yáng)”，按道理，字符“風(fēng)”是能查找到的，應(yīng)該返回?cái)?shù)字才對(duì)啊，但是卻返回錯(cuò)誤值，這究竟是為什么呢？

FIND函數(shù)的第二個(gè)參數(shù)是一個(gè)區(qū)域，所以返回的結(jié)果是若干個(gè)數(shù)據(jù)，多個(gè)數(shù)據(jù)放在一個(gè)單元格中，當(dāng)然會(huì)出錯(cuò)了。這個(gè)時(shí)候，我們需要借助一個(gè)神器：獨(dú)孤九劍，也就是F9鍵。選擇公式所在單元格，點(diǎn)擊編輯欄，按F9鍵。

Step 03 帶有紅色方框的數(shù)字個(gè)數(shù)就代表了該值班經(jīng)理的值班次數(shù)。那么怎么統(tǒng)計(jì)數(shù)字的個(gè)數(shù)呢？可以使用ISNUMBER函數(shù)，如果是數(shù)字就返回TRUE，否則就返回FALSE。

=ISNUMBER(FIND(G2,$A$2:$A$10))

Step 04 再結(jié)合SUMPRODUCT函數(shù)，結(jié)果便出來(lái)了，公式在上面已經(jīng)給出。

第三場(chǎng)比賽，雖然SUMPRODUCT函數(shù)最后完成了任務(wù)，但評(píng)委的眼睛是雪亮的，這一次，評(píng)委把票投給了COUNTIF函數(shù)。

比賽的結(jié)果并不重要，重要的是，在什么時(shí)候該使用什么函數(shù)，怎么簡(jiǎn)單怎么來(lái)，作為這次比賽的吃瓜觀眾，你們說(shuō)呢？

本文來(lái)源：Excel不加班，稅小課整理發(fā)布，文章版權(quán)歸原作者所有，如有不妥，請(qǐng)聯(lián)系刪除。