Excel 2010笔记

第一章 Excel 2010

一、简述Excel

Excel 2010，是目前流行的电子表格软件，它具有强大的数据处理功能。主要

应用于财务、行政、银行、审计及统计领域。用于制作财务报表和进行数据分析，并且

能以多种形式的图表方式来表现数据，还能够对数据表格进行排序、筛选和分类汇总（函

数）等操作。

二、启动和退出

1、启动：开始/所有程序/microsoft excel 2010

双击Excel 2010 快捷方式图标

2、退出：文件/退出 或Alt+F4

文件/关闭 或Ctrl+F4

三、窗口组成：

标题栏、菜单栏、快速访问工具栏、名称框、编辑栏、工作表、状态栏

名称框：显示当前活动单元格的名称（如：A5：列标+行号）

编辑栏：显示当前活动单元格的内容。

四、名词解释

1、工作簿：一个工作簿就是一个Excel文件（Book1——工作簿1；Book2--工作簿

2……），扩展名为 .xlsx 模板文件：.xltx)

2、工作表：又称电子表格，是Excel组织和管理数据的地方，用户可以在工作表上

输入数据，编辑数据、排序数据、筛选数据等操作。

注：

（1）默认情况下，一个工作簿包含3张工作表：分别是：Sheet1、Sheet2、Sheet3，

可以根据情况增加或删除

（2）一张工作表由16384（2^14）个列，1048576（2^20）行组成。

列标：用字母表示，从A列开始，到XFD列结束。A-Z AA-AZ BA-BZ CA-XFD

行号：用数字表示，从第1行开始，到1048576行结束。1-1048576

3、单元格：行与列交叉处形成的一个单元格。

（1）名称框表示单元格的地址：

列标在前、行号在后

如：A5表示第1列第5个单元格：

（2）单元格区域：是指该区域左上角和右下角的单元格，中间用：隔开。

如：A1：D8

（3）活动单元格：选定的单元格，四周粗线黑框；右下角“+”标记的位置

为填充柄。

4、使用键盘选定活动单元格：

→或Tab 向右移动 ←或shift+Tab 向左移动。

Ctrl+→:将光标定位到最后一列

Ctrl+←:将光标定位到第一列

Ctrl+↑:将光标定位到第一行

Ctrl+↓:将光标定位到最后一行

作业：

上级作业：1、练习Excel 的启动与退出的方法。

2、查看最后一行，最后一列的单元格。

理论作业：1、EXCEL 的主要功能

2、Excel 窗口的基本组成

3、工作簿与工作表的关系。

第二章 工作簿的基本操作

一、创建工作簿

方法1：文件/新建/空白工作簿

方法2: 快速访问工具栏的“新建”按钮

方法3：Ctrl+N

二、保存工作簿

1、保存新建的工作簿啊

方法1：文件/保存

方法2: 快速访问工具栏的“保存”按钮

方法3：Ctrl+S

2、方法1：文件/另存为……

方法2：F12

3、可设置自动保存（文件——选项—保存—□保存自动恢复信息时间间隔（10分钟））

三、打开工作簿

方法1：文件/打开

方法2: 快速访问工具栏的“打开”按钮

方法3：Ctrl+O

四、关闭工作簿

方法1：文件/关闭

方法2: Ctrl+F4

作业：

上机作业：1、练习对工作簿的各种操作

2、给文件加密（操作同Word）

理论作业：1、新建、打开、保存、关闭工作簿的快捷键

2、如何设置自动保存？

第三章 输入工作表数据

一、选定活动单元格

方法1：单击

方法2: 在名称框中输入

方法3: Tab或方向键

方法4: 开始/编辑/查找和替换/转到…… ctrl+G

“引用位置：□”输入单元格地址 如=A5或=sheet2!A5

二、输入单元格数据

1、数据：单元格中一切信息都称为数据。

2、数据类型：5类

1、文本（汉字、字母、文本型数字）

2、数字

3、日期时间

4、公式和函数

3、数据的输入

1）、文本的输入（默认为左对齐）

若想使某些特定的数字作为文本来处理，需在数字前输入英文状态下的单

撇号。“ ’”。

或右击单元格→设置单元格格式（ctrl+1/ctrl+shift+f）→“数字”选

项卡：文本→确定后再输入数据。

2）、数字的输入（默认右对齐）

（1）正数：直接输入

（2）负数：-数字或（数字）如：-3或（3）

（3）小数：直接输入

（4）分数：0空格几分之几 （0 1/2——1/2）

注：单元格中显示1/2，编辑栏内显示0.5。若直接输入1/2，则作为日期处理，

显示为1月2日。

说明：

1、若在单元格中输入较长数据（超11位），则自动显示为科学计数法。

-3,-3

例：0.0073=7.3*10，显示为7.3E-03，E=10

6

1234567～1.2*10显示为1.2E+06,

2、单元格内文本换行：Alt+Enter

3、若出现“######”则表示列宽不够。

3)、日期和时间的输入（默认为“右对齐”， 作为数字来处理）

