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

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

17^#

發表於 2015-11-21 02:16 | 只看該作者

回復 16# 准提部林
准大:
不好意思，原來第3式為陣列公式~這樣答案就符合了。
還有多長了一個經驗~答案有疑問時，應該按F9檢視。
再次感謝您的耐心教導~感恩

PS:因外出而遲覆~敬請見諒

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

16^#

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

回復 15# Airman

以Sheet3. T35為例：
=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2;3},$R35,$R35-T$3,$R35-T$3*2),{1,2,3,4,5,6,7})),0,0,0)*(COUNT(N(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3)),$R35-T$3*2,""))　

框住紅色段按Ｆ９看看，值為０，條件是不成立的！（但答案看來沒錯，應是誤撞，多一些測試值才準）

=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2;3},$R35,$R35-T$3,$R35-T$3*2),{1,2,3,4,5,6,7})),0,0,0)*(COUNT(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3),$R35-T$3*2,""))

藍色段值為TRUE！
注意：這公式應以〔陣列〕輸入！

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

15^#

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

本帖最後由 Airman 於 2015-11-20 16:44 編輯

TEST.rar (28.2 KB) 回復 14# 准提部林

准大:
但只有這樣時，第3式答案才是正確的

配上它式(交集)~1~3式答案也全一樣

???

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

14^#

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

本帖最後由准提部林於 2015-11-20 15:22 編輯

回復 13# Airman

這個公式是錯誤的,
RANK 只會出現 [數值]及[錯誤值], 用 N行不通(無法排錯), 直接COUNT即可,

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

13^#

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

本帖最後由 Airman 於 2015-11-20 14:55 編輯

回復 11# 准提部林

准大:
呵 ~呵 ~您未說前，小弟就試了~完全瞭解了N()的用法~您真是個優良老師~飽學之士，不一定能表達而會教學~讓學生完全貫通。

有將第3式改為=
(COUNT(N(RANK($R$5,OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7)))=3))
就會顯示1(TRUE)
是不是第3式少了N?
敬請賜教!感恩

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

12^#

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

回復 6# 准提部林
准大:
第3式跑不出True
查看半天~覺得公式沒有錯~查不出問題

TEST.rar (23.7 KB)

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

11^#

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

本帖最後由准提部林於 2015-11-20 14:07 編輯

回復 10# Airman

用Ｆ９看一看下方公式的差異：
=OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),ROW($1:$7))=$R$5
=N(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),ROW($1:$7)))=$R$5
=N(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7))=$R$5
=COUNTIF(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5)

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

10^#

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

回復 9# 准提部林

准大:
感謝您將MODE函數之運用法~作詳細的說明~小弟了解了~感恩

N( )，
遇〔文字〕以０顯示，..........
函數不吃〔位址參照〕，則須用Ｎ將其轉成〔數值陣列〕！
重點說明到了~懂了!
每次看到N時，雖然F1說得很明白~但明明就是沒有用到文字或日期啊!
如今明白是因為函數不吃〔位址參照〕的關係。
謝謝您的耐心指導~受益良多

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

9^#

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

本帖最後由准提部林於 2015-11-20 13:16 編輯

回復 8# Airman

=MODE($R$5,0,0,0,0,N(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),ROW($1:$7))))
MODE取眾數，若有符合者且有多個，則取出最先出現者，
之前將0,0,0放在後面，即前面有３個相同數時，則優先取之；否則只有０符合３個，
此次將$R$5放在最前面，除非後面有３個與$R$5相同湊成４個（後面３個相同數不一定是$R$5），否則只有0,0,0,0符合４個！

N( )，
遇〔文字〕以０顯示，
遇TRUE,FALSE，以１，０顯示，
在 OFFSET 上，若函數不吃〔位址參照〕，則須用Ｎ將其轉成〔數值陣列〕！
其它須自行一一去揣模～～

可測試以下公式：
=MODE(11,0,0,0,11,11,33)
=MODE(11,0,0,0,0,11,11,33)
=MODE(11,0,0,0,0,11,11,11)