根据行标题和单元格值返回列标题 [英] Return column header based on row header and cell value

问题描述

我有以下数据网格:

         ---------Header 1   Header 2   Header 3   Header 4 


Row 1       x                    x          x
Row 2                  x         x
Row 3                            x          
Row 4       x          x         x          x

然后我有第二张纸，像这样:

I then have a second sheet that looks like this:

Row 1         Row 2        Row 3        Row 4

我希望第二张纸看起来像这样:

I would like the second sheet to end up looking like this:

    Row 1         Row 2        Row 3        Row 4
  Header 1      Header 2     Header 3     Header 1
  Header 3      Header 3                  Header 2
  Header 4                                Header 3
 .                                       Header 4                                        

忽略最后一个时期，我只是用它来正确格式化它.

Ignore that last period, I just used it to format it properly.

我已经与MATCH和INDEX玩了两个小时，虽然我可以得到其中的一部分，但似乎无法将其全部一起使用.

I've been playing with MATCH and INDEX for a couple hours and while I can get pieces of it, I can't seem to get it to all work together.

我仅使用页眉1"和行1"作为示例.实际数据分别是A列和1行中的文本.另外，由于将修改源数据，所以我希望有可以自动更新第二张表的东西.

I use 'Header 1' and 'Row 1' as examples only. The actual data is text in Column A and Row 1, respectively. Also, since the source data will be modified, I'd prefer to have something that would automatically update the second sheet.

推荐答案

这是使用VBA函数的一种方法:

Here is one way to do it with a VBA function:

在开发人员"选项卡(*)中，单击Visual Basic，然后在此处单击插入"菜单，然后选择模块"以插入新模块.然后粘贴以下代码:

In the Developer Tab(*) Click on Visual Basic, then click on the "Insert" menu there and choose "Module" to insert a new module. Then paste in the Following code:

Option Explicit

Public Function GetHeaderMatchingRow(RowText As String, _
                                    SearchRange As Range, _
                                    iHdrNo As Integer) As String
    Dim rng As Range
    Set rng = SearchRange

    Dim cel As Range

    'Get the Row to scan
    Dim i As Long, rowOff As Long
    For i = 2 To rng.Rows.Count
        Set cel = rng.Cells(i, 1)
        If cel.Value = RowText Then
            rowOff = i
            Exit For
        End If
    Next i

    'Now, scan horizontally for the iHdrNo'th non-blank cell
    Dim cnt As Integer
    For i = 2 To rng.Columns.Count
        Set cel = rng.Cells(rowOff, i)
        If Not CStr(cel.Value) = "" Then
            cnt = cnt + 1
            If cnt = iHdrNo Then
                GetHeaderMatchingRow = rng.Cells(1, i).Value
                Exit Function
            End If
        End If
    Next i

    GetHeaderMatchingRow = ""
End Function

点击调试"菜单，然后选择编译VBAProject".

Click on the "Debug" menu and select "Compile VBAProject".

现在返回Excel，在第一张工作表中定义一个命名范围"以覆盖网格中的所有数据.行名称应该是该范围内的第一列，标题文本应该是该范围内的第一行.

Now go back to Excel and in your first sheet define a Named Range to cover all of your data in the grid. The Row names should be the first column in this range and the Header text should be the first row in it.

现在转到第二张工作表，并在每个输出单元格中输入如下公式:

Now go to your second sheet and enter a formula like this in every output cell:

=GetHeaderMatchingRow(A$1, RowHeaderRange, 1)

第一个参数是行文本，它将尝试在范围的第一列中进行匹配.我这里有"A $ 1"，因为在我的测试中，第二张工作表的列标题也是第一张工作表中的行名，就像您的姓名一样.

Where the First parameter is the Row text that it will try to match in the first column of the range. I have "A$1" here because the in my test, my second sheet's column headers are also the Row-names in my first sheet, just like yours.

第二个参数是要搜索的范围(在这种情况下，是我们之前定义的命名范围)，第三个参数是它要查找的匹配项的计数(第一，第二，第三等).

The second argument is the range to search (in this case, the Named Range we defined earlier), and the third argument is the count of the match that it is looking for (1st, 2nd, 3rd, etc.).

请注意，第一个和第三个参数应该根据输出所针对的列和行而改变.

Note that the first and third parameters should change based on what column and row the output is for.

这篇关于根据行标题和单元格值返回列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！