麻辣家族討論版版 - Powered by Discuz! Board

標題: [分享] 簡單技巧分享-關鍵字輸入 [打印本頁]

作者: a8350070 時間: 2011-5-14 23:18 標題: 簡單技巧分享-關鍵字輸入

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

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

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

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

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

執行步驟：
１．新置第三個工作表作為輔助，名稱設為「篩選」
[attach]6808[/attach]

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

３．將以下公式貼到「篩選!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字串者均篩選到本欄中
[attach]6810[/attach]
[attach]6811[/attach]

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

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

７．資料 e 驗證 e 跳出對話方塊
[attach]6185[/attach]

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

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

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

１１．在B欄輸入「十」後再點選儲存格右方的下拉箭頭即可選取
[attach]6189[/attach]

ＰＳ１．公式中$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．在「銷售記錄輸入」工作表的標籤上按右鍵，並點選「檢視程式碼」
[attach]6812[/attach]

E．出現Visual Basic視窗後在空白處貼上程式碼，並關閉Visual Basic視窗
[attach]6813[/attach]

F．如果巨集安全性設為高者請執行工具 e 巨集 e 安全性來調整成中或低，並重新開啟檔案
[attach]6814[/attach]
[attach]6815[/attach]

[attach]6190[/attach]

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

作者: sang520 時間: 2011-6-17 20:35

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

作者: liu5201 時間: 2011-6-18 13:20

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

作者: Qin 時間: 2011-6-18 20:23 標題: RE: 簡單技巧分享-關鍵字輸入-疑問?

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

作者: a8350070 時間: 2011-6-19 22:38

提問: 對於新使用者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

作者: liu5201 時間: 2011-6-22 11:36

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

作者: FAlonso 時間: 2011-6-24 09:53

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

還有第5點是有什麼用? 完全不明白

作者: mark15jill 時間: 2011-6-24 10:50

回復 7# FAlonso

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

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

作者: FAlonso 時間: 2011-6-24 11:05

回復 8# mark15jill
我是想了解那段程式的含意,不太理解

作者: mark15jill 時間: 2011-6-24 11:45

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

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

作者: a8350070 時間: 2011-6-26 00:48

感謝樓主a8350070 大大之回答,依您方式修正,在'銷售記錄輸入!B3輸入'十' 即點選儲存格右方之下拉式箭頭-->結果下拉清單以'十' 開頭之清單-->OK
但繼續再試試於 B4 未輸入即點選儲存格右方之下拉式箭頭-->結果下拉清單同B3 (以'十' 開頭之清單 -->無法顯示全部清單 ???
另外,自己想知道,篩選字元可否改成2個或多個?...
liu5201 發表於 2011-6-22 11:36

關於修正公式的問題:
這樣還是使用了一項假設，就是欲輸入的儲存格的前一個儲存格必需是完整輸入後的狀態，也就是說
在B2輸入「十」然後列出十全、十美、十分等清單時，必需選一個來輸入，不能只保留「十」在B2，
否則在B3輸入前時還是會以十為關鍵字作篩選，而不是顯示全部清單；這是儘量不使用巨集來達成的作
法，如果想破除這些限制還是得靠巨集來達成才行(使用巨集的解決方案小弟已於1樓帖子中編輯說明)。

篩選字元是不限定字數的，您可以在基本資料中置入這些比較類似的名稱測試看看

作者: a8350070 時間: 2011-6-26 00:58

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

還有第5點 ...
FAlonso 發表於 2011-6-24 09:53

步驟3的公式正是將「基本資料!B欄」中符合關鍵字條件的資料列示在「篩選!B欄」中。

步驟5的公式目的在於取得篩選後列示在「篩選!B欄」中的資料範圍，但是因為驗證清單並不能直接參照自跨工作表的資料來源，所以用INDIRECT函數將篩選!B1 等參照來源包裝起來，這樣就能成功參照了

作者: ANGELA 時間: 2011-6-28 10:28

回復 12# a8350070

用定義名稱的方式即可參照到其他工作表了,還有如果在沒輸入關鍵字時能篩選出全部清單,輸入特定字後篩選出特定字的公司名稱會更理想.

作者: a8350070 時間: 2011-6-28 21:49

回復 13# ANGELA

用INDIRECT等函數或定義名稱的方式均可使驗證清單參照到其他工作表,為方便
步驟說明之故,採用INDIRECT函數的方式

有關 "沒輸入關鍵字時能篩選出全部清單,輸入特定字後篩選出特定字" 這點,
小弟日前已於1樓帖子中的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輸入前還是會以十為關鍵字作篩選，而不是顯示全部清單；這是儘量不使用巨集來達成的作
法，如果想破除這些限制還是得靠巨集來達成才行。

至於運用巨集來改善必需由上而下依序輸入及未輸入關鍵字前無法顯示全部清單之方式,
日前亦已於1樓帖子中的補充步驟A~F作編輯說明

使用巨集的範例檔
[attach]6858[/attach]

作者: ANGELA 時間: 2011-6-28 23:02

回復 14# a8350070

GOOD JOB.

作者: yc1031 時間: 2012-4-23 14:14

回復 1# a8350070

我弄您教的方法, 中文字的話有成功, 但英文的話, 就無法完成。
請問有適用英文字的關鍵字輸入嗎? 感謝!

作者: a8350070 時間: 2012-4-29 22:35

回復 16# yc1031

本技巧應該中英文都能適用喔

而且在步驟3的公式中
=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())))
使用了SEARCH函數,能不區分英文大小寫均納入篩選範圍內
如欲區分大小寫,則需將SEARCH函數改成FIND函數

