EXCEL统计图表的绘制使用技巧.docx

1、EXCEL统计图表的绘制使用技巧EXCEL统计图表的绘制使用技巧EXCEL统计图表的绘制使用技巧2010-11-10 22：07 EXCEL统计图表的绘制使用技巧2007-03-30统计图表*知识点.工作簿与工作表Excel中每一个XLS文件对应的是一个工作簿，一个工作簿可以存储各种类型的工作表、图表和宏表等，因此我们可以将工作簿看成是一个活页夹。一般情况下，当启动Excel时，系统会自动打开一个临时标题为Book1的空白工作簿。如果需要打开已有的文件，可以在菜单上选择文件-打开，也可中常用工具栏中单击打开按钮，屏幕上出现打开对话框，如图2-1。在该对话框中的查找范围内确定驱动器和目录，再找到

2、需要打开的文件，击打开按钮。图2-1：打开文件对话框如果希望打开多个工作簿，只需重复打开文件的步骤；也可以在打开对话框中一次选取多个文件，让它们一次性全都打开。顺便一提选取多个文件有两种方法：如果需要的多个文件是连在一起的，则用Shift+鼠标点取。如果所需文件是分散的，则用Ctrl+鼠标点取。如果要打开最近使用过的文件，可以在菜单文件下方列示的文件中点击之，一般情况下文件菜单下方所列是将最近打开过的4个文件，如果要改变，操作如下：单击菜单工具-选项，出现选项对话框，如图2-2；选择常规卡，在最近使用的文件清单栏内填入具体的数目，按确定图2-2：选项对话框之常规卡一般情况下，我们面对的窗口只出

3、现一个工作簿的一张工作表。如果要切换到另一个工作簿上，可以有两种方法：选择窗口菜单，在其下方列出了所有打开的工作簿名称，只要点击想要打开的工作簿，就可将其显现出来；在菜单上选择窗口-重排窗口，在弹出的重排对话框上可以进行多窗口方式的选择，如选择平铺，则两个工作簿同时显示在屏幕上，如图2-3。图2-3：同时显示两个工作簿在多窗口打开的情况下，用鼠标单击某工作簿窗口的标题栏，按下鼠标左键，这时将出现虚线框，拖动鼠标，整个工作簿窗口随之移动，当移到合适位置时松开鼠标，工作簿窗口就固定在该处。如果想恢复显示一个工作簿，在菜单上选择窗口-重排窗口，在弹出的重排对话框中选当前活动工作簿窗口，见图2-4，按

4、确定。图2-4：重排窗口对话框有时候需要同时为一个工作簿设置多个窗口，以便观察，操作如下：打开一个工作簿窗口。单击菜单窗口-新建窗口。单击菜单窗口-重排窗口。在重排窗口对话框中选取平铺，按确定。这时已为该工作簿开设了两个窗口，两者的标题是不同的，结尾多了：1、：2。利用两个窗口，可同时观察一个工作簿，利用它们的各自水平、垂直滚动条观察需要的行或列的信息。工作簿与工作表的关系，就象活页夹与其中的每张活页纸，一张工作表就是一张活页纸。在一般情况下，一个工作簿包含6张工作表，当然，这也是可以改变的，操作如下：单击菜单工具-选项，出现选项对话框，如图2-2。选择常规卡，在新工作簿内的工作表数栏内填入具

5、体的数目，如4。按确定。对工作表的常用操作有：1.1.工作表的增加：在工作表Sheet3之前插入一个新工作表。操作如下：用鼠标单击状态栏中的Sheet3，使其成为当前工作表。单击菜单插入，弹出插入菜单。在插入菜单中选取工作表，再确定，即有Sheet5插入在Sheet3之前。2.工作表的删除：将工作表Sheet5删除。操作如下：用鼠标单击状态栏中的Sheet5，使其成为当前工作表。单击菜单编辑，选取删除工作表，这时系统弹出警告框，提醒删除的工作表将无法恢复。在回答是后，Sheet5被删掉，同时，它的右邻表Sheet3成为当前工作表。3.工作表的命名：工作簿给每种工作表都以Sheet1、Sheet

