Excel 如何设计可随心所欲调节汇总对象的图表?

当数据源中有多项数据时,能否在图表中任意切换数据,从而让图表可以快速调整汇总对象?

解题步骤

只有使用数据透视图才能快速地随意调整图表内容。

以图11.51所示的数据为例,生成透视图的步骤如下。

图11.51 销量与返修表

1.选择A1单元格,然后单击功能区的“插入”→“数据透视图”,在弹出的“创建数据透视图”对话框中选择“现有工作表”,并将位置设置为“G1”,然后单击“确定”按钮创建透视图。图11.52为选项设置界面,图11.53为生成的空白透视表与空白透视图。

图11.52 设置透视图选项

图11.53 空白透视表与空白透视图

2.在右方的任务窗格中将姓名拖到“轴(类别)”字段中,将销量拖到“值”字段中,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时透视图效果如图11.54所示。

图11.54 按姓名比较销量

图11.54是按姓名比较销量的,假设需要按组别比较销量之和,仅需两个步骤:在右方的任务窗格中将组别拖到“轴(类别)”字段中,将原本已经在“轴(类别)”字段中的姓名拖到单元格中从而删除它;然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时透视图将变成图11.55所示的效果。

图11.55 按组别比较总销量

如果要按组别比较平均返修率,那么需要执行三个步骤:在右方的任务窗格中将返修率拖到“值”字段中,将原本已经在“值”字段中的姓名拖到单元格中从而删除它;然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”;再右键单击 H5 单元格,从右键菜单中依次单击“值汇总依据”→“平均值”,此时图表将变成图11.56所示的效果。

图11.56 按组别比较平均返修率

如果要求按性别比较平均销量,同样只需要三个步骤:在右方的任务窗格中将销量拖到“值”字段中,将原本已经在“值”字段中的返修率拖到单元格中从而删除它;接着将性别拖到“轴(类别)”字段中,将原本在“轴(类别)”字段中的组别拖到单元格中从而删除它;最后右键单击H4单元格,从右键菜单中依次单击“值汇总依据”→“平均值”,此时透视表将变成图11.57所示的效果。

图11.57 按性别比较平均销量

如果要求同时按组别比较总销量和平均返修率,那么可按以下方式操作。

1.在右方的任务窗格中将销量拖到“值”字段中,接着将组别拖到“轴(类别)”字段中,将原本在“轴(类别)”字段中的性别拖到单元格中从而删除它,此时图表会显示为图 11.58 所示的效果。

图11.58 按组别比较总销量和平均返回率

2.选中透视图,然后从“格式”选项卡左上角的“图表元素”列表中选择“系列”求和项:返修率"”,然后单击功区的“设计”→“更改图表类型”,在“更改图表类型”对话框中将“求和项:返修率”的图表类型由簇状柱形图修改为折线图,同时选择右边的“次坐标轴”复选框,最后单击“确定”按钮保存设置。图11.59为图表系列的设置界面。

图11.59 修改“返回率”的类型及位置

3.选择I2:I4区域,按组合键<Ctrl+1>,弹出“设置单元格格式”对话框,并将数字格式设置为“百分比”,此时图表的最终效果如图11.60所示。

图11.60 按组别比较总销量和平均返修率

从图11.60中可以看出B组问题比较严重,销量最低,返修率却最高。

如果不使用图表直接看数据源,则不容易看出此问题。

知识扩展

1.数据透视表可以随心所欲地改变计算对象和计算方式,而数据透视图是建立在数据透视表的基础上的,必须搭配透视表使用,因此透视图拥有透视表的一切优点。

2.透视表和普通图表的数据刷新方式有所区别,普通图表会在单元格的数据变化后立即更新,而透视图不会随单元格的数值变化而变化,必须手工单击功能区中的“分析”选项卡下的“刷新”菜单才会更新图表内容。

Excel 对图表标示平均线的方法

图11.43中包含公司所有业务经理的本月业绩,能否用图表将它们展示出来,同时标示一条平均线,便于快速判断谁在平均线以上、谁在平均线以下?

图11.43 业绩表

解题步骤

Excel图表原本是不支持平均线的,但是可以变通一下思路,在图表中创建一个辅助系列,辅助系列的图表类型采用折线图,系列值使用AVERAGE函数计算而来。具体操作步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”,此时Excel会生成图11.44所示的图表。

