Board logo

標題: [發問] 函數公式︰求最後一組合的A欄期數。 [打印本頁]

作者: ziv976688    時間: 2016-4-4 03:39     標題: 函數公式︰求最後一組合的A欄期數。

[attach]23715[/attach]

以下問題,請各位先進賜教!謝謝!
[attach]23716[/attach]
I1=人工填入搜尋指定某數字(01~49)
I2=人工填入搜尋迄止期數
J1=人工填入搜尋顯示$I$1數字的期數的下n
J2:J50=顯示$I$1數字的期數之下n期的指定配對數字(01~49)
K2=搜尋$I$1和K2:K50最後一次同時顯示的需求公式(下拉至K50)
K2=該最後組合有顯示=$I$1數字的A欄期數。
迄止期數的範圍內,都沒有同時顯示指定的配對組合時,則K2=""

其餘...請詳閱附圖或上傳範例附件。 謝謝!
作者: ziv976688    時間: 2016-4-6 20:43

回復 1# ziv976688

[attach]23734[/attach]

爬文爬了2天,終於找到類似需求的公式
https://tw.answers.yahoo.com/question/index?qid=20150314000016KK03053

因為發問者的範例已經逾時移除了,所以只能從文字說明和准提部林版主的解答公式中,邊猜邊兜湊,兜湊了半天,終於完成了。

答案是符合,但公式這樣修改,不知是否正確(無法按F8分段檢視)?
煩請准提部林版主指導一下。謝謝您!
K2
=IF(J2<>"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2)),"")
下拉填滿

複製到
Q2
=IF(P2<>"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+P$1),1,,O$2-P$1,7)=P2)+(OFFSET($B$2,,,O$2-P$1,7)=O$1),{1;1;1;1;1;1;1})=2)),"")

如果公式這樣改是正確的
煩請准提部林版主再指導一下︰
當A2期數不是從第1期開始時,公式又要怎麼修正?
詳如︰Sheet2
謝謝您!

OFFSET   以A欄期數為標準,試寫半天,都寫不出正確答案^^"

哇~小學生不能發短消息
只能等囉^^
作者: hcm19522    時間: 2016-4-7 14:09

本帖最後由 hcm19522 於 2016-4-7 14:11 編輯

L2{=TEXT(MAX((MMULT((OFFSET(B$2,,,I$2,7)=I$1)*1,ROW($1:$7)^0)=1)*(MMULT((OFFSET(B$2,J$1,,I$2,7)=J2)*1,ROW($1:$7)^0)=1)*OFFSET(A$2,,,I$2)),"[=] ")
公式-->重算選項改自動
作者: ziv976688    時間: 2016-4-7 15:05

回復 3# hcm19522
hcm大大:您好!
感謝您的回應和解答。
公式測試結果是可以的,也把不符合條件的儲存格顯示空白了。謝謝您^^

有一個小Bug:
當超過指定範圍的儲存格,符合條件的組合是不計算的
EX:07的範例
當指定迄止範圍是200期,
200期有07,下2期的202期是不屬於計算範圍的,
所以07和13,18,26,28,34,42,48的最後組合期數不是200
應該是  空白,161,197,128,空白,81,189

試在I$2後面添上"-J$1",答案就完全符合了;如果這樣的修正不是正規的公式寫法,勞駕您不吝賜正。謝謝您!
我對MMULT函數完全陌生,所以公式只能以比對答案來測試,尚請見諒。

還有可否請您再指導 2#
當A2期數不是從第1期開始時的公式,要怎麼修正?才能通用。
感恩^^
作者: hcm19522    時間: 2016-4-7 15:51

{=TEXT(TEXT(MAX((MMULT((OFFSET(B$2,,,I$2,7)=I$1)*1,ROW($1:$7)^0)=1)*(MMULT((OFFSET(B$2,J$1,,I$2,7)=J2)*1,ROW($1:$7)^0)=1)*OFFSET(A$2,,,I$2)),"[>="&I$2&"]!0"),"[=] ")
作者: ziv976688    時間: 2016-4-7 16:32

回復 5# hcm19522
[attach]23749[/attach]

hcm大大:您好!
感謝您的快速回應和解答。

測試結果詳如上傳附件
標示玫瑰紅底色的儲存格答案沒有出來
勞駕您再賜正!感激不盡~~~
作者: ziv976688    時間: 2016-4-7 16:44

回復 5# hcm19522

補充:
hcm大大:您好!
不好意思,可否請您將顯示值改為數字型態?
因為這些期數值還要使用於計算式。
感恩^^
作者: hcm19522    時間: 2016-4-7 16:59

本帖最後由 hcm19522 於 2016-4-7 17:04 編輯

准大傑作 很OK
=IF(J2<>"",IFERROR(MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2)),""),"")
多加 IFERROR(~~,"")
作者: ziv976688    時間: 2016-4-7 17:12

回復 8# hcm19522
hcm大大:您好!
IFERROR函數2003沒有^^"
能改用其他函數嗎?謝謝您!

准提版主的公式無法套用於A2期數>1
^^"
作者: hcm19522    時間: 2016-4-8 09:35

=IF(J2<>"",IF(ISNA(MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))),"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J2)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))),"")
作者: ziv976688    時間: 2016-4-8 10:47

回復 10# hcm19522
hcm大大:您好!
謝謝您的再次回應和解答。

還是希望您能指導一下
A2期數>1的公式。謝謝您^^
作者: hcm19522    時間: 2016-4-8 12:51

本帖最後由 hcm19522 於 2016-4-8 12:52 編輯

=IF(J3<>"",IF(ISNA(MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J3)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))),"",MATCH(1,0/(MMULT((OFFSET(INDIRECT("$B$"&1+J$1),1,,I$2-J$1,7)=J3)+(OFFSET($B$2,,,I$2-J$1,7)=I$1),{1;1;1;1;1;1;1})=2))+A$2-1),"")
後面加 "+A$2-1"   題意不是很了解    是這樣?
作者: ziv976688    時間: 2016-4-8 13:05

本帖最後由 ziv976688 於 2016-4-8 13:06 編輯

回復 12# hcm19522
hcm大大:您好!
謝謝您的耐心回應和解答。

OK了!您太厲害了!謝謝您!

您的部落格內容包羅萬象,很精采,很值得參閱和推薦
作者: ziv976688    時間: 2016-4-8 15:32

回復 12# hcm19522
hcm大大:您好!
[attach]23765[/attach]
仔細研究您的3#公式和准大的原公式
以二個公式再比對,才發現二位高手的公式答案都是正確的。
只是當初自己設立I$2的用意是指填入搜尋計算的迄止期數,但一時忽略了I$2放在OFFSET使用時,是指列數高度
所以一直測試不出正解

謝謝您的耐心指導,讓我知道自己的盲點是在何處?
再次感恩




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)