6、2、顺序命名，但为了能明确各工作表中所包含的内容，往往需要更名。将Sheet1改为ex1-1。操作如下：用鼠标双击状态栏中的Sheet1，Sheet1标签变黑；用Backspace键删去Sheet1，再输入ex1-1，敲回车。4.工作簿内工作表的移动和复制：单击状态栏内某工作表名后，按住鼠标左键，这时状态栏中显示一个小三角形(标签)加上一个小白长方块；拖动鼠标时该小标签指示可以插入的工作表位置，它随鼠标的移动而间断跳动，当鼠标指针处于两个工作表的分界线时松开左键，该工作表就定位于这两个工作表之间。复制与移动不同的是，复制除了移动的操作外，只需按住Ctrl键，此时小白长方块上会出现一个小+号。如

7、复制Sheet1，完成后在工作簿中将有一个Sheet1(2)出现。5.工作表在不同工作簿之间的移动和复制：如将例题与练习.XLS中工作表图1-10移动或复制到Book1中的Sheet1之前，操作如下：打开两个工作簿，用鼠标单击状态栏中的图1-10。单击菜单编辑，选取复制或移动工作表，弹出对话框，见图2-5；图2-5：在不同工作簿中移动和复制工作表在工作簿栏中选取Book1，在下列选定工作表之前里点中Sheet1；如果是移动则不需要选择建立副本；如果是复制，则必须点定建立副本，如图2-5所示。按确定后完成移动或复制任务。6.工作表的缩放：有时为了查看整个工作表的整体情况，可以通过视图-显示比例菜

8、单来调节屏幕的显示尺寸。如果需要将工作表放大一倍，则可选择200%项；如果需要将某一范围的内容显示在屏幕上，可以先选取该范围，再确定恰好容纳选定区域。顺便一提上述操作可以用更快捷的方式实现，用鼠标点中工作表名，再击右键，将弹出一个快捷菜单，上面有插入、删除、重命名、移动和复制等选项。.单元引用地址在Excel的公式中引用单元格地址有相对单元引用地址、绝对单元引用地址和混合单元引用地址之分，它们主要影响对包含有公式的单元格的拷贝。在每一个所创建的公式中，要仔细考虑使用何种类型的单元地址，如能区分各种单元地址的功效，就可在拷贝公式时避免错误。日常生活中，经常会碰到相对、绝对引用地址的概念。假若你在

9、学校，想让同学去邮局替你寄一封挂号信，你告诉他：出大门后向南走一百米后再向左转就到邮局，这是针对学校而言的，是相对地址，如果你在别处，上述方向就不适用了。但不管你在何处，你说邮局在中山北路301号，这就是一个绝对地址，它是不会改变的。1.相对引用地址Excel通常使用相对地址作为公式中的单元格引用，这就是说，当拷贝一个公式到新的位置，或用一个公式在一个区域内填充，公式中引用的单元格地址相对地发生变化，这也是我们常常所希望的。因为在很多时候，一个区域内的公式，只需要一个单元格内的公式写正确，再拷贝到其它单元格，希望公式引用的单元格地址自动地调整，这就需要相对单元引用。例1.现有1990-1998

10、年我国国内生产总值的资料，要求计算各年的环比发展速度。原始资料年份存放A列，国内生产总值存放B列，C列用于计算环比发展速度。1990年没有环比发展速度，故用-划去，在C3单元格，填入公式=B3/B2，将该公式复制到C4：C10，即完成计算任务。这里使用了相对单元地址。见图2-6。图2-6：相对、绝对单元地址实例我们可以查看通过复制的单元格C4，其公式是=B4/B3，这正是我们所希望的。2.绝对引用地址如果在拷贝公式的过程中，要保持某些单元格或区域的引用地址不发生变化，就必须用绝对单元引用地址。如果使用A1公式格式来指定绝对引用，可在列字母和行号数字前加上一个美元符号$来固定列和行。列字母、行数

