Excel管理与分析课程讲义.doc

Excel 管理与分析课程讲 义课程设计： 张剑悦目录第1章 Excel美化和经验31.1 案例分析31.2 案例实现41.2.1 设置数据类型41.2.2 竖排文字表头的制作51.2.3 设置工作表边框、背景和底纹71.2.4 工作表的打印101.3 答疑与技巧131.4 拓展与提高15第2章 用Excel管理、查询信息182.1 案例分析192.1.1 员工信息表的建立192.1.2 员工信息查询202.1.3 员工数据的筛选分析212.2 案例实现212.2.1 利用数据有效性快速、准确输入数据212.2.2 利用身份证号码计算出生年月、年龄及性别242.2.3 快速输入带有固定前缀的数据282.2.4 用Vlookup函数快速查询满足条件数据292.2.5 用高级筛选查询条件满足的信息312.3 答疑与技巧33第3章 用Excel管理和运算353.1 案例分析353.2 案例实现373.2.1 用SUMPRODUCT函数计算销售总价373.2.2 用COUNTA函数计算文本数量383.2.3 用VLOOKUP函数提取相关数据403.2.4 为客户信息排名423.3 答疑与技巧44第4章 用Excel管理和数据分析464.1 案例分析474.1.1 产品销售统计汇总表474.1.2 销售客户信息表484.1.3 销售人员销售额统计表494.2 案例实现494.2.1 用工作组高效创建数据相似表494.2.2 跨表引用汇总多表数据524.2.3 为信息表的数据进行拆分及合并574.2.4 用批注对数据进行人性化的提示614.2.5 对重复数据的标记634.2.6 用函数快速统计销售数据和奖金654.2.7 用分类汇总快速统计销售数据69第5章 用Excel制作季度报表735.1 案例分析745.2 案例实现765.2.1 柱形图的制作765.2.2 饼图的制作805.2.3 双轴图表的制作835.2.4 带有筛选功能图表的制作85附： 课程案例操作88II培训讲师：张剑悦第1章 Excel美化和经验本章将向大家介绍制作另一种美化效果的“资产评估表”，并且最终将其打印出来。这张数据表是一个财务表，也是一个二维表格（既有行标题，又有列标题），由于“资产评估”的项目较多，为了节省空间横向打印数据，所以将数据分成了“两栏”排列，如图1所示。图1 制作完成的“产品销售损益表”可以从图中看出，这张“资产评估表”有左右两栏数据，分别记录了“资产”、“行数”、“年初数”和“期末数”4列数据信息；而且有些数据已经进行填写，有些数据要等打印出来再添加。1.1 案例分析若只在单元格中输入数据和必要的文字后，效果如图2所示。图2 数据和文字输入完成后效果与最终完成的数据表进行对比后发现，在数据输入完成后还有如下几步操作要做：$ 设置数据的格式，将输入的“年初数”和“期末数”数据添加“货币”符号，并且将负数设置成带有括号的“红色”字体效果，如图3所示。图3 设置数据格式及负数效果$ “资产评估表”的行标题“流动资产”文字本来只在一个单元格中，但是最终将其制作成竖排文字表头的效果，如图4所示。图4 文字行标题竖排表头效果$ 为整个工作表添加带有“纹理”效果的背景图片，并且将数据表“标题”文字单元格设置成“深蓝色”颜色填充，将内部数据单元格设置成“白色”颜色填充，然后再去掉多余的表格虚框。使数据表的内容突出醒目，使标题和内容清晰明确，让整个数据表给人一种自然、和谐和完整的感觉。提示：在打印这张工作表时，设置的“纹理”背景图片是不会打印出来的，在打印时就是白色填充效果。1.2 案例实现在设置数据表时，通常有一个顺序，就是先要将工作表中的数据和文本信息输入完成，然后再对单元格中的数据进行“类型”以及“对齐”方式的调整，最后再对整个数据表进行边框、底纹或者背景图片的添加等美化操作。即：先输入数据对单元格设置对工作表设置。1.2.1 设置数据类型由于本例已经将数据和文本全部添加到了单元格中，下面就可以直接修改单元格中数据的类型了。本例需要修改的数据类型就是将“年初数”和“期末数”中的数值更改成“货币”类型，然后再设置一下“负数”的形式即可。1首先将数据表内两栏“年初数”和“期末数”所在的单元格用键盘【Ctrl】键配合全部选中。2鼠标右键单击任意选中单元格，在弹出的快捷菜单中选择【设置单元格格式】命令，打开【单元格格式】对话框的【数字】选项卡。3先将数值“分类”从默认的“常规”类型更改成“货币”类型；然后在右侧“小数位数”中选择默认的“2”位；再将“货币符号”选项指定成“￥”人民币符号；最后在下方货币“负数”形式中选择带有括号的红色文字效果，如图5所示。图5 指定单元格数值为“货币”类型4单击【确定】按钮返回数据表后，刚才选中的数据已经更改成指定的货币类型，并且最后两个“负数”已经变成“红色”并带有括号效果，如图6所示。如图6 设置“货币”类型后的数据表提示：将单元格数值设定成“货币”类型，返回数据表后有时会因单元格的宽度较小而使货币数据压缩成“#”显示，调整单元格列宽即可恢复货币数据。1.2.2 竖排文字表头的制作下面来制作行标题“流动资产”竖排表头的效果，在制作时需要对文字进行“合并单元格”、“垂直居中”以及文字“竖排”等一系列的设置。1首先将“流动资产”文字所在的单元格以及下方与数据表数据相同数量的多个单元格同时选中，如图7所示。图7 将需要制作“表头”的单元格选中2鼠标右键单击任意选中单元格，在弹出的快捷菜单中选择【设置单元格格式】命令，打开【单元格格式】对话框，选择【对齐】选项卡。3在【对齐】选项卡中要做3个设置，先将左侧下方“文本控制”区的“合并单元格”选项选中；然后将中间“垂直对齐”的方式设置成“居中”；最后鼠标单击右侧“方向”中的“竖排”文本区，使其为“反白”效果，如图8所示。图8 设置“竖排”表头的选项提示：选择左侧下方“文本控制”区的“合并单元格”选项是为了将刚才选择的多个单元格进行各并；将“垂直对齐”的方式设置成“居中”是为了让合并后的“流动资产”文本到合并后单元格的中心；鼠标单击右侧“方向”中的“竖排”文本区是为了让“流动资产”4个字能竖排排放。4单击【确定】按钮返回数据表，“流动资产”文字已经转变成行标题“竖排”表头的效果，如图9所示。图9 制作完成的行标题“竖排”表头提示：在合并表头的单元格时，还可以用【格式】工具栏中的【合并及居中】命令按钮 实现。1.2.3 设置工作表边框、背景和底纹工作表的边框、背景和底纹通常是最后才设置的。尤其是数据表边框，若提早进行了设置，一旦对数据进行了增加或减少，就有可能还要再对边框重新进行设置。工作表边框对工作表边框的设定包括设置“实框”和“虚框”2种框线。所谓“实框”就是人为添加的带有一定颜色的边框框线；所谓“虚框”就是工作表本身自带的单元格格线框。若“虚框”不设置转化成“实框”，那么在打印数据表或去掉“虚框”后是看不到任何单元格“格线”的，即数据间没有框线。1设置框线前，应先调整一下各列数据的列宽，由于数据表有左右两栏相同的标题数据，所以应改将相同的“列标题”所在的数据列“列宽”调整成一样的宽度。2将数据表标题行和下面所有数据区域全部选中（除了大标题和“编制单位”、“日期”和“单位”行外）。3鼠标单击【格式】工具栏【边框】按钮中的【所有框线】按钮 ，为所选数据单元格添加上单元格实线边框，如图10所示。图10 为所选数据单元格添加实线边框提示：数据表的“实框”设置完成后，下面就该来设置数据表的“虚框”，本例是将“虚框”进行隐藏。4选择【工具】菜单【选项】命令，打开【选项】对话框，将【视图】选项卡下方的“网格线”选项去掉，也就是不显示没有设置“实框”的单元格格线，如图11所示。图11 设置成不显示“虚框”效果5单击【确定】后返回工作表。此时，数据表中没有设置“实线框”的所有虚框都会自动隐藏起来，如图12所示。图12 设置了实框线和隐藏虚框后的最终效果经过添加数据表框线和隐藏“虚框”的设置后，数据表已经显得整齐很多了，下面再来为数据表添加“纹理”背景。工作表背景工作表背景通常可以通过两种方式设定，一种是先将整个工作表全选后再利用【格式】工具栏中的【填充颜色】按钮 将整个工作表填充一种底纹颜色；另一种则是将背景图片插入平铺到工作表之中，本例用的就是此方法。1打开数据表后，选择【格式】菜单【工作表】【背景】命令。2打开【工作表背景】对话框，选择背景图片所在的文件夹和图片文件后，单击下方的【插入】按钮，如图13所示。图13 选择工作表背景图片提示：虽然可以使用多种类型的图片充当工作表背景，但是通常情况下应该选用“纹理”效果的图片。3返回数据表后，会立即将此图片进行大量复制并平铺在整个工作表当中，由于图片是“纹理”效果，所以就算图片被大量复制，也仍看不出图片的边界，效果如图14所示。图14 插入工作表背景图片后的效果提示：若想删除插入的工作表背景图片效果，此时可以选择【格式】菜单【工作表】【删除背景】命令。工作表底纹添加了工作表背景后，图片背景会应用这个工作表所有的单元格之中，为了突出数据表的标题和数据内容，所以应该将它们分别设置各自的“填充颜色”，以突出显示数据。1将数据表标题行（第“5”行）数据选中，然后通过【格式】工具栏中的【填充颜色】按钮 将其设置成“深蓝色”颜色填充；再利用【格式】工具栏中的【字体颜色】按钮 将标题文字设置成“白色”。2然后再将下面的数据单元格区域选中，同样利用【格式】工具栏中的【填充颜色】按钮 将数据区设置成“白色”颜色填充，设置完成后如图15所示。图15 为“标题”和“数据”区分别设置不同的颜色填充至此，完成了对数据表“边框”、“背景”图片和“底纹”颜色的设置。在打印数据表之前还有一些细节要进行调整，大致有下面几步操作要做：$ 为数据表大标题“资产评估表”的文字所在行进行【合并及居中】设置，让其在数据表的中心位置，并设置大标题的字体颜色和格式；$ 为数据表上方“编制单位”、“日期”和“单位：万元”一行的文字进行格式美化，为“日期”中“年、月、日”前添加“下划线”（具体方法请参见本章“3 答疑与技巧”一节）。$ 为数据表的“标题”文字和“行次”列数据设置“居中”对齐效果。全部制作完成后效果如图16所示。图16 对数据表格式美化全部完成1.2.4 工作表的打印将数据表全部美化完成后，就可以对该数据表进行输出打印了。对于Excel工作表的打印，与其它Office组件不同，有其自己的特点，下面就来看看该如何操作。1打开要打印的工作表后，单击【常用】工具栏或【文件】菜单中的【打印预览】命令按钮 ,会立即进入到【打印预览】视图，如图17所示。图17 工作表【打印预览】视图提示：Excel与Word文档不同，Word文档的【页面视图】就是纸张的模拟效果，可以说是所见即所得，而Excel的工作视图有“65536行”和“256列”单元格，因此在打印前一定要进入到【打印预览】视图进行效果预览和调整。2默认情况下，会将A4纸张竖排打印数据表，由于本例的数据表是横向的，竖排打印不全数据表信息，所以要通过“页面设置”进行调整。单击【打印预览】视图窗口上方的【设置】命令按钮，进入到【页面设置】对话框。3在【页面】选项卡中将纸张“方向”从默认的“纵向”选项更改成“横向”，其它的选项设置不用进行调整，如图18所示。图18 将纸张从“纵向”更改成“横向”设置4单击【确定】按钮后返回【打印预览】视图窗口，纸张已经更改成了“横向”，但是由于表格数据较多，还有最后两列数据没有显示在当前第1页之中，如图19所示。图19 纸张更改成“横向”后的效果提示：由于相差的不多，所以此时有两种方法可以将最后两列数据从第2页调整至第1页之中：第1，利用上方【页边距】按钮显示出数据表各列的位置以及纸张的边界，拖拽减少数据列宽度或纸张边距即可（方法请参见本章“3 答疑与技巧”一节）。第2，利用【分页预览】视图对数据表进行缩放（本例将采用此种方法来操作）。5鼠标单击【打印预览】视图窗口的【分页预览】命令按钮，进入到工作表的【分页预览】视图，如图20所示。图20 进入工作表的【分页预览】视图6可以清楚地看到在【分页预览】视图中最后两列数据前有一条蓝色的“虚线”，这说明了蓝色“虚线”左侧的部分为“第1页”数据，“虚线”右侧的部分为“第2页”数据。7用鼠标指针对准蓝色“虚线”，然后向右拖拽至最后两列数据后面，使整个数据区都变成“第1页”的效果，如图21所示。图21 将“分页”线向右拖拽包含最后两列数据8若再单击【常用】工具栏的【打印预览】命令按钮 ,可再次进入到【打印预览】视图后，可以看到所有的数据都显示在了一张A4纸张之中了，如图22所示。图22 将数据调整到1页A4纸张后的效果提示：利用【分页预览】视图将数据调整到1页纸后，会缩小数据的字体字号，所以若数据表与1页纸相差不多时采用这种方法，数据字号大小变化不大，效果不会受到影响。若数据表很大，与1页纸相差很多，则会导致数据字体过小，不利于打印后查看。9若需要打印，则直接鼠标单击【打印预览】视图窗口的【打印】命令按钮，打开【打印内容】对话框，在对话框中使用默认选项，单击下方的【确定】按钮即可，如图23所示。图23 在【打印内容】对话框中进行打印设置至此，打印Excel当前工作表的操作全部完成。在本章，我们学习了数据表从最初的数据输入完成状态，通过“边框”、“背景”和“底纹”等设置，再到最终进行的打印设置，这样一个相对完整的操作过程。1.3 答疑与技巧问：在数据表“资产”一列的数据中有一个“减：坏帐准备”数据，它的对齐效果是向右缩进了2个字符，请问是什么方式制作的？答：向右缩进2个字符其实既可以使用“空格”，又可以利用设置【单元格格式】来进行。本例中这个单元格数据的缩进效果，是利用【单元格格式】对话框【对齐】选项卡进行“左缩进”2个字符设置的，如图24所示。图24 设置“左缩进”2个字符的效果这样设置比利用“空格”进行缩进的好处就是可以通过“格式刷”来对其他具有相同缩进效果的单元格进行快速格式复制。问：为数据表上方“日期”中“年、月、日”前添加“下划线”的具体方法是什么？答：通常情况下在Excel中为文字添加“下划线”的效果可以利用为单元格添加下边的“框线”效果来实现。但是本例由于“日期”中“年、月、日”下方是数据表的框线，所以无法采用这种方法，只能通过为“年、月、日”文字前分别添加“空格”，然后再分别将“空格”选中，再单击【格式】工具栏的【下划线】按钮 ，这样便为“年、月、日”文字前的“空格”字符添加了下划线。问：打印数据表时，若数据较多，超出了1页纸的范围，但是超出的数据只有2列，仍希望将数据打印在1页纸之中，应该如何调整？答：若数据只比1页纸多出了1、2列，则除了应用本章前面介绍的在【分页视图】进行调整外，还可以利用【打印预览】视图进行操作。具体方法如下：1进入到工作表的【打印预览】视图，然后将其纸张方向调整完毕，若发现还有2列数据在第1页之外，可以单击【打印预览】视图窗口上方的【页边距】命令按钮，此时会在【打印预览】视图中显示出数据表各列的位置以及纸张的边界线，如图25所示。图25 选择【页边距】命令按钮后效果提示：窗口上方和下方均是“双线”，因为有一条线是“页眉和页脚”区的界线。2手动拖动左右最外侧的竖线，将左右边距尽量调小，若有必要还可以通过上方的控点调整每列的列宽，调整完成后最后2列数据便会进入到第1页纸张之中，如图26所示。图26 调整完成【页边距】后的效果问：有一个数据表，并不想全部打印出来，只想打印其中的一部分数据，该如何操作？答：那可以利用【设置打印区域】命令来进行打印数据范围的设置。具体操作如下：1先在数据表中将需要打印的数据区域进行选择，然后选择【文件】菜单【打印区域】【设置打印区域】命令，返回到数据表后，选中的数据区周围出现一个“虚线框”效果，如图27所示。图27 设置了数据的打印区域2进入到工作表的【打印预览】视图可以看到打印的内容只有“设置区域”之中的数据内容，如图28所示。图28 设置了打印区域后的【打印预览】视图提示：可以利用【文件】菜单【打印区域】【取消打印区域】命令将设置的打印区域去掉。1.4 拓展与提高在实际工作中，有些数据表要经常对其不同区域进行打印，还有些数据表为了查看数据方便，要经常隐藏某些行列数据。若没有好的处理方法，就需要频繁地设置数据表的【设置打印区域】或隐藏行列操作，使操作既繁琐又容易出错。在这里，向大家介绍一种“视图管理”的方法，利用它就可以将多种不同的“打印设置”以及“隐藏行列”和“筛选”结果分别进行保存，以后只要通过【视图管理器】便可以轻松进行快速的“页面”转换。下面举个例子，在【视图管理器】对话框中若选择事先制作好的“分表”视图，再单击【显示】按钮，Excel工作表立即会隐藏其他的列数据，将部分数据显示出来，如图29所示。图29 选择“分表”视图后隐藏一部分列数据提示：看列号能看出当前数据表隐藏了从“G”到“J”4列数据。在【视图管理器】对话框中若选择事先制作好的“总表”视图，再单击【显示】按钮，Excel工作表会立即还原隐藏，显示出所有的数据信息，如图30所示。图30选择“总表”视图后显示所有数据信息提示：今后可随时根据需要任意切换这两种视图，省去了手动进行设置的麻烦。下面就来介绍一下【视图管理器】的用法。【视图管理器】可以管理下面多种视图：$ 对数据表进行了“打印区域”的设置；$ 对数据表的“行高、列宽”进行了调整；$ 隐藏了数据表的行或列数据；$ 对数据表进行筛选（其实筛选就是隐藏数据表的某些行数据）。【视图管理器】的操作如下：1首先对数据表进行设置打印区域、调整行高列宽或者筛选等操作，总之是改变了当前数据表的“打印设置”或“行高列宽”。2然后通过【视图】菜单【视图管理器】命令打开【视图管理器】对话框。3再单击右下方的【添加】按钮打开【添加视图】对话框。在该对话框中为这种新的视图起一个名称，以便今后长期调用，还可以通过对话框下方的选项控制该视图包含哪些设置。如图31所示。图31 在【添加视图】对话框为新视图创建名称4单击【确定】按钮后返回【视图管理器】对话框。5以后，无论当前在哪个工作表或者当前的页面如何设置，只要进入【视图管理器】对话框选择所需的“视图”名称，Excel会立即切换工作表并更改成指定的效果。总之，【视图管理器】特别适用于经常要以不同方式打印的数据表；经常进行“隐藏行、列”或是设置筛选的数据表。第2章 用Excel管理、查询信息Office中的Excel是一个数据管理和分析软件，它用电子表格的形式存储和管理数据信息。使用Excel不仅有很多快速输入数据的技巧和方法，而且还可以对数据进行多种方式的查询和分析，本章以人力资源信息表的管理为例向大家介绍快速输入数据、分析数据、查询数据的方法和经验。在人力资源的信息表中，企业员工的各种信息统计是最常见的，如何快速将它们进行整理和输入，并合理的管理数据、快速的查询数据等都是人力资源部门最基本、最常用的操作。图2.1所示的是一个企业全部职员的“员工登记表”，在表中记录了企业员工的基本信息。图2.1 企业“员工登记表”这个总表的员工信息并不是非常完整，根据这个总表，各个分公司或部门可再建立较详细的“员工信息表”，如图2.2所示。图2.2 分公司员工信息表为了查询信息方便，可以单独设置一个工作表，创建一个简单的“信息查询”系统，当输入员工姓名后，他的基本信息就可以直接显示在下面了，如图2.3所示。图2.3 员工信息查询在查询员工的信息时，还可以利用Excel本身的“高级筛选”或“数据透视表”等功能对员工数据进行筛选和详细分析。2.1 案例分析人力资源数据信息是企业中最重要的数据之一，它记录了员工的各种信息资料，能够快速输入数据，准确快速地进行查询是提高办公效率、实现人性化管理的关键。2.1.1信息表的建立北京分公司的“员工信息表”中，职员名单全部来自于已有的“员工登记表”之中，因此可以利用数据“有效性”的方法跨表引用这些名单，如图2.4所示。图2.4 利用“有效性”功能从其他表中引用“名单”提示：输入员工“姓名”时，只需利用鼠标单击单元格右侧的“下拉列表”按钮，从中进行挑选即可。这样既确保了名单的准确性，又使输入非常方便和快捷。对于员工所在的“部门”一列数据，由于全部来自于3个部门之中，所以可以采取与输入“姓名”类似的操作，在单元格右侧利用数据“有效性”制作出下拉列表按钮，以后通过下拉列表按钮的选择便可将“部门”信息填写完成，如图2.5所示。图2.5 利用“有效性”功能填写列表中的部门在“员工信息表”中已经输入了各个员工的身份证号码，由于身份证号码具有很强的规律性，所以完全可以通过身份证号码自动获取和计算出每位员工的“性别”、“出生日期”和“年龄”等信息，避免了手动输入的麻烦，如图2.6所示。图2.6 利用身份证号码获取和计算出相应信息在“员工信息表”中，还有一列“工作证号”数据，这列数据有一个明显的特征，在数据前带有固定的前缀字符“BJX”，这种带有固定“前缀”或“后缀”的数据可以通过“设置单元格格式”的方法快速输入，如图2.7所示。图2.7 带有固定前缀字符的“工作证号”数据2.1.2信息查询当数据信息全部输入完成后，今后可随时对其进行查询，为了查询方便，可以建立一个简单的查询表格，当输入或选择“查询姓名”时，在下方的单元格内便会出现该员工的基本资料信息，如图2.8所示。图2.8 用“Vlookup”函数查询信息这种查询表格的实现是通过“Vlookup”函数实现的，“Vlookup”函数可以让某个数据与另一个数据表区域的第1列数据进行对比，若发现有相同的数据，则将数据表中指定的某列数据提取出来。本例“查询姓名”中的名单可以利用设置数据“有效性”的方法进行选择，下方“年龄”、“文化程度”和“职务”单元格中分别使用了3个“Vlookup”函数，当上面单元格中选择了某个名单后，便可自动将前面“员工登记表”以及“员工信息表”中相应的数据列信息调取过来。2.1.3数据的筛选分析本例中的“员工登记表”或“员工信息表”都是典型的数据“字段”表，即有一个标题行，下面是相应的一行一行数据记录。这种“字段”表非常便于对数据进行排序或筛选查询。利用Excel的“高级筛选”功能可以设置复杂查询条件，对多个“字段”分别设置“与、或”关系，并将筛选的结果复制到指定的位置，如图2.9所示。图2.9 利用“高级筛选”设置复杂条件进行数据查询可以看出，上方在单元格中输入的文字就是“筛选”条件，最上面一行是条件的“标题”（要与“数据表标题”完全一致）；下面有两行条件，一行是“回”，另一行是“男”、“30”、“=40”和“大学本科”，说明这两行条件是“或”关系，只要满足一个就可以。所以下面筛选出的结果数据表中既有所有民族是“回”的人员，又有性别为“男”，同时年龄在30至40岁（包含40岁）之间，同时文化程度还得是“大学本科”的人员。提示：在输入和设置“高级筛选”条件时，一行中的多个条件为“与”关系，必须同时满足；多行中的条件为“或”关系，满足其中之一就可以。2.2 案例实现本例将向大家介绍利用多种方法来快速输入数据，并对数据进行管理和查询。下面首先来看看快速输入数据的技巧和经验，待有了数据之后再对它们进行查询。2.2.1 利用数据有效性快速、准确输入数据本节将介绍两种不同的数据“有效性”设置方法来输入数据，一种是通过先定义其他表数据区的“名称”，再利用“序列”进行引用；另一种则是在“序列”中输入分类内容进行直接调用。这两种方法都能很好地保障数据的准确性，甚至改变了数据输入方式。这个功能还可以和其他很多功能综合应用，有着很强的拓展空间，希望大家能举一反三。输入来自其他表的数据在本例中，已经事先输入完成了“员工登记表”的数据信息，下面我们利用这个数据表，来创建“北京分公司的员工信息表”。北京分公司员工信息表的“姓名”一列数据全部来自于前面“员工登记表”之中，所以为了能够准确、快速地填写数据，可以采用数据有效性的方法来设置。提示：为了能够实现“有效性”数据的跨表引用，所以在设置“有效性”前应该先对引用数据的区域进行“名称”定义。1先打开“员工登记表”总表，然后将员工“姓名”一列数据选中。2鼠标单击【格式】工具栏最左侧的【名称框】，在其中输入“name”名称后，单击键盘的【Enter】键确认，如图2.10所示。图2.10 为“姓名”一列数据定义“名称”3打开后面的北京分公司的“员工信息表”，将需要输入姓名的一列区域选中，然后单击【数据】菜单【有效性】命令，打开【数据有效性】对话框。可以看出，当前选中的单元格有效性条件都是允许“任何值”，如图2.11所示。图2.11 对选中区域设置数据“有效性”4单击【数据有效性】对话框“允许”右侧的下拉列表按钮，选择其中的“序列”选项，并在“来源”行中输入公式“=name”内容，如图2.12所示。图2.12 输入“序列”有效性的行来源5单击【确定】按钮返回工作表中，选择数据区中的每个单元格右侧都会多出一个“下拉箭头”按钮，单击后可以直接显示前面“员工登记表”中“name”一列的姓名，从中进行挑选可将姓名准确、快速地依次进行填写，如图2.1所示。图2.13 利用“有效性”跨表引用数据利用定义“名称”和数据“有效性”进行配合，实现了跨表引用数据。在使用这种方法输入数据时还要注意以下几点：$ 不使用定义“名称”，而直接在【数据有效性】对话框的“来源”行输入地址“公式”是无法实现跨表引用数据的。$ 利用定义“名称”可以实现在一个文档的不同“sheet”表中进行有效性数据引用，但是不能在多个文档中进行引用；$ 今后只要改变定义“名称”的区域数据内容，原来已经利用有效性填写完成的数据不会发生变化，但是有效性“序列”下拉列表中会随着改变；$ 若总表中人员较多，定义“名称”并且设置“有效性”跨表引用时，可能会由于下拉列表内容过多造成查找不便，所以这个功能并一定是为了“快速”输入数据，而是利用其“有效性”使得数据输入更加准确；$ 若设置有效性后，单元格中的数据可以不从单元格右侧的下拉列表中进行选择，还可以使用键盘进行输入。但是必须输入“列表”中的数据，若出现不一致的情况，会立即弹出“出错警告”对话框进行报错提示，如图2.14所示。图2.14 填写的数据与“有效性”设置不相符输入来自序列中的值设置数据“有效性”后，既加快了输入的速度又保证了数据输入的准确性。在实际应用中，不仅可以在有效性“序列”中输入其他表“名称”地址进行跨表引用，还可以直接在“序列”中输入不同的分类内容来应用。下面就来看看北京分公司“员工信息表”中的“部门”一列数据怎样利用数据“有效性”进行填写。提示：在北京分公司“员工信息表”中，“部门”一列数据只有3种部门，即。1先打开北京分公司“员工信息表”，然后将“部门”一列需要输入数据的单元格选中。2然后单击【数据】菜单【有效性】命令，打开【数据有效性】对话框。将当前有效性条件从允许“任何值”更改成“序列”，并在“来源”行中直接输入“研发部,市场部,销售部”内容，如图2.15所示。图2.15 输入“序列”有效性的行来源提示：在“来源”行中输入各个分类时，一定要用“英文”的“逗号”将内容分开，否则不会被识别成为多个分类。3单击【确定】按钮返回工作表中，“部门”一列数据中每个单元格右侧都会多出一个“下拉箭头”按钮，单击后可以显示“研发部,市场部,销售部”选项，可从中直接进行挑选填写，如图2.16所示。图2.16 利用“有效性”跨表引用数据4至此，完成了“部门”一列数据的填写操作。由于应用的是数据“有效性”的设置，所以只要输入了不在列表中的数据，那么同样会弹出“出错警告”对话框进行报错提示。2.2.2 利用身份证号码计算出生年月、年龄及性别在北京分公司“员工信息表”中填写完成了各自的身份证号码，一旦身份证号码有了，那么就可以利用“身份证号码”自身的规律计算获得每个人的“出生年月”、“年龄”和“性别”等信息。为了能够让大家更好地理解如何利用“身份证号码”计算出“出生年月”和“性别”，所以下面先将身份证号码的相关知识做一个简要的介绍。当今的身份证号码有15位或2位两种。早期签发的身份证号码是15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，都是2位的。这两种身份证号码本身带有的规律如下：$ 15位的身份证号码：1-6位为地区代码，7-8位为出生年份(2位)，9-10位为出生月份，11-12位为出生日期，第1-15位为顺序号，并能够判断性别，奇数为“男”，偶数为“女”。$ 18位的身份证号码：1-6位为地区代码，7-10位为出生年份(4位)，11-12位为出生月份，13-14位为出生日期，第15-17位为顺序号，并能够判断性别，奇数为男，偶数为女,第18位为效验码。提示：本例中，我们先以全是18位身份证号的情况为例向大家介绍计算“出生日期”和“性别”，若在实际工作中是15位和18位身份证号码混排时，请参看本章“2.3 答疑与技巧”一节的内容。确定“出生日期”18位身份证号码中的生日是从7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”、“月”、“日”数据中各添加一个“/”或“-”分隔符。下面来看看具体的操作：1先打开北京分公司“员工信息表”，“身份证号码”一列中已经正确的输入了每个人的身份证号码。2将光标定在第1个人的“出生日期”单元格中，然后在单元格中输入下面的函数公式：“=MID(F5,7,4)&/&MID(F5,11,2)&/&MID(F5,13,2)”，如图2.17所示。图2.17 利用身份证号码计算第1个人的“出生日期”提示：有关这个函数公式，具体的说明如下：MID函数是从数据中间提取字符的函数。它的操作格式是：MID(text，start_num，num_chars)。$ 其中Text是包含要提取字符的文本串或单元格地址（本例为“F5”单元格）$ Start_num是文本中要提取的第一个字符的位置(本例中依次为“7”、“11”、“13”)。$ Num_chars指定要由 MID所提取的字符个数（本例中均提取年份时为“4”提取月份和日期是为“2”）。多个MID函数中的“&”起到的作用是将提取出的“年”、“月”、“日”信息合并到一起。“/”则是将提取出的“年”、“月”、“日”数据之间添加一个斜杠标记。3单击键盘的【Enter】键后，可立即计算出第1个人的“出生日期”。通过填充柄填充，可计算出下面每个人的出生日期，如图2.18所示。 图2.18 利用填充柄计算出每个人的“出生日期”由于提取出的“出生日期”在“年”、“月”、“日”数据中分别添加了“/”分隔符，所以这样的数据便可以当作日期类型进行年龄计算。确定“年龄”每个人的“出生日期”已经确定了，那么年龄就可以利用一个简单的函数公式计算出来了。1将光标定在第1个人的“出生日期”单元格中，然后在单元格中输入下面的函数公式：“=(TODAY()-G5)/365”，如图2.19所示。图2.19 利用“出生日期”计算第1个人的“年龄”提示：有关这个函数公式，具体的说明如下：$ TODAY函数是计算当前系统日期的函数。只要计算机的系统日期准确，就能立即计算出当前的日期。它无需参数，因此它的操作格式是：TODAY( )；$ 用“TODAY()-G5”，也就是用今天的日期减去出生日期，就可以计算出这个人的出生天数；$ 再除以“365”就得到这个人的年龄。2单击键盘的【Enter】键后，可立即计算出第1个人的“年龄”。通过填充柄填充，可计算出下面每个人的年龄，如图2.20所示。 图2.20 利用填充柄计算出每个人的“年龄”提示：年龄在计算出来后有可能会有很多位小数，按照不同的习惯通常有下面两种不同处理方法：第1，利用【格式】工具栏中的【减少小数位数】按钮 将年龄的数值变成“整数”，这样会自动进行“四舍五入”的计算。第2，在公式“=(TODAY()-G5)/365”中再嵌套一个“INT”取整函数，即：“=INT（(TODAY()-G5)/365）”，这样会自动将后面的小数去掉，只保留整数部分。确定“性别”每个人的性别可以利用“身份证号码”进行判断，18位身份证号码中，第15-17位为顺序号，若为奇数则为男，若为偶数则为女。判断“奇、偶”数，在Excel中可以用这样的方法：先用这个数除以2看它等不等于这个数除以2后再取整，若相等，则为“偶数”，否则为“奇数”。1将光标定在第1个人的“性别”单元格中，然后在单元格中输入下面的函数公式：“=IF(VALUE(MID(F5,15,3)/2=INT(VALUE(MID(F5,15,3)/2),女,男)”，如图2.21所示。图2.21 利用“身份证号码”计算第1个人的“性别”提示：这是一个多函数嵌套的复杂公式，具体的说明如下：$ 函数公式中“MID(F5,15,3)”的含义是将身份证中的第15-第17位提取出来；$ “VALUE(MID(F5,15,3)”的含义是将提取出的文本数字转换成能计算的“数值”；$ “VALUE(MID(F5,15,3)/2=INT(VALUE(MID(F5,15,3)/2)”的含义是判断“奇、偶”；$ “=IF(VALUE(MID(F5,15,3)/2=INT(VALUE(MID(F5,15,3)/2),女,男)”的含义是若为“偶数”就填写“女”，否则是“奇数”就填写“男”。2单击键盘的【Enter】键后，可立即计算出第1个人的“性别”。通过填充柄填充，可计算出下面每个人的性别，如图2.22所示。 图2.22 利用填充柄计算出每个人的“性别”至此，利用身份证号码来确定“出生日期”、“年龄”和“性别”的操作全部完成了，在实际工作中像这种带有很强规律性的数据还有不少，将有用的数据信息从原数中提取出来进行判断或使用，可以解决很多数据输入的问题，一旦公式设置正确，便可确保数据的准确性。2.2.3 快速输入带有固定前缀的数据在北京分公司“员工信息表”中有一列员工的“工作证号”数据信息。由于带有相同的前缀字符“BJX”，所以就可以利用【设置单元格格式】命令来快速输入。1在北京分公司“员工信息表”中，将“工作证号”一列需要输入数据的单元格选中。2然后对选中的任意单元格单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】命令。3打开【单元格格式】对话框，在【数字】选项卡中选择最后一个“自定义”分类。4在右侧的“类型”中将原来的“G/通用格式”更改成“BJX#”，如图2.23所示。图2.23 自定义“单元格”类型提示：是固定的字符，所以用英文的引号将它括在内，# 在Excel中表示数值类型，写在BJX后表示输入的数字以“BJX”为前缀。5单击【确定】按钮返回工作表中，可直接向这些单元格中输入“前缀”字符后面的数字，前缀“BJX”可自动加载到数字之前，如图2.24所示。图2.24 自动生成“前缀”的“工作证号”提示：若填写的是固定后缀信息，则在自定义单元格“类型”时，将 # 和英文引号中的固定字符反过来写即可。本例介绍的是带有前缀数值的快速输入方式，若是文本带有固定前缀，请参看本章“2.3 答疑与技巧”一节的内容。2.2.4 用Vlookup函数快速查询满足条件数据将“人力资源”信息表所有数据都输入完成后，最重要的就是根据需要随时进行信息的查询和检索了。由于经常对人员的“年龄”、“文化程度”和“职务”等信息进行查询，所以可以利用Excel的“VLOOKUP”函数建立一个专门用来查询信息的“查询表”。以后只要输入或选择要查询的人员姓名，下面便会自动显示其相关信息。1首先在“员工登记表”和“北京分公司员工信息表”后建立一个“信息查询”工作表，专门用于查询员工的信息。工作表中根据查询需要输入相关的内容并做好格式美化，如图2.25所示。图2.25 创建查询工作表2由于是查询北京分公司“员工信息表”中的人员，所以可以在“查询姓名”单元格中设置数据“有效性”，以后好通过下拉列表进行名单的选择。将北京分公司“员工信息表”中的人员“姓名”一列定义成“北分姓名”名称，如图2.26所示。图2.26 将名单定义名称3然后在“信息查询”表“查询姓名”后要输入名字的单元格选中，单击【数据】菜单【有效性】命令，打开【数据有效性】对话框将当前有效性条件从允许“任何值”更改成“序列”，并在“来源”行中直接输入公式“=北分姓名”内容，如图2.27所示。图2.27 输入“序列”有效性的行来源4单击【确定】按钮后返回工作表中，输入姓名的单元格右侧会多出一个“下拉箭头”按钮，单击后可以直接选择北京分公司“员工信息表”中的姓名，如图2.28所示。图2.28 可选择查询人名单5先不用着急输入名单，先把下面要查询信息的单元格中分别输入各自的函数。在“年龄”后的单元格中输入函数公式“=VLOOKUP(C3,北京分公司员工信息表!C4:I16,6,FALSE)”；在“文化程度”后的单元格中输入函数公式“=VLOOKUP(C3,员工登记表!C4:K36,9,FALSE)”；在“职务”后的单元格中输入函数公式“=VLOOKUP(C3,北京分公司员工信息表!C4:I16,7,FALSE)”，如图2.29所示。图2.29 分别在查询单元格中输入函数公式提示：这3个“VLOOKUP”函数的含义如下：计算“年龄”的“VLOOKUP”函数作用：用本表“C3”姓名单元格中名字与“北京分公司员工信息表”的“姓名”一列数据进行比较，若发现名字一致则将第“6”列（也就是“年龄”一列）的数据提取过来；计算“文化程度”的“VLOOKUP”函数作用：用本表“C3”姓名单元格中名字与“员工登记表”的“姓名”一列数据进行比较，若发现名字一致则将第“9”列（也就是“文化程度”一列）的数据提取过来；计算“年龄”的“VLOOKUP”函数作用：用本表“C3”姓名单元格中名字与“北京分公司员工信息表”的“姓名”一列数据进行比较，若发现名字一致则将第“7”列（也就是“职务”一列）的数据提取过来；6在“查询姓名”后的单元格中利用下拉列表按钮填写一个人员“姓名”后，在下方相应的单元格内出现了该员工的“年龄”、“文化程度”和“职务”等信息，如图2.30所示。图2.30 选择“查询姓名”后的查询结果2.2.5 用高级筛选查询条件满足的信息对人力资源信息的管理，还包括随时根据需要对人员信息表进行筛选查询。在Excel中提供了“自动筛选”和“高级筛选”两种方式，“自动筛选”是在原表中设置相对较简单的条件进行筛选查询；而“高级筛选”则可以将筛选结果复制到指定地方，并可设置多条件“与、或”关系的复杂查询。本例我们就来使用“高级筛选”对人员进行查询。高级筛选在应用前，应该先将查询条件输入到工作表的空白单元格中，而且在输入条件时要先填写与数据表完全相同的标题。本例要在全员的“员工登记表”中筛选出民族是“回”，或者是性别为“女”且年龄在“30-40”岁且文化程度是“大学本科”的人。下面来看看具体操作：1首先打开“员工登记表”，然后在右侧空白处填写出高级筛选需要用到的所有条件的标题。2然后在标题下输入条件，由于民族是“回”的条件与性别为“女”且年龄在“30-40”之间且文化程度是“大学本科”的条件是“或”关系，所以将条件写在了两行（满足一个就可以），如图2.31所示。图2.31 填写“高级筛选”条件提示：由于查找人员的年龄是“30且40”，所以在Excel中要将年龄设置两次。3条件输入完成后，就可以进行“高级筛选”了。将光标定位在数据表中任意一个单元格，然后选择【数据】菜单【筛选】【高级筛选】命令，打开【高级筛选】对话框。4在【高级筛选】对话框中“列表区域”已经自动将数据表进行了选择，所以不必更改；单击“条件区域”右侧的按钮 ，将工作表中刚刚创建的“条件”区域进行圈选（条件标题也要一起选中）；再将上方的“方式”选项从默认的“在原有区域显示筛选结果”更改成“将筛选结果复制到其他位置”，并下方“复制到”中为筛选结果选择一个起始单元格，如图2.32所示。图2.32 设置【高级筛选】对话框选项5单击【确定】按钮后返回工作表，会在刚才指定的起始单元格处生成一个查询结果的数据表。这个表满足了两种条件：第1，所有民族是“回”的人员；第2，性别为“女”且年龄在“30-40”之间且文化程度是“大学本科”的人员，如图2.33所示。图2.33 满足“高级筛选”结果的所有人员利用“高级筛选”可以对单个“字段”型（有标题行）数据表进行多种条件的复杂查询，是在实际工作中必不可少的一种查询数据的手段。通过本章的学习，大家应该掌握了带有规律性数据的快捷输入方法，以及利用数据“有效性”准确、快速的输入数据，并利用多种查询方式对数据信息进行查询和筛选。2.3 答疑与技巧问：本章介绍了输入名单等这种数据信息是可以采用数据“有效性”的方法，那如果名单时固定，而且要在不同的Ex