EXCEL实训指导书

实验一、EXCEL基本操作

一、实验目的：

1．熟悉EXCEL基本操作环境

2．掌握Excel工作薄、工作表、单元格等基本概念。

3．掌握新建、保存、打开与关闭工作薄的方法。

4．掌握输入各种类型数据的方法。

5．掌握填充柄的使用与自动填充的方法。

二、实验内容及要求：

1、启动Excel

2、打开工作薄“实训一”，并回答下列问题：

1)、它由几个工作表构成，其名称分别是什么？

2)、“产品销售明细”表中的C9单元的内容是什么？

3、打开//关闭Excel界面中的“编辑栏”和“状态栏”

4、关闭以上工作薄

5、创建一个新的工作薄，并在工作表sheet1中输入下列工件表内容，保存工作薄并命名为“人事档案表.xls”。（保存位置自定）

三、实验步骤

1、通过单击任务栏的"开始"菜单中的"程序"项实现；

2、双出打开实训素材中的”实训一xls”工作薄文件；

很简单：看看左下角工作表标签就明白了；“产品销售明细”表中的C9单元的内容是“PIV1.4G”。

3、操作提示：通过“视图”菜单中的相应项目实现。

4、选择菜单【文件】—【关闭】

思考：菜单命令【文件/关闭】与【文件/退出】功能有何不同？

5、①选择菜单【文件】—【新建】，打开“新建工作薄”任务窗格

②选择“新建工作薄”任务窗格中的“空白工作薄”

③输入工作表数据内容

操作提示：

◆“序号”栏为非数值数字，输入时要在数字前加单引号；

◆为了提高输入效率，输入时请使用填充柄；

④选择菜单【文件】—【保存】，打开“另存为”对话框

⑤指定保存位置并输入工作薄名”人事档案表”，单击“保存”按钮

实训二、编辑工作表与工作表操作

一、实验目的：

1.掌握单元格的复制、移动、插入行、列或单元格与删除、清除单元格的

方法

2.掌握工作表的选择、重命名、插入、复制、移动与删除的方法

二、实验内容及要求：

一）、工作表操作：

打开工作薄“实训二”，并完成下列操作

1、重命名工作表

将“实训二”工作簿中的“工资表”、更名为“1月工资表”。

2、插入工作表

在“实训二”工作簿中的“1月工资表”的右侧插入两个工作表。

3、移动工作表

将“实训二”工作簿中的新插入的两个工作表移至“人事档案表”表的左侧。

4、删除工作表

删除“实训二”工作簿中的新插入的两个工作表。

5、复制工作表

利用“实训二”工作簿中的“1月工资表”工作表复制出两个工作表，要求排列在“1月工资表”的右侧，并命名为“2月工资表”和“3月工资表”。二）、编辑工作表

1、利用剪贴板进行复制或移动

利用剪贴板将"人事档案表"中“姓名”数据区域中的员工姓名复制到"1月工资表"、"2月工资表"和"3月工资表"中的相应区域。

2、插入行、列

1）、在"人事档案表"中的“出生日期”列的右侧插入1空白列，并在列标题输入“工龄”

2）、在"1月工资表"中的倒数第二行插入一空白行，并输入行标题----“合计”

3、清除与删除单元格

1）、在"数据输入练习"工作表中清除"等差序列1"的格式。

2）、在"数据输入练习"工作表中，删除多余的空行和空列。

三、实验步骤

一）、工作表操作：

双击打开工作薄“实训二”

1、重命名工作表

操作步骤：

①鼠标指向工作表标签中的工作表“工资表”，右击弹出快捷菜单

②选择【重命名】

③输入工作表新名“1月工资表”

2、插入工作表

操作步骤：

①选择插入工作表位置：

单击工作表标签中的工作表“1月工资表”右侧工作表，即“数据输入练习”工作表

②选择菜单【插入】—【工作表】命令，插入一个工作表

●使用“快捷菜单”法也可以实现，试试看！

④重复步骤①至③再插入一个工作表

3、移动工作表

操作步骤：

