Zi 字媒體

由於筆者出過書的緣故，最近受公司領導的重託開始為集團一線的小伙們提供EXCEL方面的諮詢和解決方案。正好今天下午有點空，於是決定對過去一段時間的工作做一個梳理，根據文件的存檔記錄，在過去的三周的時間裡，我為集團各地的小夥伴們提供了46個各式各樣的解決方案，在這46個文件中我使用了55次函數和29次Excel工具，但統計結果讓我比較吃驚的是，在這55次函數中sumproduct出現了15次，佔比達到了27%。考慮到像sumif,sumifs和countif等函數的功能也可以被sumproduct替代，那sumproduct函數的使用可以達到22次，占整個函數的解決方案的40%。這個統計數據意味著什麼呢？也就是說，你只要學會了Sumproduct函數基本上就能解決工作中40%的問題。捫心自問Sumproduct確實不是我最喜歡的公式，我最喜歡的公式其實是Match，之前也介紹過，因為它可以用來解決爭端，詳見括弧中的內容（這個公式要好好珍藏，吵架的時候用的上！）。但是，為什麼在工作中我使用Sumproduct的頻率如些之高呢？可能還是因為Sumproduct的實用性和樸實無華的性格，它不像vlookup張揚，也不像sum那樣廣為人知。我們來看一下Excel中對於Sumproduct的介紹：SUMPRODUCT函數的功能是在給定的幾組單元格區域中，將各單元格區域對應的元素相乘，並對乘積求和。▼SUMPRODUCT函數格式為：SUMPRODUCT(數據區域1,數據區域2,數據區域3, ...)」▼SUMPRODUCT函數具有下列參數：數據區域1：必需參數。其相應元素需要進行相乘並求和的第一個數組參數。數據區域2,數據區域3, ...：可選參數。SUMPRODUCT可以使用2到255個數據區域參數，對其相應元素需要進行相乘並求和。✎注意：單元格區域參數必須具有相同的行數，否則，函數 SUMPRODUCT將返回錯誤值 #VALUE!。函數 SUMPRODUCT將非數值型的數組元素作為 0處理。寥寥幾筆就將一個如此有才華，如此重要的公式湮沒在了Excel浩瀚的公式之海中了。接下來我們將利用幾個實例讓大家對sumproduct有更清晰的認知：❶假設有一產品銷售表C3:C13為產品數量，D3:D13為產品單價，那我們要計算總體銷售額，只需要輸入公式「=SUMPRODUCT( C3:C13,D3:D13)」，公式就會將各產品的單價與其銷量分別相乘，並返回各個乘積之和。公式的計算原理是：SUMPRODUCT將C3:C13中的每一個單元格分別與D3:D13中的每個單元格相乘，然後把乘積相加，即=100*20+150*18+200*16+250*14+>>+600*19=63250。❷SUMPRODUCT公式還允許數據區域參數與數值進行運算，生成新的數組參數。還是以上圖為例，假設我們需要把單價提升5%，再計算銷售額。在這種情況下，我們只需要對上面公式中單價單元格區域*（1+5%），即可算出漲價后的銷售額，得到結果為66412.5。基於SUMPRODUCT允許通過運算產生的新數組作為參數的特性，我們可以擴展出SUMPRODUCT各種不同的用法，唯一限制你的可能是你的想象力。公式的計算原理是：SUMPRODUCT將D3:D13中的每一個單元格分別乘上（1+5%）再與C3:C13中的每個單元格相乘，然後把乘積相加。基於SUMPRODUCT允許通過運算產生的新數組作為參數的特性，我們可以擴展出SUMPRODUCT各種不同的用法。1Sumprocuct用於條件計數SUMPRODUCT函數可以替代Countif、Countifs等用於多條件計數。計算符合2個及以上條件的數據個數，有一個經典公式計數：SUMPRODUCT((條件1)*(條件2)*(條件3)*...)第一個問題：統計銷售數量大於400的銷售員有幾個人？一看此題即為單條件求和，那我們首先需要知道條件是什麼，(C3:C13>400)產品數量大於400就是條件。但是如果我們直接輸入=SUMPRODUCT(C3:C13>400)，大家可以試一試，我們得到結果為0，這是為什麼呢？這是因為我們在開頭sumproduct的用法介紹里講過：函數 SUMPRODUCT將非數值型的數組元素作為 0處理，C3:C13>400，按F9鍵得到執行結果是true、false形式的邏輯值，所以等於0。如果我們想將邏輯值轉換為數值，就需要讓邏輯值參加運算，比如我們可以用--，*1，+0等等讓邏輯值參與運算。公式「=TRUE*1」運算結果為1。公式「=FALSE*1」運算結果為0。因此在(C3:C13>400)外面加上--就算出了銷量高於400的銷售員為4人。第二個問題：統計銷量>400,售價高於17的銷售員有幾人？此題有兩個條件：第一個條件是銷量>400，用C3:C13>400表示。第二個條件是售價>17，用D3:D13>17來表示。套用SUMPRODUCT((條件1)*(條件2)*(條件3)*...)，得到公式：=SUMPRODUCT((C3:C13>400)*(D3:D13>17))，結果為2人。第三個問題：統計銷量>400,售價>17的李姓銷售人員的人數。此題有三個條件：跟前面的例子一樣，第一個條件是銷量>400，用C3:C13>400表示。第二個條件是售價>17，用D3:D13>17來表示，但是第三個條件是姓李，這個條件就不像其他的那麼容易表示。我們可以看到A列銷售人員的姓氏五花八門，同時我們也知道人名的規則是姓氏為首字元，要統計銷售員的姓氏，我們需要用到LEFT函數。比如B10單元格：丁春秋，如果我們在任一單元格輸入公式：「=LEFT(B10)"公式將返回其姓氏"丁"。還是套用SUMPRODUCT((條件1)*(條件2)*(條件3)*...)，得到公式：=SUMPRODUCT((C3:C13>400)*(D3:D13>17)*(LEFT(B3:B13)="李"))，結果為1人。2Sumproduct用於條件求和Sumproduct也可以替代sumif、sumifs用來進行條件求和運算，用函數SUMPRODUCT求和，函數需要的參數可以有N個進行判斷的條件，也可以有N個用來求和的數據區域。SUMPRODUCT函數求和應用有一個經典的套用格式：SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區域)第一個問題：統計張三的銷售額此題有一個條件：銷售員姓名為張三，用B3:B13="張三"來表示。求和區域有一個為銷售額，銷售額等於銷量*單價用C3:C13*D3:D13來表示。套用格式：SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區域) ，得出公式：=SUMPRODUCT((B3:B13="張三")*(C3:C13)*(D3:D13))。最後算出張三的銷售額為15300元。第二個問題：統計除張三外其他銷售人員銷量。此題有一個條件：銷售員姓名不為張三，用B3:B13<>"張三"來表示。求和區域有一個為銷量C3:C13來表示。套用格式：SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區域) ，得出公式：=SUMPRODUCT((B3:B13<>"張三")*(C3:C13))。最後算出其他銷售員的銷量為2850。3Sumproduct的其他應用❶我們可以利用Sumproduct計算不重複單元格的數量，例如：我們可以看到上圖中銷售員有很多重名的，例如叫「張三"的銷售員就有三個，我們如何從中找出不重名的銷售員有幾個呢？感謝互聯網，讓各種腦力在網路上激蕩，所以，有人發明了一個經典的範式來教我們如何統計不重複的個數。這個範式為：sumproduct(1/countif(區域，區域))這個範式的原理是用1/countif函數讓相同的數只統計一次。比如張三出現三次，用1/countif函數會生成三個1/3，最後匯總就得到1。套用經典範式我們就得到公式：=SUMPRODUCT(1/COUNTIF(B3:B13,B3:B13))，最後算出不重複的銷售人員個數為8個。❷我們還可以用Sumproduct來進行排名運算，例如：公式的計算原理是先用銷量*售價得出每個銷售員的銷售額，然後用每個銷售員的銷售額和當前銷售員的銷售額進行比較，如果銷售額大於當前銷售額，結果為TRUE,否則為FALSE，然後用--運算符將結果轉為數值1和0，最後統計為1的有多少，如果有6個銷售額大於當前銷售額則當前銷售額排第7，所以最後用公式：=SUMPRODUCT(--(($C$3:$C$13)*($D$3:$D$13)>C3*D3))+1算出了各個銷售員銷售額的排名，如上圖所示：從上面的的各個實例，我們可以看到Sumproduct函數的功能確實很強大，唯一限制你的使用的可能是你的想像力，只要你想像夠豐富，毫不誇張的說Sumproduct真的可以解決你50%的工作需求。為了讓廣大的表親們更好的掌據這個重要的公式，表哥整理了一個Sumproduct的練習冊。老規矩，想要索取本文原文件和課後練習冊的朋友們抓緊了，關注表哥的微信公眾號（Skexcel) 在後台回復「原文件」向表哥索取吧！