第325讲: VBA(类 Excel 思路) 和 Python(类 SQL 思路) 来实现这个需求:多表数据自动匹配与填充(VLOOKUP升级版)
- 2026-11-21 12:35:24
做数据分析的朋友,大概率都经历过这种“机械性加班”:
一张订单表,只有「商品编码」;
另一张商品信息表,存着「单价」「库存」「供应商」「成本」「保质期」……

需求很朴素:把商品信息表里的多列数据,按商品编码“贴”到订单表里。
很多人的第一反应还是老三样:VLOOKUP、INDEX+MATCH,甚至直接手动复制粘贴。一旦字段多、数据量大,不仅效率低,还容易因为漏拉公式、错选区域导致对账翻车。
今天这讲,我们不只讲“怎么做”,而是从底层逻辑 → 实战写法 → 性能对比 → 避坑细节四个层面,把这件事彻底讲透。重点是:
Excel/VBA 方案:传统但依然高频,适合轻量场景
Python/pandas 方案:类 SQL 的关联方式,一次完成多列匹配,适合中大型数据
无论你是财务、运营,还是正在转型的数据分析师,这篇都能帮你把“多表匹配”从体力活变成自动化流程。
一、先还原一个真实业务场景
假设我们有两张表,结构如下:
1. 订单明细表(主表 / 左表)
订单ID | 商品编码 | 销售数量 |
|---|---|---|
1001 | A001 | 5 |
1002 | B002 | 3 |
1003 | A003 | 8 |
2. 商品信息表(维度表 / 右表)
商品编码 | 商品名称 | 单价 | 库存 | 供应商 | 成本 |
|---|---|---|---|---|---|
A001 | 无线鼠标 | 89 | 120 | 深圳电子 | 60 |
A002 | 键盘 | 129 | 80 | 东莞外设 | 90 |
A003 | 显示器 | 899 | 45 | 苏州光电 | 650 |
B002 | U盘 | 59 | 200 | 广州存储 | 35 |
目标结果(订单表最终形态):
订单ID | 商品编码 | 销售数量 | 商品名称 | 单价 | 库存 | 供应商 | 成本 |
|---|---|---|---|---|---|---|---|
1001 | A001 | 5 | 无线鼠标 | 89 | 120 | 深圳电子 | 60 |
1002 | B002 | 3 | U盘 | 59 | 200 | 广州存储 | 35 |
1003 | A003 | 8 | 显示器 | 899 | 45 | 苏州光电 | 650 |
下面我们分别用 VBA(类 Excel 思路) 和 Python(类 SQL 思路) 来实现这个需求,并重点对比两者的差异。

