123
返回列表 上一主題 發帖

[發問] 比大小&重複取其1的函數公式。

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
11# 跳轉到 »
發表於 2019-9-6 22:08 | 只看該作者
要一式全部拉到底，需要將資料欄位對齊，不然公式會又臭又長。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
12#
發表於 2019-9-6 23:45 | 只看該作者
回復 10# ML089
M版主 :您好!
AQ3 :AR3  兩式下拉 =>答案正確
複製 AQ3:AR7 至AS3:AV7 =>AS3:AV7都跟AQ3:AR7 相同 =>答案不正確
萬分感謝您一再的熱心指導^^
===========================
M版主 :
為了讓(順應)公式好編寫
不才將表格微調修改如下~
AP9剪下，貼上AP10
AP3:AP10的儲存格式改為=>自訂=>"列"#
詳如附件 : TSET_1.rar (16.52 KB)
謹請您參考!
謝謝您!晚安^^

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
13#
發表於 2019-9-9 20:27 | 只看該作者
本帖最後由 ML089 於 2019-9-9 20:40 編輯

回復 12# ziv976688

剛好六日都有事沒有辦法立即回覆
(1)
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AO3,AO3,999)&"]!0"))
AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
複製 AQ3:AR3
貼上 AQ4:AR7
複製 AQ3:AR7
貼上 AS3:AV7

(2)
複製 AQ3:AR3
貼上各區的左上角儲存格
將公式中的 $B:$G 修改為
  木($H:$P)
  水($Q:$X)
  火($Y:$AF)
  土($AG:$AN)                       

如(1) 複製及貼上 該區公式

(3) 當然公式也可以自動抓取 第一列中如下的文字，利用INDIRECT轉為區域，但不需付出超長公式作為代價，建議用手動修改比較容易維護公式。
  金($B:$G)
  木($H:$P)
  水($Q:$X)
  火($Y:$AF)
  土($AG:$AN)

比大小&重複取其1的函數公式(1).rar (20.29 KB)
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
14#
發表於 2019-9-10 02:36 | 只看該作者
回復 13# ML089
M版主:
謝謝您的耐心賜教!
有範例檔方便許多~不才會好好研究~感恩........^^

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
15#
發表於 2019-9-10 21:06 | 只看該作者
本帖最後由 ML089 於 2019-9-11 10:43 編輯

回復 14# ziv976688

(1)        AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))
        AR3 =INDEX($B:$G,2,MATCH(AQ3,INDEX($B:$G,$AP3,),))
        陣列公式

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

(1)式的AR7會顯示 21  (數字)
(2)式的AR7會顯示 21,22 (文字)

比大小&重複取其1的函數公式(2).rar (20.94 KB)
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
16#
發表於 2019-9-10 23:39 | 只看該作者
本帖最後由 ziv976688 於 2019-9-10 23:52 編輯

回復 15# ML089
版主:您好!
您太厲害了，太神了!
將不才的最終目的都達成了!感恩.........^^

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
17#
發表於 2019-9-12 03:23 | 只看該作者
本帖最後由 ziv976688 於 2019-9-12 03:32 編輯

回復 15# ML089
測試檔 : 前3大改前3小的公式.rar (24.65 KB)
M版主:您好!
很抱歉!因為貴公式超高階，已超越不才能理解的範圍N多，所以不才試改了近一天，終是沒轍^^///(汗顏)

冒昧再請問 :
空數前3小!AQ3:BC8的前3大公式((2)式)要如何改為前3小的公式?
誠請您賜教!謝謝您^^

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
18#
發表於 2019-9-12 22:54 | 只看該作者
回復 17# ziv976688

取大至小
AQ3 =MAX(--TEXT(INDEX($B:$G,$AP3,),"[>="&IF(AQ$1="",AO3,999)&"]!0"))

取小至大
AQ3 =MIN(--TEXT(INDEX($B:$G,$AP3,),"[<="&IF(AQ$1="",AO3,-1)&"]!9!9"))
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
19#
發表於 2019-9-12 23:41 | 只看該作者
回復 18# ML089

M版主:您好!
感謝您抽空回覆^^
AQ3=> OK了!
AR3的答案也會跟著對應更動成為"前3小"(很神^^)

請問一下:
為什麼AQ3:AV3下拉到AQ8:AV8
AQ3:AV7全部正確，AQ8答案也正確，
但AS8和AU8答案不正確。

同理
AX3:BB7全部正確，AX8答案也正確，
AZ8和BB8答案不正確。
誠請您再次指教!謝謝您^^

測試檔 : 前3大改前3小的公式(0912).rar (24.42 KB)

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
20#
發表於 2019-9-13 00:40 | 只看該作者
本帖最後由 ziv976688 於 2019-9-13 00:53 編輯

回復 18# ML089

M版主:您好!
瞭解不才在19樓的問題是出在哪裡了^^
是因為52列的第1小超過99了。
將公式中的"!9!9"改為"!99!9"或"!99!99"就可以了。

但因此又產生一個小問題~
因為次數的顯示值9999，會讓比大小的作用失真，所以是否能不要顯示"9999"，而能顯示"0"，
當然能顯示"空白"是最好不過了~但不敢強求，包括前3大的次數顯示"0"能改顯示"空白"亦同。
EX : 假設將B9:G9全部改為18，則AS3=0；AU3=0或AS=""；AU3=""

不好意思，問題一堆，誠請見諒! 謝謝您^^"

祝您 : 中秋節快樂!

TOP

        靜思自在 : 發脾氣是短暫的發瘋。
123
返回列表 上一主題