123
返回列表 上一主題 發帖

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

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猴年全勤獎章
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 
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猴年全勤獎章
11#
發表於 2019-9-6 22:08 | 只看該作者
要一式全部拉到底，需要將資料欄位對齊，不然公式會又臭又長。
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

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

論壇榮譽勳章論壇贊助勳章熱烈參與勳章2014馬年全勤獎章2015羊年全勤獎章2016猴年全勤獎章
10#
發表於 2019-9-6 22:05 | 只看該作者
還不是很完美，參考看看
陣列公式
AQ3 =MAX(--TEXT(INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),"[>="&IF(AO13,AO13,999)&"]!0"))
AR3 =INDEX(INDIRECT(MID($AQ$1,3,3)),2,MATCH(AQ3,INDEX(INDIRECT(MID($AQ$1,3,3)),MID($AP3,2,3),),))
兩式下拉

複製 AQ3:AR7 至AS3:AV7
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ziv976688

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

不好意思，8樓排大小的一般公式沒有篩選重複值
修改為
次大Sheet1!AS3
=IF(MIN($B$9:$G$9)=AQ3,"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AQ3)+1))
三大Sheet1!AU3
=IF(OR(MIN($B$9:$G$9)=AS3,AS3=""),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&AS3)+1))

次小Sheet2!AS3
=IF(MAX($B$9:$G$9)=AQ3,"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AQ3)+1))
三小Sheet2!AU3
=IF(OR(MAX($B$9:$G$9)=AS3,AS3=""),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&AS3)+1))

TOP

ziv976688

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

仔細研讀https://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105後~
=SUMPRODUCT(1/COUNTIF($A$1:A$11,$A$1:A$11)) 非陣列
上述公式只是在統計範圍內篩選重複值後的數值個數加總，不是排大小。

排大小的一般公式
次大Sheet1!AS3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+2))
三大Sheet1!AU3=IF(MIN($A$9:A9)>=MIN($B9:$G9),"",LARGE($B9:$G9,COUNTIF($B9:$G9,">="&A9)+3))

次小Sheet2!AS3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+2))
三小Sheet2!AU3=IF(MAX($A$9:A9)>=MAX($B9:$G9),"",SMALL($B9:$G9,COUNTIF($B9:$G9,"<="&A9)+3))

TOP

ziv976688

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

有Google到類似公式，經套寫後~
次大Sheet1!AS3=IF(MAX(IF(B9:G9<AQ3,B9:G9))>0,MAX(IF(B9:G9<AQ3,B9:G9)),"") 陣列
三大Sheet1!AU3=IF(MAX(IF(B9:G9<AS3,B9:G9))>0,MAX(IF(B9:G9<AS3,B9:G9)),"")  陣列

次小Sheet2!AS3=IF(MIN(IF(B9:G9>AQ3,B9:G9))>0,MIN(IF(B9:G9>AQ3,B9:G9)),"")  陣列
三小Sheet2!AU3=IF(MIN(IF(B9:G9>AS3,B9:G9))>0,MIN(IF(B9:G9>AS3,B9:G9)),"")  陣列
可惜都是陣列公式。

有爬到"准提版主"回答的相關的一般公式解答~~
https://tw.answers.yahoo.com/question/index?qid=20110720000015KK10105
只是我還沒有悟出如何將直式(ROW)公式，改為橫式(column)公式?
只能再慢慢研究~@~@~

PS :
其中有一個公式，好像很簡潔，只是不知如何改^^"
【=SUMPRODUCT(1/COUNTIF($A$1:$A$11,$A$1:$A$11)) 非陣列
這式子之前看過，也曾收錄，是難得公式。 】

TOP

ziv976688

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

最大Sheet1!AQ3=MAX($B$9:$G$9)；最小Sheet2!AQ3=MIN($B$9:$G$9) =>本來只會這組；

次大Sheet1!AS3=IF(MAX(IF(B9:G9<MAX(B9:G9),B9:G9))>0,MAX(IF(B9:G9<MAX(B9:G9),B9:G9)),"") 陣列；
次小Sheet2!AS3=IF(MIN(IF(B9:G9>MIN(B9:G9),B9:G9))>0,MIN(IF(B9:G9>MIN(B9:G9),B9:G9)),"") 陣列；

第3大Sheet1!AU3 & 第3小Sheet2!AU3  => =>抓破頭，還是套寫不出來^^///

★PS : 是否次大(小)和第3大(小)都能以一般公式(部要鎮列)呈現?

誠請高手賜教 !  謝謝 !

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
5#
發表於 2019-9-2 14:55 | 只看該作者
有爬文找到
https://tw.answers.yahoo.com/question/index?qid=20141006000015KK02830
但不知怎麼套用到第2大或小和第3大或小^^"

TOP

        靜思自在 : 屋寬不如心寬。
123
返回列表 上一主題