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

標題: [發問] excel VBA 函數-垂直搜尋相對應列的值 [打印本頁]

作者: junkwei 時間: 2014-12-11 23:18 標題: excel VBA 函數-垂直搜尋相對應列的值

各位前輩大家好
小弟最近在使用EXCEL VBA做資料整理

小弟希望在儲存格列中垂直搜尋出相對應列的值(例如:在指定搜尋範圍內, 搜尋第m列的值, 當第m列某行的值等於"欲搜尋的值"時，則回傳第n列同行的值)
目前在網路上只找到VLOOKUP和LOOKUP這兩個函數可以有類似的功能
但VLOOKUP 函數限制"欲搜尋的值"只能在指定搜尋範圍的第一列
而LOOKUP 函數 "欲搜尋的值"又限制只能以遞增的順序排列

想請問各位前輩除了這兩個函數之外
有沒有其他好用的VBA函數可以有類似的功能
但不會有以上限制的

麻煩各位前輩指導了~~謝謝~~!

作者: GBKEE 時間: 2014-12-12 06:43

回復 1# junkwei
試試看

Option Explicit
Sub Ex()
Dim Rng As Range
Set Rng = Sheets("指定工作表").Range("指定範圍").Find("搜尋字串", LookAT:=xlWhole)
If Not Rng Is Nothing Then MsgBox Rng.Value
End Sub

複製代碼

作者: stillfish00 時間: 2014-12-12 10:15

回復 1# junkwei
附檔舉個你實際遇到的例子&希望的結果，問到的答案會精準的多。。

作者: ashan0418 時間: 2014-12-12 17:00

回復 2# GBKEE

GBKEE 版主您好 :

請問一下，若我有多個要搜尋的資料該如何一個一個的取代成其他的值，

例如a欄到c欄中的『test』取代成『tt』!!

作者: junkwei 時間: 2014-12-14 19:26

回復 2# GBKEE

GBKEE 版主您好:
不好意思小弟初學VBA
您的code 小弟還是有些應用上的困難..
因此依stillfish00前輩的建議
將我的問題完整呈現
再麻煩版主撥空指導了...
非常感謝...!!

作者: junkwei 時間: 2014-12-14 19:31

回復 3# stillfish00

stillfish00前輩您好:
不好意思小弟的問題敘述似乎真的不太清楚
因為我原本的目的是打算只問那個函數的名稱
再自己去找函數的相關資訊去應用
不料資質駑鈍....0rz
好像問題敘述的不完全,
前輩們所寫的程式也不見得可以完全應用
下次小弟發文時會特別注意的~!
也感謝前輩的不吝指教~~!

作者: GBKEE 時間: 2014-12-15 08:12

回復 5# junkwei
試試看

Option Explicit
Sub Ex()
Dim Rng, Ar, R As Range, i As Integer, ii As Integer
Ar = Range("A1").CurrentRegion.Value '資料庫
i = [MATCH(J1,A1:D1,0)]
'或直接給 I=3 '數學成績在資料庫的欄位
Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)
For Each R In Rng.Rows
For ii = 1 To UBound(Ar, 1)
If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 1) & Ar(ii, 2) Then
R.Cells(1, 3) = Ar(ii, 3)
Exit For
End If
Next
Next
End Sub

複製代碼

作者: stillfish00 時間: 2014-12-15 16:40

回復 5# junkwei
依5樓圖的問題，最簡單的方法是
在J2填上 =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$1:A$16)
然後往下填滿公式，
這公式算是比較進階又還蠻常遇到的一個小技巧。

若要用VBA就參考G版的吧~

作者: junkwei 時間: 2014-12-16 21:04

回復 junkwei
試試看
GBKEE 發表於 2014-12-15 08:12

謝謝GBKEE版主的幫助!!又讓小弟再次見識了一次vba的威力!
不過您原本的code好像欄位有些筆誤
因此小弟有小小修正了一下, 如下所示
供其他有需要的版友參考~~
最後再次感謝您的幫忙~!

Sub CAL2()
Dim Rng, Ar, R As Range, i As Integer, ii As Integer
Ar = Range("A1").CurrentRegion.Value '資料庫
i = [MATCH(J1,A1:D1,0)]
'或直接給 i=1 '數學成績在資料庫的欄位
Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)
For Each R In Rng.Rows
For ii = 1 To UBound(Ar, 1)
If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 2) & Ar(ii, 3) Then
R.Cells(1, 3) = Ar(ii, 1)
Exit For
End If
Next
Next
End Sub

複製代碼

作者: junkwei 時間: 2014-12-16 21:19

回復 junkwei
依5樓圖的問題，最簡單的方法是
在J2填上 =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$ ...
stillfish00 發表於 2014-12-15 16:40

感謝stillfish00前輩的方法~
讓我學到不同且實用的方法~謝謝您~~!
不知道前輩是否方便說明一下您的公式中lookup函數的第二欄
以便小弟日後應用

請問
1/(範圍=欲搜尋值) 是一種固定的語法嗎?
以及
當中的 * 符號在這裡代表的意義是"且"的意思嗎?

小弟在網路上好像不容易找到相關的說明...@@
不好意思再麻煩前輩指導了!
謝謝~~!

作者: GBKEE 時間: 2014-12-16 21:46

回復 9# junkwei
程式碼是依 5# 的圖示模擬的,你修改成功.那VBA可很快上手的.

作者: stillfish00 時間: 2014-12-16 22:36

回復 10# junkwei
(B$1:B$16=H2)
B1~B16每個元素都和H2比，相同為True、不同為FALSE
假如只有B2及B3和H2相同，就等於為回傳 :
False,True,True,False,False... 這樣的陣列

(C$1:C$16=I2)也是如此，假設例如回傳:
False,False,True,False,False...

(B$1:B$16=H2)*(C$1:C$16=I2)
相當於上述回傳的陣列個元素相乘，相乘時True會自動轉換為1、False為0
所以也是你說的"且"的動作，以上面舉的例子就是回傳:
0,0,1,0,0...

而用1除以(B$1:B$16=H2)*(C$1:C$16=I2)，以上面舉的例子就是回傳:
#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!...
#DIV/0!為除零造成的錯誤

LOOKUP就自己查看說明了
如果 LOOKUP 函數無法找到 lookup_value，便會採用在 lookup_vector 中等於或僅次於 lookup_value 的最大值。

總之是要比對的欄放分母，多欄(2欄、3欄...都可以)都要符合就用*，取回結果的欄就放第三參數。
公式到EXCEL一般區應該比較多討論。

作者: junkwei 時間: 2014-12-16 23:39

非常謝謝stillfish00前輩超級詳細的解釋~這樣小弟就完全了解~~!

也謝謝GBKEE版主的鼓勵, 小弟會繼續加油的~~!!

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