標題:
[發問]
函數_多個指定期數有一個(含)以上的交集值。
[打印本頁]
作者:
Airman
時間:
2015-11-19 17:54
標題:
函數_多個指定期數有一個(含)以上的交集值。
本帖最後由 准提部林 於 2015-11-19 18:42 編輯
參考附件︰
http://www.FunP.Net/560821
以
不再增加
名稱定義
為原則。謝謝!
說明︰
名稱定義︰
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公式的
藍色段落
要如何修改才能符合需求?
詳細說明及圖示如附件。
以上 懇請各位先進、前輩不吝賜教! 謝謝!
作者:
准提部林
時間:
2015-11-19 20:16
３對３，頂多對應出３個數字，如何叫交集？
能否用上題我做的方式，舉實例及流程說明？
作者:
Airman
時間:
2015-11-19 21:53
回復
2#
准提部林
准大:
呵 ~呵~小弟有特別註明~二個段落
無須有
對應關係~所以
只針對
三個期數
R7,R7-T3,R7-T3*2
而言:
90
{ 09,12,17,28,
39
,43,
07
}
81
{ 01,
07
,13,
39
,42,45,49 }
72
{
07
,09,14,20,21,
39
,49 }
三個期數都有(交集)
07
,
39
。
作者:
准提部林
時間:
2015-11-19 22:43
回復
3#
Airman
=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1;2;3},$R7,$R7-T$3,$R7-T$3*2),{1,2,3,4,5,6,7})),0,0,0),$R7-T$3*2,""))
=IF(L_1,"",IF(MODE(N(OFFSET($I$6,CHOOSE({1,2,3},$R7,$R7-T$3,$R7-T$3*2),ROW($1:$7))),0,0,0),$R7-T$3*2,""))
三區任一相同, 成立~~
原式 T$5,T$5-T$3,T$5-T$3*2 又有何作用???
作者:
Airman
時間:
2015-11-19 23:17
回復
4#
准提部林
准大:
三期的交集二個公式都測試OK了~但
T$5,T$5-T$3,T$5-T$3*2三個期數必須都要有T5的公式~
請教您:要怎麼套進去?
呵~呵~總不能再加寫3次 OR公式
*OR($R$5=OFFSET($I$6,T$5,1,,7))*OR(..,T$5-T$3,...)*OR(..,T$5-T$3*2,...)
開個玩笑
作者:
准提部林
時間:
2015-11-20 10:04
回復
5#
Airman
檢測三區同時含有$R$5值：
=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))))
=AND(COUNTIF(OFFSET($I$6,CHOOSE({1,2,3},T$5,T$5-T$3,T$5-T$3*2),1,,7),$R$5))
=(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)
作者:
Airman
時間:
2015-11-20 10:57
回復
6#
准提部林
准大:
測試OK了!非常謝謝您如此耐心的指導了3個檢測公式~不勝感激
作者:
Airman
時間:
2015-11-20 12:05
本帖最後由 Airman 於 2015-11-20 12:07 編輯
回復
6#
准提部林
准大:
請教您:
第一式的MOD函數為何要加4個0~$R$5不是只有3個期數嗎?
還有小弟一直對N的意思和用法不瞭解~可否麻煩您用白話文說明一下~因為看F1的說明，小弟一直霧煞煞
謝謝您
第二,三式沒問題~都了解。
作者:
准提部林
時間:
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)
作者:
Airman
時間:
2015-11-20 13:23
回復
9#
准提部林
准大:
感謝您將
MODE
函數之
運用法
~作詳細的說明~小弟了解了~感恩
N( )，
遇〔文字〕以０顯示，..........
函數不吃〔位址參照〕，則須用Ｎ將其轉成〔數值陣列〕！
重點
說明到了~懂了!
每次看到
N
時，雖然F1說得很明白~但明明就是沒有用到文字或日期啊!
如今明白是因為
函數不吃〔位址參照〕
的關係。
謝謝您的耐心指導~受益良多
作者:
准提部林
時間:
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
)
作者:
Airman
時間:
2015-11-20 14:29
回復
6#
准提部林
准大:
第3式跑不出True
查看半天~覺得公式沒有錯~查不出問題
[attach]22517[/attach]
作者:
Airman
時間:
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
?
敬請賜教!感恩
作者:
准提部林
時間:
2015-11-20 15:20
本帖最後由 准提部林 於 2015-11-20 15:22 編輯
回復
13#
Airman
這個公式是錯誤的,
RANK 只會出現 [數值]及[錯誤值], 用 N行不通(無法排錯), 直接COUNT即可,
作者:
Airman
時間:
2015-11-20 16:41
本帖最後由 Airman 於 2015-11-20 16:44 編輯
[attach]22520[/attach]
回復
14#
准提部林
准大:
但只有這樣時，第3式答案才是正確的
配上它式(交集)~1~3式答案也全一樣
???
作者:
准提部林
時間:
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！
注意：這公式應以〔陣列〕輸入！
作者:
Airman
時間:
2015-11-21 02:16
回復
16#
准提部林
准大:
不好意思，原來第3式為
陣列
公式~這樣答案就符合了。
還有多長了一個經驗~答案有疑問時，應該按F9檢視。
再次感謝您的耐心教導~感恩
PS:因外出而遲覆~敬請見諒
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