①在工作表标签中单击选择要移动的一个工作表

②选择菜单【编辑】—【移动或复制工作表】命令，打开对话框

③选择将移动到的位置，即“人事档案表”工作表，并按确定按钮

●使用“快捷菜单”法也可以实现，试试看！

④重复步骤①至③再移动一个工作表

思考：你能一次性移动2个工作表吗？（提示：如果要同时移动2个文件，你会怎么办？）

4、删除工作表

删除“实训二”工作簿中的新插入的两个工作表。

操作步骤：

①在工作表标签中同时选择要删除的2个工作表

操作提示：先单击选择1个工作表，再按住CTRL键同时选择第2个工作表②选择菜单【编辑】—【删除工作表】命令

●使用“快捷菜单”法也可以实现，试试看！

5、复制工作表

操作步骤：

①在工作表标签中单击选择“1月工资表”工作表

②选择菜单【编辑】—【移动或复制工作表】命令，打开对话框

③选择将复制到的位置，即“数据输入练习”工作表

④单击选定“建立副本”选项（打勾），并按确定按钮

⑤将复制过来的工作表“1月工资表(2)”重命名为“2月工资表”

●使用“快捷菜单”法也可以实现，试试看！

⑥重复步骤①至⑤再复制一个工作表

二）、编辑工作表

双击打开工作薄“实训二”

1、利用剪贴板进行复制或移动

操作步骤：

①选定"人事档案表"中的“姓名”数据区域—B2：B21

②选择菜单【编辑】—【复制】

③将单元光标定位到"1月工资表"中的B4单元，即第1个职员的姓名单元。

④选择菜单【编辑】—【粘贴】

●使用“快捷菜单”法也可以实现，试试看！

⑤重复重复步骤①至④再复制到另外两个工作表

思考：想想看你还有更好更快的方法吗？

2、插入行、列

1）、在"人事档案表"中的“出生日期”列的右侧插入列

操作步骤：

①将单元光标定位到"人事档案表"中的“出生日期”列的右列

②选择菜单【插入】—【列】

③在新插入列的标题单元输入“工龄”

2）、在"1月工资表"中的倒数第二行插入一空白行，，并输入行标题----“合计”

操作步骤：

①将单元光标定位到"1月工资表"中的倒数第二行，即“领导签名”的上一行位置

②选择菜单【插入】—【行】

③在新插入行输入标题文字“合计”

3、清除与删除单元格

操作步骤：

1）、在"数据输入练习"工作表中清除"等差序列1"的格式。

①单击行标签选定"数据输入练习"工作表中的"等差序列1"一行

②选择菜单【编辑】—【清除】—【格式】

2）、在"数据输入练习"工作表中，删除多余的空行和空列。

操作提示：选选择待删除的行或列，再使用菜单命令【编辑】—【删除】

实训三、格式化工作表

说明：……….

一、实验目的：

1.掌握设置字符格式的方法

2.掌握改变数字外观的方法

3.掌握改变列宽与行高的方法

4.掌握设置单元格的边框与底纹的方法

5.掌握使用条件格式的方法

6.掌握自动套用格式的方法

二、实验内容及要求：

打开工作薄“实训三”,完成下列操作：

一）、在“人事档案表”中完成下列操作

1、设置字符格式

1）、将标题“人事档案表”设置为，字体黑体；字号14；颜色红色，居中对齐

2）、将列标题设置为，字体仿宋体；字号12；颜色蓝色，居中对齐

2、为表格增加边框：

给“人事档案表”数据区域A2：I22添加边框。外边框为双线蓝色，

内边框为单线浅蓝色。其余部分不显示网格线。

3、为表格增加底纹

给“人事档案表”列标题添加底纹，颜色为浅黄；给“人事档案表”内容区域添加底纹，颜色为浅灰25%。

样张如下图示：

二）、在“工资表”中完成下列操作

1、改变数字外观

1）、将“工资表”数值区域（即区域：C4：O24）格式设置为：数值型、保留两位小数、使用千位分隔样式；

2）、将“职务工资”栏目格式设置为：使用货币样式

