ziv976688

¦^´_ 10# ML089
¼Ð¥Ü©³¦â_ML089.rar (105.69 KB)

¤U¸ü¦¸¼Æ: 1

2021-7-25 17:41

  =>¥i«ö²M°£Áä´ú¸Õ
ª©¥D :
±z¬O¤£¬O»~¸Ñ»Ý¨Dªº·N«ä¤F
If Not....Is Nothing.....
Åܦ¨<>""ªºÀx¦s®æ¥þ³¡¼Ð¥ÜÂŦâ
½Ð½ç¥¿!ÁÂÁ±z

n7822123

¦^´_ 11# ziv976688

¥ÎMatch °µªk¼gµ¹§A

Sub ¼Ð¥Ü©³¦â_Ex()
Dim Arr, Shs$, Num, Sh As Worksheet, Rg As Range
Shs = "·Ç2¶i3 ·Ç3¶i4 ·Ç4¶i5 ·Ç5¶i6 ·Ç6¶i7 ·Ç7¶i8"
For Each Sh In Sheets(Split(Shs)): With Sh
  Arr = .[M2].End(4).Resize(, 7)
  Arr = Application.Transpose(Application.Transpose(Arr))  'Âà¤@ºû
  For Each Rg In Union(.Range("A4:A52"), .Range("D2:J" & .[B65536].End(xlUp).Row - 1))
    Rg.Interior.ColorIndex = 0                           '²M©³¦â
    For Each Num In Split(Rg, ",")
      K = Application.Match(--Num, Arr, 0)                     '-- Âà¼Æ¦r¤ñ¹ï
      If Not IsError(K) Then Rg.Interior.ColorIndex = 8  '¼Ð¥ÜÂÅ©³¦â
    Next Num
  Next Rg
End With: Next Sh
End Sub

n7822123

¦^´_ 1# ziv976688

°w¹ï1¼Ó»Ý¨D~~ª©¥D¤w§¹¦¨¡A¦AÀA¤W²Kªá¤@¤U~~

¬Ý§Aªº§Ç¸¹±q 1853~1878¡A­Y¼Æ¦C¦h

¥Î°}¦C¹Bºâ¡A³t«×À³¸Ó·|§Ö¤@¨Ç

¯Â¼Æ­È¤~¥i¥H³o¼Ë°µ¡A§ïÅÜÀx¦s®æÃC¦âÔ£ªº¡A¥ÎÀx¦s®æ´N¦n~

Sub ¾l¼Æ()
Dim Arr(1 To 2), Brr, Sh As Worksheet, Shs$
Shs = "·Ç2¶i3 ·Ç3¶i4 ·Ç4¶i5 ·Ç5¶i6 ·Ç6¶i7 ·Ç7¶i8"
For Each Sh In Sheets(Split(Shs)): With Sh
  Rn& = .[B2].End(4).Row - 1: ReDim Brr(1 To Rn, 1 To 7)
  Arr(1) = .[M2].Resize(Rn, 7): Arr(2) = .[V2].Resize(Rn, 7)
  For R = 1 To Rn: For C = 1 To 7
    For Each Num In Split(Arr(2)(R, C), ",")
      ¾l¼Æ = (Arr(1)(R, C) + Num - 1) Mod 49 + 1
      Brr(R, C) = Brr(R, C) & "," & Format(¾l¼Æ, "00")
    Next Num
    Brr(R, C) = Mid(Brr(R, C), 2)
  Next C: Next R
  .[D30].Resize(Rn, 7) = Brr   '´ú¸Õ¥Î
  '.[D2].Resize(Rn, 7) = Brr     '¥¿½T¦ì¸m
End With: Next Sh
End Sub

ML089

