英文:
Complex Cocatenation in gsheets
问题
我有一个表格，其中我正在尝试连接几列，只有当所需的任何一个值都不为空时才应进行连接。
这是电子表格链接。https://docs.google.com/spreadsheets/d/1lQUG4TmFTKghV8r6Gg3EilLx6zyuTkrNfnCatRVWp_U/edit#gid=189998773
我尝试使用以下公式，但无法获得所需的输出。
=MAP(scan(,D5:D,I5:I,lambda(a,b,d,if(or(a="",b="",d="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c))),D5:D,H5:H,I5:I,J5:J,K5:K,lambda(a,b,c,e,f,if(or(a="",b="",c="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c)))
请帮助！
英文:
I have a table to entries where I am trying to concatenate few columns and the concatenation should not happen when any one of the required values are empty
Here is the spreadsheet. https://docs.google.com/spreadsheets/d/1lQUG4TmFTKghV8r6Gg3EilLx6zyuTkrNfnCatRVWp_U/edit#gid=189998773
I did try to use the formula =MAP(scan(,D5:D,I5:I,lambda(a,b,d,if(or(a="",b="",d="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c))),D5:D,H5:H,I5:I,J5:J,K5:K,lambda(a,b,c,e,f,if(or(a="",b="",c="",and(e="",f="")),,if(f<>"",f,e)&" "&a&" "&b&" "&c)))
But not able to get the the desired output.
Please help!
答案1
得分: 2
您可以通过省略 SCAN 来简化您的公式
=MAP(D5:D,H5:H,I5:I,J5:J,K5:K,
LAMBDA(dd,hh,ii,jj,kk,
IF(OR(dd="",hh="",ii="",AND(jj="",kk="")),,dd&"-"&hh&"-"&ii&"-"&if(kk<>"",kk,jj))))
(供将来参考：尝试为您的 LAMBDA 函数适当命名)
英文:
You can simplify your formula by omitting SCAN
=MAP(D5:D,H5:H,I5:I,J5:J,K5:K,
LAMBDA(dd,hh,ii,jj,kk,
IF(OR(dd="",hh="",ii="",AND(jj="",kk="")),,dd&"-"&hh&"-"&ii&"-"&if(kk<>"",kk,jj))))
(for future reference: try naming your LAMBDAs accordingly)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论