Excel中级企业培训.docx

Excel中级企业培训.docx

《Excel中级企业培训.docx》由会员分享，可在线阅读，更多相关《Excel中级企业培训.docx（43页珍藏版）》请在冰豆网上搜索。

Excel中级企业培训

EXCEL2007高级

（一）制作表格（更多用于

财务制表、数据统计）

word：

文员制表）

（二）函数：

1.统计函数：

（1）求和:

SUM

（2）平均值:

AVERAGE（3）乘法:

PRODUCT（4）最大值：

Max（5）最小值：

Min

（6）求差（7）SMALL（8）SUMIF（9）RANK（10）MODE（11）FREQUENCY（12）COUNT

（13）COUNTA（14）COUNTIF（15）COUNTBLAK

2.日期和时间函数：

（1）DAY

（2）TODAY（3）YEAR（4）INT

3.逻辑函数：

（1）AND

（2）OR

4.判断函数：

（1）IF函数

5.VLOOKUP.

EXCEL主要内容

（三）数据管理:

1.排序：

升序、降序、自定义序列

2.筛选：

自动筛选、高级筛选

3.分类汇总：

第一步排序

4.数据图表

5.数据合并计算

6.数据有效性

7.数据透视表

8.数据导入、导出

（四）引用功能：

1.相对引用功能

2.使用绝对引用功能

3.混合引用功能

①EXCEL表格：

单元格无法自己调大小，只能整行整列设置；所以经常使用word制作表格；

②EXCEL表格中换行：

Alt+Enter

文件加密码：

office按钮→准备→加密文档→输入密码→确定→再输入一次

取消文件加密码：

office按钮→准备→加密文档→删除密码→确定

一、Excel基础：

工作簿：

就是一个公司；工作表：

就是一个一个的部门

1.工作簿（Workbooks）

一个Excel文件是一个工作簿。

2.工作表（右击工作表Sheet1标签，在快捷菜单中选择命令）

工作表的标签为Sheet1、Sheet2：

（1）插入：

或sheet表底端右侧的小花，

插入一个新的工作表sheet4、5、6

（2）删除：

数据也永久删除，无法还原

（3）重命名：

右击

（4）移动或复制：

勾上建立副本就是复制，否则就是移动

3.单元格

（1）单元格的访问方式：

（单元格的地址）

A1方式：

列号行号，（A列1行）如B3

类似于坐标轴（X，Y）：

4.表格的选取

①选中单个单元格：

鼠标左键单击单元格

②选中单元格区域：

拖动鼠标左键可以选定连续的区域

③选中不连续的单元格：

也可以按Ctrl键选择不连续的区域

④选中所有单元格：

击表格1和A左上角的空白长方形

⑤选中整行或整列：

鼠标左键单击表格上方或者左侧

5.数据的编辑与修改

（1）选定单元格可在编辑栏内输入信息；双击单元格，可在单元格内录入信息

（2）选定单元格，直接在编辑栏内修改；双击单元格，在单元格内修改

PS:

快速输入横竖均可

①如何快速输入一批相同的数据？

（输入1，然后选择单击单元格，单元格右下角出现+，然后下拉复制全是1）

②序号123456789？

（输入1、2，然后选择1、2两个单元格，单击单元格右下角出现+，然后下拉是123456789）

③还可以快速输入的文本有：

星期一、星期二到星期日；一月、二月到十二月；不可周一周二，一月份二月份，可只输入一个，下拉即可

如何让一月下拉后全是一月：

输入，两个一月，选择两个一月，鼠标放在右下角→下拉即可。

6.删除表格中的数据

（1）一次性删除所有数据：

先选择整个表格→点击DELETE键→一次性删除所有数据

（2）删除一个单元格的数据：

单击该单元格→点击删除键“←”或“Backspace”

7.在工作表中添加、删除行与列

（1）先选择行或列→右击→插入→会在选择的行或列的前面插入行或列

（2）先选择行或列→右击→“删除”即可

8.工作表格式化

（1）设置列宽、行高

①鼠标光标移动到两列列标或两行行标的交界处，按下鼠标指针可显示列宽或行高。

（AB之间，12之间移动）

②精确改变列宽或行高：

（开始→单元格→格式→行高、列宽，自动调整行）

（2）Ctrl+F查找：

输入查找内容→查找全部：

海尔→苏宁展示样机→J2T-Q83DM（12T）

（3）合并单元格：

选定要合并的多个单元格，（开始→对齐方式→合并及居中），在单元格输入字体就是居中的了。

再点击一下合并单元格取消合并单元格；

选择的要合并的单元格中都有数据，合并单元格后只保留单元格左上角的数据；

