iT邦幫忙

0

請問EXCEL VBA 能實現VLOOKUP多條件取得資料嗎?

各位大大好
請問能透過EXCEL的VBA實現VLOOKUP多條件選擇嗎?
主要是想透過A表中的"公司"以及"出貨產品"欄位
去查B表中,符合"公司"以及"出貨產品"的那筆資料中的
數量、出貨時間以及包裝等資訊
並回填至A表中
如果用公式的話,可以透過VLOOKUP搭配IF來實現
但筆數一多的話,會寫到頭昏腦脹
可以麻煩各位大大協助指導如何使用VBA巨集來處理嗎?

謝謝!

A.查詢表
https://ithelp.ithome.com.tw/upload/images/20200416/20126402ctOv9NT831.png
檔案下載
https://drive.google.com/open?id=1xk1iB-_pBfsuwseYutp-cm4LAG4ggzY9
B.資料來源
https://ithelp.ithome.com.tw/upload/images/20200416/20126402ik58TUh03U.png
檔案下載
https://drive.google.com/open?id=1HnCQLK_orl5s0cQY2WcDux5ylWeO0YGf

0
海綿寶寶
iT邦大神 1 級 ‧ 2020-04-18 16:54:09
最佳解答

Sheet1=A.查詢表
Sheet2=B.資料來源

Type OneRec
    Amount As String
    Outboundtime As String
    Package As String
End Type
Function myVlookup(ByVal CompanyName As String, ByVal Product As String) As OneRec
    Dim retOneRec As OneRec
    Dim bRun As Boolean
    Dim nRow As Integer

    bRun = True
    nRow = 1
    Do
        If (Worksheets("Sheet2").Range("A" & nRow).Value = "") Then
            bRun = False
            Exit Do
        End If
        
        If ((Worksheets("Sheet2").Range("A" & nRow).Value = CompanyName) And (Worksheets("Sheet2").Range("C" & nRow).Value = Product)) Then
            retOneRec.Amount = Worksheets("Sheet2").Range("D" & nRow).Value
            retOneRec.Outboundtime = Worksheets("Sheet2").Range("E" & nRow).Value
            retOneRec.Package = Worksheets("Sheet2").Range("F" & nRow).Value
            bRun = False
        End If
               
        nRow = nRow + 1
    Loop Until bRun = False
    
    myVlookup = retOneRec
End Function
Sub Main()
    Dim Result As OneRec
    Dim bRun As Boolean
    Dim nRow As Integer
    
    bRun = True
    nRow = 2
    
    Do
        If (Worksheets("Sheet1").Range("A" & nRow).Value = "") Then
            bRun = False
            Exit Do
        End If
               
        Result = myVlookup(Worksheets("Sheet1").Range("A" & nRow).Value, Worksheets("Sheet1").Range("C" & nRow).Value)
               
        Worksheets("Sheet1").Range("D" & nRow).Value = Result.Amount
        Worksheets("Sheet1").Range("E" & nRow).Value = Result.Outboundtime
        Worksheets("Sheet1").Range("F" & nRow).Value = Result.Package
               
        nRow = nRow + 1
    Loop Until bRun = False
End Sub

通常,VBA如果用到迴圈的話,效能都不會比較好。

chengate iT邦新手 5 級 ‧ 2020-04-19 23:45:38 檢舉

感謝海綿大神幫忙撰寫VBA,立即來研究~

0
planetoid
iT邦新手 2 級 ‧ 2020-04-17 02:04:17

不需要使用 VBA 的作法是:
將多個欄位值黏起來變成索引鍵 例如 公司名稱_出貨產品名稱
兩個工作表都要建立索引鍵 並建議放在第一欄
再使用 VLOOKUP 查詢索引鍵對應的其他欄位

chengate iT邦新手 5 級 ‧ 2020-04-17 23:49:12 檢舉

感謝P大的資訊分享
只是A.查詢表資料筆數很多,一個年度最多可能會有兩三千筆
而且欄位其實不只範例的六欄而已
如果先拉好公式的話,檔案會很大

當然之前也有想過用VBA先組合字串後
再搭配P大的方法(Vlookup + if or choose函數)
只是B表的資料破萬筆,公司的電腦慢,跑起來大概就一個上午了
B表的資料也可能會一直更新,所以可能每幾天或每週就需要更新一次

所以才會希望以VBA的方式來輔助,再寫A表文字列述判斷,資料打到哪就更新到哪就好
這樣檔案應該會比較小,總之感謝提供意見

0
ccenjor
iT邦新手 3 級 ‧ 2020-04-17 19:03:48

我不會VBA,所以用公式作業。
首先在G欄將ABC三欄的內容串連。
https://ithelp.ithome.com.tw/upload/images/20200417/20109881OFFNpZpIEO.png
回到查詢表
點取D2儲存格輸入公式:
=VLOOKUP($A2&$B2&$C2,IF({1,0},工作表1!$G$2:$G$13,工作表1!D$2:D$13),2,0)
再將公式複製到D2:F4儲存格。
https://ithelp.ithome.com.tw/upload/images/20200417/20109881SepHpf2enN.png

chengate iT邦新手 5 級 ‧ 2020-04-17 23:55:39 檢舉

感謝C大還花時間幫忙寫範例的公式
只是,礙於A表的實際筆數可能至少兩三千筆以上
加上B表的資料破萬筆,而且一直都有可能會更新狀態
用公式的話,檔案其實還蠻不小的
然後這幾天網路上是有關於VBA如何跑VLOOKUP的方式,但都是單一條件
所以才會希望能學習是否能以VBA方式來寫多條件式的VLOOKUP
感謝C大還花時間幫忙指導公式

1
paicheng0111
iT邦高手 1 級 ‧ 2020-04-18 16:31:40
chengate iT邦新手 5 級 ‧ 2020-04-19 23:44:33 檢舉

感謝P大提供方法參考,我來研究看看是否可滿足需求,謝謝~

0
congimei
iT邦新手 5 級 ‧ 2020-04-22 08:21:23

如果是多筆資料,那試試只有當前cell中有改變時,只處理該cell變更事件。
像是 如何在 Excel 中的某些儲存格發生變更時執行巨集

chengate iT邦新手 5 級 ‧ 2020-04-26 01:35:29 檢舉

感謝C大提供建議喔~

我要發表回答

立即登入回答