vba 如何有条件地合并/加入excel工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14636802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to conditionally merge/join excel worksheets?
提问by phpwns
+---------+---------+ +---------+--------------+ +---------+-------------+
+ country + widgets + + country + frammis rods + + country + comex gears +
+---------+---------+ +---------+--------------+ +---------+-------------+
+ alpha + 1 + + bravo + 8 + + charlie + 18 +
+ bravo + 3 + + charlie + 16 + + delta + 9 +
+ charlie + 7 + + delta + 32 + +---------+-------------+
+ delta + 11 + +---------+--------------+
+---------+---------+
I have several Excel worksheets like these ^^^ within the same spreadsheet. I'm trying to create a new worksheet that contains merged data showing only those countries with data in allthe worksheet categories.
我在同一个电子表格中有几个像这样 ^^^ 的 Excel 工作表。我正在尝试创建一个包含合并数据的新工作表，其中仅显示那些在所有工作表类别中都有数据的国家/地区。
E.g.,
例如，
+---------+---------+--------------+-------------+
+ country + widgets + frammis rods + comex gears +
+---------+---------+--------------+-------------+
+ charlie + 7 + 16 + 18 +
+ delta + 11 + 32 + 9 +
+---------+---------+--------------+-------------+
I'm looking for a fairly simple, flexible way of doing this as the various worksheets are updated / new worksheets are added. I know how to do this in MYSQL, but I'm not sure if its possible in excel.
随着各种工作表的更新/新工作表的添加，我正在寻找一种相当简单、灵活的方式来做到这一点。我知道如何在 MYSQL 中执行此操作，但我不确定在 excel 中是否可行。
So, what do the experts have to say?
那么，专家们怎么说呢？
Thx in advance. :)
提前谢谢。:)
采纳答案by phpwns
First off, thanks to @MikeDfor an AWESOME writeup on a solution using pivot tables. I did manage to get that working in Excel 2007.
首先，感谢 @ MikeD关于使用数据透视表的解决方案的精彩文章。我确实设法让它在 Excel 2007 中工作。
However, I decided to use a SQL query since its MUCH faster. (Yes, you can use SQL to merge worksheets. Sweet!)
但是，我决定使用 SQL 查询，因为它要快得多。（是的，您可以使用 SQL 来合并工作表。太好了！）
1)Data --> From Other Sources --> From Microsoft Query
1)数据 --> 来自其他来源 --> 来自 Microsoft Query
2)Choose Data Source --> Excel Files* --> Leave "Use the Query Wizard to create/edit queries" Checked
2)选择数据源 --> Excel 文件* --> 选中“使用查询向导创建/编辑查询”
3)Select Workbook
3)选择工作簿
4)Choose the columns you want --> click next --> Click "OK" on the pop-up warning you of the need to setup a manual join
4)选择您想要的列 --> 单击下一步 --> 在弹出警告您需要设置手动连接时单击“确定”
5)In the "Microsoft Query" window --> Table --> Joins
5)在“Microsoft Query”窗口中 --> 表 --> 联接
6)Click on the "Return Data" icon
6)点击“返回数据”图标
7)Import Data --> CTL+ Click the column headers of the duplicates (e.g., "Country2," "Country3") --> Right Click --> "Hide"
7)导入数据--> CTL+ 单击重复项的列标题（例如，“Country2”、“Country3”）--> 右键单击-->“隐藏”
8)!
8）！
回答by MikeD
I recommend using a pivot table with multiple consolidation ranges. (At least in 2010) this works whether the sheets are in the same workbook or in different workbooks (see pic's)
我建议使用具有多个合并范围的数据透视表。（至少在 2010 年）无论工作表是在同一个工作簿中还是在不同的工作簿中，这都有效（参见图片）
Hint: in Excel 2010 press Alt-D, then P to access the 2003 like Pivot table wizard.
提示：在 Excel 2010 中，按 Alt-D，然后按 P 以访问 2003 之类的数据透视表向导。
no page fields
没有页面字段
don't forget to ask for SUM instead of COUNT (any value cell --> right click --> summarize values by)
不要忘记要求 SUM 而不是 COUNT（任何值单元格 --> 右键单击 --> 汇总值）
To arrive at your final layout make an additional header line just above the Pivot table (dark blue A, B; C, ...), create an autofilter and filter for non-blank in all columns
要获得最终布局，请在数据透视表（深蓝色 A、B、C、...）上方创建一个额外的标题行，为所有列中的非空白创建一个自动过滤器和过滤器
ta-taaaaa!
taaaaa！
