vgn

实训内容

在Excel 2010中打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T01_F01\\A6.XLSX，并按下列要求进行操作。

设置工作表及表格，结果如【参考样张】所示： 1.工作表的基本操作：

（1）将Sheet1工作表中的所有内容复制到Sheet2工作表中，并将Sheet2工作表重命名为“销售情况表”，将此工作表标签的颜色设置为标准色中的“橙色”。

（2）将标题行下方插入一空行，并设置行高为10；将“郑州”一行移至“商丘”一行的上方；删除第G列（空列）。 2.单元格格式的设置：

（1）在“销售情况表”工作表中，将单元格区域B2：G3合并后居中，字体设置为华文仿宋、20磅、加粗，并为标题行填充天蓝色（RGB：146,205,220）底纹。 （2）将单元格区域B4：G4的字体设置为华文行楷、14磅、“白色，背景1”，文本对齐方式为居中，为其填充红色（RGB：200,100,100）底纹。

（3）将单元格区域B5：G10的字体设置为华文细黑、12磅，文本对齐方式为居中，为其填充玫瑰红色（RGB：230,175,175）底纹；并将其外边框设置为粗实线，内部框线设置为虚线，颜色均为标准色下的“深红”色。 3.表格的插入设置：

（1）在“销售情况表”工作表中，为0（C7）单元格插入批注“该季度没有进入市场”。 （2）在“销售情况表”工作表中表格的下方建立“常用根式”公式，并为其应用“强烈效果-蓝色，强调颜色1”的形状样式。 4.工作表的打印设置：

（1）在“销售情况表”工作表第8行的上方插入分页符。

（2）设置表格的标题行为顶端打印标题，打印区域为单元格区域A1：G16，设置完成后进行打印预览。 参考样张

实训步骤

1. 工作表的基本操作

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A6文档，或单击A6文档，再单击“打开”按钮。 （1）工作表的复制及重命名

1）将鼠标放在sheet1工作表中任何一个单元格中，用【ctrl + A】选择全部内容，再用【ctrl + C】进行复制，进入sheet2工作表中单击A1单元格，用【ctrl + V】进行粘帖； 2）点击sheet2右击选择【重命名】，将sheet2改为“销售情况表”，同样右击在【工作表标签颜色】中选择标准色中的“橙色”。 （2）行列的操作

1）鼠标点击第三行，右击选择【插入】，标题行下方就出现一空行，选择该空行，右击选择【行高】，设置行高为10； 2）选择第7行，右击选择【剪切】，鼠标放在“商丘”所在行，右击选择【插入剪切的单元格】，这样“郑州”一行就移至“商丘”一行的上方；鼠标选择第G列，右击【删除】。

2. 单元格格式的设置

1）在“销售情况表”工作表中，鼠标选择单元格区域B2：G3，然后在【开始】功能区选择【合并后居中】，并设置字体为华文仿宋、20磅、加粗，为标题行填充天蓝色（RGB：146，205，220）底纹；

2）选择单元格区域B4：G4的字体设置为华文行楷、14磅、“白色，背景1”，文本对齐方式为居中，为其填充红色（RGB：200，100，100）底纹；

3）选择单元格区域B5：G10的字体设置为华文细黑、12磅、文本对齐方式为居中，为其填充玫瑰红色（RGB：230，175，175）底纹，并将其外边框设置为粗实线，内部框线设置为虚线，颜色均为标准色的“深红色”；结果如图4.1所示。

②字体字号设置 ③字体颜色、填充色设置 ①合并后居中 ④边框线的设置

图4.1 单元格格式设置

3. 表格的插入设置

1）在“销售情况表”工作表中，鼠标选择C7单元格，右击选择【插入批注】：“该季度没有进入市场”；

2）在“销售情况表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【公式】【二次公式】，然后修改成样张所示即可，如果要求的公式在【公式】菜单里没有，可以选择【对象】中的【Microsoft 公式3.0】建立所需公式；然后选择公式区域，进入【绘图工具格式】功能区，在【形状样式】中选择“强烈效果-蓝色，强调颜色1”的形状样式，如样张所示。 4. 工作表的打印设置

1）在“销售情况表”工作表中选择第8行，进入【页面布局】功能区，选择【分隔符】【插入分页符】； 2）在【打印标题】选项中设置格的标题行为顶端打印标题，打印区域为单元格区域A1：G16；

3）完成设置后点击“文件”中的“保存”按钮，保存工作薄。

4.1.2图表操作

实训内容

建立图表，结果如参考样张所示： （1）使用“销售情况表”工作表中的相关数据在Sheet3工作表中创建一个三维簇状柱形图。 （2）按参考样张所示为图表添加图表标题及坐标标题。

参考样张

实训步骤

1）鼠标放在sheet3工作表中任一单元格中，进入【插入】【图表】功能区，选择“三维簇状柱形图”，出现如图4.2所示空白区域；然后在【选择数据】中选择“销售情况表”中各地第一季度至第四季度的数据，【确定】后如图4.3所示；

图4.2 插入图表类型

图4.3 选择数据区域后结果图

2）进入【图表工具】【布局】，按参考样张设置添加图表标题及坐标标题即可；保存文档。

4.1.3数据管理与分析

实训内容

打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T01_F01\\A7.XLSX，按下列要求操作。 1.数据的查找与替换： 按【参考样张1A】所示，在Sheet1工作表中查找出所有数值“88”，并将其全部替换为“80”。 2.公式、函数的应用： 按【参考样张1A】所示，使用Sheet1工作表中的数据，应用函数公式统计出各班的“总分”，并计算“各科平均分”，结果分别填写在相应的单元格中。 3.基本数据分析：

（1）数据排序及条件格式的应用：按【参考样张1B】所示，使用Sheet2工作表中的数据，以“总分”为主要关键字，“数学”为次要关键字进行升序排序，并对相关数据使用“图标集”中的“四等级”的条件格式，实现数据的可视化效果。

（2）数据筛选：按【参考样张1C】所示，使用Sheet3工作表中的数据，筛选出各科分数均大于或等于80的记录。

