从数组公式返回逗号分隔列表 [英] Return a Comma-Separated List from an Array Formula

问题描述

我可能很难在VBA中解决这个问题，但如果可能的话，宁可不必使用这种方法，而且这似乎是Excel的内置函数应该能够管理的东西。

我有一个电子表格，我们最近停止打印，我们将用来验证来自我们各个网站的数据，以及几天。列标题对应于站点号码（此电子表格为$ D $ 1：$ AU $ 1），行为日期（12月份为$ A $ 3：$ A $ 24 - 我们不在周末进行此数据验证）。 B列包含其信息应该在12/4的信息，我们应该从每个站点的12/2收到信息。

在过去，我们用C栏写出哪些站点在后面。如果站点3仅在第4位从12/1发送信息，我们将在该列中写入3。我想继续这个公约，因为这是办公室理解的。变化是坏的，所有的。

到目前为止，我自己搞砸了，写了一个数组公式，返回 {0,0， 1,0，...，0} 如果只有站点3在后面。该公式为 = IFERROR（SEARCH（B3，D3：AU3）-SEARCH（B3，D3：AU3），1）

$ b $从这里，这样做是微不足道的。 = IF（ISBLANK（D4），，INDEX（D1：AU1,1，MATCH（1，IFERROR（SEARCH（B3，D3：AU3） SEARCH（B3，D3：AU3），1）），0））这个工作很棒，只要有一个站点落后。如果我们有一个以上的网站，它返回第一个值（如预期）。

如果站点3和6都在后面，我们想看到3， 6（或任何其他人类可读格式），但我发现唯一的解决方案是编写一个自定义VBA脚本来连接数组。谢谢，

Adam

解决方案

在BA3中尝试这个数组公式

= IFERROR（INDEX（$ D1： $ AU1，SMALL（IF（ISERROR（SEARCH（$ B3，$ D3：$ AU3）），COLUMN（$ D3：$ AU3）列（$ D3）+1），柱（$ BA3：BA3））） ）

确认与 CTRL + SHIFT + / kbd>并复制以获得所有匹配

如您所言，您可以将这些连接到C3

I could probably puzzle this one out in VBA, but would rather not have to use that approach if possible, and it seems like this is something Excel's built-in functions should be able to manage.

I have a spreadsheet we recently stopped printing that we would use to verify data came in from our various sites, and for which days. Column headers correspond to site numbers ($D$1:$AU$1 for this spreadsheet) and rows are for dates ($A$3:$A$24 for the month of December -- we don't do this data validation on weekends). The "B" column contains the date whose information SHOULD have come in, for example, on 12/4, we should receive the information from 12/2 at each site.

In the past, we used column "C" to write which sites were behind. If Site 3 only sent in information from 12/1 on the 4th, we would write a "3" in that column. I'd like to continue this convention, since it's what the office understands. Change Is Bad and all that.

So far I've muddled through on my own and wrote an Array Formula that returns {0,0,1,0,...,0} if only site 3 is behind. That formula is =IFERROR(SEARCH(B3,D3:AU3)-SEARCH(B3,D3:AU3),1)

From here it's trivial to do =IF(ISBLANK(D4),"",INDEX(D1:AU1,1,MATCH(1,IFERROR(SEARCH(B3,D3:AU3)-SEARCH(B3,D3:AU3),1)),0)) which works great, so long as there's only one site that's behind. If we have more than one site behind, it returns the first value (as expected).

If both sites 3 and 6 are behind, we want to see "3, 6" (or any other human-readable format) but the only solutions I found are to write a custom VBA script to concat an array. I'd rather stay away from custom VBA if humanly possible.

Thanks,
Adam

解决方案

Try this "array formula" in BA3

=IFERROR(INDEX($D1:$AU1,SMALL(IF(ISERROR(SEARCH($B3,$D3:$AU3)),COLUMN($D3:$AU3)-COLUMN($D3)+1),COLUMNS($BA3:BA3))),"")

confirm with CTRL+SHIFT+ENTER and copy across to get all matches

As you say you can then concatenate those back to C3

这篇关于从数组公式返回逗号分隔列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！