作者: xandertco 時間: 2012-8-16 21:32

真的是很厲害的技巧

作者: fangsc 時間: 2012-10-3 01:16

資料驗證, 顯示"輸入的值不正確".
不知是哪裡錯了. 麻煩高手們看一下並指正.
感恩!!

作者: ANGELA 時間: 2012-10-3 13:56

本帖最後由 ANGELA 於 2012-10-3 13:59 編輯

兩個問題
1資料驗證的錯誤提醒要關掉。
2篩選b1=IF(SUM(--ISNUMBER(SEARCH(A$1,Data!C$1:C$100)))<ROW(),"",INDEX(Data!C:C,SMALL(IF(ISNUMBER(SEARCH(A$1,Data!$C$1:$C$100)),ROW($A$1:$B$100)),ROW())))
範圍要一致
最好建立唯一值的客戶清單

作者: fangsc 時間: 2012-10-4 00:10

回復 20# ANGELA

了解了, 感謝指導.

作者: dakiu 時間: 2012-10-5 09:46

收藏了..雖然看不太懂....但工作上真的很適合....:)

作者: stillfish00 時間: 2012-10-8 01:55

收藏了~真的是很特別的用法
慢慢消化中

作者: e19821223 時間: 2012-11-8 11:56

我有嘗試過這公式坦白說非常好用
但.所設定的搜尋範圍不能太大
不然電腦光是要找就花費了很長一段時間會很lag

作者: kuoliang123 時間: 2012-11-10 18:52

看不懂,研究看看~~~~~

作者: playegg 時間: 2015-1-1 15:39

太厲害了,原來還可以這樣用,來試試看,應該方便很多~感謝

作者: Leo1318 時間: 2015-6-30 23:57

回復 20# ANGELA

當資料愈來愈多時，是否用定義名稱來做，運行上會比較快速些？

作者: ANGELA 時間: 2015-7-1 18:19

資料量大時，（尤其是陣列公式），用ＶＢＡ會比較理想。定義名稱有幫助，但不大。

作者: yiochang 時間: 2016-1-8 13:15

非常實用的技巧，我依照說明複製了一個一模一樣的可以使用，但是在我更動基本資料裡面客戶名稱的位置後 (從B改到C)之後就不能用了，請問如果我要改基本資料的客戶位置從B改到C有那些地方要更動嗎?

作者: shane0514 時間: 2016-1-26 09:17

請問如果我篩選的部分有重複的名稱，下拉式選單中怎麼去掉重複的項目?
先謝謝回答了

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)