¦^´_ 11# ziv976688
¬O³Ì«á¤@¦C¸ê®Æ¡A§Ú¬Ý¿ù¤F¡A­×¥¿¦p¤U
Sub ¼Ð¥Ü©³¦â_ML089()
    Dim xD As Object, xS As Worksheet, xR As Range, SP, r
    Set xD = CreateObject("Scripting.Dictionary")
    For Each xS In Sheets(Array("·Ç2¶i3", "·Ç3¶i4", "·Ç4¶i5", "·Ç5¶i6", "·Ç6¶i7", "·Ç7¶i8"))    '¨úªí®æ
        r = xS.[B65536].End(xlUp).Row
        For Each xR In xS.Range("A4:A" & xS.[A65536].End(xlUp).Row)    '¨úÀx¦s®æ
            xR.Interior.ColorIndex = 0 '²M©³¦â
            'If xR > 0 And xR = xS.Range("M" & xS.[B65536].End(xlUp).Row, "S" & xS.[B65536].End(xlUp).Row) Then xR.Interior.ColorIndex = 8  '¼Ð¥ÜÂÅ©³¦â
            If Not xS.Range("M" & r, "S" & r).Find(xR, LookAt:=xlWhole) Is Nothing Then xR.Interior.ColorIndex = 8  '¼Ð¥ÜÂÅ©³¦â
        Next

        For Each xR In xS.Range("D2:J" & r - 1)    '¨úÀx¦s®æ
            xR.Interior.ColorIndex = 0 '²M©³¦â
            For Each SP In Split(xR, ",")    '¤À¶}¼Æ¦r
                'If Val(SP) > 0 And Val(SP) = xS.Range("M" & xS.[B65536].End(xlUp).Row, "S" & xS.[B65536].End(xlUp).Row) Then xD(Val(SP)).Interior.ColorIndex = 8 '¼Ð¥ÜÂÅ©³¦â
                If Not xS.Range("M" & r, "S" & r).Find(Val(SP), LookAt:=xlWhole) Is Nothing Then xR.Interior.ColorIndex = 8: Exit For     '¼Ð¥ÜÂÅ©³¦â
            Next
        Next
    Next
End Sub

ziv976688

¦^´_ 12# n7822123
´ú¸Õ¦¨¥\
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

ziv976688

¦^´_ 13# n7822123
´ú¸Õ¦¨¥\
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

¤£¦n·N«ä¡A½Ð±Ð¤@¤U :
  ¦C8
  For Each Num In Split(Arr(2)(R, C), ",")
      ¾l¼Æ = (Arr(1)(R, C) + Num - 1) Mod 49 + 1
½Ð°Ý :
Num¬O«ü¶ñ¤J±±¨î½cªº´Á¶Z¼Æ  Num = "25" ¶Ü?
ÁÙ¬O¥u¬OÅÜ¼Æ ?
ÁÂÁ±z

ziv976688

¦^´_ 14# ML089
´ú¸Õ¦¨¥\
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