（1）日期的输入：按年月日的顺序依次输入，分隔符为“-”或“/”

（2）时间的输入：按小时分钟秒的顺序依次输入，分隔符为“：”

注：

AM（A）上午； PM（P）下午；

输入当天日期：Ctrl+; 时间：Ctrl+：

输入当前时间：Ctrl+Shift+;

若同一个单元格内，同时输入日期和时间，则用空格隔开即可。

更改日期和时间格式：设置单元格格式→“数字”选项卡

三、记忆式键入（同列中实现）

1、作用：可以在同一数据列中快速填写重复录入项，如键入的起始字符与该列已有

的录入项相符，可以自动出现其余的字符，按Enter键接受填充。

2、操作：

1）若接受当前填充的字符，则按Enter

2）若替换自动填充的字符，则继续输入。

3）若删除自动填充的字符，则按Backspace或 Delete键

3、设置：文件/选项/高级 选中□为单元格启用记忆式键入。

四、快速填充数据

1、快速输入相同的数据

方法1：①选定要输入相同数据的单元格区域

②在活动单元格中输入数据

③ctrl+enter

方法2：①输入数据并选定

②直接拖动填充柄

2、序列填充的类型

等差序列、等比序列、日期序列、自动填充

1）等差序列的填充

方法1：拖动填充柄

过程：①输入序列中的初始数值

②若步长值为1，按ctrl+拖动填充柄。

若步长值不为1，输入序列的初始值和第二个数据并选中。

③向下拖动填充柄

方法2：利用“序列”对话框输入序列

①输入序列中的初始数值

②选定包含初始值的单元格区域

③开始/编辑/填充/序列……

系列产生在：○行 ○列

在“类型”区中 选“等差序列”：

设 步长值（差值）（正负均可）（+1:1 2 3 4 5 ， -1：1 0 -1 -2 -3）

终止值（不选定单元格区域时使用）若选定区域大于终止值时，以终止

值为标准； 若终止值大于选区区域时，以选定区域为标准。（以

小的为标准）④确定。

2）等比序列的填充（只能使用对话框）

①输入序列中的初始数值

②选定包含初始值的单元格区域

③开始/编辑/填充/序列……

系列产生在：○行 ○列

在“类型”区中 选“等比序列”：

步长值： 1: 5 5 5 5 5

-1： 5 -5 -5 -5 -5

0.1： 1 0.1 0.01 0.001

10： 1 10 100 1000

④确定

3）日期序列的填充

方法1：拖动填充柄

方法2：在“序列”对话框中：○日 ○月 ○年 ○工作日

4）自定义填充序列

在“序列”对话框中：○自动填充 指按自定义序列填充。

自定义序列

方法1：文件/选项/高级/编辑自定义列表/新序列/输入序列内容/添加/确定——确定

分隔符（序列内容间的分隔符）

（1）回车

（2）英文状态下的’

方法2：导入序列

（1）在工作表中录入序列的内容

