函数A用逗号连接数字和条件

laoau138

函数A用逗号连接数字和条件

砂海

不用辅助单元格的话 , 直接用函数貌似要写很长很长 .

laoau138

砂海 发表于 2016-9-12 09:13
不用辅助单元格的话 , 直接用函数貌似要写很长很长 .

用辅助单元格的话, 要怎样写啊

砂海

算了,用辅助 也不容易

直接写
N1

=A1&IF(B1<>"",",","")&B1&IF(C1<>"",",","")&C1&IF(D1<>"",",","")&D1&IF(E1<>"",",","")&E1&IF(F1<>"",",","")&F1&IF(G1<>"",",","")&G1&IF(H1<>"",",","")&H1&IF(I1<>"",",","")&I1&IF(J1<>"",",","")&J1&IF(K1<>"",",","")&K1&IF(L1<>"",",","")&L1&IF(M1<>"",",","")&"/0~1~2~3"&IF(COUNT(A1:L1)>10,"~4","")

本公式要求是m列为空

laoau138

砂海 发表于 2016-9-12 09:39
算了,用辅助 也不容易

直接写

=MID(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(A1:F1*100^(6-COLUMN(A:F))),REPT("!,00",6))&TEXT(SUM(G1:L1*100^(6-COLUMN(A:F))),REPT("!,00",6)),",00",),",0",","),2,36)
&"/0~1~2~3"&IF(COUNT(A1:L1)>10,"~4","")

数组公式按三键后下拉