（3）合并计算：按【参考样张1D】所示，使用Sheet4工作表中的数据，在“各班各科平均成绩表”的表格中进行求“平均值”的合并计算操作。

（4）分类汇总：按【参考样张1E】所示，使用Sheet5工作表中的数据，以“班级”为分类字段，对各科成绩进行“平均值”的分类汇总。 4.数据的透视分析：

按【参考样张1F】所示，使用“数据源”工作表中的数据，以“班级”为报表筛选项，以“日期”为行标签，以“迟到”为计数项，从Sheet6工作表的A1单元格起建立数据透视表。

参考样张

参考样张1A

参考样张1B

参考样张1C

参考样张1D

参考样张1E

参考样张1F

实训步骤

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A7文档，或单击A7文档，再单击“打开”按钮。；

1. 数据的查找与替换

在sheet1工作表中，进入【开始】功能区，选择【查找和选择】【替换】，弹出替换对话框，如图4.4所示进行设置，按【全部替换】即可；

图4.4 替换对话框

2. 公式、函数的应用

在sheet1工作表中将鼠标放在G3单元格，进入【公式】功能区，选择【自动求和】【求和】，选择C3：F3单元格区域，按回车键，得出总分，然后拖动填充柄，求出其余学生的总分；同样方式计算出“各科平均分”，结果如样张1A所示； 3. 基本数据分析

1）数据排序及条件格式的应用

在sheet2工作表中，选择A2：G14单元格区域，进入【数据】功能区，选择【排序】，出现排序对话框，进行如图4.5的设置，点击【确定】完成排序；然后进入【开始】功

能区的【条件格式】，选择 “图标集”中“四等级”的条件格式，实现数据的可视化效果，如样张1B所示；

图4.5 排序对话框设置

2）数据筛选

在sheet3工作表中，选择第2行标题，在【数据】功能区选择【筛选】，在各个标题出现下拉列表框，点击各科成绩选择【数字筛选】【大于或等于】，在对话框中进行如图4.6所示的设置，依次设置各科成绩，最后筛选出各科分数均大于或等于80的记录，结果如样张1C所示；

图4.6 自动筛选对话框设置

3）合并计算

在sheet4工作表中，将鼠标放在I3单元格，选择【合并计算】，弹出合并计算对话框，选择“函数”、“引用位置”，将“标签位置”选择在“最左列”，如图4.7所示，点击【确定】即可求出各班各科平均成绩，结果如样张1D所示；

图4.7 合并计算对话框设置

4）分类汇总

在sheet5工作表中，选择A2：F14单元格区域，以“班级”为主要关键字进行排序，然后，选择【分类汇总】，进行如图4.8所示的设置，即可完成分类汇总，结果如样张1E所示；

图4.8 分类汇总对话框设置

4. 数据的透视分析

将鼠标放在sheet6工作表中的A1单元格，进入【插入】功能区，选择【数据透视表】，出现“创建数据透视表”对话框，选定数据源，如图4.9所示，按【确定】，出现如图4.10界面，然后依次将“班级”拖动至【报表筛选】区域，将“日期”拖动至【行标签】区域，将“姓名”拖动至【列标签】区域，将“迟到”拖动至【数值】区域，并双击更改为【计数项】即可，数据透视表建立完成，结果如样张1F所示。

图4.9创建数据透视表对话框

图4.10数据透视表图

4.2 Excel 2010综合练习二 4.2.1 工作表的格式设置

实训内容

在Excel 2010中打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T02_F01\\A6.XLSX，并按下列要求进行操作。

设置工作表及表格，结果如【参考样张】所示： 1.工作表的基本操作：

（1）将Sheet1工作表中的所有内容复制到Sheet2工作表中，并将Sheet2工作表重命名为“收支统计表”，将此工作表标签的颜色设置为标准色中的“绿色”。

（2）在“有线电视”所在行的上方插入一行，并输入样文中所示的内容；将“餐费支出”行上方的一行（空行）删除；设置标题行的行高为“30”。 2.单元格格式的设置：

（1）在“收支统计表”工作表中，将单元格区域A1：E1合并后居中，设置字体为华文行楷、22磅、“标准色”中的“浅绿色”，并为其填充“标准色”中的“深蓝色”底纹。 （2）将单元格区域A2：E2的字体设置为华文楷体、14磅、加粗。 （3）将单元格区域A2：A9的底纹设置为“标准色”中的“橙色”。设置整个表格中的文本的对齐方式均为水平居中、垂直居中。

（4）将单元格区域A2：E9的外边框设置为“标准色”中“紫色”的粗虚线，内边框设置为“标准色”中“蓝色”的细虚线。 3.表格的插入设置：

（1）在“收支统计表”工作表中，为345（D7）单元格插入批注“本月出差”。

（2）在“收支统计表”工作表中表格的下方建立如样张中所示的公式，并为其应用“细微效果-红色，强调颜色2”的形状样式。 4.工作表的打印设置

（1）在“收支统计表”工作表第6行的上方插入分页符。

（2）设置表格的标题行为顶端打印标题，打印区域为单元格区域A1：E18，设置完成后进行打印预览。 参考样张

实训步骤

1. 工作表的基本操作

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A6文档，或单击A6文档，再单击“打开”按钮。 （1）工作表的复制及重命名

1）将鼠标放在sheet1工作表中任何一个单元格中，用【ctrl + A】选择全部内容，再用【ctrl + C】进行复制，进入sheet2工作表中单击A1单元格，用【ctrl + V】进行粘帖； 2）点击sheet2右击选择【重命名】，将sheet2改为“收支统计表”，同样右击在【工作表标签颜色】中选择标准色中的“绿色”。 （2）行列的操作

1）鼠标点击第5行，右击选择【插入】，标题行上方就出现一空行，在空行中输入样文中所示的内容；

2）选择第9行，右击选择【删除】，删除空行；选择标题行，右击选择【行高】，设置行高为30。

2. 单元格格式的设置

