如何基于Excel输入表数据填充输出表？ERP订单生成方案问询

一、Excel公式实现（适用于Excel 365/2021及以上版本） #

利用动态数组公式可自动生成并溢出结果，无需手动下拉填充。假设输入表的客户名称在B5:F5，产品名称在A6:A20，订单数量在B6:F20，输出表表头H5:J5固定为「客户」「产品」「数量」：

在输出表的H6单元格输入以下公式：

=LET(
    客户区, B5:F5,
    产品区, A6:A20,
    数量区, B6:F20,
    客户序列, TOCOL(客户区, 1) & "",
    产品序列, TOCOL(INDEX(产品区, SEQUENCE(ROWS(产品区)), SEQUENCE(, COLUMNS(客户区))), 1) & "",
    数量序列, TOCOL(数量区, 1),
    FILTER(HSTACK(客户序列, 产品序列, 数量序列), 数量序列>0)
)

• 核心逻辑：
  • LET定义变量简化公式结构
  • TOCOL将二维区域转为一维列，参数1忽略空值
  • INDEX+SEQUENCE生成与客户列数匹配的产品重复序列
  • HSTACK合并三个一维列为二维数组
  • FILTER筛选出数量大于0的有效订单

旧版Excel（无动态数组）兼容方案 #

需按Ctrl+Shift+Enter确认数组公式，手动下拉填充足够行数：

• H6单元格（客户）：

  =IFERROR(INDEX($B$5:$F$5, INT((ROW(A1)-1)/ROWS($A$6:$A$20))+1), "")
  

• I6单元格（产品）：

  =IFERROR(INDEX($A$6:$A$20, MOD(ROW(A1)-1, ROWS($A$6:$A$20))+1), "")
  

• J6单元格（数量）：

  =IFERROR(INDEX($B$6:$F$20, MOD(ROW(A1)-1, ROWS($A$6:$A$20))+1, INT((ROW(A1)-1)/ROWS($A$6:$A$20))+1), "")
  

填充后筛选掉数量为0的行即可。

二、VBA代码实现 #

适合需要自动化更新、处理大数据量的场景：

按Alt+F11打开VBA编辑器，插入模块并粘贴以下代码：

Sub 生成ERP订单表()
    Dim 输入表 As Worksheet
    Dim 输出表 As Worksheet
    Dim 客户行 As Range, 产品列 As Range, 数量区 As Range
    Dim 客户数 As Integer, 产品数 As Integer
    Dim i As Integer, j As Integer, 输出行 As Integer
    
    ' 按需修改工作表名称
    Set 输入表 = ThisWorkbook.Worksheets("Sheet1")
    Set 输出表 = ThisWorkbook.Worksheets("Sheet1")
    
    ' 定义数据区域
    Set 客户行 = 输入表.Range("B5:F5")
    Set 产品列 = 输入表.Range("A6:A20")
    Set 数量区 = 输入表.Range("B6:F20")
    
    客户数 = 客户行.Columns.Count
    产品数 = 产品列.Rows.Count
    输出行 = 6 ' 输出起始行（表头在第5行）
    
    ' 清空原有输出数据（保留表头）
    输出表.Range("H6:J" & 输出表.Cells(输出表.Rows.Count, "H").End(xlUp).Row).ClearContents
    
    ' 遍历生成有效订单
    For i = 1 To 客户数
        For j = 1 To 产品数
            If 数量区.Cells(j, i).Value > 0 Then
                输出表.Cells(输出行, "H").Value = 客户行.Cells(1, i).Value
                输出表.Cells(输出行, "I").Value = 产品列.Cells(j, 1).Value
                输出表.Cells(输出行, "J").Value = 数量区.Cells(j, i).Value
                输出行 = 输出行 + 1
            End If
        Next j
    Next i
    
    MsgBox "订单表生成完成！"
End Sub

• 使用提示：
  1. 修改代码中的工作表名称（若输入/输出不在同一张表）
  2. 运行宏后自动清空旧数据并生成新订单记录
  3. 可添加工作表按钮，实现一键执行

内容的提问来源于stack exchange，提问作者S. Shotez