如何在excel中统计每一行数据中各个数字的个数，并生成结果？

2024-11-02 12:31:07

推荐回答（3个）

回答1：

若每个数字不超过10个，中间可不加逗号，公式
=if(countif(a1:z1,3)=0,"0","")&sumproduct(countif(a1:z1,{3;4;5;6;7})*10^(5-row($1:$5))) 下拉填充
若可能超过10个，需逐个处理：
="<"&countif(a1:z1,3)&","countif(a1:z1,4)&","countif(a1:z1,5)&","countif(a1:z1,6)&","countif(a1:z1,7)&">"
若可将得到的数字分别放在一个单元格中，可用公式：
=index(countif($a1:$z1,{3;4;5;6;7}),column(a1))右拉填充5列，再一起下拉填充

回答2：

你判断的有问题了，我需要去判断哪几个数字的有无啊？
=COUNTIF(B2:M2,1)&","&COUNTIF(B2:M2,2)&","&COUNTIF(B2:M2,3)&","&COUNTIF(B2:M2,4)&","&COUNTIF(B2:M2,5)&","&COUNTIF(B2:M2,6)&","&COUNTIF(B2:M2,7)&","&COUNTIF(B2:M2,8)&","&COUNTIF(B2:M2,9)&","&COUNTIF(B2:M2,0)

回答3：

楼上注意，表中没有1、2、8、9、0，帮你删几个。
=COUNTIF(B2:M2,3)&","&COUNTIF(B2:M2,4)&","&COUNTIF(B2:M2,5)&","&COUNTIF(B2:M2,6)&","&COUNTIF(B2:M2,7)