2、改变列宽与行高

1）、将“工资表”数据区域各行的行高设置为20

2）、将“工资表”数据区域各列的列宽设置为"最适合的列宽"

3、使用条件格式

将"职务工资"项目在1800以上的数据设置为红色、斜体；

4、自动套用格式

给“数据输入练习”工作表套用系统样式。

三、实验步骤

打开工作薄“实训三”

一）、在“人事档案表”中完成下列操作

1、设置字符格式

1）、将标题“人事档案表”设置为，字体黑体；字号14；颜色红色，居中对齐

操作步骤：

①选择“人事档案表”工作表标题行；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“字体”选项卡，设置字体为黑体，字号为14，颜色为红色

④选择“对齐”选项卡，设置齐方式为“居中”

⑤单击“确定”按钮

2）、将列标题设置为，字体仿宋体；字号12；颜色蓝色，居中对齐

也可以通过“格式”工具栏按钮进行相关设置。

操作步骤：

①选择“人事档案表”列标题区域（即：区域A2：I2）；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“字体”选项卡，设置字体为仿宋体，字号为12，颜色为蓝色

④选择“对齐”选项卡，设置齐方式为“居中”

⑤单击“确定”按钮

2、为表格增加边框：

给“人事档案表”数据区域A2：I22添加边框。外边框为双线蓝色，内边框为单线浅蓝色。其余部分不显示网格线。

操作步骤：

①选择“人事档案表”数据区域A2：I22；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“边框”选项卡，设置“线条样式”为双线，颜色为蓝色

④单击“外边框”按钮

⑤选择“边框”选项卡，设置“线条样式”为单线，颜色为浅蓝色

⑥单击“内边框”按钮

⑦单击“确定”按钮

3、为表格增加底纹

给“人事档案表”列标题添加底纹，颜色为浅黄；给“人事档案表”内容区域添加底纹，颜色为浅灰25%。

操作步骤：

①选择“人事档案表”列标题区域A2：I2；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“图案”选项卡，设置图案颜色为浅黄，并单击“确定”按钮

④选择“人事档案表”列标题区域A3：I22

⑤右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

⑥选择“图案”选项卡，设置图案颜色为浅灰25%，并单击“确定”按钮二）、在“工资表”中完成下列操作

1、改变数字外观

1）、将“工资表”数值区域（即区域：C4：O24）格式设置为：数值型、保留两位小数、使用千位分隔样式；

操作步骤：

①选择“工资表”数值区域：C4：O24；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“数字”选项卡，单击选择“分类”栏中的“数值”类型，设定小数点倍数为2，选定“使用千位分隔符”项（打勾）

④单击“确定”按钮

2）、将“职务工资”栏目格式设置为：使用货币样式

操作步骤：

①选择“工资表”中的“职务工资”栏目区域：C4：C24；

②右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】—【单元格】），打开单元格格式对话框

③选择“数字”选项卡，单击选择“分类”栏中的“货币”类型

④单击“确定”按钮

2、改变列宽与行高

1）、将“工资表”数据区域各行的行高设置为20

操作步骤：

①选择“工资表”中数据区域区域：第4至24行；

②右击弹出快捷菜单，选择“行高”（或选择菜单【格式】—【行】—【行高】），打开行高对话框

③设定行高值为20

④单击“确定”按钮

2）、将“工资表”数据区域各列的列宽设置为"最适合的列宽"

操作步骤：

①选择“工资表”中的第A至O行；

②选择菜单【格式】—【列】—【最合适的列宽】

3、使用条件格式

将"职务工资"项目在1800以上的数据设置为红色、斜体；

操作步骤：

①选择“工资表”中的"职务工资"项目区域：C4：C23；

②选择菜单【格式】—【条件格式】），打开条件格式对话框

③设置条件为“大于1800”，如下图示

④单击“格式”按钮，打开单元格格式对话框

⑤设置颜色为红色，字形为“倾斜”

⑥单击“确定”按钮

4、自动套用格式

给“数据输入练习”工作表套用系统样式。

操作步骤：

