将 2 个 Excel 表合并为一个附加数据? [英] Combine 2 Excel tables into one appending the data?
问题描述
我在 MS Excel 2007 工作簿的 2 个单独工作表上有 2 个表格，如下所示:
============================不.f_name l_name============================13 小蒂米1 约翰·多伊17 宝贝杰西卡---------------------------============================不.f_name l_name============================1 约翰蒂姆16 凯尔乔14 宝贝凯蒂22 qbcd wsde---------------------------两者具有相同的列，但它们可以具有不同的数据.
我想垂直合并两个表的数据，即单个表与第三个单独的工作表中的所有数据.如果可能，我想添加另一列，其中包含该行所在位置的工作表名称.
====================================表名编号f_name l_name====================================Sheet1 13 小蒂米表 1 1 约翰·多伊Sheet1 17 宝贝杰西卡Sheet2 1 约翰蒂姆Sheet2 16 凯尔乔Sheet2 14 宝贝凯蒂Sheet2 22 qbcd wsde---------------------不使用宏可以做到吗?
这个答案涉及
我使用 OP 的示例数据在工作表 Sheet1 和 Sheet2 上分别构造了两个名为(默认情况下)Table1 和 Table2 的表.我有意将它们与每个工作表的 A1 单元格进行不同程度的抵消，以证明结构化表能够将公式中的自身或另一个结构化表作为单独的实体进行寻址，而不管其在父工作表上的位置如何.第三个表将以类似的方式构建.这些偏移量仅用于演示目的；它们不是必需的.
第一步:构建第三个表
为第三个表格构建标题并选择未来的标题行和它下方的至少一行以作为插入 ► 表格 ► 表格命令的基础.
您在 Sheet3 工作表上新建的第三个空表应如下所示.
第 2 步:填充第三个表
首先填充第三个表的
步骤 2.5:[可选] 添加源表的父工作表名称
在右下角抓住 Table3 的大小调整手柄(由下面示例图像中的橙色箭头指示)并将其向右拖动一列以向表格中添加一个新列.将标题标签重命名为比默认值更合适的名称.我使用 Sheet 作为列标签.
虽然您无法直接检索源表的工作表名称，
将以下内容粘贴到名为 Book1 - ThisWorkbook (Code) 的新窗格中.
选项显式Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)选择案例名称案例表1.名称如果不相交(Target, Sheet1.ListObjects("Table1").Range.Offset(1, 0)) Is Nothing Then出错时转到 bm_Safe_ExitApplication.EnableEvents = False调用 update_Table3万一Case Sheet2.Name如果不相交(Target, Sheet2.ListObjects("Table2").Range.Offset(1, 0)) Is Nothing Then出错时转到 bm_Safe_ExitApplication.EnableEvents = False调用 update_Table3万一结束选择bm_Safe_Exit:Application.EnableEvents = True结束子私有子 update_Table3()Dim iTBL3rws As Long、rng As Range、rngOLDBDY As RangeiTBL3rws = Sheet1.ListObjects("Table1").DataBodyRange.Rows.CountiTBL3rws = iTBL3rws + Sheet2.ListObjects("Table2").DataBodyRange.Rows.CountiTBL3rws = iTBL3rws + Sheet3.ListObjects("Table3").DataBodyRange.Cells(1, 1).Row - _Sheet3.ListObjects("Table3").Range.Cells(1, 1).Row使用 Sheet3.ListObjects("Table3")设置 rngOLDBDY = .DataBodyRange.Resize .Range.Cells(1, 1).Resize(iTBL3rws, .DataBodyRange.Columns.Count)如果 rngOLDBDY.Rows.Count >.DataBodyRange.Rows.Count 然后对于 rngOLDBDY 中的每个 rng如果 Intersect(rng, .DataBodyRange) 什么都没有，那么rng.清除万一下一个万一结束于结束子这两个例程广泛使用了工作表
如果您一直关注到这里，那么您应该有一个非常全面的收集表，它主动结合了来自两个源子"表的数据.如果您还添加了 VBA，则几乎不需要维护第三个集合表.
重命名表
如果您选择重命名三个表中的任何一个或全部，工作表公式将立即自动反映更改.如果您选择包含 Workbook_SheetChange 和随附的帮助程序子过程，则必须返回到 ThisWorkbook 代码表并使用 Find &替换以进行适当的更改.
示例工作簿
我已经从我的公共 DropBox 中提供了完全可操作的示例工作簿.
Table_Collection_w_Sheetname.xlsb
<小时>¹ CELL 函数只能检索已保存工作簿的工作表名称.如果工作簿尚未保存，则它没有文件名，当要求输入文件名时，CELL 函数将返回一个空字符串.
I have 2 tables on 2 separate sheets of an MS Excel 2007 workbook, like below:
=========================== 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 ---------------------------Both have the same columns, but they can have different data.
I want to combine the data of both tables vertically i.e. a single table with all the data in a 3rd separate sheet. If possible, I want to add another column with the sheet name from where the row came.
=================================== 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 -----------------------------------Can it be done without using macros?
解决方案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.
Preamble
A third table can maintain the combined data of two tables with some native worksheet formulas but keeping the third table sized correctly as rows are added or deleted to/from the dependent tables will require either manual resizing operations or some VBA that tracks these changes and conforms the third table to suit. I've included options to add both the source table's worksheet name as well as some table maintenance VBA code at the end of this answer.
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.
Sample data tables
I've used the OP's sample data to construct two tables named (by default) Table1 and Table2 on worksheets Sheet1 and Sheet2 respectively. I've intentionally offset them by varying degrees from each worksheet's A1 cell in order to demonstrate a structured table's ability to address either itself or another structured table in a formula as a separate entity regardless of its position on the parent worksheet. The third table will be constructed in a similar manner. These offsets are for demonstration purposes only; they are not required.
Step 1: Build the third table
Build the headers for the third table and select that future header row and at least one row below it to base the Insert ► Tables ► Table command upon.
Your new empty third table on the Sheet3 worksheet should resemble the following.
Step 2: Populate the third table
Start by populating the first cell in the third table's DataBodyRange. In this example, that would be Sheet3!C6. Type or paste the following formula in C6 keeping in mind that it is based on the default table names. If you have changed your tables names, adjust accordingly.
=IFERROR(INDEX(Table1, ROW([@[no.]])-ROW(Table3[#Headers]),COLUMN(A:A)), INDEX(Table2, ROW([@[no.]])-ROW(Table3[#Headers])-ROWS(Table1),COLUMN(A:A)))The INDEX function first retrieves each available row from Table1. The actual row numbers are derived with the ROW function referencing defined pieces of the structured table together with a little maths. When Table1 runs out of rows, retrieval is passed to a second INDEX function referencing Table2 by the IFERROR function and its sequential rows are retrieved with the ROW and ROWS functions using a bit more maths. The COLUMN function is used as
COLUMN(A:A)which is going to retrieve the first column of the referenced table regardless of where it is on the worksheet. This will progress to the second, third, etc. column as the formula is filled right.Speaking of filling right, fill the formula right to E6. You should have something that approximates the following.
Step 2.5: [optional] Add the source table's parent worksheet name
Grab Table3's sizing handle (indicated by the orange arrow in the sample image below) in the lower right hand corner and drag it right one column to add a new column to the table. Rename the header label to something more appropriate than the default. I've used Sheet as a column label.
While you cannot retrieve the worksheet name of the source table directly, the CELL function can retrieve the fully qualified path, filename and worksheet of any cell in a saved workbook¹ as one of its optional info_types.
Put the following formula into Table3's empty cell in the first row of the new column you have just created.
=TRIM(RIGHT(SUBSTITUTE(CELL("filename", IF((ROW([@[no.]])-ROW(Table3[#Headers]))>ROWS(Table1), Table2, Table1)), CHAR(93), REPT(CHAR(32), 999)), 255))Complate populating Table3
If you are not planning on finishing this small project with some VBA to maintain Table3's dimensions when rows are added or deleted from either of the two source tables then simply grab Table3's resizing handle and drag down until you have accumulated all of the data from both tables. See the bottom of this answer for a sample image of the expected results.
If you are planning to add some VBA, then skip the full population of Table3 and move on to the next step.
Step 3: Add some VBA to maintain the third table
Full automation of a process that is triggered by changes to a worksheet's data is best handled by the worksheet's Worksheet_Change event macro. Since there are three tables involved, each on their own worksheet, the Workbook_SheetChange event macro is a better method of handling the change events from multiple worksheets.
Open the VBE with Alt+F11. Once you have it open, look for the Project Explorer in the upper left. If it is not visible, then tap Ctrl+R to open it. Locate ThisWorkbook and right-click then choose View Code (or just double-click ThisWorkbook).
Paste the following into the new pane titled something like Book1 - ThisWorkbook (Code).
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 SubThese two routines make extensive use of the Worksheet .CodeName property. A worksheet's CodeName is Sheet1, Sheet2, Sheet3, etc and does not change when a worksheet is renamed. In fact, they are rarely changed by even the more advanced user. They have been used so that you can rename your worksheets without having to modify the code. However, they should be pointing to the correct worksheets now. Modify the code if your tables and worksheets are not the same as given. You can see the individual worksheet codenames in brackets beside their worksheet .Name property in the above image showing the VBE's Project Explorer.
Tap Alt+Q to return to your worksheets. All that is left would be to finish populating Table3 by selecting any cell in Table1 or Table2 and pretending to modify it by tapping F2 then Enter↵. Your results should resemble the following.
If you have followed along all the way to here then you should have a pretty comprehensive collection table that actively combines the data from two source 'child' tables. If you added the VBA as well then maintenance of the third collection table is virtually non-existent.
Renaming the tables
If you choose to rename any or all of the three tables, the worksheet formulas will instantly and automatically reflect the changes. If you have opted to include the Workbook_SheetChange and accompanying helper sub procedure, you will have to go back into the ThisWorkbook code sheet and use Find & Replace to make the appropriate changes.
Sample Workbook
I've made the fully operational example workbook available from my public DropBox.
Table_Collection_w_Sheetname.xlsb
¹ The CELL function can only retrieve the worksheet name of a saved workbook. If a workbook has not been saved then it has no filename and the CELL function will return an empty string when asked for the filename.
这篇关于将 2 个 Excel 表合并为一个附加数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
