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

標題: [發問] 請教VLOOKUP中被查詢範圍如何加入選字數函數 [打印本頁]

作者: 97forum 時間: 2013-5-13 10:48 標題: 請教VLOOKUP中被查詢範圍如何加入選字數函數

請教各位先進：
我在excel中如果想使用vlookup查詢，其說明如下:
查詢內容(A1)                      被查詢範圍內容(C1:D4)
大甲有限公司                      大甲企業有限公司                            39
                                             大甲股份有限公司                            55
                                             大甲科技有限公司                            24
                                             大甲有限公司高雄分公司                33

如上述，如果直接下函數如下
=VLOOKUP(A1,$C$1:$D$4,2,FLASE) 這個結果應該是 FLASE

但是我希望函數能幫我找出大甲有線公司高雄分公司之資料，那函數如果用下列方式表示，有錯誤
=VLOOKUP(LEFT(A1,6),LEFT($C$1:$D$4,6),2,FLASE)

不知是否先進可以幫忙解惑？函數該如何下？如果被查詢範圍是個表格方式那會有影響嗎？

作者: p212 時間: 2013-5-13 11:57

本帖最後由 p212 於 2013-5-13 11:58 編輯

回復 1# 97forum
若將=VLOOKUP(LEFT(A1,6),LEFT($C$1:$D$4,6),2,FLASE)
改成=VLOOKUP(A1,LEFT($C$1:$D$4,6),2,FLASE) 再按Ctrl+Shift+Enter
這結果是否符合您的需求?

作者: 97forum 時間: 2013-5-13 12:34

回復 2# p212

謝謝您的回覆，其結果如下：
#NAME?

作者: Bodhidharma 時間: 2013-5-13 12:46

回復 1# 97forum

用left太限縮了，可考慮使用search函數
陣列公式

=INDEX($D$1:$D$4,MATCH(1,SEARCH($A$1,$C$1:$C$4),0))

複製代碼

會回傳D欄中，第一個函有A1的值
如果search出來符合的值不只一個(比方說A1="大甲")

=INDEX($C$1:$C$4,SMALL(IF(ISNUMBER(SEARCH($A$1,$C$1:$C$4)),ROW($C$1:$C$4),""),ROW(A1)))
=INDEX($D$1:$D$4,SMALL(IF(ISNUMBER(SEARCH($A$1,$C$1:$C$4)),ROW($C$1:$C$4),""),ROW(A1)))

複製代碼

下拉，就會顯示所有符合的公司名稱以及值

作者: Bodhidharma 時間: 2013-5-13 12:47

回復 3# 97forum

FLASE 改成FALSE試試

作者: p212 時間: 2013-5-13 13:48

本帖最後由 p212 於 2013-5-13 13:51 編輯

回復 3# 97forum
應該不會錯誤，請再試試！（上列#2公列copy自問題修改而得）
=VLOOKUP(A1,LEFT($C$1:$D$4,6),2,FALSE) 再按Ctrl+Shift+Enter
=VLOOKUP(A1,LEFT($C$1:$D$4,6),2,0) 再按Ctrl+Shift+Enter
建議使用0，1分別表示FALSE，TRUE 可避免拼字錯誤

作者: 97forum 時間: 2013-5-13 14:05

回復 97forum
若將=VLOOKUP(LEFT(A1,6),LEFT($C$1D$4,6),2,FLASE)
改成=VLOOKUP(A1,LEFT($C$1D$4,6 ...
p212 發表於 2013-5-13 11:57

抱歉，原來回復測試結果錯誤 >> #name?
是我自己錯誤將false 打成 flase
您的函數是可以的，但是如果是在多筆之下要如何處理？
我原來對應的被查詢範圍是採用表格方式(取得外部SQL資料表)
其在被查詢範圍內，他是將SQL的資料表名稱帶入範圍內，那如果使用您的函數下，又要如何處理？
附帶說明查詢值本身工作表就是表格方式

回復 97forum

用left太限縮了，可考慮使用search函數
陣列公式會回傳D欄中，第一個函有A1的值
如果s ...
Bodhidharma 發表於 2013-5-13 12:46

Bodhidharma 前輩，您的方式在我目前的表格中不能使用。
請參閱上方說明。

由於檔案複雜，恕小弟無法上傳說明。所以小弟只能舉上面例子方式解決，欲查詢出來的欄位非數值型態，是文字型態

作者: Bodhidharma 時間: 2013-5-13 15:41

回復 7# 97forum

表格也會有表格的名稱可以使用，應該不會無法使用我的公式
請參考看看以下檔案
[attach]14960[/attach]

作者: p212 時間: 2013-5-13 15:43

回復 7# 97forum
可否上傳檔案供參？大夥兒一起研究吧！

作者: 97forum 時間: 2013-5-13 17:12

回復 9# p212

我試試看，因為檔案有些複雜，我把他轉好之後傳送上來大家討論看看。

作者: 97forum 時間: 2013-5-14 15:33

回復 p212

我試試看，因為檔案有些複雜，我把他轉好之後傳送上來大家討論看看。
97forum 發表於 2013-5-13 17:12

[attach]14975[/attach]
在檔案中有個被查詢公司師漢企業股份有限公司
如果直接查詢，是無法找到該公司之資料(師漢企業股份有限公司高雄分公司)，但是如果被查詢範圍有選字數(前六個字)的時候，將可以查詢到資料。

還望各位前輩指導。

作者: Bodhidharma 時間: 2013-5-14 15:49

本帖最後由 Bodhidharma 於 2013-5-14 15:50 編輯

回復 11# 97forum

基本上就只是我原本公式改個名稱

=INDEX($D$1:$D$4,MATCH(1,SEARCH($A$1,$C$1:$C$4),0))

複製代碼

B2陣列公式

=INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(1,SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead]),0))

