问题描述
我正在尝试基于多个列进行TEXTJOIN，在这种情况下，列B和D在选定范围内(如果列A = G9并且列C = H9)，而忽略列D中不在G2范围内的项目:G5(如果D列= G2:G5范围内的项目).我尝试的一切似乎都导致NA#
I am trying to do a TEXTJOIN based on multiple columns, in this case Column B and D within a selected range (If column A = G9 and column C = H9) while ignoring items in Column D that are not in range G2:G5 (if Column D = an item in range G2:G5). Everything I try seems to result in NA#
推荐答案
这项工作是否可行:
=TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))
通过
很遗憾，我没有textjoin，也无法自我测试.
Unfortunately I don't have textjoin and am unable to test myself.
为了详细说明，我相信您需要 TRANSPOSE()函数.现在，您正在将垂直范围与垂直范围进行比较.由于要比较的数组大小不同，这可能会导致问题，即 N/A#错误.我自己对此行为感到困惑，并在此处上对此问题提出了疑问.给出的答案对于阐述此Excel行为非常有用.
To elaborate, I beleive you are needing the TRANSPOSE() function. Right now you are comparing a vertical range against a vertical range. This can cause problems, the N/A# error, due to the different size of the arrays you are comparing. I myself was confused by this behaviour and asked a question on this matter on here. The answer given was very usefull to elaborate on this Excel behaviour.
希望这可以解决您的问题:)
Hopefully this solves your issue :)
编辑
我希望我可以尝试使用 TEXTJOIN()来帮助您，但是要获得一个临时补丁，直到有人可以进一步帮助您，您可以尝试实现一些 SUBSTITUTE()函数可捕获 FALSE 值，如下所示:
I wish I could play around with TEXTJOIN() to help you out, but to have a temporary patch until someone actually can help you further, you could try implement some SUBSTITUTE() functions to catch the FALSE values like so:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))," | FALSE ",""),"| FALSE",""),"FALSE | ","")
输入为数组
这篇关于基于多个列和多个IF条件的TEXTJOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！