宁波市公安局经侦大队:Excel技巧大全（三）

Excel技巧大全（三）

转载

杂谈

Excel技巧大全（三）

轻松打造你的Excel图表　　

       一、方便灵活的文本框
      你可以在图表中的任何地方增加可移动的文本内容(不限于标题)。方法：选定图表除标题或数据系列外的任何部分，然后在编辑栏中键入文本内容，接着按回车键。这样，图表中就自动生成了包含键入内容的文本框，你可以把它移到任何地方并根据自己的喜好改变文本框的格式。
      二、文本框与单元格的链接
      上面介绍了在图表中增加方便灵活的文本框的技巧，现在再介绍怎样将工作表单元格中的内容同图表文本框进行链接。在工作表的空白单元格内输入要链接的文本，单击选中图表，在编辑栏输入等号“=”，然后单击包含要链接文本的单元格，接着按回车键，该文本就出现在图表中的某个位置上。这样，不管什么时候工作表单元格内的文本发生变化时，图表内的文本也随着改变。但要注意的是，一旦你取消了选中文本框，就很难再用鼠标选中该文本框进行进一步的修改，此时你可以使用箭头键移动选择图表元素。
      三、自动更新的智能图表
      很多情况下，我们制作了一个图表后，经常需要向图表添加新的数据点。更新一个图表的引用数据范围并不难，我们只要使用一些特殊技巧就可以创建一个在你对工作表增加或删除数据点时会自动更新的智能图表。
      下面的实例介绍了如何创建自动更新图表的具体方法：
      1.创建如图1所示的图表。
      2.选择“插入”/“名称”/“定义”，出现“定义名称”对话框。在“当前工作簿中的名称”栏中输入“月份”，在“引用位置”栏内输入公式=OFFSET(sheet1!$A$2,0,0,COUNTA(sheet1!$A:$A)-1)”，单击“添加”。再在“当前工作簿中的名称”栏中输入“贷款”，在“引用位置”栏内输入公式“=OFFSET(sheet1!$B$2,0,0,COUNTA(sheet1!$B:$B)-1”单击“添加”。然后单击“确定”按钮，关闭“定义名称”对话框。
      3.激活图表，并选择数据系列。你可以看到，在本例编辑栏中的公式为“=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$7,Sheet1!$B$2:$B$7,1)”。现在修改此公式为“=SERIES(Sheet1!月份,Sheet1!贷款，1)”，即用已定义的名称取代单元格区域引用。这样，自动更新的智能图表就制作好了。以后，当你在A列和B列增加或删除数据时图表会自动更新。
      几点说明：
      ●OFFSET函数指定第一个数据点(A2单元格)以及应用COUNTA函数读取列中的数据点。因为A列在第一行有标题，公式中减去1。
      ●每个数据系列都有一个相关连的SERIES公式，当你在图中选择数据系列时它就会出现在编辑栏中。SERIES公式由4个变量的SERIES函数组成，你可以直接在SERIES公式中编辑所指定的数据范围，甚至可以直接输入一个新的SERIES公式。要注意的是，SERIES函数使用的是绝对单元格地址，要改变数据系列使用的数据，就得在编辑栏公式中使用绝对单元格地址。
      四、有用而危险的技巧
      选择图表数据系列中的一个数据点，然后按照数值增大或减少的方向拖动数据点，你会发现工作表中的相应数值随着图中数据点的新位置改变而改变。如果你知道一个图的外形以及你要确定能生成该图的数值，这种技巧就显得非常有用。但要注意的是，这种方法在大多数情况下是危险的，因为你可能在不经意间更改了不应该更改的数值。
      五、断开图与数据联系的“死”图
      我们知道，图表中的数据系列是与存储在工作表的数据相联系的。当然，你也可以断开这种联系，使得数据系列不再依赖工作表中的数据。具体方法为：选择图表中的数据系列，激活编辑栏，按F9键，再按回车键即可。这样就把该数据系列的区域引用公式转变为包含工作表数据的数组，从而断开了图与数据的联系。如果你断开图中所有数据系列的联系，你的图就变成了一个不依赖工作表中数据的“死”图。
      六、美化你的数据表
      我们知道，图表中可以使用数据表。如果你觉得图表中的数据表不是很灵活的话(比如其格式化不是很方便)，你可以粘贴链接图片到图表来代替数据表。下面的技巧很有用，特别是将单元格区域复制成图片的方法也许对你有所帮助。创建好图表，并将数据表使用的单元格区域按你的需要进行格式化。选定需要的单元格区域，按住Shift键，选择“编辑”菜单中的“复制图片”命令，出现一个“复制图片”对话框，单击“确定”接受默认选项。这样，选定的单元格区域就作为一个图片复制到剪贴板中了。激活图表，将剪贴板中的内容粘贴到图表。此时，所粘贴的是一幅图，还不是链接的表，还需要你选择粘贴的图片，在编辑栏输入链接的单元格区域(或直接用鼠标选择)。图2中我们给出了一个示例，图中链接的公式为“=sheet1A1:F3”。这样，粘贴的图片就变成与工作表数据区域链接的图片，对于工作表中单元格区域A1:F3中的任何改变，都会直接反映在图表链接的图片中。怎么样，这样的数据表不错吧！

