Excel 函數 排序 INDEX RIGHT

Dear 大大:

請幫我於最後下表設函數找出其同仁A客戶第一大及第二大客戶為何?C客戶第一,二,三客戶為何?以此類推,謝謝!

姓名 分級 客戶名 業績 王小明 A 客戶一 281,247 王小明 A 客戶二 208,746 王小明 B 客戶三 171,928 李小平 A 客戶四 38,911 李小平 D 客戶五 7,756 李小平 D 客戶六 -1,720 陳大千 A 客戶七 1,459,564 陳大千 A 客戶八 2,262,031 陳大千 D 客戶九 39,223 陳大千 C 客戶十 29,136 陳大千 B 客戶十一 3476

Sales  A級客戶*2B級客戶*2C級客戶*3D級客戶*4 王小明 客戶名                業績                李小平 客戶名                業績                陳大千 客戶名                業績               

2 Answers

Rating
  • KK
    Lv 7
    9 years ago
    Favorite Answer

    1. 資料畫面

    圖片參考:http://imgcld.yimg.com/8/n/AF02741245/o/1012021504...

    2.公式說明

    H3 =INDEX($C:$C,RIGHT(LARGE(($F3=$A$3:$A$13)*(LEFT(LOOKUP("嫻",$H$2:H$2))=$B$3:$B$13)*(RANK($D$3:$D$13,$D$3:$D$13,1)*10^4+ROW($D$3:$D$13)),COLUMN()-LOOKUP("嫻",$H$2:H$2,COLUMN($H$2:H$2))+1),4))&""

    陣列輸入(先按CTRL SHIT 兩鍵不放,再按ENTER一起輸入)

    H4 =IF(H3="","",VLOOKUP(H3,$C:$D,2,))

    一般ENTER輸入

    上述兩公式複製到同顏色區域

    複製方式

    選擇 H3:H4 複製到 H5:H8

    選擇 H3:H8 複製到 I3:U8 PS:為了簡化公式,使用本公式須將第一列空白,有美觀因素時可以隱藏起來。

    因為RANK函數的關係資料不能用擴充範圍,因此資料增減時均須修改公式的資料範圍

    資料的範圍建議以名稱變數來替代並設定動態範圍,名稱與動態範圍公式如下。

    分級 =OFFSET(Sheet1!$B$3,,,COUNTA(Sheet1!$A:$A)-1)

    姓名 =OFFSET(Sheet1!$A$3,,,COUNTA(Sheet1!$A:$A)-1)

    業績 =OFFSET(Sheet1!$D$3,,,COUNTA(Sheet1!$A:$A)-1)

    H3公式修改如下

    =INDEX($C:$C,RIGHT(LARGE(($F3=姓名)*(LEFT(LOOKUP("嫻",$H$2:H$2))=分級)*(RANK(業績,業績,1)*10^4+ROW(業績)),COLUMN()-LOOKUP("嫻",$H$2:H$2,COLUMN($H$2:H$2))+1),4))&""

    陣列輸入(先按CTRL SHIT 兩鍵不放,再按ENTER一起輸入)

    3.範例檔案

    http://www.funp.net/835937

    2012-02-15 21:56:35 補充:

    技巧1 H12 =LEFT(LOOKUP("嫻",$H$2:H$2))

    技巧2 H13 =COLUMN()-LOOKUP("嫻",$H$2:H$2,COLUMN($H$2:H$2))+1

  • Daniel
    Lv 7
    9 years ago

    COLUMN()-LOOKUP("嫻",$H$2:H$2,COLUMN($H$2:H$2))+1

    可改為:

    COLUMN()-MATCH("嫻",$A$2:H$2)+1

    2012-02-16 00:47:00 補充:

    KK大將第一行隱藏,但如第一行刪除,即會出錯

    可先將第一行刪除,第3行即成為第2行,此時H2公式可改為:

    =INDEX($C:$C,RIGHT(LARGE(IF(($F2=姓名)*(LEFT(LOOKUP("嫻",$H$1:H$1))=分級),RANK(業績,業績,1)*10^4+ROW(業績),9999),COLUMN()-MATCH("嫻",$A$1:H$1)+1),4))&""

Still have questions? Get your answers by asking now.