（4）单元格格式（单击单元格→右击→设置单元格格式→数字、对齐、字体、边框、填充）

1数字（先设置文本型后输入数据）

数值：

设置小数点位数；货币：

设置货币符号；日期；百分比；

PS:

录入超过15位数字时，（如身份证号码）或者“0开头的文本”→输入时应将单元格更改为为“文本型”数据才能完整地保留。

2对齐：

一般当单元格内字体靠单元格的下方时候，会设置对齐方式：

水平对齐：

居中；垂直对齐：

居中（文字方向：

水平、垂直）

3字体：

字体、字形、字号、颜色等的更改（开始→字体→可更改）

4边框（设置方式桐word表格→选择要更改样式的单元格，在预览中先将其点击消失→选择新样式、颜色、宽度再点击上→确定）

作为表格是一定要有边框的，没有边框的表格打印出来只有字，先选定要添加边框的单元格，然后进行设置。

5

填充→颜色：

是给单元格填充颜色；

样式：

是给单元格填充纹路。

效果：

9.Excel表格制作顺序

（1）从A1位置开始，先打标题；

（2）将表格内所有竖线延长；

（3）数最多的表格列数；

（4）数行数，然后选择行数、列数区域进行添加内外边框；

（5）然后一行一行输入数据；

（6）遇到文本占多列单元格进行合并；（要合并的单元格较多时，合并第一个，然后选择该单元格鼠标放右下角，下拉即可）

（7）标题进行合并单元格→根据表格添加的边框长度进行合并；

（8）调整标题和正文的字号、加粗等；（在excel表格中进行字号的调整可以单击单元格，无需拖动选文字）

（9）调整行高、列宽；

（10）进行打印预览，使表格都在一页，居中位置；（如不在一页，或者不居中，可进行页边距和表格边框的调整）

10.自动套用格式（开始→样式→自动套用格式）

（1）就是将黑子白色底纹的单元格，进行添加底纹，让表格看起来更加美观；（实际工作中，无需这样花花绿绿的表格）

（2）操作步骤：

1选择要套用格式的数据区域；

2然后进入：

开始→样式→自动套用格式→选择自己看好的格式；

3弹出窗口表包含标题：

数据带标题就勾选；不带标题就不勾选；

4取消筛选标签→选择筛选标签表格→数据→排序和筛选→点击“筛选”即可取消筛选

（3）取消自动套用格式

点击已添加自动套用格式的单元格处→功能区最后出现：

表工具→设计→表样式→下拉：

清除

11.设置条件格式

当我们处理大量数据时，有时希望符合某些特定条件的数据能醒目的显示出来，方便我们查看，使用条件格式，醒目显示特定数据。

一、根据单元格本身数值大小判断

例1：

上半年销量，我们需要将B列中，数值大于等于70的填充绿色底纹，小于等于60的填充变红色底纹。

选中B2:

B7→开始→样式→条件格式→新建规则→按下图设置：

点击格式填充→绿色即可

然后重复上面的步骤，再为<=60，添加条件格式，确定即可

例2.图标集、色阶等设置：

然后点击其他规则，可进行数值的改变：

取消条件格式：

开始→样式→条件格式→清除规则→清除整个工作表的规则

二、函数（位置：

公式→函数库→自动求和→下拉→其它函数→或选择类别→全部）

（一）自己输入

步骤：

（1）点击要求和：

+（求差：

—、求积：

*、平均数：

/）的单元格

（2）在编辑栏

输入公式=单元格之间的加减乘除，（输入单元格地址，可以单击单元格单元格地址自动就到编辑栏里）（=F3+G3+H3）

（3）输入完公式最后点击“

”

（4）其他求和：

单击已经求和的单元格→鼠标放在单元格右下角→变成十字箭头→向下拉

（二）功能区选择

步骤：

（1）点击要求和（或其它函数）的单元格

（2）公式→函数库→自动求和→下拉→求和

（3）会自动圈住一些单元格→先单击一个单元格→然后按住Ctrl键选择要相加的单元格

（4）选好公式最后点击“

”

（5）其他求和：

单击已经求和的单元格→鼠标放在单元格右下角→变成十字箭头→向下拉

PS:

求和

步骤：

（1）点击要求和（或其它函数）的单元格

（2）公式→函数库→自动求和→下拉→求和（或其它函数）

（3）会自动圈住一些单元格→先单击一个单元格→然后按住Ctrl键选择要相加（或其它函数）的单元格

（4）选好公式最后点击“

”

（5）其他求和（或其它函数）：

单击已经求和的单元格→鼠标放在单元格右下角→变成十字箭头→向下拉