在Excel中打印工资条　　 在Excel中打印工资条
      现在许多单位都实现了工资代储，但每月的工资清单(工资条)还得要发给职工。如果用Excel程序处理的工资表，打印后每页只有一个表头，剪裁成条后除了每页的第一名职工外，其余的就没有表头了，这样将使职工无法清楚工资条的数字对应什么具体名目。怎样才能给每个职工的工资条加上工资表表头呢？这里笔者将解决方案介绍给大家。
      首先根据每个职工工资具有唯一电脑序号的特点，使用VBA里的判断语句，让循环从第一个电脑序号开始，如果Excel表中相邻两行的电脑序号不同，就在这两行之间插入一个空白行，然后使用循环语句，把表头的内容依次复制粘贴到每个空白行。
      下面是具体的操作步骤：
      1.在Excel中打开需要制作工资条的工资表文件。
      2.打开“工具”/“宏”(如果在菜单中没有看见“宏”，让鼠标在菜单上稍停留或者按向下的双箭头就会出现)，选择“Visual
      Basic编辑器”(图1)打开VBA编辑器窗口，在VBAProject的工程资源管理器中双击Sheet1，会出现代码编辑窗口，在代码编辑窗口输入如下代码(图2)：
      Sub gongzitiao()
      Application.ScreenUpdating = False
      '为避免破坏表一，将表一内容完整复制到表二
      Sheets(1).[A1].CurrentRegion.Copy Sheets(2).[A1]
      '定义循环变量的最大值不小于表二中职工数的二倍(我校职工总数为2564)
      a = (Application.WorksheetFunction.CountA(Sheets(2).[a1:a2600] * 2
      '如果第一列(职工的工资电脑序号)上下单元格的值不相等，则在它们之间插入一个空白行
      For i = 3 To a
      If Sheets(2).Cells(i，1)<>Sheets(2).Cells(i + 1，1) And (Sheets(2).Cells(i，
      1) <> "") Then
      Sheets(2).Rows(i + 1).Insert
      End If
      '如果第一列中的单元格为空，则将表中[A2:M2](工资清单表头项目)复制到此行
      If Sheets(2).Cells(i，1) = "" Then
      Sheets(2).[A2:M2].Copy Sheets(2).Cells(i，1)
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      3.检查源代码编辑无误后，在VBA窗口中打开菜单“运行”/“运行宏”，将弹出“宏”窗口，你只需单击“运行”即可。
      4.待宏运行完毕，再回到Excel，单击工资表下方的“Sheet2”，于是在每两个相邻职工间就插入了工资清单项目(图3)，再用打印机打印并裁条后，就可以发放给每位职工了。
      该操作在Excel 2000下调试通过。

让数据透视表帮你轻松汇总数据──做一个可查询，能自动计算的汇总表　　
      一、什么叫做数据透视表
      笔者从事建设工程质量监督工作，每天都要同大量的工程数据打交道，什么建筑面积、工程造价等，并且常常要按照不同类型(如按施工单位的“资质等级”、“工程类型”、“质量等级”等)进行分类汇总。在以前，这些工作都是靠计算器，一个一个、反反复复地敲出来的，每统计一次，我总是敲得腰酸背痛。后来，我用上了电脑，用Excel来管理这些令人头痛的数据，运用其“数据透视表”功能，使原来十分繁杂的统计工作，变得既轻松又简单了。
      图1是我天天要面对的一张数据表，是用Excel XP制作的。下面，我们就利用Excel XP(在Excel
      2000中同样可以实现)的“数据透视表”功能，按施工单位“资质等级”、“工程类型”和“质量等级”来汇总“建筑面积”和“工程造价”图2，这样可以方便我轻松查询记录的数据，和自动计算我想得到的计算数据。
      当然，这个功能你可以用来记录家庭的日常理财状况、公司里简单的财务状况，甚至编辑们也可以用来统计稿费。总之，它的应用面非常广，这期我们先来学习一下如何把一个数据表格编程可查询的数据透视表。下期将带领大家来品尝一下它的广泛应用。
      二、六步就能创建数据透视表
      1.启动Excel XP，打开需要创建数据透视表的工作表。
      2.选中数据所在单元格区域中的任意一个单元格，执行“数据→数据透视表和数据透视图”命令，启动“数据透视表和数据透视图向导──3”，并进入“步骤之1”图3。
      3.按“下一步”按钮，进入“步骤之2”，此时，系统会将数据区域智能化地填入“选定区域”后面的方框中图4。
      提示：如果你的数据源不是Excel工作表，而是用其他程序(如Access等)制作的，请在“步骤之1”中选中“外部数据源”选项，然后再按“下一步”按钮，并按随后的提示操作。
      4.再按“下一步”按钮，进入“步骤之3”。如果将数据透视表放在一个新建的工作表中，请选定“新建工作表”选项(通常是默认选项)；如果将“数据透视表”与数据源放在同一个工作表中，请选定“现有工作表”选项，并指定存放区域图5。
      5.按“完成”按钮，此时，系统新建一个工作表用来存放“数据透视表”的汇总数据，并自动打开“数据透视表”工具栏和“数据透视表字段”列表图6。
      6.到了这一步是非常重要的。我们根据汇总的需要，将“数据透视表字段列表”中相应的字段(就是我们通常所说的工作表列标题)拖到数据透视表相应区域：
      比如在我这个例子中，①将“质量等级”字段拖到“页字段”处(位于“数据透视表”区域上方)。②将“资质等级”字段拖到“行字段”处(位于“数据透视表”区域左侧)。③将“工程类型”字段拖到“列字段”处(位于“数据透视表”区域上方)。④将“建筑面积”(或“工程造价”)字段拖到“数据项”处(位于“数据透视表”区域中部)图7。
      至此，数据透视表制作完成，汇总结果便如图2所示。
      提示：制作好的数据透视表，同普通工作表一样，可以设置相应的格式(如字体、字号、对齐方式等)。
      三、与数据透视表相关的几个名词
      行字段：数据源中的字段(工作表的列标题之一)，在数据透视表的行方向显示出来。例如此处的“资质等级”字段。
      列字段：数据源中的字段(工作表的列标题之一)，在数据透视表的列方向显示出来。例如此处的“工程类型”字段。
      页字段：数据源中的字段(工作表的列标题之一)，在数据透视表中的上方显示出来，用于“筛选”不同的数据进行统计(如“合格”、“优良”、“全部”等)。例如此处的“质量等级”字段。
      数据区域：包含汇总数据的数据透视表单元格。如此处的根据“建筑面积”或“工程造价”字段汇总后的结果。

让表格自动填色　　 让表格自动填色
       Excel除了具有自由计算、自动排名、自行分析之类的功能外，它还能对自己单元格中的数据自动填上颜色。本文就以Excel
      XP编制的新生入学成绩表为例介绍具体的操作方法。
      1.打开Excel
      XP，新建一张空白表格。在第一行A1～F1中依次填入“姓名”、“语文”、“数学”、“外语”、“综合科目”和“总分”字样。然后在表格第一列A2～A6中依次填入学生姓名，并在表格第一列最下端填入“平均分”字样。如图1所示，在每个学生的相应科目下填入各自的成绩。最后将此表格保存为“新生入学成绩表”文件。
      2.为了使各有所长的新生的成绩一目了然，制作的成绩表应让入学分数在90以下的数值显示为“倾斜”的“灰色”；让分数在90～120之间的数值显示为“橙色”；让分数在120以上的数值显示为“粗体”、“红色”。
      3.用鼠标选中整个分数区域，单击“格式”菜单的“条件格式”选项。在弹出对话框图2中将“条件1”下的项目依次设置成“单元格数值”、“小于”、“90”，然后单击“格式”按钮。如图3所示，在“字体”卡片上将该种类下的文字“字形”设为“斜体”、“颜色”设为“80%的灰”。接着在“边框”卡片上选择一种“全包围”的“外边框”，按“确定”。
      4.回到“条件格式”的主对话框，暂不要“确定”退出，而是单击“添加”按钮，增添“条件2”为“单元格数值”、“介于”、“90”与“120”之间，其“格式”为常规字型的“绿色”；重复操作，增添“条件3”为“单元格数值”、“大于或等于”、“120”，文字“格式”设为“粗体”的“红色”(图2)。
      5.设置完毕，按“确定”返回。效果如图4所示。展现在我们眼前的表格已经是“五彩缤纷”了吧！所有不及格的分数都变成了深灰，同时还被加了个以示警惕的外框线；而所有介于90～120分之间的数值都成了墨绿色；当然，那些超过120的数值也都变成了加粗体的红色字。你看，面对这份条目明晰的彩色表格，哪个学生较差、哪个学生偏科严重、哪个学生实力均衡不就都一目了然了吗！此外，这里告诉各位一个小秘密：如果你硬将某一“条件格式”应用到其他单元格中，只需选定该应用格式，然后单击“格式刷”到目标区域“刷”一下即可。
      6.现在单科成绩的分值已经区分开来，该是对“总分”和“平均值”进行计算了。我们将光标定位到F2单元格，然后在“fx”区域中填入“=B2+C2+D2+E2”字样，意即将B2-E2单元格中的数值进行求和计算。输入完毕，回车即可看到结果。如图5所示，为了避免重复输入，我们右击F2单元格，选择“复制”，然后拖动鼠标选中F3～F6，仍然右击之，在弹出菜单中选择“粘贴”命令。这时我们会发现，F3～F6单元格中已经出现了相应的求和结果了。值得注意的是，Excel
      XP并非仅仅只粘贴了单纯的“fx”方程式，而是将该类型的函数计算动作应用到了选中区域。
      7.重复步骤3的操作，单击“格式”菜单的“条件格式”选项，在弹出对话框中将“条件1”设为“单元格数值”、“小于”、“500”。然后单击“格式”按钮，在“字体”卡片上将文字设为“白色”，并在“图案”卡片中选择一种无底纹的“紫色”。回到“条件格式”的主对话框，单击“添加”按钮，增添“条件2”为“单元格数值”、“大于或等于”、“500”，其“格式”为常规字型的“黑色”，按“确定”(效果如图6所示)。
      8.最后将光标定位到B7单元格，我们来算平均值。首先在“fx”区域中填入“=AVERAGE(B2，B6)”字样，意即算出B2-B6单元格中数值的平均值。和上一步骤一样，为了避免重复输入，我们右击B7单元格，选择“复制”，然后拖动鼠标选中C7-F7，在右键菜单中选择“粘贴”。这时我们会发现，C7-F7单元格中也如我们所愿出现了相应的数值了。
      9.由于该行是各科成绩的平均值，所以我们不便像如上数值一样进行“条件格式”填色，我们可以直接选取，手工定义。选择B7-F7单元格，将它定义为带“下划线”的“14号”字(见图6)。怎么样，大功告成的成绩表是不是有着不同于常规表格的别样风情呢！

数据透视表应用拓展──让表更智能和更有个性　　 数据透视表应用拓展──让表更智能和更有个性
      可以随意更改表中数据项
      我们这里说的数据项就是数据透视表中的行、列和页字段下的子类别。
      这些数据项都能够随意修改，或者为方便查询某组数据而把数据在表中排列得更为合理。
      这时，我们可以点击在某个数据项右边的下拉按钮，在随后弹出的下拉列表(如图1)中，选中其中的某个项目(子类别)，然后按“确定”按钮，数据透视表中的数据便可根据你的选择筛选数据了。
      提示：当然，你还可以清除或者隐藏表中的某组数据。你只需要在数据项前面复选框按右边的下拉按钮，在随后弹出的下拉列表中，清除某个项目(子类别)前面复选框中的“∨”号，然后“确定”即可。
      对数据智能计算
      就此例子，我们希望能够得到“建筑面积”的求和项，或者以其他计算方式计算某个数据项，我们可以采取下面的方法来进行：
      简单地数据汇总
      鼠标单击数据透视表数据区域，打开“数据透视表字段列表”，将“工程造价”字段也拖至“数据区域”，让其与“建筑面积”共同汇总出来，显示结果如图2所示。
      提示：如果鼠标单击数据区域时，“数据透视表字段列表”不能显示出来，可以这样来打开：执行“视图→工具栏→数据透视表”命令，打开“数据透视表”工具栏，按工具栏最右端的“显示字段列表”按钮即可。
      其他计算方式
      通常情况下，数据透视表默认将数据按“求和”的方法进行汇总，如果我们重新选择，就可以达到汇总其他计算项目的目的。例如：在此处，我们要汇总工程项目数，可以这样来操作：
      用鼠标在数据透视表的“求和项：建筑面积”上双击，打开“数据透视表字段”对话框(如图3)，选中“汇总方式”下面的“计数”选项后，按“确定”按钮，即可统计出各项目的工程数目，结果如图4所示。
      表中数据如何智能更新
      如果修改了数据源中的数据，就需要更新数据透视表中的数据，否则汇总结果不正确。更新数据可以采取下面三种方法来实现：
      ①打开“数据透视表”工具栏，选中数据区域中任何一个单元格，按工具栏上的“更新数据”按钮(一个感叹号)即可图5。
      ②选中数据区域中任何一个单元格，右击鼠标，在随后弹出的快捷菜单中，选“更新数据”选项即可。
      ③选中数据区域中任何一个单元格，右击鼠标，在随后弹出的快捷菜单中，选“表格选项”，打开“数据透视表选项”对话框(如图6)，选中其中的“打开时刷新”选项，“确定”退出，以后打开相应的工作簿文件时，数据即被自动更新。
      制作更个性的表单
      如果你想改变行、列及页字段，即修改数据透视表的布局，可以这样来进行：
      ①选中数据区域中任何一个单元格，执行“数据→数据透视表和数据透视图”命令，直接按“步骤之3”。
      ②按其中的“布局”按钮，打开“数据透视表和数据透视图向导──布局”对话框(如图6)。
      ③选中需要修改的字段(如行字段──资质等级)，将它拖回到右侧的字段列表中。然后将新字段拖至相应区域(行字段区域)。
      ④仿照第③步的操作，完成其他字段的修改。
      ⑤对布局满意后，按“确定”按钮返回“步骤之3”，再按下“完成”按钮即可。
      将数据透视表打印出来
      和打印正常工作表一样，按下“常用”工具栏上的“打印”按钮，即可将数据透视表打印出来。如果你对打印出来的透视表不满意，可以对其显示方式进行调整：
      打开“数据透视表”工具栏，选中数据区域中任何一个单元格，按工具栏上的“设置报告格式”按钮，打开“自动套用格式”对话框，选中一个你满意的报表格式后(初次使用该功能，常常需要反复测试)，按下“确定”按钮，再执行打印操作，就能打印出满意的汇总报表了。
      制作汇总图表
      打开“数据透视表”工具栏，选中数据区域中任何一个单元格，按工具栏上的“图表向导”按钮，系统会自动新建一个名为“Chart1”的工作表，并在其中以默认的“柱形图”的形式建立起数据透视表的图表(如图7)。
      提示：①上述对“数据透视表”的某些拓展使用功能(如“项目的选择”、“汇总子项的选择”等)，在图表中也可以实现。②对“图表类型”、“图表选项”等内容的更改，同普通EXCEL图表一样，在此不再赘述。