(2）文件/选项/高级/编辑自定义列表/——导入——确定——确定

作业：

上机作业：1、练习单元格中各种数据的输入。

2、熟练操作Excel中快速填充数据的方法。

3、课本“水电费统计表”

理论作业：1、快速定位活动单元格的方法有哪些？

2、Excel中单元格数据类型有哪几种？

3、各种数据的输入规则？

4、如何快速填充相同数据？

第四章 工作表的编辑

一、选定单元格

（一）选定一个

1、鼠标单击

2、名称框内输入要选定的单元格的名字

3、Ctrl+G——输入要选定的单元格的名字——确定

（二）选定多个连续的单元格

1、鼠标拖动

2、名称框内输入要选定的单元格区域的名字（例：A1：B10）

3、Ctrl+G——输入要选定的单元格区域的名字（例：A1：B10）——确定

4、Shift+单击

（三）选定多个不连续的单元格

1、Ctrl+鼠标拖动

2、名称框内输入要选定的单元格区域的名字，多个区域间用“，”隔开（例：

A1：B10，D45：F60）

3、Ctrl+G——输入要选定的单元格区域的名字，多个区域间用“，”隔开（例：

A1：B10，D45：F60）——确定

（四）特殊选定

1、选定整行：单击行号

2、选定不相邻的行：按Ctrl键

3、选定整列:单击列标

4、选定不相邻的列

5、全部选定：

（1）Ctrl+A

（2）单击首行与首列的交汇处按钮

6、选定指定工作表中的单元格

(指定工作表的名称！单元格地址引用)

例：

（1）选定sheet2工作表中D10单元格：

Sheet2!D10

（2）选定sheet2工作表中D1到D10的所有单元格：

Sheet2!D1:D10

二、编辑单元格中的数据

方法：1、双击单元格

2、选中单元格后按F2键

3、单击编辑栏，修改后单击“”按钮。

三、移动和复制单元格中的数据

1、同一工作表中移动

A、移动：选中要移动的单元格数据——拖动到目标位置

B、 也可按Ctrl+X、Ctrl+V

插入式移动（同一工作表）：选中要移动的单元格数据——按Shift键拖

动到目标位置，不会替换目标位置内容。

2、不同工作表间移动：选中要移动的单元格数据——按Alt键拖动到目标工作

表标签

3、同一工作表中复制

A、复制：选中要复制的单元格数据——按Ctrl+拖动到目标位置

B、选中——Ctrl+C——Ctrl+V

4、不同工作表间复制：选中要复制的单元格数据——按Ctrl+Alt+拖动到目标

工作表标签

5、以插入方式移动和复制（同一工作表）

插入式移动：选中要移动的单元格或者行、列数据——按Shift键拖动到目标

位置，不会替换目标位置内容。

插入式复制：按Ctrl+Shift+拖动到目标位置。

注： 在复制数据时，粘贴后右下角会出现一个小按钮——“粘贴选项”按钮：

1、保留源格式

2、值和数字格式

四、行的插入与删除

1、插入：①选中一行或多行

②右击“行号”→“插入”，新行插入在当前行上方。

或开始/单元格/插入/行 选中几行插入几行。

2、删除：右击行号 →删除或选中一行或几行，开始/单元格/删除/行

五、列的插入与删除

方法同“行”插入与删除。

六、单元格的插入与删除

1、插入：

①选定要插入单元格的位置 ②开始/单元格/插入/单元格……

○活动单元格右移

○活动单元格下移

○整行

○整列

2、删除：○活动单元格左移

○活动单元格上移

○整行删除

○整列删除

七、插入批注：

1、批注：对一些包含特殊内容或公式的单元格，进行的一些说明性的信息，使

用户更容易理解单元格中的内容。

2、插入批注：

（1）选中要插入批注的单元格

（2）“审阅”选项卡——“批注”面板——新建批注——输入内容

注：

添加了批注的单元格，在右上角会出现一个小红三角，指向它时，会自

动显示批注内容。

3、编辑批注：可对输入的内容进行更改。

4、显/隐批注：可控制批注的显示状态。

5、删除批注：

注：3——5项的操作方法：右击插入批注的单元格

八、清除单元格与删除单元格

1、清除单元格：只是删除了单元格中的内容、格式、批注，单元格本身仍然保

留在工作表中。

2、删除单元格：是指将选定的单元格从该工作表中删除，并且周围的单元格自

动填补空缺的单元格。

注：

使用“清除”命令

1、方法：

选中要清除的一个或多个单元格——“开始”选项卡——“编辑”

面板——“清除”命令（橡皮擦）

2、选项：

（1）全部清除：清除单元格中的内容、格式、批注

（2）清除格式：保留数据内容和批注

（3）清除内容：保留格式和批注

（4）清除批注：保留内容和格式

九、查找和选择

1、查找、替换、定位（转到）（Ctrl+F、Ctrl+H、Ctrl+G）

方法：“开始”选项卡——“编辑”面板——查找和选择

2、定位条件：将光标定位于指定条件的单元格（批注、常量）

3、条件格式：将满足条件的记录用特定格式（字体、字形、颜色）显示出来

方法：

将需要进行条件格式查找的数据选定——“开始”选项卡——“样式”

面板——条件格式——突出显示单元格规则——

（1）针对数值：大于、小于、等于、介于

（2）针对文本：

（3）针对日期：

作业：

上机作业：1、上机练习单元格数据的修改、移动、复制

2、练习行、列、单元格的插入与删除

3、给指定的单元格添加批注

4、做相关表格的习题

理论作业：1、如何将sheet1的数据复制到sheet2中？

2、如何将一列数据已插入式移动到其它位置？

3、如何插入、修改、删除批注？

4、清除单元格与删除单元格的区别。

十、格式化工作表

（一）字符格式化设置

方法1、Ctrl+1

方法2、“开始”选项卡——“字体”面板

方法3、右击要设置格式化的字符——设置单元格格式

（二）数字格式化设置

方法1、Ctrl+1——数字

方法2、“开始”选项卡——“数字”面板

方法3、右击要设置格式化的字符——设置单元格格式——

数字

操作：

1、设置小数位数（增加/减少小数位数）

2、设置货币符号（人民币、美元）

3、设置千位分隔符（，）

4、设置百分比

（三）日期和时间格式化设置（设置日期的显示方式）

方法同上

（四）设置数据的对齐方式

方法同上（“对齐”标签）

1、设置单元格内数据的角度（45度、90度）

2、自动换行（同Alt+Enter）

3、缩小字体填充：可自动缩减单元格字符的大小，使数据调整到与单元格

列宽一致。

注：

“开始”选项卡——“对齐”面板

1、合并单元格：将选中的多个单元格合并为一个单元格

2、合并后居中：将选中的多个单元格合并为一个单元格，并将数据内容居中

显示

3、对齐：

（1）顶对齐、居中对齐、底对齐、左对齐、右对齐

（2）设置角度旋转

（五）改变列宽和行高（二者方法相同,默认列宽为8.38）

以“列宽”为例：

方法1、鼠标置于右侧边界处拖动（不精确）

方法2、右击列标——列宽（准确）

方法3、“开始”选项卡——“单元格”面板——格式——列宽（准确）

方法4、以当前单元格内输入的最大数据的宽度为宽度，来设定当前列宽（最适

合列宽）

“开始”选项卡——“单元格”面板——格式——自动调整列宽

（六）隐藏和取消行/列（二者方法相同）

以行为例：

隐藏行：

方法1、右击想要隐藏的行号——隐藏

方法2、选中想要隐藏的行号——“开始”——“单元格”面板——格式

——隐藏和取消隐藏——隐藏行

方法3、显示行时，鼠标呈 状态时，可直接动；显示列时，鼠标呈 状

态时，可直接动。

取消隐藏：

方法1、选中要取消隐藏行的，上下两行——右击——取消隐藏

方法2、选中要取消隐藏行的，上下两行——“开始”——“单元格”面板——

格式——隐藏和取消隐藏——取消隐藏行

（七）添加边框和底纹

方法1、选中单元格数据区域——Ctrl+1

方法2、右击单元格数据区域——设置单元格格式

方法3、选中单元格数据区域——“开始”——“单元格”面板——格式——设

置单元格格式——边框/填充

（八）显/隐网格线（网格线不打印）

方法：

“文件”选项卡——选项——高级——显示网格线

更改网格线的颜色：（默认为灰色）

方法：

“文件”选项卡——选项——高级——网格线颜色

（九）切换工作表

方法1、单击工作表标签

方法2、Ctrl+PageUP（向前一个工作表）

Ctrl+PageDown

（十）显示工作表标签

工作表标签左侧有4个按钮

1、第一个按钮：显示此工作簿的第一个工作表

2、第四个按钮：显示此工作簿的最后一个工作表

3、第二个按钮：左移一个工作表

4、第三个按钮：右移一个工作表

显/隐工作表标签：文件——选项——高级——显示工作表标签

（十一）插入、删除、重命名工作表

1、插入：是指在当前工作表的前面插入一个新的工作表

2、重命名：右击工作表——重命名

双击工作表标签

（十二）选定多个工作表

1、选择不连续的工作表：按Ctrl键

2、选择连续的工作表：按Ctrl键或Shift键

（十三）设置工作表标签的颜色

方法：右击工作表标签——设置工作表标签的颜色——设置颜色

注：只有关闭当前工作表的时候，才可以显示颜色

（十四）设置工作表的背景

方法：选中工作表——Ctrl+1(设置单元格格式)——填充（可设置单色、渐

变色、图案）

方法2、选中工作表——“页面布局”选项卡——“页面设置”面板——背

景（可设置背景图片）

（十五）隐藏工作表

方法：右击工作表——隐藏

（十六）改变工作表视图方式

1、视图方式：5种

普通视图、页面视图、分页预览视图、自定义视图、全屏显示

视图

2、改变视图显示方式：

方法1、状态栏

方法2、“视图”选项卡——“工作簿视图”面板——

3、改变视图的显示比例：

方法1、Ctrl+滚珠

方法2、状态栏——“显示比例”按钮

方法3、“视图”选项卡——“显示比例”面板（最小显示比例10%，最

大400%）

（十七）拆分、冻结窗口

Excel最多可拆出4个窗口，word 只能拆出上下2个窗口。

方法：利用水平、垂直滚动条上的拆分框来实现。

取消拆分：双击拆分框。

1、新建窗口：是指将当前工作簿的内容复制到一个新的窗口中。

注：

若当前工作簿名为“学生成绩表”，则新建的窗口名为：“学生成绩表：2”

2、全部重排：是指改变当前工作簿的显示状态，共4种方式：平铺、水平

并排、垂直并排、层叠。

3、冻结：是指一直有显示

（1）冻结行标题：光标放在行标题右侧的单元格

（2）冻结列标题：光标放在列标题下方的单元格

方法：“视图”选项卡——“窗口”面板

第五章 公式和函数的使用

一、公式的使用

1、公式定义：公式是在工作表中，对数据进行分析的等式，它可以对工作表中数

据进行加减乘除运算。

2、输入公式：

1、先选中输入公式的单元格

2、再输入“=”

3、再输入公式表达式（表达式由运算符（加减乘除）、常量（数）、单元

格引用、函数及括号等组成。(公式以“=”开头，由“运算符”和“运

算数”组成。

4、输入完毕，按Enter键或单击编辑栏上的“”按钮。在结果单元格

中即可显示计算结果。

3、公式中的运算符

1）算术运算符：+ - * / ^ %

（1） ^：乘方

例：3^2=3（表示3的2次方）

2

（2）%：百分比

2）文本运算符：

&（数字键7）——与：对单元格中的文本起连接作用。连接两个单元格时，

也可在&间插入某些文字，并加上英文状态的双引号。

例：

A1=“计算机”

B1=“应用”

C1=A1&B1(计算机应用)

例2：

A1=“东方”

B1=“学校”

C1=“东方计算机学校”，则C1中的公式为：

=A1&”计算机”&B1

3）比较运算符：

> < = <>(不等于) >= <=

运用它产生两个逻辑值：

逻辑真TRUE 逻辑假FALSE

二、编辑公式：（和编辑单元格中的数据方法相同）

方法1、双击

2、编辑栏

3、F2

三、复制公式

方法1、选中要复制公式的单元格——双击填充柄

方法2、选中要复制公式的单元格——拖动填充柄

方法3、选中要复制公式的单元格——Ctrl+C———Ctrl+V

五、单元格的引用

类型：3种

相对引用、绝对引用、混合引用

1、相对引用：是指相对于公式所在单元格位置的单元格。当公式被复制到其他位置

时，Excel会根据移动的位置，自动调整引用的单元格。

表示方法：列表行号 如A4 复制时能根据位置自动调整。

若D6单元格中的公式为=A6+B6+C6，则D10单元格相对引用D6单元格，D10单元

格中的公式为=A10+B10+C10

若D10单元格中的公式为=D3+D4+D5

，则C10单元格相对引用D10单元格，C10单元格中的公式为=C3+C4+C5

若C10单元格中的公式为=E2+F2，则

D10单元格相对引用C10单元格，D10单元格中的公式为=F2+G2

2、绝对引用：是指指向工作表中固定位置的单元格，它的位置与包含公式的单元格

无关，当复制绝对引用时，单元格的引用不会发生变化。（复制单元格中

的数据）

方法：“$列标$行号” 如$A$4 复制时单元格地址不变。

3、混合引用：指采用绝对列（列采用绝对引用，行采用相对引用）表示方法：$A4

采用绝对行（行列采用绝对引用，列行采用相对引用）表示方法：A$4

这两种方法来引用单元格（复制公式时，相对引用部分自动调整，绝

对引用不作调整）

转换：相对引用

→绝对引用→绝对行→绝对列→相对引用

补充：引用运算符：

（1）区域运算符（：）——连续区域

（2）联合运算符（,）——不连续区域

（3）交叉运算符（空格）

注：

求和SUM 求平均值AVERAGE

（1）求某一区域的平均值：

= AVERAGE（A1：A10）

（2）求两个或多个区域的平均值：

A1：A10 E1：E10

= AVERAGE（A1：A10，E1：E10）

（3）求交叉运算的平均值：

A1：A10 A3：C6

六、自动求和

1、自动求和

f4f4f4f4

方法1：①选定区域（包含放结果的单元格） Alt+“=”=自动求和

②单击开始/编辑/自动求和按钮/自动求和

方法2：①选中放结果的单元格

②选择自动求和按钮

③选择求和区域（虚线框）

④回车

2、快速计算:指工作表中选定了要计算的单元格区域，可在状态栏上显示该区域的平均

值、计数、求和。

七、函数的使用

方法1：输入函数

①输入=函数名（）

②光标定位于（）内，选定或输入要引用的单元格区域

③回车

方法2：利用“插入函数”对话框

公式插入函数

编辑栏上的“fx”按钮

八、常用函数

1、求和函数 Sum

格式：Sum(Number1, Number2……)参数≤30个

功能：计算所有参数数值之和。参数可以是数值，也可以是单元格地址的引用。

2、求平均值函数 Average

格式：Average(Number1, Number2……)参数≤30个

功能：计算所有参数的平均值，参数可以是数值，单元格或单元格区域的引用。

如：Average（A1：D1，E2：F2，7，8）按单元格的个数平均

3、求最大值 Max

格式：Max(Number1, Number2……)

功能：求出一组参数中的最大值

4、求最小值 Min

格式：Min(Number1, Number2……)

功能：求出一组参数中的最小值

5、条件函数（IF）

格式：IF（表达式，值1，值2）

功能：如果表达式正确，则返回第一个值，若表达式错误，返回第二个值。

例：=IF(A1>=80,“优秀”，“一般”)

6、IF嵌套函数

例1：

=IF(A1>=90,“优秀”,IF(A1<70, “一般”,“良好”))

例2：and（两个或多个条件同时满足） Or（两个或多个条件满足其中一个）

=IF(and(A1>90,B1=“男”)“满足条件”，“不满足条件”)

是指单元格内的数据，必须同时符合大于90且性别为男——最终结果才为“满足条

件”；若都不符合或只符合其中一个条件——最终结果为“不满足条件”。

=IF(or(A1>90,B1=“男”)“满足条件”，“不满足条件”)

是指单元格内的数据，只需符合数值大于90或者性别为男，其中一个条件——最终结

果为“满足条件”；若一个条件也不符合——最终结果为“不满足条件”。

7、条件求和函数 Sumif

格式：Sumif(条件区域 ，“条件”， 求和区域)

功能：对满足条件的单元格求和

例1： A B C

1姓名 工资 年龄

2张三 400 20

3李四 200 35

4张三 100 20

5王五 300 30

求张三的总工资

=Sumif(a2:a5,”张三“，B2：B5)

结果：500

例2：求20岁以上员工的总工资

= Sumif(C2：C5,”>20”,B2:B5)

8、多条件求和函数 Sumifs

格式：Sumifs(求和区域，条件区域，条件1，条件区域2，条件2……)

功能：对同时满足多个条件的区域求和

A B C D

1产品 月份 销售人 销量

2 A 二月 张三 10

3 B 二月 李四 22

4 C 三月 张三 34

5 A 二月 张三 2

6 A 三月 李四 2

7 B 二月 张三 13

例1：求张三 二月总销量

= Sumifs（D2：D7，C2：C7，”张三“，B2：B7，“二月”）

例2：求张三 二月A产品总销量

= Sumifs（D2：D7，C2：C7，”张三“，B2：B7，“二月”，A2：A7，“A”）

9、条件求平均值函数AverageIF

格式：AverageIF(条件区域 ，“条件”， 求平均值区域)

功能：对满足条件的单元格求平均值

A B C

1编号 地区 数值

2 001 江西 123

3 002 朝鲜 231

4 003 河北 912

5 004 山西 3432

6 005 陕西 6878

7 006 北朝鲜 435

8 007 南朝鲜 287

例1

求包含“朝鲜”两字的所有地方数值的平均值

=AverageIF（B2：B8，“=*朝鲜”，C2：C8）

注：“*”表示一个或多个字符

例2

求除了“朝鲜”两字以外的所有地方数值的平均值

=AverageIF（B2：B8，“<>朝鲜”，C2：C8）

10、多条件求平均值函数 AverageIFs

格式：AverageIFs(求平均值区域，条件区域，条件1，条件区域2，条件2……)

功能：对同时满足多个条件的区域求平均值

11、计数函数

1）记数字单元格的个数

格式： COUNT（参数1，参数2……）

功能：对选定区域的单元格中有数值的单元格，统计个数。

注：参数可以是数字常量，单元格地址，单元格区域

2）记非空单元格的个数

格式： COUNTA（参数1，参数2……）

3) 记空单元格的个数

格式： COUNTBLANK（参数1，参数2……）

4)条件计数 COUNTIF

