[發問] 函數_多個指定期數有一個(含)以上相同欄位的交集值。

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

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2015-11-19 16:01 | 只看該作者

[發問] 函數_多個指定期數有一個(含)以上相同欄位的交集值。

參考附件︰http://www.FunP.Net/941833

以不再增加名稱定義為原則。謝謝!

說明︰
名稱定義︰
R_R=$R7； T_3=T$3； T_5=T$5； L_1==OR(T$5="",$R7="",$R7>=T$5,$R7-T$3*2<7)

Sheet1!T7
=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,CHOOSE({1;2;3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5,OFFSET($I$6,CHOOSE({1;2;3},$R7,$R7-T$3,$R7-T$3*2),1,,7)),0,0,0),$R7-T$3*2,""))

需求︰Sheet2!T7
=T5和T5-T3和T5-T3*2三個期數都有R5值；另在$R7,$R7-T$3,$R7-T$3*2三個期數有一個(含)以上相同欄位的交集值。
PS:二個段落無須有對應關係。

請問︰
Sheet1!T7公式的藍色段落要如何修改才能符合Sheet2!T7的需求?

詳細說明及圖示如附件。

以上懇請各位先進、前輩不吝賜教! 謝謝!

樓主熱帖

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

2^#

發表於 2015-11-19 18:01 | 只看該作者

抱歉~
T7公式的藍色段落的顏色漏標了~
Sheet1!T7
=IF(L_1,"",IF(MODE(SUMIF(OFFSET($I$6,CHOOSE({1;2;3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5,OFFSET($I$6,CHOOSE({1;2;3},$R7,$R7-T$3,$R7-T$3*2),1,,7)),0,0,0),$R7-T$3*2,""))

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

3^#

發表於 2015-11-19 22:21 | 只看該作者

本帖最後由 Airman 於 2015-11-19 22:22 編輯

對於相同欄位的交集值之涵義再作補充說明:
因T5,T5-T3,T5-T3*2三個期數和R7,R7-T3,$R7-T3*2三個期數的二個段落無須有對應關係~
所以只針對R7,R7-T3,R7-T3*2三個期數而言:
72 { 07,09,12,20,21,45,39 }
81 { 01,07,13,20,42,45,12 }
90 { 09,12,17,20,39,45,13 }
三個期數的相同欄位都有20,45 。

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

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

4^#

發表於 2015-11-20 10:25 | 只看該作者

回復 3# Airman

三列同欄, 任一相同：
=MODE(N(OFFSET($I$6,CHOOSE({1,2,3},$R96,$R96-T$3,$R96-T$3*2),ROW($1:$7)))+ROW($1:$7)*100,0,0,0)

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

5^#

發表於 2015-11-20 10:50 | 只看該作者

回復 3# Airman
准大:
測試OK了!謝謝您的耐心指導

可否請您再賜教:
檢測T$5和T$5-T$3和T$5-T3$*2三個期數都有$R$5值；且該三期的$R$5值都是相同欄位。感恩

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

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

6^#

發表於 2015-11-20 11:46 | 只看該作者

回復 5# Airman

=AND(SUMIF(OFFSET($I$6,T$5,1,,7),$R$5,OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),1,,7))=$R$5)
=AND(N(OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),MATCH($R$5,OFFSET($I$6,T$5,1,,7),)))=$R$5)
=MODE($R$5,0,0,0,N(OFFSET($I$6,IF({1,0},T$5-T$3,T$5-T$3*2),MATCH($R$5,OFFSET($I$6,T$5,1,,7),))))