麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 函數公式︰求最後一組合的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/)