图11.44 默认状态的柱形图

2.按组合键<Ctrl+F3>,弹出“名称管理器”对话框,然后单击“新建”按钮,弹出“新建名称”对话框。

3.将名称设置为“平均线”,将引用位置设置为以下公式:

此公式可以生成8个业绩平均值,即一个数组中包含8个等值的元素。设置界面如图11.45所示。

4.选择图表,然后单击功能区的“设计”→“选择数据”,然后在“选择数据源”对话框中单击“添加”按钮,在弹出的对话框中将系列名称设置为“平均线”,将系列值设置为“=Sheet1!平均线”,设置界面如图11.46所示。

图11.45 定义名称

图11.46 添加新系列

5.单击“确定”按钮返回“选择数据源”对话框,再次单击“确定”按钮返回工作表界面,此时图表中会新增一个系列,效果如图11.47所示。

图11.47 添加新系列后的效果

6.从“格式”选项卡左上角的“图表元素”列表中选择“系列"平均线"”,然后单击功能区的“设计”→“更改图表类型”,在“更改图表类型”对话框的下方找到系列名称为“平均线”的系列,并将它的图表类型修改为“折线图”,操作界面如图11.48所示。

图11.48 将“平均线”修改为折线图

7.单击“确定”按钮返回工作表界面,此时图表中原本显示为柱形的“平均线”系列将会变成一条横线,效果如图11.49所示。

8.从“格式”选项卡左上角的“图表元素”列表中选择“系列 "业绩"”,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”。

9.单击功能区的“设计”→“添加图表元素”→“图表标题”→“图表上方”,然后将默认的图表标题修改为“业绩比较图”,图表最终效果如图11.50所示。

图11.49 在图表中添加平均线

图11.50 图表最终效果

图11.50中的横线表示平均线,可以从图中看到有4人的业绩高于平均线、4人的业绩低于平均线。

知识扩展

1.为图表添加辅助系列时必须提供数据来源,数据源包含三种形式:其一是在工作表中添加辅助列,将数据放在该区域中,然后在图表的辅助系列中引用此区域的值,此方法最简单,但是不太美观;其二是定义名称,在名称中引用数据,在图表的辅助系列中引用名称;其三是直接在“添加数据系列”对话框中输入数组公式,此方法不支持函数和运算符,因此适应范围很小。

2.利用本例的思路可以在图表中添加业绩的最大值或最小值,也可以同时添加最大值和最小值的标示线。

3.如果选择图表中的图表系列以外的组件,然后对图表添加数据标签,那么图表中的所有系列都会显示数据标签,如果只选择单个图表系列再添加数据标签,则Excel只对当前选中的图表系列添加数据标签。

Excel 用何种方式正确呈现差异明显的数据?

图11.37是某卖场的7大品牌手机销量,其中三星、苹果和小米的销量远远高于其他品牌,将所有品牌放在一起比较意义不大。

应该用何种方法实现既在同一个图表中比较所有品牌,又能将销量相近的品牌单独比较呢?

图11.37 手机销量表

解题步骤

Excel提供了复合饼图和复合条饼图来展示图11.37所示的这种数据,以复合条饼图为例,具体操作步骤如下。

1.选择A1单元格,然后单击功能区的“插入”→“插入饼图或圆环图”→“复合条饼图”,图表效果如图11.38所示。

图11.38 默认状态的复合条饼图

2.双击图表系列中的任意点,在工作表右方会出现“设计数据系列格式”任务窗格,然后在此窗格中将“第二绘区中的值”选项的值由“3”修改为“4”,并将“第二绘图区大小”的百分比调整为100%。设置界面如图11.39所示,图11.40则是调整后的图表效果。

图11.39 设置数据系列的格式

图11.40 将“华为”移到饼图中

3.单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”。

4.选择图表中的数据标签,然后取消选择右方的“设置数据标签”任务窗格中的“值”复选框,选择“百分比”复选框。设置界面如图11.41所示,而图11.42则是调整后的最终效果。

图11.41 将数据标签改为百分比

图11.42 图表的最终效果

知识扩展

