结合2 Excel表格到一个附加的数据？ [英] Combine 2 Excel tables into one appending the data?

问题描述

我有2个独立的MS Excel 2007工作簿的2张表，如下图所示：

  ===========================
没有。 f_name l_name
===========================
13小提米
约翰母鹿
17杰西卡宝宝
---------------------------
===========================
没有。 f_name l_name
===========================
1约翰·蒂姆
16乔·凯尔
14宝贝凯蒂
22 qbcd WSDE
---------------------------
 

两者具有相同的列，但它们可以有不同的内容

我想这两个表垂直即单个表的数据与第3独立表中的所有数据合并。
如果可能的话，我想从行来的地方工作表名称添加另一列。

  ===================================
SHEETNAME没有。 f_name l_name
===================================
工作表Sheet1 13小提米
工作表Sheet1约翰李四
17工作表Sheet1杰西卡宝宝
1 Sheet2的蒂姆·约翰
16 Sheet2的乔·凯尔
14 Sheet2的宝贝凯蒂
Sheet2的22 qbcd WSDE
-----------------------------------
 

可不可以不使用宏来完成？

解决方案

您可以激活的 Office剪贴板的（在功能区上的Home标签剪贴板部分右下角的箭头）。复制两个范围，然后使用粘贴所有命令，如下图所示。

您仍需要一个额外的列先向下填充工作表名称虽然可以通过双击填充柄来完成。

更新

要得到的公式相同的结果尽量充绒本作的工作表名称：

<$p$p><$c$c>=IF(ROW()<=COUNTA(Sheet1!A:A),\"Sheet1\",IF(ROW()<COUNTA(Sheet1:Sheet2!A:A),\"Sheet2\",\"\"))

，然后向下填充并在这个公式表中的值：

<$p$p><$c$c>=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),\"\"))

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?

解决方案

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.

Update

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屋！