返回列表 上一主題 發帖

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

回復 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 三鍵輸入公式

TOP

2樓問題我在找時間看看
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 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 三鍵輸入公式

TOP

回復 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 三鍵輸入公式

TOP

本帖最後由 ML089 於 2019-9-24 17:02 編輯

回復 9# ziv976688

AQ3 =TEXT(MAX(--TEXT(INDEX(INDIRECT($AP3),AQ$1,),"[>="&IF(N(AP$1),AO3,999)&"]-9;;0")),"0;!N;0")

AR3 =IF(AQ3="N","",SUBSTITUTE(TEXT(SUM(LARGE(IF(INDEX(INDIRECT($AP3),AR$1,)=AQ3*1,INDEX(INDIRECT($AP3),2,),0),{1,2,3,4})*10^{0,2,4,6}),"00"&REPT("!,00",4)),"00,",))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 得理要饒人,理直要氣和。
返回列表 上一主題