如何用excel計算復(fù)雜的產(chǎn)品成本?簡單的求和函數(shù)就能KO它!
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-09-06 09:28:19點擊:5712
編按:
哈嘍,大家好!最近有位小伙伴被一個計算產(chǎn)品成本的問題難住了,要求是根據(jù)配件成本核算出成品的成本。這個問題看上去似乎有點復(fù)雜,感覺一下子想不出好的解決辦法,實際上,卻非常簡單,而且僅用常見的求和函數(shù),就能輕松解決問題,話不多說,趕緊來看看吧!
今天分享的這個問題來源于一位群友的求助,從平時的表現(xiàn)來看,這位朋友的技術(shù)還是不錯的,經(jīng)常為群里其他的小伙伴提供幫助,但是面對他自己的問題時,好像一下子沒了辦法。
其實他遇到的這個問題相信大家一看就能明白,問題并不是太難理解,但是解決起來好像不知道如何下手。問題如圖所示:
就是生產(chǎn)企業(yè)核算產(chǎn)品成本的一類問題。
表中有很多種配件,不同的配件組合成一個個成品,相同配件在一個產(chǎn)品中只出現(xiàn)一次。現(xiàn)在的問題是根據(jù)配件成本核算出成品的成本,例如成品1的成本就是200.84+953.61+48.83=1203.28。
相信這個計算規(guī)則大家都能看明白,在實際環(huán)境中,產(chǎn)品和配件都遠不止這9種,如果靠一個一個手工去算,效率低不說,還容易出錯。
那么對于這樣一個問題,有沒有一個公式可以幫我們得到正確的結(jié)果呢?
肯定有,而且不止一種方法。
今天和大家分享兩個比較容易理解的公式。
公式1:SUM-SUMIF組合
具體公式為=SUM(SUMIF($J$2:$J$10,B2:G2,$K$2:$K$10)),來看一下操作方法。
這是個數(shù)組公式,輸入完成后需要按Ctrl+Shift+Enter,會自動在公式兩邊添加大括號,得到結(jié)果。
公式的核心部分當(dāng)然是SUMIF,關(guān)于這個函數(shù)的基礎(chǔ)用法可以參考以前的教程《求和,我是認(rèn)真的(Excel函數(shù)教程)》。
與基本用法不同之處在于,本例中SUMIF的第二參數(shù),也就是求和的條件是一個區(qū)域:
當(dāng)求和條件為多個值或者多個單元格時,SUMIF會得到一組數(shù)據(jù),可以使用F9鍵看到這個結(jié)果。
通俗一點說,SUMIF在這里實現(xiàn)了將每個配件對應(yīng)的成本計算出來,再由SUM完成合計成本的任務(wù)。
講到這里,相信大家對于這個公式的套路應(yīng)該明白了。
可見一些看似麻煩的問題,只要找到正確的思路,用一些常用的函數(shù)就能搞定。
其實對于這個問題來說,用兩個函數(shù)都有點多余,一個SUMPRODUCT就可以輕松解決。
公式2:SUMPRODUCT函數(shù)
具體公式為:=SUMPRODUCT((B2:G2=$J$2:$J$10)*$K$2:$K$10),來看一下操作方法。
這個公式看起來比第一個公式短,但是理解起來稍微有點難度,公式利用了邏輯值和數(shù)組的計算規(guī)則實現(xiàn)了最終的結(jié)果。
(B2:G2=$J$2:$J$10)這部分通過比較配置清單與配件名稱列表中的每一個數(shù)據(jù),得到一大堆邏輯值:
看起來密密麻麻的,其實仔細觀察,還是有一定的規(guī)律性。例如六個邏輯值之間是一個分號,也就是說用B2:G2中的數(shù)據(jù)先與J2進行比較,沒有相符的,得到一組FALSE,然后繼續(xù)用B2:G2中的數(shù)據(jù)與J3進行比較,以此類推,直到與J10比較后才結(jié)束這個過程。
箭頭所指的位置表示在第三輪比較中,匹配到配件3,因此得到一個TRUE,后面的都是如此。
雖然這一大堆邏輯值,看起來很多,但實際有用的只有TRUE。邏輯值有個特性,FALSE在計算時等于0,TRUE在計算時等于1。將比較結(jié)果與成本所在的區(qū)域相乘后,得到一組數(shù)字。
這樣看起來就清楚了很多,SUMPRODUCT函數(shù)的功能只是對這一組數(shù)字求和。
是不是覺得SUMPRODUCT很厲害,關(guān)于這個函數(shù),之前有好幾篇教程,推薦大家再去看看,溫故知新,一定會有所收獲。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
SUMPRODUCT的應(yīng)用《加了*的 SUMPRODUCT函數(shù)無所不能》
SUMIF函數(shù)出錯了《無往而不利的SUMIF面對這種條件求和竟然傻眼了!》
常見公式《我花了6小時,整理出這10個職場人士最常用的excel公式(建議收藏)》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的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單元格中的算式,四種求和方法請收好!