EXCEL人口数据常用公式
举例图示
A
B
C
D
E统计员常用的excel公式
F
G
H
1
身份证号
年龄
性别
国籍
人户关系
文化程度
政治面貌
2
3
4
5
根据身份证号计算年龄：
“=TEXT(TODAY(),"YYYY")-(IF(LEN(B2)=18,"",19)&LEFT(REPLACE(B2,1,6,""),2+(LEN(B2)=18)*2))”
根据身份证号判断性别：
“=IF(MOD(MID(B2,17,1),2)=1,"男","女")”
统计不同性别的人数：
男  “=COUNTIF(D2:D5,"男")”
女  “=COUNTIF(D2:D5,"女")”
统计不同年龄段的人数：
0—3岁“=COUNTIF(C2:C5,"<=3")”
4—7岁“=COUNTIF(C2:C5,"<=7")-COUNTIF(C2:C5,"<=3")”
8—14岁“=COUNTIF(C2:C5,"<=14")-COUNTIF(C2:C5,"<=7")”
15—18岁“=COUNTIF(C2:C5,"<=18")-COUNTIF(C2:C5,"<=14")”
19—35岁“=COUNTIF(C2:C5,"<=35")-COUNTIF(C2:C5,"<=18")”
36—59岁“=COUNTIF(C2:C5,"<=59")-COUNTIF(C2:C5,"<=35")”
60—69岁“=COUNTIF(C2:C5,"<=69")-COUNTIF(C2:C5,"<=59")”
70—79岁“=COUNTIF(C2:C5,"<=79")-COUNTIF(C2:C5,"<=69")”
80—89岁“=COUNTIF(C2:C5,"<=89")-COUNTIF(C2:C5,"<=79")”
》=90岁“=COUNTIF(C2:C5,"<=120")-COUNTIF(C2:C5,"<=89")”
已入住户籍人数：“=COUNTIF(F2:F5,"常住有户籍")”
常住非户籍：“=COUNTIF(F2:F5,"常住无户籍")”
党员人数：“=COUNTIF(H2:H5,"党员")”
人士：“=总人数-COUNTIF(E2:E5,"中国")”