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

[µo°Ý] ¤½¦¡µ{¦¡½X¤Æ

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-25 20:29 ½s¿è

¦^´_ 17# ziv976688

¤½¦¡µ{¦¡½X¤Æ_singo1232001_v2.zip (140.41 KB)

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-25 20:07 ½s¿è

¦^´_ 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)
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-25 19:51 ½s¿è

¦^´_ 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

TOP

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

TOP

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-7-25 18:00 ½s¿è

¦^´_ 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
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 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
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

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

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-7-25 17:25 ½s¿è

¦^´_ 9# ziv976688

Sub ¼Ð¥Ü©³¦â()
    Dim  xS As Worksheet, xR As Range, SP, r
   
    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 Not xS.Range("M2:S" & r).Find(xR) 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 Not xS.Range("M2:S" & r).Find(Val(SP)) Is Nothing Then xR.Interior.ColorIndex = 8: Exit For    '¼Ð¥ÜÂÅ©³¦â
            Next
        Next
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-25 16:27 ½s¿è

¦^´_ 4# ML089
¼Ð¥Ü©³¦â.rar (105.15 KB) =>¥i«ö²M°£Áä´ú¸Õ
¤£¦n·N«ä¡A¥i§_½Ðª©¥D½ç¥¿~
¼Ð¥Ü©³¦â
Module 5
' If xR > 0 And xR = xS.Range("M" & xS.[B65536].End(xlUp).Row, "S" & xS.[B65536].End(xlUp).Row) Then xR.Interior.ColorIndex = 8  '¼Ð¥ÜÂÅ©³¦â
¦C7

=·íA4:A52<>""ªº¬YÀx¦s®æ¤§­È~
¦³Åã¥Ü¦bM:S¦³¼Æ¦r¤§³Ì«á¤@¦Cªº¥ô¤@­ÓÀx¦s®æ®É¡A
«hAÄ檺¸Ó­È¤§Àx¦s®æ¼Ð¥ÜÂÅ©³¦â¡C

' 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 '¼Ð¥ÜÂÅ©³¦â
¦C12

=·íxS.Range("D2:J" & xS.[B65536].End(xlUp).Row - 1) <>""ªº¬YÀx¦s®æ¤§¥ô1­Ó­È~
¦³Åã¥Ü¦bM:S³Ì«á¤@¦Cªº¥ô¤@­ÓÀx¦s®æ®É¡A
«hxS.Range("D2:J" & xS.[B65536].End(xlUp).Row - 1)ªº¸Ó­È¤§Àx¦s®æ¼Ð¥ÜÂÅ©³¦â¡C

½Ð°Ý¡G
Module 5ªº¦C7©M¦C12¤§µ{¦¡½X­n¦p¦ó­×¥¿ ?
ÁÂÁ±z

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD