Excel 函數 排序 INDEX RIGHT

Question

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 王小明 客戶名 　　　　　　　　　　　　　　 業績 　　　　　　　　　　　　　　 李小平 客戶名 　　　　　　　　　　　　　　 業績 　　　　　　　　　　　　　　 陳大千 客戶名 　　　　　　　　　　　　　　 業績 　　　　　　　　　　　　　　

Answer 1

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

Answer 2

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))&""