①选择“数据输入练习”中的数据区域：A1：K14；

②选择菜单【格式】—【自动套用格式】，打开自动套用格式对话框

③选择一种系统格式，例如：古典2格式

④单击“确定”按钮

实训四、公式与函数运算

一、实验目的：

1.掌握公式输入方法；

2.了解单元格引用的基本概念；

3.掌握在公式中使用函数的方法；

4.掌握常用函数（SUM、A VERAGE、MAX、MIN、IF、COUNT等）的

正确使用方法；

二、实验内容及要求：

打开工作薄“实训四”,在“成绩表”中完成下列操作：

1、设置“成绩表”项目计算公式

按照下列说明分别设置操作考核、期末成绩、总评成绩等项目的计算公式；

1)操作考核=EXCEL模块（50%）+PowerPoint模块（30%）+FrontPage模

块（20%）、

2)期末成绩=操作考核（70%）+理论考核（30%）

3)总评成绩=期末成绩（60%）+平时成绩（40%）

2、绝对地址引用

使用“成绩权重表”中的相应权重值，改写“成绩表”中的“期末成绩”和“总评成绩”项目公式

3、常用函数应用

使用求平均函数（average）设置“平均成绩”行（68行）的各项计算公式；思考：你能计算出各项成绩项目的最高、最低成绩吗？

4、编写“等级成绩”栏计算公式，计算方法如下：

85<=S<=100

等级成绩= 合格60=

S<60

其中：S指总评成绩

三、实验步骤

打开工作薄“实训四”

1、设置“成绩表”项目计算公式

1）、设置“操作考核”项目公式

操作步骤：

①将单元光标定位到“工资表”的G2单元（第一位同学的“操作考核”栏）；

②按“=”键

③直接输入公式“=D2*50%+E2*30%+F2*20%”后回车，完成第一位同学的“操

作考核”项目公式设置

●输入公式时，直接使用鼠标选择相应成绩项目单元输入其地址更快捷准

确。

④使用填充柄完成其他同学“操作考核”项目设置（将单元光标定位到“工

资表”的G2，并将鼠标指向该单元的右下角，当出现黑十字光标后拖动鼠标至G67单元）

2）、设置“期末成绩”项目公式：期末成绩=操作考核（70%）+理论考核（30%）操作方法与1）类似

思考：当“操作考核”与“理论考核”权重发生变化时，公式就要重新设置，是不是很麻烦，你有更好的办法吗？---参见下题目2

3）、设置“总评成绩”项目公式：总评成绩=期末成绩（60%）+平时成绩（40%）

操作方法与1）类似

2、绝对地址引用

使用绝对地址引用“成绩权重表”中的“期末成绩”与“总评成绩”各项权重值，改写“期末成绩”和“总评成绩”项目公式

1）、改写“期末成绩”项目公式

操作步骤：

①将单元光标定位到“成绩表”的I2单元（第一位同学的“期末成绩”栏）；

②按“=”键

③直接输入公式“=G2*成绩权重表!$C$2+ H2*成绩权重表!$C$3”后回车，

完成第一位同学的“期末成绩”项目公式设置

●输入公式时，直接使用鼠标选择输入更快捷，进行相对地址与绝对地址

转换时可使用F4键。

④使用填充柄完成其他同学“期末成绩”项目的设置（将单元光标定位到“成

绩表”的I2单元，并将鼠标指向该单元的右下角，当出现黑十字光标后拖动鼠标至I67单元）

2）、改写“总评成绩”工资项目公式

操作方法与1）相似（略）

思考：将“期末成绩”中的“操作考核”和“理论考核”权重调整为50%和50%，观察公式计算结果是否正确？上面的思考题你清楚了吗？

3、常用函数应用

使用求平均函数（average）设置“平均成绩”行（68行）的各项计算公式操作步骤：

①将单元光标定位到“成绩表”的C68单元；

②按“=”键

③直接输入公式“=AVERAGE(C2:C67)”后回车，完成“平时成绩”的平均值

项目公式设置（直接输入公式既麻烦又容易出错，建议通过下列方法使用鼠

