excel函數應用技巧：求和函數SUM的進階用法

2020-10-20 部落窩教育BLW

編按：哈嘍，大家好！平時我們用SUM函數一般都是處理一些簡單的求和問題，今天我們要給大家分享幾招SUM函數的進階用法：快速對交叉區域、應收款、小計行自動求和。

Excel函數家族樹大根深，枝繁葉茂，但若要按使用頻率高低排個序，那唯一能和IF函數一爭高下的，恐怕只有SUM了。我們會在很多不經意的地方用到它，比如自動求和，各種總計小計等等。SUM函數的基本用法非常簡單，=SUM(求和範圍)，幾乎人人都會用！但你知道嗎，SUM函數的小宇宙也有大爆發的高光時刻。不信？一起來瞧瞧，SUM的這些牛皮用法，你會嗎？

案例一：交叉區域求和

什麼是交叉區域，就是兩個不同單元格區域的重疊的部分(交集)。例如下圖所示，單元格區域A5:G8是華南區不同產品的銷售情況，單元格區域D2:E2是B系列產品在不同區域的銷售情況，這是兩個不同的單元格區域，他們的交叉區域是D5:E8單元格區域。當我們要對華南區域B系列產品銷售額進行求和時，就是要對A5:G8和D2:E2單元格區域的交叉區域求和。

這種對交叉區域求和的公式應該怎麼寫？很簡單，只要給SUM函數一個空格，它就能輕鬆幫你搞定。

=SUM(D2:E11 A5:G8)

公式說明:

空格是Excel引用符之一，和冒號(表示連續區域引用)、逗號(表示並列區域)引用不同，空格表示兩個單元格區域的交集。所以SUM(D2:E11 A5:G8)就表示對A5:G8和D2:E2單元格區域的交叉區域求和。

案例二：應收款管理

財務的表哥表姐們，對應收款項的管理怎能不熟悉？銷售收款政策產生了分月應收明細，而財務實際收款通常無法直接與之一一對應。但為了對應收款的帳齡進行分析，我們又需要區分每個月應收款的實際收取情況，形成未收明細和逾期帳齡明細。具體案例如下圖，應收客戶帳款總額200萬，對應各月份到期帳款如下，截止目前已收款85萬(收款金額動態變化)，目前需計算得出各期應收款催收情況。

一邊是應收款，一邊是實收款，通往未收帳款明細的道路在哪裡？人工計算填寫？IF多重嵌套？別迷茫，SUM披荊斬棘，踏馬而來，快快把它收入你的技能包吧！

=MIN($F$1-SUM($A5:A5),B4)

公式說明:

該公式十分簡短，其精髓不在函數本身，而在於對應收未收款計算邏輯的理解。

1.假定先欠先還，以3月為例，3月份款項收回的前提是已收款總額大於往期月份應收款的總額，換句話說，已收款總額優先用於填補往期應收款，有剩餘可償還金額方能用於填補本期應收款，這個可償還金額就是$F$1-SUM($A5:C5)。這裡將SUM求和範圍的初始單元格鎖定，結束單元格定為當前單元格左側的一個單元格，就表示已收取往期應收款的總額。

2.另一方面，償還本期帳款的金額不能大於本期應收款，於是我們使用MIN函數在可歸還額度和應收帳款之間取得最小值，確保不會出現超額收取的情況。

3.MIN函數的使用也確保了每一期已收款額都不會大於可償還額，因為$F$1-SUM($A5:A5)的最小值為0，遞推，已收帳款(即MIN函數返回值)一定不小於0，所以不會出現已收帳款為負數的情況。

綜上，應收帳款管理問題的完美解決方案就是MIN+SUM的函數組合，這是一個邏輯的結晶，無關公式難易，建議多看幾遍，定有助於舉一反三！案例三：批量自動求和Alt+=

在實操中，SUM函數最常應用的場景就是對行列的小計求和，就像下圖這樣，我們需要根據銷售情況按行合計，形成每個人的銷售總額，同列總計形成各產品的銷售總額，如何快速設置下圖紅色方框內的單元格求和公式呢？

「加」字門門人相信，沒有什麼求和是一個加法解決不了的，如果有，就用N個。

「拖」刀幫幫眾堅持，只要寫好一個公式，拖動填充能圍繞地球兩圈。

不管你是「加」字門還是「拖」刀幫，小花接下來祭出的這個大殺器，一定會讓你放下「屠刀」，立刻「出家」！

選中求和區域和結果區域，即C2:G12，接著按Alt+=，行列求和一秒搞定！

注意：結果區域必須為空值，否則將影響自動求和技巧的使用。

