excel - 检索列excel的值

Question

我需要您的帮助，我想检索单元格包含 1 的列的值，这是一个示例:

图片



一个 |乙| C | D

1 | 0 | 0 |

0 | 1 | 1 |

1 | 1 | 0 |

期望的结果:

一个 |乙| C | D

1 | 0 | 0 |一个

0 | 1 | 1 |公元前

1 | 1 | 0 |甲，乙

谢谢 !

Answer 1

这是适用于任意数量列的 VBA 方法:

Function SelectedColumns(R As Range) As String
    Dim i As Long, n As Long, count As Long
    Dim cols As Variant

    n = R.Columns.count
    ReDim cols(1 To n)
    For i = 1 To n
        If R.Cells(1, i).Value = 1 Then
            count = count + 1
            cols(count) = Split(R.Cells(1, i).Address, "$")(1)
        End If
    Next i
    If count > 0 Then
        ReDim Preserve cols(1 To count)
        SelectedColumns = Join(cols, ",")
    End If
End Function

将上述代码放入标准代码模块中。在您的示例中，如果您输入 =SelectedColumns(A1:C1)在单元格中 D1并复制下来，然后它将按预期工作。