如何组合有两个共同的列（但不是相同的顺序） [英] How to combine two excels that have a common column (but aren't in the same order)

问题描述

我有两个excel文件，一个是从excel中提取出来的，另一个是从SAP提取的文件。一个文件较大的一个文件有一个名为EmployeeID的列，另一个文件名为EMP_ID，唯一的区别是名称，这些列中的实际员工ID是相同的。我想要做的是将这两个文件组合成一个文件;然而，例如，第一个excel文件上的员工IDZZZ可能在第一行，但在另一个文件中，ZZZ的EMP_ID可能在第35行。

有没有办法组合这两个文件？另外，通过EMP_ID A排序到Z将不会有帮助，因为文件没有完全相同的条目数，所以不会同步它们，以便每个员工在每个文件的同一行上。 >

这两个文件是重要的。档案1 - http://i.imgur.com/go1S6Ra.png 档案2 - http://i.imgur.com/x4vOKIN.png

解决方案

这是一个纯粹的excel解决方案。打开文件1，然后转到空白页。复制并粘贴文件2的内容。

转到第二页上空白栏中的第一个单元格，并使用vlookup。公式会像这样，但你必须修改它。

  = VLOOKUP（A1，Sheet1。$ A $ 1：$ B $ 419,2，FALSE）
  

有四个参数，你需要玩前三个获得你想要的。

A1 将其更改为员工ID居住在单元格中的单元格二。所以如果它在第三列，这应该是 C1

Sheet1。$ A $ 1：$ B $ 250 这是文件1的数据所在。第一个选项卡的默认名称为 Sheet1 ，但根据打开文件的方式可能会有所不同。第二部分是数据范围。左上方细胞和右下方细胞。所以如果你有5列在一张和1000行，那将是 $ A $ 1：$ E $ 1000 （我会在最后解释美元符号）

1 第三个参数目标数据在员工ID右侧有多少列。所以如果你的列是ID，名字，姓氏，把3放在这里可以给你最后的名字。

FALSE ，它控制excel的匹配行为。 TRUE寻找紧密的比赛，但真的只是让事情变得不可预测。

一旦搜索正常工作，您正在查找所需的内容，请填写公式列的其余部分。如果您尝试查找的ID不在Sheet1中，那么您将收到错误，因此请确保将公式调整为存在的公式。如果您需要查找多个东西（例如名字和姓氏），只需将公式拖到右边，并为第三个参数输入不同的数字。

那么美元符号怎么处理？这会影响将公式向下或向下拖动到新单元格中时会发生什么。如果你这样写：

= VLOOKUP（A1，Sheet1.A1：B100,2，FALSE） p>

并将其拖放到一个单元格下，您将获得这一点：

= VLOOKUP（A2 ，Sheet1.A2：B101,2，FALSE）

所有行坐标向上移一。如果你拖动它，那么所有的列坐标都会上升一个，如下所示：

= VLOOKUP（B1，Sheet1.B1 ：C100,2，FALSE）

美元符号将其冻结到位，所以范围不会漂移。您希望搜索值继续移动，因为每行都应该查找下一个员工ID，但是您不希望移动范围。

I have two excel files, one that I pulled from excel and the other that I pulled from SAP. One file, the larger one, has a column named EmployeeID and the other has a column named EMP_ID, the only difference is the name, the actual employee id within these columns is the same. What I am wanting to do it combine these two files to make a single file; however, for example, Employee ID 'ZZZ' on the first excel file may be on line 1, but on the other file the EMP_ID of 'ZZZ' may be on line 35.

Is there a way to somehow combine these 2 files? Also, sorting by EMP_ID A to Z won't help, because the files don't have the exact same number of entries, so that wouldn't sync them so that each employee is on the same line on each file.

These two files are for reference if it matters. File 1 - http://i.imgur.com/go1S6Ra.png File 2 - http://i.imgur.com/x4vOKIN.png

解决方案

Here's a pure excel solution. Open file 1 one and go to an empty sheet. Copy and paste the content of file 2.

Go to the first cell in a blank column on sheet two and use vlookup. The formula will look something like this, but you'll have to modify it.

=VLOOKUP(A1,Sheet1.$A$1:$B$419,2,FALSE)

There are four arguments and you will need to play with the first three to get what you want.

A1 Change this to the cell where the employee ID lives in sheet two. So if it's in the third column, this should be C1

Sheet1.$A$1:$B$250 This is where the data from file 1 is. The default name for the first tab is Sheet1 but it might be something different depending on how you opened the file. The second part is the data range. Top left cell and bottom right cell. So if you have 5 columns in sheet one and 1000 rows it will be $A$1:$E$1000 (I'll explain the dollar signs at the end)

1 The third argument how many columns the target data is to the right of the employee ID. So if your columns are ID, First Name, Last Name, putting 3 here would get you the last name.

FALSE, this controls excel's matching behaviour. TRUE looks for close matches, but really it just makes thing unpredictable.

Once the search is working and you're looking up what you want, fill the formula in for the rest of the column. You will get an error if the ID you're trying to lookup isn't in Sheet1, so make sure you tweak the formula on one that exists. If you need to look up multiple things (e.g. First Name and Last Name), just drag the formula to the right and put in a different number for the third argument.

So what's the deal with the dollar signs? This affects what happens when you drag the formula down or over into new cells. If you write this:

=VLOOKUP(A1,Sheet1.A1:B100,2,FALSE)

and drag it down one cell you'll get this:

=VLOOKUP(A2,Sheet1.A2:B101,2,FALSE)

All the row coordinates go up by one. If you dragged it over instead, all of the column coordinates would go up by one, like this:

=VLOOKUP(B1,Sheet1.B1:C100,2,FALSE)

Dollar signs freeze them in place, so the range doesn't drift. You want the search value to keep moving because every line should be looking up the next employee ID, but you don't want the range to move.

这篇关于如何组合有两个共同的列（但不是相同的顺序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！