vba 在vbs excel中返回匹配多个条件的行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10690642/
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
Return a row number that matches multiple criteria in vbs excel
提问by HLMI_IT
I need to be able to search my whole table for a row that matches multiple criteria. We use a program that outputs data in the form of a .csv file. It has rows that separate sets of data, each of these headers don't have any columns that are unique in of them self but if i searched the table for multiple values i should be able to pinpoint each header row. I know i can use Application.WorksheetFunction.Match to return a row on a single criteria but i need to search on two three or four criteria.
我需要能够在整个表中搜索符合多个条件的行。我们使用一个程序以 .csv 文件的形式输出数据。它具有分隔数据集的行，这些标题中的每一个都没有任何自己独特的列，但是如果我在表格中搜索多个值，我应该能够查明每个标题行。我知道我可以使用 Application.WorksheetFunction.Match 根据单个条件返回一行，但我需要搜索两个三个或四个条件。
In pseudo-code it would be something like this:
在伪代码中，它会是这样的：
Return row number were column A = bill & column B = Woods & column C = some other data
回答by user2586356
We need to work with arrays:
我们需要使用数组：
There are 2 kinds of arrays:
有2种数组：
numeric {1,0,1,1,1,0,0,1}
boolean {TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE}
to convert between them we can use:
要在它们之间进行转换，我们可以使用：
MATCH function
MATCH(1,{1,0,1,1,1,0,0,1},0) -> will result {TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE}
simple multiplication
{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE}*{TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE} -> will result {1,0,1,1,1,0,0,1}
you can can check an array in the match function, entering it like in the picture below, be warned that MATCH function WILL TREAT AN ARRAY AS AN "OR" FUNCTION (one match will result in true ie:
您可以在 match 函数中检查一个数组，如下图所示输入它，注意 MATCH 函数会将数组视为“或”函数（一个匹配将导致 true，即：
MATCH(1,{1,0,1,1,1,0,0,1},0)=TRUE
, YOU MUST CTR+SHIFT+ENTER !!! FOR IT TO GIVE AN ARRAY BACK!!!
in the example below i show that i want to sum the hours of all the employees except the admin per case
在下面的示例中，我表明我想对每个案例中除管理员之外的所有员工的工作时间求和
we have 2 options, the long simple way, the complicated fast way:
我们有两种选择，简单的方法，复杂的快速方法：
long simple way
D2=SUMPRODUCT(C2:C9,(A2=A2:A9)*("admin"<>B2:B9)) <<- SUMPRODUCT makes a multiplication
basically A1={2,3,11,3,2,4,5,6}*{0,1,1,0,0,0,0,0} (IT MUST BE A NUMERIC ARRAY TO THE RIGHT IN SUMPRODUCT!!!) ie: A1=2*0+3*1+11*1+3*0+2*0+4*0+5*0+6*0
基本上 A1={2,3,11,3,2,4,5,6}*{0,1,1,0,0,0,0,0} （它必须是 SUMPRODUCT 右边的数字数组!!!) 即：A1=2*0+3*1+11*1+3*0+2*0+4*0+5*0+6*0
this causes a problem because if you drag the cell to autocomplete the rest of the cells, it will edit the lower and higher values of ie: D9=SUMPRODUCT(C9:C16,(A9=A9:A16)*("admin"<>B9:B16)), which is out of bounds same as the above if you have a table and want to view the results in a diferent order
这会导致问题，因为如果您拖动单元格以自动完成其余单元格，它将编辑较低和较高的值，即： D9=SUMPRODUCT(C9:C16,(A9=A9:A16)*("admin"< >B9:B16))，如果你有一个表格并且想以不同的顺序查看结果，这和上面的一样越界
the fast complicated way
D3=SUMPRODUCT(INDIRECT("c2:c9"),(A3=INDIRECT("a2:a9"))*("admin"<>INDIRECT("b2:b9")))
it's the same, except that INDIRECT was used on the cells that we want not be modified when autocompleting or table reorderings be warned that INDIRECT sometimes give VOLATILE ERROR,i recommend not using it on a single cell or using it only once in an array
它是相同的，除了在自动完成或表重新排序时我们不想修改的单元格上使用了 INDIRECT 警告 INDIRECT 有时会给出易失性错误，我建议不要在单个单元格上使用它或在数组中只使用一次
f* c* i cant post pictures :( table is:
f* c* 我不能发图片 :( 表是：
case emplyee hours totalHoursPerCaseWithoutAdmin
1 admin 2 14
1 him 3 14
1 her 11 14
2 him 3 5
2 her 2 5
3 you 4 10
3 admin 5 10
3 her 6 10
and for the functions to check the arrays, open the insert function button (it looks like and fx) then doubleclick MATCH and then if you enter inside the Lookup_array a value like A2=A2:A9 for our example it will give {TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE} that is because only the first 3 lines are from case=1
对于检查数组的函数，打开插入函数按钮（它看起来像和 fx）然后双击 MATCH 然后如果你在 Lookup_array 中输入一个像 A2=A2:A9 这样的值，对于我们的例子，它会给出 {TRUE,TRUE ,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE} 那是因为只有前 3 行来自 case=1
回答by Siddharth Rout
Something like this?
像这样的东西？
Assuming that you data in in A1:C20
假设您在 A1:C20 中输入数据
I am looking for "Bill" in A, "Woods" in Band "some other data" in C
我正在寻找中的“比尔” A、中的“伍兹”B和中的“其他一些数据”C
Change as applicable
适用时更改
=IF(INDEX(A1:A20,MATCH("Bill",A1:A20,0),1)="Bill",IF(INDEX(B1:B20,MATCH("Woods",B1:B20,0),1)="Woods",IF(INDEX(C1:C20,MATCH("some other data",C1:C20,0),1)="some other data",MATCH("Bill",A1:A20,0),"Not Found")))
=IF(INDEX(A1:A20,MATCH("Bill",A1:A20,0),1)="Bill",IF(INDEX(B1:B20,MATCH("Woods",B1:B20,0), 1)="伍兹",IF(INDEX(C1:C20,MATCH("一些其他数据",C1:C20,0),1)="其他一些数据",MATCH("Bill",A1:A20,0 ），“未找到”）））
SNAPSHOT
快照
回答by andy holaday
I would use this array* formula (for three criteria):
我会使用这个数组*公式（对于三个标准）：
=MATCH(1,((Range1=Criterion1)*(Range2=Criterion2)*(Range3=Criterion3)),0)
*commit with Ctrl+Shift+Enter
*承诺 Ctrl+Shift+Enter