11、字前都放置美元号$，在拷贝过程中该单元引用地址列、行都不发生变化。建立公式时，若公式中某个单元地址必须是绝对引用，可在列号与行号前顺次录入美元符号$。若公式已经键入成相对单元地址，可以选定需要改变的地址，按功能键F4。反复按功能键F4可以在几种不同的单元格地址引用方式间进行切换。按F4四次，可以使相对单元引用A1依次变为$A、A、$A1、A1。例2.原始资料如上题，要求计算各年的定基发展速度(基年定在1990年)。D列用于计算定基发展速度。在D2单元格，填入公式=B2/$B，将该公式复制到D3：D10，即完成计算任务。这里使用了绝对单元地址。见图2-6。我们可以查看通过复制的单元格D3，其公式

12、是=B3/$B，B2一直被放在分母上。3.混合引用地址若在拷贝公式的过程中，只希望公式中某个单元格引用地址的行号或列号不改变，这时就要使用混合单元引用地址。与绝对单元引用一样，要想让公式中某个单元引用的行或列号不改变，就需要在行或列号前加上美元符号$。仅列字母或行号前加上美元号的引用地址，在向水平或竖直方向拷贝公式时，所引用的列字母或行号不改变。以A1为例，其混合引用地址有：$A1、A。在投入产出分析中计算直接消耗系数时，如果正确使用混合引用地址，会给计算带来巨大的方便。在创建或修改公式时，利用功能键F4可以方便地使一个公式中所引用的单元地址从一个引用变为另一种引用。4.三维引用地址使用三维引

13、用地址可以用来引用多个工作表上的同一单元格或区域，工作簿必须包含多张工作表。操作如下：单击需要输入公式的单元格；键入=和函数名称，接着再键入左圆括号。单击需要引用的第一个工作表标签。按住SHIFT键，单击需要引用的最后一个工作表标签。选定需要引用的单元格或单元格区域。键入右圆括号，敲回车键。顺便一提循环引用，是指公式的内容引用到自己的数值，即公式本身的单元格。如在F1单元格输入公式=E1+F1*10，Excel会显示不能求出循环引用的解，给出一个提示框，见图2-7，按确定按钮后，单元格值为0，同时在状态栏会显示循环：F1。图2-7：循环引用的实例在需要反复运算求解循环引用的公式时，我们可以设定

14、其反复运算的次数与最大误差，求得它的近似解。设定操作如下：选择菜单工具-选项，在选项对话框中选重新计算卡，如图2-3。在重新计算卡上确认反复操作，在最多迭代次数和最大误差栏中填写合适的值，按确定按钮。例3.公司预计分配给员工的红利必须等于纯利的12%，而纯利又视红利的多少而定。如公司的毛利为50000万元，杂支费用为12000，则员工的红利与公司的纯利各为多少?见图2-7的A1-C5区域。Excel求解循环公式，是使用前一此反复运算的结果，来重新计算每一个涉及循环引用的单元格数值。在理论上，每一次运算后求出的数值，通常会更接近正确解，我们称之为收敛循环。如果其结果相反，我们则称之为发散循环。因

15、此当我们得到的数值为一发散值，可试着将某一固定值的起始值做些修正，再重新计算。一、统计表(一)编制统计表一张含有重要数据的统计表往往会保存很长一段时间，因而，应该精心设计每张统计表结构。一张统计表的基本的内容有：需要表示的是什么数据、按什么顺序来表示，以及要对数据执行什么操作。在开始使用Excel之前，先花些时间来考虑一下数据的要求，然后再建立统计表。下面举例说明统计表编制的基本步骤。例3.编制宏大计算机公司销售情况(上旬)统计表，见图2-8。图2-8：统计表的编制实例编表步骤：1.建立统计表：输入标题行、输入项目列、输入销售量和进销价格的具体数据，在E列利润栏，E3单元格输入公式=(D3-C

16、3)*B3，其经济含义是：利润等于销量乘以进销价差；该公式是相对公式，将复制到E4：E8，在E9单元格输入函数=SUM(E3：E8)，以计算出总利润。2.统计表的编辑：在完成对工作表的数据输入之后，可能会发现输入的某些数据有误或情况变化，需要对表中的数据进行修正。如果在输入数据过程中出了差错(在按回车键之前)，可按Backspace键删除掉最后的字符，也可以在取消框(编辑区中的按钮)单击鼠标，还可以按Esc键，删掉单元格的内容，重新键入数据。如果发现前面数据有错误，则将光标移至错误字符处，再按Delete键即可。如果要连续删除几个相邻的字符串，则在当前单元格中或编辑栏的公式框中，用鼠标拖拽这几

