英文:
Excel textjoin give me wrong output
问题
我相信我理解了真正的问题：我已经创建了两列：一列是名字（列“A”），另一列是姓氏（列“B”）。
作者想要一个由名字和姓氏组合而成的逗号分隔列表，其中两个名字之间用空格分隔。
为了实现这一点，可以创建一个辅助列，其中包含以下公式：=TEXTJOIN(" ",,A2:B2)
最终列表可以使用以下公式创建：=TEXTJOIN(", ",,C2:C6)（参见单元格“E2”）
...真正的问题是如何消除那个辅助列：以下公式由于明显的原因无法完成任务：=TEXTJOIN(", ",,TEXTJOIN(" ",,A2:B6))（参见单元格“E3”）
我的Excel表格：
我已经发布了预期输出的屏幕截图。
英文:
I believe I understand the real question here:
I have created two columns: one with the first name (column "A") and one with the last name (column "B").
The author wants a comma-separated list of the combinations of first and last names, where both names are separated by a space.
In order to do this, a helper column can be created, containing following formula: =TEXTJOIN(" ",,A2:B2)
The final list can be created, using the following formula: =TEXTJOIN(", ",,C2:C6) (see cell "E2")
... the real problem is how to eliminate that helper column: the following formula does not to the job, for obvious reasons: =TEXTJOIN(", ",,TEXTJOIN(" ",,A2:B6)) (see cell "E3")
My Excel sheet:
I have posted the screenshot of expected output
答案1
得分: 1
你需要两个TEXTJOIN()函数，然后求和。尝试以下公式，并根据叙述文本进行调整。
="There are 5 emp... " & TEXTJOIN(", ", 1, BYROW(A2:B6, LAMBDA(x, TEXTJOIN(" ", 1, x)))) & " and their salary is: " & SUM(C2:C6)
或者使用连接运算符：
="There are 5 emp... " & TEXTJOIN(", ", 1, A2:A6 & " " & B2:B6) & " and their salary is: " & SUM(C2:C6)
英文:
You need two TEXTJOIN() then sum. Try the following formula and make adjustment for narration texts.
="There are 5 emp... " & TEXTJOIN(", ",1, BYROW(A2:B6,LAMBDA(x,TEXTJOIN(" ",1,x)))) & " and their salary is: " & SUM(C2:C6)
Or using concatenate operator,
="There are 5 emp... " & TEXTJOIN(", ",1, A2:A6& " " &B2:B6) & " and their salary is: " & SUM(C2:C6)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论