Условное преобразование в Excel
Как преобразовать строку в Excel на основе условия с помощью формулы. Как здесь, три столбца A, B, C. Если столбец содержит «да», то имя заголовка добавляется в столбец «Действия» в новой строке. Вот пример
До:
name | A | B | C
name1 | yes | yes | no
name2 | no | yes | yes
name3 | yes | no | no
После:
name | Activities
name1 | A
| B
name2 | B
| C
name3 | A
теперь напиши формулу
Мне будет полезно, если вы предоставите какой-либо ресурс, спасибо.
Ресурс находится здесь, поэтому вы можете искать вопросы и ответы, используя предложенные мной слова.
«теперь напиши формулу»... Возможно, вам захочется немного перефразировать эту строку.
=LET(y,B2:D4 = "yes",HSTACK(TOCOL(IFS(y,A2:A4),2),TOCOL(IFS(y,B1:D1),2))) или имена нужны пробелами на случай повторов?
Ответы 1
Если вам нужно, чтобы в случае повторяющихся имен были пробелы:
=LET(y,B2:D4 = "yes",a,TOCOL(IFS(y,A2:A4),2),HSTACK(IF(XMATCH(a,a)=SEQUENCE(ROWS(a)),a,""),TOCOL(IFS(y,B1:D1),2)))
Где IFS используется для создания массива, возвращающего name строки, где значение в диапазоне B2:D4 равно тексту yes. В противном случае он возвращает ошибку.
Завернутый в TOCOL с аргументом 2 (пропустить значения ошибок) возвращает сглаженные имена массива.
Та же логика используется для значений заголовка.
Чтобы не повторять значения имен, мы применяем XMATCH к рассчитанному массиву имен. Это возвращает массив позиции, в которой имя впервые встречается в массиве. Если эта позиция равна последовательности строк массива, возвращается имя, в противном случае — пустое значение.
Еще:
=LET(y,B2:D4 = "yes",HSTACK(TOCOL(IFS(y,A2:A4),2),TOCOL(IFS(y,B1:D1),2)))
Отредактируйте, включив решение для более старой версии Excel, которое не так просто, как в Office 365:
По именам:
=IFERROR(TEXTJOIN(,,IF(AGGREGATE(15,6,(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1/ISNUMBER(LEN(IF($B$2:$D$4 = "yes",$A$2:$A$4,NA()))),ROW(A1))=(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1,IF($B$2:$D$4 = "yes",$A$2:$A$4,NA()),"")),"")
Для заголовков заголовков:
=IFERROR(TEXTJOIN(,,IF(AGGREGATE(15,6,(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1/ISNUMBER(LEN(IF($B$2:$D$4 = "yes",$B$1:$D$1,NA()))),ROW(A1))=(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1,IF($B$2:$D$4 = "yes",$B$1:$D$1,NA()),"")),"")
Я думаю, что оба значения необходимо ввести с помощью ctrl+shift+enter (формулы массива).
какая версия Excel поддерживается для этих функций?
Office 365 (а может быть, Excel 2021?)
@mestu, ты используешь последнюю лицензионную версию?
я использую лицензионную версию Professional Plus 2019
@mestu Я добавил редактирование, включив решение для более старой версии Excel. Вам действительно нужно упомянуть свою версию в своем сообщении, если она не последняя, иначе опубликованные ответы могут быть хорошими, но не подходящими для вас.
Поищите здесь вопросы о транспонировании или индексировании с совпадением.