½Ð±Ð¤@¤U :
Module 3  ¾l¼Æµn¿ý
°õ¦æ°_¨Ó·|¥d¥dªº~´å¼Ð¤@­È¸õ
¦pªG§âDATA!VB½s¿è¾¹ªº¦C47  Call ¾l¼Æµn¿ý ÂI¬°µù¸Ñ~
´å¼Ð´N¥¿±`¤£¸õ¤F¡C
½Ð°Ý :
³o¬O¤°»ò­ì¦] ?
¬O¤£¬O§Úªº¹q¸£¦³°ÝÃD ?
¼Ð¥Ü©³¦â_ML089_V1.rar (107.08 KB)

¤U¸ü¦¸¼Æ: 3

2021-7-25 20:06

ÁÂÁ±z

singo1232001

¦^´_ 17# ziv976688

ziv976688

¦^´_ 18# singo1232001
´ú¸Õ¦¨¥\
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

singo1232001

¦^´_  n7822123
´ú¸Õ¦¨¥\
ÁÂÁ±zªºÀ°¦£©M«ü¾É~·P®¦

¤£¦n·N«ä¡A½Ð±Ð¤@¤U :
  ¦C ...
ziv976688 µoªí©ó 2021-7-25 19:45

¤£¦n·N«ä¡A½Ð±Ð¤@¤U :
  ¦C8
  For Each Num In Split(Arr(2)(R, C), ",")
      ¾l¼Æ = (Arr(1)(R, C) + Num - 1) Mod 49 + 1
½Ð°Ý :
Num¬O«ü¶ñ¤J±±¨î½cªº´Á¶Z¼Æ  Num = "25" ¶Ü?
ÁÙ¬O¥u¬OÅÜ¼Æ ?
ÁÂÁ±z
'------------------------------------------
À°¦£¸ÑŪ¤@¤U
³o¬q¬OÁY¼g
»Ý­n¥ý©î¤À
¨Ã¥B¥ý±qsplitªº°ò¦¹B¥Î¤è¦¡ ¥ý¾Ç
x=Split(Arr(2)(R, C), ",")
­n¥ý¤F¸Ñ³o¬qªº·N«ä
´N¥²¶·¥ý¤F¸Ñ
Arr(2)(R, C)
¤S¬O¬Æ»ò·N«ä
Arr(1) ¸òarr(2)
§A´N·Q¹³¦¨ ¨â­ÓµêÀÀªº¤u§@ªí (¹ê»Ú¦WºÙ "°}¦C")
©Ò¥H
Arr(2)(R,C)
´N¥Nªí¦b²Ä¤G±iµêÀÀ¤u§@ªíªº ¬Y­ÓÀx¦s®æ¦]¬°¦³R¦CCÄæ­È
¦]¬°¦b°}¦C¤¤ ³B²z³t«×¤ñ¸û§Ö ¤]¤ñ¸û³æ¯Â
¨Ì·Ó·Ç2¶i3¤u§@ªí[V2]¶×¤J·í«eArr(2)µêÀÀ¤u§@ªíªº¦ì¸m
³Ì²× ´N¯à¨ú±o¤@²Õ¦r¦ê ¤ñ¦p "8,27"
¦ì¸mÀ³¸Ó¬OArr(2)°}¦Cªº(0,0)

¨º»ò§Ú­Ì¦^¨ì
x=Split(Arr(2)(R, C), ",")
·N«ä´N¬O
x=Split("8,27", ",")
³o¬O¤@ºØ§Aµ¹¥L¤À¹j­È ¦o´NÀ°§A¨Ì·Ó¤À¹j­È ¤Á¤Àªº¨ç¼Æ ¨Ã¥B·|­Ó§O¦s©ñ
¦p¤Wªº·N«ä
§Ú±N8,27 ¨Ì·Ó","²Å¸¹ ¤Á¤À
³Ì«á§Ú·|±o¨ì
8 »P27¨â­Ó­È  
¤À§O©ñ¦b
x(0)  <-¸Ì­±¬O8
x(1)  <-¸Ì­±¬O27
ªºùØ­±
¦pªG§Aµ¹¥Lªº¦r¦ê¬O5,4,3,2
¨º»ò§A´N·|±o¨ì
x(0) <-¸Ì­±¬O5
x(1) <-¸Ì­±¬O4
x(2) <-¸Ì­±¬O3
x(3) <-¸Ì­±¬O2

¦p¤W­z ¦pªG·Q­n¨ú±ox(1)¸ê®Æ
§Ú­Ì´N¥i¥H
y=x(1)
¨º»òyªº­È´N¬O4

±µµÛÁ¿
°j°é¤À¨âºØ
for i = 1 to 4  ³o¥Nªí³o¦^°é¬O 1 2 3 4
¥t¤@ºØ¬O
for each a In x
³o·§©À¤£¦Pªº¦a¤è«Ü²³æ

°²³] ²{¦b­±«e¦³40­Ó¤jÀY§L ¥L­Ì¬OA¯Z
²Ä¤@ºØ´N¬O §A»¡1¸¹ ¥L¥X¨Ó1¸¹
¦Ó¥B §A¥²¶·½T»{¸Ì­±¦³¦h¤Ö¤H ¤~¤£·|º|³ø ©ÎªÌ¦h³ø
²Ä¤GºØ«h¬O §A»¡ A¯Z³ø¼Æ
³oºØ¤è¦¡ ´N¤£¥Îª¾¹D³o¯Z¦³¦h¤Ö¤H¦s¦b
¤]´N¬O ´N±q·í«eªº¶¶¦ì²Ä¤@­Ó ¨ì³Ì«á¤@­Ó ¤@­Ó¤@­Ó¦Û¤v¯¸¥X¨Ó

³o¼Ë§Ú­Ì´N¯à¤F¸Ñ²Ä¤@¬q
  For Each Num In Split("8,27", ",")

§Ú­Ì­n¸Ì­±ªº 8,27 ½ü¬y¯¸¶i¥hNum¸Ì­±
¤è«K§Ú­Ì«áÄò¨Ï¥Î

·í8¶i¥hNum¤§«á
      ¾l¼Æ = (Arr(1)(R, C) + Num - 1) Mod 49 + 1
§Ú­ÌÄ~Äò°õ¦æ¤W­±³o¬q
Arr(1)(R,C) ¬O¤u§@ªí¤WM2ªº¦ì¸m ¤]´N¬O11
³Ì«á´NÅܦ¨
¾l¼Æ = (11+8-1) mod 49 +1
³Ì«áµª®×
¾l¼Æ= 18+1

µM«á ¦b¶i¦æ¤U¤@­Ó 27¯¸¥X¨Ó
¾l¼Æ = (11+27-1) mod 49 +1
¾l¼Æ= 38
³Ì«á¦A§â 19¸ò38µ²¦X¤@°_
´N·|Åܦ¨
19,38