格式：COUNTIF(条件区域，条件)

例：= COUNTIF（B2：B11，”>80”）

5)多条件计数 COUNTIFS

格式：COUNTIF(条件区域1，条件1，条件区域2，条件2……)

例： A B C

1 班级 姓名 性别

2 一班 张丽 女

3 二班 汪红 女

4 一班 李明 男

5 二班 江宇 男

6 一班 张晓平 男

7 一班 秦坤 男

求出一班男生人数

=COUNTIFS(A2：A7，”一班“，C2：C7，”男”)

10、纵向查找函数 VLOOKUP

格式：VLOOKUP（查找值，查找区域，第几列，0）

注：0或FALSE 精确查找

1或TRUE 模糊查找

查找值必须在查找区域的第一列

例：=vlookup(A8,$B$8:$D$15,2,0)

12、排名次RANK(返回某数字在一列数字中，相对于其他数值的大小排位)

格式：rank(数字单元格，范围,排序方式)

功能：返回一个数字在某个范围中的排位，如数字有相同，则并列。

排序方式： 0：降序（默认为0）

1：升序

例：rank(H4,H2:H9)——rank(H4，H$2:H$9)

是指H4单元格内的数值，在H2至H9的范围内的排名

13、索引查找函数

格式：INDEX(查找区域,行,列)

