英文:
I want to find out header name in MS Excel on the basis of starting value of each row
问题
根据每行的起始值，我想找出标题名称。
例如：
根据下面的截图，第2行的起始值在G列，然后在N列中，公式应该检索2028作为标题名称。
谢谢您提前的帮助！
英文:
I want to find out header name on the basis of starting value of each rows.
For Example:
As per below screenshot, the start point value in row 2 is from column G then in Column N the formula should retrieve 2028 as the header name.
Thanks for your help in advance
答案1
得分: 2
你可以尝试使用 FILTER() 函数。
=INDEX(FILTER($B$1:$G$1,B2:G2>0),1,1)
对于动态溢出数组-
=BYROW(B2:G5,LAMBDA(x,INDEX(FILTER($B$1:$G$1,x>0),1,1)))
英文:
You can try FILTER() function.
=INDEX(FILTER($B$1:$G$1,B2:G2>0),1,1)
For dynamic spill array-
=BYROW(B2:G5,LAMBDA(x,INDEX(FILTER($B$1:$G$1,x>0),1,1)))
答案2
得分: 1
首次出现非零值在一行中
Excel (结构化) 表格
- 在 Excel 表格中，溢出的公式不起作用。
- 在 Excel 表格中，所有标题都是字符串。
- 假设年份按升序排列，连续在列中，并且是唯一的数值标题。它们可以位于表格标题的“任何位置”。
- 调整表格名称。
- 在将公式输入到其第一行之前，最好清除“年份”列的内容。其余行和任何新增行将自动填充。
=LET(h,Table1[#Headers],d,Table1[@],ne,0,nf,"No",
nh,IFERROR(--h,""),iMin,XMATCH(MIN(nh),nh),
iMax,XMATCH(MAX(nh),nh),s,SEQUENCE(,iMax-iMin+1,iMin),
hd,INDEX(nh,,s),dd,INDEX(d,,s),
TAKE(FILTER(hd,dd<>ne,nf),,1))
表格范围
- 在
M10中的以下公式延伸到M14。
=LET(Data,B9:L14,ne,0,nf,"No",
h,TAKE(Data,1),d,DROP(Data,1),
BYROW(d,LAMBDA(r,TAKE(FILTER(h,r<>ne,nf),,1))))
英文:
First Occurrence of a Non-Zero Value in a Row
Excel (Structured) Table
- In an Excel table, spilling formulas don't work.
- In an Excel table, all headers are strings.
- It is assumed that the years are in ascending order and in consecutive columns and are the only numeric headers. They can be located 'anywhere' in the table's headers.
- Adjust the table name.
- Best clear the contents of the
Yearcolumn before entering the formula into its first row. The remaining rows and any newly added rows will get populated automatically.
=LET(h,Table1[#Headers],d,Table1[@],ne,0,nf,"No",
nh,IFERROR(--h,""),iMin,XMATCH(MIN(nh),nh),
iMax,XMATCH(MAX(nh),nh),s,SEQUENCE(,iMax-iMin+1,iMin),
hd,INDEX(nh,,s),dd,INDEX(d,,s),
TAKE(FILTER(hd,dd<>ne,nf),,1))
Table Range
- The following formula in
M10spills down toM14.
=LET(Data,B9:L14,ne,0,nf,"No",
h,TAKE(Data,1),d,DROP(Data,1),
BYROW(d,LAMBDA(r,TAKE(FILTER(h,r<>ne,nf),,1))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论