将2个Excel表合并成一个附加数据？ [英] Combine 2 Excel tables into one appending the data?

问题描述

我在MS Excel 2007工作簿的两张单独的表中有2张表，如下所示：

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?

推荐答案

激活Office剪贴板（功能区主页选项卡上剪贴板部分右下方的箭头）。复制两个范围，然后使用粘贴全部命令，如下所示。

You can activate the Office Clipboard (arrow at bottom right of clipboard section on Ribbon Home Tab). Copy both ranges then use the Paste All command as shown below.

您仍然需要先填写一个额外的列，然后双击填充句柄可以完成。

You would still need to fill down the sheet name in an extra column first though which can be done by double-clicking the fill handle.

更新

要获得与公式相同的结果，请尝试将其填写为工作表名称：

To get the same results with formulas try filling down this for the sheet name:

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

然后填写表中值的公式：

and then fill down and across this formula for the values in the tables:

=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),""))

这篇关于将2个Excel表合并成一个附加数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！