使用查询或宏从Excel中获取价格 [英] Get Price from Table in excel using lookup or macro
问题描述
我的表格PriceList在excel中,结构如下
类型厚度宽度高度价格
iron 5 7 10 20
铁10 10 15 24
铁12 14 17 26
如果有人进入铁,厚度10,宽度9,高度14使用vlookup或宏,我可以找到价格。我已经尝试使用vlookup,但它不工作,后来我发现在谷歌Vlookup只检索数据与一个参数。我可以使用宏来做这个,就像我们用查询选择表格数据一样。
select * from [sheet1 $] where col1 = x
请建议?
编辑:我已经使用此计算了上述内容。现在我打电话给这个宏表单更改事件,如下所示
Private Sub Worksheet_Change(ByVal Target As Range)
如果Target.Column = 41或Target.Column = 43或Target.Column = 45或Target.Column = 46然后
Dim i As Currency
i = Calculate_CorePrice(cell(Target.Row,46 ).Value,cell(Target.Row,45).Value,cell(Target.Row,41).Value,cell(Target.Row,43).Value,RWJ Doorset Schedule,ED15)
cell(Target.Row,134).Value = i
End If
End Sub
如果我使用硬编码值调试函数本身,它正在工作,但如果我以上述方式调用函数,它不返回值,我无法调试sheet_change事件。 / p>
如何调试此事件。
假设您的列是A,B,C等。您可以使用Index / Match公式作为数组(输入 CTRL + SHIFT + ENTER )。
如果用户输入t y,厚度,宽度和高度 W1
, X1
, Y1
和 Z1
,您可以使用:
=索引($ E $ 2:$ E $ 10中,匹配度(W1&安培; X1&安培; Y1&安培; Z1,$ A $ 2:$ A $ 10安培; $ B $ 2:$ B $ 10安培; $ C $ 2:$ C $ 10安培; $ D $ 2:$ D $ 10 ,0))
这是复杂的,但基本上匹配
部分是你在 A2:A10
的范围内寻找 W1
,那么任何 X1
,您将搜索 B2:B10
等。
I have table "PriceList" in excel with below structure
Type Thickness width Height Price
iron 5 7 10 20
iron 10 10 15 24
iron 12 14 17 26
how can i find the price if some-one enters type iron, thickness 10, width 9, height 14 using vlookup or macro. i have tried using vlookup but its not works and later on i found on google that Vlookup only retrieve data with one parameter. Can i do this using macro like we do for selecting sheet data using queries
select * from [sheet1$] where col1=x
Please suggest??
Edit: i have calculated the above using this. Now i am calling this macro on sheet change event like below
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 41 Or Target.Column = 43 Or Target.Column = 45 Or Target.Column = 46 Then
Dim i As Currency
i = Calculate_CorePrice(cell(Target.Row, 46).Value, cell(Target.Row, 45).Value, cell(Target.Row, 41).Value, cell(Target.Row, 43).Value, "RWJ Doorset Schedule", "ED15")
cell(Target.Row, 134).Value = i
End If
End Sub
if i debug the function itself with hard-coded values, it is working but if i call the function as in above way, it does not return value and i am not able to debug sheet_change event.
How can i debug this event.
Assuming your columns are A, B, C, etc. You can use an Index/Match formula as an array (enter with CTRL+SHIFT+ENTER).
If the user enters the type, thickness, width, and height in W1
, X1
, Y1
, and Z1
, you can use:
=Index($E$2:$E$10,Match(W1&X1&Y1&Z1,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,0))
It's convoluted, but basically the Match
part is you are looking for W1
in the range A2:A10
, then whatever is in X1
, you will search B2:B10
, etc.
这篇关于使用查询或宏从Excel中获取价格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!