功能：返回行和列交差位置的单元格引用。

14、返回当前光标所在的行号

格式：ROW（）

返回当前光标所在的列号

ROWS（单元格引用）返回所选区域的行数

COLUMN（）

COLUMNS（单元格引用）返回所选区域的列数

15、求余函数

格式：MOD(被除数,除数)

求出两数相除的余数

16、取整函数

格式：INT(数值运算符数值)

功能：将数字向下舍入到最接近的整数。

17、提取字符串函数MID（表示从当前单元格内第几个字符的位置开始提取，共提取几

位）

格式：MID（单元格，起始位置，提取几位）

18、日期类函数

（1）将数字转为日期DATE

格式：date(数字，数字，数字)

例：date(2016,12,14)——2016/12/14

（2）提取日期中的年Year

格式：year(日期)

例：year(2016/12/14)——2016

（3）提取日期中的月Month

格式： Month (日期)

（4）提取日期中的日Day

格式： Day (日期)

（5）返回当前系统日期Today()

格式：Today()

（6）返回当前系统日期和时间Now

格式：Now( )

19、大小写字母的转换

格式： UPPER( )

功能：将所有英文字母转换成大写。

格式：LOWER( )

功能：将所有英文字母转换成小写。

20、删除字符串中多余的空格

格式：TRIM()

