EXCEL统计图表的绘制使用技巧Word文件下载.docx

1、17 如果想恢复显示一个工作簿，在菜单上选择“窗口”“重排窗口”，在弹出的重排对话框中选“当前活动工作簿窗口”，见图2-4，按“确定”。18 19 图2-4：“重排窗口”对话框20 有时候需要同时为一个工作簿设置多个窗口，以便观察，操作如下：21 打开一个工作簿窗口。22 单击菜单“窗口”“新建窗口”。23 单击菜单“窗口”“重排窗口”。24 在重排窗口对话框中选取“平铺”，按“确定”。25 这时已为该工作簿开设了两个窗口，两者的标题是不同的，结尾多了:1、:2。利用两个窗口，可同时观察一个工作簿，利用它们的各自水平、垂直滚动条观察需要的行或列的信息。26 工作簿与工作表的关系，就象活页夹与其

2、中的每张活页纸，一张工作表就是一张活页纸。在一般情况下，一个工作簿包含6张工作表，当然，这也是可以改变的，操作如下：27 单击菜单“工具”“选项”，出现“选项”对话框，如图2-2。28 选择“常规”卡，在“新工作簿内的工作表数”栏内填入具体的数目，如4。29 按“确定”。30 对工作表的常用操作有：31 1 1 工作表的增加：在工作表Sheet3之前插入一个新工作表。操作如下：32 用鼠标单击状态栏中的Sheet3，使其成为当前工作表。33 单击菜单“插入”，弹出插入菜单。34 在插入菜单中选取“工作表”，再“确定”，即有Sheet5插入在Sheet3之前。35 2工作表的删除：将工作表She

3、et5删除。36 用鼠标单击状态栏中的Sheet5，使其成为当前工作表。37 单击菜单“编辑”，选取“删除工作表”，这时系统弹出警告框，提醒“删除的工作表将无法恢复”。38 在回答“是”后，Sheet5被删掉，同时，它的右邻表Sheet3成为当前工作表。39 3工作表的命名：工作簿给每种工作表都以Sheet1、Sheet2、顺序命名，但为了能明确各工作表中所包含的内容，往往需要更名。将Sheet1改为ex1-1。40 用鼠标双击状态栏中的Sheet1，Sheet1标签变黑；41 用“Backspace”键删去Sheet1，再输入ex1-1，敲回车。42 4工作簿内工作表的移动和复制：单击状态栏

4、内某工作表名后，按住鼠标左键，这时状态栏中显示一个小三角形（标签）加上一个小白长方块；拖动鼠标时该小标签指示可以插入的工作表位置，它随鼠标的移动而间断跳动，当鼠标指针处于两个工作表的分界线时松开左键，该工作表就定位于这两个工作表之间。复制与移动不同的是，复制除了移动的操作外，只需按住“Ctrl”键，此时小白长方块上会出现一个小“”号。如复制Sheet1，完成后在工作簿中将有一个Sheet1（2）出现。43 5工作表在不同工作簿之间的移动和复制：如将“例题与练习.XLS”中工作表“图1-10”移动或复制到“Book1”中的“Sheet1”之前，操作如下：44 打开两个工作簿，用鼠标单击状态栏中的

5、“图1-10”。45 单击菜单“编辑”，选取“复制或移动工作表”，弹出对话框，见图2-5；46 47 图2-5：在不同工作簿中移动和复制工作表48 在“工作簿栏”中选取“Book1”，在“下列选定工作表之前”里点中“Sheet1”；49 如果是移动则不需要选择“建立副本”；如果是复制，则必须点定“建立副本”，如图2-5所示。50 按“确定”后完成移动或复制任务。51 6工作表的缩放：有时为了查看整个工作表的整体情况，可以通过“视图”“显示比例”菜单来调节屏幕的显示尺寸。如果需要将工作表放大一倍，则可选择“200%”项；如果需要将某一范围的内容显示在屏幕上，可以先选取该范围，再确定“恰好容纳选定