PS：

=SUM（F3：

H3）或=PRODUCT（C2，D2）

求差：

无英文只能自己输入单元格之间相减：

=A2-A1

（一）统计函数

1.

求和：

SUM

2.平均值:

AVERAGE

3.乘法:

PRODUCT

4.最大值：

Max

5.最小值：

Min

6.求差

7.SMALL

（1）含义：

返回数据集中第k个最小值。

（2）语法：

=SMALL（array,k）

array：

为需要找到第k个最小值的数组或数字型数据区域。

k：

为返回的数据在数组或数据区域里的位置（从小到大）。

（3）案例：

假设你在A1至A100是分数，你要找出这个区域的最低分，公式可以为：

=SMALL（A1：

A100,1）

查找倒数第二的分数，公式为：

=SMALL（A1：

A100,2）

8.SUMIF

（1）含义：

计算符合指定条件的单元格区域内的数值和。

（2）语法：

=SUMIF（Range,criteria,Sum_range）

Range：

代表条件判断的单元格区域；（男女区域）

criteria：

为指定条件表达式；（男还是女）

Sum_range：

代表需要计算的数值所在的单元格区域。

（语文成绩）

（3）案例：

在C34单元格中输入公式：

=sumif（C3：

C32,”男”,D3：

D32），确认后即可求出“男”生的语文成绩和。

在C354单元格中输入公式：

=sumif（C3：

C32,”女”,D3：

D32），即可求出“女”生的语文成绩和。

其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号（”男”，”女”）中。

9.RANK

（1）含义：

rank函数是排名函数。

rank函数最常用是求某一个数值在某一区域内的排名。

（2）语法：

=rank（number，ref,order）

number：

为需要排名的那个数值或者单元格名称（单元格内必须为数字）

ref：

为排名的参照数值区域。

Ø默认不用输入，得到的就是从大到小的排名；

Ø想求倒数第几，order的值请使用1.

order：

（3）案例：

示例1：

正排名=RANK（20,A1:

A6）

我们在B2单元格求20这个数值在A1：

A5区域内的排名情况，不输入order参数的情况下，默认order值为0，也就是从高到低排序。

此例，20在A1：

A5区域内的正排序是1，所以显示结果是1。

示例2：

求一列数的排名：

我们求A1到A5单元格内的数据的各自排名情况。

=RANK（A2,$A$2:

$A$6）

我们可以使用单元格引用的方法来排名：

试求：

A列数据的倒数排名：

=RANK（A2,$A$2:

$A$6，1）

10.MODE

（1）含义：

该函数用以返回出现频率最高的数值。

（2）语法：

假设已知某些同学的语文、数学和英语成绩如图，现计算各科成绩中出现次数最多的分数，在单元格H12中输入以下公式：

“=MODE（H3：

H11）”。

如果没有出现两次及以上的分数，会出现结果“#N/A”。

（3）案例：

11.FREQUENCY

（1）含义：

以一列垂直数组返回某个区域中数据的频率分布。

（2）语法：

=FREQUENCY（data_arry,bins_arry）

1data_arry：

表示用来计算频率的一组数据或单元格区域；

2bins_arry：

表示为前面数组进行分隔一列数值。

（3）案例：

统计出数学成绩在0-59；60-69；70-79；80-89；90-100的人数分布。

特别提醒：

上述输入的是一个数组公式，输入完成后，需要通过按“ctrl+shift+enter”组合键进行确认，确认后公式两端出现一对大括号（{}），

此大括号不能直接输入。

12.COUNT（只计数→文本空格不计）

13.COUNTA（统计非空单元格→只要有内容就会统计）

区分函数COUNT和COUNTA；

案例：

1）制作1月出勤加班统计表，表中包括员工1月出勤加班统计表以及需要统计的内容；

2）使用COUNT函数统计各列单元格的个数，在单元格B13中输入以下公式：

“=COUNT（B3：

B11）”，此时可以看到包含文字的单元格和空白单元格被忽略了，只统计包含数字的单元格；

3）使用COUNTA函数统计各列单元格的个数，在单元格B14中输入以下公式：

“=COUNTA（B3：

B11）”，此时可以看到包含文字的单元格也被统计在内了。

14.COUNTIF

（1）含义：

根据条件在另一个区域进行个数的统计。

（2）语法：

=COUNTIF（range，criteria）；

1Range：

表示要计算其中非空单元格数目的区域；

2Criteria：

表示统计条件。

（3）案例：

15.COUNTBLANK

（1）含义：

统计空白单元格的个数。

（2）案例：

在统计报表中，经常由于多种原因导致无数据值的情况。