功能：删除字符串中多余的空格，但会在英文字符串中保留一个作为词与词之间

分隔的空格。

第六章 数据管理操作

一、数据清单应注意以下规则：

1、避免在同一工作表中创建多个数据清单。

2、数据清单与其他数据间至少留出一行或一列空白单元格。

3、不要在数据清单中插入空白行或空白列。

4、不要将关键数据放到数据清单的左右两侧，以免在筛选数据清单时会隐藏。

5、在数据清单的第一行中创建列标志。

6、单元格中不要有多余的空格。

二、数据清单的排序

方法：开始/编辑/排序和筛选/升序、降序

数据/排序和筛选/升序、降序、排序

步骤：（1）选中数据清单中任一单元格

（2）排序……

三、数据清单的筛选

筛选：是指将数据清单中，满足条件的记录显示出来。

（一）分类：2类 自动筛选和高级筛选

1、自动筛选

适用于：同一列数据满足条件的或多列数据与的关系的满足条件的记录。

（1）选中数据清单中任一单元格

（2）开始/编辑/排序和筛选/筛选 (字段名旁出现倒三角)

数据/排序和筛选/筛选

（3）显示全部数据清单：开始/编辑/排序和筛选/筛选

2、高级筛选：可设置多个条件（多个条件同时满足或只满足其中一个即可）