1.复合饼图或复合条饼图的特点是一个图表中显示一个主图和一个副图,主图总是包含副图中的数据,副图用于细化数据源中所有符合某个条件的数据。例如,据源中包含很多数据,其中部分数据拥有一个共同特点,那么可在副图中单独比较这部分有共同特点的数据。

复合图的优势在于既能整体比较又可局部比较。

本例中,华为、魅族、中兴、金立四家的销量较少,因此将它们作为一个整体在主图中参与比较,同时又在副图中额外比较一次。

2.生成图表时 Excel 会自动分配部分数据产生在副图中,绝大多数时候会分配有误,无法与用户的预期效果一致。当分配有误时,可在“设置系列点格式”任务窗格中调整。Excel 提供了4种调整方式,具体使用哪一种方式由数据的规律来决定。4种调整方式的功能说明见表11.1。

表11.1 复合图系列点说明

Excel 能否形象化地展示跳远成绩?

图11.32中的数据是8个运动员的跳远成绩,能否使用图表形象化地展示出这些数据?

图11.32 跳远成绩

解题步骤

跳远成绩宜用直方图来展示,具体操作步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图或条形图”→“簇状条形图”,默认生成的图表效果如图11.33所示。

图11.33 条形图的默认效果

2.双击图表系列(本图表的系列“成绩(米)”由8个系列点组成,双击任意点皆可),从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将分类间距修改为5%,此时图表将显示为图11.34所示的效果。

4.从“格式”选项卡左上角的“图表元素”列表中选择“水平 (值) 轴主要网格线”,然后按下Delete键删除网格线。

5.双击水平轴,然后在工作表右方的任务窗体中将最小值修改为0,将最大值修改为7,设置界面如图11.35所示。

图11.34 将分类间距调整5%

图11.35 修改水平轴的最大值和最小值

6.选择图表的系列,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时图表将显示为图11.36所示的效果。

图11.36 添加数据标签

知识扩展

1.直方图和柱形图的区别在于方向不同,其他方面都没有区别。

2.当图表中只有单个系列时,图表不会生成图例。尽管可以手工将图例调用出来,但是在这种前提下图例已失去了存在价值,可以不用显示图例。

3.图表系列的默认分类间距修改为5%后,系列点变得更宽,从而使图表更美观,同时保留了5%的间距,使图表系列看起来像一条一条跑道,配合“跳远”这个主题,使图表更加形象化。

Excel 如何设计目标数据和实际数据的达成率表?

图11.25中包含目标数据与实际的业务数据,现要求使用柱形图表将它们表现出来,同时以百分比的形式标示出达成率。

图11.25 目标数据与实际数据

解题步骤

柱形图或折线图本身无法生成包含百分比的数据标签,本例的思路是创建一个辅助列,在辅助列中计算每月的达成率,然后将达成率插入图表中,但是隐藏达成率的图表系列,仅保留其数据标签作参考,具体步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”,图表效果如图11.26所示。

图11.26 簇状柱形图的默认效果

2.在“格式”选项卡左上角的“图表元素”列表中选择“系列 "实际"”,然后单击下方的“设置所选内容格式”,从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将系列重叠值修改为50%,将分类间距修改为10%。

4.选择图表标题,将原文修改为“目标、实际与达成率”,然后将图表下方的图例移到右上角,按住绘图区下方的控制点向下拉,从而将绘图区拉伸至填满下方的图表区。此时图表将显示为图11.27所示的效果。

图11.27 调整图表系列及绘图区大小后的效果

5.在D1单元格中输入“达成率”,然后在D2单元格中输入公式“=C2/B2”,并将公式向下填充到D11。

6.选择 D2:D11 区域,然后选择图表并按组合键<Ctrl+V>,粘贴数据到图表中,此时图表将显示为图11.28所示的效果。

图11.28 添加辅助系列

7.在“格式”选项卡左上角的“图表元素”列表中选择“系列3”,然后单击下方的“设置所选内容格式”,从而在工作表右方产生“设置数据系列格式”任务窗格。

8.在任务窗格中单击“次坐标轴”,表示将图表的第3个系列放在次坐标轴中,设置界面如图11.29所示,而设置完成后的图表则将变为图11.30所示的效果。

图11.29 将辅助系列放到次坐标轴中

