　暱稱: Tiger97

帖子: 71
主題: 16
精華: 0
積分: 111
點名: 235
作業系統: Windows 10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
註冊時間: 2011-3-7
最後登錄: 2024-11-25

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

1^# 跳轉到 » 正序看帖

打印

字體大小: tT

發表於 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)

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

樓主熱帖

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-25

19^#

發表於 2013-5-15 16:19 | 只看該作者

回復 18# Bodhidharma

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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

18^#

發表於 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)
當有不只一個符合時，會回傳「符合的字串是在最後面的」符合項目，感覺有點隨機

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-25

17^#

發表於 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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-25

16^#

發表於 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)　＞　字串〔包含〕索引文字即可

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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

15^#

發表於 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))

複製代碼

可以發現整列的公式都一樣，與所在的列數無關

TEST-VLOOKUP_sol.rar (11.91 KB)

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

TOP

97forum

　暱稱: Tiger97

中學生 Rank: 2

帖子: 71
主題: 16
精華: 0
積分: 111
點名: 235
作業系統: Windows 10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
註冊時間: 2011-3-7
最後登錄: 2024-11-25

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

14^#

發表於 2013-5-14 16:50 | 只看該作者

回復 13# Bodhidharma

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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

13^#

發表於 2013-5-14 16:01 | 只看該作者

回復 12# Bodhidharma

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

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

複製代碼

TEST-VLOOKUP_sol.rar (12.04 KB)

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

12^#

發表於 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))

複製代碼

TEST-VLOOKUP_sol.rar (12.02 KB)

TOP

97forum

　暱稱: Tiger97

中學生 Rank: 2

帖子: 71
主題: 16
精華: 0
積分: 111
點名: 235
作業系統: Windows 10
軟體版本: Office 2016
閱讀權限: 20
性別: 男
註冊時間: 2011-3-7
最後登錄: 2024-11-25

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章

11^#

發表於 2013-5-14 15:33 | 只看該作者

回復 p212

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

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

還望各位前輩指導。

TOP