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

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

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

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

¤£¦n·N«ä¡AÀµ½Ð¦U¦ì¤j¤j«ü¾É©MÀ°¦£~
½Ð±N¤U¦C¤½¦¡µ{¦¡½X¤Æ¡C
ÁÂÁ¡I
¤½¦¡µ{¦¡½X¤Æ.rar (85.64 KB)
Sheets("·Ç2¶i3")!D2ªº­ì¤½¦¡
=LEFT(TEXT(SUM(TEXT(MOD(M2+(0&TRIM(MID(SUBSTITUTE(V2,",","         "),1+{0,1,2,3,4}*9,9))),49),"[=0]!49")*10^{8,6,4,2,0}),"00!,00!,00!,00!,00"),IF(V2="",0,2+3*(LEN(V2)-LEN(SUBSTITUTE(V2,",",)))))
¥k©Ô¨ìJ2¡A¦A¤U©Ô¨ì.Range("J" & .[B2].End(xldown).row - 1) ¶ñº¡

Module 1
¤å¦r»¡©ú¡G
³Æµù¡G
1_­n¬Û¥[ªº2­Ó¥Ø¼ÐÀx¦s®æ=M:S©MV:AB¤G­Ó°Ï¬q¤§¦P¦Cµ¥Äæ¶Z(¶¡¹j8Äæ)ªº¦UÀx¦s®æ¡C
2_VB2:AB" & .[B2].End(xldown).row - 1) = ""ªºÀx¦s®æ¤£­pºâ(²¤¹L)¡C
3_¾l¼Æ=0®É¡Aµø¦P=49¡C   
4_¦³Åã¥Ü2­Ó(§t)¥H¤W®É¡A«h¥H","²Å¸¹°Ï¹j¡C
¹ê¨Ò»¡©ú¡G
Sheets("·Ç2¶i3")
·íV2<>""®É¡A«hD2=(M2ªº­È+V2ªº­È)/49 =>¨ú¾l¼Æ
EX¡GD2 =MOD(11+8,49)=19
EX¡GD2 =MOD(11+27,49)=38
§YD2=19,38
¡G
¡G
·íAA2<>""®É¡A«hI2=(R2ªº­È+AA2ªº­È)/49 =>¨ú¾l¼Æ
EX¡GI2 =MOD(42+30,49)=23
§YI2=23
¡G
¡G
·íW13<>""®É¡A«hE13=(N13ªº­È+W13ªº­È)/49 =>¨ú¾l¼Æ
EX¡GE13 =MOD(04+0,49)=04
EX¡GE13 =MOD(04+45,49)=49
§YE13=04,49
¡G
¡G
·íAB26<>""®É¡A«hJ26=(S26ªº­È+AB26ªº­È)/49 =>¨ú¾l¼Æ
EX¡GJ26=MOD(02+9,49)=11
§YJ26= 11

¨ä¾l¡KSheets("·Ç3¶i4")~ Sheets("·Ç7¶i8")¡KÃþ±À¡C

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-24 23:12 ½s¿è

¦^´_ 1# ziv976688
  1. Sub allsheet()   '<--°õ¦æ³o­Ó
  2. Dim s As Worksheet
  3. For Each s In Worksheets
  4. If s.Name <> "DATA" Then Call ¾l¼Æµn¿ý(s.Name)
  5. Next
  6. End Sub

  7. Sub ¾l¼Æµn¿ý(sN)
  8. Set s = Sheets(sN)
  9. n = s.Columns("v:ab").Find("*", , -4163, , 1, 2).Row
  10. Dim ar
  11. ReDim ar(1 To n, 1 To 7)

  12. For i = 1 To n
  13. For j = 1 To 7
  14. x = s.Cells(i + 1, j + 21)
  15. If x <> "" Then
  16. x = Split(x, ",")
  17.     For Each x0 In x
  18.     x0 = (x0 + s.Cells(i + 1, j + 12)) Mod 49
  19.     If ar(i, j) <> "" Then ar(i, j) = ar(i, j) & "," & x0
  20.     If ar(i, j) = "" Then ar(i, j) = x0
  21.     Next
  22. End If
  23. Next
  24. Next
  25. s.[d2].Resize(n, 7) = ar
  26. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# singo1232001
  1. Sub allsheet()   '<--°õ¦æ³o­Ó
  2. Dim s As Worksheet
  3. For Each s In Worksheets
  4. If s.Name <> "DATA" Then Call ¾l¼Æµn¿ý(s.Name)
  5. Next
  6. End Sub

  7. Sub ¾l¼Æµn¿ý(sN)
  8. Set s = Sheets(sN)
  9. n = s.Columns("v:ab").Find("*", , -4163, , 1, 2).Row
  10. Dim ar
  11. ReDim ar(1 To n, 1 To 7)

  12. For i = 1 To n
  13. For j = 1 To 7
  14. x = s.Cells(i + 1, j + 21)
  15. If x <> "" Then
  16. x = Split(x, ",")
  17.     For Each x0 In x
  18.     x0 = Format((x0 + s.Cells(i + 1, j + 12)) Mod 49,"00") <--§Ñ¤F¥[00
  19.     If ar(i, j) <> "" Then ar(i, j) = ar(i, j) & "," & x0
  20.     If ar(i, j) = "" Then ar(i, j) = x0
  21.     Next
  22. End If
  23. Next
  24. Next
  25. s.[d2].Resize(n, 7) = ar
  26. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# ziv976688
Sub ¾l¼Æµn¿ý()
    Dim xS As Worksheet, xV As Range, xD, SP
    Tm = Timer
    For Each xS In Sheets(Array("·Ç2¶i3", "·Ç3¶i4", "·Ç4¶i5", "·Ç5¶i6", "·Ç6¶i7", "·Ç7¶i8"))    '¨úªí®æ
        For Each xV In xS.Range("V2:AB" & xS.[B65536].End(xlUp).Row)    '¨úÀx¦s®æ
            xD = ""
            For Each SP In Split(xV, ",")    '¤ÀÂ÷¦r¦ê
                SP = (SP + xV.Offset(, -9)) Mod 49: If SP = 0 Then SP = 49 'V2+M2 mod 49
                xD = xD & "," & Format(SP, "00")
            Next
            xV.Offset(30, -18) = Mid(xD, 2, 99) '´ú¸Õ¥Î ¦ì¸m¤U²¾30®æ
            'xV.Offset(, -18) = Mid(xD, 2, 99) '¥¿½T¦ì¸m
        Next
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  singo1232001
singo1232001 µoªí©ó 2021-7-24 23:43

¤½¦¡µ{¦¡½X¤Æ_singo1232001.rar (85.97 KB)
´ú¸Õµ²ªG :µª®×³£¬O FALSE
½Ð¦A­×¥¿~ÁÂÁ±z

TOP

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

¦^´_ 4# ML089
´ú¸Õ¦¨¥\
µ{¦¡½X¥[µù¸Ñ~¨ü¯q¨}¦h
ÁÂÁª©¥Dªº­@¤ß«ü¾Éº~¤@¦Aªº¼ö¤ßÀ°¦£~·P®¦

TOP

¦^´_ 6# ziv976688

¤½¦¡µ{¦¡½X¤Æ_singo1232001.zip (117.49 KB)

TOP

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

¦^´_ 7# singo1232001
¤½¦¡µ{¦¡½X¤Æ_singo1232001_V1.rar (99.52 KB) =>¥i«ö²M°£Áä´ú¸Õ
99%OK¤F!
¥u®t¦C20
x0 = Format((x0 + s.Cells(i + 1, j + 12)) Mod 49, "00") '< --§Ñ¤F¥[00
½Ð¦A¥[¤W¦p¤U±ø¥ó»Ý¨D :
·í¾l¼Æ=0®É¡Aµø¦P=49
EX: E10=19,00
¥¿½TÀ³¬°~
E10=19,49
ÁÂÁ±z

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

¥»©«³Ì«á¥Ñ 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

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD