Excel与VBA在数据管理与分析中的应用.ppt

Excel与VBA在数据管理与分析中的应用 EXCEL在人事管理方面的运用 1、 性别、出生月日、年龄的自动填充功能设置 2、 劳动合同期限的自动生成和提前30天定期提醒功能设置 3、 采用“记录单”录入信息 4、 用“窗口冻结”功能可以进行简单的数据查询 5、“自动筛选”功能可以进行简单的数据统计 6、用“数据透视表”功能快速汇总各项数据 EXCEL在人事管理方面的运用 首先打开excel工作表,录入人员信息,具体项目依各自情况而定.如下图: 一、性别、出生月日、年龄的自动填充功能设置 我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式: (1)性别: =IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,男,女) (2)出生年月: =DATE(MID(E3,7,4),MID(E3,11,2),MID(E3,13,2)) (3)年龄: =DATEDIF(G3,TODAY(),Y) 二、劳动合同期限的自动生成和提前30天定期提醒功能设置 劳动合同管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始，再到签订正式劳动合同以及后来的续签劳动合同，一系列的日期如何能够让系统自动生成，并且形成系统提前30天自动提醒我们的功能呢？这需要对一些信息项进行函数设置。如图二所示: 函数公式 (1)试用期到期时间: =DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1) (2)劳动合同到期时间: =DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1) 　　(3)续签合同到期时间: =DATE(YEAR(S3)+1,MONTH(S3),DAY(S3)) 　　(4)试用期提前7天提醒: =IF(DATEDIF(TODAY(),Q3,d)=7,试用期快结束了,) 　(5)提前30天提醒: =IF(DATEDIF(TODAY(),S4,m)=1,该签合同了,) 三、采用“记录单”录入信息 通过对一些人事信息项进行函数设置后，我们便可以开始录入信息了。逐行的键入人事信息，会让人很快产生疲劳感，甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”―“记录单”如图(三)我们可以用“Tab键进行项目的换行录入如图(四)。” 图三 记录单 图四 Tab键进行项目的换行录入 四、用“窗口冻结”功能可以进行简单的数据查询 当我们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图(五)情况: 图五 查询信息 五、“自动筛选”功能可以进行简单的数据统计 有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(图(六))，在学历处选择本科(图(七))，在性别处选择男(图(八))，最后用鼠标将显示的性别全部选上，这时注意图(九)中用红笔圈住的地方就是我们所需要的数据了。 六、用“数据透视表”功能快速汇总各项数据 以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行大量的信息总结，比如人员的离职情况，入职情况，各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。 　　请点击编辑菜单栏中“数据”选项，选择“数据透视表和数据透视图”选项，如图(十) 图十 选择“数据透视表和数据透视图”选项 选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格” 如图(十一)。点击“完