EXCEL人口数据常用公式

HR常⽤的Excel函数公式⼤全（共21个），帮你整理齐了！

在HR同事电脑中，经常看到海量的Excel表格，员⼯基本信息、提成计算、考勤统计、合同管理....看来再完备的HR系统也取代不了Excel表格的作⽤。⼈⼒君尽可能多的收集HR⼯作中的Excel公式，进⾏了整理编排，于是有了这篇本平台史上最全HR的Excel公式 数据分析技巧集~

⼈⼒君

⼀、员⼯信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),'男','⼥')

2、出⽣年⽉(G列)

=TEXT(MID(E3,7,8),'0-00-00')

3、年龄公式(H列)

=DATEDIF(G3,TODAY(),'y')

4、退休⽇期 (I列)

=TEXT(EDATE(G3,12*(5*(F3='男') 55)),'yyyy/mm/dd aaaa')

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带⽰例中有地址库代码表

6、社会⼯龄(T列)

=DATEDIF(S3,NOW(),'y')

7、公司⼯龄(W列)

=DATEDIF(V3,NOW(),'y')&'年'&DATEDIF(V3,NOW(),'ym')&'⽉'&DATEDIF(V3,NOW(),'md')&'天'

8、合同续签⽇期(Y列)

=DATE(YEAR(V3) LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期⽇期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),'[<0]过期0天;[<30]即将到期0天;还早')

10、⼯龄⼯资(AA列)

=MIN(700,DATEDIF($V3,NOW(),'y')*50)

11、⽣肖(AB列)

=MID('猴鸡狗猪⿏⽜虎兔龙蛇马⽺',MOD(MID(E3,7,4),12) 1,1)⼆、员⼯考勤表公式

1、本⽉⼯作⽇天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4) 1,),)

Excel04解题步骤

1、 将工作表sheet1更名为“第五次普查数据”，将sheet2更名为“第六次普查数据”。

做法：

选择“sheet1”工作表标签右键“重命名”更名为“第五次普查数据”

同样方法将sheet2更名为“第六次普查数据”

2、 浏览网页“第五次全国人口普查公报.htm”，将其中的“2000年第五次全国人口普查主要数据”表格导入到工作表“第五次普查数据”中；浏览网页“第六次全国人口普查公报.htm”，将其中的“2010年第六次全国人口普查主要数据”表格导入到工作表“第六次普查数据”中（要求均从A1单元格开始导入，不得对两个工作表中的数据进行排序）

做法： 1） 打开网页“第五次全国人口普查公报.htm”，复制其中的“2000年第五次全国人口普查主要数据”表格，在工作表“第五次普查数据”中选中A1单元格，右键“选择性粘贴”，在选择性粘贴窗口中选择粘贴方式为“HTML”确定

2） 同样方法将“第六次全国人口普查公报.htm”中的“2010年第六次全国人口普查主要数据”表格导入到工作表“第六次普查数据”中 3、 对两个工作表中的数据区域设置自动套用格式，设置第五次普查数据表为“表样式浅色 16”，设置第六次普查数据表为“表样式浅色 17”，并将所有人口数列的数字格式设为带千分位分隔符的整数。

做法：

1） 在“第五次普查数据”中选中A1：34数据区域，在“开始”选项卡中的“套用表格格式”中选择“表样式浅色 16”

2） 选中“2000年人口数”列，右键鼠标“设置单元格格式”，在“数字”选项卡中选择分类为“数值”，设置小数位数为0，勾选“使用千分位分隔符”

3） 同样的方法设置“第六次普查数据”中的数据区域格式为“表样式浅色 17”，并将人口数列的数字格式设为带千分位分隔符的整数。

4、 将Sheet3重命名为“比较数据”；将第五次普查数据和第六次普查数据两个工 作表的数据合并到工作表“比较数据”中（自A1单元格开始存放，标题顺序依次为：地区、2010年人口数（万人）、2010年比重、2000年人口数（万人）、2000年比重）。并以“地区”为关键字按升序排列。设置比较数据工作表自动调整行高和列宽、字体为“黑体”、字号为“12”、内外边框均设置为细实线。

在Excel中统计多列多行数据个数的公式可以使用以下几种方法：

1. 使用COUNTIF函数配合单元格范围引用：COUNTIF函数用于在给定范围内统计满足某个条件的单元格个数。如果要统计某一列或某一区域内的数据个数，可以使用以下公式：

```excel

=COUNTIF(range,"*value*")

```

其中，"range"表示要统计的单元格范围，"value"表示要统计的数据。如果要统计多列或多行数据，可以将上述公式中的"range"替换为多个单元格范围的组合。

2. 使用SUMPRODUCT函数配合数组公式：SUMPRODUCT函数用于计算数组间对应元素乘积之和。如果要统计多列或多行数据个数，可以将数据区域作为数组输入，并使用以下公式：

```excel

=SUMPRODUCT((count(range)*{1,1,1,1})^2)

```

其中，"range"表示要统计的单元格范围。这个公式会返回一个数组，其中包含多列或多行数据的个数。

3. 使用通配符和SUM函数：可以使用通配符和SUM函数来统计多列或多行数据的个数。具体来说，可以使用以下公式：

```excel

=SUM(SUMIF(range,"*value*",range))

```

其中，"range"表示要统计的单元格范围，"value"表示要统计的数据。"*"是通配符，表示可以匹配任意字符。这个公式会将多列或多行数据作为单个数组进行处理，并使用SUMIF函数对每个数据逐一进行计数，最终使用SUM函数将计数结果相加。

请注意，这些公式都假定输入的数据不为空。如果数据可能为空，可以使用条件判断或ISNUMBER函数来处理空值。

excel的计数公式

1、COUNT函数（只统计数字单元格的个数）。方法：在目标单元格中输入公式：=COUNT（C3：C9）。备注：C3：C9是成绩所在的单元格区域，也就是数字区域。由于B2：B9区域中没有数字，所以统计结果为0.

2、COUNTA函数（计算区域中非空单元格的个数）。方法：在目标单元格中输入公式：=COUNTA（C3：C9）。备注：B3：B9为名字所在单元格区域，没有空值，所以统计值为：7.C3：C9位成绩所在单元格区域，一个空值，所以统计值为：6.

3、COUNTBLANK函数（计算区域中空单元格的个数）。方法：在目标单元格中输入公式：=COUNTBLANK（C3：C9）。备注：B3：B9为名字所在单元格区域，没有空值，所以统计值为：0.C3：C9位成绩所在单元格区域，一个空值，所以统计值为：1.

4、COUNTIF函数（计算区域中满足条件的单元格个数）。方法：在目标单元格中输入公式：=COUNTIF（C3：C9，>59）。

5、COUNTIFS函数（统计给定区域中多条下的数据）。方法：在目标单元格中输入公式：=COUNTIFS（C3：C9，>=60，C3：C9，<80）。