我需要根据一行中单元格的值从一个表返回多个列标题 [英] I need to return multiple column headers from a table based on the value of the cells within a row

问题描述

我在Excel中有一个表，其设置如下:

I have a table in Excel, that is set up as follows;

我需要做的是在另一张纸上，根据选择了该人姓名的下拉框的值(例如，测试1，测试2等)，对该表格，并在下拉菜单旁边的单元格中返回标头，其中单元格中的值为Y.

What I need to be able to do, is on another sheet, based on the value of a drop down box where the person's name is selected (i.e. Test 1, Test 2, etc...), perform a lookup against the table and in the cell next to the drop down return the headers where the value in the Cell is Y.

例如，基于上表，如果从下拉列表中选择测试1"，则返回的值应为

For example, based on the table above, If Test 1 was selected from the drop down then the value returned should be

技能1，技能4

Skill 1, Skill 4

任何对此的建议将不胜感激.我已尝试按照此帖子，但未成功.

Any advice on this would be much appreciated. I have tried to follow the instructions in the answer on this post but have been unsuccessful.

推荐答案

尝试以下 UDF():

Public Function GetHeaders(r1 As Range, r2 As Range) As String
   Dim r As Range, s As String, rr As Range, rTOP As Range
   GetHeaders = ""
   s = r1.Text
   Set rTOP = r2.Rows(1).Cells

   For Each r In r2.Columns(1).Cells
      If r.Value = s Then
         For Each rr In Intersect(r.EntireRow, r2).Cells
            If rr.Value = "Y" Then
               GetHeaders = GetHeaders & "," & Intersect(rr.EntireColumn, rTOP).Value
            End If
         Next rr
      End If
   Next r
   GetHeaders = Mid(GetHeaders, 2)
End Function

因此，将数据放在 Sheet1 中，将下拉列表放在 Sheet2 单元格 B1 和 Sheet2 中单元格 A1 输入:

So with the data in Sheet1, put the pull-down in Sheet2 cell B1 and in Sheet2 cell A1 enter:

=GetHeaders(B1,Sheet1!A1:F5)

注意::我们传递了整个表，包括标题行/列.

NOTICE: we pass the entire table, including header rows / columns.

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