EXCEL 深度教程

 

       EXCEL(一) 创建EXCEL文档

一．启动与退出

1.      启动：

           方法一：通过桌面上或＂开始＂菜单－＞＂程序＂中的快捷方式启动

方法二：通过查找文件的方式打开：点击＂开始＂－＞＂查找＂－＞＂文件或文件夹＂，输入查找文件名为EXCEL.EXE1

2.         退出：方法一：点击程序主窗口上的＂关闭＂按钮

　　　　　方法二：Alt+F4

          方法三：点击＂文件＂菜单－＞＂退出＂

二．重要概念

1． 工作簿：EXCE处理和存储数据的文件，其扩展名为XLS。

2． 工作表：是工作簿的组成部分，每张工作表包含65536行，256列。

3． 活动工作表：当前正在使用的工作表，活动工作表的标签呈凹下去状态。

4． 数据清单：是包含相关数据的一系列数据行。例如一个成绩单或一个发货单

²        在数据清单中，第一行被称为标题行，数据清单中必须要有标题行。

5． 单元格：任意一行和任意一列的交叉位置，单元格是EXCEL编辑数据的最小单位。

6． 单元格名称：列行（其中列用字母表示，行用数字表示） 或 R行号C列号（行号和列号均为数字）例如：A1 或R1C1 都代表第一行第一列

三．单元格区域的选择

1．选择相邻的矩形区域

方法一：用鼠标拖动

方法二：点击矩形区域左上角的单元格，再按住SHIFT键，单击右下角单元格

2．选择不相邻区域

方法：先选择第一个区域，按住CTRL键不放，依次选择其它区域

3．选择整行或整列

方法：选择整行，点击行号，选择整列，点击列号。

4．选择多行或多列

方法：先选择第一行或第一列，按住CTRL键不放，再选择其它行或其它列。

5．选择整张工作表

用鼠标点击工作表编辑区的左上角（行号与列号的交叉处）

四．输入资料

1． 输入文本

1） 输入字母、汉字：选中单元格，直接输入即可。

2） 输入特殊符号：方法一：点击＂插入＂菜单－＞＂特殊符号＂

　　　方法二：右击输入法状态条上的键盘标记，选择一种符号类型，从中选择一个符号，单击键盘标记可以关      闭软键盘输入．

3） 输入数字型的文本，如编号，则需先输入单引号，再输入数字

4） 如果要在单元格输入回车符，按ALT+回车键

²        如果要实现自动换行，则点击＂格式＂菜单－＞＂单元格＂－＞＂对齐＂标签项－＞选中＂自动换行＂

２．输入数值

１）在半角状态下直接输入整数或小数即可．

２）输入分数：要先输入分数的整数部分，再加一个空格，再输入分子／分母（即使没有整数，如1/2也必须将整数输入为０，不能省略）

３．输入日期和时间

１） 输入日期时，用＂－＂分隔，例如：年－月－日　　

２） 输入时间时，用 时：分：秒格式，如果按12小时制输入，则应在输入的时间后，加一个空格，然后输入字母a（代表上午）或p（代表下午）

３） 若要输入当前日期，按CTRL + ; 若要输入当前时间，按CTRL + SHIFT + ;

4.数据的自动填充

１）数值序列的自动填充

　Ａ）等差序列

方法一：选中前两个单元格，拖动选中区域右下角的填充柄完成。

方法二：选中含有起始值的单元格，点击＂编辑＂菜单－＞＂填充＂－＞＂序列＂，在对话框中选择类型中的＂等差序列＂，序列生成的位置（行或列）及步长

Ｂ）等比序列

方法：选中起始单元格，点击＂编辑＂菜单－＞＂填充＂－＞＂序列＂，在对话框中选择类型中的＂等比序列＂，序列生成的位置（行或列）及步长

２）日期序列的自动填充

方法一：选中起始单元格，拖动填充柄

方法二：选中起始单元格，点击＂编辑＂菜单－＞＂填充＂－＞＂序列＂，在对话框中选择类型中的＂日期＂，序列生成的位置（行或列）及日期单位．

３）自定义序列的填充

方法：点击＂工具＂菜单－＞＂选项＂－＞自定义序列＂标签项，输入创建新序列（每项输入完毕要以回车结束）．在单元格中输入序列初始值，拖动填充柄．

４）同时在多个单元格中输入相同的数据

方法：先选定需要输入数据的单元格（相邻或不相邻），然后键入数据，最后按CTRL+回车键

５）在多张工作表输入相同的数据

　１）在多张工作表同时输入数据

