Board logo

標題: [發問] 取得指定範圍內的各k值 [打印本頁]

作者: ziv976688    時間: 2021-7-20 23:06     標題: 取得指定範圍內的各k值

本帖最後由 ziv976688 於 2021-7-20 23:17 編輯

[attach]33705[/attach]
備註:當餘數=0時,視同=49;當同1個儲存格有>1個k值時,則以","符號區隔。
指定範圍=.Range("V2:AB" & .[AC1].End(xldown).row - 1)

For k = 0 To 48    '列37
Sheets("準2進3")
MOD(AE2+k,49)的餘數,顯示在.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
MOD(AN2+k,49)的餘數也有顯示在.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
則V2= k;否則V2= ""
右拉下拉到 .Range("AB" & .[AC1].end(xldown).row - 1) 填滿

Sheets("準3進4")
MOD(AE2+k,49)的餘數,顯示在.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
MOD(AN2+k,49)的餘數也有顯示在.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
MOD(AW2+k,49)的餘數也有顯示在.Range("AW"& .[AC1].End(xldown).row, " BC" & .[AC1].End(xldown).row)
則V2= k;否則V2= ""
右拉下拉到 .Range("AB" & .[AC1].End(xldown).row - 1) 填滿

其餘…Sheets("準4進5")~ Sheets("準7進8")...同理類推。
Next

詳如範例附件Sheets(Shrr(s - 1))

請賜教以上需求的程式碼。
謝謝!
作者: samwang    時間: 2021-7-21 14:14

回復 1# ziv976688


很難理解您的需求,可以解釋詳細一點嗎? 謝謝
作者: ziv976688    時間: 2021-7-21 16:48

本帖最後由 ziv976688 於 2021-7-21 16:53 編輯

回復 2# samwang
不好意思,1樓的文字說明,我是以函數概念作說明~
EX:Sheets("準2進3")
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,"")  陣列公式
右拉到AB2,再下拉到AB26填滿

EX:Sheets("準3進4")
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,"")  陣列公式
右拉到AB2,再下拉到AB26填滿

其餘…Sheets("準4進5")~ Sheets("準7進8")…類推。

但我不知道函數能不能設立For k = 0 To 48去計算,
且因為當同1個儲存格有>1個k值時,必須以","符號區隔,對函數的難度太高了,
所以只有上來求助VBA。

如果有需要實例說明(如標示底色儲存格的運算),
請您不吝提出~謝謝您 !
作者: ziv976688    時間: 2021-7-21 19:22

本帖最後由 ziv976688 於 2021-7-21 19:25 編輯

回復 2# samwang
呼~終於完稿~希望您有耐心看完

For k = 0 To 10的實例說明:
Sheets("準2進3")
當MOD(AE2+k,49)的餘數,顯示在.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
MOD(AN2+k,49)的餘數也有顯示在.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
V2= k否則V2= ""
右拉下拉到 .Range("AB" & .[AC1].End(xldown).row - 1) 填滿

EX:
當AE2=04,k=0時=(04+0)/49的餘數=4,當4沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=1時=(04+1)/49的餘數=5,當5沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=2時=(04+2)/49的餘數=6,當6沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=3時=(04+3)/49的餘數=7,當7沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=4時=(04+4)/49的餘數=8,當8沒有顯示在AE27:AK27中=>略過(V2= "")。

當AE2=04,k=5時=(04+5)/49的餘數=9,當9顯示在AE27:AK27中的AF27=09=>繼續驗算AN2
當AN2=08,k=5時=(08+5)/49的餘數=13,當13沒有顯示在AN27:AT27中=>略過(V2= "")。

當AE2=04,k=6時=(04+6)/49的餘數=10,當10沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=7時=(04+7)/49的餘數=11,當11沒有顯示在AE27:AK27中=>略過(V2= "")。

當AE2=04,k=8時=(04+8)/49的餘數=12,當12顯示在AE27:AK27中的AG27=12=>繼續驗算AN2
當AN2=08,k=8時=(08+8)/49的餘數=16,當16也有顯示在AN27:AT27中的AT27=16=>則V2= 8

當AE2=04,k=9時=(04+9)/49的餘數=13,當13沒有顯示在AE27:AK27中=>略過(V2= "")。
當AE2=04,k=10時=(04+10)/49的餘數=14,當14沒有顯示在AE27:AK27中=>略過(V2= "")。

結果:V2=8
其餘For k = 11 To 48類推。
………………………………………………………………………………………………………
當AJ14=47,k=0時=(47+0)/49的餘數=47,當47沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=1時=(47+1)/49的餘數=48,當48沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=2時=(47+2)/49的餘數=0=49,當49沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=3時=(47+3)/49的餘數=1,當1沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=4時=(47+4)/49的餘數=2,當2沒有顯示在AE27:AK27中=>略過(AA14= "")。

