二維碼 購物車(chē)
部落窩在線(xiàn)教育歡迎您!

用SUMIF錯位求和多表求和與查找

?

作者:小窩來(lái)源:部落窩教育發(fā)布時(shí)間:2024-04-19 18:31:34點(diǎn)擊:725

分享到:
0
收藏    收藏人氣:0人
版權說(shuō)明: 原創(chuàng )作品,禁止轉載。
編按:

大家習慣性的認為SUMIF就是1列條件區域1列求和區域。實(shí)際上并非如此,SUMIF條件求和的規則或原理:可以有多列條件區域,它按條件區域的大小與求和區域進(jìn)行成對的條件求和。利用這點(diǎn)可以很簡(jiǎn)單地搞定多表匯多表查找、并列表匯總、錯位求和等等。

 

在《按條件進(jìn)行多列求和》的教程中,小窩曾提到SUMIF的奇怪規則,由下面的一個(gè)故意為難人的要求引起。

 

圖形用戶(hù)界面, 表格
中度可信度描述已自動(dòng)生成

 

小窩的解決方法就是增加兩列姓名,讓條件區域和求和區域大小一致:

 

表格
描述已自動(dòng)生成

 

以上用到了SUMIF鮮為人知的運算規則

以條件區域大小為基礎,將條件列(或行)和求和列(或行)按相同列序(或行序)成對,然后累加每對中符合條件的數據。

啥意思?下方的示意圖有利于大家理解。

 

圖示
描述已自動(dòng)生成

 

小窩把條件區域各列中“王靜”的排序調整一下,大家看得更明白:

 

圖示, 示意圖
描述已自動(dòng)生成

 

首先在第1列條件中查找“王靜”,返回第1列求和區域中的“142”;接著(zhù)在第2列條件中查找,返回第2列求和區域中的“109”;再在第3列條件中查找,返回第3列求和區域中的“60”;最后三者相加就等于311。

 

由于是以條件區域的大小為基礎進(jìn)行成對條件求和,所以求和區域可以簡(jiǎn)寫(xiě),只引用區域中第一個(gè)單元格,SUMIF在實(shí)際運算中會(huì )自動(dòng)補齊,讓求和區域與條件區域大小相等。

 

表格
描述已自動(dòng)生成

 

了解了SUMIF按照條件區域大小成對進(jìn)行條件求和的規則,那神秘的錯位求和、多表查找就很簡(jiǎn)單了。

 

1.錯位求和

所謂的錯位求和,就是指條件區域和求和區域有重疊,或者位置有錯行或者錯列。

1)多表匯總、并列表匯總——條件區域和求和區域重疊

譬如求下方王文、劉新的總分,可以認為是按條件匯總英語(yǔ)、語(yǔ)文、數學(xué)三個(gè)并列表中的數據。

 

表格
描述已自動(dòng)生成

 

簡(jiǎn)單,公式=SUMIF($A$21:$E$26,H20,$B$21:$F$26),向下填充即可。

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

把姓名到姓名列A21:E26作為條件區域,分數到分數列B21:F26作為求和區域,然后進(jìn)行成對條件求和,實(shí)質(zhì)如下圖。

 

電腦屏幕的照片上有文字
描述已自動(dòng)生成

 

在第1列條件中查“王文”,返回第1列求和區域中的69;在第2列條件中查找,沒(méi)有符合條件的,返回0……以此類(lèi)推,完成5對條件求和并累加69+0+99+0+61=229。

 

2)求所有列最后一個(gè)數據的和

譬如求下方5種產(chǎn)品最后報價(jià)之和。

 

圖形用戶(hù)界面, 應用程序, 表格
描述已自動(dòng)生成

 

也很簡(jiǎn)單,公式=SUMIF(B41:F47,"",B40:F46)

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

將第二次報價(jià)到最后一次報價(jià)下一行B41:F47作為條件區域,將第一次報價(jià)到最后一次報價(jià)B40:F46作為求和區域,條件是空。此處既有重疊——條件區域和求和區域存在重疊,又有位置錯位——條件區域位與求和區域錯開(kāi)了一行(但是大小一致)。

 

表格, 日歷
描述已自動(dòng)生成

 

1列條件中等于空的,對應第1列求和區域中的900(空);第2列條件中等于空的,對應第2列求和區域中的94……以此類(lèi)推得到各列最后一次報價(jià)并累加。

 

如果表格是橫向的,也是一樣的用SUMIF求和搞定。

 

表格
描述已自動(dòng)生成

 

2.多表查找并列表查找

譬如從下方并列的多個(gè)表中查找林菲的成績(jì)和學(xué)號。小窩曾分享過(guò)用INDIRECT進(jìn)行多表格查找,但太復雜了。今天借助SUMIF來(lái)查找。

 

表格
描述已自動(dòng)生成

 

因為并不知道林菲位于那一列,所以不管是VLOOKUP,還是XLOOKUP,都不好使。

但用SUMIF很簡(jiǎn)單就搞定。

公式=SUMIF($A$72:$D$80,$H$72,B72:E80),然后向右填充即可。

 

表格
描述已自動(dòng)生成

 

說(shuō)明:

由于沒(méi)有重名,成績(jì)和學(xué)號都是數字,所以用SUMIF求和可以完美的搞定多表查找。

把姓名到姓名區域$A$72:$D$80作為條件區域,把成績(jì)到成績(jì)區域B72:E80作為求和區域,就可以獲得成績(jì)。

把姓名到姓名區域$A$72:$D$80作為條件區域,把學(xué)號到學(xué)號區域C72:F80作為求和區域,就可以獲得學(xué)號。

 

 

本文配套的練習課件請添加客服微信buluowojiaoyu索取。

Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!

掃下方二維碼關(guān)注公眾號,可隨時(shí)隨地學(xué)習Excel

IMG_256

相關(guān)推薦:

多區域查找用INDIRECT

含單位和通配符的數字求和

按條件進(jìn)行多列求和

為何根據顏色查找單元格查找不到?

版權申明:

本文作者小窩;部落窩教育享有稿件專(zhuān)有使用權。若需轉載請聯(lián)系部落窩教育。