[分享] 簡單技巧分享-關鍵字輸入

本帖最後由 a8350070 於 2011-6-26 10:30 編輯

小弟野人獻曝一下, 這是緣自2009年底回答網友提問時發現的小技巧

說明：
在資料紀錄建立的時候，我們常使用VLOOKUP等函數來自動帶出相關資料以提昇輸入效率，
例如輸入客戶編號就能以函數自動帶出客戶名稱、地址、電話等基本資料，
不過我們不可能都記住客戶的編號，而查詢客戶的編號免不了又得費一番工夫，
我覺得比較理想的作法是：先輸入客戶名稱，再用VLOOKUP等函數來自動帶出相關資料，
但客戶名稱有時很冗長，而且我們往往只記得大約的名稱，無法記得正確全名，
這時候如果有一項功能可以像YAHOO搜尋時，只輸入關鍵字詞就能自動列出清單讓我們選取,那就方便許多了，

例如有二個工作表：「基本資料」、「銷售記錄輸入」
在「基本資料!B欄」我們存放了客戶名稱
　　A　　　B　　       　C　　      　D　　　E　　
１客戶編號客戶名稱
２         一心股份有限公司
３    一意股份有限公司
４    十全股份有限公司
５    十美股份有限公司
６    一蘭股份有限公司
７    十分股份有限公司

在「銷售記錄輸入!B欄」輸入時我們希望只要輸入「十」之後就能自動列出十全、十美、十分等清單讓我們選取
　　A　　　B　　    　    C　　      　D　　    　    E　　
１銷售編號客戶名稱
２
３

執行步驟：
１．新置第三個工作表作為輔助，名稱設為「篩選」
未命名01.jpg

２．將以下公式貼到「篩選!A1」儲存格 (可返回「銷售記錄輸入!B欄」的最末筆資料，作為欲篩選的關鍵字)
=LOOKUP(REPT("龤",255),銷售記錄輸入!B:B)

３．將以下公式貼到「篩選!B1」儲存格後不要直接按 Enter ，而是同時按 Ctrl + Shift + Enter 三鍵，使成為陣列公式
=IF(SUM(--ISNUMBER(SEARCH(A$1,基本資料!B$1:B$100)))<ROW(),"",INDEX(基本資料!B:B,SMALL(IF(ISNUMBER(SEARCH(A$1,基本資料!$B$1:$B$100)),ROW($A$1:$B$100)),ROW())))

４．點選 B1儲存格後將游標移至其右下角，俟游標變成黑色十字型後再下拉(複製填滿)
　　B欄公式可將「基本資料!B欄」中含有A1字串者均篩選到本欄中

５．選取下列公式後按Ctrl + C (複製)
　　=OFFSET(INDIRECT("篩選!B1"),,,SUMPRODUCT(--(INDIRECT("篩選!B1:B100")<>"")))

６．回到「銷售記錄輸入」工作表，選取整個B欄

７．資料 e 驗證 e 跳出對話方塊

８．設定頁籤中 e 儲存格內允許清單 & 在來源中按 Ctrl + V (貼上)

９．錯誤提醒頁籤中 e 取消勾選輸入的資料不正確時顯示警訊

１０．按確定結束對話方塊

１１．在B欄輸入「十」後再點選儲存格右方的下拉箭頭即可選取
關鍵字輸入範例-4.JPG

ＰＳ１．公式中$B$1:$B$100以及ROW($1:$100)的100是假設客戶資料在100筆以內，如果不夠使用請自行修改

ＰＳ２．此方法乃無巨集達成之簡單作法，自然有一些假設及限制，主要是使用者於銷售「記錄輸入!B欄」輸入時
　　　　必需由上而下依序輸入，因為步驟２的公式是返回「銷售記錄輸入!B欄」的最末筆資料，作為欲篩選的
　　　　關鍵字，也就是說如果銷售記錄輸入!B3儲存格有資料時，想回頭輸入B2儲存格時就會有問題，因為這
　　　　裡會以B3(最末筆資料)作為關鍵字作為篩選的依據，若想改善這問題就需利用巨集事件程序來達成。

ＰＳ３．還有一項限制是：在未輸入關鍵字前並無法顯示全部清單來點選輸入，解決方式為：將步驟5的公式改為
　　　　=IF(COUNTIF(INDIRECT("基本資料!B:B"),LOOKUP(REPT("龤",255),INDIRECT("銷售記錄輸入!B:B"))),OFFSET(INDIRECT("基本資料!B2"),,,SUMPRODUCT(--(INDIRECT("基本資料!B1:B100")<>""))-1),OFFSET(INDIRECT("篩選!B1"),,,SUMPRODUCT(--(INDIRECT("篩選!B1:B100")<>""))))　　　　　　　　
　　　　但這樣還是使用了一項假設，就是欲輸入的儲存格的前一個儲存格必需是完整輸入後的狀態，也就是說
　　　　在B2輸入「十」然後列出十全、十美、十分等清單時，必需選一個來輸入，不能只保留「十」在B2，
　　　　否則在B3輸入前還是會以十為關鍵字作篩選，而不是顯示全部清單；這是儘量不使用巨集來達成的作
　　　　法，如果想破除這些限制還是得靠巨集來達成才行。

邏輯解析
　　首先需取得使用者已輸入的關鍵字，假設使用者於「銷售記錄輸入!B欄」輸入時是由上而下依序輸入，所以
使用者正在輸入的關鍵字就是「銷售記錄輸入!B欄」的最末筆資料，而步驟２的公式可於「篩選!A1」儲存格中
返回「銷售記錄輸入!B欄」的最末筆資料，作為欲篩選的關鍵字。
　　接下來必需將「基本資料!B欄」中含有A1字串者均篩選出來，步驟３～４正可將「基本資料!B欄」中符合
關鍵字條件的資料列示在「篩選!B欄」中。
　　最後則是將篩選後的資料置入正在輸入的儲存格驗證清單中，達成方法即步驟５～１０。

運用巨集來改善必需由上而下依序輸入及未輸入關鍵字前無法顯示全部清單之方式：

A．不要執行上述步驟２ (要執行也沒關係，只不過會多此一舉)

B．將步驟５的公式改為下述，並重新執行上述的步驟５～１0
　　　　=IF(INDIRECT("篩選!A1")="",OFFSET(INDIRECT("基本資料!B2"),,,SUMPRODUCT(--(INDIRECT("基本資料!B1:B100")<>""))-1),OFFSET(INDIRECT("篩選!B1"),,,SUMPRODUCT(--(INDIRECT("篩選!B1:B100")<>""))))

C．選取並複製下列程式碼
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 And Selection.Cells.Count = 1 Then '此處 Column=2 的 2 是假設在B欄輸入,若輸入區為C欄請改=3....依此類推
Sheets("篩選").Range("A1").Formula = "=" & ActiveSheet.Name & "!" & Target.Address & "&"""""
End If
End Sub

D．在「銷售記錄輸入」工作表的標籤上按右鍵，並點選「檢視程式碼」