當AJ14=47,k=5時=(47+5)/49的餘數=3,當3顯示在AE27:AK27中的AE27=03=>繼續驗算AS14
當AS14=48,k=5時=(48+5)/49的餘數=4,當4沒有顯示在AN27:AT27中=>略過(AA14= "")。

當AJ14=47,k=6時=(47+6)/49的餘數=4,當4沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=7時=(47+7)/49的餘數=5,當5沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=8時=(47+8)/49的餘數=6,當6沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=9時=(47+9)/49的餘數=7,當7沒有顯示在AE27:AK27中=>略過(AA14= "")。
當AJ14=47,k=10時=(47+10)/49的餘數=8,當8沒有顯示在AE27:AK27中=>略過(AA14= "")。

結果:AA14= ""
其餘For k = 11 To 48類推。
……………………………………………………………………………………………………………….
當AK25=02,k=0時=(02+0)/49的餘數=2,當2沒有顯示在AE27:AK27中=>略過(AB25= "")。

當AK25=02,k=1時=(02+1)/49的餘數=3,當3顯示在AE27:AK27中的AE27=03=>繼續驗算AT25
當AT25=19,k=1時=(19+1)/49的餘數=20,當20也有顯示在AN27:AT27中的AP27=20=>則AB25 = 1

當AK25=02,k=2時=(02+2)/49的餘數=4,當4沒有顯示在AE27:AK27中=>略過(AB25= "")。
當AK25=02,k=3時=(02+3)/49的餘數=5,當5沒有顯示在AE27:AK27中=>略過(AB25= "")。
當AK25=02,k=4時=(02+4)/49的餘數=6,當6沒有顯示在AE27:AK27中=>略過(AB25= "")。
當AK25=02,k=5時=(02+5)/49的餘數=7,當7沒有顯示在AE27:AK27中=>略過(AB25= "")。
當AK25=02,k=6時=(02+6)/49的餘數=8,當8沒有顯示在AE27:AK27中=>略過(AB25= "")。

當AK25=02,k=7時=(02+7)/49的餘數=9,當9顯示在AE27:AK27中的AF27=09=>繼續驗算AT25
當AT25=19,k=7時=(19+7)/49的餘數=26,當26也有顯示在AN27:AT27中的AR27=26=>則AB25 = 7

當AK25=02,k=8時=(02+8)/49的餘數=10,當10沒有顯示在AE27:AK27中=>略過(AB25= "")。
當AK25=02,k=9時=(02+9)/49的餘數=11,當11沒有顯示在AE27:AK27中=>略過(AB25= "")。

當AK25=02,k=10時=(02+10)/49的餘數=12,當12顯示在AE27:AK27中的AG27=12=>繼續驗算AT25
當AT25=02,k=10時=(19+10)/49的餘數=29,當29沒有顯示在AE27:AK27中=略過(AB25= "")。

結果:AB25= 1,7
其餘For k = 11 To 48類推。
======================================================================
Sheets("準3進4")
當MOD(AE2+k,49)的餘數,顯示在.Range("AE"& .[AC1].End(xldown).row, " AK"& .[AC1].End(xldown).row)
MOD(AN2+k,49)的餘數也有顯示在.Range("AN"& .[AC1].End(xldown).row, " AT" & .[AC1].End(xldown).row)
MOD(AW2+k,49)的餘數也有顯示在.Range("AW"& .[AC1].End(xldown).row, " BC" & .[AC1].End(xldown).row)
V2= k否則V2= ""
右拉下拉到 .Range("AB" & .[AC1].End(xldown).row - 1) 填滿

EX:
當AE6=01,k=0時=(01+0)/49的餘數=1,當1沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=1時=(01+1)/49的餘數=2,當2沒有顯示在AE27:AK27中=>略過(V6= "")。

當AE6=01,k=2時=(01+2)/49的餘數=3,當3顯示在AE27:AK27中的AE27=03=>繼續驗算AN6
且AN6=08,k=2時=(08+2)/49的餘數=10,當10沒有顯示在AN27:AT27中=>略過(V6= "")。

當AE6=01,k=3時=(01+3)/49的餘數=4,當4沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=4時=(01+4)/49的餘數=5,當5沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=5時=(01+5)/49的餘數=6,當6沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=6時=(01+6)/49的餘數=7,當7沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=7時=(01+7)/49的餘數=8,當8沒有顯示在AE27:AK27中=>略過(V6= "")。

