如何将Excel一个工作表中的非空数据批量导入到其他工作表

场景说明 #

主工作表存储全量原始数据，多数行存在空值，原逐列筛选、去空白、复制粘贴的操作效率极低，可通过以下两种方法快速实现需求：

方法1：公式法（Excel 365/2021及以上版本优先用） #

• 打开目标工作表，选中要存放数据的起始单元格
• 输入公式：=TOCOL(主表!A:A,1)，将公式中的「主表」替换为你的实际主表名称，A:A替换为要提取的对应列，第二个参数1表示跳过空值
• 回车后公式会自动溢出填充该列所有非空值，主表数据更新后目标表也会自动同步
• 其余列仅需修改公式中的列号即可快速完成配置

如果使用的是没有TOCOL函数的旧版Excel，可使用数组公式实现：

• 选中目标工作表A1单元格，输入公式：=INDEX(主表!A:A,SMALL(IF(主表!A$1:A$1000<>"",ROW(主表!A$1:A$1000),4^8),ROW(A1)))&""
• 按下Ctrl+Shift+Enter触发数组计算，下拉公式直到出现空白单元格即完成该列提取

方法2：VBA法（适合列数较多的批量处理场景） #

如果需要处理的列数非常多，逐列配置公式效率仍较低，可使用宏一键批量处理：

1. 按下Alt+F11打开VBA编辑器，依次点击「插入」→「模块」
2. 将如下代码粘贴到模块编辑区，把代码里的主表替换为你实际的主工作表名称：

Sub 批量提取各列非空值()
    Dim mainSht As Worksheet, targetSht As Worksheet
    Dim lastCol As Integer, lastRow As Long, i As Integer, j As Long, outputRow As Long
    Set mainSht = ThisWorkbook.Worksheets("主表") '此处修改为你的主表名称
    lastCol = mainSht.Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lastCol
        On Error Resume Next
        Set targetSht = ThisWorkbook.Worksheets("第" & i & "列数据")
        If Err.Number <> 0 Then
            Set targetSht = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            targetSht.Name = "第" & i & "列数据"
        End If
        targetSht.Cells.Clear
        lastRow = mainSht.Cells(Rows.Count, i).End(xlUp).Row
        outputRow = 1
        For j = 1 To lastRow
            If mainSht.Cells(j, i) <> "" Then
                targetSht.Cells(outputRow, 1) = mainSht.Cells(j, i)
                outputRow = outputRow + 1
            End If
        Next j
    Next i
    MsgBox "所有列非空值提取完成"
End Sub

3. 按下F5运行代码即可，会自动为每一列生成独立的工作表，存放该列的所有非空值

注意：使用VBA前需要将文件另存为*.xlsm启用宏的工作簿格式，否则代码无法正常运行。

内容的提问来源于stack exchange，提问作者Szczepan Czarniak