二、VBA 方案:传统但依然实用
1. 为什么 VBA 还在被大量使用?
在绝大多数公司里,报表流转仍然以 Excel 文件为主。
即便你会 Python,最终交付给业务部门的可能还是一个 .xlsx。
在这种环境下,VBA 的优势在于“就地解决”:不依赖环境、不引入新工具,直接在 Excel 内部完成。
针对本例,常见做法有三种:
VLOOKUP 逐列写公式
INDEX + MATCH 组合(更稳健)
VBA 批量写入公式或一次性转成“值”
我们重点讲 INDEX + MATCH,因为它比 VLOOKUP 更灵活,也更不容易出错。
2. VBA + INDEX-MATCH 实现多列匹配
(1)核心函数逻辑回顾
=INDEX(返回列, MATCH(查找值, 查找列, 0))MATCH:找到“商品编码”在商品信息表中的行号INDEX:根据行号,从指定列取对应值
(2)示例:在订单表中填充“商品名称”
假设:
订单表:
Sheet1商品信息表:
Sheet2订单表商品编码在 B 列
商品信息表商品编码在 A 列
商品名称在商品信息表 B 列
从第 2 行开始有数据
Sub FillProductInfo()Dim wsOrder As Worksheet, wsProd As WorksheetDim lastRow As LongDim i As LongSet wsOrder = ThisWorkbook.Sheets("Sheet1")Set wsProd = ThisWorkbook.Sheets("Sheet2")' 订单表最后一行lastRow = wsOrder.Cells(wsOrder.Rows.Count, "B").End(xlUp).Row' 从第2行开始循环For i = 2 To lastRow' 商品名称wsOrder.Cells(i, "D").Value = Application.WorksheetFunction.Index( _wsProd.Range("B:B"), _Application.WorksheetFunction.Match(wsOrder.Cells(i, "B").Value, wsProd.Range("A:A"), 0))' 单价wsOrder.Cells(i, "E").Value = Application.WorksheetFunction.Index( _wsProd.Range("C:C"), _Application.WorksheetFunction.Match(wsOrder.Cells(i, "B").Value, wsProd.Range("A:A"), 0))' 库存wsOrder.Cells(i, "F").Value = Application.WorksheetFunction.Index( _wsProd.Range("D:D"), _Application.WorksheetFunction.Match(wsOrder.Cells(i, "B").Value, wsProd.Range("A:A"), 0))' 供应商wsOrder.Cells(i, "G").Value = Application.WorksheetFunction.Index( _wsProd.Range("E:E"), _Application.WorksheetFunction.Match(wsOrder.Cells(i, "B").Value, wsProd.Range("A:A"), 0))' 成本wsOrder.Cells(i, "H").Value = Application.WorksheetFunction.Index( _wsProd.Range("F:F"), _Application.WorksheetFunction.Match(wsOrder.Cells(i, "B").Value, wsProd.Range("A:A"), 0))Next iMsgBox "多列匹配完成!"End Sub
(3)这段代码的“潜台词”
逐行扫描:VBA 本质是循环,每一行都要重新
MATCH一次逐列填充:每一列都要单独写一次
INDEX强依赖 Excel:必须在 Excel 环境中运行
易错点:如果商品编码不存在,
MATCH会报错,需要加错误处理(后面会讲)
(4)工程级优化:错误处理 + 只写值
实际工作中,商品编码可能有脏数据(空格、不存在的编码等),建议这样改:
On Error Resume Next' 匹配代码If Err.Number <> 0 ThenwsOrder.Cells(i, "D").Value = "未找到"Err.ClearEnd IfOn Error GoTo 0
另外,为了避免文件体积膨胀,可以在匹配完成后 一次性将公式转为数值:
wsOrder.Range("D2:H" & lastRow).Value = wsOrder.Range("D2:H" & lastRow).Value3. VBA 方案的适用边界
✅ 适合:
数据量不大(万行以内)
必须交付 Excel 文件
不想引入 Python 环境
❌ 不适合:
几十万行以上的数据
需要频繁、定时执行的自动化任务
多表、多条件复杂关联
这也是为什么越来越多人转向 Python。
三、Python 方案:类 SQL 的一次性关联
1. pandas.merge() 的核心思想
如果你用过 SQL,那么 pandas.merge()对你来说几乎没有学习成本。