（1）两个或多个条件同时满足：条件位于同一行，“与”关系

语文 数学

>90 >90（是指语文和数学成绩同时满足大于90分才显示）

（2）两个或多个条件满足其中一个即可：条件位于不同行，“或”关系。

语文 数学

>90

>90（是指语文或数学成绩只要有其中一个大于90分的就显示）

1）对数据清单中的字段筛选

步骤：（1）建立条件区域，在数据清单下方至少隔一行的位置

（2）选中数据清单中任意一单元格

（3）数据/排序和筛选/高级……

在原有区域显示筛选结果

将筛选结果复制到其他位置

数据区域 条件区域

复制到 指将筛选结果放置的起始单元格 确定

取消高级筛选：数据/排序和筛选/清除

2）高级筛选支持通配符的使用

条件区域： 姓名 姓名

张* <>张*

筛选姓张的员工 筛选不姓张的员工

3）区间的查询

销售日期 销售日期

>=2017-1-15 <=2017-2-15 筛选销售日期在……之间的记录

4）利用公式进行筛选

可筛选清单中两列或多列关系的数据

记录：不能是清单中的字段名

条件：可以是表达式、公式、函数

例：听力大于笔试 笔试 听力

=D12>C12 45 72

98 93

例;09-3题

平均成绩 单项条件

① ②

True True

① =average(B12:E12)>=80

② =or(B12>90,C12>90,E12>90)

筛选：平均成绩80分以上（含80），且至少有一单项成绩超过90分（不含90）的记录

另外：电子表格32份07中“统计”工作表

09中“英语成绩表”

四、分类汇总

（一） 1、创建分类汇总

①、对要进行分类汇总的字段进行排序（光标定位于要分类字段的任意一

个单元格/始/编辑/排序和筛选/升/降序）

②、光标定位于数据清单中任一单元格

③、数据/分级显示/分类汇总……

设置：

（1）分类字段（排序的字段名）

（2）汇总的方式（求和、平均值等）

（3）选定汇总项

替换当前分类汇总（不选中可做多重分类汇总）

每组数据分页

汇总结果显示在数据下方。确定

2、显示或隐藏明细数据

分级显示栏（1,2,3）标题行的左侧 1 2 3 逐级明细

单击行级符号 1，仅显示总计和字段名

2，仅显示各类别的分类汇总值和总计

3，显示分类汇总的明细数据

单击“隐藏明细数据按钮”，隐藏下一级明细数据

单击“显示明细数据按钮”，显示当前级的下一级明细数据

（二）取消分类汇总（恢复原数据）

方法：光标定位于数据区域的任意一个单元格/数据/分级显示/分类汇总/全

部删除/确定

（三）注：

1、替换当前分类汇总：

（1）若选中，则新的汇总结果将覆盖原有结果（原汇总结果没有）

（2）若取消，则旧/新汇总结果依次显示（都有显示）

2、汇总结果显示在数据的下方

（1）若选中，则结果显示在数据的下方

（2）若取消，则结果显示在数据的上方

3、每组数据分页：将每一个分类汇总的结果都单独放在一页上显示。

五、合并计算

是指可以通过合并计算的方法来汇总一个或多个源区中的数据。

前提：在源区域的数据必须被相同排列在相同的相对位置上。

步骤：选定合并计算的目标区 数据/数据工具/合并计算

函数：求和

引用位置：合并计算的第一个区域“添加”

合并计算的第二个区域“添加”

标签位置 首行

最左列

确定，即可将两区域的数值“求和”合并计算。

六、数据透视表：是指按相同项目进行统计

方法：

1、选中清单中任一单元格

2、“插入”选项卡/“数据透视表”

3、选择源数据：（1、在当前工作表内选择2、从外部数据获取）

4、选择放置数据透视表的位置（1、在当前工作表内；2、存在于新工作

表中）

5、将字段分别拖至“报表筛选”、“列标签”、“行标签”、“数值区”完成

类型：2种

数据透视表

数据透视图（相对于透视表而言，多一项图表）

第七章 图表的使用

一、创建图表

1、类型：2类

（1）嵌入式图表：图表插入在有数据的工作表中。

(2 )图表工作表(F11)：默认名称为Chart1,是指单独存在于一个工作

表中,默认为柱形图。

