为了账号安全,请及时绑定邮箱和手机立即绑定

将2个Excel表合并为一个附加数据?

/ 猿问

将2个Excel表合并为一个附加数据?

慕田峪4524236 2019-10-21 14:51:18

我在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

-----------------------------------

不用宏就可以做到吗?


查看完整描述

3 回答

?
狐的传说

该答案涉及Excel解释的结构化表。尽管可以轻松地将这些方法转录为原始数据矩阵,而无需分配表结构,但该解决方案的公式和VBA编码将针对真正的结构化表。


前言

第三个表可以使用某些本机工作表公式维护两个表的合并数据,但是在向相关表中添加行或从中删除行时,保持第三个表的大小正确将需要手动调整大小操作或一些VBA来跟踪这些更改并遵循适合的第三张桌子。我在此答案的末尾包含了用于添加源表的工作表名称以及一些表维护VBA代码的选项。


如果您只需要一个不带所有解释的操作示例工作簿,请跳至此答案的末尾,以找到用于创建此过程的工作簿的链接。


样本数据表

//img.mukewang.com/5dad557d0001271904230219.jpg

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


步骤1:建立第三个表格

构建第三个表的标题,然后选择该将来的标题行及其下方至少一行,以将“插入”►“表格”►“表格”命令作为基础。

//img4.mukewang.com/5dad558600018bcd03220156.jpg

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


//img1.mukewang.com/5dad55910001d70506380220.jpg

步骤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函数首先检索每个可用行表1。实际的行号是通过ROW函数导出的,该函数引用结构化表的定义部分以及一些数学运算。当表1用完的行,检索被传递到第二INDEX函数参照表2由IFERROR函数和其顺序的行与所述行和检索ROWS功能使用更多的数学。该COLUMN函数用作COLUMN(A:A)这是会检索参考表的第一列,无论它是在工作表上。随着公式的正确填写,这将前进到第二,第三等列。


说到填充权,将公式权填充到E6。您应该有一些近似以下内容。

//img3.mukewang.com/5dad559b00018ecb06390241.jpg

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

抓住右下角的Table3大小调整手柄(由下面的示例图像中的橙色箭头指示),并将其向右拖动一列以向表中添加新列。重命名标题标签,使其比默认名称更合适。我已经使用工作表作为列标签。


//img2.mukewang.com/5dad55ad0001616902490219.jpg

虽然您不能直接检索源表的工作表名称,但是CELL函数可以检索已保存工作簿¹中任何单元格的标准路径,文件名和工作表,作为其可选的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的调整大小手柄并向下拖动,直到从两个表中累积了所有数据。请参阅此答案的底部以获取预期结果的示例图像。


如果您打算添加一些VBA,则跳过Table3的全部填充,然后继续下一步。


步骤3:添加一些VBA来维护第三个表

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


用Alt+ 打开VBE F11。打开它后,在左上角找到Project Explorer。如果看不到,请点击Ctrl+ R将其打开。找到ThisWorkbook并单击鼠标右键,然后选择“查看代码”(或仅双击ThisWorkbook)。

//img1.mukewang.com/5dad55b30001c87c02770288.jpg

将以下内容粘贴到标题为Book1-ThisWorkbook(代码)之类的新窗格中。


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

这两个例程广泛使用Worksheet .CodeName属性。工作表的代号为Sheet1,Sheet2,Sheet3等,并且重命名工作表时不会更改。实际上,即使是高级用户也很少更改它们。使用它们是为了使您可以重命名工作表而不必修改代码。但是,他们现在应该指向正确的工作表。如果表和工作表与给定的不同,请修改代码。您可以在上图中显示VBE项目浏览器的工作表.Name属性旁边的方括号中看到各个工作表的代号。


点击Alt+ Q返回到您的工作表。所有剩下将完成填充表3通过选择任一单元格表1和表2,假装通过点击对其进行修改F2,然后Enter↵。您的结果应类似于以下内容。

        //img1.mukewang.com/5dad55be0001f36206700219.jpg

如果一直到这里,那么您应该有一个非常全面的收集表,该表可以有效地合并来自两个源“子”表的数据。如果还添加了VBA,则实际上不存在对第三个收集表的维护。


重命名表格

如果您选择重命名这三个表中的任何一个或全部,则工作表公式将立即自动反映更改。如果您选择包括Workbook_SheetChange和随附的辅助程序子过程,则必须返回ThisWorkbook代码表并使用“查找和替换”进行适当的更改。


样本工作簿


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


Table_Collection_w_Sheetname.xlsb


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


查看完整回答
反对 回复 2019-10-21
?
慕虎7371278

您可以激活Office剪贴板(“功能区主页”选项卡上剪贴板部分右下角的箭头)。复制两个范围,然后使用“ 全部粘贴”命令,如下所示。


您仍然需要首先在额外的列中填写工作表名称,尽管可以通过双击填充手柄来完成。


//img4.mukewang.com/5dad55d00001554607760316.jpg

更新资料


为了获得与公式相同的结果,请尝试填写工作表名称:


=IF(ROW()<=COUNTA(Sheet1!A:A),"Sheet1",IF(ROW()<COUNTA(Sheet1:Sheet2!A:A),"Sheet2",""))

然后填写该公式并填写表格中的值:


=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!A2,IF(ROW()<COUNTA(Sheet1:Sheet2!A:A),INDEX(Sheet2!A:A,ROW()-COUNTA(Sheet1!A:A)+1),""))


查看完整回答
反对 回复 2019-10-21
?
慕斯709654

对Jeeped的代码略作修改。


如果您碰巧使用类似的方法,但是有多个表(例如,超过10个),那么尝试手动添加每个表的每个名称将非常麻烦。如果您更改表的名称,这也是一个问题,因为名称在VBA中是固定连接的。为了避免其他工作,请考虑以下事项:


因此,假设以下内容:


在每个工作表上都有一个或几个表,但是它们具有相似的结构。

工作表上只有表格-没有其他ListObjects集合成员。

每次我们在工作表上编辑表格时,这都会触发主表(表3)中的更新。

然后,上例中的Workbook_SheetChange Sub可能如下所示:


     Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

     Dim tbl As ListObject

     For Each tbl In ActiveSheet.ListObjects

        If Not Intersect(Target, tbl.Range.Offset(1, 0)) Is Nothing Then

            On Error GoTo bm_Safe_Exit

            Application.EnableEvents = False

            Call update_Table

        End If

        Next tbl

        bm_Safe_Exit:

        Application.EnableEvents = True

     End Sub

编辑。第二个例程如下所示:


   Private Sub update_Table()

    Dim iTBL3rws As Long, rng As Range, rngOLDBDY As Range

    Dim tbl As ListObject

    Dim sht As Worksheet

    iTBL3rws = 0


    ' consider all tables, excluding master table

    For Each sht In ThisWorkbook.Worksheets

        For Each tbl In sht.ListObjects

            If tbl.Name <> "Table3" Then

                iTBL3rws = iTBL3rws + tbl.DataBodyRange.Rows.Count

            End If

        Next tbl

    Next sht


    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

此例程与先前的例程有所不同,因为它消除了预编程的情况。当活动工作表上注册了更改时,该工作表中任何将要更改的表都将触发update_Table过程。


查看完整回答
反对 回复 2019-10-21

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信