[發問] 公式優化的問題。

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

2^#

發表於 2019-9-24 04:12 | 只看該作者

本帖最後由 ziv976688 於 2019-9-24 04:21 編輯

回復 1# ziv976688
1樓的需求可忽略不強求~一因只是多寫1個AS3的公式。也不是多麻煩的事，二因有更重要的問題求解~
五行的前3小工作表的AR3
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",))
請問: 公式中的1,2,3,4,5和0,2,4,6,8和4代表什麼意義?
PS :五行的欄數比=6:9:8:8:8
如下圖 :

因為當格式改為如尾數的前3小工作表，其AR3公式就必須改為
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$E,AR$1,)=AQ3,$B$2:$E$2,0),{1,2,3})*10^{0,2,4}),"00"&REPT("!,00",4)),"00,",))
才能顯示正確的答案。
請問: AR3的公式改成這樣正確嗎?
PS :尾數的欄數比=4:4:4:4:4:4:4:4:4:3
如下圖 :

因為539還有
生肖的欄位比=3:3:3:3:3:3:3:3:4:4:4:3
八卦的欄位比=4:5:5:5:5:5:5:5

另外大樂透49有
五行的欄位比=8:12:10:10:9
尾數的欄位比=5:5:5:5:5:5:5:5:5:4
生肖的欄位比=4:4:4:4:4:4:4:4:4:4:5:4
八卦的欄位比=6:6:7:6:6:6:6:6

所以請指導和說明公式中各段的意涵，以利不才能因應格式的變化而填入適當正確的公式。
謝謝!

測試檔 :

測試檔.rar (42.52 KB)

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2019-9-24 09:28 | 只看該作者

回復 1# ziv976688

原問題先回覆，
AQ3原式 =MAX(--TEXT(INDEX($B:$G,AQ$1,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))

AQ3改為 =MAX(--TEXT(INDEX(INDIRECT($AP3),AQ$1,),"[>="&IF(N(AP$1),AO3,999)&"]!0"))
-----------------------
AR3原式 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

AR3改為 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

這樣 AQ3：AR3 可以往下複製，往右複製，
也可以 AQ3：AV7 塊狀複製到 AX3 、BE3

說明：
資料排列正常化可以讓公式較具有通用性
原先 $B:$G 可以由 AP3中的文字轉換，公式為 INDIRECT($AP3)
原先 $B2:$G2 可以由 AP3中的文字轉換，公式為 INDEX(INDIRECT($AP3),2,)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2019-9-24 09:29 | 只看該作者

2樓問題我在找時間看看

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

5^#

發表於 2019-9-24 13:31 | 只看該作者

本帖最後由 ziv976688 於 2019-9-24 13:42 編輯

回復 4# ML089
M版主 : 您好!
感謝您3樓的精闢解題公式~實在是太神太方便了^^
......................................................................................................................
因為由3樓的貴解，得知AP欄的文字，可以用"INDIRECT"轉換~(本來不才是以為$B$2:$G$2無函數可轉換)~
所以2樓的圖片和附件範例的尾數檔案之AP3:AP12，也改為B:E；F:I；......；AL:AN~如附件 :

測試檔_V1.rar (45.92 KB)
謝謝您^^

PS: 目前依3樓的貴解
五行的AR3 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",)

尾數的AR3 =SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3,INDEX(INDIRECT($AP3),2,),0),{1,2,3})*10^{0,2,4}),"00"&REPT("!,00",4)),"00,",)

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2019-9-24 13:44 | 只看該作者

回復 2# ziv976688

五行的前3小工作表的AR3
=IF(AQ3=999,"",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5})*10^{0,2,4,6,8}),"00"&REPT("!,00",4)),"00,",))
請問: 公式中的1,2,3,4,5和0,2,4,6,8和4代表什麼意義?

LARGE(IF(INDEX($B:$G,AR$1,)=AQ3,$B$2:$G$2,0),{1,2,3,4,5}) 其中的 {1,2,3,4,5} 就是公式一次抓出前5大數值，
這5個數字各別乘於 10^{0,2,4,6,8} (這也是5個乘數 {10^0, 10^2, 10^4, 10^6, 10^8} ，也就是 {1,  100, 10000, 1000000, 100000000})

假設前5大數字為 {20, 15, 0, 0, 0}  再乘於 10^{0,2,4,6,8} 就變成 {20, 1500,0,0,0} 再將5數字合計後為 1520，
用 TEXT將數字分隔開來 TEXT(1520, ,"00"&REPT("!,00",4)) 變成 "00,00,00,15,20"
用 SUBSTITUTE,"00,00,00,15,20" ,"00,",) 去除前面的 "00,"  就變成 "15,20"

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2019-9-24 14:02 | 只看該作者

回復 5# ziv976688

"00"&REPT("!,00",4) 展開是 "00,00,00,00,00" 是5組 00，於前面的 {1,2,3,4,5} 取5個數字是對應的。
當然 "00"&REPT("!,00",4) 對應 {1,2,3} 取3個數子也不會有錯，因為3個數字合計後也就是一個數字，頂多前面會多出 "00,00," ，最後SUBTITUTE會將多餘的 "00," 刪除

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

8^#

發表於 2019-9-24 14:10 | 只看該作者

回復 7# ML089
M版主 : 您好!
感謝您的耐心說明與指導~不才瞭解了^^
其餘不同欄位比的公式，不才應該可以自行調整了~感恩...........n+1

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

9^#

發表於 2019-9-24 16:25 | 只看該作者

本帖最後由 ziv976688 於 2019-9-24 16:34 編輯

回復 7# ML089

M版主 : 您好!
不好意思，前3大公式有1個地方，不知能否調整?
謝謝您!

文字說明 :
當沒有次大的次數值(AS3)或第三大的次數值(AU3)時，希望(AS3)或(AU3)能顯示"N"~
因為AS3或AU3顯示0，在某情況下(尤其是在比對欄位數較少時，常會發生)，AT3或AV3會產生"錯誤值"。
詳如下列圖片 ~

測試附件 :

539_尾數排序-V2.rar (4.06 KB)

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

10^#

發表於 2019-9-24 16:45 | 只看該作者

補充 :
如果顯示文字，會比任何純數值都大而不可行時，是否能顯示"負值"~EX : "-999"。
謝謝您!