Excel如何以Index函數查找傳票摘要




實務上有個案例,有一連串傳票摘要的資料,然後是一組客戶名稱,想要從摘要裡,把有包含的客戶名稱抓出來。還有另外一個類似案例,手上只有明細分類帳,需要把一張張傳票,依照某種方式分類彙總,系統並沒有合適報表,像這些狀況,可以考慮以下方式操作:

一、如圖所示,讀者案例:「★A欄位有出現D欄位的客戶名,將客戶名回傳至B欄位」。

A欄位有出現D欄位的客戶名,將客戶名回傳至B欄位

二、首先,我需要把「比對」那一欄垂直排列的客戶名稱,轉換成水平排列,第一個想到的是「TRANSPOSE」函數,客戶有六個,所以先選取「C1」到「H1」一列六個儲存格,直接輸入「=TRANSPOSE(A2:A7)」,由於是陣列資料,公式輸完必須在公式欄先按住「Ctrl」及「Shift」,再按下「Enter」,結果如圖所示。

=TRANSPOSE(A2:A7)

三、「TRANSPOSE」有個缺點,要先看垂直有多少家客戶,然後選擇多少個水平儲存格範圍,這個工作量,如果是十家以內還好,但是,一般正常公司,少說也有幾十家客戶,用「TRANSPOSE」陣列操作起來,非常麻煩。依照Excel慣用手法,最好是有個函數能一直拉下去的,例如:「INDEX」!

依照Excel慣用手法,最好是有個函數能一直拉下去的,例如:「INDEX」

四、輸入公式:「=INDEX($A:$A,COLUMN(A1)+1)」,INDEX函數是像座標軸般,超連結引用其它儲存格的內容,例如這裡,我引用範圍是整個A欄,一開始「C1」我引用的是「A1」欄數(1)再加1,也就是第二列,A欄第二列:「大樂」。將公式往右拉,便把垂直的客戶名稱序列,水平往右拉下去。

=INDEX($A:$A,COLUMN(A1)+1)

五、接下來介紹「FIND」函數:「=FIND(C$1,$B2)」,意思是要在「B2」裡找出「C1」的起始位置,在「C2」這裡是找不到,所以顯示「#VALUE!」,不過公式拉到了「E2」,在「B2」字串第五個字元開始,正是「大潤發」,所以顯示結果是「5」,如此,應能理解「FIND」函數的妙用了吧。

=FIND(C$1,$B2)

六、「=IF(IFERROR(FIND(C$1,$B2),0)>0,COLUMN(C2)-2,0)」,Excel翻譯糕:摘要裡如果找不到客戶名稱,便顯示「0」,找得到(起始字元>0)便顯回本身儲存格欄數減2,「C2」(欄數3)減2是1、「D2」(欄數4)滅2是2,因為「C2」和「D2」摘要裡沒有相對應「C1」和「D1」的客戶,所以顯示「0」,到了「E2」,摘要裡有客戶,因此顯示「3」,「G4」顯示「5」、「H3」顯示「6」,其餘儲存格顯示「0」,都是同樣道理。

=IF(IFERROR(FIND(C$1,$B2),0)>0,COLUMN(C2)-2,0)

七、仔細想想上一步驟造成的結果,每一列摘要如有出現客戶名稱,那一列客戶所在欄位,便會顯示客戶序號,例如「E2」的「3」。依照如此規則,再下最後一個公式:「=INDEX($F$1:$K$1,SUM(F2:K2))」,可在函數視窗裡參考說明。

=INDEX($F$1:$K$1,SUM(F2:K2))

八、呼,好了,經過這麼一個實例,應該徹底瞭解「INDEX」函數了吧,妙用無窮呢!

經過這麼一個實例,應該徹底瞭解「INDEX」函數了吧




當前文章延伸閱讀:
經典函數