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

[µo°Ý] ¨ú±o«ü©w½d³ò¤ºªº¦Uk­È

[µo°Ý] ¨ú±o«ü©w½d³ò¤ºªº¦Uk­È

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

¨ú±o«ü©w½d³ò¤ºªº¦Uk­È.rar (80.05 KB)
³Æµù¡G·í¾l¼Æ=0®É¡Aµø¦P=49¡F·í¦P1­ÓÀx¦s®æ¦³>1­Ók­È®É¡A«h¥H","²Å¸¹°Ï¹j¡C
«ü©w½d³ò=.Range("V2:AB" & .[AC1].End(xldown).row - 1)

For k = 0 To 48    '¦C37
Sheets("·Ç2¶i3")
·íMOD(AE2+k,49)ªº¾l¼Æ¡A¦³Åã¥Ü¦b.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
¥BMOD(AN2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
«hV2= k¡F§_«hV2= ""
¥k©Ô¤U©Ô¨ì .Range("AB" & .[AC1].end(xldown).row - 1) ¶ñº¡

Sheets("·Ç3¶i4")
·íMOD(AE2+k,49)ªº¾l¼Æ¡A¦³Åã¥Ü¦b.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
¥BMOD(AN2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
¥BMOD(AW2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AW"& .[AC1].End(xldown).row, " BC" & .[AC1].End(xldown).row)
«hV2= k¡F§_«hV2= ""
¥k©Ô¤U©Ô¨ì .Range("AB" & .[AC1].End(xldown).row - 1) ¶ñº¡

¨ä¾l¡KSheets("·Ç4¶i5")~ Sheets("·Ç7¶i8")...¦P²zÃþ±À¡C
Next

¸Ô¦p½d¨Òªþ¥óSheets(Shrr(s - 1))

½Ð½ç±Ð¥H¤W»Ý¨Dªºµ{¦¡½X¡C
ÁÂÁ¡I

¦^´_ 1# ziv976688


«ÜÃø²z¸Ñ±zªº»Ý¨D¡A¥i¥H¸ÑÄÀ¸Ô²Ó¤@ÂI¶Ü? ÁÂÁÂ

TOP

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

¦^´_ 2# samwang
¤£¦n·N«ä¡A1¼Óªº¤å¦r»¡©ú¡A§Ú¬O¥H¨ç¼Æ·§©À§@»¡©ú~
EX¡GSheets("·Ç2¶i3")
V2
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27)),k,"")  °}¦C¤½¦¡
¥k©Ô¨ìAB2¡A¦A¤U©Ô¨ìAB26¶ñº¡

EX¡GSheets("·Ç3¶i4")
V2
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27)),k,"")  °}¦C¤½¦¡
¥k©Ô¨ìAB2¡A¦A¤U©Ô¨ìAB26¶ñº¡

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

¦ý§Ú¤£ª¾¹D¨ç¼Æ¯à¤£¯à³]¥ßFor k = 0 To 48¥h­pºâ¡A
¥B¦]¬°·í¦P1­ÓÀx¦s®æ¦³>1­Ók­È®É¡A¥²¶·¥H","²Å¸¹°Ï¹j¡A¹ï¨ç¼ÆªºÃø«×¤Ó°ª¤F¡A
©Ò¥H¥u¦³¤W¨Ó¨D§UVBA¡C

