Google Sheets преобразует лист переменной ширины / высоты в список с помощью ARRAYFORMULA

Question

Я не могу понять, как создать обобщенную формулу для преобразования следующего:

Name  | cool | smart | funny | ... |
Bill  |      | x     | x     |     |
Sally | x    | x     |       |     |
Bob   | x    |       | x     |     |
Cindy |      |       |       |     |
Steve | x    |       | x     |     |
Carol |      |       | x     |     |
Bob   |      | x     |       |     |
...   |      |       |       |     |

На что-то вроде этого:

Bill:smart,funny
Sally:cool,smart
Bob:cool,funny
Cindy:
Steve:cool,funny
Carol:funny
Bob:smart

Я бы хотел, чтобы он мог работать с произвольным числом столбцов и строк. Есть ли способ достичь этого без необходимости "перетаскивания"? То есть с ARRAYFORMULAs.

Вот ссылка на пример Google Sheet.

Answer 1

Ну, это хакерский медведь, но это работает. Он будет работать для произвольных строк и произвольных столбцов без перетаскивания формул.

=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(TRANSPOSE(SPLIT(CONCATENATE({Data!A2:A&":", REGEXREPLACE(Data!B2:O, "x", REGEXREPLACE(Data!B1:O1, "$", ",")), IF(LEN(Data!A2:A)=LEN(Data!A2:A), "|")}), "|")), ",$", ""), "^:$", ""))

Несколько предостережений:

• Не должно быть никаких несвязанных данных ниже или справа от начала таблицы данных (т. Е. За исключением дополнительных строк или столбцов заголовка, данные должны быть в отдельном листе).
• Имена и метки не должны содержать "|", так как он используется внутри как разделенный символ (его можно изменить на другой символ)
• Данные не могут выходить за пределы данного столбца. В приведенной выше формуле он достигает максимума в столбце "O", но это может быть любой произвольный столбец (например, столбец "ZZ")

Я ввел формулу в работу - вы можете просмотреть таблицу, нажав здесь

Лист "Результат" имеет формулу и вывод, который вы хотели.

Answer 2

Надеюсь, это работает:

=ArrayFormula(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(A2:A8&": /")),"/"))&SUBSTITUTE(trim(transpose(query(transpose(IFERROR(ArrayFormula(HLOOKUP(REGEXREPLACE(B2:D8,"x","1")*1*COLUMN(B2:D8)*row(B2:D8)^0,{COLUMN(B2:D8);B1:D1},2,0)),"")),,counta(B1:D1))))," ",", "))

мой образец в действии =)

---

Обновить

Я понял, как сделать формулу короче:

=ARRAYFORMULA(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(A2:A8&": /")),"/"))&SUBSTITUTE(trim(transpose(query(transpose(REPT(B1:D1,B2:D8="x")),,counta(B1:D1))))," ",", "))

Формула выше работы такая же. Здесь используется хитрость, предложенная здесь.