标选择输入）

首先使用鼠标单击左上角函数栏右侧按钮，从下拉菜单中选择AVERAGE

函数,并在Number1栏设置求平均区域C2:C67（可直接输入，也可使用

鼠标选择输入），最后单击“确定”按钮。

④使用填充柄完成“EXCEL模块”等其他栏目平均值的设置

先将单元光标定位到“成绩表”的C68单元，再将鼠标指向该单元的右下角，当出现黑十字光标后直接拖动鼠标至J68单元。

思考：你能计算出各项成绩项目的最高、最低成绩吗？

提示：使用最大值函数max和最小值函数min。

4、编写“等级成绩”栏计算公式，计算方法如下：

操作步骤：

①将单元光标定位到“成绩表”的K2单元（第一位同学的“等级成绩”栏）；

②按“=”键

③直接输入公式“=IF(J2>=85,"优秀",IF(J2>=60,"合格","不合格"))”后

回车，完成第一位同学的“等级成绩”项目公式设置（直接输入公式既麻烦又容易出错，建议通过下列方法使用鼠标选择输入）。

●首先使用鼠标单击左上角函数栏右侧按钮，从下拉菜单中选择if函数,

再设置if函数条件值后单击“确定”按钮，函数条件设置如下图示：

因为当S（总评成绩）<85时，还不能确定具体的计算公式，这里先用？代替。

设置完成后，编辑栏显示公式如下：

●用鼠标选择编辑栏中公式“=IF(J2>=85,"优秀","？")”中的”?”，按Del

键将其删除。

●与前面方法类似，使用鼠标单击左上角函数栏右侧按钮，从下拉菜单

中选择if函数,再设置if函数条件值后单击“确定”按钮完成公式设置，函数条件设置如下图示：

④使用填充柄完成其他同学“等级成绩”项目设置

先将单元光标定位到“成绩表”的K2单元，并将鼠标指向该单元的右下角，当出现黑十字光标后直接拖动鼠标至K67单元（最后一名同学的“等级成绩”栏）

思考：如果将“等级成绩”栏计算公式改为如下，你能正确设置其计算公式吗？

优秀90<=S<=100

等级成绩= 良好75=

及格60=

S<60

其中：S指总评成绩

实训五、数据管理与分析

一、实验目的：

1.掌握利用记录单查找记录的方法

2.掌握数据排序的方法

3.掌握数据筛选的方法

4.掌握分类汇总的方法

5.掌握数据透视表的简单运用

二、实验内容及要求：

打开工作薄“实训五”,完成下列操作：

1、使用“数据/记录单”命令，查找“人事档案表”中姓黄的员工记录信息；

2、在"人事档案表"中按"部门"和"应发工资"两列数据排序。"部门"相同的情况下，按"应发工资"降序排列；

3、对"人事档案表"工作表进行筛选，显示部门为“信息技术系”或“中文系”员工中"应发工资"在2500-3000元的记录；

4、对"人事档案表"工作表按"部门"进行分类汇总，计算"应发工资"分类合计值；

5、利用“人事档案表”表建立数据透视表，分析各部门人员职称人数分布情况，结果如下图示：

三、实验步骤

打开工作薄“实训五”，选择“人事档案表”工作表

1、使用“数据/记录单”命令，查找“人事档案表”中姓黄的员工记录信息；

操作步骤：

①选择记录单区域A2：J22（或将单元光标定位在记录单区域内任何单元位

置）；

②选择菜单【数据】—【记录单】，打开记录单对话框；

③单击“条件”按钮；

④在姓名栏输入“黄”后回车；

⑤单击“下一条”、“上一条”按钮翻页浏览满足条件的记录信息

2、在"人事档案表"中按"部门"和"应发工资"两列数据排序。"部门"相同的情况下，按"应发工资"降序排列；

操作步骤：

①选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；

②选择菜单【数据】—【排序】，打开排序对话框；

③设置排序条件，如下图示；

④单击“确定”按钮；

3、对"人事档案表"工作表进行筛选，显示部门为“信息技术系”或“中文系”员工中"应发工资"在2500-3000元的记录；