6、区域”。52 顺便一提 上述操作可以用更快捷的方式实现，用鼠标点中工作表名，再击右键，将弹出一个快捷菜单，上面有插入、删除、重命名、移动和复制等选项。53 单元引用地址54 在Excel的公式中引用单元格地址有相对单元引用地址、绝对单元引用地址和混合单元引用地址之分，它们主要影响对包含有公式的单元格的拷贝。在每一个所创建的公式中，要仔细考虑使用何种类型的单元地址，如能区分各种单元地址的功效，就可在拷贝公式时避免错误。55 日常生活中，经常会碰到相对、绝对引用地址的概念。假若你在学校，想让同学去邮局替你寄一封挂号信，你告诉他：“出大门后向南走一百米后再向左转就到邮局”，这是针对学校而言的，是相对

7、地址，如果你在别处，上述方向就不适用了。但不管你在何处，你说“邮局在中山北路301号”，这就是一个绝对地址，它是不会改变的。56 1相对引用地址57 Excel通常使用相对地址作为公式中的单元格引用，这就是说，当拷贝一个公式到新的位置，或用一个公式在一个区域内填充，公式中引用的单元格地址相对地发生变化，这也是我们常常所希望的。因为在很多时候，一个区域内的公式，只需要一个单元格内的公式写正确，再拷贝到其它单元格，希望公式引用的单元格地址自动地调整，这就需要相对单元引用。58 例1现有1990-1998年我国国内生产总值的资料，要求计算各年的环比发展速度。59 原始资料“年份”存放A列，“国内生产

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

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

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

11、用。72 4三维引用地址73 使用三维引用地址可以用来引用多个工作表上的同一单元格或区域，工作簿必须包含多张工作表。74 单击需要输入公式的单元格；键入“”和函数名称，接着再键入左圆括号。75 单击需要引用的第一个工作表标签。76 按住“SHIFT”键，单击需要引用的最后一个工作表标签。77 选定需要引用的单元格或单元格区域。78 键入右圆括号，敲回车键。79 顺便一提 循环引用，是指公式的内容引用到自己的数值，即公式本身的单元格。如在“F1”单元格输入公式“E1+F1*10”，Excel会显示不能求出循环引用的解，给出一个提示框，见图2-7，按“确定”按钮后，单元格值为0，同时在状态栏会显示

12、“循环：F1”。80 81 图2-7：循环引用的实例82 在需要反复运算求解循环引用的公式时，我们可以设定其反复运算的次数与最大误差，求得它的近似解。设定操作如下：83 选择菜单“工具”“选项”，在“选项”对话框中选“重新计算”卡，如图2-3。84 在“重新计算”卡上确认“反复操作”，在“最多迭代次数”和“最大误差”栏中填写合适的值，按“确定”按钮。85 例3公司预计分配给员工的红利必须等于纯利的12%，而纯利又视红利的多少而定。如公司的毛利为50000万元，杂支费用为12000，则员工的红利与公司的纯利各为多少？86 见图2-7的A1-C5区域。Excel求解循环公式，是使用前一此反复运算的

13、结果，来重新计算每一个涉及循环引用的单元格数值。87 在理论上，每一次运算后求出的数值，通常会更接近正确解，我们称之为收敛循环。如果其结果相反，我们则称之为发散循环。因此当我们得到的数值为一发散值，可试着将某一固定值的起始值做些修正，再重新计算。88 一、统计表89 （一）编制统计表90 一张含有重要数据的统计表往往会保存很长一段时间，因而，应该精心设计每张统计表结构。一张统计表的基本的内容有：需要表示的是什么数据、按什么顺序来表示，以及要对数据执行什么操作。在开始使用 Excel 之前，先花些时间来考虑一下数据的要求，然后再建立统计表。下面举例说明统计表编制的基本步骤。91 例3编制“宏大计

14、算机公司销售情况（上旬）”统计表，见图2-8。92 93 图2-8：统计表的编制实例94 编表步骤：95 1建立统计表：输入标题行、输入项目列、输入销售量和进销价格的具体数据，在E列利润栏，E3单元格输入公式“（D3-C3）*B3”，其经济含义是：利润等于销量乘以进销价差；该公式是相对公式，将复制到E4:E8，在E9单元格输入函数“SUM（E3:E8）”，以计算出总利润。96 2统计表的编辑：在完成对工作表的数据输入之后，可能会发现输入的某些数据有误或情况变化，需要对表中的数据进行修正。97 如果在输入数据过程中出了差错（在按回车键之前），可按“Backspace ”键删除掉最后的字符，也可以

