Airman

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2016-1-31 10:14 | 只看該作者

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

本帖最後由 Airman 於 2016-1-31 10:20 編輯

TEST.rar (17.29 KB)

請問︰符合如下說明需求的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

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

2^#

發表於 2016-2-1 16:02 | 只看該作者

本帖最後由 Airman 於 2016-2-1 16:17 編輯

回復 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修改後之前段公式~
後段公式~ 敬請各位高手不吝賜教是幸!感恩

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

3^#

發表於 2016-2-1 17:02 | 只看該作者

陣列:
=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,"")

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

4^#

發表於 2016-2-1 17:41 | 只看該作者

本帖最後由 Airman 於 2016-2-1 17:42 編輯

回復 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個儲存格值的單獨公式?(以備當要單獨用時之需)~
謝謝您

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

5^#

發表於 2016-2-2 11:55 | 只看該作者

本帖最後由 Airman 於 2016-2-2 12:08 編輯

回復 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之和值的公式需求~
敬請您賜教為禱~感恩

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

6^#

發表於 2016-2-2 12:56 | 只看該作者

回復 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}

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

7^#

發表於 2016-2-2 13:06 | 只看該作者

回復 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 都是將陣列收合為單一值，須注意運用時機！
多揣摩幾次吧！！！
　
　

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

8^#

發表於 2016-2-2 13:41 | 只看該作者

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

=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,"")

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

9^#

發表於 2016-2-2 14:26 | 只看該作者

本帖最後由 Airman 於 2016-2-2 14:29 編輯

回復 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有相同值就是修改需求後的答案了

TOP

Airman

中學生 Rank: 2

帖子: 315
主題: 51
精華: 0
積分: 367
點名: 0
作業系統: win7
軟體版本: office2003
閱讀權限: 20
性別: 男
註冊時間: 2015-9-29
最後登錄: 2021-10-12

10^#

發表於 2016-2-4 20:06 | 只看該作者

回復 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之同欄位儲存格。

謝謝您^^

TOP