一个Excel宏,省下一天的时间,作为顾问必备技能,你值得拥有

今天遇到一个棘手的问题，需要给300多家公司代码分配产品组、分销渠道、销售办公室、销售组，花了一天的时间终于搞定了，别想错了，我可没有一行一行的复制粘贴，而是开发了一个Excel宏，一键全处理。可以动态获取Sheet1和Sheet2的列数及行数，数据任意组合。

下面就给大家详细介绍下，先上菜，再介绍厨房。

使用示例

Sheet1（3列数据）

Sheet2（2列数据）

运行结果

Sheet3结果（共20行）

数据说明

• Sheet1数据
  
  ：4行销售主数据（销售组织、产品组、分销渠道的组合）
• Sheet2数据
  
  ：5行销售办公室数据（销售办公室、销售组的组合）
• 结果
  
  ：每行Sheet1数据与所有Sheet2数据进行组合
• 每个销售组织/产品组/分销渠道的组合，都会匹配所有可能的销售办公室和销售组
• 共生成4 × 5 = 20种组合

执行后的提示信息

运行宏后，会弹出消息框显示统计信息：

处理完成！ Sheet1: 4行 × 3列 Sheet2: 5行 × 2列 结果: 20行 × 5列 

操作步骤也很简单

1. 按 Alt + F8 运行 宏
2. 查看Sheet3的结果

实际应用场景

1.这种在以下场景中很有用： – 销售授权矩阵：为每个销售组织-产品组组合，赋予所有可能的销售办公室权限 – 数据准备：为后续的数据分析或系统导入准备完整的组合数据 – 交叉分析：生成所有可能的维度组合进行业务分析

验证数据

您可以通过以下方式验证结果： 1. 运行宏后，Sheet3中应该正好有20行数据 2. 列标题会自动添加”Sheet1_”和”Sheet2_”前缀 3. 所有Sheet1的数据都会与所有Sheet2的数据完全匹配一次

如果您需要调整输出格式或列顺序，可以修改代码中表头输出的部分。

注意事项

1. 表头位置
   
   ：默认表头在第1行，数据从第2行开始
2. 空列处理
   
   ：自动忽略中间的空列，只处理有数据的连续列区域
3. 列名冲突
   
   ：自动添加”Sheet1_”和”Sheet2_”前缀，避免列名重复
4. 性能
   
   ：使用数组操作，即使数据量较大也能高效运行
5. 错误处理
   
   ：包含基本的数据验证和错误提示

收费情况

• 如需要请加我微信，直接发你Excel，收费1元，终身维护。

处理逻辑说明（如果你不想看代码，下面的内容请忽略）

1. 动态获取列数

' 获取Sheet1的列数（从第1行获取非空列） lastCol1 = ws1.Cells(headerRow, ws1.Columns.Count).End(xlToLeft).Column 

• 使用 End(xlToLeft) 从最右侧向左查找最后一个非空单元格
• 自动识别数据区域的实际列数

2. 动态获取行数

' 获取Sheet1的最后数据行（从A列判断） lastRow1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row 

• 从最后一行向上查找最后一个非空行

3. 动态读取所有列数据

' 将Sheet1数据读入数组（包含所有列） arr1 = ws1.Range(ws1.Cells(headerRow + 1, 1), ws1.Cells(lastRow1, lastCol1)).Value 

• 使用动态范围，自动包含所有数据列

4. 动态输出表头

' 添加Sheet1的表头 For col = 1 To lastCol1     ws3.Cells(1, col).Value = "Sheet1_" & ws1.Cells(headerRow, col).Value Next col 

• 为每一列添加前缀 “Sheet1_” 或 “Sheet2_”，避免列名冲突
• 保留原始列名

5. 动态输出数据

' 输出Sheet1的所有列 For col = 1 To lastCol1     ws3.Cells(outputRow, col).Value = arr1(i, col) Next col  ' 输出Sheet2的所有列 For col = 1 To lastCol2     ws3.Cells(outputRow, lastCol1 + col).Value = arr2(j, col) Next col 

• 使用循环动态输出所有列
• Sheet2的列自动接在Sheet1列后面