17、个字符串(这时被拖拽的字符变成反白显示)，再按退格键或Delete即可全部删除。如果是确定后修改输入数据：删除一个单元格的数据，只要在窗口工作表中单击指定单元格，把指定单元格变为当前单元格，然后敲键盘上的DEL键，或把指定单元格变为当前单元格，再单击菜单编辑-清除，则指定单元格的内容就被删除了，而指定单元格仍为当前单元格。更简单的方法是：选定要删除的单元格，单击鼠标右键，出现一个菜单，选择清除内容，则指定单元格的内容被清除。这里的删除操作实际上只是清除了指定单元格的内容，而指定单元格空间仍保留。有时需要修改的单元格中内容复杂或数据太长，而要改动的只是其中的一部分，如对该单元格进行重新输入，则费

18、时费力。简便方法是：单击指定的单元格，编辑栏中出现单元格中的内容，如果在编辑栏中想要进行修改的数据位置上双击，则该位置出现一个竖条光标，在编辑栏此位置就可进行相应的插入、删除、覆盖等修改操作了，也可以用键盘上的、键来左右移动光标寻找到需要修改的位置，修改完毕，单击编辑区中的按钮，系统即承认修改，并按修改的内容填入单元格，如果单元格中是公式或函数，则重新计算并填入新的值。双击单元格是使该单元格直接进入编辑状态，可以添加数据，而如果单击单元格仅使该单元格进入活动状态，这时若输入数据，系统先清除原有数据，然后存入本次输入数据。3.数据定位、查找与替换：有时统计表很大，窗口中无法显示全部数据，若需要修

19、改的内容没在窗口中，则首先要寻找到指定的单元格，通过窗口滚动条来翻动统计表，把需要的部分调入窗口中，但这比较慢。若明确知道需要修改的单元格或内容，则可以用Excel提供的方法快速地进行定位和替换。(1)定位：定位是一种直接用单元格的地址名或名字，把指定单元格快速变为当前单元并在窗口中显示的方法。操作如下：单击菜单上编辑-定位，或按F5键，则弹出对话框，见图2-9。图2-9：定位对话框在定位框中有指定的单元格地址，如P100，按确定按钮或回车。则系统自动调到指定单元格所在的区域并在窗口中显示它，指定单元格成为当前单元格。(2)查找：查找是一种根据指定单元格中的数据内容，把指定单元格快速变为当前单

20、位格并在窗口中显示的方法。操作如下：单击菜单上编辑-查找，或按Shift+F5键，弹出对话框，见图2-10。图2-10：查找对话框定义查找的各种设置：*单击搜索方式框右边的小按钮，弹出一个列表，有两项，选按行，则按搜索行序的先后顺序进行；选按列，则按搜索列序的先后顺序进行。*单击搜索内容框右边的小按钮，弹出一个列表，有3项：选公式，则对数值、字符内容都可以查找，但对公式单元，只能查公式的形式，不能查公式的运算结果；选值，对数值、字符内容都可以查找，但对公式单元，只能查公式的运算结果，能够查公式的形式；选附注，查找代有附注的所有单元格。*单击区分大小写框的，框中有，则查找字符串时字母区分大小写；