方法：选定一组工作表（CTRL+单击标签），选定输入数据的单元格（区域），输入数据，然后按CTRL+回车键

　２）将已有的工作表中的数据添加到其它工作表中

方法：先选定包含数据的工作表及数据区域，再同时选定目标工作表，点击＂编辑＂菜单－＞＂填充＂－＞＂至同组工作表＂

五．插入行、列、单元格

方法：点击“插入”菜单—>＂行＂或＂列＂或＂单元格＂

  注意:在插入多行(列、单元格)时，应先选择多行（列、单元格）

六．清除或删除行、列、单元格

1、 清除：是指删除单元格中的内容（公式和数据）、格式或批注，清除后的单元格仍然保留在工作表中。

方法：选中单元格（区域），点击＂编辑＂菜单－＞＂清除＂

１）批注

a、 插入：点击“插入”菜单—>“批注”命令

b、 编辑:指向要修改批注的单元格右击,选择“编辑批注”

c、 删除:指向要删除批注的单元格右击,选择“删除批注”

２．删除：是指从工作表中移去这些单元格，并调整周围的单元格填补删除后的空缺。

方法：选择要删除的单元格、行、列，点击 ＂编辑＂菜单－＞＂删除＂

七．调整行高或列宽

方法：选中要调整的行或列，在行号（或列号）分隔线上拖动鼠标．

²    要调整多行或多列，可以一次选定多行或多列，拖动任意一行或一列的分隔线

²         当行高或列宽为０时，表示隐藏行或列

八．工作表的操作

１．插入工作表

　方法一：点击＂插入＂菜单－＞＂工作表＂

　方法二：用鼠标指向工作表标签，右击－＞＂插入＂，在＂插入＂对话框中选择＂工作表＂

２．删除工作表+

方法：右击要删除的工作表标签－＞＂删除＂

３．工作表的重命名

方法：右击要重命名的工作表标签－＞＂重命名＂，输入新的名称

４．工作表的移动与复制

移动：直接拖动要移动的工作表标签至目标位置．

复制：按住CTRL键，拖动工作表标签至目标位置

课后作业

1.      用EXCEL制作一份成绩表（至少含有5名学生），成绩表中含有“编号”、“姓名”、“语文”、“数学”、“英语”，“考试日期”六个字段

2.      在单元格区域B2:B10中利用自动填充输入：序列一：1，8，15，22，29，36，43，50

                                             序列二：2，4，8，16，32，64，128，256，512，1024

3.      在单元格区域A3:G3中，利用自动填充输入序列：“姓名，基本工资，书报费，水电费，奖金，实发工资，签名”。

4.      在1题中的数学和英语列之间插入一列“政治”，并将第二条记录行删除，将其所在的工作表重命名为“成绩表“

EXCEL(二)　格式化EXCEL文档

一．设置单元格（区域）的格式

方法：点击＂格式＂菜单－＞＂单元格＂，根据下面６种情况选择不同的标签项

1.    设置数字的显示形态

方法：点击＂格式＂对话框中的＂数字＂标签项

2.    单元格的对齐

方法：点击＂格式＂对话框中的＂对齐＂标签项

１）             设置水平对齐和垂直对齐

２）             设置文本旋转

３）             设置文本缩进

４）             将单元格中的文本字符缩小填充

3.    单元格字体的设置

  方法：点击 ＂格式＂对话框中的＂字体＂标签项

4.    单元格的边框

方法：点击＂格式＂对话框中的＂边框＂标签项

5.    单元格的背景色

方法：点击＂格式＂对话框中的＂图案＂标签项

6.    单元格的合并

方法：选中要合并的单元格，点击＂格式＂对话框中的＂对齐＂标签项，选中＂合并单元格＂或点击“格式＂工具栏中的＂合并及居中＂按钮

²      取消合并：选中单元格，右击－＞＂单元格格式＂－＞＂对齐＂标签项，清除＂合并单元格＂复选框

7.    单元格的命名

选中单元格（区域），点击＂插入＂菜单－＞＂名称＂－＞＂定义＂，输入名称

或　选中单元格（区域）后，点击窗口中的＂名称＂框，输入新的名称．

8.    设置条件格式：

方法：选中单元格（区域），点击＂格式＂菜单－＞＂条件格式＂，设置某一条件后，点击＂格式＂按钮设置

9.    自动套用格式：选中表格，点击“格式”菜单-＞＂自动套用格式＂

二．设置单元格中数据的有效性

