Excel疑難解答
每個單元格有多個已銷商品代碼,如何統(tǒng)計代碼數(shù)量計算銷售金額?
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2020-08-19 11:59:11點擊:4301
編按:
部落窩推出Excel疑難解答專欄,負責(zé)解答各學(xué)員實際工作中遇到的各類問題。今天是第一個。在一份銷售記錄表中,各單元格記錄了每次銷售的產(chǎn)品代碼。因為一次銷售可能是多個產(chǎn)品,所以同一單元格就記錄了多個代碼。如何直接根據(jù)代碼計算出每個銷售員當(dāng)日的銷售金額呢?教程提供兩個方法給你參考。
今天要和大家分享學(xué)員小王求助的真實案例。
小王是一家快餐店的財務(wù)人員。受疫情影響公司開展了店外銷售業(yè)務(wù),所有銷售采取手工記賬的方式。為了簡化銷售人員的工作量,為每種商品指定了一個字母代碼,營業(yè)員只需要記錄每一單銷售的商品代碼即可。下面是根據(jù)手工記賬登記的銷售記錄表:
右側(cè)為實際銷售記錄,左側(cè)是商品代碼及價格對照表(實際上有很多品種)。
業(yè)務(wù)開展得很順利,但是小王的麻煩事來了。以前都是直接在電腦上下賬,通過現(xiàn)成的收銀報表即可快速算出每個人每天的應(yīng)收合計,現(xiàn)在根據(jù)上面的銷售記錄表進行計算就很讓人頭疼,麻煩不說,還容易出錯。
為此小王向老菜鳥求助,想看看是否有什么公式可實現(xiàn)自動求和。
這個問題乍一看真的有點無從下手,但是經(jīng)過一番摸索,還真的找到了求和公式。下面就和大家一起來梳理一下解決問題的過程。
問題的難點在于每筆銷售中包含的商品代碼不是唯一的。如果能統(tǒng)計出每種代碼的出現(xiàn)次數(shù),然后再來核算金額就比較容易了。
例如先統(tǒng)計焦玉芳一共銷售了多少個A,我們可以使用COUNTIF函數(shù)來實現(xiàn),J2單元格公式為:=COUNTIF(B2:H2,"*A*")
這是COUNTIF最基本的用法,"*A*"中的星號*是通配符,兩邊都加了星號就表示統(tǒng)計含有A的單元格個數(shù)。
注:如果有顧客買了兩個相同的商品,記錄成AA,則此法無法正確統(tǒng)計,因為AA也只能算是一個包含了A的記錄。
統(tǒng)計出A的數(shù)量后,只需要乘以單價就得到焦玉芳銷售的A商品的金額,公式為=COUNTIF(B2:H2,"*A*")*M2
按照這種思路,只需要分別統(tǒng)計出A、B、C、D的金額再相加,就能得到每個人當(dāng)日的銷售金額合計。
但是這樣做的話,如果商品品種很多,公式就會很長或者需要多個輔助列,很麻煩。我們可以借助數(shù)組的應(yīng)用,實現(xiàn)起來就比較方便。
首先將COUNTIF中的"*A*"做一下變化,改成這樣的=COUNTIF(B2:H2,"*"&K2:K5&"*")
注意:這里的條件已經(jīng)不是一個值了,而是一組單元格。直接回車得到的將仍然是第一個產(chǎn)品A的數(shù)量。不用疑惑,繼續(xù)往下看。
該公式可以對單元格K2:K5內(nèi)的數(shù)據(jù)分別進行統(tǒng)計,得到的結(jié)果就是每個代碼的計數(shù)。選中公式按F9鍵可以看到四個數(shù)字:
這四個數(shù)字就表示A、B、C、D的個數(shù)。
在COUNTIF的后面乘以單價區(qū)域,就可以得到每個商品的售價,公式為:
=COUNTIF(B2:H2,"*"&K2:K5&"*")*M2:M5
最后,只要再對這四個數(shù)字求和就能完成某個人的銷售金額計算。
如果使用SUM函數(shù)求和需要三鍵,也就是Ctrl、shift和回車鍵;如果使用SUMPRODUCT函數(shù)求和則無需三鍵,可以直接得到結(jié)果。在I2單元格輸入公式:
=SUMPRODUCT(COUNTIF(B2:H2,"*"&K$2:K$5&"*"),M$2:M$5)
公式中,考慮到下拉填充,商品代碼和單價這兩個區(qū)域使用絕對引用符號$。
公式中的三個區(qū)域,B2:H2是銷售記錄區(qū)域,K$2:K$5是代碼區(qū)域,M$2:M$5是單價區(qū)域,可以根據(jù)實際數(shù)據(jù)進行調(diào)整。
強調(diào):
需要注意的是COUNTIF的第二參數(shù)如果使用通配符,而條件值又是單元格的話,要給星號加引號,同時需要用連接符&進行連接。這個原則同樣適用于SUMIF函數(shù)。
更多解決辦法:
對于這類問題,還有另一個常用的套路,就是ISNUMBER和FIND組合,公式分享給大家,有興趣的朋友可以自己試試分析這個思路:
=SUMPRODUCT(ISNUMBER(FIND($K$2:$K$5,B2:H2))*$M$2:$M$5)
思考題:
如何解決文章中提到的如果同一單元格中出現(xiàn)兩個代碼A或者B的計算?(答案請加群264539405下載)
如果你在實際工作中也遇到了一些麻煩而又不好百度的問題,不妨留言告訴我們。在我們幫你解決問題的同時,大家也能從中得到一些收獲。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
同樣是countifs函數(shù),為什么同事卻使得比你好?原因在這里!
countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個案例分享
TEXT和SUMPRODUCT強強聯(lián)合,只為解決一個“微不足道”的編號問題?
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!