Как транспонировать значения нескольких столбцов по группам между разделителями групп в смежных столбцах Google Sheets?

У меня есть следующие минимальные примерные данные (на самом деле 100 групп) в диапазоне A1:P9 (те же данные в диапазоне A14:A22):

С образцом A1:AR9:

2 61 219 2 4 2 : 61 219 26 26 26 94 21 33 4 26 26 26 94 2 2 : 154 26 40 19 3 2 21 33 14 1 2 3 : 87 39 54 38 26 32 38 26 32 87 39 54 38 26 23 23 4 6 28 2 154 26 2 2 40 19 14 87 39 54 38 26 32 38 26 32 87 39 54 38 26 1 23 2 23 4 4 3 6 20 28

Или образец A14:AQ22:

2 61 219 2 : 61 219 4 : 26 26 26 94 2 : 21 33 4 26 26 26 94 2 : 154 26 2 : 40 19 3 2 21 33 14 : 87 39 54 38 26 32 38 26 32 87 39 54 38 26 1 : 23 2 : 23 4 : 3 6 20 2 154 26 2 2 40 19 14 87 39 54 38 26 32 38 26 32 87 39 54 38 26 1 23 2 23 4 4 3 6 20 28

Мне нужен вывод, как показано в диапазоне Q1:AR3 или в диапазоне Q14:AQ16.

По сути, для каждой группы значений с разделителями/между ними в столбце A мне потребуется:

  • Промежуточные соседние значения в столбце B для горизонтального переноса
  • И смежное содержимое столбцов C-P (не менее 14 столбцов) должно быть «соединено» вместе по горизонтали последовательно «на группу», включая содержимое строки разделителя (в столбце A).
  • В качестве бонуса было бы очень приятно, если бы за транспонированными данными следовал :, а каждое подсодержимое столбцов от C до P также было бы разделено | (как показано на снимке экрана Q1:AR3 или Q14:AR16).

(Или, если это более осуществимо, как вариант, проще прочитать 2-ю модель, как в A14:AQ22).

Мне очень трудно составить формулу, чтобы прийти к ожидаемому результату.

Все, о чем я мог думать, было:

  • Транспонирование содержимого столбца B путем получения строк соседних ячеек со значениями в столбце A,
  • Объединение с буквой столбца,
  • Дублируя его в новый столбец и отфильтровывая пустые промежуточные ячейки,
  • Затем сдвиньте дублированный столбец на 1 ячейку вверх,
  • Затем объединив формулу TRANSPOSE, чтобы получить диапазон групп,
  • Затем, наконец, перенос всех групп из столбцов B в новый столбец.

(очень запутанный, но я не мог найти лучшего способа).

Чтобы добраться до этого входа:

=TRANSPOSE(B1:B3)
=TRANSPOSE(B4:B5)
=TRANSPOSE(B7:B9)

Это уже был очень ручной и подверженный ошибкам процесс, и все же я не мог успешно придумать, как сделать оставшееся содержимое, соединяющее столбцы C и P в формуле.

Я протестировал следующий подход, но он не работает, и было бы очень утомительно исправить его и реализовать на больших наборах данных:

=TRANSPOSE(B1:B3)&": "&JOIN( " | " , FILTER(C1:P1, NOT(C2:P2 = "") ))&JOIN( " | " , FILTER(C2:P2, NOT(C2:P2 = "") ))&JOIN( " | " , FILTER(C43:P3, NOT(C3:P3 = "") ))
=TRANSPOSE(B4:B5)&": "&JOIN( " | " , FILTER(C4:P4, NOT(C4:P4 = "") ))&JOIN( " | " , FILTER(C5:P5, NOT(C5:P5 = "") ))
=TRANSPOSE(B6:B9)&": "&JOIN( " | " , FILTER(C6:P6, NOT(C6:P6 = "") ))&JOIN( " | " , FILTER(C7:P7, NOT(C7:P7 = "") ))&JOIN( " | " , FILTER(C8:P8, NOT(C8:P8 = "") ))&JOIN( " | " , FILTER(C8:P8, NOT(C9:P9 = "") ))

Какой лучший подход в пользу ожидаемого результата? Предпочтительно с помощью формулы или, если это невозможно, с помощью скрипта.

Любая помощь приветствуется.

Извините, опечатки в таблице и на картинке. Только что исправил.

Lod 06.02.2023 00:07

можете поделиться образцом листа?

ztiaa 06.02.2023 00:07

Конечно. Вот образец листа: docs.google.com/spreadsheets/d/…

Lod 06.02.2023 00:09

Извините, ребята, за 1-ю таблицу в посте. Почему-то в конце смешно. Я оставлю это как есть, поскольку это второй раз, когда я пытаюсь исправить, если безрезультатно, и я думаю, что это должно быть достаточно ясно, чтобы понять. Если нет, пожалуйста, дайте мне знать. Спасибо!

Lod 06.02.2023 00:12
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
4
65
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Для примера 1 попробуйте следующее:

=LAMBDA(norm,MAP(UNIQUE(norm),LAMBDA(ζ,{TRANSPOSE(FILTER(B1:B9,norm=ζ)),":",SPLIT(BYROW(TRANSPOSE(FILTER(BYROW(C1:P9,LAMBDA(r,TEXTJOIN("ζ",1,r))),norm=ζ)),LAMBDA(rr,TEXTJOIN("γ|γ",1,rr))),"ζγ")})))(SORT(SCAN(,SORT(A1:A9,ROW(A1:A9),),LAMBDA(a,c,IF(c = "",a,c))),ROW(A1:A9),))

Другие вопросы по теме