15、在取消框（编辑区中的“”按钮）单击鼠标，还可以按“Esc”键，删掉单元格的内容，重新键入数据。如果发现前面数据有错误，则将光标移至错误字符处，再按Delete键即可。如果要连续删除几个相邻的字符串，则在当前单元格中或编辑栏的公式框中，用鼠标拖拽这几个字符串（这时被拖拽的字符变成反白显示），再按退格键或Delete即可全部删除。98 如果是确定后修改输入数据：删除一个单元格的数据，只要在窗口工作表中单击指定单元格，把指定单元格变为当前单元格，然后敲键盘上的“DEL”键，或把指定单元格变为当前单元格，再单击菜单“编辑”“清除”，则指定单元格的内容就被删除了，而指定单元格仍为当前单元格。更简单的方法

16、是：选定要删除的单元格，单击鼠标右键，出现一个菜单，选择“清除内容”，则指定单元格的内容被清除。这里的删除操作实际上只是清除了指定单元格的内容，而指定单元格空间仍保留。有时需要修改的单元格中内容复杂或数据太长，而要改动的只是其中的一部分，如对该单元格进行重新输入，则费时费力。简便方法是：单击指定的单元格，编辑栏中出现单元格中的内容，如果在编辑栏中想要进行修改的数据位置上双击，则该位置出现一个竖条光标，在编辑栏此位置就可进行相应的插入、删除、覆盖等修改操作了，也可以用键盘上的“、”键来左右移动光标寻找到需要修改的位置，修改完毕，单击编辑区中的“”按钮，系统即承认修改，并按修改的内容填入单元格，如

17、果单元格中是公式或函数，则重新计算并填入新的值。双击单元格是使该单元格直接进入编辑状态，可以添加数据，而如果单击单元格仅使该单元格进入活动状态，这时若输入数据，系统先清除原有数据，然后存入本次输入数据。99 3数据定位、查找与替换：有时统计表很大，窗口中无法显示全部数据，若需要修改的内容没在窗口中，则首先要寻找到指定的单元格，通过窗口滚动条来翻动统计表，把需要的部分调入窗口中，但这比较慢。若明确知道需要修改的单元格或内容，则可以用Excel 提供的方法快速地进行定位和替换。100 （1）定位：定位是一种直接用单元格的地址名或名字，把指定单元格快速变为当前单元并在窗口中显示的方法。101 单击菜

18、单上“编辑”“定位”，或按“F5”键，则弹出对话框，见图2-9。102 103 图2-9：“定位”对话框104 在定位框中有指定的单元格地址，如“P100”，按“确定”按钮或回车。105 则系统自动调到指定单元格所在的区域并在窗口中显示它，指定单元格成为当前单元格。106 （2）查找：查找是一种根据指定单元格中的数据内容，把指定单元格快速变为当前单位格并在窗口中显示的方法。107 单击菜单上“编辑”“查找”，或按“ShiftF5”键， 弹出对话框，见图2-10。108 109 图2-10：“查找”对话框110 定义查找的各种设置：111 单击搜索方式框右边的小按钮，弹出一个列表，有两项，选“按

19、行”，则按搜索行序的先后顺序进行；选“按列”，则按搜索列序的先后顺序进行。112 单击搜索内容框右边的小按钮，弹出一个列表，有项：选“公式”，则对数值、字符内容都可以查找，但对公式单元，只能查公式的形式，不能查公式的运算结果；选“值”，对数值、字符内容都可以查找，但对公式单元，只能查公式的运算结果，能够查公式的形式；选“附注”，查找代有附注的所有单元格。113 单击区分大小写框的“”，框中有“”，则查找字符串时字母区分大小写；框中无“”，查找时不区分大小写。114 单击单元格匹配框的“”，框中有“”，则查找字符串时是部分匹配查找，如查找内容为321，单元格B5中有321，单元格B10中有321

20、09，则查找时B5、B10 都能被查到。框中无“”，则查找字符串时是全部匹配查找，对上述情况，单元格B5被查到，而单元格B10则查不到。115 单击“查找下一个”按钮或回车，便开始查找。116 找到一个符合的条件单元格就停止，单击“关闭”按钮，返回符合条件的单元格，并把它作为当前单元格，或单击“查找下一个”按钮或回车，继续查找。117 （3）替换：替换是在查找的同时进行替换操作，对话框中替换条件的设置与查找条件的设置一样，只是在替换框中添如替换内容。例如，要把查找到的含有数字50的单元格的内容50改为100，如单击“全部替换”按钮，则把找到的所有单元格的50 都改为100；否则，找到一个含有数

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

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

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

