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

Airman

中學生 Rank: 2

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

3^#

發表於 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

4^#

發表於 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

Airman

中學生 Rank: 2

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

5^#

發表於 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

6^#

發表於 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

Airman

中學生 Rank: 2

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

7^#

發表於 2016-4-14 06:28 | 顯示全部帖子

回復 11# 准提部林

准大:
呵~呵~當天只有看到提醒，但奇怪的是連續數日帖中一直沒有顯示答案。
今天因為上網查資料，才突然看到貴解(日期還是當天2/5的)~真玄!

謝謝您的耐心和費神指導!感恩

TOP