如何简化以下IF公式,或突破7层嵌套的限制.=IF(COUNTIF(G3:AB11,"A")>0,"A:"&COUNTIF(G3:AB11,"A"),IF(COUNTIF(G3:AB11,"B")>0,"B:"&COUNTIF(G3:AB11,"B"),IF(COUNTIF(G3:AB11,"C")>0,"C:"&COUNTIF(G3:AB11,"C"),IF(COUNTIF(G3:AB11,"D")>0,"D:"&COUNTI

Question

如何简化以下IF公式,或突破7层嵌套的限制.
=IF(COUNTIF(G3:AB11,"A")>0,"A:"&COUNTIF(G3:AB11,"A"),IF(COUNTIF(G3:AB11,"B")>0,"B:"&COUNTIF(G3:AB11,"B"),IF(COUNTIF(G3:AB11,"C")>0,"C:"&COUNTIF(G3:AB11,"C"),IF(COUNTIF(G3:AB11,"D")>0,"D:"&COUNTIF(G3:AB11,"D"),IF(COUNTIF(G3:AB11,"E")>0,"B:"&COUNTIF(G3:AB11,"E"),IF(COUNTIF(G3:AB11,"E")>0,"E:"&COUNTIF(G3:AB11,"E"),IF(COUNTIF(G3:AB11,"F")>0,"F:"&COUNTIF(G3:AB11,"F"),IF(COUNTIF(G3:AB11,"G")>0,"G:"&COUNTIF(G3:AB11,"G"),IF(COUNTIF(G3:AB11,"H")>0,"H:"&COUNTIF(G3:AB11,"H"),IF(COUNTIF(G3:AB11,"I")>0,"I:"&COUNTIF(G3:AB11,"I"),IF(COUNTIF(G3:AB11,"J")>0,"J:"&COUNTIF(G3:AB11,"J"),IF(COUNTIF(G3:AB11,"K")>0,"K:"&COUNTIF(G3:AB11,"K"),IF(COUNTIF(G3:AB11,"L")>0,"L:"&COUNTIF(G3:AB11,"L"),"M:"&COUNTIF(G3:AB11,"M"))))))))))))))
因为目前是A－M的字母,区域内不会全部都有,但只会显示其中的几个,要统计它们的个数.并在另外的一个单元格内显示 如“A:7 ” 如果A没有,就显示 B：4 ,以些类推.

Answer 1

公式本身的意义在哪里呢?难道这么大的区域内只能存在A-L之中的一个字母?否则只会显示考前面的字母