1）在“收支统计表”工作表中，鼠标选择单元格区域A1：E1，然后在【开始】功能区选择【合并后居中】，并设置字体为华文行楷、22磅、“标准色”中的“浅绿色”，并为其填充“标准色”中的“深蓝色”底纹；

2）选择单元格区域A2：E2，设置字体为华文楷体、14磅、加粗； 3）选择单元格区域A2：A9，设置底纹为“标准色”中的“橙色”；选择表格中的所有文本，将对齐方式设为水平居中、垂直居中；

4）选择单元格区域A2：E9，将其外边框设置为“标准色”中“紫色”的粗虚线，内边框设置为“标准色”中“蓝色”的细虚线； 3. 表格的插入设置

1）在“收支统计表”工作表中，鼠标选择D7单元格，右击选择【插入批注】：“本月出差”；

2）在“收支统计表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【对象】中的【Microsoft 公式3.0】建立所需公式；然后选择公式区域，进入【绘图工具格式】功能区，在【形状样式】中选择“细微效果-红色，强调颜色2”的形状样式，如样张所示。 4. 工作表的打印设置

1）在“收支统计表”工作表中选择第6行，进入【页面布局】功能区，选择【分隔符】【插入分页符】； 2）在【打印标题】选项中设置格的标题行为顶端打印标题，打印区域为单元格区域A1：E18；

3）完成设置后点击“文件”中的“保存”按钮，保存工作薄。

4.2.2图表操作

实训内容

建立图表，结果如参考样张所示：

（1）使用“收支统计表”工作表中的“项目”和“季度总和”两列数据在Sheet3工作表中

创建一个分离型圆环图。

（2）按参考样张所示为图表添加图表标题。

参考样张

实训步骤

1）鼠标放在sheet3工作表中任一单元格中，进入【插入】【图表】功能区，选择“分离型圆环图”，出现如图4.11所示空白区域；然后在【选择数据】中选择“收支统计表”中“项目”和“季度总和”两列数据，【确定】后如图4.12所示；

图4.11 插入图表类型

图4.12 选择数据区域后结果图

2）进入【图表工具】【布局】，按参考样张设置添加图表标题即可；保存文档。

4.2.3数据管理与分析

实训内容

打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T02_F01\\A7.XLSX，按下列要求操作。 1.数据的查找与替换： 按【参考样张2A】所示，在Sheet1工作表中查找出所有数值“100”，并将其全部替换为“150”。 2.公式、函数的应用：

按【参考样张2A】所示，使用Sheet1工作表中的数据，应用函数公式计算出“实发工资”，将结果填写在相应的单元格中。 3.基本数据分析：

（1）数据排序及条件格式的应用：按【参考样张2B】所示，使用Sheet2工作表中的数据，以“基本工资”为主要关键字，“津贴”为次要关键字进行降序排序，并对相关数据使用“图标集”中的“三色旗”的条件格式，实现数据的可视化效果。

（2）数据筛选：按【参考样张2C】所示，使用Sheet3工作表中的数据，筛选出部门为“工程部”，基本工资大于“1700”的记录。

（3）合并计算：按【参考样张2D】所示，使用Sheet4工作表中“一月份工程原料款（元）”和“二月份工程原料款（元）”表格中的数据，在“利达公司前两个月所付工程原料款（元）”的表格中进行求“求和”的合并计算操作。

（4）分类汇总：按【参考样张2E】所示，使用Sheet5工作表中的数据，以“部门”为分类字段，对“基本工资”和“实发工资”进行“平均值”的分类汇总。 4.数据的透视分析：

按【参考样张2F】所示，使用“数据源”工作表中的数据，以“项目工程”为报表筛选项，以“原料”为行标签，以“日期”为列标签，以“金额”为求和项，从Sheet6工作表的A1单元格起建立数据透视表。

参考样张

参考样张2A

参考样张2B

参考样张2C

参考样张2D

参考样张2E

参考样张2F

实训步骤

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A7文档，或单击A7文档，再单击“打开”按钮。；

1. 数据的查找与替换

在sheet1工作表中，进入【开始】功能区，选择【查找和选择】【替换】，弹出替换对话框，设置查找内容和替换内容，按【全部替换】即可；

2. 公式、函数的应用

在sheet1工作表中将鼠标放在G3单元格，进入【公式】功能区，选择【自动求和】【求和】，选择D3：F3单元格区域，按回车键，得出实发工资，然后拖动填充柄，求出其余的实发工资，结果如样张2A所示； 3. 基本数据分析

1）数据排序及条件格式的应用

在sheet2工作表中，选择A2：G14单元格区域，进入【数据】功能区，选择【排序】，出现排序对话框，选择“基本工资”为主要关键字，“津贴”为次要关键字，选择降序排序,点击【确定】完成排序；然后进入【开始】功能区的【条件格式】，选择 “图标

集”中“三色旗”的条件格式，实现数据的可视化效果，如样张2B所示； 2）数据筛选

在sheet3工作表中，选择第2行标题，在【数据】功能区选择【筛选】，在各个标题出现下拉列表框，点击部门选择“工程部”，点击基本工资选择【数字筛选】【大于】，在对话框中进行设置，最后筛选出基本工资大于1700的记录，结果如样张2C所示； 3）合并计算

在sheet4工作表中，将鼠标放在A20单元格，选择【合并计算】，弹出合并计算对话框，在“函数”中选择求和，在“引用位置”里分别选择“一月份工程原料款（元）”和“二月份工程原料款（元）”进行添加，将“标签位置”选择在“最左列”，如图4.13所示，点击【确定】即可求出“利达公司前两个月所付工程原料款（元）”，结果如样张2D所示；

图4.13 合并计算对话框设置

4）分类汇总

在sheet5工作表中，选择A2：G14单元格区域，以“部门”为主要关键字进行排序，然后，选择【分类汇总】，设置“部门”为分类字段，汇总方式为“平均值”，汇总项为“基本工资”和“实发工资”，点击【确定】即可完成分类汇总，结果如样张2E所示； 4. 数据的透视分析

