ArrayFormula 联接中的过滤器(Google 电子表格) [英] ArrayFormula a Filter in a Join (Google Spreadsheets)

问题描述

我搜索了很长时间，没有找到我要找的东西.也许有人可以提供帮助?

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

我的 Google 电子表格中有这个公式(我将在下面解释它的作用):

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

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

工作表 1 中有一个表格:A 列是名字(例如James")，B 列是一些评论(例如头痛").詹姆斯(或其他任何人)可能有多行，每一行都有不同的评论(例如，詹姆斯有 2 行，一个说头痛"，另一个说膝盖疼痛".)

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'.)

在工作表 2 的 A 列中，我有一个出现在工作表 1 中的名称列表(使用=UNIQUE"公式).A1 说詹姆斯".在单元格 B1 中，我输入上述公式.

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.

结果几乎正是我想要的.它将 James 的所有评论合并到一个单元格中，每个评论之间有一个空格".因此，单元格 B1 中的结果是:头痛膝痛".

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'.

但是，我必须将此公式拖到下面的所有单元格中.有谁知道我如何像过去使用的所有其他 ArrayFormulas 一样制作它，其中公式会自动填充下面的所有单元格?我曾尝试将其设为数组公式，但没有成功.

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.

感谢您的关注！

推荐答案

应用像 CONCATENATE row-by-row 这样的聚合函数通常有点复杂.

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)))))

(抱歉我没有机会测试错误/错别字，如果您能确认它有效，将删除此行)

这篇关于ArrayFormula 联接中的过滤器(Google 电子表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！