上述案例過於基礎，無法充分體現Alt+=的神奇力量，為此，我們需要加點難度，即分區域分產品求和，如圖。我們需要對紅色區域進行求和，Alt+=是否也能一鍵搞定？

答案顯而易見，能！但需要Ctrl+G定位技巧的配合，Alt+=才能發揮作用，我們來看具體操作。

操作步驟:

1.選擇C1:G15單元格區域，按Ctrl+G，彈出【定位】對話框，點擊【定位條件】按鈕，彈出【定位條件】對話框，選擇【空值】，點擊【確定】，即可快速選中上圖中的紅色區域；

2.再按Alt+=，即可一鍵求和，真的再便捷不過了，NICE！

The End

SUM函數的高級用法，我們就先分享這三招，希望對小花瓣們的工作能有所幫助。你還知道哪些與SUM函數相關的獨門絕技，歡迎留言與我們分享，下篇我們繼續深挖SUM函數，敬請期待！本文配套的練習課件請加入QQ群：1043683754下載。

＊＊＊＊部落窩教育-excel函數SUM應用技巧＊＊＊＊

原創：小花/部落窩教育（未經同意，請勿轉載）

更多教程：部落窩教育

微信公眾號：exceljiaocheng，+v：blwjymx2

相關焦點

  • excel函數公式:累計求和不用sum函數也可以嗎?
    在前面的文章中，小編給大家分享了累計求和，使用的是sum函數來實現。今天小編分享的這兩個案例是前面文章中使用過的例子，但是使用的是和之前不同方法來實現的，給大家分享N函數和NA函數的用法.sum函數實現累計求和在 C3單元格中輸入公式：=SUM($B$3:B3)，向下填充即可。
  • excel如何按條件求和?全都在這裡:從sum函數到sumifs函數
    對於excel裡面的數字，常用的統計就是求和及計數。如果你沒有學會這些函數，那麼就無法應對大量數據的表格。現在，我們就來用函數公式實現excel自動化辦公。一、掌握「sum函數」的用法sum函數是將單元格中的數字相加，用法為：=sum（單元格\單元格區域，……）不引用單元格的話，就直接填寫內容。可以添加N個單元格\單元格區域。
  • Excel萬能函數-Excel-函數技巧-sumproduct函數乘積求和函數
    Sumproduct函數在實際表格的應用中非常常用，特別是要計算排名，有條件的求和，求積等，現在讓我們來介紹用法。舉例下圖：（表示2X4X7+3X3X7+4X4X5+5X6X6+6X7X7）我們現在有一張圖-今日銷售表：函數使用方法一（乘積求和）：計算今日銷售的總額：使用函數：SUMPRODUCT(C2:C12,D2:D12)函數使用方法二（條件求和）：
  • 【Excel函數教程】SUMPRODUCT函數的應用
     提示：點擊上方"excel教程"↑免費訂閱　第一部分：SUMPRODUCT函數用法介紹　　SUMPRODUCT是什麼?　　第二，輸入公式：=SUMPRODUCT(A2:B4)，得到結果為31。根據上面的要點介紹，如果是一個數組，那麼就是對這個數組的求和，因此就是對{3,4;8,6;1,9}這個區域求和。　　提示：兩個數組相乘是同一行的對應兩個數相乘。數組數據用大括號{}括起來，行數據之間用分號";"分隔，如果是同一行的數據，用逗號","分隔。
  • Excel函數應用篇:sumproduct函數的含義及各種用法
    一、sumproduct函數介紹SUMPRODUCT 函數功能SUMproduc先計算多個數組的元素之間的乘積再求和SUMPRODUCT函數語法=SUMPRODUCT(array1,array2,array3, ...)Array為數組參數說明array：指定包含構成計算對象的值的數組或單元格區域
  • Excel表格怎麼求和?sum函數的五種求和用法
    Excel表格求和是常見的，大家都知道可以使用sum函數進行求和，下面給大家詳細的介紹sum函數的五種求和用法。1、橫向求和輸入求和函數sum橫向選擇數據就可進行橫向求和，演示公式=SUM(C2:E2)。2、縱向求和輸入求和函數sum，縱向選擇數據就可進行縱向求和，演示公式=SUM(D2:D11)。
  • excel函數公式大全之利用sum函數進行匯總以及對多個匯總進行求和
    excel函數公式大全之利用sum函數進行匯總以及對多個匯總進行求和，excel函數與公式在工作中使用非常的頻繁，會不會使用公式直接決定了我們的工作效率，今天我們來學習一下提高我們工作效率的函數sum函數。
  • Sum函數的4個巧妙用法
    1、小計求和對小計行求和，一般是=小計1+小計2+小計3...有多少小計行加多少次。換換一種思路，總計行=（所有明細行+小計行）/2,所以公式可以簡化為：=SUM(C2:C11)/22、多表求和多個工作表如果格式完全相同，可以用sum函數的多表求和功能。如下圖所示，要求在匯總表裡設置合計公式，匯總前19個工作表B列的和。
  • Excel最強求和函數SUMPRODUCT()的使用方法
    excel首先，這個函數類似於求和函數SUM，但是比SUM的用法要更高級，它是對數組的成績進行求和的。包含2個數組的用法：1、單行單列數組計算：SUMPRODUCT( A1:A3, B1:B3 )，計算過程是A1 X B1 + A2 X B2 + A3 X B3 = 6，這個函數要求數組維數必須相同，數組就是｛A1:A3 ｝、｛B1:B3｝，這種結構，這都是3行1列的數組。當然也可以選擇1行多列的數組進行計算。
  • Excel教程:sumproduct函數多條件求和
    Excel技巧37：sumproduct函數多條件求和
  • excel函數公式應用:多列數據條件求和公式知多少?
    如果是根據條件求單列數據之和，SUMIF函數即可解決，但如果是求多列數據呢？我們這裡分享12種方法，各有各的特色。學習更多技巧，請收藏關注部落窩教育excel圖文教程。 先來看一下什麼是按條件求多列數據之和。 類似下圖這樣的數據，需要根據G列的產品名稱在H列匯總數據。條件區域在B列，而要求和的數據在C、D、E三列中。
  • 「Excel技巧」sumproduct函數的含義及各種用法
    今天跟大家一起來認識一個很好用的函數：sumproduct函數。sumproduct函數，sumproduct是由兩個英文單詞組成，即sum和product。Sum代表求和，product代表乘積，組成的sumproduct就是乘積之和。
  • excel數據計算的高效技能,求和函數的使用技巧
    我們在實際工作中，當我們需要處理大量數據的時候，我們通常會選擇excel表格來處理，因為excel表格處理數據自帶很多實用的公式和函數，今天我們要分享的是數據求和，這次我們不使用公式求和，這次我們分享一個更加高效的技巧，使用求和函數對數據進行快速求和。
  • 函數sum用法你已熟悉,那麼功能更強大的函數sumif呢?
    ，我們講述的內容都是關於函數count、函數countif和函數countifs的操作方法，小編不才，雖說沒將內容講到十成，卻也將這些函數的基礎用法說了七八分，希望對大家的工作有所幫助。今天的內容就開啟新篇章了，不再執著於count系列函數。今天會從大家十分熟悉的函數sum談到功能更強大的函數sumif的具體操作運用，這裡似乎會給人一種似曾相識的感覺，就像函數count和函數countif，那麼是否有函數sumifs呢？該有的肯定會有，不過咱們循序漸進，一步一步來。
  • excel區域乘積求和,使用函數sumproduct就可以快速實現
    銷售人員經常會使用到excel區域乘積求和的技能，因為銷售人員會對自己銷售的物品進行銷售金額的計算，銷售金額等於單價乘銷售數量，而銷售人員需要對求得一個月的銷售總金額，如果銷售的商品比較多，使用傳統計算器計算這些數據，就相對比較麻煩了，所以可以採用excel表格處理數據，excel
  • EXCEL | SUMIF條件求和函數應用之一:單條件、多條件、模糊條件求和
    SUMIF條件求和函數是excel最常用的函數之一。
  • Excel函數應用篇:Sumifs()函數的使用技巧
    前幾日介紹了COUNTIF（Excel函數應用篇：Countif()函數）和COUNTIFS（Excel函數應用篇：Countifs()函數的使用技巧
  • sum函數應用教程
    sum函數語法雖然簡單，但是功能十分強大。當你深挖其內涵時，你會被sum函數的博大精深所震動。我們要深挖每個函數的功能和應用，才能深刻體會每個函數的魅力。 Sum函數語法：.sum(number1,number2....)，為 1 到 254 個需要求和的參數。
  • Excel應用:SUMIFS求和函數的用法
    學習Excel不僅要掌握操作技巧，更要學習函數公式的用法，今天小編和大家分享一個求和函數，這個函數不僅可以求和，還有計算查詢的功能，相信你的會計核算工作中經常要處理這樣的問題，不想加班的，那就趕緊加入Excel與財務的學習大軍吧！
  • Excel中sum求和函數的幾種用法你都會了嗎?
    Excel是辦公室工作經常需要用到的軟體，而求和這個操作相信很多小夥伴都會經常用到，用Excel求和有兩種辦法，一種可以說是比較笨的辦法，就是直接將要求和的數加起來，例如「=A+B」；另外一種就是利用sum函數進行求和，例如「=sum(A1:B1)」,這種看起來比較高大上一點。