21、框中无，查找时不区分大小写。*单击单元格匹配框的，框中有，则查找字符串时是部分匹配查找，如查找内容为321，单元格B5中有321，单元格B10中有32109，则查找时B5、B10都能被查到。框中无，则查找字符串时是全部匹配查找，对上述情况，单元格B5被查到，而单元格B10则查不到。单击查找下一个按钮或回车，便开始查找。找到一个符合的条件单元格就停止，单击关闭按钮，返回符合条件的单元格，并把它作为当前单元格，或单击查找下一个按钮或回车，继续查找。(3)替换：替换是在查找的同时进行替换操作，对话框中替换条件的设置与查找条件的设置一样，只是在替换框中添如替换内容。例如，要把查找到的含有数字50的单元

22、格的内容50改为100，如单击全部替换按钮，则把找到的所有单元格的50都改为100；否则，找到一个含有数字50的单元格就停止，若单击替换按钮，则进行替换，不单击替换按钮，就不进行替换，直至单击查找下一个按钮，继续查找下一个符合条件的单元格。4.统计表的美化：Excel提供了格式工具栏和格式菜单。一般来说，常用和格式工具栏会出现在窗口上，如果没有格式工具栏，可以将它请出来，操作如下：打开视图-工具栏。选择格式项，格式工具栏就会出现在窗口上方。在格式工具栏中既可以改变字体、字号，进行各种类型的对齐；又有可以制作各类边框线，改变背景、字体和表格线的颜色。除了格式工具栏，可以帮助我们进行统计表美化的还

23、有格式菜单，它具有更为细致的格式调节功能，如可以进行行高列宽的调整，等等。美化统计表需要耐心和艺术眼光，没有定式。但简洁、清晰是统计表的基本要求。顺便一提灵活地使用鼠标，可以快捷地完成的行、列格式的调整。另外常用工具栏中的格式刷是统一格式的有力工具。(二)汇总统计表Excel能对多个工作表和工作簿内相类似的数据进行合并计算，将合并的结果放到一张(合并)工作表内。这种能力可以用来把各门市部的销售收入等合并到各销售区，再把销售区的数据合并到总公司；或是统计部门用此功能将月度资料汇总成季度资料，再将其汇总成年度资料。这里的合并计算，不仅仅指求和计算，还可以计算平均数、最大值、最小值等，共可作11种合

24、并计算，合并还可以包括链接数据。要合并的多张工作表，可以是标题排列顺序完全相同的表、也可以不完全相同。如果是完全相同，Excel按各工作表内对应位置对各单元格施行合并计算；如果是不完全相同，可以要求Excel根据各个源工作表内数据的行、列标题来进行合并，合并计算那些有相同行和相同列标题的单元格内的数据，而不管行、列标题在各个源工作表内的位置次序。1.1.完全相同型的合并对于各个要合并计算的工作表，不仅行、列标题完全一样，而且次序也完全一样，但各个数据区的位置可以不一样。这种合并十分简单，可以按照存放合并数据的目标工作表单元格或区域的选取，表2-1是完全相同型的合并规则。表2-1：完全相同型的合

25、并规则目标选取合并结果一个单元格结果数据占用源数据区域同样形状、大小的区域一行单元格从所选行起向下填合并结果，最多有数据的行数为所选行数一列单元格从所选列起右填合并结果，最多有数据的列数为所选列数区域尽可能多地填入所选区域例4.现有宏大计算机公司某月上、中、下旬销售情况的统计报表，要求汇总各旬情况，完成月度销售情况的报表。进行合并操作如下：选定一个目标区域，本例为月度汇总表的B3：B8区域。请只选要显示合并数据的位置，注意文本、日期等数据是不能参与合并的。如果需要合并结果的行、列标题，先将它们从一个源工作表复制到目标工作表后，再选目标区域。选取数据-合并计算，弹出合并计算对话框，见图2-11。

