Excel如何读取指定区域内非空单元格数据及其行列号到另一个工作表中?
Sub 统计()
Dim st1 As Worksheet, st2 As Worksheet
Dim Rng As Range, Rg As Range
Dim arr()
Dim i
Set st1 = Sheets("sheet1")
Set st2 = Sheets("sheet2")
Set Rng = st1.Range("b2:e4")
ReDim arr(1 To Rng.Count, 1 To 2)
i = 1
For Each Rg In Rng
If Rg <> "" Then
arr(i, 1) = st1.Cells(Rg.Row, 1) & st1.Cells(1, Rg.Column)
arr(i, 2) = Rg
i = i + 1
End If
Next
st2.Range("a1").Resize(UBound(arr), 2) = arr
End Sub
其中$B$2:$E$4修改为实际数据区域
2、sheet2
A1:=INDEX(Sheet1!A:A,SMALL(aaa,ROW(A1)))&INDEX(Sheet1!$1:$1,MOD(SMALL(aaa,ROW(A1)),1)*1000)
B1:=OFFSET(Sheet1!$A$1,SMALL(aaa,ROW(A1))-1,MOD(SMALL(aaa,ROW(A1)),1)*1000-1)
一起下拉复制
用VBA好吗?
以下附件有VBA方法和函数方法,你自己选择你要的
VBA的方法不限制你1表的行列数,而函数的方法需要您选择自定义名称的范围。
本例函数自定义名称为“数据源"