excel函数----统计

来源：百度知道 编辑：UC知道 时间：2024/05/12 09:30:54
Z1INC0900T RQTAC0906T
Z3INC090OT F1CTC0041T
Z2BLC0017T ZHHCC1919T 以Z1,Z2,Z3表示中山，JK,JM表示江门，F1,NA,SS表示佛山，
JKINC0913T ZHPKC0048T 其余表示珠海，请问做表中中山，江门，佛山，珠海各有多少
Z2PKC0912T RQHCC0920T 烦请使用函数统计
Z3BKC0929T Z2MNC0919T
ZHHCC0017T SKMSC1580K
JKDKC0037T Z3JAC0915T
Z2BKC0930T Z1MSC0920T
ZHKER0901T Z1MSC0922T
RQTAC0906T BJMNC0923T
F1CTC0042T F1HCC1922T

中山
=countif(a:b,"Z1*")+countif(a:b,"Z2*")+countif(a:b,"Z3*")

其它类似

="中山有"&SUMPRODUCT((LEFT(A1:A12,2)={"Z1","Z2","Z3"})+(LEFT(B1:B12,2)={"Z1","Z2","Z3"}))&"个"
="江门有"&SUMPRODUCT((LEFT(A1:A12,2)={"JK","JM"})+(LEFT(B1:B12,2)={"JK","JM"}))&"个"
="佛山有"&SUMPRODUCT((LEFT(A1:A12,2)={"F1","NA","SS"})+(LEFT(B1:B12,2)={"F1","NA","SS"}))&"个"
="珠海有"&COUNTA(A1:B12)-SUMPRODUCT((LEFT(A1:A12,2)={"Z1","Z2","Z3","JK","JM","F1","NA","SS"})+(LEFT(B1:B12,2)={"Z1","Z2","Z3","JK","JM","F1","NA","SS"}))&"个"

Function tongji(b As Range)
tong