将鼠标放在sheet6工作表中的A1单元格，进入【插入】功能区，选择【数据透视表】，出现“创建数据透视表”对话框，选定数据源，如图4.14所示，按【确定】，出现数据透视表的界面，然后依次将“项目工程”拖动至【报表筛选】区域，将“原料”拖动至【行标签】区域，将“日期”拖动至【列标签】区域，将“金额”拖动至【数值】区域，并双击更改为【求和项】即可，数据透视表建立完成，结果如样张2F所示。

图4.14创建数据透视表对话框

4.3 Excel 2010综合练习三 4.3.1 工作表的格式设置

实训内容

在Excel 2010中打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T03_F01\\A6.XLSX，并按下列要求进行操作。

设置工作表及表格，结果如【参考样张】所示： 1.工作表的基本操作：

（1）将Sheet1工作表中的所有内容复制到Sheet2工作表中，并将Sheet2工作表重命名为“客户订单查询表”，将此工作表标签的颜色设置为标准色中的“紫色”。

（2）将“10005”一行移至“10006”一行的上方，将“E”列（空列）删除；设置标题行的行高为“33”，整个表格的列宽均为10。 2.单元格格式的设置：

（1）在“客户订单查询表”工作表中，将单元格区域A1：E1合并后居中，设置字体为华文彩云、26磅、加粗、橙色（RGB：226,110,10），并为其填充图案样式6.25%灰色底纹。 （2）将单元格区域A2：E2的字体设置为华文细黑，居中对齐，并为其填充橙色（RGB：226,110,10）底纹。

（3）将单元格区域A3：E9的底纹设置浅橙色（RGB：250,190,140）。设置整个表格中的文本的对齐方式均为水平居中、垂直居中。

（4）设置单元格区域D3：E9中的数据小数位数只显示1位。

（5）将单元格区域A2：E9的外边框设置为深蓝色的双实线，内边框设置为浅蓝色的细实线。

3.表格的插入设置：

（1）在“客户订单查询表”工作表中，为0.0（E5）单元格插入批注“未付定金”。 （2）在“客户订单查询表”工作表中表格的下方建立如样张中所示的公式，并为其应用“彩色填充-橙色，强调颜色6”的形状样式。 4.工作表的打印设置

（1）在“客户订单查询表”工作表第6行的上方插入分页符。

（2）设置表格的标题行为顶端打印标题，设置完成后进行打印预览。

参考样张

实训步骤

1. 工作表的基本操作

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A6文档，或单击A6文档，再单击“打开”按钮。 （1）工作表的复制及重命名

1）将鼠标放在sheet1工作表中任何一个单元格中，用【ctrl + A】选择全部内容，再用【ctrl + C】进行复制，进入sheet2工作表中单击A1单元格，用【ctrl + V】进行粘帖； 2）点击sheet2右击选择【重命名】，将sheet2改为“客户订单查询表”，同样右击在【工作表标签颜色】中选择标准色中的“紫色”。 （2）行列的操作

1）鼠标点击“10005”行，右击选择【剪切】，然后选择“10006”行，右击选择【已剪切单元格】，将“10005”一行移至“10006”一行的上方； 2）选择第E列，右击选择【删除】，删除空列；选择标题行，右击选择【行高】，设置行高为33，选择表格所有列，右击选择【列宽】，设置列宽为10。 2. 单元格格式的设置

1）在“客户订单查询表”工作表中，鼠标选择单元格区域A1：E1，然后在【开始】功能区选择【合并后居中】，并在【开始】功能区设置字体为华文彩云、26磅、加粗、橙色（RGB：226,110,10），并为其填充图案样式6.25%灰色底纹；

2）选择单元格区域A2：E2，设置字体为华文细黑，文本对齐方式为居中对齐，并为其填充橙色（RGB：226,110,10）底纹；

3）选择单元格区域A3：E9，设置底纹为浅橙色（RGB：250,190,140）；选择表格中的所有文本，将对齐方式设为水平居中、垂直居中；

4）选择单元格区域D3：E9，右击选择【设置单元格格式】，在“数字”标签栏下，选择“数值”，设置小数位数为1位，如图4.15所示；

图4.15 设置单元格格式对话框

5）选择单元格区域A2：E9，右击选择【设置单元格格式】，在边框设置中将其外边框设置为深蓝色的双实线，内边框设置为浅蓝色的细实线； 3. 表格的插入设置

1）在“客户订单查询表”工作表中，鼠标选择E5单元格，右击选择【插入批注】：“未付定金”；

2）在“客户订单查询表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【对象】中的【Microsoft 公式3.0】建立所需公式；然后选择公式区域，进入【绘图工具格式】功能区，在【形状样式】中选择“彩色填充-橙色，强调颜色6”的形状样式，如样张所示。 4. 工作表的打印设置

1）在“客户订单查询表”工作表中选择第6行，进入【页面布局】功能区，选择【分隔符】【插入分页符】；

2）在【打印标题】选项中设置格的标题行为顶端打印标题； 3）完成设置后点击“文件”中的“保存”按钮，保存工作薄。

4.3.2图表操作

实训内容

建立图表，结果如参考样张所示：

（1）使用“客户订单查询表”工作表中的相关数据在Sheet3工作表中创建一个三维簇状条形图。

（2）按参考样张所示为图表添加图表标题及坐标标题。

参考样张

实训步骤

1）鼠标放在sheet3工作表中任一单元格中，进入【插入】【图表】功能区，选择“三维簇状条形图”，出现如图4.16所示空白区域；然后在【选择数据】中选择“客户订单查询表”中“姓名”、“订单金额”和“预付”三列数据，【确定】后如图4.17所示；

图4.16 插入图表类型

图4.17 选择数据区域后结果图

2）进入【图表工具】【布局】，按参考样张设置添加图表标题及坐标标题即可；保存文档。

4.3.3数据管理与分析

实训内容

打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T03_F01\\A7.XLSX，按下列要求操作。 1.数据的查找与替换： 按【参考样张3A】所示，在Sheet1工作表中查找出所有数值“J-06”，并将其全部替换为“G-06”。 2.公式、函数的应用：

