[發問] 動態參照公式的編寫

ziv976688

附件： 動態參照公式的編寫.rar (11.21 KB)

下載次數: 3

2022-1-19 02:08

M3=IF(OR($L$1="",INDEX(M:M,MATCH($L1,$H:$H,0))=""),"",MOD($M$1-M2,39))
右拉到Q3
M3的公式需求：
當$L$1=""或INDEX(M:M,MATCH($L1,$H:$H,0))=""時，則M3=""；否則M3=($M$1-M2)/39的餘數。

以下…….同理類推。
S3=IF(OR($L$1="",INDEX(S:S,MATCH($L1,$H:$H,0))=""),"",MOD($S$1-S2,39))
右拉到W3

Y3=IF(OR($L$1="",INDEX(Y:Y,MATCH($L1,$H:$H,0))=""),"",MOD($Y$1-Y2,39))
右拉到AC3

AE3=IF(OR($L$1="",INDEX(AE:AE,MATCH($L1,$H:$H,0))=""),"",MOD($AE$1-AE2,39))
右拉到AI3

紅字的位址以INDIRECT函數，無法達到動態參照的需求。

請問：由M3右拉到AI3一式到位的函數公式要如何編寫？

ziv976688

M3=IF(OR($L$1="",INDEX(M:M,MATCH($L1,$H:$H,0))=""),"",MOD($M$1-M2,39))
右拉到Q3
M3的公式需求：
當$L$1=""或INDEX(M:M,MATCH($L1,$H:$H,0))=""時，則M3=""；否則M3=($M$1-M2)/39的餘數。

以下…….同理類推。
S3=IF(OR($L$1="",INDEX(S:S,MATCH($L1,$H:$H,0))=""),"",MOD($S$1-S2,39))
右拉到W3

Y3=IF(OR($L$1="",INDEX(Y:Y,MATCH($L1,$H:$H,0))=""),"",MOD($Y$1-Y2,39))
右拉到AC3

AE3=IF(OR($L$1="",INDEX(AE:AE,MATCH($L1,$H:$H,0))=""),"",MOD($AE$1-AE2,39))
右拉到AI3

紅字的位址以INDIRECT函數，無法達到動態參照的需求。

請問：由M3右拉到AI3一式到位的函數公式要如何編寫？

hcm19522

$M$1改LOOKUP(99,$M1:M1)

ziv976688

回復 3# hcm19522
測試OK
謝謝您的幫忙和指導~感恩

ziv976688

回復 3# hcm19522
附件 : 函數_動態參照公式的編寫-0120.rar (12 KB)

下載次數: 4

2022-1-20 01:08

不好意思，還有一個類似的需求，末學冒昧再請教 :
M10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:M$1)-B10,10)),B10,""))}
M10的公式需求：
當$L$1=""時，則M10=""；當($B10:$F10)/10的餘數=($B11:$F11)/10的餘數；則M10=B10，否則M10=""。
M10右拉到Q10；
Q10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:Q$1)-F10,10)),F10,""))}

以下…….同理類推。
S10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:S$1)-B10,10)),B10,""))}
S10右拉到W10
W10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:W$1)-F10,10)),F10,""))}

Y10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:Y$1)-B10,10)),B10,""))}
Y10右拉到AC10
AC10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AC$1)-F10,10)),F10,""))}

AE10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AE$1)-B10,10)),B10,""))}
AE10右拉到AI10
AI10{=IF($B11="","",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:AI$1)-F10,10)),F10,""))}

請問：由M10右拉再下拉到AI21一式到位的函數公式要如何編寫？

hcm19522

回復 5# ziv976688


    M10{=IF(($B11="")+(M2=""),"",IF(OR(MOD($B11:$F11,10)=MOD(LOOKUP(99,$M$1:M$1)-N(OFFSET($A10,,MOD(COLUMN(A1),6))),10)),OFFSET($A10,,MOD(COLUMN(A1),6)),""))

ziv976688

回復 6# hcm19522
受益良多
謝謝大大的熱心幫忙和耐心指導~感恩