ArrayFormula加入过滤器（Google Spreadsheets） [英] ArrayFormula a Filter in a Join (Google Spreadsheets)

问题描述

我做了冗长的搜索，找不到要找的内容。也许有人可以帮忙吗？

我在Google Spreadsheet中有这个公式（我会在下面解释它的作用）：

  = Join（，FILTER（Sheet1！B：B; Sheet1！A：A = A1））
  

在表1中有一张表：列A是名字（例如'James'），列B是一些注释（例如'头痛'）。詹姆斯（或任何其他人）可能会有多行，每一行都有不同的评论（例如，詹姆斯有两排，一个说着'头痛'，另一个说'膝盖疼痛'）。

在工作表2列A中，我有一个名称出现在Sheet1中的列表（使用'= UNIQUE'公式）。 A1说'詹姆斯'。在B1单元格中输入上面的公式。

结果几乎就是我想要的。它将詹姆斯的所有评论加入到一个单元格中，每个评论之间有一个空格。所以在B1单元格中的结果是：'头疼膝盖疼痛'。

但是，我必须将此公式拖到下面的所有单元格中。有谁知道我可以如何使它像过去使用过的所有其他ArrayFormulas一样，公式自动填充下面的所有单元格？我已经尝试过将它作为一个数组公式，但没有成功。

我也一直在玩这个公式，它给了我同样的结果'头痛膝盖疼痛'，但该公式仍然不会复制到下面的单元格中。

  = SUBSTITUTE（Arrayformula（concatenate（FILTER（Sheet1！B：B ; Sheet1！A：A = A1）&;|））;|;）
  

如果有人知道如何做到这一点，我将非常感激 - 您的宝贵帮助将得到很大的赞赏。

感谢您的期待！

解决方案

使用像CONCATENATE这样的聚合函数逐行地应用通常有点复杂。 b（b）（b）（b）（b）（b）（b）（b）（b） & REPT（& CHAR（9）; TRANSPOSE（ROW（Sheet1！A：A））= ROWS（Sheet1！A：A）））; CHAR（9）））））

（编辑：道歉我没有机会测试对于错误/错别字，如果你能确认它是否有效，将会删除这行）

I've done a lengthy search and couldn't find what I'm looking for. Maybe someone out there can kindly help?

I have this formula in my Google Spreadsheet (I will explain what it does below):

=Join(" ",FILTER(Sheet1!B:B;Sheet1!A:A=A1))

In Sheet 1 is a table: Column A is first names (e.g. 'James') and Column B is some comment (e.g. 'Headache'). James (or anyone else) may have multiple rows with different comments in each one (e.g James has 2 rows, one saying 'Headache' and another saying 'Knee pain'.)

In sheet 2, column A, I have a list of the names that appear in Sheet1 (Using the '=UNIQUE' formula). A1 says 'James'. In cell B1 I input the above formula.

The result is almost exactly what I want. It joins all of James' comments into one cell, with a space " " between each comment. So the result in cell B1 is: 'Headache Knee pain'.

However, I have to drag this formula to all the cells below. Does anyone know how I can make this like all the other ArrayFormulas I've used in the past, where the formula automatically fills all the cells below? I have tried making it an array formula but with no success.

I have also been playing around with this formula which gives me the same result 'Headache Knee pain', but the formula still won't copy into the cells below.

=SUBSTITUTE(Arrayformula(concatenate(FILTER(Sheet1!B:B;Sheet1!A:A=A1)&" "; "|"));" |";"")

If anyone knows how to achieve this I will be very grateful indeed - your valuable help will be much appreciated.

Thanks for looking!

解决方案

It is generally a bit complicated to apply an aggregating function like CONCATENATE row-by-row.

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(Sheet1!B:B&" ");A:A=TRANSPOSE(Sheet1!A:A))&REPT(" "&CHAR(9);TRANSPOSE(ROW(Sheet1!A:A))=ROWS(Sheet1!A:A)));CHAR(9)))))

(edit: apologies I haven't had the opportunity to test for bugs/typos, will remove this line if you can confirm it works)

这篇关于ArrayFormula加入过滤器（Google Spreadsheets）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！