小型物流公司會有托運業(yè)務,一般會有指定的線路,在指定時間內(nèi)進行運輸配送。有些商家可能會采取托運方式進行發(fā)貨,將貨品提前放在物流公司,公司統(tǒng)一發(fā)貨到達目的地。
托運的價格有些是按照體積計算,不同路線,不同體積會有不同價格。
由于同個路線可以進行合并一個車發(fā)貨,所以有一種計價方式,是依據(jù)合并的體積進行計價。
如下圖:現(xiàn)在,我們需要計算出客戶的報價。
完成這個效果,只需要編寫函數(shù)公式,這里會涉及使用到三個函數(shù)。點我領取《35 個常用函數(shù)說明》
下面,我們一起來看看。
1、sumif 函數(shù),對相同路線,進行匯總求和。
由于具體報價是對同個路線匯總后,再進行查詢的。所以在利用查詢函數(shù)查詢之前,我們需要先用 sumif 函數(shù)按條件(相同路線)對體積進行匯總求和。
公式如下:
=SUMIF($B$2:$B$33,B2,$C$2:$C$33)
sumif 函數(shù)的語法規(guī)則:
=sumif (條件區(qū)域,條件,求和區(qū)域)
對應的也就是 :
條件區(qū)域:$B$2:$B$33, 城市這一列
條件:B2, 江門佛山
求和區(qū)域:$C$2:$C$33,體積列。
匯總之后,我們需要將體積跟區(qū)間進行查詢匹配。
2、match 函數(shù),匹配對應位置。
由于參數(shù)表是一個二維表,也就是我們需要進行交叉匹配。這里我們先使用 match 函數(shù)匹配對應體積區(qū)間(0,5,10,20,50)的位置。
公式如下:
=MATCH( SUMIF($B$2:$B$33,B2,$C$2:$C$33), $H$1:$L$1, 1)
match 函數(shù)主要用來匹配位置的。
=MATCH (查找值,查找范圍,查找類型)
第三參數(shù)中,查找類型有三種方式:
0 精確匹配
1 小于
-1 大于
對應公式:
查找值:sumif 匯總后的值
查找范圍:$H$1:$L$1,不同體積數(shù)的列表
查找類型:1 小于。比如說 0-5 這個區(qū)間內(nèi),體積數(shù)值是 2 返回 0 這一列的位置,所以這里要寫小于也就是 1。
match 函數(shù)得出的數(shù)值 + 1,對應的位置就是 vlookup 函數(shù)中的第三參數(shù) —— 結果需要返回的列數(shù)。
所以下面我們用 vlookup 函數(shù)查詢最終結果。
3、vlookup 函數(shù),返回符合條件的值。
利用 vlookup 函數(shù)查詢每個路線下的價格。
公式如下:
=VLOOKUP( B2, $G$2:$L$13, MATCH(SUMIF($B$2:$B$33,B2,$C$2:$C$33),$H$1:$L$1,1)+1, 0)
=VLOOKUP (要查找的值,查找區(qū)域,要返回的結果在查找區(qū)域的第幾列,精確匹配或近似匹配)
要查找的值:B2 ,江門佛山
查找區(qū)域:$G$2:$L$13 參數(shù)表
要返回的結果在查找區(qū)域的第幾列:根據(jù) match 函數(shù)所得的列數(shù) + 1。因為列數(shù)從查找列開始數(shù)的,所以需要加上 1。
精確匹配或近似匹配:0 精確匹配。
現(xiàn)在,對應的報價就得出來了。
4、總結一下
本文講解的是,按條件匯總值后,進行交叉近似匹配的公式編寫。
? sumif 函數(shù)先匯總同路線的值;
? 針對 sumif 函數(shù)得出的結果,利用 match 函數(shù)去匹配不同體積數(shù)(0,5,10,20,50)的相對位置;
? 用 vlookup 函數(shù)返回最終結果,match 函數(shù) + 1 得出的結果就是 vlookup 第三參數(shù)的列數(shù)。
整個完成的過程并不難,這里的關鍵在于,對需求進行拆解后,利用函數(shù)工具,去一步步完成我們的所需效果。
本文來自微信公眾號:秋葉 Excel (ID:excel100),作者:小爽
廣告聲明:文內(nèi)含有的對外跳轉(zhuǎn)鏈接(包括不限于超鏈接、二維碼、口令等形式),用于傳遞更多信息,節(jié)省甄選時間,結果僅供參考,IT之家所有文章均包含本聲明。