按【参考样张3A】所示，使用Sheet1工作表中的数据，应用函数公式计算出“合格率”，将结果填写在相应的单元格中。 3.基本数据分析：

（1）数据排序及条件格式的应用：按【参考样张3B】所示，使用Sheet2工作表中的数据，以“总数（个）”为主要关键字，“产品型号”为次要关键字进行降序排序，并对相关数据使用“图标集”中的“3个三角形”的条件格式，实现数据的可视化效果。

（2）数据筛选：按【参考样张3C】所示，使用Sheet3工作表中的数据，筛选出“不合格产品（个）”小于“200”，“合格产品（个）”大于“5000”的记录。

（3）合并计算：按【参考样张3D】所示，使用Sheet4工作表中“上半年各车间产品合格情况表”和“下半年各车间产品合格情况表”表格中的数据，在“全年各车间产品合格情况统计表”的表格中进行求“求和”的合并计算操作。

（4）分类汇总：按【参考样张3E】所示，使用Sheet5工作表中的数据，以“产品型号”为分类字段，对其他各项分别进行“求和”的分类汇总。 4.数据的透视分析：

按【参考样张3F】所示，使用“数据源”工作表中的数据，以“产品规格”为报表筛选项，以“季度”为行标签，以“车间”为列标签，以“不合格产品（个）”、“合格产品（个）”、“总数（个）”为求和项，从Sheet6工作表的A1单元格起建立数据透视表。

参考样张

参考样张3A

参考样张3B

参考样张3C

参考样张3D

参考样张3E

参考样张3F

实训步骤

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A7文档，或单击A7文档，再单击“打开”按钮。；

1. 数据的查找与替换

在sheet1工作表中，进入【开始】功能区，选择【查找和选择】【替换】，弹出替换对话框，设置查找内容和替换内容，按【全部替换】即可；

2. 公式、函数的应用

在sheet1工作表中将鼠标放在F3单元格，输入“=D3/E3”，按回车键，得出合格率，然后拖动填充柄，求出其余的合格率，结果如样张3A所示；

3. 基本数据分析

1）数据排序及条件格式的应用

在sheet2工作表中，选择A2：E10单元格区域，进入【数据】功能区，选择【排序】，出现排序对话框，选择“总数（个）”为主要关键字，“产品型号”为次要关键字，选择降序排序，点击【确定】完成排序；然后进入【开始】功能区的【条件格式】，选择 “图标集”中“3个三角形”的条件格式，实现数据的可视化效果，如样张3B所示； 2）数据筛选

在sheet3工作表中，选择第2行标题，在【数据】功能区选择【筛选】，在各个标题出现下拉列表框，点击“不合格产品（个）”选择【数字筛选】【小于】，在对话框中进行设置，同样的点击“合格产品（个）”选择【数字筛选】【大于】，在对话框中进行设置，最后结果如样张3C所示； 3）合并计算

在sheet4工作表中，将鼠标放在C25单元格，选择【合并计算】，弹出合并计算对话框，在“函数”中选择求和，在“引用位置”里分别选择“C3：E10”和“C14：E21”的数据进行添加，在“标签位置”中不做任何选择，如图4.18所示，点击【确定】即可求出“全年各车间产品合格情况统计表”的数据，结果如样张3D所示；

图4.18 合并计算对话框设置

4）分类汇总

在sheet5工作表中，选择A2：E10单元格区域，以“产品型号”为主要关键字进行排序，然后，选择【分类汇总】，设置“产品型号”为分类字段，汇总方式为“求和”，汇总项为其它各项，点击【确定】即可完成分类汇总，结果如样张3E所示；

4. 数据的透视分析

将鼠标放在sheet6工作表中的A1单元格，进入【插入】功能区，选择【数据透视表】，出现“创建数据透视表”对话框，选定数据源，按【确定】，出现数据透视表的界面，然后依次将“产品规格”拖动至【报表筛选】区域，将“季度”拖动至【行标签】区域，将“车间”拖动至【列标签】区域，将“不合格产品（个）”、“合格产品（个）”、“总数（个）”拖动至【数值】区域，并双击更改为【求和项】即可，数据透视表建立完成，结果如样张3F所示。

4.4 Excel 2010综合练习四 4.4.1 工作表的格式设置

实训内容

在Excel 2010中打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T04_F01\\A6.XLSX，并按下列要求进行操作。

设置工作表及表格，结果如【参考样张】所示： 1.工作表的基本操作：

（1）将Sheet1工作表中的所有内容复制到Sheet2工作表中，并将Sheet2工作表重命名为“预算统计表”，将此工作表标签的颜色设置为标准色中的“黄色”。

（2）在“预算统计表”工作表中标题行的下方插入一空行，并设置行高为12；将“2008年”一列移至“2009年”一列的前方；设置整个表格的列宽为“10”。 2.单元格格式的设置： （1）在“预算统计表”工作表中，将单元格区域B2：F3合并后居中，设置字体为华文新魏、20磅、绿色，并为其填充“标准色”中的“黄色”、“细水平剖面线”的底纹。 （2）将单元格区域B4：B11的字体设置为方正姚体、14磅、深红色，并为其添加淡紫色（RGB：200,190,210）底纹。

（3）设置整个表格中的文本的对齐方式均为水平居中、垂直居中。

（4）将单元格区域B2：F3添加红色、双实线外边框，为其它单元格添加黑色、粗虚线边框线。

3.表格的插入设置：

（1）在“预算统计表”工作表中表格的下方建立如样张中所示的公式，并为其应用“浅色1轮廓，彩色填充-橄榄色，强调颜色3”的形状样式。

（2）在“预算统计表”工作表中表格的下方插入基本循环型的SmartArt图形，颜色为“彩色范围-强调文字颜色2至3”，并为其添加“优雅”的三维效果。 4.工作表的打印设置

（1）在“预算统计表”工作表第6行的下方插入分页符。