图11.30 添加辅助系列后

9.从“格式”选项卡左上角的“图表元素”列表中选择“系列3”,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“居中”。

10.从“格式”选项卡左上角的“图表元素”列表中选择“系列3”,然后单击功能区的“格式”→“形状填充”→“无填充颜色”,接着依次单击功能区的“格式”→“形状轮廓”→“无填充颜色”,此时图表中的“系列 3”没有轮廓、没有填充色,相当于被隐藏起来,只能看到目标数据和实际数据两个系列和“系列3”的数据标签,图表的具体显示效果如图11.31所示。

图11.31 隐藏辅助系列只保留标签

11.选择图例,然后单击图例中的“系列3”,按下Delete键删除。

知识扩展

1.Excel的图表支持次坐标轴,允许次坐标轴与主坐标轴使用不同的刻度值,因此设计复杂的图表往往需要用到次坐标轴。

次坐标轴的功能是让多个数值差异较大的图表系列在同一个图表中不起冲突,让两者都清晰地显现出来。假设本例不使次坐标轴,生成的图表就会像图11.28那样,由于“系列3”的数值太小,整个系列都显示在水平轴旁边,完全看不到图表系列的起伏变化,甚至会让人误以为图表中只有两个系列。

2.将“系列3”放到坐次标轴后,它会覆盖“目标”和“实际”两个系列。当为“系列3”添加数据标签之后不能删除“系列3”,因为删除系列的同时系列的数据标签也会一并消失,因此只能通过设置“无填充颜色”的方法隐藏“系列3”。

3.假设要求表示达成率的数据标签必须显示在“实际”系列的末端,那么只能手工逐个移动数据标签的位置,Excel不提供批量移动数据标签的工具。

Excel 如何设计目标数据和实际数据的差异表?

图11.16中包含目标数据与实际的业务数据,现要求使用图表将它们表现出来,需要同时展现两者的差异。

图11.16 目标数据与实际数据

解题步骤

突显差异值的直接解决方法是使用误差线,具体操作步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图和条形图”→“簇状柱形图”,图表效果如图11.17所示。

图11.17 簇状柱形图的默认效果

2.选中图表的任意位置,然后打开功能区的“格式”选项卡,在选项卡左上角有一个名为“图表元素”的组合键,单击其倒三角按钮会弹出一个列表,从列表中选择“系列 "实际"”。然后单击下方的“设置所选内容格式”,从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将系列重叠值修改为50%,将分类间距修改为10%,设置界面如图11.18所示,而设置后的图表将显示为如图11.19所示的效果:

图11.18 修改“系列 "实际"”的重叠

图11.19 调整后的图表

百分比与间距百分比

4.选择图表标题,将原文本修改为“目标与实际比较图”,然后将图表下方的图例移到右上角,按住绘图区下方的控制点向下拉,从而将绘图区拉伸至填满下方的图表区。此时图表将显示为图11.20所示的效果。

图11.20 移动图例及拉伸绘图区

通常,没有特殊要求时,图11.20已经是一个完整的图表,它将目标数据和实际数据两个系列半重叠显示,目的是告知图表查看者它们是一组数据、存在某种关联,吸引查看者去关注两者的差异。尽管在本质上此图11.20和图11.17没有区别,但是调整两个系列的重叠百分比之后有助于强调两者的关系,同时方便比较。

本例还要求标示出差值,因此还需要继续以下步骤。

5.在D1单元格输入“差值”,在D2单元格输入公式“=B2-C2”,然后将公式向下填充到D13。

6.从“格式”选项卡中的“图表元素”列表中选择“系列"目标"”,然后依次单击功能区的“设计”→“添加图表元素”→“误差线”→“其他误差线”选项。

7.在右边的“设置误差线格式”窗格中将误差线的方向设置为“负偏差”,将末端样式设置为“线端”,将误差量设置为“自定义”,最后单击“指定值”,弹出“自定义错误栏”对话框。

8.在“负错误值”栏中输入“=Sheet1!D2:D13”,然后单击“确定”按钮返回工作表界面。图11.21是误差线选项设置界面,图11.22则用于指定负错误值的来源。

图11.21 设置误差线选项

图11.22 设置负错误值来源

