ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð±Ð°}¦C¤ñ¹ï¶W¹L3­Ó

½Ð±Ð°}¦C¤ñ¹ï¶W¹L3­Ó

½Ð±Ð¥ý¶i°ª¤â  °}¦C¤ñ¹ï¶W¹L3­Ó ¤º¦³¸Ô²Ó»¡©ú
°}¦C¤ñ¹ïA.rar (8.29 KB) ·PÁÂ~~~:lol

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-6 10:35 ½s¿è

¦^´_ 1# duck_simon


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç½m²ß°}¦C»P¦r¨åªº¸Ñ¨M¤è®×¦p¤U,½Ð°Ñ¦Ò

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST_1()
Dim Brr, xR As Range, i&, j&, N&, A, B, V, Y, Z
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
Brr = [A1:J12]: [A1:J12].Interior.ColorIndex = xlNone: [K20] = ""
For Each xR In [B20:F20]
   Z(xR & "") = xR.Interior.ColorIndex: V = "/" & xR & "/" & V
Next
For i = 4 To UBound(Brr)
   Set Y(i) = CreateObject("Scripting.Dictionary")
   For j = 2 To UBound(Brr, 2)
      If Val(Brr(i, j)) > 0 Then
         Set Y(i)(Brr(i, j) & "") = Cells(i, j)
      End If
   Next
Next
For Each A In Y.Keys
   For Each B In Y(A).Keys
      If InStr(V, "/" & B & "/") Then
         N = N + 1
         If N >= 3 Then [K20] = "X": Exit For
      End If
   Next
   If N >= 3 Then
      For Each B In Y(A).Keys
         Y(A)(B).Interior.ColorIndex = Z(B)
      Next
   End If
   N = 0
Next
Set Y = Nothing: Set Z = Nothing: Set Brr = Nothing
End Sub
'==================================
'¨C¦CÂo­«½Æ¼Æ¦r
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 3# Andy2483

ÂǦ¹©«½m²ß§ï¬°¦Û­q¨ç¼Æ

Bingo(¸ê®Æ®æ, ÃöÁä¦r®æ, Åã¥Ü¦r¤¸,¨C¦C²Å¦X¼Æ¶q)
K15=Bingo(B4:J12,B20:F20,"X",3)


Option Explicit
Function Bingo(¸ê®Æ®æ As Range, ÃöÁä¦r®æ As Range, T$, S%)
Dim Brr, xR As Range, i&, j&, N&, A, B, V, Y
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
Brr = ¸ê®Æ®æ
For Each xR In ÃöÁä¦r®æ
   V = "/" & xR & "/" & V
Next
For i = 1 To UBound(Brr)
   Set Y(i) = CreateObject("Scripting.Dictionary")
   For j = 1 To UBound(Brr, 2)
      If Val(Brr(i, j)) > 0 Then
         Set Y(i)(Brr(i, j) & "") = Cells(i, j)
      End If
   Next
Next
For Each A In Y.Keys
   For Each B In Y(A).Keys
      If InStr(V, "/" & B & "/") Then
         N = N + 1
         If N >= S Then
            Bingo = T
            Set Y = Nothing: Set Brr = Nothing
            Exit Function
         End If
      End If
   Next
   N = 0
Next
Bingo = ""
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ duck_simon ©ó 2023-3-6 14:55 ½s¿è

·PÁ¤G¦ì«e½ú«ü¾É..§Ú¥N¤J«á..¦]¬°®æ¤£¦P²£¥Í#VALUE!
IF(OR(MMULT(COUNTIF($N18:$S18,$AV$3:$BH$14),ROW(3:14)^0)>2),"X","")
½Ð±Ð­þ¸Ì¥X°ÝÃD? ¦]¦b¤U²Â©å..¬O¦æ¦C°ª«× ¶Ü?   ÁÂÁÂ

°}¦C¤ñ¹ïA.rar (8.29 KB)

TOP

¦^´_ 5# duck_simon

ªí®æ ¤â°Ê§ï¦Û°Ê :ÂI¤W­± "¤½¦¡"-->ÂI¥kÃä "¹Bºâ¿ï¶µ"-->ÂI "¦Û°Ê"
ROW(3:14)§ïROW(1:13) ¸Õ¸Õ
±Æ°£N18:S18ªÅ®æ=IF(OR(MMULT(COUNTIF(N18:S18,AV3:BH14)*(AV3:BH14<>""),ROW(1:13)^0)>2),"X","")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 5# duck_simon


    ¤µ¤Ñ­×§ï½Æ²ß¤F¤@¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST_1()
