返回列表 上一主題 發帖

[發問] 函數公式︰求最後一組合的A欄期數。

[發問] 函數公式︰求最後一組合的A欄期數。

求最後一組合的A欄期數.rar (7.12 KB)

以下問題,請各位先進賜教!謝謝!
求最後一組合的A欄期數.png
2016-4-4 03:38

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=""

其餘...請詳閱附圖或上傳範例附件。 謝謝!

回復 1# ziv976688

TEST_求最後一組合的A欄期數.rar (10.28 KB)

爬文爬了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欄期數為標準,試寫半天,都寫不出正確答案^^"

哇~小學生不能發短消息
只能等囉^^

TOP

本帖最後由 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)),"[=] ")
公式-->重算選項改自動

TOP

回復 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期開始時的公式,要怎麼修正?才能通用。
感恩^^

TOP

{=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"),"[=] ")

TOP

回復 5# hcm19522
TEST-2_求最後一組合的A欄期數.rar (10.15 KB)

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

測試結果詳如上傳附件
標示玫瑰紅底色的儲存格答案沒有出來
勞駕您再賜正!感激不盡~~~

TOP

回復 5# hcm19522

補充:
hcm大大:您好!
不好意思,可否請您將顯示值改為數字型態?
因為這些期數值還要使用於計算式。
感恩^^

TOP

本帖最後由 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(~~,"")

TOP

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

准提版主的公式無法套用於A2期數>1
^^"

TOP

=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))),"")

TOP

        靜思自在 : 我們要做好社會的環保,也要做好內心的環保。
返回列表 上一主題