9.从“格式”选项卡中的“图表元素”列表中选择“系列 "实际"”,依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签内”;接着从“格式”选项卡中的“图表元素”列表中选择“系列 "目标"”,并依次单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时图表的最终效果如图11.23所示。

图11.23 图表的最终效果

知识扩展

1.误差值的原本作用并不是标示两个系列之间的差异,它属于统计学中的一个概念,用于标注当前系列的取值范围,允许上下浮动一定范围。由于Excel允许自定义误差值,因此它也可以用于连接两个系列或标注两个系列的数值差异。

2.当两个系列的大小相近时,应该将一个系列的标签放在靠上的位置,另一个系列的标签放在靠下的位置,从而避免标签重叠,影响美观,同时也可以提升查看图表的效率。

3.如果要求在图表中标注误差值,那么只能采用手工修改的方式来完成。例如,对系列“实际”添加数据标签,然后逐个选中数据标签,并修改为误差值,修改后的效果如图11.24所示。

图11.24 将系统“实际”的数据标签修改为误差值

图11.24中的标签207表明实际数据与目标数据还差207,-60则表示超过了目标值60个单元格。

Excel 2016 如何展示成绩升降趋势线?

图 11.1 是某学生一年级到六年级的语文成绩和数学成绩,查看文字很难瞬间判断成绩升降趋势,能否将数字图形化,从而瞬间判断数据的变化趋势呢?

图11.1 成绩表

解题步骤

折线图可以展示数据的升降趋势,设计折线图的步骤如下。

1.选择 A1 单元格,打开“插入”选项卡,然后单击“插入折线图或面积图”右方的倒三角按钮,最后从列表中选择“折线图”。图11.2展示了“折线图”的位置,而图11.3则为单击菜单后生成的默认样式的折线图。

图11.2 “折线图”位置展示

图11.3 折线图的默认样式

2.单击图表的标题,然后输入“成绩走势图”。

3.选择图例,并将它移到图表的右上角,然后将绘图区向下拉大,从而让绘图区填满下方的图表区。图11.4所示的箭头表示在该处按下鼠标拖动即可调整绘图区大小,图11.5是调整后的效果。

图11.4 扩大绘图区

图11.5 扩大绘图区后的效果

查看图表时的重点在于垂直轴的刻度、图例和图表系列,其中图例的功能是通过颜色来区分图表系列,图表系列则是图表的重中之重,它用于展示数据的大小和发展趋势。

图 11.6 展示了一个图表的基本组件,其中浮在网格线上方的两条折线即为图表系列,它们展示了语文和数学成绩的发展趋势。

图11.6 图表基本组件说明

从图11.6中可以看到语文成绩和数学成绩都在上下波动,但是整体上语文成绩呈上升趋势、数学成绩呈下降趋势。

如果要让图表系列显示数据,应按以下方式操作。

1.单击系列“语文”,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“上方”。

2.以同样的方式对系列“数学”添加数据标签,最终效果如图11.7所示。

图11.7 为图表系列添加数据标签

知识扩展

1.使用图表展示数据时有一个基本规则:如果没有特别指定图表的种类,那么当数据源不存在时间递进关系时,应使用柱形图或条形图,反之采用折线图。

本例中的数据来自六个年级的成绩,而这六个年级存在时间递进关系,因此宜用折线图展示数据的发展变化趋势。对于图11.8这种数据,则宜用柱状图或条形图,图表效果如图11.9所示。

图11.8 成绩表

图11.9 柱状的成绩比较图

线折图通常展示数据的变化趋势,柱状图则用于比较大小。当然也可以在柱状图中添加趋势线表明趋势,或者直接在一个图表中同时呈现柱形和折线,效果如图11.10所示。

图11.10 柱形与折线同时存在于一个图表中

图11.10的设计过程不再讲解,但是会提供一个已经设计好的成品供读者下载学习。

2.如果要突出显示数据的变化、差异,将数值轴(也称垂直轴)的最大值和最小值的差值缩小即可。操作方法是双击数值轴,然后在工作表右方的任务窗格中将最小值由 0 修改为 50,将最大值由100修改为95。图11.11是数值轴设置界面,图11.12则是调整后的效果。

图11.11 缩小最大值与最小值的差值

