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

《Excel与VBA在数据管理与分析中的应用》由会员分享，可在线阅读，更多相关《Excel与VBA在数据管理与分析中的应用（53页珍藏版）》请在金锄头文库上搜索。

1、Excel与VBA在数据管理与分析中的应用,EXCEL在人事管理方面的运用,1、 性别、出生月日、年龄的自动填充功能设置 2、 劳动合同期限的自动生成和提前30天定期提醒功能设置 3、 采用“记录单”录入信息 4、 用“窗口冻结”功能可以进行简单的数据查询 5、“自动筛选”功能可以进行简单的数据统计 6、用“数据透视表”功能快速汇总各项数据,EXCEL在人事管理方面的运用,首先打开excel工作表,录入人员信息,具体项目依各自情况而定.如下图:,一、性别、出生月日、年龄的自动填充功能设置,我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成“性别”，“

2、出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息项单元格输入下列公式: (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天定期提醒功能设置,劳动合同管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果由于我们的人为原因造成了劳动合同漏签、

3、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人入职签订试用期合同开始，再到签订正式劳动合同以及后来的续签劳动合同，一系列的日期如何能够让系统自动生成，并且形成系统提前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(DA

4、TEDIF(TODAY(),Q3,“d“)=7,“试用期快结束了“,“)(5)提前30天提醒: =IF(DATEDIF(TODAY(),S4,“m“)=1,“该签合同了“,“),三、采用“记录单”录入信息,通过对一些人事信息项进行函数设置后，我们便可以开始录入信息了。逐行的键入人事信息，会让人很快产生疲劳感，甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个问题。请点击编辑栏中的“数据”“记录单”如图(三)我们可以用“Tab键进行项目的换行录入如图(四)。”,图三 记录单,图四 Tab键进行项目的换行录入,四、用“窗口冻结”功能可以进行简单的数据查询,当我

5、们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下面的信息又不知道此信息所对应的信息项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项，同时保留每个人的编号、姓名、部门，让其他信息可以根据需要进行查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻结窗口”，就可以出现如图(五)情况:,图五 查询信息,五、“自动筛选”功能可以进行简单的数据统计,有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有多少人?这时我们可以采用自动筛选功能。请选择“人

6、员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职(图(六)，在学历处选择本科(图(七)，在性别处选择男(图(八)，最后用鼠标将显示的性别全部选上，这时注意图(九)中用红笔圈住的地方就是我们所需要的数据了。,六、用“数据透视表”功能快速汇总各项数据,以上功能的设置多用于日常人事工作中，但到年底我们需要对这一年的人事情况进行大量的信息总结，比如人员的离职情况，入职情况，各类数据构成比例等。这项工作更是一项非常繁重的工作。我们可以利用Excel自带的“数据透视表”功能为我们排忧解难。请点击编辑菜单栏中“数

7、据”选项，选择“数据透视表和数据透视图”选项，如图(十),图十 选择“数据透视表和数据透视图”选项,选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统会自动将整个信息库区域设置成我们要选择的区域。在出现“数据透视表和数据透视图向导-3”第3步时我们选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格” 如图(十一)。点击“完成”后，便会出现(图(十二)结果。这里解释一下我们为什么要选择A3单元格，这主要是因为显示区上面有两行用来放置页字段。,图十一 将工作表区域选择在新的sheet中A3单元格,图十二 蓝圈圈住的地方,例如我们现在想统计各部

8、门2003年入职人员情况。我们便可以将相关项目用鼠标托至指定位置，如图(十二)红色箭头显示。通过对信息项目的拖拽，系统会自动出现相关信息的统计数据。如果我们还想出现统计数据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图(十二)蓝圈圈住的地方。这时会出现chart1，如图(十三)所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择。,图十三 完成,通过这个功能我们可以很容易的统计出各种人事资料，比如学历构成，性别比例等。同时您还可以根据自己的需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时，我们可以点击“布局”按钮，接下来会出现“数据透视表和数据透视图向导-布

9、局”在这里我们可以通过对话框中右侧的数据按钮添加或删除我们需要的数据项。如图(十四),图十四 统计人事资料,用EXCEL进行运动会的秩序编排和成绩统计,第一步：为各班运动员合理编号 运动员号码设6位。 第一位第二、三位第四第五六位年级班级性别序号第一位年级号：用数字1、2、3来分别区分。 第二、三位班级号：用这个班的班号来填，例如： 3班，填“03” 第四位性别号，男生用“1”女生用“2” 第五、六位是序号，由各班自己编排，只要不重复就可，（还为学生对特殊数字的偏好留了空间）。 整个编号是这样一个情况： 如“603105”就代表，六年级、三班、男子、班内第五个编号的运动员。,第二步：各项目的分

10、组工作,（1）运动员报名信息的录入： 在这一步中只需按报名表录入即可。这时不用考虑相同组别、相同项目放在一块的问题，这样作更加符合我们常规操作的习惯，有利于提高工作效率。然后在E2中输入公式“=MID(a2,4,1)”将号码中的性别号生成在E2中，用填充柄向下填充：,（2）分组编排：分两步完成,按组别、项目汇总 用筛选、复制、粘贴的方法就可以完成。例如：男子组100M的运动员，我们用自动筛选的办法，条件为：组别“1”，项目一“100M”，将筛选结果复制、粘贴到一个新的工作表并将工作表重命名为“男100M“，再筛选：组别“1”、项目二“100M”的追加到“男100M”工作表中。其他照此法进行即可

11、。 在大会秩序册中一般都是以行排列，而在EXCEL表中是以列排列的，这时可以进行行列转换，转换的方法是：选中复制后在粘贴到一个新的工作表时点击右击选择性粘贴，并对话框转置前打上钩即可。,将同一个班、相同项目的运动员分到不同的组 分别对“男100M”“女100M”等工作表进行“排序”（按号码）再用需要分成的组数填充就可以了。例如：男100M需要分成8组，就从上到下在分组号中填充“1、2、3、4、5、6、7、8”反复循环直到结束，这样一个班的同一项目运动员就分到了不同的组里。（可以用自定义序列的方法完成）,第三步：团体总分的计算,拿到各项比赛的成绩单后，在一个工作表内输入成绩，（我们可以把这个工作

12、表重命名为总成绩表）A列号码、B列姓名、C列项目、D列成绩、E列名次、F列累计分、G列团体。然后在G2输入“=left(a2,3)”这时对应运动员号码中的前三位（即年级、班级号码）就出现在G列中。把所有数据按“团体”一项排序，然后用数据分类汇总功能，分类字段选“团体”汇总方式选“求和”对汇总项选“累计分”这样所有班的团体总分就都算出来了。如果感觉看着不方便，那就再用一次复制、粘贴，将这些数据放到新的工作表中，（不过这时复制后要用“选择性粘贴”，并选择粘贴“数值”），,再用自动筛选功能只让带有“汇总”二字的行出现在屏幕上。方法如下图,用EXCEL开发管理信息系统,目前国内管理信息系统(MIS)开

13、发研制一般采用人们熟悉的dBaseX、Foxbase或Foxpro等,本文介绍了如何用Excel开发MIS。一般来说,一套MIS主要包括信息输入、信息处理、信息输出三大组成部分,用Excel处理这些部分均显得游刃有余。Excel是Windows环境下的一种电子表格软件,可向用户提供史无前例的超强功能和易用性。它同时具有电子数据表、图表和数据库的功能,具有极强的分析性能、报表制作工具和丰富的统计图表。在本文笔者用中文Excel2000 for Windows开发了水电管理系统，充分感受到其强大功能和方便性。下面详细阐述水电管理系统的开发过程。,应用程序：水电计价系统,1、 水电计价系统的功能2、

14、 程序设计过程,1、 水电计价系统的功能,本程序具有如下功能： 自动计算功能：可完成各户水费、电费、合计的计算。 自动汇总功能：自动汇总全校教职工楼的用电量、用水量及分类汇总。 查询功能：可查询各户的电表数、水表数、电费数、水费数。 报表的打印输出功能。 系统保护功能：具有口令保护、工作表保护以防非法用户进入和修改。,2、 程序设计过程,（2）界面设计,水电计价系统主界面如下所示：,主界面是以EXCEL工作表作为输入输出界面。 具体设计如下： 选定一个工作表并命名为“主界面”。 选定单元格区域，把区域颜色设置成你喜欢的颜色。 放置命令按钮，并给按钮指定宏。 分割区域并冻结，锁定界面使之不能滚动

15、。 工作表加密保护使用户不能修改。,记录单界面如下所示：,具体设计过程如下： 选定一个工作表并命名为“记录单”。 制表（如上图所示 ）。 选定年、月、水表数、电表数空白单元格区域，取消锁定。 放置命令按钮，并给按钮指定宏。 工作表保护使用户不能修改,主报表如下图所示：,具体设计过程如下： 选定一个工作表并命名为“主报表”。 制表（如上图所示 ）。 纵向冻结A、B两列，横向冻结1-5行。 放置命令按钮，并给按钮指定宏。 输入公式：计算上次表底、本月查表记录、水电用量、水、电费、水电费合计的校内、校外合计、楼房合计、平房合计。 在C338单元格输入下面公式，拖动填充柄复制公式至L338。 =SUM

16、IF($M$6:$M$331,“=l校内“,C6:C331)+SUMIF($M$6:$M$331,“=p校内“,C6:C331) 在C339单元格输入下面公式，拖动填充柄复制公式至L339。 =SUMIF($M$6:$M$331,“=l校外“,C6:C331)+SUMIF($M$6:$M$331,“=p校外“,C6:C331) 在C340单元格输入下面公式，拖动填充柄复制公式至L340。 =SUMIF($M$6:$M$331,“=l校外“,C6:C331)+SUMIF($M$6:$M$331,“=l校内“,C6:C331) 在C341单元格输入下面公式，拖动填充柄复制公式至L341。 =SUMIF($M$6:$M$331,“=p校外“,C6:C331)+SUMIF($M$6:$M$331,“=p校内“,C6:C331) 工作表保护使用户不能修改。,查表档案界面如下所示：,具体设计过程如下： 选定一个工作表并命名为“档案”。 制表（如上图所示 ）。 纵向冻结A、B两列，横向冻结13行。 放置命令按钮，并给按钮指定宏。 工作表保护使用户不能修改。,