26、图2-11：合并计算对话框在函数列表框内选取合并的计算方式，预选为求和，不改变时可以跳过。在引用位置文本框内，通过选取或直接键入方式确定源区域。如果源区域位于打开的工作表上，可以通过鼠标单击加拖动方式；如果源区域在未打开的工作簿文件内，应键入路径名、工作簿名、工作表名与区域(或区域名称)，或者单击浏览按钮，打开打开文件对话框，再选取文件名称。单击增加按钮，将引用位置文本框内的区域加入到所有引用位置列表框内。重复第四、五步，将所有源区域(可多达255个)增加到所有引用位置列表框内。注销标记位置复选钮组内的顶端行、最左列选择框，注销(也可选)与源数据链接选择框。如果选中与源数据链接选择框，结果在目

27、标工作表内生成分级显示；最后单击确定按钮。特别注意在做报表汇总时，第一，要留足适当大空间给目标区域，否则合并计算结果要覆盖原有数据，造成数据丢失。第二，只有数值数据参与合并计算，文本和公式不能进行合并计算。2.不完全相同型的合并当源工作表上数据的行列标题位置不完全相同时，要使用行、列标题进行合并计算。这种方法最为灵活方便，容许每个源区域的实际位置不同。例5.某计算机总公司下属宏大公司1-3月(见图2-12的窗口1)和光大公司1-4月(见图2-12的窗口2)的销售量统计报表已上报，要求汇总出分品种、分月份的销售量。注意下属两公司的销售品种是不同的。见图2-12。图2-12：合并统计表的实例进行合

28、并操作如下：选取目标区域，在公司总销售工作表上选取A1：E9。如果希望按照标题的指定次序，应选取事先准备好行列标题的区域，但标题要与源区域内标题拼写完全一致。若目标区域不包括标题，Excel会自动生成标题。选取数据-合并计算，打开合并计算对话框。在函数列表内选取合并的计算方式，预设为求和，若不想改变可以跳过。在引用位置文本框内，通过鼠标选取或直接键入确定源区域。如果源区域位于打开的工作表上，可以通过鼠标单击加拖动方式，或直接按外部引用公式键入源区域引用；如果源区域在未打开的工作簿上，应键入路径名、工作簿名、工作表名与区域(或区域名称)，或者单击浏览按钮，以弹出打开文件对话框，再选取文件名称。单

29、击增加按钮，将引用位置文本框的区域加到所有引用位置框内。重复第四、五步，将所有源区域(最多多达255个)增加到所有引用位置框内。在标记位置复选钮组选中顶端行、最左列选择框，注销与源数据链接选择框；最后单击确定按钮。特别注意在合并计算中：第一，要删除或编辑源数据区域，可在弹出的合并计算对话框内操作。要删去某个已选的源数据区域，可以先在所有引用位置列表框内选中它，再单击删除按钮；要编辑一个源数据区域，可先选中它，然后在引用位置文本框内修改它，认可后单击增加按钮，并删除原来的源数据区域；第二，在第步中可以选中与源数据链接选择框，其结果如图2-13，它与如图2-12的窗口3的数据完全一致，但有两点不同

30、：一是Excel自动创建分级显示符号，它可以查看数据的来源；二是如果源数据区域中的数据被修改，则汇总表的数据也随之改变，而前者不会发生变化。图2-13：合并统计表(加链接)(三)保护数据当我们不得不放下手中的工作，但又不希望关闭Excel时，为了防止因误操作而造成对工作表数据的损害，为工作表或工作簿建立保护是最好的选择。有时也会需要对某些单元格实施保护，如上级部门向下级单位发放的各种报表，需要填写的是数据，对于指标名称、计量单位等都不需要改变，以便于汇总，为此指标名称等要保护，而填写数据的地方则必须是开放的，不能保护。Excel提供了几种方法，用于保护工作簿、工作表、单元格、图形对象和公式，以阻止被别人存取或修改。1.1.保护工作表保护工作表操作如下：单击菜单工具-保护-保护工作表，出现对话框，见图2-14。图2-14：保护工作表对话框选择保护功能：内容，即禁止更改工作表中的单元格与图表项；对象，即在工作图表中禁止删除、移动、编辑或缩放图形对象；方案，即在工作表中，禁止更改方案的定义。密码，即输入密码可防止别人取消对工作表的保护。