比大小&重複取其1的函數公式。 - Excelㄧ般區

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

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

2^#

發表於 2019-9-6 22:08 | 顯示全部帖子

要一式全部拉到底，需要將資料欄位對齊，不然公式會又臭又長。

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

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

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

3^#

發表於 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 三鍵輸入公式

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

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

4^#

發表於 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 三鍵輸入公式

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

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

5^#

發表於 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 三鍵輸入公式

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

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

6^#

發表於 2019-9-13 08:12 | 顯示全部帖子

回復 20# ziv976688

因為次數的顯示值9999，會讓比大小的作用失真，所以是否能不要顯示"9999"，而能顯示"0"，
當然能顯示"空白"是最好不過了~但不敢強求，包括前3大的次數顯示"0"能改顯示"空白"亦同。

我習慣用 999 9999 這樣的數字代表溢位
公式是看前面的數字用TEXT的數字大小篩選功能來決定哪些數字要留下，若前面是 "" 時公式就沒有判斷依據
當然你講的都能做，公式變複雜
不習歡看到 9999 也可以用儲存格格式功能來遮蔽 "[=9999] ;0"，或設定格式化條件當9999時字的顏色與底色相同。

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