標題:
[發問]
第幾大值的格式化設定
[打印本頁]
作者:
papaya
時間:
2018-4-27 19:05
標題:
第幾大值的格式化設定
[attach]28629[/attach]
EXCEL的版本為MS2003
文字說明︰
最大數的儲存格標示黃色
次大數的儲存格標示綠色
第三大數的儲存格標示粉藍色
同樣排名的同數儲存格
可以重複
標示相同顏色~即可以有1個以上的最大數或次大數或第三大數。
請教︰
設定格式化的條件1~3公式？謝謝！
作者:
naruto018
時間:
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
複製代碼
注意：會把文字也算入排名
作者:
hcm19522
時間:
2018-4-28 10:43
http://blog.xuite.net/hcm19522/twblog/578319049
作者:
papaya
時間:
2018-4-28 11:02
回復
2#
naruto018
回復
3#
hcm19522
謝謝二位大大的回覆和熱心指導。
測試OK了!感恩!
作者:
准提部林
時間:
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
作者:
papaya
時間:
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
取其純數值和公式值均可用，並可略過空白儲存格的優點，
尤其是可點選"
整欄
"，不需要拉個要命去選取儲存格範圍的特性。
非常感謝您的熱心協助和耐心指導!感恩!
作者:
准提部林
時間:
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就抓不到
還有, 這些公式只對[整數]管用~~
作者:
papaya
時間:
2018-4-30 14:38
本帖最後由 papaya 於 2018-4-30 14:39 編輯
回復
7#
准提部林
對噢!沒有注意到^^"
那最大和第二大的差數<>1時，第二大的公式不也是不能用。
以A欄只有第1列標題為文字，其餘為含公式的整數值，列數為5000列左右為背景資料，
其最大值格式化設定公式=MAX(A:A)=A1
其第二大和第三大的格式化設定公式?
謝謝您!
作者:
准提部林
時間:
2018-4-30 17:24
本帖最後由 准提部林 於 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照舊
作者:
papaya
時間:
2018-4-30 19:35
本帖最後由 papaya 於 2018-4-30 19:39 編輯
回復
9#
准提部林
公式漏了=A1，有自行補上。
測試後誠如需求!
感謝您一再的熱心協助和耐心指導。
感恩再感恩!
作者:
ML089
時間:
2018-5-5 15:55
設定名稱
MAX1 =MAX($A:$A)
MAX2 =LARGE($A:$A,1+COUNTIF($A:$A,">="&MAX1))
MAX3 =LARGE($A:$A,1+COUNTIF($A:$A,">="&MAX2))
A:A 設定格式化條件
=A1=MAX1 (條件一)
=A1=MAX2 (條件二)
=A1=MAX3 (條件三)
作者:
papaya
時間:
2018-5-7 07:04
本帖最後由 papaya 於 2018-5-7 07:07 編輯
回復
11#
ML089
不好意思，今早上論壇才看到。
簡單易解的公式，中間有空白格也能適用~很實用。
謝謝版主的不吝指導!感恩!
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
