搜索表并返回多个列标题的公式 [英] Formula to search table and return multiple column headers

问题描述

我试图在单元格中输入某个值，在表中搜索该值(垂直和水平)，然后返回包含该值的列的列标题.我的公式有效，但它只会返回1个列标题，而我需要它返回多个

I am trying to enter a certain value in a cell, search a table for that value (vertically and horizontally), then return the column header of the columns containing that value. My formula works, but it will only return 1 column header, and I need it to return multiple

公式:

{=INDEX(Sheet2!A1:J31,,MAX(IF(Sheet2!A1:J31=B2,COLUMN(Sheet2!A1:J1)-COLUMN(Sheet2!A1)+1)))}

推荐答案

我不知道会执行此操作的内置Excel公式，但这可以通过用户定义的函数来实现.

I'm not aware of a built in Excel formula that will do this, but this can be accomplished with a user-defined function.

这是一个示例数据集，显示了结果和使用的公式:

Here is an example dataset, showing the results and the formulas used:

以下是用户定义函数的代码:

Here is the code for the user-defined functions:

Function SearchColumns(SearchValue As String, SearchRange As Range,    Delimiter As String) As String

SearchColumns = ""

Dim counter As Integer
For i = 1 To SearchRange.Columns.Count
    counter = 0
    For j = 1 To SearchRange.Rows.Count
        If SearchRange.Cells(j, i).Value = SearchValue Then
            counter = counter + 1
        End If
    Next j
    If counter > 0 Then
        SearchColumns = SearchColumns + SearchRange.Cells(1, i).Value + Delimiter + " "
    End If
Next i

SearchColumns = Left(SearchColumns, Len(SearchColumns) - 2)

End Function

Function SearchRows(SearchValue As String, SearchRange As Range, Delimiter As String) As String

SearchRows = ""

Dim counter As Integer
For i = 1 To SearchRange.Rows.Count
    counter = 0
    For j = 1 To SearchRange.Columns.Count
        If SearchRange.Cells(i, j).Value = SearchValue Then
            counter = counter + 1
        End If
    Next j
    If counter > 0 Then
        SearchRows = SearchRows + SearchRange.Cells(i, 1).Value + Delimiter + " "
        End If
    Next i

SearchRows = Left(SearchRows, Len(SearchRows) - 2)

End Function

这篇关于搜索表并返回多个列标题的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！