图11.12 调整后的图表效果

3.如果需要让图表显示效果更美观,如将图表区调整为圆角、阴影,那么可以双击图表靠边框的空白区域,然后在右方的任务窗格中依次单击“填充与线条”→“边框”→“圆角”,然后再依次单击“效果”→“阴影”→“右下斜偏移”。圆角与阴影的设置界面见图11.13和图11.14,而图11.15则是设置完成后的图表效果。

图11.13 让图表区显示为圆角

图11.14 让图表区显示为阴影

图11.15 添加圆角和阴影后的图表

Excel 批量插入签名图片到指定位置

图10.54所示的工资表中每一页末尾都有合计行与签名行,其中制表人的签名采用图10.54中右上角的图片,而非在单元格中输入姓名。

每一页末尾的“制表:”右方都需要插入该图片,有没有办法一次性将图片插入到所有的目标单元格中呢?

图10.54 工资明细及签名图

解题步骤

当图片的尺寸小于等于单元格或区域的尺寸时,复制单元格或区域时可以将图片一起复制,基于此前提,将图片放到第一个签名处,调整好大小与位置后复制到其他签名处即可,具体操作步骤如下。

1.将图片移动到 H48 单元格,然后将图片的高度调到小于等于 H48 单元格的高度,此时签名效果如图10.55所示。

图10.55 设置第一个签名

2.选择H48:I48区域,然后按组合键<Ctrl+C>复制单元格。

3.按组合键<Ctrl+A>全选所有单元格,然后按组合键<Ctrl+F>,弹出“查找和替换”对话框,将“查找内容”设置为“制表”,然后单击“查找全部”按钮,Excel会在下方罗列出所有找到的目标单元格地址,查找结果如图10.56所示。

图10.56 查找所有符合条件的单元格

4.按组合键<Ctrl+A>,选择所有找到的目标单元格,然后关闭对话框。

5.按组合键<Ctrl+V>粘贴数据。由于所有包含“制表”的单元格都处于选中状态,因此粘贴时会在所有“制表”所在单元格的右方生成签名图片,效果如图10.57所示。

图10.57 通过粘贴批量插入签名图片

知识扩展

1.要确保复制区域时将图片一并复制,必须选择“Excel选项”对话框中的“将插入对象与其父对象单元格一起剪切、复制和排序”复选框。

此选项默认处于选中状态,选项位置如图10.58所示。

图10.58 允许复制单元格时也一并复制图片

2.需要在工作表中大量使用图片时,图片一定要调到100KB以下再插入,对于本例这种签名尺寸的图片,在20KB以下即可。

Excel 能否像公式引用数据一样引用图片?

图10.49所示的工作表中,A列是图片名称,B列是图片。现要求在F2单元格中输入不同的图片名称时在G2单元格产生对应的图片,有办法实现此需求吗?

图10.49 工作表中的图片

解题步骤

根据名称引用对应的图片无法直接使用公式实现,需要使用名称和辅助图片,具体操作步骤如下。

1.按组合键<Ctrl+F3>,弹出“名称管理器”对话框。

2.单击“新建”按钮,然后在对话框中按图10.50所示的方式将名称设置为“图片”,将引用位置设置为以下公式:

图10.50 定义名称“图片”

3.单击“保存”按钮返回“名称管理器”对话框,然后单击“关闭”按钮返回工作表界面。4.随意插入一张图片,然后将它缩小,拖到G2单元格中,效果如图10.51所示。

图10.51 插入任意图片

5.在编辑栏中输入公式“=图片”,当单击Enter键后,图片内容会自动切换为F2单元格所指定的图片,效果如图10.52所示。

图10.52 引用a-002图片

6.在F2单元格中输入“a-025”,图片会自动更新为A列中值为“a-025”的单元格所对应的图片,效果如图10.53所示。

图10.53 引用a-025图片

知识扩展

1.不能直接选中图片后输入公式,但可以将公式定义为名称,然后将名称应用到图片中。

2.稳妥起见,定义名称时,名称中要使用的所有单元格都应采用绝对引用方式。

3.公式“=OFFSET($A$1,MATCH($F$2,$A$1:$A$29,0),1)”的含义是:F2单元格的值在A1:A29区域中排名第几,公式就返回B列中第几个单元格的图片。

