? 發問時間: 電腦與網際網路軟體 · 9 年前

EXCEL︰次數前三高的號碼橫列公式和設定格式化條件公式。

參考下列問題的最佳解答後︰

http://tw.knowledge.yahoo.com/question/question?qi...

得I2=當A2︰G2次數最高的A1︰G1號碼由小而大橫式表列︰

=IF(COUNTIF($A2:$G2,MAX($A2:$G2))>=COLUMN(A:A),SMALL(IF($A2:$G2=MAX($A2:$G2),$A$1:$G$1),COLUMN(A:A)),"")

陣列公式~右拉下拉

…………………………………………………………………………………………………………

參考附表 A B C D E F G H I J K L M N O 1 01 02 03 04 05 06 07   前 三 高 次 數 號 碼 2 3 8 5 8 4 6 2   02 04 06 03       3 9 12 6 5 6 6 6   02 01 03 05 06 07   4 9 7 4 7 8 3 5   01 05 02 04       5 8 6 8 6 10 3 6   05 01 03 02 04 07   6 8 4 7 5 6 3 2   01 03 05         7                              

附表說明

第一列為標題

A2︰Gn為A1︰G1號碼的次數區

I2︰On為I2需求公式的解答區

需求︰

1.I2的公式條件為次數前三高的號碼由小而大橫式表列

2.I︰O欄次數前三高號碼儲存格的設定格式化條件公式

3.A︰G欄次數前三高儲存格的設定格式化條件公式

請問︰以上3項需求的函數公式? 謝謝!

5 個解答

