vba 在与另一列和日期匹配的列中搜索值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11232210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search for value in column that matches another column AND a date?
提问by Alaa Elwany
I have data stored in three columns of Excel
我将数据存储在三列 Excel 中
Column A: Serial Number Column B: Date Column C: Value (e.g. Cost)
A 列：序列号 B 列：日期 C 列：价值（例如成本）
I need to look for the Value (Column C) associated with a particular Serial Number (Column A) AND Date (Column B).
我需要查找与特定序列号（A 列）和日期（B 列）相关联的值（C 列）。
So for example, in the screenshot below, if I want to look for the Value associated with Serial number (T455) and Date (Dec 13, 2010), the value should be 8.
例如，在下面的屏幕截图中，如果我想查找与序列号 (T455) 和日期 (2010 年 12 月 13 日) 关联的值，则该值应为 8。
The only method I can come up with would be computationally inefficient, because I would go through ALL the cells each time I look for a value.
我能想出的唯一方法是计算效率低下，因为我每次查找值时都会遍历所有单元格。
Is there a method, for example, that would limit the search area for a given serial number?
例如，是否有一种方法可以限制给定序列号的搜索区域？
For example, if I am looking for a value for Serial Number T455, how can I limit the code to search for the date in Rows (6-13) and find the corresponding value in Column C, rather than searching the whole table?
例如，如果我正在寻找序列号 T455 的值，我如何限制代码在第 (6-13) 行中搜索日期并在 C 列中找到相应的值，而不是搜索整个表？
Sub FindValue()
Dim S as String
Dim D as Date
Dim V as Integer
S = T455
D = Dec 13, 2010
for i = 1 to Range("A1").End(xldown).Row
If Range("A" & i) = S And Range("B" & i) < Date - 7 And Range("B" & i) < Date + 7 Then
' This way i search a date range rather than a specific date
V = Range("C" & i).Value
End If
End Sub
I thought of While loops, or Lookup functions, but reached a dead end.
我想到了 While 循环或 Lookup 函数，但走到了死胡同。
回答by Scott Holtzman
Non-VBA Solution that may be a lot easier and less of a headache.
非 VBA 解决方案可能会更容易，也不会让人头疼。
Column A consists of the formula, for A1 = "=B1&C1"
A 列由公式组成，对于 A1 = "=B1&C1"
Cell G1 formula can be seen in formula bar.
单元格 G1 公式可以在公式栏中看到。
UPDATEHere is a VBA solution that will work faster, but there are some notes based on what you wrote that I am unsure of. Also, see some comments to help your code work more like you want it to.
更新这是一个 VBA 解决方案，它可以更快地工作，但有一些基于你写的内容的注释，我不确定。此外，请参阅一些注释以帮助您的代码更像您希望的那样工作。
Sub FindValue()
Dim S As String, D As Date, V As Integer, rngFound As Range, cel As Range
S = "T455" 'needs quotes around the string
D = "Dec 13, 2010" 'needs quotes around the date
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
'always better to AutoFilter than Loop when you can!
.UsedRange.AutoFilter 1, S
.UsedRange.AutoFilter 2, ">" & D - 7, xlAnd, "<" & D + 7
Set rngFound = Intersect(.UsedRange, .Columns(3)).SpecialCells(xlCellTypeVisible)
'the only thing here is if you have a date range _
'you may return more than one result _
'in that case, I don't know what you want to do with all the V's
If Not rngFound Is Nothing Then
For Each cel In rngFound
V = cel.Value
Next
End If
.AutoFilterMode = False
End With
End Sub
回答by Zairja
If you are willing to entertain a non-VBA solution, then you can use this implementation, which basically uses this formula:
如果你愿意接受非 VBA 解决方案，那么你可以使用这个实现，它基本上使用这个公式：
=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H, List)+
IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")
There are several VBA methods, which really depend on how comfortable you are with the language and how efficient you want your solution to be. Off the top of my head:
有几种 VBA 方法，这实际上取决于您对语言的熟悉程度以及您希望解决方案的效率。在我的头顶：
1) filter the list with both criteria, then return the relevant value in whatever row is visible (if any)
1) 使用两个条件过滤列表，然后在任何可见行（如果有）中返回相关值
2) sort your data by those two columns (first by serial, then date), then perform searches (you'd probably want to call built-in functions like MATCH)
2）按这两列对您的数据进行排序（首先按序列，然后按日期），然后执行搜索（您可能想要调用内置函数，例如MATCH）
