123
返回列表 上一主題 發帖

[發問] 取得指定範圍內的各k值

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2021-7-20 23:06 | 只看該作者

[發問] 取得指定範圍內的各k值

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

取得指定範圍內的各k值.rar (80.05 KB)
備註：當餘數=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

帖子
976 
主題
7 
精華
0 
積分
1018 
點名
0  
作業系統
Win10 
軟體版本
Office 2016 
閱讀權限
50 
性別
男 
註冊時間
2013-4-19 
最後登錄
2024-12-18 
2#
發表於 2021-7-21 14:14 | 只看該作者
回復 1# ziv976688


很難理解您的需求，可以解釋詳細一點嗎? 謝謝

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
3#
發表於 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。

如果有需要實例說明(如標示底色儲存格的運算)，
請您不吝提出~謝謝您 !

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
4#
發表於 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")...同理類推。

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
5#
發表於 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

僅供參考~謝謝您！

TOP

samwang

帖子
976 
主題
7 
精華
0 
積分
1018 
點名
0  
作業系統
Win10 
軟體版本
Office 2016 
閱讀權限
50 
性別
男 
註冊時間
2013-4-19 
最後登錄
2024-12-18 
6#
發表於 2021-7-21 21:20 | 只看該作者
回復 5# ziv976688


看到您可以寫到這樣真的好厲害，看得都眼花，我會試著解讀您的意思，謝謝

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
7#
發表於 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

TOP

samwang

帖子
976 
主題
7 
精華
0 
積分
1018 
點名
0  
作業系統
Win10 
軟體版本
Office 2016 
閱讀權限
50 
性別
男 
註冊時間
2013-4-19 
最後登錄
2024-12-18 
8#
發表於 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

TOP

samwang

帖子
976 
主題
7 
精華
0 
積分
1018 
點名
0  
作業系統
Win10 
軟體版本
Office 2016 
閱讀權限
50 
性別
男 
註冊時間
2013-4-19 
最後登錄
2024-12-18 
9#
發表於 2021-7-22 12:01 | 只看該作者
回復 7# ziv976688

更新#8樓程式 -1 因為最後一筆不用比對，謝謝
   
'開始比對
For i = 1 To UBound(Arr)-1

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
10#
發表於 2021-7-22 13:54 | 只看該作者
回復 9# samwang
不好意思，我測試後~跑不出答案^^"
能否作個範例供參?
謝謝!
取得指定範圍內的各k值_samwang.rar (78.22 KB)

TOP

        靜思自在 : 好事要提得起，是非要放得下，成就別人即是成就自己。
123
返回列表 上一主題