[發問] 函數_指定對應數字再顯示它列的公式。

Airman

TEST.rar (17.29 KB)

下載次數: 3

2016-1-31 10:13

請問︰符合如下說明需求的T7公式要如何增編？謝謝！
如果函數公式無法達到需求，亦勞駕告知。再次謝謝！

當I欄的T$5期數之J︰P儲存格數字有顯示在I欄的$R7期數之J︰P儲存格，
且I欄的T$5期數之J︰P儲存格的該數字在I欄的$R7期數之J︰P對應儲存格數字，
亦有再顯示於I欄的$R7+T$3期數之J︰P儲存格時~
則$T7儲存格顯示$R7+T$3之和值，否則顯示空白。
EX1︰I欄的T$5期數之M94(=33)有顯示在N7(=33)，且M94在$R7期數之M7對應儲存格數字(=13)，
亦有再顯示於I欄的$R7+T$3期數之K13(=13)儲存格時~則$T7顯示$R7+T$3之和值(=13)。

EX2︰I欄的T$5期數之J94(=23)有顯示在P15(=23)，且J94在$R15期數之J15對應儲存格數字(=04)，
亦有再顯示於I欄的$R15+T$3期數之P27(=04)儲存格時~則$T15顯示$R15+T$3之和值(=21)。

EX3︰I欄的T$5期數之O94(=38)有顯示在M34(=38)，且O94在$R34期數之O34對應儲存格數字(=43)，
亦有再顯示於I欄的$R34+T$3期數之N46(=43)儲存格時~則$T34顯示$R34+T$3之和值(=40)。

EX4︰I欄的T$5期數之N94(=36)有顯示在N35(=36)，且N94在$R35期數之N35對應儲存格數字(=36)，
亦有再顯示於I欄的$R35+T$3期數之N47(=36)儲存格時~則$T35顯示$R35+T$3之和值(=41)。
其餘......同理類推。

詳如附件。

Airman

回復 1# Airman
以公式修改的方式作補充說明︰
本來的公式都是以$R$5值為搜尋標準~
EX︰
=IF(OR($R7="",$R7>=T$5),"",IF((COUNT(RANK($R$5,OFFSET($I$6,CHOOSE({1,2},T$5,$R7),1,,7)))=2)*OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7)),$R7+T$3,""))

但因有的$R$5值會100期都無答案~(EX︰$R$5=21)~
所以不再限制為$R$5儲存格值，而將其擴大為一期7個儲存格值。

但前段學習了，後段自行修改一直沒有成功~因此提問只PO修改後之前段公式~
後段公式~   敬請各位高手不吝賜教是幸!感恩

准提部林

陣列:
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)),R7+T$3,"")

Airman

回復 3# 准提部林

准大:
測試OK了~感恩

可否另請您再順道賜教後段公式~
OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
由$R$5儲存格值擴大為一期7個儲存格值的單獨公式?(以備當要單獨用時之需)~
謝謝您

Airman

回復 3# 准提部林
准大:
不好意思，雖然SUMPRODUCT,SUMIF,OFFSET等3個函數的意義都了解，但為何能以"/"連結而達到需求答案~至今尚不知所以然

初看貴解時，小弟認為應該是後段公式將$R$5去掉後，擴大為T$5期數的7個儲存格值在$R7期數的對應值有再顯示於$R7+T$3期數之J︰P儲存格~
但不論有或沒有加上前段公式，其答案都是一樣的正解答案~因此以答案說來，小弟又認為貴解答公式應該是將二段公式需求合併成一整段~
也因此才再提出#4的     *OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))
將$R$5值擴大為T$5期數的7個儲存格值在$R7期數的對應值有再顯示於$R7+T$3期數之J︰P儲存格時，顯示$R7+T$3之和值的公式需求~
敬請您賜教為禱~感恩

准提部林

回復 5# Airman


公式不難，要會〔層層〕逐步解，以下用Ｆ９逐一檢視：
Ｕ７：=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))　　
　　　{0,0,0,33,0,0,21}
Ｖ７：=SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0　　
　　　{#NUM!,#NUM!,#NUM!,1,#NUM!,#NUM!,1}
Ｗ７：=OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0　　
　　　{#NUM!,#NUM!,#NUM!,13,#NUM!,#NUM!,21}
Ｘ７：=SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)/SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))^0)
　　　{0,0,0,13,0,0,0}

公式及程式，其實原理差不多，要從錯誤中去修正，了解錯在哪？才能學到真正的點，
今天做不出來，明天再來，這是我強調的〔三花〕．花時間．花精神．花體力，
ＶＢＡ則再加〔一花〕＞〔花錢〕＞買書看～～～沒有捷徑的！

准提部林

回復 5# Airman


OR(SUM(($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7))=OFFSET($I$6,$R7+T$3,1,,7))　

SUM　將　($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7)　加總，
哪有可能與　OFFSET($I$6,$R7+T$3,1,,7)　比對？？？

SUM 與 OR 都是將陣列收合為單一值，須注意運用時機！
多揣摩幾次吧！！！
　
　

准提部林

可再用以下公式逐一推敲!!!

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(SUMIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(COUNTIF(OFFSET($I$6,R7,1,,7),OFFSET($I$6,T$5,1,,7))))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*ISNUMBER(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7))>0))),R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,R7+T$3,1,,7),OFFSET($I$6,R7,1,,7)*(MATCH(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,R7,1,,7),)>0))),R7+T$3,"")

Airman

回復 8# 准提部林
准大:
謝謝您的耗時費神的耐心教導~感恩
小弟尚在研究和努力中~回應先

另外~
SUM　將　($R$5=OFFSET($I$6,T$5,1,,7))*OFFSET($I$6,$R7,1,,7)　加總，
哪有可能與　OFFSET($I$6,$R7+T$3,1,,7)　比對？？？

您誤會了~
小弟的意思是要將~T$5期數有$R$5值且當與該$R$5值同欄位的$R7期數之儲存格值亦有顯示在$R7+T$3期數之J︰P儲存格時，則顯示$R7+T$3之和值~的上述公式~
改為~
當T$5期數的7個儲存格值，其在$R7期數同欄位的儲存格值亦有顯示在$R7+T$3期數之J︰P儲存格時，則顯示$R7+T$3之和值。

但現在想通了~是自己將它想得太複雜而掉進死胡同~只要$R7期數和$R7+T$3有相同值就是修改需求後的答案了

Airman

回復 8# 准提部林
准大:
不好意思~下列的貴2個解答公式~
=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(SUMIF(OFFSET($I$6,$R7,1,,7),OFFSET($I$6,T$5,1,,7))>0))),$R7+T$3,"")

=IF(SUMPRODUCT(SUMIF(OFFSET($I$6,$R7+T$3,1,,7),OFFSET($I$6,$R7,1,,7)*(RANK(OFFSET($I$6,T$5,1,,7),OFFSET($I$6,$R7,1,,7))>0))),$R7+T$3,"")

要如何將其中顯示於$R7+T$3的J︰P任一儲存格之條件~
改成
顯示於$R7+T$3的J︰P之同欄位儲存格。

謝謝您^^