Excel傳票摘要神抓關鍵字,INDEX與FIND函數綜合應用

目錄

Excel經常要組合許多不同的函數,運用巢狀公式解決實務難題。本文以總帳傳票摘要為例,綜合應用TRANSPOSE、INDEX、COLUMN、FIND、IF、IFERROR、SUM函數,新增報表分類欄位。

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

一、費用傳票摘要

如圖所示:「★A欄位有出現D欄位的通路名,將通路名稱回傳至B欄位」。

二、TRANSPOSE函數

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

三、INDEX函數

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

四、COLUMN函數

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

五、FIND函數

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

六、IF函數

「=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」,都是同樣道理。

七、SUM函數

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

八、神抓關鍵字

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

ERP系統與總帳傳票摘要應用

導入完整ERP的企業,大部份傳票是從前端的功能模組拋轉過來,例如應收、應付、固資、成本傳票等,然後有一小部是直接錄入的總帳傳票。通常拋轉傳票都可以設定自動生成摘要,把諸如客戶、供應商、設備、存貨的資料帶入摘要裡面,如此運用本章節所提到的方式,便可以從明細分類帳的摘要中,抓取所需要的資料。雖然ERP各模組能跑出很多系統報表,但還是不排除,哪天必須用到這個INDEX函數的情況!
本文內容取材自贊贊小屋線上課程,想成為Excel函數高手嗎?就是這堂課!

相關文章