Yaude Huang wrote:
你想要的文字組合，如...(恕刪)
拆解給你看
先把A轉換成杯數
=(C$2:E$4=G2)*B$2:B$4
組合字串
=CONCATENATE(C$1:E$1,"-",A$2:A$4,"-",(C$2:E$4=G2)*B$2:B$4,"杯")
加上過濾條件，去除0的部分
=FILTER(TOROW(CONCATENATE(C$1:E$1,"-",A$2:A$4,"-",(C$2:E$4=G2)*B$2:B$4,"杯")),TOROW((C$2:E$4=G2)*B$2:B$4)>0)
但是把另外兩組弄出來會發現上午下午會錯亂
主要是因為是由左到右，由上到下排序
加上TRANSPOSE轉個方向
最後用ARRAYTOTEXT把陣列串成文字
=ARRAYTOTEXT(FILTER(TOROW(TRANSPOSE(CONCATENATE(C$1:E$1,"-",A$2:A$4,"-",(C$2:E$4=G2)*B$2:B$4,"杯"))),TOROW(TRANSPOSE((C$2:E$4=G2)*B$2:B$4))>0),0)
剩下兩個欄位都複製貼上就好了
