excel - 将 2 个 Excel 表合并为一个 append 数据？

Question

我在 MS Excel 2007 工作簿的 2 个单独工作表上有 2 个表，如下所示:

===========================
no.   f_name     l_name  
===========================
13   Little     Timmy
1   John       Doe
17   Baby       Jessica
---------------------------


===========================
no.   f_name     l_name  
===========================
1   john       Tim
16   kyle       joe
14   Baby       katy
22   qbcd       wsde
---------------------------

两者具有相同的列，但它们可以具有不同的数据。

我想垂直组合两个表的数据，即单个表与第三个单独工作表中的所有数据。
如果可能，我想添加另一列，其中包含该行所在位置的工作表名称。

===================================
SheetName   no.   f_name     l_name  
===================================
Sheet1      13   Little     Timmy
Sheet1      1   John       Doe
Sheet1      17   Baby       Jessica
Sheet2      1   john       Tim
Sheet2      16   kyle       joe
Sheet2      14   Baby       katy
Sheet2      22   qbcd       wsde
-----------------------------------

可以不使用宏来完成吗？

Answer 1

This answer deals with Structured Tables as interpreted by Excel. While the methods could easily be transcribed to raw data matrixes without assigned table structure, the formulas and VBA coding for this solution will be targeted at true structured tables.

前言

第三个表可以使用一些 native 工作表公式维护两个表的组合数据，但是在从依赖表中添加或删除行时保持第三个表的大小正确将需要手动调整大小操作或一些跟踪这些更改并符合的 VBA第三张 table 适合。我在此答案的末尾包含了添加源表的工作表名称以及一些表维护 VBA 代码的选项。

If all you want is an operational example workbook without all the explanation, skip to the end of this answer for a link to the workbook used to create this procedure.

示例数据表



我已经使用 OP 的示例数据在工作表 Sheet1 和 Sheet2 上分别构造了两个名为(默认情况下)Table1 和 Table2 的表。我特意将它们从每个工作表的 A1 单元格中进行了不同程度的抵消，以证明结构化表能够将公式中的自身或另一个结构化表作为单独的实体进行寻址，而不管其在父工作表上的位置如何。第三个表将以类似的方式构建。这些偏移量仅用于演示目的；它们不是必需的。

第 1 步:构建第三个表

构建第三个表格的标题并选择 future 的标题行和它下面的至少一行，以作为插入 ► 表格 ► 表格命令的基础。



Sheet3 工作表上的新空第三个表应类似于以下内容。



第 2 步:填充第三个表

首先填充第三个表 DataBodyRange 中的第一个单元格.在本例中，这将是 Sheet3!C6。在 C6 中键入或粘贴以下公式，记住它基于默认表名。如果您更改了表名称，请相应地进行调整。

