求和,我是認真的(Excel函數(shù)教程)
?
作者:小花來源:部落窩教育發(fā)布時間:2018-06-23 11:37:09點擊:5910
小編有話說:聽說在函數(shù)運用中,求和函數(shù)大家用的最多,那excel中那么多的求和函數(shù),你都知道怎么用嗎?今天小編給大家分享一篇求和集錦,學會之后可以少走彎路,求和時只需要用最簡單的那個求和函數(shù)就好了!
求和是表親們進行數(shù)據(jù)統(tǒng)計的常規(guī)需求之一。但你知道嗎,Excel中為我們提供了很多求和函數(shù),可不止SUM函數(shù)一個?今天,就讓我們來逐一認識下Excel中的求和函數(shù)吧!
NO.1
平凡世界:SUM
作為求和的正統(tǒng)血脈,SUM函數(shù)是資歷最老的求和函數(shù)。這個以求和的英文單詞直接冠名的函數(shù),相信很多小花瓣都不會陌生。點擊按鈕,或輸入=SUM(求和區(qū)域),抑或按<Alt+=>,都可以調(diào)用SUM函數(shù)來對數(shù)據(jù)源進行求和。在沒有使用數(shù)組運算的情況下,SUM函數(shù)的運算原理是相對比較簡單的,但仍需注意文本和邏輯值會被SUM函數(shù)當成0處理,而當求和區(qū)域有錯誤值,SUM函數(shù)也會報錯。眼中不容沙子是SUM函數(shù)最顯著的脾氣。
圖1 快速批量調(diào)用SUM函數(shù):Alt+=
用法與說明:
<Alt+=>是自動求和的快捷鍵,定位空值后,調(diào)用自動求和,能夠為空單元格自動填充SUM函數(shù),求和區(qū)域也能夠智能識別為左方和上方的相鄰連續(xù)單元格區(qū)域。例如B5單元格,其上方相鄰連續(xù)單元格為B2:B4,則B5的公式為“=SUM(B2:B4)”,即對B2、B3、B4進行求和。
附圖1 Alt+=
NO.2
單一條件求和:SUMIF
作為求和函數(shù)科班出身的SUMIF函數(shù),可謂是給求和函數(shù)家族帶來了革命性的變化。從SUMIF開始,求和不再是“一團和氣”,真正做到求同存異,和而不同。=SUMIF(條件區(qū)域,條件,求和區(qū)域)這樣的函數(shù)語句想必花瓣們都已經(jīng)爛熟于心了。今天,就讓我們用SUMIF函數(shù)來秀演示一下如何解決隔列求和問題。
圖2 單一條件隔列求和
公式說明:
以N3為例,SUMIF函數(shù)將條件區(qū)域B2:M2中的每一個單元格都與條件值N2單元格進行比對,如果相等,則將B3:M3中與之對應的單元格求和,因為B2、E2、H2和K2都與N2同為“銷額”,所以對應的B3、E3、H3和K3都被加總起來。實例中的$符號表示鎖定行列的標志,這樣的操作是為了能夠?qū)⒐酵蟿犹畛涞?span>N2:P8區(qū)域中。
NO.3
多條件求和:SUMIFS
SUMIFS函數(shù)作為SUMIF函數(shù)的威力加強版,幾乎具備了后者全部的本領。它的顯著優(yōu)勢在于它可以為求和區(qū)域設定的條件數(shù)量不再僅限于一個,而是將條件區(qū)域與條件值的組合擴大到最多127組,這是一次質(zhì)的飛躍。=SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2......)是SUMIFS函數(shù)的基本語法,小花瓣們要注意它和SUMIF函數(shù)的差別在于SUMIFS函數(shù)的求和區(qū)域是前置的。
圖3 多條件求和
公式說明:
以G2為例,SUMIFS函數(shù)的作用是將C2:C10中的每一個單元格與E2進行比較,同時將B2:B10中的每一個單元格與F2進行比較,將同時滿足兩個條件的對應求和區(qū)單元格C2和C5進行求和。
NO.4
交叉條件求和:SUMPRODUCT
SUMPRODUCT在函數(shù)屆具有不可撼動的霸主地位,原因便是它兼具多項才能,除了我們常見的多條件查詢,乘積求和和交叉條件求和也是它的拿手好戲。SUMPRODUCT的基本語法是=SUMPRODUCT(乘積區(qū)域1,乘積區(qū)域2),兩個乘積區(qū)域中的數(shù)字會被一一對應相乘并求和,即乘積和;它還有一個非常著名的變形語法=SUMPRODUCT((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)......*(求和區(qū)域)),這個語法實際上是一個數(shù)組運算,本文我們不深入探究其用法,僅以圖5為例稍作演示。
圖4 乘積和
公式說明:
SUMPRODUCT(A2:A10,B2:B10)表示將A2:A10和B2:B10一一對應相乘,例如A2*B2、A3*B3等,最后將這些乘積求和。
圖5 交叉條件求和
NO.5
可見求和:SUBTOTAL
SUBTOTAL函數(shù)對很多小花瓣還說,就像一個熟悉的陌生人,似曾相識卻與不可名狀。你一定用過它,但你很可能并不認識它。沒錯,當你在篩選的情況下點擊,那么被調(diào)用的求和函數(shù)就不是SUM,而是SUBTOTAL函數(shù)。=SUBTOTAL(功能代碼,求和區(qū)域1,求和區(qū)域2......)是它的基本語法。參數(shù)1中的功能碼有很多,其中與求和有關的有兩個,9和109。當求和區(qū)域包含隱藏單元格時,9表示包含隱藏值求和,109忽略隱藏值求和,即可見求和。
圖6 可見求和
公式說明:
C12中SUBTOTAL的功能碼為9,包含隱藏單元格求和,其求和結果不會隨隱藏行操作而變化;而D12中的功能碼為109,不包含隱藏單元格,因此,當求和區(qū)域所在行被隱藏,隱藏單元格將不被納入求和范圍內(nèi)。
NO.6
忽略求和:AGGREGATE
AGGREGATE作為求和函數(shù)界的“飲水機守護者”,一直默默無聞,了解或者說知道它的花瓣屈指可數(shù)。幾乎把板凳坐穿的命運與它全能的戰(zhàn)斗力并不匹配,懷才不遇的AGGREGATE函數(shù)需要一次放光發(fā)熱的而機會。于是,連小花都不忍心再次埋沒它了。=AGGREGATE(9,忽略類型碼,求和區(qū)域)就是運用AGGREGATE求和時的基本語句,其中9是AGGREGATE第一個參數(shù)中表示求和的功能碼。而忽略類型嗎共有8個,分別表示忽略不同類型的數(shù)據(jù),具體如下表:
圖7 忽略類型代碼表
我們不妨使用AGGREGATE來完成上述函數(shù)都無法完成的忽略錯誤值求和。
圖8 忽略錯誤求和
公式說明
SUM函數(shù)求和時,無法因?qū)η蠛蛥^(qū)域中有錯誤值的情況;而AGGREGATE則能克服這一缺陷,忽略求和公式中的錯誤值求和。
NO.7
數(shù)據(jù)庫求和:DSUM
作為數(shù)據(jù)庫函數(shù)的一員,DSUM函數(shù)難免默默無聞,充滿未知和神秘的色彩。今天小花帶你來揭開它的神秘面紗。DSUM的功能是返回列表或數(shù)據(jù)庫中滿足條件的記錄字段列數(shù)字之和。=DSUM(列表區(qū)域或數(shù)據(jù)庫,字段,條件區(qū)域)是它的基本語句,其中條件區(qū)域是由字段標簽單元格和表示條件的單元格組成。
圖9 數(shù)據(jù)庫求和
公式說明
求和列表區(qū)域為A1:D10,該區(qū)域必須包含求和列和條件列,且其首行必須為字段標題(品名、2L等);公式中的B1表示求和字段名為“2L”,該字段值為列表區(qū)域的標題行標簽之一;而條件區(qū)域F1:G2的第一行為字段標簽,該字段標簽必須包含在列表區(qū)域中,它的第二行為公式值,F1:F2表示條件一為品名包含“C產(chǎn)品”,G1:G2表示條件二為2L的產(chǎn)量大于0。利用DSUM函數(shù)對滿足兩個條件對應的B列值進行求和。
本文介紹的求和函數(shù)應用實例都是比較簡單的,主要目的是讓小花瓣們熟悉它們,以便可以視情況擇優(yōu)使用。這些函數(shù)看似簡單,但如果你深入研究,你會發(fā)現(xiàn)函數(shù)的變形寫法、加入通配符的使用、數(shù)組求和等等都能大幅度提高求和函數(shù)的功能性,這些函數(shù)的深入用法,都有待小花瓣去研究探索,本文僅作為一個引子,感謝您的閱讀。
本文配套的練習課件請加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學習。
相關推薦:
《看透了這些運算符,函數(shù)技能飆升25%》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!