小型物流公司會有托運(yùn)業(yè)務(wù)，一般會有指定的線路，在指定時間內(nèi)進(jìn)行運(yùn)輸配送。有些商家可能會采取托運(yùn)方式進(jìn)行發(fā)貨，將貨品提前放在物流公司，公司統(tǒng)一發(fā)貨到達(dá)目的地。

托運(yùn)的價格有些是按照體積計(jì)算，不同路線，不同體積會有不同價格。

由于同個路線可以進(jìn)行合并一個車發(fā)貨，所以有一種計(jì)價方式，是依據(jù)合并的體積進(jìn)行計(jì)價。

如下圖：現(xiàn)在，我們需要計(jì)算出客戶的報價。

完成這個效果，只需要編寫函數(shù)公式，這里會涉及使用到三個函數(shù)。點(diǎn)我領(lǐng)取《35個常用函數(shù)說明》

下面，我們一起來看看。

sumif 函數(shù)，對相同路線，進(jìn)行匯總求和。

由于具體報價是對同個路線匯總后，再進(jìn)行查詢的。所以在利用查詢函數(shù)查詢之前，我們需要先用 sumif 函數(shù)按條件（相同路線）對體積進(jìn)行匯總求和。

公式如下：

=SUMIF($B$2:$B$33,B2,$C$2:$C$33)




sumif 函數(shù)的語法規(guī)則：

=sumif(條件區(qū)域,條件,求和區(qū)域)

對應(yīng)的也就是 :

條件區(qū)域：$B$2:$B$33, 城市這一列

條件：B2, 江門佛山

求和區(qū)域：$C$2:$C$33，體積列。

匯總之后，我們需要將體積跟區(qū)間進(jìn)行查詢匹配。

match 函數(shù)，匹配對應(yīng)位置。

由于參數(shù)表是一個二維表，也就是我們需要進(jìn)行交叉匹配。這里我們先使用 match 函數(shù)匹配對應(yīng)體積區(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 大于

對應(yīng)公式：

查找值：sumif 匯總后的值

查找范圍：$H$1:$L$1，不同體積數(shù)的列表

查找類型：1 小于。比如說 0-5 這個區(qū)間內(nèi)，體積數(shù)值是 2 返回 0 這一列的位置，所以這里要寫小于也就是 1。

match 函數(shù)得出的數(shù)值+1，對應(yīng)的位置就是 vlookup 函數(shù)中的第三參數(shù)——結(jié)果需要返回的列數(shù)。

所以下面我們用 vlookup 函數(shù)查詢最終結(jié)果。

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ū)域,要返回的結(jié)果在查找區(qū)域的第幾列,精確匹配或近似匹配)

要查找的值：B2 ，江門佛山

查找區(qū)域：$G$2:$L$13 參數(shù)表

要返回的結(jié)果在查找區(qū)域的第幾列：根據(jù) match 函數(shù)所得的列數(shù)+1。因?yàn)榱袛?shù)從查找列開始數(shù)的，所以需要加上 1。

精確匹配或近似匹配：0 精確匹配。

現(xiàn)在，對應(yīng)的報價就得出來了。

總結(jié)一下

本文講解的是，按條件匯總值后，進(jìn)行交叉近似匹配的公式編寫。

? sumif 函數(shù)先匯總同路線的值；

? 針對 sumif 函數(shù)得出的結(jié)果，利用 match 函數(shù)去匹配不同體積數(shù)（0,5,10,20,50）的相對位置；

? 用 vlookup 函數(shù)返回最終結(jié)果，match 函數(shù)+1 得出的結(jié)果就是 vlookup 第三參數(shù)的列數(shù)。

整個完成的過程并不難，這里的關(guān)鍵在于，對需求進(jìn)行拆解后，利用函數(shù)工具，去一步步完成我們的所需效果。

當(dāng)然，有些小伙伴可能不想局限于函數(shù)做法，下面我來小小的延伸一下 M 函數(shù)做法。有興趣的小伙伴可以在文末領(lǐng)取源文件研究。

函數(shù)在查找匹配的方面，其實(shí)是優(yōu)于 pq 匹配的方法的。因?yàn)椴檎液瘮?shù)有對應(yīng)的查找區(qū)域，以及對應(yīng)的匹配區(qū)域。而 pq，返回匹配值，需要對應(yīng)的索引值。

下面，是我簡單寫的 pq 方法，有興趣的小伙伴可以自己去拆解一下。

• let

• ? ?源 = Excel.CurrentWorkbook(){[Name="表 1"]}[Content],

• ? ?lst = List.Transform(List.Skip(Table.ColumnNames(表 2)),Number.From),

• ? ?result = Table.AddColumn(源,

• ? ? ? ?"報價",

• ? ? ? ?each?

• ? ? ? ? ? ? ?let

• ? ? ? ? ? ? ? ? data=Table.Group(源,"城市",{{"a",(x)=>List.Sum(x[#"體積(方)"])}}){[城市=_[城市]]},

• ? ? ? ? ? ? ? ? para=Record.ToList(表 2{[線路=data[城市]]}),

• ? ? ? ? ? ? ? ? c=List.Count(List.Select(lst(z)=>z<=Number.From(data[a])))

• ? ? ? ? ? ? ?in para{c})

• in

• ? ?result

好了，關(guān)于文章還有什么不懂的，小伙伴們可以留言，一起來聊個五毛錢的~

Excel 操作簡單、應(yīng)用廣泛，如果你想系統(tǒng)學(xué)習(xí)，讓你的工作能力有機(jī)會增強(qiáng) 10 倍！

那就趕緊點(diǎn)擊下方小程序，加入《3 天 Excel 集訓(xùn)營》吧！

現(xiàn)在報名，只需 1 元！

還送35 個常見函數(shù)使用說明……

快保存下方圖片掃碼報名

加入《3 天 Excel 集訓(xùn)營》吧！

*廣告