=IFERROR(INDEX(Table1, ROW([@[no.]])-ROW(Table3[#Headers]),COLUMN(A:A)), INDEX(Table2, ROW([@[no.]])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)))

INDEX function首先从 中检索每个可用的行表1 .实际行号由 ROW function 派生而来引用结构化表的定义部分以及一些数学运算。当 Table1 用完行时，检索将传递给引用 的第二个 INDEX 函数。表2 由 IFERROR function并使用 ROW 和 ROWS functions 检索其连续行使用更多的数学。 COLUMN function用作 COLUMN(A:A)这将检索引用表的第一列，而不管它在工作表上的什么位置。随着公式正确填写，这将进入第二、第三等列。

说到填权，把公式填到E6。你应该有一些近似于以下的东西。



步骤 2.5:[可选] 添加源表的父工作表名称

捕获右下角的 Table3 的大小调整 handle (由下面示例图像中的橙色箭头指示)并将其向右拖动一列以向表格中添加一个新列。将标题标签重命名为比默认值更合适的名称。我用过 表作为列标签。



虽然您无法直接检索源表的工作表名称，但 CELL function可以检索已保存工作簿¹中任何单元格的完全限定路径、文件名和工作表作为其可选的 info_types 之一。

将以下公式放入您刚刚创建的新列的第一行中 Table3 的空单元格中。

=TRIM(RIGHT(SUBSTITUTE(CELL("filename", IF((ROW([@[no.]])-ROW(Table3[#Headers]))>ROWS(Table1), Table2, Table1)), CHAR(93), REPT(CHAR(32), 999)), 255))

计算填充表3

如果您不打算用一些 VBA 完成这个小项目，以便在从两个源表中的任何一个添加或删除行时维护 Table3 的维度，那么只需捕获 Table3 的调整大小 handle 并向下拖动，直到您收集了两个表中的所有数据.有关预期结果的示例图像，请参阅此答案的底部。

如果您打算添加一些 VBA，则跳过 Table3 的完整填充并继续下一步。

第三步:添加一些VBA来维护第三张表

由工作表数据更改触发的流程的完全自动化最好由工作表的 Worksheet_Change 处理。事件宏。由于涉及三个表，每个表都在自己的工作表上， Workbook_SheetChange event macro是处理来自多个工作表的更改事件的更好方法。

使用 Alt+F11 打开 VBE。打开后，查找 项目浏览器 在左上角。如果它不可见，请点击 Ctrl+R 将其打开。定位 本工作簿并右键单击然后选择“查看代码”(或只需双击“ThisWorkbook”)。



将以下内容粘贴到名为 Book1 - ThisWorkbook (Code) 的新 Pane 中。

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case Sheet1.Name
            If Not Intersect(Target, Sheet1.ListObjects("Table1").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
        Case Sheet2.Name
            If Not Intersect(Target, Sheet2.ListObjects("Table2").Range.Offset(1, 0)) Is Nothing Then
                On Error GoTo bm_Safe_Exit
                Application.EnableEvents = False
                Call update_Table3
            End If
    End Select

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Private Sub update_Table3()
    Dim iTBL3rws As Long, rng As Range, rngOLDBDY As Range
    iTBL3rws = Sheet1.ListObjects("Table1").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet2.ListObjects("Table2").DataBodyRange.Rows.Count
    iTBL3rws = iTBL3rws + Sheet3.ListObjects("Table3").DataBodyRange.Cells(1, 1).Row - _
                          Sheet3.ListObjects("Table3").Range.Cells(1, 1).Row
    With Sheet3.ListObjects("Table3")
        Set rngOLDBDY = .DataBodyRange
        .Resize .Range.Cells(1, 1).Resize(iTBL3rws, .DataBodyRange.Columns.Count)
        If rngOLDBDY.Rows.Count > .DataBodyRange.Rows.Count Then
            For Each rng In rngOLDBDY
                If Intersect(rng, .DataBodyRange) Is Nothing Then
                    rng.Clear
                End If
            Next rng
        End If
    End With
End Sub

这两个例程广泛使用了工作表 .CodeName property .工作表的 代号 是 Sheet1、Sheet2、Sheet3 等，并且在重命名工作表时不会更改。事实上，即使是更高级的用户也很少更改它们。它们已被使用，以便您可以重命名工作表而无需修改代码。但是，他们现在应该指向正确的工作表。如果您的表格和工作表与给定的不同，请修改代码。您可以在工作表旁边的括号中看到各个工作表的代号 .Name property在上图中显示了 VBE 的项目资源管理器。

点击 Alt+Q 返回到您的工作表。剩下的就是完成填充 表3 通过选择 中的任何单元格表1 或 表2 并假装通过点击 F2 和 Enter↵ 来修改它。您的结果应类似于以下内容。



如果您一直遵循到这里，那么您应该有一个非常全面的收集表，它主动结合了来自两个源“子”表的数据。如果您还添加了 VBA，则几乎不需要维护第三个集合表。

重命名表

如果您选择重命名三个表中的任何一个或全部，工作表公式将立即自动反射(reflect)更改。如果您选择包含 Workbook_SheetChange 和随附的帮助程序子过程，则必须返回 ThisWorkbook 代码表并使用 Find & Replace 进行适当的更改。

示例工作簿

我已经从我的公共(public) DropBox 中提供了完全可操作的示例工作簿。

Table_Collection_w_Sheetname.xlsb

¹ CELL function只能检索已保存工作簿的工作表名称。如果工作簿尚未保存，则它没有文件名，当要求输入文件名时，CELL 函数将返回一个空字符串。