¦pªG¦³»Ý­n¹ê¨Ò»¡©ú(¦p¼Ð¥Ü©³¦âÀx¦s®æªº¹Bºâ)¡A
½Ð±z¤£§[´£¥X~ÁÂÁ±z !

TOP

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

¦^´_ 2# samwang
©I~²×©ó§¹½Z~§Æ±æ±z¦³­@¤ß¬Ý§¹

For k = 0 To 10ªº¹ê¨Ò»¡©ú¡G
Sheets("·Ç2¶i3")
·íMOD(AE2+k,49)ªº¾l¼Æ¡A¦³Åã¥Ü¦b.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
¥BMOD(AN2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
«hV2= k¡F§_«hV2= ""
¥k©Ô¤U©Ô¨ì .Range("AB" & .[AC1].End(xldown).row - 1) ¶ñº¡

EX¡G
·íAE2=04,k=0®É=(04+0)/49ªº¾l¼Æ=4¡A·í4¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=1®É=(04+1)/49ªº¾l¼Æ=5¡A·í5¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=2®É=(04+2)/49ªº¾l¼Æ=6¡A·í6¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=3®É=(04+3)/49ªº¾l¼Æ=7¡A·í7¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=4®É=(04+4)/49ªº¾l¼Æ=8¡A·í8¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C

·íAE2=04,k=5®É=(04+5)/49ªº¾l¼Æ=9¡A·í9¦³Åã¥Ü¦bAE27:AK27¤¤ªºAF27=09=>Ä~ÄòÅçºâAN2¡C
¥B·íAN2=08,k=5®É=(08+5)/49ªº¾l¼Æ=13¡A·í13¨S¦³Åã¥Ü¦bAN27:AT27¤¤=>²¤¹L(V2= "")¡C

·íAE2=04,k=6®É=(04+6)/49ªº¾l¼Æ=10¡A·í10¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=7®É=(04+7)/49ªº¾l¼Æ=11¡A·í11¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C

·íAE2=04,k=8®É=(04+8)/49ªº¾l¼Æ=12¡A·í12¦³Åã¥Ü¦bAE27:AK27¤¤ªºAG27=12=>Ä~ÄòÅçºâAN2¡C
¥B·íAN2=08,k=8®É=(08+8)/49ªº¾l¼Æ=16¡A·í16¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAT27=16=>«hV2= 8¡C

·íAE2=04,k=9®É=(04+9)/49ªº¾l¼Æ=13¡A·í13¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C
·íAE2=04,k=10®É=(04+10)/49ªº¾l¼Æ=14¡A·í14¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V2= "")¡C

µ²ªG¡GV2=8
¨ä¾lFor k = 11 To 48Ãþ±À¡C
¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K
·íAJ14=47,k=0®É=(47+0)/49ªº¾l¼Æ=47¡A·í47¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=1®É=(47+1)/49ªº¾l¼Æ=48¡A·í48¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=2®É=(47+2)/49ªº¾l¼Æ=0=49¡A·í49¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=3®É=(47+3)/49ªº¾l¼Æ=1¡A·í1¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=4®É=(47+4)/49ªº¾l¼Æ=2¡A·í2¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C

·íAJ14=47,k=5®É=(47+5)/49ªº¾l¼Æ=3¡A·í3¦³Åã¥Ü¦bAE27:AK27¤¤ªºAE27=03=>Ä~ÄòÅçºâAS14¡C
¥B·íAS14=48,k=5®É=(48+5)/49ªº¾l¼Æ=4¡A·í4¨S¦³Åã¥Ü¦bAN27:AT27¤¤=>²¤¹L(AA14= "")¡C

·íAJ14=47,k=6®É=(47+6)/49ªº¾l¼Æ=4¡A·í4¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=7®É=(47+7)/49ªº¾l¼Æ=5¡A·í5¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=8®É=(47+8)/49ªº¾l¼Æ=6¡A·í6¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=9®É=(47+9)/49ªº¾l¼Æ=7¡A·í7¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C
·íAJ14=47,k=10®É=(47+10)/49ªº¾l¼Æ=8¡A·í8¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AA14= "")¡C

µ²ªG¡GAA14= ""
¨ä¾lFor k = 11 To 48Ãþ±À¡C
¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K.
·íAK25=02,k=0®É=(02+0)/49ªº¾l¼Æ=2¡A·í2¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C

·íAK25=02,k=1®É=(02+1)/49ªº¾l¼Æ=3¡A·í3¦³Åã¥Ü¦bAE27:AK27¤¤ªºAE27=03=>Ä~ÄòÅçºâAT25¡C
¥B·íAT25=19,k=1®É=(19+1)/49ªº¾l¼Æ=20¡A·í20¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAP27=20=>«hAB25 = 1¡C

·íAK25=02,k=2®É=(02+2)/49ªº¾l¼Æ=4¡A·í4¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C
·íAK25=02,k=3®É=(02+3)/49ªº¾l¼Æ=5¡A·í5¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C
·íAK25=02,k=4®É=(02+4)/49ªº¾l¼Æ=6¡A·í6¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C
·íAK25=02,k=5®É=(02+5)/49ªº¾l¼Æ=7¡A·í7¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C
·íAK25=02,k=6®É=(02+6)/49ªº¾l¼Æ=8¡A·í8¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C

