如下价格表:
如下是查询表:
举例,设定始发地为义乌,目的地为北京,重量为1680.57,对应价格为1.6。 金额即是:=1680.57*1.6,得到的金额再和最低价200相比较,以获取最大值,即是:=MAX(1680.57*1.6,200)。 对于多条件查找,LOOKUP函数最合适,其语法为: =LOOKUP(1,0/((条件1)*(条件2)),返回区域) 最低价比较简单。公式为:
=LOOKUP(1,0/((B2=价格表!$A$3:$A$24)*(FIND(C2,价格表!$B$3:$B$24))),价格表!$J$3:$J$24) 对于区间单价,有些麻烦,首先要判断在哪个区间。 为便于判断在哪个区间,我们在第一行将各区间下限写出来。
在有了下限后,可借助MATCH函数模糊查找,判断在哪列。
之后再借助OFFSET函数,引用此列的区域。OFFSET函数引用区域时,公式不可直接写在一个单元格里,不然看不出效果。 OFFSET(价格表!$B$3:$B$24,0,MATCH(A2,价格表!$C$1:$I$1) 现在即可查询单价,公式为: =LOOKUP(1,0/((B2=价格表!$A$3:$A$24)*(FIND(C2,价格表!$B$3:$B$24))),OFFSET(价格表!$B$3:$B$24,0,MATCH(A2,价格表!$C$1:$I$1))) 在单价出来之后,金额也会跟着出来。
=A2*LOOKUP(1,0/((B2=价格表!$A$3:$A$24)*(FIND(C2,价格表!$B$3:$B$24))),OFFSET(价格表!$B$3:$B$24,0,MATCH(A2,价格表!$C$1:$I$1))) 最低价与金额比较,即可获取最大值。
最后,再将所有公式合并起来,嵌套ROUND函数即可搞定。
=ROUND(MAX(LOOKUP(1,0/((B2=价格表!$A$3:$A$24)*(FIND(C2,价格表!$B$3:$B$24))),OFFSET(价格表!$B$3:$B$24,0,MATCH(A2,价格表!$C$1:$I$1)))*A2,LOOKUP(1,0/((B2=价格表!$A$3:$A$24)*(FIND(C2,价格表!$B$3:$B$24))),价格表!$J$3:$J$24)),2) 对于上述案例,大家要尝试着拆分开,再组合起来,更容易理解一些。
|