例如，在学生成绩中，由于有学生缺考，所以成绩表中不存在该学生的成绩，导致对应单元格为空白，通过使用COUNTBLANK函数可以快速方便的统计出缺考人数。

方法/步骤：

选择B10单元格，输入“=COUNTBLANK（B2：

B9）”区域内空白单元格的个数。

（二）日期和时间函数：

（以下单元格日期尽量使用：

2014-10-7格式）

1.DAY

（1）含义：

这个函数可以从一个完整的日期中提取出日。

（2）语法：

=DAY（serial_number）

serial_number：

代表指定的日期或引用的单元格。

（3）案例：

输入公式：

=DAY（“2003-12-18”），确认后，显示出18。

特别提醒：

如果是给定的日期，请包含在英文双引号中。

2.TODAY

（1）含义：

给出系统日期。

（2）语法：

=TODAY（）参数说明：

该函数不需要参数。

（3）案例：

输入公式：

=TODAY（），确认后即刻显示出系统日期和时间。

Ø如果系统日期和时间发生了变化，只要按一下F9功能键，即可让其随之变化。

Ø特别提醒：

显示出的日期格式，可以通过单元格格式进行重新设置。

3.YEAR

（1）含义：

这个函数可以从一个完整的日期中提取出年。

（2）语法：

=YEAR（serial_number）

serial_number：

为一个日期值，其中包含要查找年份的日期。

（3）案例：

“工龄”计算（将E2：

E4设置为数字→常规）

所谓“工龄”就是从参加工作起，每过一年就增加一年工龄，利用YEAR函数计算工龄的公式是：

=year（today（））-year（D2）

4.INT

（1）含义：

将数值向下取整为最接近的整数。

（2）语法：

=INT（number）

number：

表示要取整的数值或包含数值的应用单元格。

（3）案例：

应用举例：

输入公式：

=INT（18.89），确认后显示出18.

特别提醒：

在取整时，不进行四舍五入；如果输入的公式为=INT（-18.89），则返回结果为-19。

（三）逻辑函数

1.AND

（1）含义：

AND（与），”就是”并且”的意思。

AND函数有两个（或以上）参数,AND的功能就是取这几个参数的交集.我们要记住的是, 只要参数中有任何一个的值是FALSE,那么AND函数的值就是FALSE;仅当所有参数都是TRUE的时候,AND函数的值才是TRUE。

（2）语法：

=AND（logical1，logical2，…）

logical1，logical2，logical3……：

表示待测试的条件值或表达式，最多30个。

（3）案例：

应用举例：

在C3单元格输入公式：

=AND（A3>60,B3>10），确认。

两个条件其中一个是错误的：

返回FALSE；

两个条件两个都是正确的：

返回TURE；

2.OR

（1）含义：

OR（或） :

OR其实和AND的用法和参数都一样,区别是"AND只要有一个参数是FALSE则结果是FALSE";"OR函数只要有一个参数是TRUE则结果就是TRUE".

（2）语法：

=OR（logical1，logical2，…）

logical1，logical2，logical3……：

表示待测试的条件值或表达式，最多30个。

（3）案例：

应用举例：

在C1单元格输入公式：

=OR（A1>=60,B1>=15），确认。

两个条件其中一个是错误的：

返回TURE；

两个条件两个都是错误的：

返回FALSE；

我们现在来举个例子：

如下图所示数据，列出了TeamA和TeamB中每个人的电话量和邮件处理量。

我们的问题是：

请找出电话和邮件量都多于20个的Agent.

分析题目:

要找出电话和邮件量都多于20的Agent,也就是说"电话量要大于20"并且"邮件量也要大于20":

"电话量要大于20"用公式表达就是（G4>20）；"邮件量要大于20"用公式表达就是（H4>20）

那么:

"电话量要大于20"并且"邮件量也要大于20"就是:

I4=AND（G4>20,H4>20）（然后我们用之前说的把鼠标移到I4单元格的右下角变成粗体十字的时候双击.这样我们就得到下面的结果:

这样看其实已经知道结果了，那我们就用前面讲的IF函数来"美化"一下:

如果"电话和邮件量都多于20个",则显示"Good",其他显示"Normal".怎么写?

J4=IF（AND（G4>20,H4>20）,"Good","Normal"），结果如下:

假设今天是3月24日，D列的“约定还款日期”距今天不足10天时变绿色提醒，距今天不足2天时变红色提醒。

提示：

条件格式、AND、today函数的使用

操作步骤：

选中A2：

D4区域→开始→样式→条件格式→新建规则→按下图设置：

点击格式填充→绿色即可

