Excel合并两张桌子
我已经设置了1个
| 班级 | 测试 | 2 2 47 A | 分数 |
|---|---|---|---|
| A | 1 | 1 | 46 |
| A | 1 | 2 | 50 |
| A | 1 | 3 | 45 |
| A | 2 | 1 | 45 |
| A | 2 | 3 | 31 |
| A | 3 | 1 | 34 |
| A | 3 | 2 | 45 |
| B | 1 | 2 | 36 |
| B | 1 | 3 | 2 1 36 |
| B | 2 | 1 31 | 3 31 |
| B | 2 | 2 | 41 |
| B | 3 | 1 | 50 |
| C | 1 | 1 | 42 |
| C | 3 | 1 | 31 |
和2
| 级 | 尺寸 | 年 |
|---|---|---|
| A | 39 | 7 |
| B | 20 | 12 |
| C | 31 | 6 |
，并希望合并以使SET 3
| 级 | 学生 | 考试 | A | 成绩尺寸 | 39 7 |
|---|---|---|---|---|---|
| 1 | 1 | 46 | A A 1 1 46 | 39 | 7 |
| A A | 1 | 2 | 50 | 39 | 7 |
| A | 1 | 3 | 45 | 39 | 7 |
| A | 2 | 1 | 45 | 39 | 7 |
| A | 2 | 2 | 47 | 39 | 7 |
| A | 2 | 3 | 31 | 39 | 7 |
| A | 3 | 1 | 34 | 39 | 7 |
| A | 3 | 2 | 45 | 39 | 7 |
| B | 1 | 1 1 | 36 | 20 | 12 |
| B | 2 | 1 | 31 | 20 | 12 |
| B | 2 | 2 | 41 | 20 | 12 |
| B | 3 | 1 | 50 | 20 | 12 |
| C | 1 | 42 | 31 | 2 | 6 |
| C | 3 | 1 | 31 | 3 | 因此，基本上添加了Set |
的尺寸和年列，并在class上合并到集合1。在Excel中可以这样做吗？我需要在课堂上匹配
I have SET 1
| CLASS | Student | TEST | SCORE |
|---|---|---|---|
| A | 1 | 1 | 46 |
| A | 1 | 2 | 50 |
| A | 1 | 3 | 45 |
| A | 2 | 1 | 45 |
| A | 2 | 2 | 47 |
| A | 2 | 3 | 31 |
| A | 3 | 1 | 34 |
| A | 3 | 2 | 45 |
| B | 1 | 1 | 36 |
| B | 2 | 1 | 31 |
| B | 2 | 2 | 41 |
| B | 3 | 1 | 50 |
| C | 1 | 1 | 42 |
| C | 3 | 1 | 31 |
and SET 2
| CLASS | SIZE | YEARS |
|---|---|---|
| A | 39 | 7 |
| B | 20 | 12 |
| C | 31 | 6 |
and wish to COMBINE to make SET 3
| CLASS | STUDENT | TEST | SCORE | SIZE | YEARS |
|---|---|---|---|---|---|
| A | 1 | 1 | 46 | 39 | 7 |
| A | 1 | 2 | 50 | 39 | 7 |
| A | 1 | 3 | 45 | 39 | 7 |
| A | 2 | 1 | 45 | 39 | 7 |
| A | 2 | 2 | 47 | 39 | 7 |
| A | 2 | 3 | 31 | 39 | 7 |
| A | 3 | 1 | 34 | 39 | 7 |
| A | 3 | 2 | 45 | 39 | 7 |
| B | 1 | 1 | 36 | 20 | 12 |
| B | 2 | 1 | 31 | 20 | 12 |
| B | 2 | 2 | 41 | 20 | 12 |
| B | 3 | 1 | 50 | 20 | 12 |
| C | 1 | 1 | 42 | 31 | 6 |
| C | 3 | 1 | 31 | 31 | 6 |
so basically add the SIZE and YEARS columns from SET 2 and merge on CLASS onto SET 1. In excel how you can do this? I need to match on CLASS
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将两个设置定义为表，并在PowerQuery中定义“左联接”。在那里您可以选择结果表的列。
https://lealen.microsoft.com/en-comrosoft.com/en-en-en-en-en-en-en-en-en-en-n-come美国/电力/Query/Merge-Queries-Left-outer
Define both sets as tables and “left join” in PowerQuery. There you can choose the columns of the resulting table.
https://learn.microsoft.com/en-us/power-query/merge-queries-left-outer
如果您在工作表“ set1”的左上方设置了1个，并且在工作表“ set2”的左上方设置了2个，则可以使用公式
= vlookup（a2;'set2'！$ a $ 2：$ 2：$ c $ 4; 2; false），其中$ a $ 2：$ c $ 4是set2的范围，而a2是从set1，这是用于在set2中进行查找的方法。下一个参数2表示从SET2中占据第二行，最后一个false表示您只需要在课堂上进行精确匹配。您可以使用此公式进行自动填充，并在多年来执行类似的步骤。如果您查找Excel中Vlookup的帮助，那应该有助于您了解其工作原理。If you have Set 1 on the top left of a worksheet "Set1" and Set 2 on the top left of a worksheet "Set2", then you can use the formula
=VLOOKUP(A2;'Set2'!$A$2:$C$4;2;FALSE), where $A$2:$C$4 is the range of Set2, and A2 is the class value from Set1, which is what is used to do the lookup in Set2. The next argument, 2, means to take the second row from Set2, and the FALSE at the end means that you only want exact matches on the CLASS. You can do auto-fill with this formula, and do similar steps for the years. If you look up the help for VLOOKUP within Excel, that should help you to understand how it works.您的第一组数据本质上是您只想添加属性列的主要数据集。我构建
注意您必须在Excel中浮出水面才能起作用。要测试，请查看您是否有公式
= unique（）Your first set of data is essentially your primary set of data that you just want to add attribute columns to. I built this example on Google Sheets which should help explain. Using spill formulas, only a few cells are needed with their own formulas. You can see them as they are highlighted in yellow. When you use in Excel, obviously make sure you change the column references, but this would get you the answer.
Note you have to have SpillRange in Excel for this to work. To test, see if you have the formula
=unique()如果这两个集合在同一列中，则该解决方案可能对您有用。例如，在我的图像中，这两个都从A列A开始。您可以使用单个vlookup公式获取所有数据：
celm e2中的公式是：
请注意，请注意第一和第三个参数的混合引用和绝对的参考第二个中的参考。第三个参数至关重要，因为这是两个集合之间的关系位置，这就是两个集合在同一列开始的原因。如果没有，您需要根据情况调整此参数提取或添加。
无论如何，使用一个公式，您可以获得任意数量的列。此公式的唯一不利于的是，您需要手动向右拖动，直到获得所有列（10、30或其他）。您会注意到自己完成了，因为公式会引起错误：
此错误意味着您正在尝试在列区域之外获得引用。
This solution may work for you if both sets start in the same column. As example in my image, both of them start at column A. You can get all data with a single VLOOKUP formula:
Formula in cell E2 is:
Notice the mixed references at first and third argument and absolute references in the second one. Third argument is critical, because is the relational position between both sets, that's the reason it's easier if both sets start at same column. If not, you'll need to adjust this argument substracting or adding, depending on the case.
Anyways, with a single formula, you can get any number of columns. The only disavantage of this formula is that you need to manually drag to right until you got all the columns (10, 30 or whatever). You'll notice you are done because the formula will raise an error:
This error means you are trying to get a referenced outside of your column area.