如上图,如何输入一个公式,然后直接得到统计结果:A-级3家;AA-级1家;AAA-级2家
即为0的不统计,我希望在某个单元格里用函数直接显示这个结果,向大家请教!
公式如下:=IF(B10=0,"",A10&"级"&B10&"家;")&IF(B11=0,"",A11&"级"&B11&"家;")&IF(B12=0,"",A12&"级"&B12&"家;")&IF(B13=0,"",A13&"级"&B13&"家;")&IF(B14=0,"",A14&"级"&B14&"家;")&IF(B15=0,"",A15&"级"&B15&"家;")&IF(B16=0,"",A16&"级"&B16&"家;")&IF(B17=0,"",A17&"级"&B17&"家;")&IF(B18=0,"",A18&"级"&B18&"家;")
用VBA自定义了个函数HB,使用时在HB后括号中加上左列范围,会根据右列范围合并文本,具体见附件
Private Function HB(mRng As Range) As String
For Each c In mRng
If c.Offset(0, 1) <> 0 Then HB = HB & c.Value & "级" & c.Offset(0, 1).Value & "家" & "; "
Next
End Function