Объединить текст в ArrayFormula

Question

У меня есть таблица с использованием Google Sheets. Он имеет три столбца, которые всегда будут иметь нулевое значение или определенное значение для этого столбца. Каждая строка будет иметь одно, два или три значения; у него никогда не будет трех нулевых значений в одной строке. В четвертом столбце я хочу ArrayFormula, который объединит эти значения и разделит значения запятой, если их больше одного.

Вот фотография того, что я пытаюсь сделать.

До сих пор я пробовал несколько идей, и эта формула является самой близкой, которую я получил до сих пор, но она все еще не совсем работает правильно; Я думаю, что каждый столбец рассматривается как массив перед объединением, а не как функция строка за строкой. Я использую функцию LEN вместо A2="" или ISBLANK(A2), потому что столбцы AC также являются ArrayFormulas. Я понимаю, что это, вероятно, не самая эффективная формула для использования, но я думаю, что она охватывает все возможности. Я определенно открыт для других идей.

={"Focus";
  ArayFormula(
    IFS(
      $A$2:$A="", "",
      (LEN(A2:A)>0 & LEN(B2:B)>0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, A2:A, B2:B, C2:C),
      (LEN(A2:A)>0 & LEN(B2:B)>0 & LEN(C2:C)=0), TEXTJOIN(", ", TRUE, A2:A, B2:B),
      (LEN(A2:A)>0 & LEN(B2:B)=0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, A2:A, C2:C),
      (LEN(A2:A)=0 & LEN(B2:B)>0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, B2:B, C2:C),
      (LEN(A2:A)>0 & LEN(B2:B)=0 & LEN(C2:C)=0), A2:A,
      (LEN(A2:A)=0 & LEN(B2:B)>0 & LEN(C2:C)=0), B2:B,
      (LEN(A2:A)=0 & LEN(B2:B)=0 & LEN(C2:C)>0), C2:C
    )
  )
}

Можно ли добиться этого с помощью Google Sheets?

Answer 1

Образец файла

Пожалуйста, попробуй:

=ARRAYFORMULA(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(A2:C,ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0)))),,2^99)))," ",", "))

Заметки:

• Формула будет работать некорректно, если в некоторых именах есть пробел: например, "Aston Martin"
• Поэтому, если у вас есть пробелы, попробуйте это:

=ARRAYFORMULA(SUBSTITUTE( SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(SUBSTITUTE(A2:C," ",char(9)),ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0)))),,2^99)))," ",", "), CHAR(9)," "))

РЕДАКТИРОВАТЬ

Заметил более короткий вариант (без *COLUMN(A2:C)^0) буду работать:

=ARRAYFORMULA(SUBSTITUTE( SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(SUBSTITUTE(A2:C," ",char(9)),ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C),0)))),,2^99)))," ",", "), CHAR(9)," "))

Заметки:

• Я использовал старый трюк, чтобы соединить строки с array-formula, Посмотреть образец файла

---

Пояснения

Если вам нравится разбираться в любой многоуровневой формуле, лучше всего разбить ее на части:

Часть 1. Фильтр данных

1. FILTER(any_columns,ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0))), это мой способ ограничить диапазон данных.
2. Диапазон открыт, означает, что он начинается со второго ряда (A2) и заканчивается в любом ряду.
3. На этом шаге я хочу получить ограниченный массив, чтобы уменьшить объем работы, которую должна выполнять формула. Это сделано с условием, if,
4. ROW(A2:C) должен быть меньше или равен максимальному ряду данных.MAX(IF(LEN(A2:C), some_rows) дает максимальный ряд.
5. If(len.. Часть проверяет, есть ли в ячейке какой-либо текст внутри.
6. Заметка some_rows часть:MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0)))),,2^99))),ROW(A2:C) должны быть умножены на столбцы, потому что filter формула принимает только один ряд в свое состояние. Вот почему я умножаю наCOLUMN(A2:C)^0 который является столбцы с 1 с. Редактировать. Теперь заметил, что формула прекрасно работает без *COLUMN(A2:C)^0так что это перебор.

Часть 2. Присоединяйтесь к тексту

7. query Формула имеет 3 аргумента: data, query_text и number_of_header_rows.

8. data сделано с фильтром.

9. query_text пусто, что дает нам эквивалент выбрать все ("select *").

10. А количество строк заголовка какое-то большое число (2^99). Это хитрость: когда query имеет больше заголовков, чем один ряд, он соединит их с пробелом.

11. После объединения, transpose Функция преобразует результат обратно в столбец.

Часть 3. Замена и обшивка

12. Функция trim удаляет лишние пробелы.

13. Затем мы заменяем пробелы разделителем: ", ", Вот почему формулу необходимо изменить, если пробелы находятся в строках. Правильный результат: "Форд, Астон Мартин". Неправильно: "Форд, Астон, Мартин". Но если мы ранее заменим пробелы на некоторый символ (char(9) это вкладка), то мы не заменим его на этом шаге.