帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2018-4-27 19:05 | 只看該作者

[發問] 第幾大值的格式化設定

EXCEL的版本為MS2003

文字說明︰
最大數的儲存格標示黃色
次大數的儲存格標示綠色
第三大數的儲存格標示粉藍色

同樣排名的同數儲存格可以重複標示相同顏色~即可以有1個以上的最大數或次大數或第三大數。

請教︰
設定格式化的條件1~3公式？謝謝！

樓主熱帖

naruto018

中學生 Rank: 2

帖子: 91
主題: 5
精華: 0
積分: 130
點名: 0
作業系統: Win10
軟體版本: Office 2010
閱讀權限: 20
性別: 男
註冊時間: 2016-5-14
最後登錄: 2020-5-17

2^#

發表於 2018-4-28 10:25 | 只看該作者

回復 1# papaya
參考看看
第1大

=SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=1

複製代碼

第2大

=SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=2

複製代碼

第3大

=SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=3

複製代碼

注意：會把文字也算入排名

TOP

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-25

熱烈參與勳章

3^#

發表於 2018-4-28 10:43 | 只看該作者

http://blog.xuite.net/hcm19522/twblog/578319049

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

papaya

中學生 Rank: 2

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

4^#

發表於 2018-4-28 11:02 | 只看該作者

回復 2# naruto018
回復 3# hcm19522

謝謝二位大大的回覆和熱心指導。
測試OK了!感恩!

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

5^#

發表於 2018-4-28 11:10 | 只看該作者

１．資料筆數多少？　太多的話，某些函數會卡檔
２．數字是手動輸入或公式，有否含""或其它文字？
　
最好上檔案看實際數據結構～～
　
先試：
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),1)=A1　或用　=MAX(A:A)=A1
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),2)=A1　或用　=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),3)=A1

TOP

papaya

中學生 Rank: 2

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

6^#

發表於 2018-4-30 07:58 | 只看該作者

本帖最後由 papaya 於 2018-4-30 08:11 編輯

回復 5# 准提部林

不好意思，系統沒有"提醒"，剛剛才看到。

因您的審慎斟酌解答準確性的態度，小弟感覺理應詳細回覆背景資料，有煩瑣多言之處，尚請見諒。
１．每月的資料列數約30-31列，20~30欄左右(每個業務員1欄，5~7人為一組，看業務員的出勤率)。
所以年度也只有365列，20欄左右；
只是另開立的年度銷售數量產品專屬報表的總列數會達5000列左右

２．日報表的數字是手動輸入，週報表、月報表、年度總報表則以公式代出；
只有標題為文字，其餘均為數字(產品序號和數量)；但會有空白的儲存格。
7人組的產品銷售日報表檔案格式，大概(發問用，無數量欄，沒有很精準)如
http://forum.twbts.com/thread-20733-1-1.html

發問前都會先上網查詢資料，有搜尋到
http://forum.twbts.com/viewthrea ... amp;highlight=LARGE
也是"LARGE+FREQUENCY"；但文中提到︰資料3000列就不好用，必須再設"定義名稱"，
覺得麻煩，所以上來發問，希望能找到更佳的答案。

您的解答公式針對特性各有所用，小弟採用
=MAX(A:A)=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-2))=A1
取其純數值和公式值均可用，並可略過空白儲存格的優點，
尤其是可點選"整欄"，不需要拉個要命去選取儲存格範圍的特性。

非常感謝您的熱心協助和耐心指導!感恩!

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-26

7^#

發表於 2018-4-30 12:04 | 只看該作者

本帖最後由准提部林於 2018-4-30 12:37 編輯

回復 6# papaya

=MAX(A:A)=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-2))=A1

第三大那個公式是錯的,
如果最大數與第三大差數不是2, 如 99 95 90 88 84 這90就抓不到

還有, 這些公式只對[整數]管用~~

TOP

papaya

中學生 Rank: 2

帖子: 323
主題: 79
精華: 0
積分: 407
點名: 0
作業系統: win7
軟體版本: office 2003
閱讀權限: 20
性別: 男
註冊時間: 2017-10-22
最後登錄: 2022-1-18

8^#

發表於 2018-4-30 14:38 | 只看該作者

本帖最後由 papaya 於 2018-4-30 14:39 編輯

回復 7# 准提部林
對噢!沒有注意到^^"
那最大和第二大的差數<>1時，第二大的公式不也是不能用。

以A欄只有第1列標題為文字，其餘為含公式的整數值，列數為5000列左右為背景資料，
其最大值格式化設定公式=MAX(A:A)=A1
其第二大和第三大的格式化設定公式?
謝謝您!

TOP

准提部林