（2）设置表格的标题行为顶端打印标题，打印区域为单元格区域A1：H32，设置完成后进行打印预览。

参考样张

实训步骤

1. 工作表的基本操作

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A6文档，或单击A6文档，再单击“打开”按钮。 （1）工作表的复制及重命名

1）将鼠标放在sheet1工作表中任何一个单元格中，用【ctrl + A】选择全部内容，再用【ctrl + C】进行复制，进入sheet2工作表中单击A1单元格，用【ctrl + V】进行粘帖； 2）点击sheet2右击选择【重命名】，将sheet2改为“预算统计表”，同样右击在【工作表标签颜色】中选择标准色中的“黄色”。 （2）行列的操作

1）鼠标点击标题行下方一行，右击选择【插入】，标题行上方就出现一空行，右击空行选择【行高】，设置行高为12；；

2）鼠标点击“2008年”一列，右击选择【剪切】，然后选择“2009年”一列，右击选择【插入已剪切单元格】，将“2008年”一列移至“2009年”一列的前方，选择表格所有列，右击【列宽】，设置列宽为10。 2. 单元格格式的设置

1）在“预算统计表”工作表中，鼠标选择单元格区域B2：F3，然后在【开始】功能区选择【合并后居中】，并设置字体为华文新魏、20磅、绿色，并为其填充“标准色”中的“黄色”、“细水平剖面线”的底纹；

2）选择单元格区域B4：B11，设置字体为方正姚体、14磅、深红色，并为其添加淡紫色（RGB：200,190,210）底纹；

3）选择表格中的所有文本，将对齐方式设为水平居中、垂直居中；

4）选择单元格区域B2：F3，将其外边框设置为红色双实线；选择单元格B3：F10，将其边框线设置为黑色粗虚线。 3. 表格的插入设置

1）在“预算统计表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【对象】中的【Microsoft 公式3.0】建立所需公式；然后选择公式区域，进入【绘图工具格式】功能区，在【形状样式】中选择“浅色1轮廓，彩色填充-橄榄色，强调颜色3”的形状样式，如样张所示；

2）在“预算统计表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【SmartArt】，弹出SmartArt图形对话框，如图4.19所示，选择“循环”中的“基本循环”，点击【确定】后出现如图4.20所示的界面；然后选择【更改颜色】，如图4.21所示，选择“彩色范围-强调文字颜色2至3”；在【SmartArt样式】中选择三维效果中的“优雅”，如图4.22所示；最后点击图中的任何一个形状，右击选择【添加形状】【在后面添加形状】即可。

图4.19 SmartArt图形对话框

图4.20 SmartArt图形界面

图4.21 更改颜色选项

图4.22 SmartArt样式选项

4. 工作表的打印设置

1）在“预算统计表”工作表中选择第7行，进入【页面布局】功能区，选择【分隔符】【插入分页符】； 2）在【打印标题】选项中设置格的标题行为顶端打印标题，打印区域为单元格区域A1：H32；

3）完成设置后点击“文件”中的“保存”按钮，保存工作薄。

4.4.2图表操作

实训内容

建立图表，结果如参考样张所示：

（1）使用“预算统计表”工作表中的相关数据在Sheet3工作表中创建一个折线图。 （2）按参考样张所示为图表添加图表标题，并在底部显示图例。 参考样张

实训步骤

1）鼠标放在sheet3工作表中任一单元格中，进入【插入】【图表】功能区，选择“折线图”，出现如图4.23所示空白区域；然后在【选择数据】中选择“预算统计表”中所有数据，【确定】后如图4.24所示；

图4.23 插入图表类型

图4.24 选择数据区域后结果图

2）进入【图表工具】【布局】，按参考样张设置添加图表标题，并将图例显示设置在底部；保存文档。

4.4.3数据管理与分析

实训内容

打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T04_F01\\A7.XLSX，按下列要求操作。 1.数据的查找与替换： 按【参考样张4A】所示，在Sheet1工作表中查找出所有数值“12.5”，并将其全部替换为“16.5”。 2.公式、函数的应用：

按【参考样张4A】所示，使用Sheet1工作表中的数据，应用函数公式计算出“销售总额”，将结果填写在相应的单元格中。 3.基本数据分析：

（1）数据排序及条件格式的应用：按【参考样张4B】所示，使用Sheet2工作表中的数据，以“类别”为主要关键字，“单价”为次要关键字进行降序排序，并对相关数据使用“数据条”中的“绿色数据条”渐变填充的条件格式，实现数据的可视化效果。

（2）数据筛选：按【参考样张4C】所示，使用Sheet3工作表中的数据，筛选出部门为“销售数量” 大于5000或小于4000的记录。

（3）合并计算：按【参考样张4D】所示，使用Sheet4工作表中“文化书店图书销售情况表”、“西门书店图书销售情况表”和“中原书店图书销售情况表”表格中的数据，在“图书销售情况表”的表格中进行求“求和”的合并计算操作。

（4）分类汇总：按【参考样张4E】所示，使用Sheet5工作表中的数据，以“类别”为分类字段，对“销售数量”进行“平均值”的分类汇总。 4.数据的透视分析：

按【参考样张4F】所示，使用“数据源”工作表中的数据，以“书店名称”为报表筛选项，以“书籍名称”为行标签，以“类别”为列标签，以“销售数量”为求平均值项，从Sheet6工作表的A1单元格起建立数据透视表。 参考样张

参考样张4A

参考样张4B

参考样张4C

参考样张4D

参考样张4E

参考样张4F

实训步骤

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A7文档，或单击A7文档，再单击“打开”按钮。；

1. 数据的查找与替换

在sheet1工作表中，进入【开始】功能区，选择【查找和选择】【替换】，弹出替换对话框，设置查找内容和替换内容，按【全部替换】即可；

2. 公式、函数的应用

在sheet1工作表中将鼠标放在E3单元格，输入“=C3*D3”，按回车键，得出销售总额，然后拖动填充柄，求出其余的销售总额，结果如样张4A所示； 3. 基本数据分析

1）数据排序及条件格式的应用