當AE6=01,k=8時=(01+8)/49的餘數=9,當9顯示在AE27:AK27中的AF27=09=>繼續驗算AN6。。
當AN5=08,k=8時=(08+8)/49的餘數=16,當16也有顯示在AN27:AT27中的AT27=16=>繼續驗算AW6
當AW6=03,k=8時=(03+8)/49的餘數=11,當11也有顯示在AW27:BC27中的AW27=11=>則V6= 8

當AE6=01,k=0時=(01+9/49的餘數=10,當10沒有顯示在AE27:AK27中=>略過(V6= "")。
當AE6=01,k=0時=(01+10)/49的餘數=11,當11沒有顯示在AE27:AK27中=>略過(V6= "")。

結果:V6= 8
其餘For k = 11 To 48類推。

其餘…Sheets("準4進5")~ Sheets("準7進8")...同理類推。
作者: ziv976688    時間: 2021-7-21 19:50

本帖最後由 ziv976688 於 2021-7-21 20:00 編輯

回復 2# samwang
如果全以陣列公式作說明:

For k = 0 To 48
Sheets("準2進3")
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進4")
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進5")
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進6")
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進7")
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進8")
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,"")

公式都右拉下拉到 .Range("AB" & .[AC1].end(xldown).row - 1)
Next

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

僅供參考~謝謝您!
作者: samwang    時間: 2021-7-21 21:20

回復 5# ziv976688


看到您可以寫到這樣真的好厲害,看得都眼花,我會試著解讀您的意思,謝謝
作者: ziv976688    時間: 2021-7-21 23:14

本帖最後由 ziv976688 於 2021-7-21 23:43 編輯

回復 6# samwang
筆誤修正:
Sheets("準3進4")
:
:
當AE6=01,k=8時=(01+8)/49的餘數=9,當9顯示在AE27:AK27中的AF27=09=>繼續驗算AN6。。
AN5=08,k=8時=(08+8)/49的餘數=16,當16也有顯示在AN27:AT27中的AT27=16=>繼續驗算AW6
當AW6=03,k=8時=(03+8)/49的餘數=11,當11也有顯示在AW27:BC27中的AW27=11=>則V6= 8
修正為:
當AE6=01,k=8時=(01+8)/49的餘數=9,當9顯示在AE27:AK27中的AF27=09=>繼續驗算AN6。。
AN6=08,k=8時=(08+8)/49的餘數=16,當16也有顯示在AN27:AT27中的AT27=16=>繼續驗算AW6
當AW6=03,k=8時=(03+8)/49的餘數=11,當11也有顯示在AW27:BC27中的AW27=11=>則V6= 8
======================================
因為您說:可以解釋詳細一點嗎?
而我也不知道要說明到什麼程度才是詳細?
所以就一字一字敲~反正疫情期間不能上班也不敢外出~新北市重災區~我膽小~哈~

說明的字元雖多的眼花瞭亂,但能抓住重點也就很簡單~
Sheets("準2進3")只要看(比對)AE2,AJ14,AK25三個儲存格~其說明都是一式+0~+10
Sheets("準3進4")只要看(比對)AE6一個儲存格~其說明都是一式+0~+10
作者: samwang    時間: 2021-7-22 11:40

本帖最後由 samwang 於 2021-7-22 11:46 編輯

回復 7# ziv976688

請先測試看看是否為您的需求,下面只是準2進3,如果沒問題請您自行修改程式給其它工作表,感謝。

Sub 準2進3()
Dim Arr, Arr1, Brr, xD, xD1, T, T1, k%, i&, j&
Arr = Range([ae2], [ak65536].End(3))    '資料裝入數組
Arr1 = Range([an2], [at65536].End(3))   '資料裝入數組
ReDim Brr(1 To UBound(Arr), 1 To 7)     '預設答案數組
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
'將數組的最後一列資料裝到字典
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
'開始比對
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)的餘數
        If xD.Exists(a & "") And xD1.Exists(a1 & "") Then   '2邊餘數都有在各別字典
            If Brr(i, j) = "" Then                          '將k裝入Brr
                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
作者: samwang    時間: 2021-7-22 12:01

回復 7# ziv976688

更新#8樓程式 -1 因為最後一筆不用比對,謝謝
   
'開始比對
For i = 1 To UBound(Arr)-1
作者: ziv976688    時間: 2021-7-22 13:54

回復 9# samwang
不好意思,我測試後~跑不出答案^^"
能否作個範例供參?
謝謝!
[attach]33719[/attach]
作者: samwang    時間: 2021-7-22 14:25

回復 10# ziv976688