·íAK25=02,k=7®É=(02+7)/49ªº¾l¼Æ=9¡A·í9¦³Åã¥Ü¦bAE27:AK27¤¤ªºAF27=09=>Ä~ÄòÅçºâAT25¡C
¥B·íAT25=19,k=7®É=(19+7)/49ªº¾l¼Æ=26¡A·í26¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAR27=26=>«hAB25 = 7¡C

·íAK25=02,k=8®É=(02+8)/49ªº¾l¼Æ=10¡A·í10¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C
·íAK25=02,k=9®É=(02+9)/49ªº¾l¼Æ=11¡A·í11¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(AB25= "")¡C

·íAK25=02,k=10®É=(02+10)/49ªº¾l¼Æ=12¡A·í12¦³Åã¥Ü¦bAE27:AK27¤¤ªºAG27=12=>Ä~ÄòÅçºâAT25¡C
¥B·íAT25=02,k=10®É=(19+10)/49ªº¾l¼Æ=29¡A·í29¨S¦³Åã¥Ü¦bAE27:AK27¤¤=²¤¹L(AB25= "")¡C

µ²ªG¡GAB25= 1,7
¨ä¾lFor k = 11 To 48Ãþ±À¡C
======================================================================
Sheets("·Ç3¶i4")
·íMOD(AE2+k,49)ªº¾l¼Æ¡A¦³Åã¥Ü¦b.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
¥BMOD(AN2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
¥BMOD(AW2+k,49)ªº¾l¼Æ¤]¦³Åã¥Ü¦b.Range("AW"& .[AC1].End(xldown).row, " BC" & .[AC1].End(xldown).row)
«hV2= k¡F§_«hV2= ""
¥k©Ô¤U©Ô¨ì .Range("AB" & .[AC1].End(xldown).row - 1) ¶ñº¡

EX¡G
·íAE6=01,k=0®É=(01+0)/49ªº¾l¼Æ=1¡A·í1¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=1®É=(01+1)/49ªº¾l¼Æ=2¡A·í2¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C

·íAE6=01,k=2®É=(01+2)/49ªº¾l¼Æ=3¡A·í3¦³Åã¥Ü¦bAE27:AK27¤¤ªºAE27=03=>Ä~ÄòÅçºâAN6¡C
¥BAN6=08,k=2®É=(08+2)/49ªº¾l¼Æ=10¡A·í10¨S¦³Åã¥Ü¦bAN27:AT27¤¤=>²¤¹L(V6= "")¡C

·íAE6=01,k=3®É=(01+3)/49ªº¾l¼Æ=4¡A·í4¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=4®É=(01+4)/49ªº¾l¼Æ=5¡A·í5¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=5®É=(01+5)/49ªº¾l¼Æ=6¡A·í6¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=6®É=(01+6)/49ªº¾l¼Æ=7¡A·í7¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=7®É=(01+7)/49ªº¾l¼Æ=8¡A·í8¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C

·íAE6=01,k=8®É=(01+8)/49ªº¾l¼Æ=9¡A·í9¦³Åã¥Ü¦bAE27:AK27¤¤ªºAF27=09=>Ä~ÄòÅçºâAN6¡C¡C
¥B·íAN5=08,k=8®É=(08+8)/49ªº¾l¼Æ=16¡A·í16¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAT27=16=>Ä~ÄòÅçºâAW6¡C
¥B·íAW6=03,k=8®É=(03+8)/49ªº¾l¼Æ=11¡A·í11¤]¦³Åã¥Ü¦bAW27:BC27¤¤ªºAW27=11=>«hV6= 8¡C

·íAE6=01,k=0®É=(01+9/49ªº¾l¼Æ=10¡A·í10¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C
·íAE6=01,k=0®É=(01+10)/49ªº¾l¼Æ=11¡A·í11¨S¦³Åã¥Ü¦bAE27:AK27¤¤=>²¤¹L(V6= "")¡C

µ²ªG¡GV6= 8
¨ä¾lFor k = 11 To 48Ãþ±À¡C

¨ä¾l¡KSheets("·Ç4¶i5")~ Sheets("·Ç7¶i8")...¦P²zÃþ±À¡C

TOP

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

¦^´_ 2# samwang
¦pªG¥þ¥H°}¦C¤½¦¡§@»¡©ú¡G

