用不同的思路,有多少種不同的函數(shù)解法搞定條件求和
?
作者:老菜鳥(niǎo)來(lái)源:部落窩教育發(fā)布時(shí)間:2021-04-14 11:09:20點(diǎn)擊:2603
編按:
從一開(kāi)始接觸EXCEL,大家的心目中一直都有對(duì)函數(shù)的敬畏。是否能玩轉(zhuǎn)函數(shù)更是一個(gè)判斷EXCEL能力高下的標(biāo)準(zhǔn)。但,其實(shí)對(duì)于絕大多數(shù)人來(lái)說(shuō),不用學(xué)會(huì)太多函數(shù),只需要掌握最基礎(chǔ)的函數(shù),就有無(wú)數(shù)種解決EXCEL問(wèn)題的方法……今天,小E就用一個(gè)條件求和問(wèn)題,用不同思路,為大家打開(kāi)基礎(chǔ)函數(shù)百變應(yīng)用方法的大門(mén)!
對(duì)于絕大多數(shù)使用EXCEL的職場(chǎng)人來(lái)說(shuō),能夠解決自己工作中遇到的問(wèn)題是首要需求。平時(shí)也有很多人都在問(wèn)相同的幾個(gè)問(wèn)題:
“我這個(gè)問(wèn)題用哪個(gè)函數(shù)能解決???”
“要學(xué)的函數(shù)太多了,咋辦?”
“請(qǐng)問(wèn)常用的函數(shù)是哪些???有的函數(shù)太難了,學(xué)不會(huì)。”
“單個(gè)函數(shù)能明白,為什么解決問(wèn)題的時(shí)候就沒(méi)思路了?”
對(duì)于有同樣問(wèn)題的同學(xué)來(lái)說(shuō),首先要明確幾點(diǎn):
①能用一個(gè)函數(shù)就解決的問(wèn)題是比較少的,大多數(shù)問(wèn)題可能需要兩步或者三步。
②學(xué)多少函數(shù)才夠?這個(gè)是沒(méi)標(biāo)準(zhǔn)的,但有一點(diǎn)是肯定的,會(huì)的函數(shù)多了方法就多了。
③最后一點(diǎn),遇到問(wèn)題要分析,不管用什么方法先解決再說(shuō),然后再去思考更多的方法,這樣訓(xùn)練一段時(shí)間自己的思路也就廣了。
接下來(lái),就用一個(gè)實(shí)際問(wèn)題,和大家一起看看用不同的思路,有多少種不同的函數(shù)解法,當(dāng)然涉及到的函數(shù)都是非?;A(chǔ)的函數(shù)。
問(wèn)題:
下圖是一些客戶的付款明細(xì)表,要求匯總他們?cè)谌ツ晁膫€(gè)季度里的總付款金額。其中,只對(duì)四個(gè)季度都付款的數(shù)據(jù)進(jìn)行求和,如果存在任何一季度未付款的情況,則匯總處標(biāo)記為0,并在F列中顯示。
問(wèn)題很容易理解,大家可以自己先思考一下,然后再看后面的內(nèi)容。
思路一:計(jì)數(shù)法①
常規(guī)公式:=IF(COUNTIF(B3:E3,"未付款")=0,SUM(B3:E3),0)
思路:
這個(gè)公式采用的COUNTIF、IF、SUM都是很基礎(chǔ)的函數(shù),它的思路是先用COUNTIF統(tǒng)計(jì)四個(gè)季度中未付款的次數(shù),再對(duì)次數(shù)進(jìn)行判斷,如果結(jié)果為0,就用SUM求和,否則顯示為0。
站在解決問(wèn)題的角度來(lái)說(shuō),這樣就OK了,但是如果你還想在函數(shù)的路上走的更遠(yuǎn)一些,那不妨多想想其它的思路,比如下面的方法!
思路二:計(jì)數(shù)法②
公式:=IF(COUNT(B18:E18)=4,SUM(B18:E18),0)
思路:
公式中將COUNTIF換成了COUNT,也是個(gè)常用函數(shù),但是公式原理發(fā)生了一點(diǎn)點(diǎn)變化。這時(shí),不再是統(tǒng)計(jì)未付款的次數(shù),而是統(tǒng)計(jì)已付款的次數(shù),也就是四個(gè)季度中數(shù)字的個(gè)數(shù),如果有4個(gè)數(shù)字就求和,否則顯示0。
是不是有點(diǎn)啟發(fā)了?繼續(xù)思考還有更多的思路,比如下面這個(gè)公式。
思路三:計(jì)數(shù)法③
公式:=(COUNT(B3:E3)=4)*SUM(B3:E3)
思路:
這個(gè)公式的本質(zhì)與上一個(gè)并沒(méi)有區(qū)別,只是利用了邏輯值參與計(jì)算得到最終的結(jié)果。(關(guān)于邏輯值的玩法,感興趣的話,請(qǐng)到官方微信公眾號(hào)留言,筆者將單獨(dú)寫(xiě)一期教程。)
以上三個(gè)公式算是一個(gè)路數(shù),都是用計(jì)數(shù)的方法作為判斷條件,進(jìn)而得到所需結(jié)果。
下面的這個(gè)公式則是按照題意直奔答案!
思路四:判斷法
公式:=IF(OR(B18:E18="未付款"),,SUM(B18:E18))
思路:
OR(B18:E18="未付款")的作用是判斷四個(gè)季度中是不是有未付款,注意這里是一個(gè)數(shù)組用法,所以公式要按Ctrl、shift和回車(chē)鍵錄入。
與之類(lèi)似的還有公式=IF(AND(B3:E3<>"未付款"),SUM(B3:E3),)和公式=AND(B3:E3<>"未付款")*SUM(B3:E3),都是數(shù)組公式,公式的原理不難理解,就不剝奪大家思考的樂(lè)趣了。
要判斷是不是有未付款,其實(shí)還有一個(gè)思路,就是用MATCH函數(shù)。
思路五:錯(cuò)誤值判斷法①
公式5:=ISERROR(MATCH("未付款",B3:E3,0))*SUM(B3:E3)
思路:
這個(gè)公式中出現(xiàn)了一個(gè)之前沒(méi)有用到的函數(shù)ISERROR,這個(gè)函數(shù)的功能是判斷參數(shù)是否為錯(cuò)誤值,用MATCH在指定的區(qū)域中匹配未付款,如果有,則得到一個(gè)數(shù)字,否則得到錯(cuò)誤值,對(duì)于ISERROR來(lái)說(shuō),如果有返回false,反之得到true,又回到了邏輯值。
這個(gè)過(guò)程大家需要好好思考其中的奧妙,一旦你想明白的話,今后解決問(wèn)題時(shí)絕對(duì)是思如泉涌。
既然已經(jīng)提到了錯(cuò)誤值,不妨繼續(xù)順著這個(gè)方向思考。
思路六:錯(cuò)誤值判斷法②
公式:=IFERROR(B3+C3+D3+E3,0)
思路:
直接用四個(gè)單元格相加,只要有未付款出現(xiàn),結(jié)果就是錯(cuò)誤值,用IFERROR將錯(cuò)誤值轉(zhuǎn)為0。
是不是覺(jué)得只用加法有點(diǎn)空虛,好歹也用個(gè)SUM啊……
當(dāng)然行,但是SUM會(huì)忽略區(qū)域中的文本內(nèi)容,所以用SUM時(shí),得做點(diǎn)小動(dòng)作,如下面的方法。
思路七:錯(cuò)誤值判斷法③
公式:=IFERROR(SUM(B3:E3*1),0)
在單元格中輸入以后,注意要按CTRL+SHIFT+ENTER三鍵。
思路:
這個(gè)公式的關(guān)鍵在SUM(B3:E3*1),它將區(qū)域中的每個(gè)單元格先乘1,這樣可以讓“未付款”的單元格出現(xiàn)錯(cuò)誤值。
明白這一點(diǎn)的話,公式還能改成=IFERROR(SUM(B3:E3/1),0),或者=IFERROR(SUM(--B3:E3),0),這樣比一個(gè)個(gè)相加的方法方便快捷多了。
擴(kuò)展思維:
如果不習(xí)慣三鍵的話,可以把這幾個(gè)公式中的SUM改成SUMPRODUCT。
至此,一個(gè)簡(jiǎn)單的問(wèn)題就已經(jīng)出現(xiàn)了近十種解法,用到的都是一些最基礎(chǔ)的函數(shù)?;蛟S你已經(jīng)從這些不同思路得到啟發(fā)!這是一個(gè)好的開(kāi)始,不妨找一下你以前會(huì)解決的問(wèn)題,去看看是否有其他方法。
如果這篇文章讓你有所收獲的話,歡迎分享給大家。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
函數(shù)小白的福利來(lái)了,不會(huì)函數(shù)也能匯總數(shù)據(jù)
Excel銷(xiāo)售統(tǒng)計(jì)只要會(huì)這6個(gè)函數(shù)就可以了
版權(quán)申明:
本文作者老菜鳥(niǎo);同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- 一個(gè)公式搞定空行的動(dòng)態(tài)插入
- 多功能銷(xiāo)售日歷模板制作
- 用正則函數(shù)快速拆分單元格中的同類(lèi)數(shù)據(jù)
- 用免費(fèi)AI將圖片批量轉(zhuǎn)表格
- 先入先出出入庫(kù)模板中出庫(kù)批次的自動(dòng)匹配
- 用SUBSTITUTE替換法為產(chǎn)品找到最匹配的類(lèi)別
- SHEETSNAME,一鍵獲得所有工作表名稱(chēng)完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)