24、相同型的合并规则。130 表2-1：完全相同型的合并规则 131 目标选取132 133 合 并 结 果134 一个单元格135 136 结果数据占用源数据区域同样形状、大小的区域137 一行单元格138 139 从所选行起向下填合并结果，最多有数据的行数为所选行数140 一列单元格141 142 从所选列起右填合并结果，最多有数据的列数为所选列数143 区域144 145 尽可能多地填入所选区域146 147 例4现有宏大计算机公司某月上、中、下旬销售情况的统计报表，要求汇总各旬情况，完成月度销售情况的报表。148 进行合并操作如下：149 选定一个目标区域，本例为“月度汇总表”的B3:B8

25、区域。请只选要显示合并数据的位置，注意文本、日期等数据是不能参与合并的。如果需要合并结果的行、列标题，先将它们从一个源工作表复制到目标工作表后，再选目标区域。150 选取“数据”“合并计算”，弹出“合并计算”对话框，见图2-11。151 152 图2-11：“合并计算”对话框153 在“函数”列表框内选取合并的计算方式，预选为求和，不改变时可以跳过。154 在“引用位置”文本框内，通过选取或直接键入方式确定源区域。如果源区域位于打开的工作表上，可以通过鼠标单击加拖动方式；如果源区域在未打开的工作簿文件内，应键入路径名、工作簿名、工作表名与区域（或区域名称），或者单击“浏览”按钮，打开“打开文件

26、”对话框，再选取文件名称。155 单击“增加”按钮，将“引用位置”文本框内的区域加入到“所有引用位置”列表框内。156 重复第四、五步，将所有源区域（可多达255个）增加到“所有引用位置”列表框内。157 注销“标记位置”复选钮组内的“顶端行”、“最左列”选择框，注销（也可选）“与源数据链接”选择框。如果选中“与源数据链接”选择框，结果在目标工作表内生成分级显示；最后单击“确定”按钮。158 特别注意 在做报表汇总时，第一，要留足适当大空间给目标区域，否则合并计算结果要覆盖原有数据，造成数据丢失。第二，只有数值数据参与合并计算，文本和公式不能进行合并计算。159 2不完全相同型的合并160 当

27、源工作表上数据的行列标题位置不完全相同时，要使用行、列标题进行合并计算。这种方法最为灵活方便，容许每个源区域的实际位置不同。161 例5某计算机总公司下属宏大公司1-3月（见图2-12的窗口1）和光大公司1-4月（见图2-12的窗口2）的销售量统计报表已上报，要求汇总出分品种、分月份的销售量。注意下属两公司的销售品种是不同的。见图2-12。162 163 图2-12：合并统计表的实例164 进行合并操作如下：165 选取目标区域，在“公司总销售”工作表上选取A1:E9。如果希望按照标题的指定次序，应选取事先准备好行列标题的区域，但标题要与源区域内标题拼写完全一致。若目标区域不包括标题，Exce

28、l会自动生成标题。166 选取“数据”“合并计算”，打开“合并计算”对话框。167 在“函数”列表内选取合并的计算方式，预设为“求和”，若不想改变可以跳过。168 在“引用位置”文本框内，通过鼠标选取或直接键入确定源区域。如果源区域位于打开的工作表上，可以通过鼠标单击加拖动方式，或直接按外部引用公式键入源区域引用；如果源区域在未打开的工作簿上，应键入路径名、工作簿名、工作表名与区域（或区域名称），或者单击“浏览”按钮，以弹出“打开文件”对话框，再选取文件名称。169 单击“增加”按钮，将“引用位置”文本框的区域加到“所有引用位置”框内。170 重复第四、五步，将所有源区域（最多多达255个）增加到“所有引用位置”框内。171 在“标记位置”复选钮组选中“顶端行”、“最左列”选择框，注销“与源数据链接”选择框；172 特别注意 在合并计算中：第一，要删除或编辑源数据区域，可在弹出的“合并计算”对话框内操作。要删去某个已选的源数据区域，可以先在“所有引用位置”列表框内选中它，再单击“删除”按钮；