For k = 0 To 48
Sheets("·Ç2¶i3")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27)),k,"")
Sheets("·Ç3¶i4")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27)),k,"")
Sheets("·Ç4¶i5")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27))*OR(IF(MOD(BF2+k,49),MOD(BF2+k,49),49)=($BF$27:$BL$27)),k,"")
Sheets("·Ç5¶i6")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27))*OR(IF(MOD(BF2+k,49),MOD(BF2+k,49),49)=($BF$27:$BL$27))*OR(IF(MOD(BO2+k,49),MOD(BO2+k,49),49)=($BO$27:$BU$27)),k,"")
Sheets("·Ç6¶i7")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27))*OR(IF(MOD(BF2+k,49),MOD(BF2+k,49),49)=($BF$27:$BL$27))*OR(IF(MOD(BO2+k,49),MOD(BO2+k,49),49)=($BO$27:$BU$27))*OR(IF(MOD(BX2+k,49),MOD(BX2+k,49),49)=($BX$27:$CD$27)),k,"")
Sheets("·Ç7¶i8")
V2=
=IF(OR(IF(MOD(AE2+k,49),MOD(AE2+k,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2+k,49),MOD(AN2+k,49),49)=($AN$27:$AT$27))*OR(IF(MOD(AW2+k,49),MOD(AW2+k,49),49)=($AW$27:$BC$27))*OR(IF(MOD(BF2+k,49),MOD(BF2+k,49),49)=($BF$27:$BL$27))*OR(IF(MOD(BO2+k,49),MOD(BO2+k,49),49)=($BO$27:$BU$27))*OR(IF(MOD(BX2+k,49),MOD(BX2+k,49),49)=($BX$27:$CD$27))*OR(IF(MOD(CG2+k,49),MOD(CG2+k,49),49)=($CG$27:$CM$27)),k,"")

¤½¦¡³£¥k©Ô¤U©Ô¨ì .Range("AB" & .[AC1].end(xldown).row - 1)
Next

.Range("AB" & .[AC1].End(xldown).row - 1) = AB26

¶È¨Ñ°Ñ¦Ò~ÁÂÁ±z¡I

TOP

¦^´_ 5# ziv976688


