[已解决]excel中如何令另一单元格中包含第一单元格中的其中一个数时返回1

pqp888

求一公式，请看附件。

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2016-1-18 17:45

D5=

1. =IF($B5="","",COUNT(FIND(","&D$4&",",","&$B5&",")))

复制代码

跳转到最佳答案楼层

心正意诚身修

D5=

1. =IF($B5="","",COUNT(FIND(","&D$4&",",","&$B5&",")))

复制代码

pqp888

心正意诚身修 发表于 2016-1-18 17:45
D5=

老师您好：您给的公式很好，但用后发现有个决点，当数据量大时，就不太合适用，不知我的这个目的您能否帮我用ＶＢＡ来实现？谢谢！

心正意诚身修

1. Sub dem()
   
2. Dim a, b, s, k
   
3. a = Range("b5:c" & [b65536].End(3).Row)
   
4. For s = 1 To UBound(a)
   
5.     b = Split(a(s, 1), ",")
   
6.     a(s, 1) = ""
   
7.     For k = 0 To UBound(b)
   
8.            If Val(b(k)) = [d4] Then a(s, 1) = 1: Exit For Else a(s, 1) = 0
   
9.     Next
   
10.     For k = 0 To UBound(b)
    
11.            If Val(b(k)) = [e4] Then a(s, 2) = 1: Exit For Else a(s, 2) = 0
    
12.     Next
    
13. Next
    
14. [d5].Resize(k, 2) = a
    
15. End Sub
    

复制代码

pqp888

心正意诚身修 发表于 2016-1-19 12:12

老师您好：代码我复制了，好象不成功。

pqp888

心正意诚身修 发表于 2016-1-19 12:12

老师您好：可否烦请您帮我看看您给我的代码是不是哪里出错了，我复制后只能运行几行。

心正意诚身修

1. Sub dem()
   
2. Dim a, b, s, k
   
3. Range("d5:e9999") = ""
   
4. a = Range("b5:c" & [b65536].End(3).Row)
   
5. For s = 1 To UBound(a)
   
6.     b = Split(a(s, 1), ",")
   
7.     a(s, 1) = ""
   
8.     For k = 0 To UBound(b)
   
9.            If Val(b(k)) = [d4] Then a(s, 1) = 1: Exit For Else a(s, 1) = 0
   
10.     Next
    
11.     For k = 0 To UBound(b)
    
12.            If Val(b(k)) = [e4] Then a(s, 2) = 1: Exit For Else a(s, 2) = 0
    
13.     Next
    
14. Next
    
15. [d5].Resize(s - 1, 2) = a
    
16. End Sub
    

复制代码

pqp888

心正意诚身修 发表于 2016-1-20 14:38

谢谢