EXCEL中有没有这样一个公式,可以实现统计前面单元格有几个A,几个B,几个C,如果字母统计为0时就不显示,直接用一个公式就可完成??
统计后会出现5A0B,我不想显示0B
假设在A1单元格,B1显示结果
B1输入公式 ="A:" & LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",)) &"个," & "B:" & LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"B",)) &"个," & "C:" & LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"C",)) &"个"
效果参见附图!
------------------------------------------
=if(countif(a1:e1,"A")=0,"",countif(a1:e1,"A") & "A") & if(countif(a1:e1,"B")=0,"",countif(a1:e1,"B") & "A")