如何将Excel中大型垂直数据集转换为多个表格？

PowerQuery是处理这类Excel数据拆分的最优方案，无需复杂VBA，全程可视化操作+少量M代码即可完成，比SQL更适配Excel本地环境。以下是针对你需求的具体步骤：

假设数据结构 #

先明确你的数据格式（方便对应步骤）：

A1: 19/02/2024（日期）
A2: 华东区（分组表头行，B2/C2为空）
A3: 一月 | 100 | 200
A4: 二月 | 150 | 250
A5: 华北区（分组表头行，B5/C5为空）
A6: 一月 | 80 | 180
...

步骤1：导入数据到PowerQuery #

1. 选中A1到数据最后一行的全部区域
2. 点击Excel顶部「数据」选项卡 → 「从表格/范围」
3. 取消勾选「我的表格有标题」，点击「确定」进入PowerQuery编辑器

步骤2：提取日期并处理数据行 #

打开PowerQuery的「高级编辑器」，替换默认代码为以下内容（注释已标注关键逻辑）：

let
    // 导入原始数据
    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    // 提取A1单元格的日期值，转换为指定文本格式
    DateValue = Source{0}[Column1],
    DateText = Text.From(DateValue, "en-GB"), // 生成"dd/mm/yyyy"格式的日期文本
    // 跳过第一行的日期，处理后续分组数据
    DataRows = Table.Skip(Source, 1),
    // 添加分组标识列：标记表头行（B/C列为空的行）的分组名称
    AddGroupMarker = Table.AddColumn(DataRows, "分组名称", each if [Column2] = null and [Column3] = null then [Column1] else null),
    // 向下填充分组名称，让每个数据行关联到所属分组
    FillGroupNames = Table.FillDown(AddGroupMarker, {"分组名称"}),
    // 过滤掉表头行（只保留B/C列有数据的行）
    FilterDataRows = Table.SelectRows(FillGroupNames, each [Column2] <> null),
    // 按分组名称分组，将每组数据打包为单独的表
    GroupedData = Table.Group(FilterDataRows, {"分组名称"}, {{"组内数据", each _, type table [Column1=any, Column2=any, Column3=any, 分组名称=any]}}),
    // 循环处理每个分组：重命名列、生成指定表名
    GenerateTables = List.Transform(GroupedData[组内数据], (tbl) =>
        let
            CurrentGroupName = tbl{0}[分组名称],
            // 重命名列：首列改为分组名称，B/C列改为Header2/Header3
            RenamedColumns = Table.RenameColumns(tbl, {
                {"Column1", CurrentGroupName},
                {"Column2", "Header2"},
                {"Column3", "Header3"}
            }),
            // 移除辅助的分组名称列
            CleanedTable = Table.RemoveColumns(RenamedColumns, {"分组名称"})
        in
            [表名 = CurrentGroupName & "-" & DateText, 表数据 = CleanedTable]
    )
in
    GenerateTables

步骤3：将分组数据加载回Excel #

1. 点击PowerQuery编辑器顶部「开始」→「关闭并上载至」
2. 选择「仅创建连接」，点击「确定」
3. 在Excel右侧「查询和连接」面板中，右键点击刚创建的连接，选择「加载到」
4. 选择「表」→「新工作表」，重复此操作将每个分组数据加载到独立工作表，然后将工作表重命名为对应的「名称-日期」即可（或在M代码中添加自动加载逻辑，简化手动操作）

补充：SQL方案（需依赖数据库） #

如果习惯用SQL，可将Excel数据导入Access/SQL Server数据库后，用动态SQL批量生成目标表：

-- 提取日期值
DECLARE @DateValue DATE = (SELECT TOP 1 Column1 FROM RawData);
DECLARE @DateText VARCHAR(20) = FORMAT(@DateValue, 'dd/MM/yyyy');

-- 标记并填充分组
WITH MarkedGroups AS (
    SELECT 
        Column1, Column2, Column3,
        CASE WHEN Column2 IS NULL AND Column3 IS NULL THEN Column1 ELSE NULL END AS GroupName,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM RawData
    WHERE RowNum > 1 -- 跳过日期行
),
FilledGroups AS (
    SELECT 
        Column1, Column2, Column3,
        MAX(GroupName) OVER (ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupName
    FROM MarkedGroups
),
FilteredData AS (
    SELECT Column1, Column2, Column3, GroupName
    FROM FilledGroups
    WHERE Column2 IS NOT NULL
)
-- 动态创建每个分组的表
SELECT DISTINCT GroupName INTO #TempGroups FROM FilteredData;

DECLARE @GroupName VARCHAR(100);
DECLARE GroupCursor CURSOR FOR SELECT GroupName FROM #TempGroups;

OPEN GroupCursor;
FETCH NEXT FROM GroupCursor INTO @GroupName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @TableName VARCHAR(150) = @GroupName + '-' + @DateText;
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            Column1 AS [' + @GroupName + '],
            Column2 AS [Header2],
            Column3 AS [Header3]
        INTO [' + @TableName + ']
        FROM FilteredData
        WHERE GroupName = ''' + @GroupName + ''';
    ';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM GroupCursor INTO @GroupName;
END

CLOSE GroupCursor;
DEALLOCATE GroupCursor;
DROP TABLE #TempGroups;

注意：SQL方案需要额外数据库支持，操作成本高于PowerQuery，优先推荐PowerQuery方案。

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