=SUMPRODUCT(--TEXT(OFFSET(收费标准!F$2,MATCH(A2,收费标准!A:A,)-2*(D2="一级城市")-(D2="二级城市"),(LEFT(E2,2)="经济")*2,,2)*IF({1,0},1,ROUNDUP((F2-1)/0.5,)),"0.00;!0;0"))
公式其实只有两部分,第一部分OFFSET(收费标准!F$2,MATCH(A2,收费标准!A:A,)-2*(D2="一级城市")-(D2="二级城市"),(LEFT(E2,2)="经济")*2,,2);第二部分IF({1,0},1,ROUNDUP((F2-1)/0.5,))
先说第一部分,是通过行列偏移函数offset来获取收费标准,MATCH(A2,收费标准!A:A,)-2*(D2="一级城市")-(D2="二级城市")先通过省份来查找在收费标准表里的位置,再通过判断是否是一级城市或二级城市来上移来找到需要对应的行数;
(LEFT(E2,2)="经济")*2通过判断是否是经济快递或标准快递,来左右移动列数,如果是经济快递,则向右移动两列,最后得到首重或续重所对应的收费标准。
第二部分IF({1,0},1,ROUNDUP((F2-1)/0.5,)),由于首重始终是1公斤,此项不变,主要是第二项,即续重=F2-1,续重以0.5公斤为1单位,不足0.5按0.5计算,则通过ROUNDUP((F2-1)/0.5,)得到续重的次数。
当总重量小于1公斤时,续重为负数,因此用TEXT函数来对续重为负的强制设为0,不参与计算,即TEXT(OFFSET(收费标准!F$2,MATCH(A2,收费标准!A:A,)-2*(D2="一级城市")-(D2="二级城市"),(LEFT(E2,2)="经济")*2,,2)*IF({1,0},1,ROUNDUP((F2-1)/0.5,)),"0.00;!0;0")
最后通过求和函数SUMPRODUCT对首重金额与续重金额进行求和。
|