下图是我举例说明的,原表数据很多,请问橙色区域的2列 分别用什么函数得出相应的结果,查找的是 已发货 订单已关闭 退款 的所有订单, 将属于这3个条件(已发货但退款了)全部统计出来,谢谢。
Excel数组任列查找另一列不重复值自定义函数
工具/原料
Microsoft Office Excel 2007
Excel VBA
一、函数VLOOKUPAR
功能:在数组任一列(可以重复)中查找另一列,然后在行之间移动以返回单元格的值(不重复值),找不到返回错误值#N/A。
语法:
VLOOKUPAR(lookup_value,col_index_num1,table_array,col_index_num2,range_lookup)
Lookup_value 为需要在表格数组任一列中查找的数值。Lookup_value 可以为数值或引用。
col_index_num1 为 table_array 中Lookup_value列序号
Table_array 为两列或多列数据。使用对区域或区域名称的引用。table_array 任一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。文本区分大小写。
Col_index_num2 为 table_array 中待返回的匹配值的列序号。Col_index_num2 为 1 时,返回 table_array 第一列中的数值;col_index_num2 为 2,返回 table_array 第二列中的数值,以此类推。
如果 col_index_num2 :
小于 1,VLOOKUPAR 返回错误值 #VALUE!。
大于 table_array 的列数,VLOOKUPAR 返回错误值 #REF!。
Range_lookup 为Lookup_value上行Lookup_value值区域单元格。
END
二、函数VLOOKUPAR编制
首先打开Microsoft Office Excel 2007,新建文档并保存文件名《Excel数组任列查找另一列不重复值自定义函数.xlsm》(演示文件,下面代码复制到能运行宏的工作簿都可以)如图。
然后按下快捷键ALT+F11打开VBE(宏)编辑界面,然后点菜单栏【插入】下拉中列表中点【模块(M)】如图。
然后插入了一个模块1,在代码框中复制如下代码:
Function VLOOKUPAR(mr, n1, mb As Range, n2, mrs As Range)
'2020-10-25 21:35:26
Dim mmr, nn1, mmb, nn2, mmrs, mbr As Long, mbc As Long
Dim i As Long, n As Long, k As Long
On Error Resume Next
mmr = mr.Value
If Err.Number <> 0 Then
mmr = mr
Err.Clear
End If
nn1 = n1.Value
If Err.Number <> 0 Then
nn1 = n1
Err.Clear
End If
nn2 = n2.Value
If Err.Number <> 0 Then
nn2 = n2
Err.Clear
End If
mbr = mb.Rows.Count
mbc = mb.Columns.Count
If nn1 < 1 Or nn2 < 1 Then VLOOKUPAR = "#VALUE!"
If nn1 > mbc Or nn2 > mbc Then VLOOKUPAR = "#REF!"
n = Application.WorksheetFunction.CountIf(mrs, mr)
k = 0
For i = 1 To mbr
If mb.Cells(i, nn1).Value = mmr Then
k = k + 1
If k = n + 1 Then
VLOOKUPAR = mb.Cells(i, nn2).Value
Exit For
Else
VLOOKUPAR = "#N/A"
End If
End If
Next i
If k = 0 Then VLOOKUPAR = "#N/A"
End Function
以上操作动态过程如下:
END
三、函数VLOOKUPAR使用
回到工作表窗口,填写公式如下:
F2: =VLOOKUPAR(E2,3,$A$1:$C$10,1,$E$1:E1)
F3: =VLOOKUPAR(E3,3,$A$1:$C$10,1,$E$1:E2)
F4: =VLOOKUPAR(E4,3,$A$1:$C$10,1,$E$1:E3)
F5: =VLOOKUPAR(E5,3,$A$1:$C$10,1,$E$1:E4)
F6: =VLOOKUPAR(E6,3,$A$1:$C$10,1,$E$1:E5)
F7: =VLOOKUPAR(E7,3,$A$1:$C$10,1,$E$1:E6)
F8: =VLOOKUPAR(E8,3,$A$1:$C$10,1,$E$1:E7)
F9: =VLOOKUPAR(E9,3,$A$1:$C$10,1,$E$1:E8)
F10: =VLOOKUPAR(E10,3,$A$1:$C$10,1,$E$1:E9)
G2: =VLOOKUPAR(E2,3,$A$1:$C$10,2,$E$1:E1)
G3: =VLOOKUPAR(E3,3,$A$1:$C$10,2,$E$1:E2)
G4: =VLOOKUPAR(E4,3,$A$1:$C$10,2,$E$1:E3)
G5: =VLOOKUPAR(E5,3,$A$1:$C$10,2,$E$1:E4)
G6: =VLOOKUPAR(E6,3,$A$1:$C$10,2,$E$1:E5)
G7: =VLOOKUPAR(E7,3,$A$1:$C$10,2,$E$1:E6)
G8: =VLOOKUPAR(E8,3,$A$1:$C$10,2,$E$1:E7)
G9: =VLOOKUPAR(E9,3,$A$1:$C$10,2,$E$1:E8)
G10: =VLOOKUPAR(E10,3,$A$1:$C$10,2,$E$1:E9)
得到结果如下图: