再會！
我正在尋求您的幫助。達到以下輸出的正確公式是什么？先感謝您。
Data
Ref Activity 1 Activity 2 Activity 3
001 23-Oct-2021 25-Oct-2021 5-Nov-2021
002 25-Oct-2021 30-Oct-2021 5-Nov-2021
003 25-Oct-2021 5-Nov-2021 6-Nov-2021
Output
23-Oct-2021
001 - Activity 1
25-Oct-2021
001 - Activity 2
002 - Activity 1
003 - Activity 1
uj5u.com熱心網友回復：
嘗試：
=INDEX(QUERY(FLATTEN(SPLIT(QUERY(FLATTEN(TRANSPOSE(QUERY(SPLIT(FLATTEN(
IF(B2:D="",," ?"&TO_TEXT(B2:D)&"?"&A2:A&" - "&B1:D1)), "?"),
"select max(Col2) where Col2 is not null group by Col2 pivot Col1"))),
"where Col1 is not null"), "?")),
"where Col1 is not null offset 1", 0))
更新：
=INDEX(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(SPLIT(FLATTEN(
QUERY(SUBSTITUTE(QUERY(SPLIT(FLATTEN(
IF(C4:E6="",,TO_TEXT(C4:E6)&"???"&B4:B6&" - "&C3:E3)), "?"),
"select max(Col2) where Col2 is not null group by Col2 pivot Col1"),
" ", CHAR(13)),,9^9)), "?"))), " ", CHAR(10)), "?", ))
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/338180.html