返回列表 上一主題 發帖

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

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

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

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

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

例如有二個工作表 :「基本資料」、「銷售記錄輸入」                                       
在「基本資料!B欄」我們存放了客戶名稱                                       
                  A            B                 C                D            E  
        1        客戶編號        客戶名稱                       
        2                    一心股份有限公司                       
        3                   一意股份有限公司                       
        4                   十全股份有限公司                       
        5                   十美股份有限公司                       
        6                   一蘭股份有限公司                       
        7                   十分股份有限公司                       

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

執行步驟:                               
1.新置第三個工作表作為輔助,名稱設為「篩選 」                                       
未命名01.jpg
2011-6-26 00:07


2.將以下公式貼到「篩選!A1」儲存格   (可返回「銷售記錄輸入!B欄 」的最末筆資料,作為欲篩選的關鍵字)
      =LOOKUP(REPT("龤",255),銷售記錄輸入!B:B)                                       
未命名02.jpg
2011-6-26 00:07

                                               
3.將以下公式貼到「篩選!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())))

4.點選 B1儲存格後將游標移至其右下角,俟游標變成黑色十字型後再下拉(複製填滿)
  B欄公式可將「基本資料!B欄」中含有A1字串者均篩選到本欄中
未命名04.jpg
2011-6-26 00:07

未命名05.jpg
2011-6-26 00:07

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

6.回到「銷售記錄輸入」工作表,選取整個B欄

7.資料 e 驗證 e 跳出對話方塊
關鍵字輸入範例-1.JPG
2011-5-14 23:10


8.設定頁籤中 e 儲存格內允許 清單 & 在來源中按 Ctrl + V (貼上)
關鍵字輸入範例-2.JPG
2011-5-14 23:11


9.錯誤提醒頁籤中 e 取消勾選 輸入的資料不正確時顯示警訊
關鍵字輸入範例-3.JPG
2011-5-14 23:11


10.按確定結束對話方塊

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


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

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

PS3.還有一項限制是:在未輸入關鍵字前並無法顯示全部清單來點選輸入,解決方式為:將步驟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欄 」的最末筆資料,而步驟2的公式可於「篩選!A1」儲存格中
返回「銷售記錄輸入!B欄 」的最末筆資料,作為欲篩選的關鍵字。
  接下來必需將「基本資料!B欄」中含有A1字串者均篩選出來,步驟3∼4正可將「基本資料!B欄」中符合
關鍵字條件的資料列示在「篩選!B欄」中。
  最後則是將篩選後的資料置入正在輸入的儲存格驗證清單中,達成方法即步驟5∼10。

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

A.不要執行上述步驟2 (要執行也沒關係,只不過會多此一舉)

B.將步驟5的公式改為下述,並重新執行上述的步驟5∼10
    =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.在「銷售記錄輸入」工作表的標籤上按右鍵,並點選「檢視程式碼」
關鍵字輸入範例-5.JPG
2011-6-26 00:07


E.出現Visual Basic視窗後在空白處貼上程式碼,並關閉Visual Basic視窗
關鍵字輸入範例-6.JPG
2011-6-26 00:07


F.如果巨集安全性設為高者請執行 工具 e 巨集 e 安全性 來調整成中或低,並重新開啟檔案
關鍵字輸入範例-7.JPG
2011-6-26 00:07

關鍵字輸入範例-8.JPG
2011-6-26 00:07


關鍵字輸入範例.zip (106 KB)

關於無法下載範例檔的朋友們,請到小弟的部落格下載
http://blog.xuite.net/a8350070
1

評分人數

    • ANGELA: 謝謝樓主的用心及分想金錢 + 2

超厲害的!完全沒想過可以這樣用,最多雙條件驗證就覺得很棒了!這位分享者真的很厲害,讚!

TOP

感謝a8350070 大大之分享,這對日常作業需使用但客戶眾多(如300個客戶)輸入+篩選清單變成很方便
但對於新使用者key-in連客戶名皆不知道時,可否顯示全部清單來點選輸入,不知各位大大有可改善方式嗎?

TOP

RE: 簡單技巧分享-關鍵字輸入-疑問?

拷貝了1樓的所給的公式,自己試做了一下,卻不成功...
在銷售記錄輸入時,卻找不到我希望的,只要輸入「十」之後就能自動列出十全、十美、十分等清單讓我選取 ..
懇請各位大大賜教...

JianDanJiQiao-TEXT.zip (3.94 KB)

TOP

提問:  對於新使用者key-in連客戶名皆不知道時,可否顯示全部清單來點選輸入?

解決方式為: 將步驟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")<>""))))

關於無法下載範例檔的朋友們,請到小弟的部落格下載
http://blog.xuite.net/a8350070
My Blog
http://blog.xuite.net/a8350070

TOP

感謝樓主a8350070 大大之回答,依您方式修正,在'銷售記錄輸入!B3輸入'十' 即點選儲存格右方之下拉式箭頭-->結果下拉清單以'十' 開頭之清單-->OK
但繼續再試試於 B4 未輸入即點選儲存格右方之下拉式箭頭-->結果下拉清單同[color=Red]B3 (以'十' 開頭之清單 -->無法顯示全部清單 ???
另外,自己想知道,篩選字元可否改成2個或多個?例如:在 B4 輸入「十分」之後再點選儲存格右方之下拉式箭頭出現清單有:
十分股份有限公司
十分工業股份有限公司
十分科技股份有限公司
十分有限公司
十分食品股份有限公司
以上問題,不知各位大大有可改善方式嗎?

test-01.rar (98.98 KB)

TOP

有點技術問題,第3點是否在"基本資料"的客戶名稱群當中,找出符合'銷售記錄輸入'公司名稱的程式?

還有第5點是有什麼用? 完全不明白
80 字節以內
不支持自定義 Discuz! 代碼

TOP

回復 7# FAlonso


    第五點的話 應該是篩選 B欄相同的客戶字串


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

TOP

回復 8# mark15jill
我是想了解那段程式的含意,不太理解
80 字節以內
不支持自定義 Discuz! 代碼

TOP

回復  mark15jill
我是想了解那段程式的含意,不太理解
FAlonso 發表於 2011-6-24 11:05



    以 B1 的儲存格為標題
在 B2 ~ B100 篩選  非空字串的資料 (個人解讀

TOP

        靜思自在 : 我們要做好社會的環保,也要做好內心的環保。
返回列表 上一主題