21Excel高级应用.docx
- 文档编号:17044365
- 上传时间:2023-07-21
- 格式:DOCX
- 页数:18
- 大小:568.25KB
21Excel高级应用.docx
《21Excel高级应用.docx》由会员分享,可在线阅读,更多相关《21Excel高级应用.docx(18页珍藏版)》请在冰点文库上搜索。
21Excel高级应用
Excel高级应用
一、合并计算
在日常工作中,经常要将一些分散的数据整理成一份完整的表格,这是一个将多个数据库合并成一个数据库、同时对其中的数据进行统计的操作。
【例1】如图1-5-59所示,在工作表Sheet1中有三个数据清单,现在要计算三年来各单位同类商品的销售额总和,并在Sheet2中用一个新的数据清单表示出来。
三个清单中共有的字段名有“单位名称”、“卫浴”、“日丰管”,共有的记录有五个单位。
合并成为一个新的数据清单后,具有相同字段名和相同记录名的数据进行合并,否则显示原始数据。
操作步骤如下:
①单击工作表标签Sheet2的A2单元格。
②单击【数据】选项卡“数据工具”分组中的“合并计算”按钮
,打开“合并计算”对话框,如图5-60所示。
在函数列表中选择“求和”,在引用位置栏,单击工作表标签Sheet1,用鼠标拖动选择B2:
F7,单击“添加”按钮,同样再添加Sheet1的B10:
E15、B18:
F24,选定“标签位置”中的“首行”和“最左列”,单击“确定”按钮。
③在Sheet2的A2单元格中输入字段名“单位名称”,结果如图1-5-61所示。
图1-5-59三个数据清单
图1-5-60“合并计算”对话框
图1-5-61合并计算结果
二、Excel函数综合应用
Excel函数非常丰富,可以各类函数结合应用,解决一些较复杂的问题。
【例2】如何利用身份证号码计算出生日期、年龄、性别?
1、身份证号码简介(18位):
1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为女;第18位为校验码。
2、计算“出生日期”:
生日是从第7位开始至第14位结束。
提取出来后为了计算“年龄”应该将“年”、“月”、“日”数据中添加一个“/”或“-”分隔符。
如下图1-5-92所示表格,将光标定位在单元格D2中,然后输入函数公式“=MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2)”,即可计算“出生日期”。
图1-5-92计算“出生日期”
3、计算“年龄”:
“出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来。
如图1-5-93所示,将光标定位在E2单元格中,然后输入函数公式“=INT((TODAY()-D2)/365)”,即可计算出“年龄”。
说明:
TODAY函数用于计算当前系统日期。
只要计算机的系统日期准确,就能立即计算出当前的日期,无需参数。
操作格式是TODAY()。
图1-5-93计算“年龄”
4、计算“性别”:
如图1-5-94所示,将光标定位在B2单元格中,然后输入函数公式“=IF(MOD(VALUE(MID(C2,17,1)),2)=0,"女","男")”或者“=IF(VALUE(MID(C2,17,1))/2=INT(VALUE(MID(C2,17,1))/2),"女","男")”,即可计算出“性别”。
说明:
VALUE(MID(C2,17,1))的含义是将提取出来的文本数字转换成能够计算的数值。
图1-5-94计算“性别”
三、Excel单元格下拉列表设置方法
利用Excel数据有效性,可以方便设置单元格下拉列表,这里介绍三种设置方法。
1.直接输入方法
①选择要设置的单元格,比如A1单元格;
②单击【数据】选项卡“数据工具”分组中的数据有效性
按钮,弹出“数据有效性”对话框,如图1-5-95所示;
③在“设置”选项中→“有效性条件”→“允许”中选择“序列”→右边的“忽略空值”和“提供下拉箭头”全部打勾→在“来源”下面输入数据,比如“1,2,3,4,5,6,7,8,9”(不包括双引号,分隔符号“,”必须为半角模式)→按“确定”就完成了,再次选择该A1单元格,就出现了下拉列表。
图1-5-95“数据有效性”对话框
2.引用同一工作表内的数据
如果同一工作表的某列就是下拉列表想要的数据,比如引用工作表Sheet1的B2:
B5,B2:
B5分别有数据1、2、3、4,操作方法与第1种方法相似,不同之处仅在第③步输入数据不相同,这里需要输入数据“=$B$2:
$B$5”,也可以按右边红色箭头直接选择B2:
B5区域即可。
3.引用不同工作表内的数据
如果不同工作表的某列就是下拉列表想要的数据,比如工作表Sheet1的A1单元格要引用工作表Sheet2的B2:
B5区域,工作表Sheet2的B2:
B5分别有数据1、2、3、4,操作方法如下:
①定义区域名称。
在Sheet2工作表中选中B2:
B5区域,右击选择“定义名称”或者选择【公式】选项卡功能区中的“定义名称”,打开“新建名称”对话框,如图1-5-96所示。
在“名称”中输入想定义的名称,如“DW”(可以自己随便命名),在“引用位置”下面输入“=Sheet2!
$B$2:
$B$5”,也可以按右边红色箭头直接选择B2:
B5区域,按“确定”完成。
当然最简单的定义区域名称的方法,是选中区域后,直接在名称框输入名称。
②在Sheet1工作表中选择要设置的单元格,比如A1单元格;
③后面操作步骤跟前面相似,不同之处,在于输入数据“=DW”,“DW”就是刚刚定义好的名称。
图1-5-96“新建名称”对话框
通过下拉列表设置方法,可以查询记录相关信息。
【例3】要实现在一个单元格中输入数据,同时它同一行相关的一些数据都要显示出来,而且要输入的数据量很大。
如图1-5-97所示课程成绩表,A3是一个下拉列表,选中学号,同时一行的姓名、课程名称、学分、期末成绩、总评成绩等信息都出现,而且每行都是这样。
图1-5-97课程成绩表
具体步骤如下:
①原数据表在sheet1表,新表建在Sheet2表,表格式同Sheet1表。
②选中Sheet1表的A列学号的区域(A3至A65),定义名称为“学号”。
③在Sheet2表的A3单元格,设置“数据有效性”,在“数据有效性”对话框中,“允许”选“序列”,“来源”中输入“=学号”,确定退出。
④在B3单元格输入公式:
=IF($A3<>0,VLOOKUP($A3,Sheet1!
$A$3:
$K$65,COLUMN(),FALSE),"")。
【说明】:
VLOOKUP格式:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
参数说明:
Lookup_value代表需要查找的数值;Table_array代表需要在其中查找数据的单元格区域;Col_index_num为在table_array区域中待返回的匹配值的列序号(当Col_index_num为2时,返回table_array第2列中的数值,为3时,返回第3列的值,……);Range_lookup为一逻辑值,如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值;如果为FALSE,则返回精确匹配值,如果找不到,则返回错误值#N/A。
⑤再将B3单元格横向填充到K3单元格。
⑥再将A3至K3单元格向下填充若干行。
⑦A列选学号后,后面出现相关数据。
四、Excel几种复杂图表的制作
1.组合图表
⏹组合数据图表在一个数据图表中表示两个或者两个以上的数据系列,而且不同的数据系列用不同的图表类型表示。
⏹利用组合图能够很方便地比较两个数据系列的异同。
2.双轴图表
⏹双轴数据图表是指在一个数据图表中,在主、次坐标轴上分别绘制一个或者多个数据系列,采用两种不同的坐标值来度量数据。
⏹当一个数据图表涉及多个数据系列,并且不同数据系列的数值相差悬殊的时候,比较合理的图表方案就是建立双轴图表。
3.复合饼图和复合条饼图
在Excel中插入饼图时有时会遇到这种情况,饼图中的一些数值具有较小的百分比,将其放到同一个饼图中难以看清这些数据,这时使用复合条饼图就可以提高小百分比的可读性。
复合饼图(或复合条饼图)可以从主饼图中提取部分数值,将其组合到旁边的另一个饼图(或堆积条形图)中。
例如下面的饼图1-5-98显示了某杂志的读者职业分布情况,其中小百分比数据较为密集不易看清楚。
图1-5-98普通饼图
而改用复合条饼图就显得一目了然。
如图1-5-99所示。
图1-5-99复合条饼图
有时还需要强调饼图中的一组数值,这时就可以使用复合饼图或复合条饼图,如图1-5-100为某些产品在几个城市的销售情况,第二个表格为“广州”的销售数据。
图1-5-100产品销售表
用复合饼图可以突出显示“广州”的详细销售情况。
如图1-5-101所示。
图1-5-101产品销售复合饼图
【例4】下面以上表为例介绍复合饼图的制作方法,复合条饼图的制作方法类似。
首先,为了制作复合饼图,需要将上表中的数据进行重新组合,将数值放到同一行或同一列中,如图1-5-102中底部的表格。
图1-5-102整合数据
具体步骤如下:
①选择重新组合后表格中的某个单元格,如B9单元格,在功能区中选择“插入”选项卡,在“图表”组中单击“饼图→复合饼图”,生成如图1-5-103的复合饼图。
图1-5-103复合饼图
②删除右侧的图例,双击图表中的数据系列,弹出“设置数据系列格式”对话框,如图1-5-104所示,将“第二绘图区包含最后一个”右侧的数值改为“4”,调整第二绘图区的大小,然后“关闭”对话框。
图1-5-104“设置数据系列格式”对话框
当“系列分割依据”选择“自定义”时,可以将某个数据点任意放置到主饼图或次饼图中。
方法是选择某个数据点,在“点属于”右侧的下拉列表中选择放置的位置。
③在图表的数据系列中右击,选择“添加数据标签”。
双击图表中添加的数据标签,弹出“设置数据标签格式”对话框,如图1-5-105所示,在“标签选项”中勾选“类别名称”,然后关闭对话框。
图1-5-105“设置数据标签格式”对话框
④将图表中的“其他”数据标志改为“广州”,如图1-5-106所示。
图1-5-106更改复合饼图数据标志
⑤要进一步美化图表,可以在“图表工具-设计”选项卡中的“样式”组中选择某种样式,本例选择“样式26”,最终效果如图1-5-101所示。
4.双层饼图
Excel中的饼图可以反映一组数据中各数据点所占百分比,可以作成三维的,也可以使用复合饼图表达某一块饼的下属子集组成。
但是如果对每一块饼,都要显示其下属子集的组成,则需要根据实际数据绘制多层饼图,如两层、三层乃至于更多层的饼图。
绘制多层饼图,需要使用一些特殊的方法,有时要将饼图和圆环图结合起来。
当然,最基本的是双层饼图。
下面举例说明双层饼图的制作方法。
【例5】某公司各地区及下属部门销售营业额,如下图1-5-107所示,根据各地区及各部门的隶属关系做成双层饼图,如图1-5-108所示。
其中,地区分类为中间(内层)饼图,各地下属部门为外层数据。
图1-5-107销售营业额形式一
图1-5-108双层饼图
使用Excel制作饼图时,应尽量避免合并单元格的数据,因此将上述表格改成图1-5-109的形式。
图1-5-109销售营业额形式二
具体操作步骤如下。
①由于是要绘制双层饼图,因此要先绘制最里面一层饼图,这是非常重要的一点。
选择A2:
B5,绘制普通饼图,添加数据标签,在设置数据标签格式中,勾选类别名称,设置调整大小和字体,如图1-5-110所示。
图1-5-110普通饼图
②增加外层饼图系列。
选择图表,单击右键,执行【选择数据】命令,打开【选择数据源】对话框,在“图例项(系列)”中点击添加,设置系列名称为“系列2”,其值为D2:
D13,单击【确定】按钮。
如图5-111所示此时可见图表似乎没有任何变化,只是多了几个图例标识。
图1-5-111增加外层饼图系列
③选择当前图表中可见的系列1,右键点击图表,选择【设置数据系列格式】,在【设置数据系列格式】对话框中,切换到“系列选项”选项卡,在【系列绘制在】处选择“次坐标轴”按钮,如图1-5-112所示,单击【关闭】按钮。
出现图表如图1-5-113所示。
图1-5-112设置数据系列次坐标轴
图1-5-113设置“次坐轴”后的饼图
④选择图表,单击右键,执行【选择数据】命令,打开【选择数据源】对话框,选择【系列2】,在【水平(分类)轴标签】处,点击【编辑】,将【轴标签区域】选定在C2:
C13,单击【确定】按钮,关闭【选择数据源】对话框。
此时出现图表如图1-5-114所示。
图1-5-114设置“系列2”后的饼图
⑤选择系列1,即目前可见层,然后单击其中某一块饼,比如“天津”,按住左键不放,向外拖动该饼,将整个系列一起往外拖,使整个系列的扇区形状一起缩小到合适的大小,当你拖到认为合适的时候,松开左键时,就会发现整个饼图会发生变化,如图1-5-115所示。
图1-5-115拖动“系列1”后的饼图
⑥两次单击(不是双击,而是单击后稍等一下再单击)该系列的每一块。
分别将分离的每块小饼一块一块地拖到饼的中央对齐,就得到如图1-5-116所示的图表。
图1-5-116将分离的小饼对齐到中央后的饼图
⑦选中外圆数据,右键点击【添加数据标签】对话框,然后再右键点击【设置数据标签格式】,在【标签选项】中勾选【类别名称】,然后关闭,并将“图例”删除,即得到图1-5-108所示的双层饼图。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 21 Excel 高级 应用