使用Excel的VLOOKUP处理区间数据并核查费用差异

一、文件数据说明 #

1. source_data.xlsx（标准成本对照表） #

DISTANCE     COST    
1-100        4800
101-120      5100    
121-140      5500    
141-160      5900    
161-180      6200    
181-200      6600    
210-220      6900   
221-240      7200

2. Analysis.xlsx（实际行程数据表） #

loading_station  distance_travel     total_cost    status    
PUGU                  40                4000       PAID        
PUGU                  80                3200       PAID    
MOROGORO              50                5000       PAID    
MOROGORO              220               30400      PAID    
DODOMA                150               5100       PAID   
KIGOMA                90                2345       PAID   
DODOMA                230               6000       PAID    
DODOMA                180               16500      PAID    
KIGOMA                32                3000       PAID    
DODOMA                45                6000       PAID   
DODOMA                65                5000       PAID    
KIGOMA                77                1000       PAID    
KIGOMA                90                4000       PAID

二、需求 #

基于source_data里的标准成本区间，核对Analysis中的实际费用，识别多付/少付情况，需新增两列：

• source_cost：通过VLOOKUP匹配对应距离的标准成本
• Difference：计算实际费用与标准成本的差值（正数=多付，负数=少付）

三、操作步骤 #

1. 预处理source_data.xlsx

   • 新增一列Start_Distance，用公式提取每个区间的起始数值：

     =LEFT(A2,FIND("-",A2)-1)*1
     

     下拉填充所有行，得到每个区间的起始值（比如1-100对应1，101-120对应101）。
   • 选中Start_Distance和COST列，按Start_Distance升序排序（VLOOKUP模糊匹配要求查找区域必须有序）。

2. 在Analysis.xlsx中计算匹配成本

   • 在E列（原数据最后一列右侧）输入表头source_cost，E2单元格输入公式：

     =VLOOKUP(B2, [source_data.xlsx]Sheet1!$C$2:$D$9, 2, TRUE)
     

     下拉填充所有行，自动匹配对应区间的标准成本。
   • 在F列输入表头Difference，F2单元格输入公式：

     =C2-E2
     

     下拉填充所有行，得到费用差值。

四、预期输出 #

loading_station distance_travel total_cost  status  source_cost Difference    
PUGU               40                4000     PAID     4800        -800    
PUGU               80                3200     PAID     4800        -1600    
MOROGORO           50                5000     PAID     4800         200    
MOROGORO           220               30400    PAID     6900         23500    
DODOMA             150               5100     PAID     5900         -800    
KIGOMA             90                2345     PAID     4800         -2455   
DODOMA             230               6000     PAID     7200         -1200   
DODOMA             180               16500    PAID     6200          10300
KIGOMA             32                3000     PAID     4800          -1800
DODOMA             45                6000     PAID     4800           1200 
DODOMA             65                5000     PAID     4800           200   
KIGOMA             77                1000     PAID     4800           -3800  
KIGOMA             90                4000     PAID     4800           -800

内容的提问来源于stack exchange，提问作者Gastone Dereck