1． 设置方法：点击＂数据＂菜单－＞＂有效性＂－＞＂设置＂点击对话框中的＂允许＂栏中设置限定．

2． 设置错误警告信息

设置方法：点击＂数据＂菜单－＞＂有效性＂－＞＂出错警告＂标签项，设置警告的样式和内容．

²        警告样式有三种

中止：强制性最强，该样式只允许输入符合要求的值

警告：强制性稍弱，允许输入错误的值且允许在输入错误后直接改正

信息：强制性最弱，允许输入错误的值，但不能直接更改．

1． 设置输入提示信息

点击＂数据＂菜单－＞＂有效性＂，点击＂对话框＂中的＂输入＂标签项，然后输入提示信息．

2． 审核工作表

点击＂工具＂菜单－＞＂审核＂－＞＂显示审核工具栏＂，在＂审核＂工具栏中，点击＂圈释无效数据＂或＂清除无效数据标识圈＂

3． 取消有效性设置

方法：选中要取消有效性设置的单元格，点击＂数据＂菜单－＞＂有效性＂－＞＂设置＂标签项－＞＂全部清除＂按钮．

课后作业

1.       若给下列单元格输入数字5，然后按照下表对格式输出数据，请分别设置。

5.00	￥5.00	5.00%	5	5.000E+00
5	五	000005	伍	5

2.       按下表格式设置边框

品名	品牌型号	价格	台数	金额
VCD	长虹LB	2310	1	2310
彩电	松下	1620	3	4860
VCD	三洋	1542	3	4626
录像机	三洋	2630	3	7890

3.       将B4：B10 区域的有效数据设置为0到100之间的小数，将C4：C10的有效数据设置为序列“男，女”，将D4：D10区域的有效数据设置为文本长度等于6。

4.       创建下表

		星期一	星期二	星期三	星期四	星期五
上 午	1
	2
	3
休息
下 午	4
	5

 

EXCEL(三)数据管理(一)

一．             运算符及公式

1） 运算符：

算术运算符：＋(加)、－(减)、＊(乘)、／(除)、＾(乘方)

关系运算符：> (大于)、<(小于)、 >=(大于或等于)、 <=(小于或等于)、 =(等于)、 <>或!=(不等于)

2） 公式：是由单元格内的一系列数据、单元格名称、函数和运算符构成的式子，可以通过现有数据计算出新的数值。

²        公式必须以等号开始。

3） 公式的录入：

方法一：选择单元格后，点击“编辑栏”中的“编辑公式”按钮，然后输入。

方法二：选择单元格后，先输入“＝”，然后输入公式的内容。

　4）公式的复制

方法一：选择要复制的公式所在的单元格，右击－＞＂复制＂，选择目标单元格，右击－＞＂粘贴＂

方法二：选择要复制的公式所在的单元格，拖动填充柄至其它单元格。

二．单元格的引用

　１）三种引用方式

　　　Ａ）相对引用：当公式被粘贴到新位置时，公式中的单元格会随新位置的变化而相对变化。（默认）

Ｂ）绝对引用：当公式被粘贴到新位置时，公式中的单元格不会随新位置的变化而相对变化。绝对引用时，要在单元格名称的行列前加＂$＂符号。

Ｃ）混合引用：当公式被粘贴到新位置时，公式中的单元格的行或列会随新位置的变化而相对变化。混合引用时，要在行号或列号之前加＂＄＂符号

　　２）单元格（区域）的表示

　　　Ａ）相邻的区域：左上角单元格名称：右下角单元格名称

　　　Ｂ）不相邻的区域：单元格区域１，单元格区域２，单元格区域３，．．．．．．

　　　Ｃ）两个单元格区域的公共区域：单元格区域１　单元格区域２

　　　Ｄ）用自定义名称来表示

三．函数

输入函数的方法：点击＂插入＂菜单－＞＂函数＂，或点击＂常用＂工具栏上的＂粘贴函数＂按钮，选择一个函数，设置各个参数．

１．常用函数

1）     求和函数：sum( )

2）     求平均值函数：average( )

3）     求最大值函数：max( )

4）     求最小值函数：min( )

5）     求乘积函数：product( )

6）      选择执行函数：if（）                             if(条件，条件成立时取值，条件不成立时取值)

   函数的嵌套：除IF函数外，其它函数的运算方向均为由内向外。   

2．日期函数

1)      返回当前日期及时间函数：now( )

2)      返回当前日期函数：today( )                       

3．文本函数：v

1)      文本连接函数：concatenate( )

2)      查找一个字符串在另一个字符串位置函数：find( )

