ArrayFormula a Фильтр в объединении (таблицы Google)

Я провел долгий поиск и не смог найти то, что я ищу. Может быть, кто-то может помочь?

У меня есть эта формула в моей электронной таблице Google (я объясню, что она делает ниже):

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

На листе 1 приведена таблица: столбец A - это первые имена (например, "Джеймс"), а столбец B - некоторый комментарий (например, "головная боль"). У Джеймса (или любого другого) может быть несколько рядов с разными комментариями в каждом (например, у Джеймса есть 2 ряда, один из которых говорит "Головная боль", а другой - "Боль в колене".)

На листе 2, столбец A, у меня есть список имен, которые появляются в Sheet1 (используя формулу '=UNIQUE'). А1 говорит "Джеймс". В ячейку B1 вводим приведенную выше формулу.

Результат - почти то, что я хочу. Он объединяет все комментарии Джеймса в одну ячейку с пробелом " " между каждым комментарием. Таким образом, результат в ячейке B1: "Головная боль, боль в колене".

Тем не менее, я должен перетащить эту формулу во все ячейки ниже. Кто-нибудь знает, как я могу сделать это, как и все другие ArrayFormulas, которые я использовал в прошлом, где формула автоматически заполняет все ячейки ниже? Я попытался сделать это формулой массива, но безуспешно.

Я также играл с этой формулой, которая дает мне тот же результат "Головная боль, боль в колене", но формула все равно не будет копироваться в ячейки ниже.

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

Если кто-нибудь знает, как этого добиться, я буду очень признателен - ваша ценная помощь будет высоко оценена.

Спасибо за внимание!

Источник

3 ответа

Решение

Обычно немного сложно применять агрегирующую функцию, такую ​​как CONCATENATE, строка за строкой.

=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(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT), TRANSPOSE(Sheet1!B:B&" "));filter (A: A, A: A<> "")= TRANSPOSE (Sheet1! A: A)) & REPT ("" & CHAR (9); TRANSPOSE (ROW (Sheet1! A: A)) = ROWS (Sheet1! A: A))); СИМ (9)))))

Источник

Вместо обходных хаков я реализовал простойjoinMatching(matches, values, texts, [sep])функция в скрипте Google Apps .

В вашем случае это будет просто =joinMatching(A1:A, Sheet1!A1:A, Sheet1!B1:B, " ").

Источник:

      // Google Apps Script to join texts in a range where values in second range equal to the provided match value
// Solves the need for `arrayformula(join(',', filter()))`, which does not work in Google Sheets
// Instead you can pass a range of match values and get a range of joined texts back

const identity = data => data

const onRange = (data, fn, args, combine = identity) =>
  Array.isArray(data)
    ? combine(data.map(value => onRange(value, fn, args)))
    : fn(data, ...(args || []))

const _joinMatching = (match, values, texts, sep = '\n') => {
  const columns = texts[0]?.length
  if (!columns) return ''
  const row = i => Math.floor(i / columns)
  const col = i => i % columns
  const value = i => values[row(i)][col(i)]
  return (
    // JSON.stringify(match) +
    texts
      .flat()
      // .map((t, i) => `[${row(i)}:${col(i)}] ${t} (${JSON.stringify(value(i))})`)
      .filter((_, i) => value(i) === match)
      .join(sep)
  )
}

const joinMatching = (matches, values, texts, sep) =>
  onRange(matches, _joinMatching, [values, texts, sep])```
Источник
Другие вопросы по тегам