分析：如果将显示部门、应发工资看成两个条件，条件间为同时满足关系，故可以通过自动筛选功能实现。

操作步骤：

①选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；

②选择菜单【数据】—【筛选】—【自动筛选】；

③单击“部门”右侧按钮，从下拉菜单中选择“自定义”项，打开对话框；

④设置自动筛选“部门”项条件，如下图示：

⑤单击“确定”按钮；

⑥重复步骤④-⑤，设置设置自动筛选“应发工资”项条件，如下图示：

⑦单击“确定”按钮

思考：如何取消自动筛选结果？

此题也可以通过高级筛选方法来实现，你能完成吗？

4、对"人事档案表"工作表按"部门"进行分类汇总，计算"应发工资"分类合计值；

操作步骤：

①首先，参照题1步骤，按分类字段---"部门"进行排序（分类汇总的先决条

件！）；

②选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；

③选择菜单【数据】—【分类汇总】，打开分类汇总对话框；

④设置“分类汇总”条件，如下图示：

⑤单击“确定”按钮；

思考：如何清除“分类”汇总结果？

5、利用“人事档案表”表建立数据透视表，分析各部门人员职称人数分布情况

操作步骤：

①选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；

②选择菜单【数据】—【数据透视表和数据透视图】，打开数据透视表向导；

③单击“下一步”，设置数据源区域，如下图示：

④单击“下一步”，再单击“布局”按钮，打开“布局”对话框，如下图示：

⑤使用鼠标拖动相应项目，设计数据透视表布局，如下图示：

⑥单击“确定”按钮；

⑦指定数据透视表显示位置；

⑧单击“完成”按钮；

思考：你能设计出下列格式的数据透视表吗？（与上题相似，想必你能行）

实训六、图表

一、实验目的：

1.掌握创建图表的方法

2.掌握图表的格式化的方法

二、实验内容及要求：

打开工作薄“实训六”，并完成下列操作

1、根据“职称结构分布表”中的"职称"和"中文系"汇总数据创建分离形三维饼图，如下图示：

2、对以上建立的图表中各图表项格式进行修改。样图如下：

三、实验步骤

打开工作薄“实训六”

1、根据“职称结构分布表” 中的"职称"和"中文系"汇总数据创建分离形三维饼图

操作步骤：

①选择数据源区域A1：A5和G1：G5

；（按住Ctrl 键分别选择） ②选择菜单【插入】—【图表】，打开图表向导； ③选择图表类型为“饼图”，子图表类型为“分离形三维饼图”： ④单击“下一步”，再单击“下一步”， ⑤输入图表标题为“中文系职称结构图”

⑥点击打开“数据标志”选项卡，选定“百分比”项和“类别名称”项（打勾）

⑦单击“下一步”，最后单击“完成”按钮，结果如下：

⑧将鼠标指向饼图紫色区域，单击左键后向左拖动调整至下图结果。

2、对以上建立的图表中各图表项格式进行修改。

操作提示：

①鼠标右击图表中的空白处,从弹出的快捷菜单中选择“设置三维视图格式”选项，弹出设置对话框如下：

②将上下仰角值从15改成40，单击“确定”按钮，结果如下：

③鼠标右击图表中的空白处弹出的快捷菜单，选择“图表区格式”选项，从弹出设置对话框中选择“图案”选项卡，显示如下：

④单击“填充效果”按钮，弹出设置对话框如下：

⑤指定“颜色”栏为“双色”，并设置“颜色1”为浅紫色，“颜色2”为浅黄色；将“底纹样式”设置为“水平”，最后单击“确定”按钮，结果如下：

⑥鼠标右击图表中的图表标题“中文系职称结构图”弹出的快捷菜单，选择“图表区格式”选项，参照上面步骤③至⑤方法，可将图表标题格式设置为题目要求的效果。

实训七、其他功能

一、实训目的

掌握工作表页面设置方法

掌握工作表保护方法

掌握工作表窗口拆分与冻结方法

二、实验内容及要求：