二維碼 購物車
部落窩在線教育歡迎您!

Excel疑難解答

每個單元格有多個已銷商品代碼,如何統(tǒng)計代碼數(shù)量計算銷售金額?

?

作者:老菜鳥來源:部落窩教育發(fā)布時間:2020-08-19 11:59:11點擊:4301

分享到:
0
收藏    收藏人氣:0人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

部落窩推出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)計出AB、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、CD的個數(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ù)。

更多解決辦法:

對于這類問題,還有另一個常用的套路,就是ISNUMBERFIND組合,公式分享給大家,有興趣的朋友可以自己試試分析這個思路:

=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

IMG_256

相關(guān)推薦:

同樣是countifs函數(shù),為什么同事卻使得比你好?原因在這里!

countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個案例分享

加了*的 SUMPRODUCT函數(shù)無所不能

TEXT和SUMPRODUCT強強聯(lián)合,只為解決一個“微不足道”的編號問題?