它的本质就是:
按“键”(key)把两张表拼在一起
对应到 SQL,就是:
SELECT *
FROM 订单表
LEFT JOIN 商品信息表
ON 订单表.商品编码 = 商品信息表.商品编码;在 pandas 里,这一行就够:
pd.merge(左表, 右表, on='键', how='left')不需要循环,不需要逐列写公式,一次完成多列匹配。
2. 实战:用 Python 完成订单表多列填充
(1)准备环境与数据
import pandas as pd# 订单表orders = pd.DataFrame({'订单ID': [1001, 1002, 1003],'商品编码': ['A001', 'B002', 'A003'],'销售数量': [5, 3, 8]})# 商品信息表products = pd.DataFrame({'商品编码': ['A001', 'A002', 'A003', 'B002'],'商品名称': ['无线鼠标', '键盘', '显示器', 'U盘'],'单价': [89, 129, 899, 59],'库存': [120, 80, 45, 200],'供应商': ['深圳电子', '东莞外设', '苏州光电', '广州存储'],'成本': [60, 90, 650, 35]})(2)核心操作:merge 一次搞定result = pd.merge(orders,products,on='商品编码',how='left')print(result)
输出结果:
订单ID 商品编码 销售数量 商品名称 单价 库存 供应商 成本
0 1001 A001 5 无线鼠标 89 120 深圳电子 60
1 1002 B002 3 U盘 59 200 广州存储 35
2 1003 A003 8 显示器 899 45 苏州光电 650✅ 所有列一次性匹配完成
✅ 不需要循环
✅ 逻辑清晰,可读性强
3. merge 的四种关联方式(必会)
参数 | 含义 | 类比 SQL |
|---|---|---|
| 左连接 | LEFT JOIN |
| 右连接 | RIGHT JOIN |
| 内连接 | INNER JOIN |
| 全外连接 | FULL OUTER JOIN |
在本例中,我们使用 left,确保订单表的所有记录都被保留,即使某些商品在商品信息表中不存在。
4. 进阶:处理“键名不一致”的情况
现实中,两张表的关联字段名称经常不一样,比如:
订单表:
sku_id商品表:
product_code
这时用 left_on/ right_on:
result = pd.merge(orders,products,left_on='sku_id',right_on='product_code',how='left')
5. 性能对比:为什么 Python 更快?
维度 | VBA | Python/pandas |
|---|---|---|
执行逻辑 | 逐行循环 | 向量化运算 |
多列匹配 | 多次 MATCH | 一次 merge |
大数据表现 | 较慢 | 极快 |
可读性 | 冗长 | 简洁 |
可维护性 | 差 | 好 |
经验值参考:
1 万行以内:VBA 尚可接受
10 万~100 万行:强烈建议 Python
多表关联、复杂清洗:Python 完胜
四、关键对照:类 SQL 关联 vs 逐列公式填充
对比项 | VBA(逐列公式) | Python(类 SQL 关联) |
|---|---|---|
思维方式 | Excel 单元格思维 | 表(DataFrame)思维 |
关联逻辑 | 多次查找 | 一次关联 |
代码复用 | 低 | 高 |
自动化程度 | 一般 | 极高 |
学习曲线 | 低 | 中 |
工程化能力 | 弱 | 强 |
一句话总结:
VBA 是在“填格子”,Python 是在“操作表”。
五、实战建议:如何选择你的工具?
日常报表、小数据量、交付 Excel → VBA / 原生 Excel 函数
数据清洗、大表关联、自动化脚本 → Python + pandas
团队协作、版本管理、调度任务 → Python + Git + 定时任务
如果你正在从 Excel 向数据分析转型,merge()是你必须跨过的门槛之一。它几乎等同于 SQL 中的 JOIN,是数据分析师的“基本功”。

六、避坑指南(经验之谈)
关联前先去重
确保“右表”(商品信息表)的关联键唯一,否则
merge会产生笛卡尔积。注意数据类型一致
一个为字符串
'1001',一个为整数1001,会导致匹配失败。处理缺失值
merge(how='left')后,未匹配到的行会是NaN,可用fillna()填充默认值。VBA 中避免整列引用
不要用
Range("A:A"),改用Range("A2:A" & lastRow),否则速度会明显变慢。
七、课后练习(选择题)
在使用
pandas.merge()时,如果想保留左表的所有记录,即使右表中没有匹配项,应使用哪种连接方式?A. inner
B. left
C. right
D. outer
VBA 中使用
INDEX+MATCH进行多列匹配时,主要的时间开销来源于?A. 一次性读取内存
B. 逐行循环和重复查找
C. 屏幕刷新
D. 变量声明
以下关于
VLOOKUP与INDEX+MATCH的说法,正确的是?A.
VLOOKUP可以向左查找B.
INDEX+MATCH比VLOOKUP更灵活C.
VLOOKUP计算速度一定更快D.
INDEX+MATCH不支持精确匹配在 pandas 中,如果左右两张表的关联字段名称不同,应该使用什么参数?
A.
onB.
left_on和right_onC.
keyD.
merge_key当商品信息表中存在重复的商品编码时,直接使用
merge()最可能导致的问题是?A. 程序报错终止
B. 产生笛卡尔积,行数异常增加
C. 自动去重
D. 匹配速度显著提升
八、答案
B
B
B
B
B