3)      返回字符串左边若干字符函数：LEFT( )

4)      返回字符串右边若干字符函数：RIGHT( )

5)      从指定位置返回指定长度的字符函数： MID( )

6)      字符串替换函数：replace( )

四．数组的运算

1.    数组：将多个单元格区域用运算符连接起来进行整体运算．

　如：A2:A10+B2:B10

2.    使用方法：选中存放结果的单元格或单元格区域，输入数组公式，结束时按Ctrl+Shift+回车符.

²         注意：不能只点击＂确认＂按钮或直接按回车键．

²         数组公式所计算的结果受到保护，不能更改或删除．

存放数据的单元格区域要在方向和数量上与公式中单元格区域相同．

课后作业

1.     制作一个表格，含有如下字段：学号、语文、数学、英语、政治，（至少5人），利用函数计算出各名学生的：总成绩、平均成绩、单科成绩最高分、单科成绩最低分

2.    为上一表格添加一个“等级”字段，根据“总分”字段的值评比等级，评比方式如下：

   总分≥300为“优”，240≤总分<300为“良”，总分<240为“差”

3.    为上一表格添加一个字段“加分后的总分”，为每位同学在原总分的基础上的提升10%.

4.    根据下列x的值（变化横向步长为0.05，纵向步长 为0.2），计算相应的(1-X)/(1+X)值，结果保留三位小数。

X的值为：
0.05	0.1	0.15	0.2
0.25	0.3	0.35	0.4
0.45	0.5	0.55	0.6
0.65	0.7	0.75	0.8
0.85	0.9	0.95	1
(1-X)/(1+X)的值为：

 

EXCEL(四)数据管理(二)

一．图表的建立

1.      方法：点击＂常用＂工具栏上的＂图表向导＂按钮，根据向导完成．             

　系列产生在：“行”或“列”，是指以表格的第一行还是第一列作为分类依据。

2.      更改图表中的数据范围

方法：选中欲更改的图表，拖动数据表中对应区域的范围，扩大或缩小．

3.      更改图表的类型

方法：选中图表，点击＂图表＂菜单－＞＂图表类型＂，选择一种新的图表类型．

4.      设置图表

选中图表，点击“图表”菜单－＞＂图表选项＂

 

二。．排序

1.    方法：将光标移至数据清单的任意一个单元格中，点击＂数据＂菜单－＞＂排序＂，在排序对话框中选择排序的依据及排序方式．

²      ＂有标题行＂和＂无标题行＂是指是否要表格的第一行（标题行）参与排序，＂有标题行＂指第一行不参与排序．＂无标题行＂是指要第一行参与排序

²      ＂次要关键字＂只重排＂主要关键字＂重复的结果，＂第三关键字＂只重排＂次要关键字＂重复的结果．

²      点击＂选项＂按钮，可以设置按＂字母＂或按＂笔划＂排序．

2.    排序的种类

a)      按列排序

方法：点击＂数据＂－＞＂排序＂－＞＂选项＂按钮，选择＂按列排序＂（默认），选择排序依据及排序方式．

b)      按行排序

方法：点击＂数据＂－＞＂排序＂－＞＂选项＂按钮，选择＂按行排序＂，选择排序依据及排序方式．

注：按行排序时，没有标题行．

三．筛选

1.    自动筛选

方法：将光标移至数据清单的任意一个单元格内，点击＂数据＂菜单－＞＂筛选＂－＞＂自动筛选＂

²        使用＂自定义＂条件时，可以设置＂与＂＂或＂关系

²        各个关键字所设置的条件之间是并且的关系．

²        点击＂数据＂菜单－＞＂筛选＂－＞＂全部显示＂，可以取消所有设置．

2.    高级筛选

方法：点击＂数据＂菜单－＞＂筛选＂－＞＂高级筛选＂，设置数据源、条件区域、存放区。

数据源：进行数据筛选的数据区域。

条件区域：放置筛选条件的单元格。

注：条件区域要和数据源至少分隔三行，且条件区域中的关键字必须和数据源中关键字相同。

²        同一个关键字的＂与＂、＂或＂的设置

与：在条件区中输入该关键字两次，并在同一行输入两次该关键字的值．

或：在条件区中输入该关键字一次，并在不同行输入两次该关键字的值．

姓名

李四

张三

                                     

数学	数学
>=80	<=90

与:                                        或：

²        不同关键字＂与＂、＂或＂的设置

与：将所有关键字的值输入同一行．

或：将所有关键字的值分别输入在不同行上．

语文	数学
>80
	<90