請先確認是否您的需求,如果是正確無誤,剩下其它工作表就簡單了,謝謝
作者: 准提部林    時間: 2021-7-22 14:56

Sub TEST()
Dim xS As Worksheet, xD, Arr(6), Brr, R&, i&, j%, k%, x%, N%, T$
Set xD = CreateObject("Scripting.Dictionary")
For Each xS In Sheets(Array("準2進3", "準3進4", "準4進5", "準5進6", "準6進7", "準7進8"))
    xD.RemoveAll
    R = xS.[ac65536].End(xlUp).Row - 1
    N = N + 1: If R < 1 Then GoTo s01
    ReDim Brr(1 To R - 1, 1 To 7)
    For k = 0 To N
        Arr(k) = xS.[ae2].Cells(1, k * 9 + 1).Resize(R, 7)
        For j = 1 To 7
            xD(Arr(k)(R, j) & "|" & k) = 1
        Next j
    Next k
    '--------------------------------------
    For i = 1 To R - 1
    For j = 1 To 7
        For x = 0 To 48
        For k = 0 To N
            T = (Arr(k)(i, j) + x) Mod 49 & "|" & k
            If xD(T) = 0 Then GoTo x001
        Next k
            Brr(i, j) = Brr(i, j) & IIf(Brr(i, j) = "", "", ",") & x
x001:   Next x
    Next j
    Next i
    '-------------------------------------
    With xS.[v2].Resize(R - 1, 7)
         .NumberFormatLocal = "@"
         .Value = Brr
    End With
s01: Next
End Sub

說明亂, 也亂寫一通~~
作者: ziv976688    時間: 2021-7-22 15:00

回復 11# samwang
2016版打不開
不好意思~給您添麻煩^^"
作者: ziv976688    時間: 2021-7-22 15:18

回復 12# 准提部林
測試成功
謝謝您一再的幫忙和指導。
感恩
作者: ziv976688    時間: 2021-7-22 20:47

回復 11# samwang
[attach]33723[/attach]
我把二個貴解程式碼分別放在Module1和Module3
整理後,Module1還是顯示不出V2:AB26的答案;Module3的A4:A52的答案就OK了!
可否煩請您再賜正?
謝謝您
作者: samwang    時間: 2021-7-22 21:25

回復 15# ziv976688


請再測試看看,謝謝
作者: samwang    時間: 2021-7-22 21:36

回復 12# 准提部林


准大寫的很太精簡,只用了一個數組和字典就搞定多個工作表資料,之前已有看過字典同時裝入不一樣的資料
這次又學到數組也可以這樣使用,真的是一個很好的範例學習了,感謝分享。
作者: ziv976688    時間: 2021-7-22 22:10

回復 16# samwang
測試成功
謝謝您的耐心指導和幫忙
作者: ziv976688    時間: 2021-7-28 00:53

本帖最後由 ziv976688 於 2021-7-28 01:07 編輯

回復 12# 准提部林
[attach]33771[/attach]  =>可按清除鍵~重新測試
版主 : 您好!
請教您~
Module 2
如果要把列20  T = (Arr(k)(i, j) + x) Mod 49 & "|" & k
改為 -x
然後有二種方式的計算狀況 :
A式 : 當 -x之後的差值為負數時,則T自動轉為正數(即如=絕對值)
EX : 準2進3!V2=
IF(OR(IF(MOD(ABS(AE2-x),49),MOD(ABS(AE2-x),49),49)=($AE$27:$AK$27))*OR(IF(MOD(ABS(AN2-x),49),MOD(ABS(AN2-x),49),49)=($AN$27:$AT$27)),x,"")
右拉再下拉填滿
EX :  W2=11;X2=37

B式 : 當 -x之後的差值為負數時,則b]T自動再+49轉為正數(即如=一般的函數計算)
EX : 準2進3!V2=
IF(OR(IF(MOD(AE2-x,49),MOD(AE2-x,49),49)=($AE$27:$AK$27))*OR(IF(MOD(AN2-x,49),MOD(AN2-x,49),49)=($AN$27:$AT$27)),x,"")
右拉再下拉填滿
EX :  V2=23;X2=48  

以上 二種的計算方式之程式碼~懇請指導。
謝謝您^^
作者: 准提部林    時間: 2021-7-28 11:02

回復 19# ziv976688


1)
T = ABS((Arr(k)(i, j) - x) Mod 49) & "|" & k

2)
V=(Arr(k)(i, j) - x) Mod 49
IF V<0 THEN V=V+49
T=V & "|" & K
作者: ziv976688    時間: 2021-7-28 12:42

回復 20# 准提部林
版主 :
測試成功
謝謝您的幫忙和指導~感恩




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)