如何Excel取交集

1.请问excel中两个相等区域里的交集怎么做

以6位数为例的公式如下： =IF(ISERR(FIND(MID(A1,1,1),F1)),"",MID(A1,1,1))&IF(ISERR(FIND(MID(A1,2,1),F1)),"",MID(A1,2,1))&IF(ISERR(FIND(MID(A1,3,1),F1)),"",MID(A1,3,1))&IF(ISERR(FIND(MID(A1,4,1),F1)),"",MID(A1,4,1))&IF(ISERR(FIND(MID(A1,5,1),F1)),"",MID(A1,5,1))&IF(ISERR(FIND(MID(A1,6,1),F1)),"",MID(A1,6,1)） 请看附件。

2.Excel 关于判断两个范围是否有交集,详见附件,请大神们指导

写了个简单的自定义函数：Function f$(RngA As Range, RngB As Range)m = WorksheetFunction.Max(RngA, RngB) '范围内数值最大值mn = WorksheetFunction.Min(RngA, RngB) '范围内数值最小值nReDim a&(n To m) '定义一个范围n To m的数组用于记录For j = 1 To RngA.Columns.CountFor i = RngA.Cells(1, j) To RngA.Cells(2, j)a(i) = 1 '首先遍历A区域，循环各个范围内向数组内赋值=1 即标记为该区段已占用NextNextFor j = 1 To RngB.Columns.CountFor i = RngB.Cells(1, j) To RngB.Cells(2, j)If a(i) Then f = "交集 Intersect!": Exit Function'接着遍历B区域 检查如果对应范围已被占用则提示有交集，退出NextNextf = "无交集 No intersect" '如果全部循环结束都没有交集，则提示无交集End Function。