97forum

回復  p212


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

TEST-VLOOKUP.rar (20.09 KB)

下載次數: 4

2013-5-14 15:21

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

還望各位前輩指導。

Bodhidharma

回復 11# 97forum

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

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

複製代碼

B2陣列公式

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

複製代碼

TEST-VLOOKUP_sol.rar (12.02 KB)

下載次數: 3

2013-5-14 15:49

Bodhidharma

回復 12# Bodhidharma

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

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

複製代碼

TEST-VLOOKUP_sol.rar (12.04 KB)

下載次數: 7

2013-5-14 16:00

97forum

回復 13# Bodhidharma

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

Bodhidharma

回復 14# 97forum

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

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

複製代碼

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

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

複製代碼

可以發現整列的公式都一樣，與所在的列數無關
TEST-VLOOKUP_sol.rar (11.91 KB)

下載次數: 6

2013-5-14 19:03

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

准提部林

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

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

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

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

准提部林

若想使用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

回復 16# 准提部林

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

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

複製代碼

只要稍微修改為

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

複製代碼

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

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

准提部林

回復 18# Bodhidharma

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