Dim Brr, A, B, V, Y, Z, xR As Range, i&, j&, N&
'¡ô«Å§iÅܼÆ:(Brr,A,B,V,Y,Z)¬O³q¥Î«¬ÅܼÆ,xR¬OÀx¦s®æÅܼÆ,(i,j,N)¬Oªø¾ã¼ÆÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO¦r¨å
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO¦r¨å
Brr = [A1:BH14]
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C!¥H[A1:BH14]Àx¦s®æ­È±a¤J
[AV3:BH14].Interior.ColorIndex = xlNone
'¡ô¥O[AV3:BH14]Àx¦s®æ©³¦â¬OµL¦â
[K20] = ""
'¡ô¥O[K20]Àx¦s®æ­È¬OªÅ¦r¤¸
For Each xR In [N18:S18]
'¡ô³]°j°é¥OxR³oÀx¦s®æÅܼƬO [N18:S18]¸Ìªº¤@Àx¦s®æ
   Z(xR & "") = xR.Interior.ColorIndex
   '¡ô¥OxRÅÜ¼Æ ³s±µªÅ¦r¤¸²Õ¦Xªº¦r¦ê·íKey,Item¬OxRÅܼƪº©³¦â,¯Ç¤JZ¦r¨å
   V = "/" & xR & "/" & V
   '¡ô¥OV³o³q¥Î«¬ÅܼƬO "/" ³s±µ xRÅÜ¼Æ ¦A³s±µ "/" ³Ì«á³s±µ VÅܼƦۨ­
Next
For i = 3 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q3 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   Set Y(i) = CreateObject("Scripting.Dictionary")
   '¡ô¥OiÅܼƷíKey,Item¬O¦r¨å,¯Ç¤JY¦r¨å¸Ì
   For j = 22 To UBound(Brr, 2)
   '¡ô³]¶¶°j°é!j±q22 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹
      If Val(Brr(i, j)) > 0 Then
      '¡ô¦pªG¥HVal()Âà¤Æ i¦CjÄ檺Brr°}¦C­È¤j©ó0 ?
         Set Y(i)(Brr(i, j) & "") = Cells(i, j)
         '¡ô¥Oi¦CjÄ檺Brr°}¦C­È³s±µªÅ¦r¤¸ªº·s¦r¦ê·íKey,
         'Item¬Oi¦CjÄæÀx¦s®æ,¯Ç¤JiÅܼƪºY¦r¨å

      End If
   Next
Next
For Each A In Y.Keys
'¡ô³]¥~³v¶µ°j°é!¥OA³o³q¥Î«¬ÅܼƬOY¦r¨åªº¨ä¤¤¤@­ÓKey
   For Each B In Y(A).Keys
   '¡ô³]¤º³v¶µ°j°é!¥OB³o³q¥Î«¬ÅܼƬOAÅܼÆY¦r¨åªº¨ä¤¤¤@­ÓKey
      If InStr(V, "/" & B & "/") Then
      '¡ô¦pªGVÅܼƸ̦³ ¥]§t(BÅܼƫe«á³s±µ "/"ªº·s¦r¦ê)??
         N = N + 1
         '¡ôIf±ø¥ó¦¨¥ß´N¥ON³oªø¾ã¼ÆÅܼƲ֥[1
         If N >= 3 Then [K20] = "X": Exit For
         '¡ô¦pªGNÅÜ¼Æ >=3!´N¥O[K20]Àx¦s®æ­È¬O"X"!µM«á¸õ¥X¤º°j°é
      End If
   Next
   If N >= 3 Then
   '¡ô¦pªGNÅÜ¼Æ >=3 ?
      For Each B In Y(A).Keys
      '¡ô³]¤º³v¶µ°j°é!¥OBÅܼƬOAÅܼÆY¦r¨åªº¨ä¤¤¤@­ÓKey
         Y(A)(B).Interior.ColorIndex = Z(B)
         '¡ô¥OBÅܼƬdAÅܼÆY¦r¨åªºItem©³¦â¬O ¥HBÅܼƬdZ¦r¨å¦^¶Ç­È
      Next
   End If
   N = 0
Next
Set Y = Nothing: Set Z = Nothing: Set Brr = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# hcm19522

¦^hcm19522 ¤j¤j:
N18:S18¨SªÅ®æ..¥B¬O¼Æ­ÈµL»~.¸Õ¤F¤@±ß.§ä¤£¨ì­ì¦]§Ú¹Á¸Õ¨Ï¥Î»²§UÄæ  ©Î§ï¬°²Î­p¼Æ¦r..ÁÂÁÂ¥ý¶i!....

TOP

¦^´_ 7# Andy2483


    ¤j¤j¹ï¤_VB «D±`²`¤J..±©¤pªº³s¤ò³£¤£À´..¨£¯º¤F..§ÚÁÙ¬O¦¬ÂÃ.®³¨Ó¥Î¤W.«D±`·PÁÂ!.

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD