第1次上机课Excel基本操作.docx

第1次上机课Excel基本操作.docx

《第1次上机课Excel基本操作.docx》由会员分享，可在线阅读，更多相关《第1次上机课Excel基本操作.docx（23页珍藏版）》请在冰豆网上搜索。

第1次上机课Excel基本操作

实验Excel电子表格制作

【实验目的】

1.掌握Excel文件的建立、保存与打开。

2.掌握工作表的选择、添加、删除、重命名、复制与移动。

3.掌握单元格的输入、编辑、删除、修改、插入、复制与移动。

4.掌握工作表的修饰、公式与函数的应用（重点掌握）。

5.掌握文本的版面格式设计。

6.掌握工作表的高级应用

实验1.1工作表的基本操作

【实验内容与要求】

1.创建如图1.1内容的工作表文件。

图1.1工作簿文件内容

2.在A1单元格输入标题“部分城市降水量”，在A2单元格输入“城市”。

3.　在B2单元格输入“第一季”后，拖动填充柄填充C2~F2中的内容。

其余单元格按所给内容输入。

4.将A1~G1合并及居中，表格自动套用格式为“彩色2”，为数据清单加“粗外边框、细内边框”。

5.标题字体格式设置为：

楷体、14磅、加粗、浅青绿色。

6.工作表sheet1重命名为“降水量表”。

7.利用函数计算北京市的年总降水量和季平均降水量。

（均保留小数点后两位小数）；拖动填充柄填充F4~F7和G4~G7单元格中的内容。

8.按“年总量”列数值降序排序。

9.第二行表头区设文字水平居中，字体为粗体。

10.B3~E7数据设条件格式，数值≤65的设为红色、粗体。

拖动方向

图1.2填充柄

【实验步骤】

1.数据填充。

用鼠标指针指向填充柄，光标变为细十字，拖动鼠标即可，如图1.2所示。

2.合并及居中。

鼠标拖过A1~G1，然后按格式工具栏上的“合并及居中”按钮（如图1.3所示）；或者执行“格式/单元格”→[对齐]选项卡命令，按图1.4设置。

3.表格自动套用格式。

执行“格式/自动套用格式”命令，在图1.6对话框中单击“彩色2”，按[确定]按钮。

图1.3合并及居中操作

图1.4合并及居中设置

图1.5字体格式设置

4.标题格式设置。

在图1.5中[字体]选项卡中进行各种选择设置。

5.计算年总量。

单击F3单元格，执行“插入/函数”命令，在图1.7插入函数对话框中单击SUM求和函数，[确定]后，用鼠标拖选计算范围，如图1.7所示，回车或按编辑栏上绿色的√对号，完成F3单元格的计算。

向下拖动填充柄直至F7单元格。

图1.6表格“自动套用格式”

图1.7插入函数

注：

常用函数可以用“常用”工具栏上的Σ按钮及旁边的小箭头调出，也可以单击编辑栏左侧的fx按钮调出“插入函数”对话框。

图1.8求和函数的用法

6.计算季均量。

单击G3单元格，仿照年总量的计算方法，此次函数要选AVERAGE。

7.　工作表中数据区加表格线。

用鼠标选取A1~G7区域，执行“格式/单元格”命令调出如图1.10[边框]选项卡；在“线条样式”区单击粗线，在“预置”区单击“外边框”；在“线条样式”区单击细线，在“预置”区单击“内部”。

8.　排序。

单击数据区中任一单元格，执行“数据/排序”命令，按图1.11排序对话框设置，主要关键字选“年总量”，降序。

图1.9求平均值函数的用法

图1.10数据区加边框

图1.11排序对话框

图1.12最终完成结果图

实验1.2公式和函数的灵活运用

【实验内容与要求】

完成下表计算内容。

图1.13计算用表及完成结果

1.用函数计算成绩列的结果，四个科目的平均分数≥60为通过，否则为不通过。

2.用函数统计不及格的人数。

3.用函数计算及格率，及格率=及格的人数/总人数。

4.用百分比表示及格率，保留小数1位。

5.工作表标签重命名为“数据1”。

【实验步骤】

⒈建立图1.13成绩表，其中F3~F9、A10~E11区域空白，需计算得出。

图1.14IF函数的参数

⒉成绩列的计算。

根据所给条件：

如果“四个科目的平均分数≥60为通过，否则为不通过”，选择函数IF，计算公式为：

“=IF（AVERAGE（B3:

E3）<60,"未通过","通过"）”。

操作方法：

单击F3单元格，直接输入公式即可，或者采用如下步骤。

①单击F3单元格；，

②执行“插入/函数”命令，从图1.7“插入函数”对话框中选择IF函数；

IF函数有3项参数：

一是判断条件，二是条件为真时的取值，三是条件为假时的取值，

③依次按图所图1.14所示填写；

④在Logical_test一栏中，光标插入<60之前，再按编辑栏×号左侧的箭头，选择AVERAGE函数，如图1.15；

图1.15IF函数嵌套AVERAGE函数的操作方法

⑤AVERAGE函数参数选择可以输入或用鼠标拖选，如图1.16，按[确定]；

图1.16AVERAGE函数的参数

⑥拖动F3单元格的填充柄向下至F9，成绩计算完毕。

⒊统计不及格的人数。

此为条件统计，需要使用统计类函数中的COUNTIF函数。

计算公式为“=COUNTIF（B3:

B9,"<60"）”。

操作方法：

①单击B10单元格；，

②执行“插入/函数”命令，从图1.7“插入函数”对话框中选择COUNTIF函数；

③按下图填写参数，按[确定]按钮。

图1.17COUNTIF函数的参数

⒋计算及格率。

及格率=及格的人数/总人数，需要使用函数COUNTIF统计及格总人数和函数COUNT统计总人数。

计算公式为“=COUNTIF（B3:

B9,">=60"）/COUNT（B3:

B9）”。

操作方法：

①单击B11单元格；

②执行“插入/函数”命令，从图1.7“插入函数”对话框中选择COUNTIF函数；

③仿照图1.17示例，第二行参数改为“>=60”，按[确定]按钮完成公式第一部分；

④在编辑栏公式后输入“/”号，再从左侧函数列表中选COUNT函数；

图1.18选择COUNT函数

⑤填写参数如下图，或用鼠标拖选要计算的区域，按[确定]完成整个计算。

图1.19COUNT函数的参数

⑥百分比样式。

执行“格式/单元格”命令，在[数字]选项卡中设置。

也可以按格式工具栏上的“%”百分比样式按钮和增加|减少小数位数按钮进行处理。

图1.20设定百分比格式

⑦拖动B11单元格的填充柄向右至E11，及格率计算完毕。

⒌工作表改名。

执行“格式/工作表/重命名”，键入新的名称即可。

更简单的方法是双击工作表标签，直接更名。

最终完成效果如图1.13。

实验1.3绝对引用和混合引用

【实验内容与要求】

完成下表的计算，学习绝对引用的概念和方法。

前面已经熟悉了对单元格地址的相对引用方式，可以非常方便地完成大多数自动填充的计算任务，不过有时需要从固定的位置取数据，就需要用到绝对引用。

任务：

利用公式计算出各类项目的百分比，百分比=差额/各项目总差额。

（格式为百分比样式，保留小数点后两位小数）。

图1.21计算用表

【实验步骤】

第一种方法：

①　单击E10单元格，用SUM函数计算总差额；

②　单击F10单元，输入“=”，单击E3，输入“/”，单击E10，若此时结束输入，

图1.22输入公式

图1.23向下拖动填充柄出现相对引用错误（除数为零）

F3单元格可以得到正确结果，但用填充柄向下拖动则回出现“#DIV/0!

”错误，这是因为填充公式引用了E10以下单元格的地址，产生了被0除的错误。

我们希望填充柄向下拖动时对E10单元格的引用能够固定，解决的方法是将公式改写为“=E3/$E$10”，$E$10这种写法为绝对应用。

在公式中相对引用的单元格地址随填充柄的拖动而改变，而绝对引用的地址则固定不变，从而得到正确的结果。

图1.24绝对引用获得正确结果

第二种方法：

①　单击F10单元格，输入“=”；

②　单击E3，在编辑栏公式后输入“/”号，再从左侧函数列表中选SUM函数；

图1.25调用SUM函数

③　按[确定]按钮后，把编辑栏内公式改为“=E3/SUM（$E$3:

$E$9）”；

图1.26SUM函数参数采用绝对引用

④单击格式工具栏上的“%”百分比样式按钮，数值变成百分比样式。

⑤向下拖动填充柄直至F9单元格，完成计算。

注意，因为向下拖动填充柄时列不发生变化，所以SUM的计算区域也可以写成SUM（E$3:

E$9）这种混合引用的模式。

图1.27SUM函数参数采用混合引用

实验1.4图表制作

【实验内容与要求】

按图1.28表格内容制作成绩分布图和平均成绩分布图，图表样式为簇状柱型图。

图1.28成绩分布图

【实验步骤】

1．成绩分布图。

选取数据区域B2:

E9，执行“插入/图表”命令，按图表向导指引逐步完成图表的制作。

完成效果见图1.28成绩分布图。

2．平均成绩分布图制作方法：

①用鼠标选取B2:

E2区域，按住Ctrl再鼠标选取B10:

E10区域；

图1.29选取数据区域

②执行“插入/图表”命令，如图1.30选取簇状柱型图，按[下一步]按钮；

③选择系列产生在“行”，如图1.31，按[下一步]按钮；

④如图1.32填写标题信息，按[下一步]按钮；

⑤图1.33数据标志显示为“值”，按[下一步]按钮；

⑥在“图表向导-4步骤之4–图表位置”对话框中选“作为其中的对象插入”；

⑦按[完成按钮]，得到图1.34平均成绩分布图。

用鼠标拖拽坐标轴文字“科目”和“分数”到相应位置；

⑧Y轴坐标文字“分数”需要顺时针旋转90度，在文字“分数”上右击鼠标，在菜单中选“图表标题格式”，出现对话框，选“对齐选项卡”，调整“方向”为0度。

图1.30簇状柱型图

图1.31系列产生在行

图1.32填写标题信息

图1.33数据标志显示为“值”

图1.34平均成绩分布图

实验1.5数据的自动筛选和高级筛选

【实验内容与要求】

图1.35自动筛选

1．按图1.37所给样表，用自动筛选方法查找出计算机优秀和不及格的学生的记录。

2．用高级筛选方法查找出计算机优秀而物理不及格的记录。

【实验步骤】

1．自动筛选。

①执行“数据/筛选/自动筛选”，在列标题旁出现小箭头，单击“计算机”字段旁的小箭头，在出现的下拉列表中选“自定义”；

②按图1.36填写筛选条件，按[确定]按钮，即可获得结果。

图1.36填写筛选条件

2．高级筛选

图1.37学生成绩原始数据表

①先在工作表空白处建立筛选条件区，如图1.38，条件为“与”的关系；

图1.38高级筛选的筛选条件区，条件“与”

图1.39确定高级筛选的3个区域

②光标放到数据清单区中任一格；

③执行“数据/筛选/高级筛选”，如图1.39选择“条件区域”和“复制到”的位置；（注：

因为事先并不清楚筛选出来的记录有多少，所以复制到的位置并不需要用鼠标精确框选，只需要确定放置位置的左上角单元格就可以了。

）

④按[确定]按钮完成。

高级筛选可以完成比较复杂的或用自动筛选无法完成的筛选任务。

图1.40高级筛选完成结果（计算机优秀而物理不及格）

实验1.6分类汇总

【实验内容与要求】

根据图1.37实验数据样表，分别求出各班学生各个科目的平均成绩。

本实验需要采用分类汇总的方法完成。

分类汇总前需要先按分类字段排序，然后才能进行数据的处理。

操作步骤如下：

【实验步骤】

①光标放置于欲排序列中任一单元格，执行“数据/排序”，选择排序字段；

图1.41分类汇总前先按分类字段排序

②排序后，再执行“数据/分类汇总”，在对话框中选择分类字段、汇总方式和汇总项，按[确定]按钮。

图1.42分类汇总的各个选项

③最终得到结果如图1.43。

分类汇总表左上角按钮123可以折叠分类的明细项。

④如果想取消分类汇总数据，可以在图1.42的分类汇总对话框中按[全部删除]按钮，即可使数据还原。

图1.43分类汇总结果

*实验1.7数据透视表的制作

【实验内容与要求】

根据图1.37学生成绩原始数据表的内容，求各班英语、高数、物理和化学各科目的平均成绩，制作出如图1.44和图1.45所示的数据透视表。

【实验步骤】

1．数据透视表一的制作。

①选取要制作数据透视表的数据区域；

②执行“数据/数据透视表和数据透视图”；

图1.44数据透视表一

图1.45数据透视表二

③弹出如下数据透视表制作向导--3步骤之1，按[下一步]按钮；

图1.46数据透视表向导--3步骤之1

④弹出如下数据透视表制作向导--3步骤之2，选定数据区，按[下一步]按钮；

图1.47数据透视表向导--3步骤之2

⑤弹出如下数据透视表制作向导--3步骤之3，按[布局]按钮；

图1.48数据透视表向导--3步骤之3

⑥如下图，把各个字段按钮拖放到图中相应位置；

图1.48数据透视表—布局

⑦双击上图中[求和项：

英语]按钮，弹出如下对话框，在“汇总方式”栏中选择“平均值”，按[数字]按钮可以设置数字格式，但也可以后再做调整；

图1.49数据透视表字段—汇总方式

⑧其它求和项均依此办理，按[确定]按钮后，即得到如下数据透视表。

图1.50完成的数据透视表—

2．数据透视表二的制作。

①在上一个数据透视表的基础上，右击鼠标，在菜单中选“显示数据透视表工具栏”，调出该工具栏，按“设置报告格式”按钮；

图1.51数据透视表工具栏

②在如下“自动套用格式”对话框中选“报表1”，按[确定]按钮。

图1.52数据透视表自动套用格式

③对完成后的数据透视表还可以进一步修饰，如调整小数位数、修改单元格格式、修改字段名称、添加标题等。

④稍加修饰调整后，最终完成的效果如图1.53。

图1.53数据透视表二完成效果图

【说明】

数据透视表的功能十分强大，可以灵活方便地进行数据的各种分析。

初学时不太容易掌握，读者需要多多进行练习。

能否实现想要表达的结果，数据分析表的布局是关键。

下面利用本题提供的数据，举几个数据分析的实例，读者可以试做完成。

图1.54数据透视表示例1

图1.55数据透视表示例2

图1.56数据透视表示例3

图1.57数据透视表示例4