如果没有VBA的列的单元格中有值，则将列标题显示到一行 [英] Show the column header to a row if there is value in cells of that columns without VBA

问题描述

更新1:

因为我的问题不清楚，所以我发表第二个例子

Because my question is not clear, so I post second example

由于第2行的第04项值为1，因此获取项名称=第04项.它是随机的，并且具有大量列(500).

Because row 2, there is value 1 at Item 04 so the Get Item Name = Item 04. It is random and have a large number of columns (500).

问题:

如果要向该标题下的单元格输入任何值，我想有一种方法来获取列标题.请注意，如果第2行和第1列具有值，则第2行的其他单元格将没有任何值(0除外).

I would like to have a way to get a column header if there is any value input to the cells under that header. Please note that if at row 2 and column 1 has value, then other cell of row 2 will not have any value (other than 0).

很难用语言解释这个问题，所以我创建了一个例子.

It is hard to explain the problem in words so I have created an example.

推荐答案

我在上面的评论中发布了一个公式，您可能没有看过.就是这样:

I posted a formula in the comments above, you may not have seen it. This is it:

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2<>"",0),0)))

将获取已填充单元格的第一个实例的标头-如果您具有数字值并想忽略零，则将其更改为

that will get the header for the first instance of a populated cell - if you have numeric values and want to ignore zeroes change to

=IF(COUNTA($B2:$D2)=0,"",INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)))

无论哪种方式，公式都可以扩展到所需的范围

Either way the formula can be extended to as large a range as you need

....如果有500列，则可以使用IFERROR缩短一点

.....and if you have 500 columns you could use IFERROR to shorten a little

=IFERROR(INDEX($B$1:$D$1,MATCH(TRUE,INDEX($B2:$D2>0,0),0)),"")

这篇关于如果没有VBA的列的单元格中有值，则将列标题显示到一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！