評分
  • 9 年前
    最佳解答

    請參考~

    圖片參考:http://i280.photobucket.com/albums/kk189/vint5704/...

    I2

    =IF(SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))<=3,INDEX($A$1:$G$1,MATCH(LARGE(($A2:$G2+1/COLUMN($A2:$G2)),COLUMN(A$1)),($A2:$G2+1/COLUMN($A2:$G2)),)),"")

    陣列公式,右拉下拉

    I2:O2 格式

    1.紅色

    =SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))=1

    2.綠色

    =SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))=2

    3.藍色

    =SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))=3

    A2:G2格式

    1.紅色

    =SUMPRODUCT(($A2:$G2>=A2)/COUNTIF($A2:$G2,$A2:$G2))=1

    2.綠色

    =SUMPRODUCT(($A2:$G2>=A2)/COUNTIF($A2:$G2,$A2:$G2))=2

    3.藍色

    =SUMPRODUCT(($A2:$G2>=A2)/COUNTIF($A2:$G2,$A2:$G2))=3

    格式往下複製

    請參考附件

    http://www.funp.net/684601

    2011-09-15 17:45:43 補充:

    同准大疑問~

    A1~G1 是號碼還是順序 ?

  • 9 年前

    各位大大︰

    對不起!我沒說清楚!

    A1︰G1是號碼沒錯。

    Vincent知識長的公式可以用,

    只差同名次的號碼有二個(含)以上時,是依照欄位排序,

    我希望同名次的號碼有二個(含)以上時,能依照號碼的由小而大排序。

    不知道可不可以呢?謝謝您!

    2011-09-15 18:37:14 補充:

    准大︰

    謝謝您的指導。

    I2公式也是無法將同名次有二個(含)以上的號碼依照號碼的由小而大排序。

    格式設定用Vincent知識長的設定公式就OK了啊!

    2011-09-15 18:44:47 補充:

    准大︰

    014意見公式顯示值不正確!

    ^^

    2011-09-15 19:18:03 補充:

    准大︰

    謝謝您的耐心指導!

    016意見可以了!

    ^^

    請將016意見的公式移到

    http://tw.knowledge.yahoo.com/question/question?qi...

    謝謝您!

  • 9 年前

    貓大:

    A1 ~ G1 是〔號碼〕吧!可能是 10,12,19,20,25,26,33,

    而不是固定的 1 ~ 7!

    2011-09-15 18:09:10 補充:

    若 A1 ~ G1 是不定數的〔號碼〕,

    下面公式可將其依出現次數排列,但尚無法排除第四位以後的:

    =--RIGHT(LARGE(--((100+$A2:$G2)&((8-COLUMN($A2:$G2))*100+$A$1:$G$1)),COLUMN()-8),2)

    但配上〔格式化條件〕,在視覺上還是可輕易分辨!

    另 A1 ~ G1 的第一個〔格式化條件〕公式,

    第一條件:=(A2>0)*(A2=MAX($A2:$G2)) 即可,

    第二條件:

    =(A2>0)*(A2=LARGE($A2:$G2,COUNTIF($A2:$G2,MAX($A2:$G2))+1))

    第三條件:超長,不現醜了!

    2011-09-15 18:36:30 補充:

    試試:

    =--RIGHT(LARGE(--((100+$A2:$G2)&(100-$A$1:$G$1)&$A$1:$G$1),COLUMN()-8),2)

    第四順位後的排除,可加上Vincent 大 公式的前段。

    2011-09-15 18:40:29 補充:

    =IF(SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))<=3,--RIGHT(LARGE(--((100+$A2:$G2)&(100-$A$1:$G$1)&$A$1:$G$1),COLUMN()-8),2),"")

    陣列輸入

    2011-09-15 18:48:55 補充:

    =IF(SUMPRODUCT(($A2:$G2>=LARGE($A2:$G2,COLUMN(A$1)))/COUNTIF($A2:$G2,$A2:$G2))<=3,--RIGHT(LARGE(--((100+$A2:$G2)&((100-$A$1:$G$1)*100+$A$1:$G$1)),COLUMN()-8),2),"")

    陣列

    抱歉,測試號都用2位數,而忽略了個位數,再試試!

  • 顯栓
    Lv 7
    9 年前

    I2=IF(COUNTIF($A2:$G2,">="&LARGE(IF(MATCH($A2:$G2,$A2:$G2,)=$A$1:$G$1,$A2:$G2),3))>=COLUMN(A:A),(1-MOD(LARGE($A2:$G2-$A$1:$G$1/10,A$1),1))*10,"")

    陣列

  • 您覺得這個回答如何?您可以登入為回答投票。
  • piny
    Lv 7
    9 年前

    I2=IF(COLUMN(A1)>SUM(N($A2:$G2>=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),3))),"",-MOD(LARGE(IF($A2:$G2>=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),3),$A2:$G2*100-COLUMN($A:$G)),COLUMN(A1)),-100))

    陣列 右拉下拉

    2011-09-15 12:43:16 補充:

    第二問 格式化條件

    I2=INDEX(2:2,I2)=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),1) A顏色

    I2=INDEX(2:2,I2)=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),2) B顏色

    I2=INDEX(2:2,I2)=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),3) C顏色

    2011-09-15 12:46:04 補充:

    第三問 格式化條件

    A2=A2=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),1) A顏色

    A2=A2=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),2) B顏色

    A2=A2=LARGE(IF(FREQUENCY($A2:$G2,$A2:$G2),TRANSPOSE($A2:$G2)),3) C顏色

    2011-09-15 13:42:29 補充:

    哈 想複雜了 ><...

    2011-09-15 16:58:47 補充:

    A大的思維讚 可略簡10字元 COLUMN(A:A)就不動了

    =IF(COLUMN(A:A)>COUNTIF($A2:$G2,">"&LARGE(IF(MATCH($A2:$G2,2:2,)=$A$1:$G$1,$A2:$G2),4)),"",-MOD(LARGE($A2:$G2-$A$1:$G$1%,A$1),-1)/1%)

    2011-09-15 18:04:17 補充:

    咦 有道理 先前想簡單了 >

還有問題?馬上發問,尋求解答。