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

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

《Excel管理与分析课程讲义.docx》由会员分享，可在线阅读，更多相关《Excel管理与分析课程讲义.docx（101页珍藏版）》请在冰豆网上搜索。

Excel管理与分析课程讲义

Excel管理与分析课程

讲义

课程设计：

张剑悦

第1章Excel美化和经验3

1.1案例分析3

1.2案例实现4

1.2.1设置数据类型4

1.2.2竖排文字表头的制作5

1.2.3设置工作表边框、背景和底纹7

1.2.4工作表的打印10

1.3答疑与技巧13

1.4拓展与提高15

第2章用Excel管理、查询信息18

2.1案例分析19

2.1.1员工信息表的建立19

2.1.2员工信息查询20

2.1.3员工数据的筛选分析21

2.2案例实现21

2.2.1利用数据有效性快速、准确输入数据21

2.2.2利用身份证号码计算出生年月、年龄及性别24

2.2.3快速输入带有固定前缀的数据28

2.2.4用Vlookup函数快速查询满足条件数据29

2.2.5用高级筛选查询条件满足的信息31

2.3答疑与技巧33

第3章用Excel管理和运算35

3.1案例分析35

3.2案例实现37

3.2.1用SUMPRODUCT函数计算销售总价37

3.2.2用COUNTA函数计算文本数量38

3.2.3用VLOOKUP函数提取相关数据40

3.2.4为客户信息排名42

3.3答疑与技巧44

第4章用Excel管理和数据分析46

4.1案例分析47

4.1.1产品销售统计汇总表47

4.1.2销售客户信息表48

4.1.3销售人员销售额统计表49

4.2案例实现49

4.2.1用工作组高效创建数据相似表49

4.2.2跨表引用汇总多表数据52

4.2.3为信息表的数据进行拆分及合并57

4.2.4用批注对数据进行人性化的提示61

4.2.5对重复数据的标记63

4.2.6用函数快速统计销售数据和奖金65

4.2.7用分类汇总快速统计销售数据69

第5章用Excel制作季度报表73

5.1案例分析74

5.2案例实现76

5.2.1柱形图的制作76

5.2.2饼图的制作80

5.2.3双轴图表的制作83

5.2.4带有筛选功能图表的制作85

附：

课程案例操作88

第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利用“高级筛选”设置复杂条件进行数据查询

可以看出，上方