☰

在 Excel 中汇总多个表格

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
在 Excel 中汇总多个表格

我希望这是正确的网站对于这个问题

我有一年的交货收据（60 多张表格，每张都在单独的工作表上），所有收据都有相同的标题，我想将它们全部添加到年度汇总表中。

我只需要Art-No.和Description总计Delivered Quantity，但逐一查看每张表并将它们合计起来会花很长时间。不是每个订单上都有相同的产品，也不是在同一订单中。有些产品可能一年只出现一次，而其他产品则出现在每个订单中。这有点混乱。

本质Art-No.上是一个产品ID，可以用来识别产品（以及Description）。

示例表 1 表格标题和示例文本

示例表 2 在此处输入图片描述

示例表 3 在此处输入图片描述

期望结果表格示例在此处输入图片描述

我尝试使用 Power Query 进行合并，但它并不能整合数据，而只是将其附加起来。

我试过了数据透视表但他们不想听到“数据源引用无效”的声音

有没有更简单的方法可以做到这一点？

答案1

我认为您使用 Power Query 的方法是正确的。通常，步骤如下：

  1. 将每个表加载到 Power Query 中（这很麻烦，但我不知道有任何批量加载选项）。您可以将默认选项设置为仅加载连接，这样就不会出现每个表的 Power Query 版本的重复工作表。

  2. 在 Power Query 编辑器中打开第一个表。选择附加查询（不是合并）来自功能区的“合并”部分。

  3. 在“追加”对话框中，选择第二个表，然后单击“确定”。对已加载到 Power Query 的每个表重复此操作。现在，您要将 60 个表中的行添加到一个“超级表”中。

  4. 当所有已加载的表格都已添加后，将附加查询加载到 Excel。

  5. 使用附加查询表作为数据源来创建数据透视表。选择 Art-No. 和 Description 作为行标签（格式为表格布局，无小计），选择 Quantity 作为值。

样本

答案2

如果所有数据都在一张表中，那么将数据透视表应用于其中以生成摘要将非常容易。手动将 60 多张表复制并粘贴到一张表中需要几分钟，但可以做到。

或者，您可以使用宏进行复制和粘贴。假设每张工作表都从 A1 开始，并且所有工作表都包含数据表，则此宏应该有效：

Sub Macro1()

NumSheets = Application.Sheets.Count
Sheets.Add After:=Sheets(Sheets.Count)

i = 1
Do While i <= NumSheets
    Sheets(i).Select
    If (i = 1) Then
      Range("A1").Select
    Else
      Range("A2").Select
    End If
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(NumSheets + 1).Select
    If (i = 1) Then
       Range("A1").Select
    Else
       Range("A2").Select
       Selection.End(xlDown).Select
       ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    ActiveSheet.Paste
    i = i + 1
    Loop


End Sub

答案3

这是一种不使用 Power Query 的方法。

对于任何给定的表格，您可以使用以下方法汇总特定产品（Art-No.）的交付数量求和函数例如，以下公式将在名为 Table1 的表格中给出 Art-No. 59792 的总交付数量。

`=SUMIF(Table1[Art-No.],"=59792",Table1[Delivered Quantity])`

您可以使用间接函数从另一个单元格中提取表格名称。例如，如果单元格 A2 包含“Table1”，则以下公式将产生与上述公式相同的结果

`=SUMIF(INDIRECT(A2"[Art-No.]"),"=59792",INDIRECT(A2&"[Delivered Quantity]"))`

因此，要创建一个新表，其中包含每个表中每种文章类型的总数，您可以执行以下操作：

  1. 将所有文章编号放在第一行，从 B1 开始。
  2. 将所有表格名称放在第一列，从 A2 开始。
  3. 将以下内容复制到B2中

=SUMIF(INDIRECT($A2&“[Art-No.]”),B$1,INDIRECT($A2&“[交付数量]”))

  1. 复制 B2 并粘贴到表格的其余部分。

答案4

这是一个不使用 Power Query 或数据透视表的选项：

正如您在评论中所述，您可以创建您想要的结果表，其中包含除Ordered qty.和Delivered qty.字段之外的所有内容。这意味着我们要做的就是使用Art-no.作为唯一 ID 来汇总每张工作表的值。

步骤1：创建一个包含所有工作表名称的列表。由于文件中有很多工作表，因此可以使用一些 VBA 来完成此操作。

Sub SheetNames()
    Dim ws As Worksheet
    Dim r As Integer
    Set ws = Worksheets.Add
    For r = 1 To Worksheets.Count
        ws.Cells(r, 1).Value = Worksheets(r).Name
    Next
End Sub

第2步：创建一个引用工作表名称列表的命名范围。我将列表转换为表格，并以此为基础确定名称范围，但您不必这样做。

命名范围

步骤3：Ordered qty.创建不填写和值的基础结果表Delivered qty.。如果你没有可用的表，最简单的方法可能是使用类似阿德莱德爵士发布以合并所有工作表、删除不需要的字段、清除所有数量值并删除重复项。您可能想知道如果只是按照该方法操作，为什么还需要这些，但下一步中的技巧太酷了，不能不分享。

桌子

步骤4：编写公式来一次性汇总所有工作表中的数据。首先，以下是可用于的公式Ordered qty.：

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"B2:B10000"),[@[Art-no.]],INDIRECT("'"&sheets&"'!"&"D2:D10000")))

这是一个特殊的SUMIF包裹。使用 3D 参考SUMPRODUCT进行工作的关键是函数和我们制作的图纸名称列表。以下是公式的各个部分：SUMIFINDIRECT

  • INDIRECT("'"&sheets&"'!"&"B2:B10000")这是一个标准INDIRECT函数，只不过我们引用了我们在步骤 2 中创建的指向工作表名称列表的命名范围。这就是使其成为 3D 引用的原因。我选择了它，B2:B10000但您可以根据需要调整/扩展它。只要两个函数中的范围大小相同，就可以很容易地使引用比所需的大得多，以确保不会遗漏任何内容INDIRECT。它需要指向Art-no.其他工作表中的列。
  • [@[Art-no.]]由于我将结果表变成 Excel 中的实际表格，因此这只是指向带有Art-no.公式的当前行中的值。
  • INDIRECT("'"&sheets&"'!"&"D2:D10000")这与另一个相同，INDIRECT但它指向Ordered qty.列。
  • SUMIF(~)现在变成了SUMIF(Art-no. from every sheet, Art-no. in this row, Ordered qty. from every sheet)
  • SUMPRODUCT(~)将SUMIF返回一个数组而不是单个值，因此我们使用它来将数组中的所有值相加。

的公式Delivered qty.是相同的，只是您应该更改D2:D10000为G2:G10000或任何需要指向Delivered qty.其他工作表中的列的内容。

值得赞扬的是，我发现了这个方法CreditJet.net我觉得这太棒了。

相关内容