比较两个数据表 [英] Compare two data sheets

问题描述

我遇到的问题是我在Excel中有两张数据。它们是一个资料列表，列出与库存变化有差异的项目。这些项目是随机放置在两个文档之间的，所以几乎不可能做一个并排的视图，即使我要订购列（我已经有）。例如，如下所示：

表1：

 A1（Apple） ）
 A2（胡萝卜）（-3）
 A3（香蕉）（4）
 A4（巧克力（-7）

而第2页可能是：

 A1（橙色）（-2）
 A2（苹果）（3）
 A3（松饼）（-8）
 A4（胡萝卜）（3）

所以你可以看到，可能会出现相同的数据，如果我想比较这两个集合，要知道方差，即Sheet 1表示-3，而表格2表示+1 ...我最好在批量的情况下做这个，如果可能的话，因为有超过800个单元格

只是为了让您可以看到我在处理什么，这里是两张贴纸的粘贴页面的链接;

表1： http://pastebin.com/6i7QKJ6N
表格2：< a href =http://pastebin.com/zjtC2U7q =nofollow> http://pastebin.com/zjtC2U7q

是否有任何人都可以想到的是能够帮助我，除了我经历这个一个一个我正在考虑的事情之外？

解决方案

我假设股票项目的唯一标识符是标签为 CYSKU 的列，对吗？

如果是这样，那么只有两张之间192个常用项目。我在两张表中运行了一个vlookup，有点类似于所使用的一个pnuts，并使用了一个过滤器。

CYCOST比CYRETL有更多的差异，只要我能看到（我没有比较其他列）。

要执行比较，您可以执行以下操作：

1. 在列C和F之间插入列（刚刚 CYSKU 之后），并在该列的第2行中放置一个vlookup公式，填写下来：

     = VLOOKUP（C2，Sheet2！C：C，1，0）
     




2. 插入一个过滤器并过滤掉＃N / A 列，以获得两张表之间共同的那些。




3. 在列M（CYDVAR之后）插入另一个vlookup并填写：

     = VLOOKUP（C2，Sheet2！C：F，4，0）
     

   这将从Sheet2中给出相应的CYRETL。您可以比较两个CYRETL。

如何使用VLOOKUP：

1. 第一个参数是VLOOKUP将要查找的内容。


2. 第二个参数是查看第一个参数的表范围。 li>
   
3. 第三个参数是第i个第n个列，从中返回一个匹配，限于表（如果表在列A：A中，只有1列可用，如果表是A：B，2列可用，等）。


4. 最后一个参数是精确或近似匹配。确切是 0 （或 FALSE ），大概是 1 （或 TRUE ）。

您可以更改表范围列号可以从Sheet2更改要查找的值。

The issue I'm faced with is I have two sheets of data in Excel. They are a stocksheet list, listing items that have a variance from a stocktake. The items are randomly placed between both documents, so it is almost impossible to do a side-by-side view even if I were to order the columns (which I already have). For example it would be like this:

Sheet 1:

A1 (Apple) (1)
A2 (Carrot) (-3)
A3 (Banana) (4)
A4 (Chocolate (-7)

Whereas Sheet 2 may be:

A1 (Orange) (-2)
A2 (Apple) (3)
A3 (Muffin) (-8)
A4 (Carrot) (3)

So as you can see, the same data may appear, and if it does I want to compare those two sets, to know the variance, i.e. Sheet 1 said -3 whereas sheet 2 said +1... I preferably would like to do this in a batch if possible, as there are over 800 cells to go through.

Just so that you can see what I'm dealing with, here's links to pastebins of both sheets;

Sheet 1: http://pastebin.com/6i7QKJ6N Sheet 2: http://pastebin.com/zjtC2U7q

Is there anything anyone can think of that would be able to assist me, other than me going through this one by one which I am considering doing?

解决方案

I'm assuming that the unique identifier for the stock items is the column labelled CYSKU, right?

If that's so, then there are only 192 common items between the two sheets. I ran a vlookup in both sheets a bit similar to the one pnuts used and used a filter.

There are more variances between CYCOST than with CYRETL as far as I can see (I haven't compared the other columns).

To perform the comparison, you can do the following:

1. Insert a column between columns C and F (just after CYSKU) and put a vlookup formula in row 2 of this column and fill it down:

   =VLOOKUP(C2, Sheet2!C:C, 1, 0)
   

2. Insert a filter and filter out #N/A from this column to get only those that are common between the two sheets.

3. In column M (after CYDVAR), insert another vlookup and fill it down:

   =VLOOKUP(C2, Sheet2!C:F, 4, 0)
   

   This will give you the corresponding CYRETL from Sheet2. You can then compare the two CYRETL.

How VLOOKUP works:

1. The first parameter is what VLOOKUP will be looking for.
2. The second parameter is the table range in which to look the first parameter.
3. The third parameter is the nth column from which a match will be returned, limited to the table (if the table is in column A:A, only 1 column is available, if the table is A:B, 2 columns are available, etc).
4. The last parameter is for either exact or approximate match. Exact is 0 (or FALSE) and approximate is 1 (or TRUE).

You can just change the table range and the column number to change the value you're looking for from Sheet2.

这篇关于比较两个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！