英文:
Excel TEXTJOIN Range, Order is by numbering
问题
我有一个范围，我想要使用TEXTJOIN函数，分隔符是CHAR(10)（换行符）
这些值是标题名称，顺序是根据行上的编号来确定的。
如果值为空，则忽略。
请参见下面的图片。
任何帮助将不胜感激。
英文:
I have a range that I want to use TEXTJOIN on with delimiter CHAR(10) (linebreak)
The values are the header names and the order is by the numbering that is on the row.
If values are empty then ignore.
see image below.
any help will be much appreciated.
答案1
得分: 4
=TEXTJOIN(CHAR(10),TRUE,SORTBY(FILTER($A$1:$D$1,A2:D2),FILTER(A2:D2,A2:D2),1))
这将放在单元格E2中，然后您可以拖动公式向下。它根据行中是否有水果名称的排名数字来筛选它们，并删除没有排名数字的水果（例如第4行的Orange和Peach）。然后，它按升序数字排序并使用换行符连接结果。
英文:
=TEXTJOIN(CHAR(10),TRUE,SORTBY(FILTER($A$1:$D$1,A2:D2),FILTER(A2:D2,A2:D2),1))
This would go in E2 and then you can drag the formula down. It filters the fruit names according to if there are rank numbers for them in that row, and removes any that don't (e.g. Orange and Peach on row 4). It then sorts by ascending number and joins the results with a line break.
答案2
得分: 1
以下是代码的中文翻译：
Public Function makeList(items As Range, idxs As Range) As String
Dim c As Long, ccnt As Long, i As Variant
ccnt = items.Columns.CountLarge
For c = 1 To ccnt
i = idxs(1, c)
If i <> vbNullString And IsNumeric(i) Then
If i >= 1 And i <= ccnt Then
makeList = makeList & IIf(makeList = vbNullString, vbNullString, vbLf) & items(1, i)
End If
End If
Next
End Function
如果需要进一步的翻译或解释，请告诉我。
英文:
Public Function makeList(items As Range, idxs As Range) As String
Dim c As Long, ccnt As Long, i As Variant
ccnt = items.Columns.CountLarge
For c = 1 To ccnt
i = idxs(1, c)
If i <> vbNullString And IsNumeric(i) Then
If i >= 1 And i <= ccnt Then
makeList = makeList & IIf(makeList = vbNullString, vbNullString, vbLf) & items(1, i)
End If
End If
Next
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论