第一个条件中有公式=AND（$D2-TODAY（）<10,$D2-TODAY（）>=2）：

它的含义是判断今天的日期与D2的相差天数，是否同时符合“小于等于10”并且“大于2”，如果成立就启用第一个条件格式。

函数TODAY（）是日期函数，它可取得电脑中当天的日期值。

第二条件中有公式=$D2-TODAY（）<2，判断D2格中的日期值与今天是否相差2天。

（四）判断函数：

1.IF函数（在输入栏中输入）

=IF（B2<60,”不及格”,”及格”）

不符

合条件

符合

条件

判断

条件

（1）先将公式写在纸上，再输入；标点在英文状态下输入

（2）IF数=条件数-1=右侧括号数（条件数：

即及格、不及格的条件数量）

例1.X>=85，优

85>X>=60,及格

X<60,不及格

=IF（J3>=85,”优”，IF（J3>=60,”及格”,”不及格”））

例2.

X>=85，优

85>X>=75,良

75>X>=60，及格

X<60,不及格

=IF（J3>=85,”优”,IF（J3>=75,”良”,IF（J3>=60,”及格”,”不及格”）））

练习题：

优、良、中等、及格、不及格

优:

>=90

良:

80<=X<90

中等:

70<=X<80

及格:

60<=X<70

不及格:

X<60

=IF（J3>=90,”优”,IF（J3>=80,”良”,IF（J3>=70,”中等”,IF（J3>=60,”及格”,”不及格”））））

AND函数中IF使用：

23页

（五）VLOOKUP

（1）含义：

VLOOKUP是一个查找函数，给定一个查找的目标，能从指定的查找区域中查找返回想要查找到的值。

（2）语法：

=VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找）

（3）案例：

例1：

如下图所示，要求根据表二中的姓名，查找到姓名所对应的年龄。

排序：

选除标题外，所有数据

三、数据管理筛选：

选要筛选的列选数据

分类汇总：

选除标题外，所有数据

数据图表：

选择需要信息的列（不连续的列CTRL）

（一）排序：

升序、降序、自定义序列

1.位置：

开始→编辑→排序和筛选→自定义排序

2.普通排序原则：

（1）“升序”--从小到大；

（2）“降序”--从大到小

（3）自定义序列：

升降序无法达到的排序，用它，打字用回车键隔开。

3.

（1）主要关键字：

先满足第一个条件；

（2）次要关键字：

再满足第二个条件。

4.排序操作

选取除标题外的所有的数据→数据→排序→选择关键字→选择升降序→确定即可

5.排序：

可将同类数据放到一起，分类汇总时需要；

6.在Excel中经常要对一些姓名进行排序，在默认情况下是按文字的拼音排序的，很多时候需要其它的排序方法，比如说按姓氏的笔画来排序，具体做法：

选取除标题外的所有的数据→数据→排序→选择关键字：

姓名→选择升降序→确定即可（默认按照：

ABCDEFG…排序）

注：

如果需要按笔画排序，选择关键字姓名以后，需要设置选项内容，点击以后里面勾选笔画即可

（二）筛选：

自动筛选、高级筛选

1.数据筛选：

按指定条件对数据清单中的记录进行选取，只显示满足条件的记录，而隐藏其它记录。

2.筛选操作步骤：

筛选：

选择要筛选的列→开始→编辑→排序和筛选→筛选

高级筛选：

输入条件后→数据→排序和筛选→高级

筛选：

回到数据→排序和筛选→再点击“筛选

”

3.取消筛选操作：

高级筛选：

回到数据→排序和筛选→再点击“清除”

4.筛选：

当条件数是一个时；进行简单的筛选（选择要筛选的列→数据→排序和筛选→筛选→标题位置有小三角）

进入筛选界面→

（1）日期筛选→等于、之前、之后、介于（筛选1978年到1979年出生：

1978-1-1到1979-12-31）

（2）数字筛选→大于、小于、等于、10个最大值、低于平均值

5.高级筛选：

当条件数大于一个时；

以高级筛选的方式筛选出“销售额”大于10000或利润大于1000的记录。

在B16单元格输入“销售额”，在B17单元格输入“>10000";

在C16单元格输入“利润”，在C18单元格输入“>1000"

注意，上述条件区域上方或下方至少插入三行空白行，且条件“>10000"与“>1000"不能位于同一行。

（或的关系；同一行是并且：

“销售额”>10000且利润>1000）

单击一个单元格（离数据区域远点的任意单元格）点击”数据“-”筛选“-”高级筛选“，列表区域：

除了标题外的数据（同排序选择数据方式）；条件区域：

选中B16到C18的单元格→确定。