Excel搜尋關鍵字回傳:1組公式幫你抓取特定文字

Excel搜尋關鍵字回傳是工作上很常有的需求,尤其在複雜的報表資料分析中發揮很大的作用,本文介紹如何設計1組公式,結合許多強大函數完成這項任務。

職場實務案例

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

步驟操作教學

Step 1 尋找多個目標

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

Step 1 尋找多個目標

Step 2 關鍵字函數整理

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

Step 2 關鍵字函數整理

Step 3 INDEX公式怎麼用

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

Step 3 INDEX公式怎麼用

Step 4 COLUMN函數

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

Step 4 COLUMN函數

Step 5 FIND函數抓取

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

Step 5 FIND函數抓取

Step 6 尋找關鍵字位置

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

Step 6 尋找關鍵字位置

Step 7 搜尋文字回傳

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

Step 7 搜尋文字回傳

Step 8 Excel搜尋關鍵字回傳

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

Excel搜尋關鍵字回傳:1組公式幫你抓取特定文字

如何有效整理ERP報表

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

微軟關於INDEX函數的說明。

想要學習更多Excel技巧嗎?歡迎前往贊贊小屋Excel教學中心。

Excel教學課程:一次學會Excel所有必須技能

最新文章: