1. <rp id="asmyu"></rp>
      1. <progress id="asmyu"><track id="asmyu"></track></progress>

        <em id="asmyu"><strike id="asmyu"><u id="asmyu"></u></strike></em>
        <span id="asmyu"></span>
        <rp id="asmyu"><ruby id="asmyu"><input id="asmyu"></input></ruby></rp>

            二維碼 購物車
            部落窩在線教育歡迎您!

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

            ?

            作者:小窩來源:部落窩教育發布時間:2024-04-19 18:31:34點擊:554

            分享到:
            0
            收藏    收藏人氣:0人
            版權說明: 原創作品,禁止轉載。
            編按:

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

             

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

             

            圖形用戶界面, 表格
中度可信度描述已自動生成

             

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

             

            表格
描述已自動生成

             

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

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

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

             

            圖示
描述已自動生成

             

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

             

            圖示, 示意圖
描述已自動生成

             

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

             

            由于是以條件區域的大小為基礎進行成對條件求和,所以求和區域可以簡寫,只引用區域中第一個單元格,SUMIF在實際運算中會自動補齊,讓求和區域與條件區域大小相等。

             

            表格
描述已自動生成

             

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

             

            1.錯位求和

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

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

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

             

            表格
描述已自動生成

             

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

             

            表格
描述已自動生成

             

            說明:

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

             

            電腦屏幕的照片上有文字
描述已自動生成

             

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

             

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

            譬如求下方5種產品最后報價之和。

             

            圖形用戶界面, 應用程序, 表格
描述已自動生成

             

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

             

            表格
描述已自動生成

             

            說明:

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

             

            表格, 日歷
描述已自動生成

             

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

             

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

             

            表格
描述已自動生成

             

            2.多表查找并列表查找

            譬如從下方并列的多個表中查找林菲的成績和學號。小窩曾分享過用INDIRECT進行多表格查找,但太復雜了。今天借助SUMIF來查找。

             

            表格
描述已自動生成

             

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

            但用SUMIF很簡單就搞定。

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

             

            表格
描述已自動生成

             

            說明:

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

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

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

             

             

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

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

            掃下方二維碼關注公眾號,可隨時隨地學習Excel

            IMG_256

            相關推薦:

            多區域查找用INDIRECT

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

            按條件進行多列求和

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

            版權申明:

            本文作者小窩;部落窩教育享有稿件專有使用權。若需轉載請聯系部落窩教育。

             

             

            国产欧美一区二区精品久久久_自拍性旺盛老熟女视频_人与禽ZOZ0性伦交老太婆_久久精品国产第一区二区三区