比较Excel中的两个范围(不进行线性比较) [英] Compare two ranges in excel (not to be compared linearly)

问题描述

我在Excel中有两个范围.

I've got two ranges in excel.

我需要执行以下操作:

1).计算除零以外我有多少个相等的值.在我的示例中，该值应为2(1和8).我找到了这个公式:SUMPRODUCT(--(A2:E2=A3:E3))，但是它只匹配B1，B2，而忽略了数字8也出现了两次.

1). Count how many equal values I have, apart from zero. In my example it should be 2 (1 and 8). I found this formula: SUMPRODUCT(--(A2:E2=A3:E3)), but it will match only B1, B2, ignoring that number 8 appeared two times as well.

2).另外，我需要将这些重复的值放在单个单元格中，并用逗号分隔，就像"1,8"一样.

2). Separately, I need to have these repeated values in a single cell, separated with comma, just like "1,8".

推荐答案

尝试以下简单的 UDF():

Public Function compare(r1 As Range, r2 As Range) As Long
   Dim r As Range, v As Variant, v2 As Variant
   Dim rr As Range
   For Each r In r1
      v = r.Value
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then compare = compare + 1
         Next rr
      End If
   Next r
End Function

用户定义函数(UDF)易于安装和使用:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11弹出VBE窗口
2. ALT-I ALT-M打开一个新模块
3. 将内容粘贴并关闭VBE窗口

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

如果保存工作簿，则UDF将与它一起保存. 如果要在2003年以后使用Excel版本，则必须保存 该文件为.xlsm而不是.xlsx

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

要删除UDF:

1. 如上调出VBE窗口
2. 清除代码
3. 关闭VBE窗口

要从Excel使用UDF，请执行以下操作:

To use the UDF from Excel:

= myfunction(A1)

=myfunction(A1)

要全面了解有关宏的更多信息，请参见:

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft. com/en-us/library/ee814735(v = office.14).aspx

，有关UDF的详细信息，请参见:

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其正常工作！

注意:

NOTE:

如果第二个范围中有 4 个 qwerty ，您将获得每个计数. (但是稍加修改就能避免这种情况)

If there are 4 qwerty in the second range, you will get a count for each one. (but a slight mod can avoid this)

此例程将返回 CSV :

Public Function compare2(r1 As Range, r2 As Range) As String
   Dim r As Range, v As Variant, v2 As Variant
   Dim rr As Range
   For Each r In r1
      v = r.Value
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then compare2 = compare2 & "," & CStr(v)
         Next rr
      End If
   Next r
   If compare2 <> "" Then compare2 = Mid(compare2, 2)
End Function

这篇关于比较Excel中的两个范围(不进行线性比较)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！