![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
excel中如何把某一列数据自动筛选然后返回对应值到另一个表格中?
用下面修改下
Excel按规定提取列数据
Option Base 1
Sub 按规定提取某列数据()
'2020-4-3 21:34:49
Dim mb(), m(), i As Long, j As Long, r As Long, c As Long, n As Range, k As Long, myr As Range, n1 As Long
mb = Selection
r = UBound(mb, 1)
c = UBound(mb, 2)
ReDim m(r, 1)
Set n = Application.InputBox(prompt:="选择规定数据单元格", Type:=8)
n1 = 1
k = 0
On Error Resume Next
For i = 1 To r
For j = 1 To c
If InStr(1, mb(i, j), n.Text, 1) > 0 Then
k = k + 1
m(k, 1) = mb(i, n1)
Exit For
End If
Next j
Next i
If k = 0 Then
MsgBox "数据不存在!"
Else
Sheets("Sheet5").Select
Set myr = Range("a1")
myr.Value = "商户"
Range(myr.Offset(1, 0), myr.Offset(k, 0)) = m
End If
End Sub
在D列任单元格输入2020-06
运行上面宏时,选该单元格可以
=index(sheet4!a:a,small(if(month(sheet4!c$2:c$100)=6,row($2:$100),6^6),row(a1)))&""
同时按下CTRL+SHIFT+Enter三键,使数组公式出现{ },公式下拉到空值
您好,问一下就是,这个公式里面C$100是指到第100行为止是么?我把他改成1000提示错误了
是100行,改成1000不能只改C$1000,也要改$2:$1000
=index(sheet4!a:a,small(if(month(sheet4!c$2:c$1000)=6,row($2:$1000),6^6),row(a1)))&""