比较excel中的两列 [英] Compare two columns in excel

问题描述

嘿，我有一个excel文档与列A和B.列A有2595个值。行B具有411个值。

我试图找出如何比较这两列，通过标记列C与我们从行a中找到一个值的次数。 / p>

例如

  COLUMN A COLUMN B COLUMN C 
 1 1 1（因为我们从A列有1个值）
 2 2 1（因为我们从A列有一个2的值）
 3 3 2（因为我们从A列有2个thri ）
 3 4 
 4 5 
 5 6 2（因为A列中有两个6）
 6 7 0（因为A列中没有7）
 6 
  

我相信你可以看到我要去的地方，我不知道如何做到这一点，我一直在搜索周围所有的早晨。帮助！

如果需要，我可以提供excel文档。

解决方案

您可以使用 COUNTIF 函数

 列A列B列C答案
 1 1 = COUNTIF（$ A $ 2：$ A $ 9，B2）[1] 
 2 2 = COUNTIF（$ A $ 2：$ A $ 9，B3）[1] 
 3 3 = COUNTIF（$ A $ 2：$ A $ 9，B4）[2] 
 3 5 = COUNTIF $ A $ 2：$ A $ 9，B5）[1] 
 4 6 = COUNTIF（$ A $ 2：$ A $ 9，B6）[2] 
 5 7 = COUNTIF（$ A $ 2：$ A $ 9，B7）[0] 
 6 
 6 
  

Hey, I have an excel document with columns A and B. Row A has 2595 values. Row B has 411 values.

I'm trying to figure out how I can compare these two columns by marking column C with the number of times we find a value(s) from row a.

For Example

COLUMN A           COLUMN B        COLUMN C
1                  1               1 (because we have one value of 1 from column A)
2                  2               1 (because we have one value of 2 from column A) 
3                  3               2 (because we have 2 threes from column A) 
3                  4 
4                  5
5                  6               2 (because there are two 6's in column A) 
6                  7               0 (because there are no 7's in column A)
6

I'm sure you can see where I'm going with this but for the life of me I cannot figure out how to do this, I've been searching around all morning. Help!

If needed I can supply the excel document.

解决方案

You can use the COUNTIF function

Column A    Column B    Column C                Answer
1          1            =COUNTIF($A$2:$A$9,B2) [1]
2          2            =COUNTIF($A$2:$A$9,B3) [1]
3          3            =COUNTIF($A$2:$A$9,B4) [2]
3          5            =COUNTIF($A$2:$A$9,B5) [1]
4          6            =COUNTIF($A$2:$A$9,B6) [2]
5          7            =COUNTIF($A$2:$A$9,B7) [0]
6           
6

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