此处OFFSET的功能是根据偏移量引用单元格,当MATCH的计算结果为10时,OFFSET就会引用A1向下偏移9行、向右偏移1列的单元格,即B列第10个单元格。

Excel 如何设计打印模板?

公司要求使用针式打印机打印送货单,预设的格式如图10.43所示。

图10.43 送货单模板

公司将以上自制的送货单样板送交印刷厂,印刷厂将它们印刷在空白的4联打印纸上,然后将打印纸卖给公司。现在的问题是,如何操作才能方便输入文字,同时又不会打印重影,因为纸张上已经有网格线和文字,如果Excel的单元格中也有网格线和文字,那么打印时就会重影,如果取消工作表中的所有网格线和标题文字,又会导致输入时不方便。

应该如何解决以上所有问题呢?

解题步骤

既要确保工作表中能看到网格线和标题文字,又不能在打印时将它们打印出来,最好的办法是将该画面截图,保存为jpg格式的文件,然后删除工作表中的文字和网格线,并将图片作为背景,具体操作步骤如下。

1.使用截图工具将送货单模板的打印区域截图,并保存为“样本.jpg”文件。

2.将“样本”工作表复制一份,且命名为“录入界面”。

3.全选工作表的所有单元格,然后删除所有文字,再删除所有边框(在边框列表中选择“无边框”即可)。

4.由于送货单正文仅需占用14行、9列,因此可以将“录入界面”工作表中的第14行以下的行和I列以后的列都隐藏起来,方法是选择第15行并按组合键<Ctrl+Shift+↓>,然后单击右键,从右键菜单中选择“隐藏”;接着选择 J 列并按下组合键<Ctrl+Shift+→>,然后单击右键,从右键菜单中选择“隐藏”,此时工作表状态如图10.44所示。

图10.44 去除文字、边框和多余行列的界面

5.单击功能区的“视图”→“网格线”,从而让工作表不显示网格线。

6.单击功能区的“页面布局”→“背景”,并在弹出的“插入图片”对话框中选择“来自文件”,从文件夹中找到“样本.jpg”后双击打开此文件。返回工作表界面后可以看到如图 10.45所示的效果。

图10.45 用打印模板图片作为背景后的显示效果

设置背景后,工作表中会显示网线格、标题文字,但是这些文字和网格线是无法打印出来的,仅用于标示每项数据的位置。

经过以上设置后,工作已经基本完成,不过为了简化工作量,还可以使用公式生成当前日期、金额合计及大写人民币,操作步骤如下。

1.在H2单元格中输入以下公式:

2.在G5单元格输入以下公式,然后将公式向下填充到G12单元格。

3.在G13单元格输入以下公式:

4.在B13单元格输入以下公式:

经过以上10个步骤,所有设计工作都已经完成,图10.46为添加公式后的效果。

图10.46 添加公式后的录入界面

5.下面输入一组数据来测试打印效果。

假设输入图10.47所示的数据,其中日期、总价、金额汇总和大写金额都是自动生成的。

图10.47 测试数据

6.按组合键<Ctrl+F2>,打开如图10.48所示的打印预览界面,可以看到尽管工作表中有边框、标题,但是实际打印时只出现当前订单相关的内容,从而避免了打印重影问题。

图10.48 打印预览效果

知识扩展

1.将图片放到页眉中,在打印工作表时会将图片一起打印出来,但将图片作为工作表背景插入到工作表中时,Excel无法将工作表背景图一起打印出来。本例正是利用此规则达成需求的。

2.由于打印纸张上的日期、金额汇总和人民币大写都已经有部分字符,使用公式生成的字符需要搭配原来的字符显示,不能覆盖纸张上的字符,因此在设置公式时需要在公式中产生若干空格,这些空格刚好对应纸张上的字符,从而避免打印后字符重叠或错位。

例如,公式“=""&TEXT(TODAY(),"yyyy mm dd ")”后面的3处空格是为了给打印纸张上的年、月、日三字预留的空间,而前面空格的作用则是为了让日期靠后显示。

3.在截图时要注意不能将行号、列标都截下来,只需要截正文即可,如A1:I14区域。截图不规范可能造成标题与正文错位。