2、方法：（1）光标定位于数据区域的任意一个单元格内或选中图表中所

需数据（2）插入/图表/选图表类型……

注：柱形图：用于显示或比较多个数据组。

饼 图：通常用来表示一组数据，可表示每一项占总和的比例。

折线图：适用于表示数据的变化趋势

二、图表的基本操作

1、更改图表区的格式

单击图表项或右击图表项

图表区属性设置：

（1）填充 可设置纯色、渐变色、图片和纹理、图案、自动填充

（2）图表区边框及边框的颜色 （3）图表区的阴影

（4）图表区的三维格式 （5）图表区的大小

2、更改图表位置

方法1：右击图表的空白处——移动图表

方法2:图表工具/设计/位置/移动图表

注：创建快速图表工作表

选中数据区域——F11（只能创建柱形图表）

3、设置图表的各个标题（整个图表的标题、分类轴标题（X轴）、数值轴

标题（Y轴）、图例、数据标签）

选中图表区（绘图区）/图表工具/布局/标签/图表标题、分类轴标题、

图例

4、切换行/列

图表工具/设计/数据/切换行/列 选择数据

5、格式菜单：用来设置图表的填充、轮廓等格式。

6、设置数值轴的主要、次要刻度值

右击数值轴——设置主（次）要网格线格式

7、更改各部分文字的字体、字号等格式

右击文字——字体

注：

1、如源数据发生变化，相应的图表也会随着变化。

2、如源数据被删除，相应的图表中该项会自动删除。

理论作业：1、图表的分类？

2、如何创建图表？

3、如何更改图表的格式？

第八章 查看、保护和打印表格

一、查看工作表

在查看大型报表时，因为行、列数太多，使得数据内容与行列标题无法对照。可

使用拆分窗格或冻结窗格命令来解决，从而提高工作效率。

拆分的主要作用是进行数据的比较，而冻结的主要作用是始终显示想要的行标题

和列标题。

拆分窗格的方法：利用鼠标拖动窗口右上角的垂直拆分框或右下角的水平拆分框。

冻结窗格的方法：选中某单元格，单击视图/窗口/冻结窗格/冻结拆分窗格。

二、隐藏单元格内容

思路：首先隐藏不想显示的单元格内容，然后对工作簿的结构进行保护，再保护

有重要数据的工作表。

操作过程：1、在工作表中选择要隐藏的单元格区域。

2、单击开始/字体右下角按钮，出现“设置单元格格式”对话框，在数字

选项卡的“分类”列表中选“自定义”，在“类型”框中输入三个半角的分号;;;

(若要显示隐藏的单元格内容，选择要显示内容的单元格区域，单击开始/

编辑/清除/清除格式

三、保护工作簿

为防止他人添加或删除工作簿中的工作表，或者改变工作簿窗口的大小和位置等

1、打开工作簿，单击审阅/更改/保护工作簿，出现“保护结构和窗口”对话框

2选中“结构”复选框，在“密码”框中输入密码

注意：要撤销工作簿的保护，再次执行上述命令，输入密码。

四、保护工作表

保护工作簿只能防止工作簿的结构和窗口不被编辑修改，要使工作表中的数据不被修

改，还需对工作表进行保护。

1、打开工作簿，选中要保护的工作表

2、单击审阅/更改/保护工作表，输入密码，选择允许操作的选项。

撤销工作表保护与撤销工作簿的保护类似。

补充：

一、单变量求解 是函数公式的逆运算。

以如下表格为例，要求单价和数量不变，请问折扣要为多少？

A B

1 单价 2.2

2 数量 870

3 折扣 93%

4 金额 =B1*B2*B3 1780.02

数据/数据工具/模拟分析/单变量求解……

假如设定想要金额为2000

目标单元格 B4

单价 2.2

目标值 2000

数量 870

可变： 折扣

可变单元格 B3

折扣： 104%

确定，分析完成，原数据不变

金额 2000

确定

二、模拟运算表

利用模拟运算表，在一种特定的运算方式下，改变其中一个条件，那么结果也要

发生相应变化，属于一种假设分析的情况。

A B

1 单价 10

2 数量 20

3 金额 =$B$1*$B$2

(一)单变量模拟运算

格式1：

20 25 30 40

数量

公式

金额

选中区域后，数据/数据工具/模拟分析/模拟运算表……

输入引用行的单元格：B2 确定

格式2：

单价

5 5.5 6 8 10

金额

公式

输入引用行的单元格：B1

格式3：

公式

20

25

30

40

输入引用列的单元格B

数量

（二）双变量模拟运算

格式：

补充：

二十、“文件”选项卡

1、更改工作簿表中默认的工作表数量

文件——选项——常规——包含的工作表数

注：

一个工作表默认情况下由1048576行，16384列（1048576*16384）个

单元格组成

二十一、“页面布局”选项卡

1、打印预览：Ctrl+F2

2、设置打印标题：

页面布局——页面设置——打印标题——选择“顶端标题行”

3、设置工作表的居中方式（一般为“水平居中”）

页面布局——页面设置——页边距——自定义边距——居中方式