返回列表 上一主題 發帖

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

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

第幾大值的格式化設定.png
2018-4-27 19:03


EXCEL的版本為MS2003

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

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

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

回復 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
複製代碼
注意:會把文字也算入排名

TOP

隨意窩 "EXCEL迷"  blog  或 http://blog.xuite.net/hcm19522/twblog
已收集4100篇 EXCEL函數

TOP

回復 2# naruto018
回復 3# hcm19522

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

TOP

1.資料筆數多少? 太多的話,某些函數會卡檔
2.數字是手動輸入或公式,有否含""或其它文字?
 
最好上檔案看實際數據結構∼∼
 
先試:
=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
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

回復 5# 准提部林

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

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

2.日報表的數字是手動輸入,週報表、月報表、年度總報表則以公式代出;
    只有標題為文字,其餘均為數字(產品序號和數量); 但會有空白的儲存格。
    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

本帖最後由 准提部林 於 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就抓不到

還有, 這些公式只對[整數]管用~~
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

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

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

TOP

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

回復 8# papaya

第三大:
=SMALL(A:A,FREQUENCY(A:A,INDEX(SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))-1,1)))
  
第1及2照舊
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

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

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

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

TOP

        靜思自在 : 布施如播種,以歡喜心滋潤種子,才會發芽。
返回列表 上一主題