¬Ý¨ì±z¥i¥H¼g¨ì³o¼Ë¯uªº¦n¼F®`¡A¬Ý±o³£²´ªá¡A§Ú·|¸ÕµÛ¸ÑŪ±zªº·N«ä¡AÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-7-21 23:43 ½s¿è

¦^´_ 6# samwang
µ§»~­×¥¿¡G
Sheets("·Ç3¶i4")
:
:
·íAE6=01,k=8®É=(01+8)/49ªº¾l¼Æ=9¡A·í9¦³Åã¥Ü¦bAE27:AK27¤¤ªºAF27=09=>Ä~ÄòÅçºâAN6¡C¡C
¥B·íAN5=08,k=8®É=(08+8)/49ªº¾l¼Æ=16¡A·í16¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAT27=16=>Ä~ÄòÅçºâAW6¡C
¥B·íAW6=03,k=8®É=(03+8)/49ªº¾l¼Æ=11¡A·í11¤]¦³Åã¥Ü¦bAW27:BC27¤¤ªºAW27=11=>«hV6= 8¡C
­×¥¿¬°¡G
·íAE6=01,k=8®É=(01+8)/49ªº¾l¼Æ=9¡A·í9¦³Åã¥Ü¦bAE27:AK27¤¤ªºAF27=09=>Ä~ÄòÅçºâAN6¡C¡C
¥B·íAN6=08,k=8®É=(08+8)/49ªº¾l¼Æ=16¡A·í16¤]¦³Åã¥Ü¦bAN27:AT27¤¤ªºAT27=16=>Ä~ÄòÅçºâAW6¡C
¥B·íAW6=03,k=8®É=(03+8)/49ªº¾l¼Æ=11¡A·í11¤]¦³Åã¥Ü¦bAW27:BC27¤¤ªºAW27=11=>«hV6= 8¡C
======================================
¦]¬°±z»¡¡G¥i¥H¸ÑÄÀ¸Ô²Ó¤@ÂI¶Ü?
¦Ó§Ú¤]¤£ª¾¹D­n»¡©ú¨ì¤°»òµ{«×¤~¬O¸Ô²Ó?
©Ò¥H´N¤@¦r¤@¦rºV~¤Ï¥¿¬Ì±¡´Á¶¡¤£¯à¤W¯Z¤]¤£´±¥~¥X~·s¥_¥«­«¨a°Ï~§ÚÁx¤p~«¢~

»¡©úªº¦r¤¸Áö¦hªº²´ªáÁA¶Ã¡A¦ý¯à§ì¦í­«ÂI¤]´N«Ü²³æ~
Sheets("·Ç2¶i3")¥u­n¬Ý(¤ñ¹ï)AE2,AJ14,AK25¤T­ÓÀx¦s®æ~¨ä»¡©ú³£¬O¤@¦¡+0~+10
Sheets("·Ç3¶i4")¥u­n¬Ý(¤ñ¹ï)AE6¤@­ÓÀx¦s®æ~¨ä»¡©ú³£¬O¤@¦¡+0~+10

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-7-22 11:46 ½s¿è

¦^´_ 7# ziv976688

½Ð¥ý´ú¸Õ¬Ý¬Ý¬O§_¬°±zªº»Ý¨D¡A¤U­±¥u¬O·Ç2¶i3¡A¦pªG¨S°ÝÃD½Ð±z¦Û¦æ­×§ïµ{¦¡µ¹¨ä¥¦¤u§@ªí¡A·PÁ¡C

Sub ·Ç2¶i3()
Dim Arr, Arr1, Brr, xD, xD1, T, T1, k%, i&, j&
Arr = Range([ae2], [ak65536].End(3))    '¸ê®Æ¸Ë¤J¼Æ²Õ
Arr1 = Range([an2], [at65536].End(3))   '¸ê®Æ¸Ë¤J¼Æ²Õ
ReDim Brr(1 To UBound(Arr), 1 To 7)     '¹w³]µª®×¼Æ²Õ
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
'±N¼Æ²Õªº³Ì«á¤@¦C¸ê®Æ¸Ë¨ì¦r¨å
For i = UBound(Arr) To UBound(Arr)
    For j = 1 To 7: xD(Arr(i, j) & "") = "": Next
Next
For i = UBound(Arr1) To UBound(Arr1)
    For j = 1 To 7: xD1(Arr1(i, j) & "") = "": Next
Next
'¶}©l¤ñ¹ï
For i = 1 To UBound(Arr): For j = 1 To UBound(Arr, 2)
    T = Arr(i, j): T1 = Arr1(i, j)
    For k = 0 To 48
        a = (T + k) Mod 49: a1 = (T1 + k) Mod 49            'a:MOD(AE2+k,49)ªº¾l¼Æ
        If xD.Exists(a & "") And xD1.Exists(a1 & "") Then   '2Ãä¾l¼Æ³£¦³¦b¦U§O¦r¨å
            If Brr(i, j) = "" Then                          '±Nk¸Ë¤JBrr
                Brr(i, j) = k
            Else
                Brr(i, j) = Brr(i, j) & "," & k
            End If
        End If
    Next
Next: Next
Range("V2").Resize(UBound(Brr), 7) = Brr
End Sub

TOP

¦^´_ 7# ziv976688

§ó·s#8¼Óµ{¦¡ -1 ¦]¬°³Ì«á¤@µ§¤£¥Î¤ñ¹ï¡AÁÂÁÂ
   
'¶}©l¤ñ¹ï
For i = 1 To UBound(Arr)-1

TOP

¦^´_ 9# samwang
¤£¦n·N«ä¡A§Ú´ú¸Õ«á~¶]¤£¥Xµª®×^^"
¯à§_§@­Ó½d¨Ò¨Ñ°Ñ?
ÁÂÁÂ!
¨ú±o«ü©w½d³ò¤ºªº¦Uk­È_samwang.rar (78.22 KB)

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD