我想修改这个函数:Custom Excel VBA Function (Modified VLOOKUP) from Cell referring to a range in different file gives an error

我需要的功能在概念上很简单——我需要 VlookUp，它的返回值对应于查找值的第 k 次出现而不是标准的第 1 次出现，例如:

如果第 k 次出现不存在，则函数应返回错误。

类似电子表格的数据:

     A B   
1 "a" "1a"   
2 "a" "2a"    
3 "b" "1b"
4 "a" "3a"
5 "b" "2a"

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=1) should return 1a

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=2) should return 2a

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=3) should return 3a

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=1) should return 1b

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=2) should return 2b

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=3) should return error

我熟悉 R 和 Matlab，所以我的想法是面向向量的，我首先尝试通过重写一行代码(来 self 链接到的帖子):

row = .Match(lookup_value, table_array.Columns(1), 0)

进入:

If k =2 Then
row_1 = .Match(lookup_value, table_array.Columns(1), 0)
number_of_rows=table_array.Columns(1).Rows.Count

row = .Match(lookup_value, table_array.Columns(1).Rows( (row_1+1):number_of_rows ), 0)

上面一行是伪代码，因为我不知道如何正确编写它(.Rows( (row_1+1):number_of_rows ) 是数字向量，看起来很有趣)

khác
row = .Match(lookup_value, table_array.Columns(1), 0)

End If

for k > 2 将此代码放入 for 循环会很简单(但效率低下)。

我注意到修改后的 .Match() 也将 k 作为参数，这将使所有工作都需要。使用循环查找值的第 k 次出现的位置似乎很慢，或者我对 VBA 不是很熟悉。

1 Câu trả lời

您可以尝试这两个基于 Excel 的公式:根据您的数据表进行调整。

• 方法一:

CountIF 函数允许您计算查找值在列范围内出现的次数。

=COUNTIF(columnRange,lookupvalue)

假设这就是您要查找的内容:从引用中提取的数据。

CUST 列使用 =F78&COUNTIF($F$75:$F78,F78)

Master Data Starts from `F75 to H84`    
Customer CUST Phone number
Smith Smith1 320-966-4023
Smith Smith2 686-612-7782
Jason Jason1 122-617-7154
Albert Albert1 547-436-7376
Nancy Nancy1 956-633-7322
Smith Smith3 132-716-5240
Grove Grove1 340-267-0529
Andy Andy1 531-413-4718
Jason Jason2 613-228-4294
Nancy Nancy2 272-525-2042

最后第 n 次查找:

例如客户第 4 次出现的电话号码 = Smith

=VLOOKUP($D$74&"4",$G$75:$H$93,2,FALSE)

Lookup  
Customer Smith
Phone number    
1st 320-966-4023
2nd 686-612-7782
3rd 132-716-5240
4th 185-813-8883

Reference from Chandoo: 4. Lookup 2nd / 3rd / 4th occurrence of an item in a list .

• 方法 2:用于以下公式的示例数据:

公式:

=INDEX(ALTable,SMALL(IF(OFFSET(ALTable,0,0,ROWS(ALTable),1)=F90,
ROW(OFFSET(ALTable,0,0,ROWS(ALTable),1))-ROW(OFFSET(ALTable,0,0,1,1))+1,
ROW(OFFSET(ALTable,ROWS(ALTable)-1,0,1,1))+1),F91),2)

Reference from CPearson Arbitary Lookups .我个人不喜欢volatile functionsVí dụ chỉ số()...虽然..

关于algorithm - 修改后的 VlookUp 返回与查找值对应的第 k 个值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14093856/