複製代碼

[attach]14976[/attach]

作者: Bodhidharma 時間: 2013-5-14 16:01

回復 12# Bodhidharma

抱歉，我公式裡應該再加個isnumber，不然只有名稱出現在最前面才會搜尋的到
B2陣列公式

=INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))

複製代碼

[attach]14977[/attach]

作者: 97forum 時間: 2013-5-14 16:50

回復 13# Bodhidharma

感謝 Bodhidharma 前輩給予的指導，我將您的程式修改之後，在我的檔案中可以獲得我所需要的資料了。
小弟不才，對於Excel的函數認識不深，無論如何非常感謝 Bodhidharma 前輩的幫忙。小弟還要再多研究了！
謝謝！

作者: Bodhidharma 時間: 2013-5-14 19:03

本帖最後由 Bodhidharma 於 2013-5-14 19:05 編輯

回復 14# 97forum

剛剛找到一篇不錯的說明，(關鍵字："結構化參照")
http://office.microsoft.com/zh-tw/excel-help/HA010342999.aspx#BMexamples_of_using_structured_referenc
才發現我的公式有一點概念上的問題

=INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(A2,表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))

複製代碼

之中有使用到"A2"，其實在表格中不應該出現這樣的東西，從該篇說明可以知道應該使用"表格5[[#這個列],[中文師名]]"，才不會因為位置而造成公式有非預期的效果
因此整個公式應該是陣列公式：

=INDEX(表格__2003sv_CHIComp01_comCustomer[ID],MATCH(TRUE,ISNUMBER(SEARCH(表格5[[#這個列],[中文師名]],表格__2003sv_CHIComp01_comCustomer[InvoiceHead])),0))

複製代碼

可以發現整列的公式都一樣，與所在的列數無關
[attach]14979[/attach]

note：另外match函數中一定要使用true，不能用1，因為後面isnumber回傳的會是邏輯值(true,false)而非數字，所以如果用1，後面的邏輯值又沒有轉換為數字的話就會找不到

作者: 准提部林 時間: 2013-5-14 22:02

以原公式：=VLOOKUP(A1,D:E,2,0)
將〔索引值〕加入〔萬用字元"*"〕，即可〔模糊比對〕取得對應值：
例如：輸入〔大甲有限公司*〕〔大甲*高雄*〕，都可以抓到〔大甲有限公司高雄分公司〕的對應值。

若〔索引值〕維持為〔大甲有限公司〕，則〔萬用字元〕可加入公式中：
=VLOOKUP(A1&"*",D:E,2,0)　＞　字串首相同即可抓出來

可類推：
索引值〔高雄分公司〕，公式：=VLOOKUP("*"&A1,D:E,2,0)　＞　字串尾相同即可
索引值〔高雄〕，=VLOOKUP("*"&A1&"*",D:E,2,0)　＞　字串〔包含〕索引文字即可

以上規則與〔尋找〕功能類似！參考參考！

作者: 准提部林 時間: 2013-5-14 22:25

若想使用FIND做搜尋，參考如下：
=LOOKUP(9^9,FIND(A1,D1:D4),E1:E4)

以索引值〔大甲有限公司〕為例：=FIND(A1,D1:D4)　
＞公式陣列值為{#VALUE!;#VALUE!;#VALUE!;1}
符合者為第４格，利用9^9此極大值為目標，LOOKUP將傳回上述陣列最後一個數字相對應位置的Ｅ４儲存格值３３。

以上公式資料例：
　　　A　　　　　B　　　　　C　　　　　　　　D　　　　　　　　　E
大甲有限公司　　公式　　　　　　　　　　大甲企業有限公司　　　　　39
　　　　　　　　　　　　　　　　　　　　大甲股份有限公司　　　　　55
　　　　　　　　　　　　　　　　　　　　大甲科技有限公司　　　　　24
　　　　　　　　　　　　　　　　　　　　大甲有限公司高雄分公司　　33

作者: Bodhidharma 時間: 2013-5-15 14:09

回復 16# 准提部林

原來vlookup可以使用萬用字元，受教！
所以原po檔案的原始公式

=VLOOKUP(LEFT(表格5[[#這個列],[中文師名]],6),表格__2003sv_CHIComp01_comCustomer[[#全部],[InvoiceHead]:[ID]],2,0)

複製代碼

只要稍微修改為

=VLOOKUP("*"&表格5[[#這個列],[中文師名]]&"*",表格__2003sv_CHIComp01_comCustomer[[#全部],[InvoiceHead]:[ID]],2,0)

複製代碼

即可達成和我公式相同的效果

至於=LOOKUP(9^9,FIND(A1,D1:D4),E1:E4)
當有不只一個符合時，會回傳「符合的字串是在最後面的」符合項目，感覺有點隨機

作者: 准提部林 時間: 2013-5-15 16:19

回復 18# Bodhidharma

使用〔萬用字元〕即是進行〔模糊比對〕，當符合〔關鍵字〕者不只一筆時，因其隨機性高，可能就抓不出正確值，
因此，公式的建立，使用者理應先釐清資料型態，尤其是重覆及類似資料的問題，在〔方便性〕及〔準確性〕上須兩擇其一，
不可能只輸入〔中華〕兩字，而由公式自行判斷〔中華郵政〕還是〔中華電信〕！
另〔MATCH．COUNTIF．SUMIF〕也都可利用萬用字元套在索引值上以取得相關數據！若是針對〔群組性〕的統計，相當便利就是！

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