与：　　　　　　　　　　　　　或：

数学	语文
>=80	<=90

四．分类汇总

1.    分类汇总：按照指定的列，将数据分类，对指定的列进行各种运算（求和、计数、平均值、最大值、最小值等）

2.    方法：将光标移至要汇总的数据清单中，单击＂数据＂菜单－＞＂分类汇总＂，选择＂分类字段＂、＂汇总方式＂和＂汇总项＂．

²        分类汇总之前，必须按分类字段对数据清单进行排序．

3.    分级显示

分类汇总后，数据的显示按照明细程度分为三层，分别有1、2、3表示，在每一类的标题前有“＋”或“－”号按钮。

单击1、2、3可以转换数据的明细。

单击“＋”：表示展开该项目中的明细数据。

单击“－”：表示收缩该项目中的明细数据。

4.    取消分类汇总

方法：点击＂数据＂菜单－＞＂分类汇总＂，点击＂全部删除＂按钮．

课后作业

1.    建立含有下列字段的表格（至少10人）：（姓名、性别、出生日期、学历、职称、基本工资、生活补贴，总收入），然后完成下列题

2.    按照“总收入”降序排列数据清单，若“总收入“值相同，则按照“姓名”笔划升序排序。

3.    使用自动筛选法，使工作表中显示出满足下列条件的记录

性别为“男”，学历为“大本”，“总收入”在2000元以上。

4.       对数据清单按照学历字段分类，统计各种学历工作人员的基本工资、生活补贴及总收入的平均值。

5.       筛选出上表中所有男工程师或学历为研究生的工作人员

 

EXCEL(五)工作簿及工作表的显示控制

一．隐藏工作表、工作表中的行与列

1．             隐藏工作表

方法：选中欲隐藏的工作表（点击相应标签），单击＂格式＂菜单－＞＂工作表＂－＞＂隐藏＂

取消隐藏：单击＂格式＂菜单－＞＂工作表＂－＞＂取消隐藏＂

2．             隐藏行与列

方法同上．

3．             保护工作表及工作簿

a)      保护工作表

方法：选中工作表，点击＂工具＂菜单－＞＂保护＂－＞＂保护工作表＂

保护范围：工作表中的行、列、单元格。

b)      保护工作簿

点击＂工具＂菜单－＞＂保护＂－＞＂保护工作簿＂

保护范围：工作簿中的工作表，但不保护工作表中行、列、单元格。

二．按比例显示工作表和全屏幕显示

1.      按不同的比例显示

点击＂常用＂工具栏上的＂显示比例＂下拉按钮，选择一个比较合适的比例．

2.      全屏幕显示

方法：点击＂视图＂菜单－＞＂全屏幕显示＂

三．工作表数据的组及分级显示

组：就是一个单元格区域，在分级显示时，可以全部折叠起来，也可以全部显示出来，组可以按行或按列创建．

1.      为单元格区域创建组

方法：选择要创建组的单元格区域，单击＂数据＂菜单－＞＂组及分级显示＂－＞＂组合＂，在对话框中指定是按行还是按列创建．

²         通过单击＂＋＂、＂－＂号，可以展开或收缩显示

²         ＂１＂、＂２＂、＂３＂．．．．．．数字按钮，表示显示的级别

2.      取消分级显示的组

方法：选中欲取消分组的单元格区域，单击＂数据＂菜单－＞＂组及分级显示＂－＞＂取消组＂，在取消对话框中选择按行或按列取消．

²         如果取消工作表中所有的组，选中整个工作表，单击＂数据＂菜单－＞＂组及分级显示＂－＞＂清除分级显示＂

四．拆分、冻结窗口

1.    拆分窗口

方法：在工作表中确定一个要拆分窗口的位置，单击＂窗口＂菜单－＞＂拆分＂

拆分位置如靠着行号，则分为上、下两个窗口

拆分位置如靠着列号，是拆分为左、右两个窗口

拆分位置如不靠着行号也不靠着列号，则拆分为上、下、左、右四个窗口

²         撤销拆分：点击＂窗口＂菜单－＞＂撤销拆分窗口＂

2.    冻结窗口

方法：在工作表中确定一个要冻结窗口的位置，单击＂窗口＂菜单－＞＂冻结窗格＂

冻结位置如靠着行号，则冻结上方窗口．

冻结位置如靠着列号，是冻结左方窗口．

冻结位置如不靠着行号也不靠着列号，则冻结左上方窗口

²         撤销冻结：点击＂窗口＂菜单－＞＂撤销窗口冻结＂