EXCEL 中如何对非空白单元格的字符求和？ 例： A B C D E F 品目1 1 1 2 返回结果为B/D/F

Question

如题所述

Answer 1

假设源数据在A:G列
H列得出结果：
I:N列作为辅助列
复制下列公式到I2单元格
=CHOOSE(($B2:$G2<>"")*COLUMN($A:$F)+1,"","A","/B","/C","/D","/E","/F")
选取I2：N2单元格区域，鼠标点击编辑栏公式的任何位置，ctrl+shift+enter组合键确认
H2单元格复制下面公式：
=SUBSTITUTE(" "&I2&J2&K2&L2&M2&N2," /"," ")
选取H2：N2单元格区域，格式柄下拉复制公式。
具体引用区域需要自行校正

Answer 2

假定非空白单元格是A1单元格
=if(A1=" A B C D E F 品目1 1 1 2","B/D/F","")追问

不是很理解 IF公式试过了 因为涉及的数据较多太长了 就想要一种类似SUMPRODUCT的公式针对非空白单元格求和 唯一不同的是是字符
　 A B C D F 邀请结果
香蕉 1 　 2 　 　 A/C
橘子 　 1 　 1 2 B/D/F

追答

作辅助列G、H、I、J、K
在G1输入如下数组公式（用Shift+Ctrl+Enter输入）：
=INDEX({"A","B","C","D","F",""},SMALL(IF($A1:$E1"",COLUMN($A1:$E1),6),COLUMN(A1)))
在H1输入如下数组公式（用Shift+Ctrl+Enter输入）：
=G1&IF(INDEX({"A","B","C","D","F",""},SMALL(IF($A1:$E1"",COLUMN($A1:$E1),6),COLUMN(B1)))"","/","")&INDEX({"A","B","C","D","F",""},SMALL(IF($A1:$E1"",COLUMN($A1:$E1),6),COLUMN(B1)))
右拉至K1
在F1输入如下公式：
=K1
选定F1:K1
下拉
F列即所求的

Answer 3

用VBA简单，用EXCEL公式就麻烦很多，因为没有合并数组文本的函数。针对你的问题给出一个笨办法：
=SUBSTITUTE(CONCATENATE(IF(B2<>"",$B$1&"/",""),IF(C2<>"",$C$1&"/",""),IF(D2<>"",$D$1&"/",""),IF(E2<>"",$E$1&"/",""),IF(F2<>"",$F$1&"/","")),"/",,SUMPRODUCT(N(B2:F2<>""))) 下拉填充

Answer 4

=if(a1<>"","A")&if(b1<>"","B")&if(C1<>"","C")&if(D1<>"","D")&if(E1<>"","E")&if(F1<>"","F")
下拉复制公式

Answer 5

没明白。你这哪里是求和？描述清楚一点。追问

　 A B C D F 邀请结果
香蕉 1 　 2 　 　 A/C
橘子 　 1 　 1 2 B/D/F