在sheet2工作表中，选择A2：D12单元格区域，进入【数据】功能区，选择【排序】，出现排序对话框，选择“类别”为主要关键字，“单价”为次要关键字，选择降序排序,点击【确定】完成排序；然后进入【开始】功能区的【条件格式】，选择 “数据条”中的“绿色数据条”渐变填充的条件格式，实现数据的可视化效果，如样张4B所示； 2）数据筛选

在sheet3工作表中，选择第2行标题，在【数据】功能区选择【筛选】，在各个标题出现下拉列表框，点击“销售数量”，选择【数字筛选】【大于】或【小于】，在对话框中进行设置，最后筛选出结果，如样张4C所示； 3）合并计算

在sheet4工作表中，将鼠标放在A16单元格，选择【合并计算】，弹出合并计算对话框，在“函数”中选择求和，在“引用位置”里分别选择“A3：B12”、“D3：E10”和“G3：H10”的数据进行添加，将“标签位置”选择在“最左列”，点击【确定】即可求出“图书销售情况表”，结果如样张4D所示； 4）分类汇总

在sheet5工作表中，选择A2：D12单元格区域，以“类别”为主要关键字进行排序，然后，选择【分类汇总】，设置“类别”为分类字段，汇总方式为“平均值”，汇总项为“销售数量”，点击【确定】即可完成分类汇总，结果如样张4E所示； 4. 数据的透视分析

将鼠标放在sheet6工作表中的A1单元格，进入【插入】功能区，选择【数据透视表】，出现“创建数据透视表”对话框，选定数据源，按【确定】，出现数据透视表的界面，然后依次将“书店名称”拖动至【报表筛选】区域，将“书籍名”拖动至【行标签】区域，将“类别”拖动至【列标签】区域，将“销售数量”拖动至【数值】区域，并双击

更改为【求平均值项】即可，数据透视表建立完成，结果如样张4F所示。

4.5 Excel 2010综合练习五 4.5.1 工作表的格式设置

实训内容

在Excel 2010中打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T05_F01\\A6.XLSX，并按下列要求进行操作。

设置工作表及表格，结果如【参考样张】所示： 1.工作表的基本操作：

（1）将Sheet1工作表中的所有内容复制到Sheet2工作表中，并将Sheet2工作表重命名为“儿童发育调查表”，将此工作表标签的颜色设置为标准色中的“深红色”。

（2）在“儿童发育调查表”工作表中，将“2月年”一行移至“3月”一行的上方，将“G”列（空列）删除；将表格第一列（B列）的宽度设置为11，并自动调整整个表格的行高。 2.单元格格式的设置：

（1）在“儿童发育调查表”工作表中，将单元格区域B2：J2合并后居中，设置字体为华文行楷、24磅、天蓝色（RGB：180,220,230），并为其填充深紫色（RGB：85,65,105）底纹。 （2）分别将单元格区域B3：B5、C3：F3、G3：J3均设置为合并后居中格式。 （3）将单元格区域B3：J5的字体设置为方正姚体、14磅，并为其填充淡紫色（RGB：205,190,220）底纹。

（4）将单元格区域B6：J13的对齐方式设置为水平居中，字体为华文行楷、14磅、白色，并为其填充紫色的底纹。

（5）将单元格区域B2：J2的外边框设置为黑色的粗实线；将单元格区域B3：J13除上边框之外的外边框线设置为深蓝色的粗实线，内边框设置为黄色的细实线。 3.表格的插入设置：

（1）在“儿童发育调查表”工作表中，为60.00（E10）单元格插入批注“此处数据有误，请核实”。

（2）在“儿童发育调查表”工作表中表格的下方插入图片C:\\ATA_MSO\esting\\125254-3359\\excel\\T05_F01\\pic6-5.jpg，并为其应用“棱台矩形”的形状样式。

4.工作表的打印设置

（1）在“儿童发育调查表”工作表第10行的上方插入分页符。

（2）设置表格的标题行为顶端打印标题，设置完成后进行打印预览。

参考样张

实训步骤

1. 工作表的基本操作

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A6文档，或单击A6文档，再单击“打开”按钮。 （1）工作表的复制及重命名

1）将鼠标放在sheet1工作表中任何一个单元格中，用【ctrl + A】选择全部内容，再用【ctrl + C】进行复制，进入sheet2工作表中单击A1单元格，用【ctrl + V】进行粘帖； 2）点击sheet2右击选择【重命名】，将sheet2改为“儿童发育调查表”，同样右击在【工作表标签颜色】中选择标准色中的“深红色”。 （2）行列的操作

1）鼠标选择“2月”一行，右击选择【剪切】，选择“3月”行右击选择【插入已剪切单元格】，将“2月”一行移至“3月”一行的上方； 2）选择“G”列，右击选择【删除】，删除空列；选择B列，右击选择【列宽】，设置列宽为11，然后选择表格所有行，设置【自动调整行高】。 2. 单元格格式的设置

1）在“儿童发育调查表”工作表中，鼠标选择单元格区域B2：J2，然后在【开始】功能区选择【合并后居中】，并设置字体为华文行楷、24磅、天蓝色（RGB：180,220,230），

并为其填充深紫色（RGB：85,65,105）底纹；

2）选择单元格区域B3：B5，然后在【开始】功能区选择【合并后居中】；同样的设置单元格区域C3：F3、G3：J3；

3）选择单元格区域B3：J5，设置字体为方正姚体、14磅，并为其填充淡紫色（RGB：205,190,220）底纹；

4）选择单元格区域B6：J13，设置字体为华文行楷、14磅、白色，并为其填充紫色的底纹，设置文本的对齐方式为水平居中；

5）选择单元格区域B2：J2，将其外边框设置为黑色的粗实线；选择单元格区域B3：J13除上边框之外的外边框线设置为深蓝色的粗实线，内边框设置为黄色的细实线； 3. 表格的插入设置

1）在“儿童发育调查表”工作表中，鼠标选择E10单元格，右击选择【插入批注】：“此处数据有误，请核实”；

