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

papaya

下載 (44.71 KB)

2018-4-27 19:03

EXCEL的版本為MS2003

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

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

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

naruto018

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

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

複製代碼

第2大

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

複製代碼

第3大

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

複製代碼

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

hcm19522

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

papaya

回復 2# naruto018
回復 3# hcm19522

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

准提部林

１．資料筆數多少？　太多的話，某些函數會卡檔
２．數字是手動輸入或公式，有否含""或其它文字？
　
最好上檔案看實際數據結構～～
　
先試：
=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

papaya

回復 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
取其純數值和公式值均可用，並可略過空白儲存格的優點，
尤其是可點選"整欄"，不需要拉個要命去選取儲存格範圍的特性。

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

准提部林

回復 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就抓不到

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

papaya

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

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

准提部林

回復 8# papaya

第三大:
=SMALL(A:A,FREQUENCY(A:A,INDEX(SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))-1,1)))
  
第1及2照舊

papaya

回復 9# 准提部林
公式漏了=A1，有自行補上。

測試後誠如需求!
感謝您一再的熱心協助和耐心指導。
感恩再感恩!