2）在“儿童发育调查表”工作表中将鼠标放在表格下方任一单元格中，进入【插入】功能区，选择【图片】，在选择对话框里找到图片C:\\ATA_MSO\esting\\125254-3359\\excel\\T05_F01\\pic6-5.jpg，然后在【图片样式】中选择“棱台矩形”的形状样式，如样张所示。 4. 工作表的打印设置

1）在“儿童发育调查表”工作表中选择第10行，进入【页面布局】功能区，选择【分隔符】【插入分页符】；

2）在【打印标题】选项中设置格的标题行为顶端打印标题； 3）完成设置后点击“文件”中的“保存”按钮，保存工作薄。

4.5.2图表操作

实训内容

建立图表，结果如参考样张所示： （1）使用“儿童发育调查表”工作表中的相关数据在Sheet3工作表中创建一个簇状圆柱图。 （2）按参考样张所示为图表添加图表标题，并在底部显示图例。 参考样张

实训步骤

1）鼠标放在sheet3工作表中任一单元格中，进入【插入】【图表】功能区，选择“簇状圆柱图”，出现如图4.25所示空白区域；然后在【选择数据】中选择“儿童发育调查表”中所有数据，【确定】后如图4.26所示；

图4.25 插入图表类型

图4.26 选择数据区域后结果图

2）按照样张选择【切换行/列】，然后进入【图表工具】【布局】，按参考样张设置添加图表标题，然后设置图例显示在图表底部；保存文档。

4.5.3数据管理与分析

实训内容

打开文件C:\\ATA_MSO\esting\\121954-1A17\\excel\\T05_F01\\A7.XLSX，按下列要求操作。 1.数据的查找与替换：

按【参考样张5A】所示，在Sheet1工作表中查找出所有数值“4863”，并将其全部替换为“4835”。

2.公式、函数的应用：

按【参考样张5A】所示，使用Sheet1工作表中的数据，应用函数公式计算出“总计”和“平均值”，将结果填写在相应的单元格中。 3.基本数据分析：

（1）数据排序及条件格式的应用：按【参考样张5B】所示，使用Sheet2工作表中的数据，以“搅拌机”为主要关键字进行升序排序，并对相关数据使用“蓝-白-红色阶”的条件格式，实现数据的可视化效果。

（2）数据筛选：按【参考样张5C】所示，使用Sheet3工作表中的数据，筛选为“塑料”大于等于“6000”或者小于等于“4000”的记录。

（3）合并计算：按【参考样张5D】所示，使用Sheet4工作表中“西南区建筑材料销售统计（万元）”和“西北区建筑材料销售统计（万元）”表格中的数据，在“建筑材料销售总计（万元）”的表格中进行求“求和”的合并计算操作。

（4）分类汇总：按【参考样张5E】所示，使用Sheet5工作表中的数据，以“产品名称”为分类字段，对“销售额”进行“平均值”的分类汇总，并设置所有数值均只显示一位小数。 4.数据的透视分析：

按【参考样张5F】所示，使用“数据源”工作表中的数据，以“销售地区”为行标签，以“产品名称”为列标签，以“销售额”为求和项，从Sheet6工作表的A1单元格起建立数据

透视表。

参考样张

参考样张5A

参考样张5B

参考样张5C

参考样张5D

参考样张5E

参考样张5F

实训步骤

启动EXCEL 2010，单击“文件”选项卡中的“打开”按钮，弹出“打开”对话框，在对话框中直接双击A7文档，或单击A7文档，再单击“打开”按钮。；

1. 数据的查找与替换

在sheet1工作表中，进入【开始】功能区，选择【查找和选择】【替换】，弹出替换对话框，设置查找内容和替换内容，按【全部替换】即可；

2. 公式、函数的应用

在sheet1工作表中将鼠标放在G3单元格，进入【公式】功能区，选择【自动求和】【求和】，选择B3：F3单元格区域，按回车键，得出总计，然后拖动填充柄，求出其余的总计；同样的操作求出平均值即可，结果如样张5A所示； 3. 基本数据分析

1）数据排序及条件格式的应用

在sheet2工作表中，选择A2：F8单元格区域，进入【数据】功能区，选择【排序】，出现排序对话框，选择“搅拌机”为主要关键字，选择升序排序，点击【确定】完成排序；然后进入【开始】功能区的【条件格式】，选择 “蓝-白-红色阶”的条件格式，实现数据的可视化效果，如样张5B所示； 2）数据筛选

在sheet3工作表中，选择第2行标题，在【数据】功能区选择【筛选】，在各个标题出现下拉列表框，点击“塑料”，选择【数字筛选】【大于等于】或【小于等于】，在对话框中进行设置，最后筛选出结果如样张5C所示； 3）合并计算

在sheet4工作表中，将鼠标放在A12单元格，选择【合并计算】，弹出合并计算对话框，在“函数”中选择求和，在“引用位置”里分别选择“A3：B7”和“D3：E7”表格数据进行添加，将“标签位置”选择在“最左列”，点击【确定】即可求出“建筑材料销

售总计（万元）”，结果如样张5D所示； 4）分类汇总

在sheet5工作表中，选择A2：C32单元格区域，以“产品名称”为主要关键字进行排序，然后，选择【分类汇总】，设置“产品名称”为分类字段，汇总方式为“平均值”，汇总项为“销售额”，点击【确定】即可完成分类汇总，然后选择所有数值区域，右击【设置单元格格式】，在“数字”标签栏下，选择“数值”，设置小数位数为1位，结果如样张5E所示； 4. 数据的透视分析

将鼠标放在sheet6工作表中的A1单元格，进入【插入】功能区，选择【数据透视表】，出现“创建数据透视表”对话框，选定数据源，如图4.14所示，按【确定】，出现数据透视表的界面，然后依次将“销售地区”拖动至【行标签】区域，将“产品名称”拖动至【列标签】区域，将“销售额”拖动至【数值】区域，并双击更改为【求和项】即可，数据透视表建立完成，结果如样张5F所示。