ECEL基础培训教材

目录

第一部分基本功能应用.............................4

隐藏/显示功能区：......................................................4

条件求和函数的应用......................................................5

SUMIF函数............................................................5

SUMIFS函数..........................................................7

保护工作表.............................................................9

如何快速取出单元格内空格...............................................14

圈释无效数据...........................................................15

数据分列...............................................................18

日期的处理............................................................22

DATE函数............................................................24

LEFT函数............................................................25

MID函数.............................................................26

RIGHT函数...........................................................26

LEN函数.............................................................27

FIND函数............................................................27

制作日期表............................................................29

WEEKDM函数........................................................30

如何将文本型数字转换为数字.............................................33

VALUE函数...........................................................33

不规范表格整理.........................................................36

嵌套查询..............................................................39

IF函数..............................................................39

多级查询及筛选.........................................................42

VLOOKUP函数........................................................42

IFERROR函数（07版新增）.............................................43

1/116

自动筛选............................................................46

从报表中提取相关信息..................................................46

日期函数的应用.........................................................47

EOMONTH函数.......................................................48

YEAR函数............................................................49

YEARFRAC函数........................................................50

INT函数.............................................................51

DATEDIF函数.........................................................51

ROUNDUP函数........................................................52

时间格式的设置.......................................................53

自定义单元格格式.......................................................53

日期提醒............................................................53

根据条件格式制作表格................................................54

自定义数字格式.......................................................56

分类汇总与分级显示.....................................................57

分类汇总............................................................57

分级显示............................................................59

合并计算工具...........................................................60

第二部分数据透视表的使用.........................62

透视表的使用规则.......................................................62

多重合并计算数据区域..................................................62

SQL语句...............................................................66

MicrosoftQuery工具.....................................................70

使用数据透视表向导.....................................................75

第三部分举例练习................................77

如何制作多角度分析报表................................................77

如何制作“年、季度、月汇总”报表......................................80

如何对2张表的数据进行快速对比........................................82

如何对数据进行同比分析................................................84

如何对各学历各年龄段的结婚情况进行统计.................................94

动态生成报表图表.......................................................96

使用POWERPOINT动态显示报表图表......................................104

小结.......................................116

第一部分基本功能应用

这一部分主要介绍些EXCEL的基本操作和小窍门，便于日后方便快捷的使用。

使用EXCEL的基本素养

1、充分认识Excel是一个科学管理工具

Excel是一个科学管理工具，不是一个高级计算器，也不是一个方格纸；

管理要用数据说话，而不是拍脑袋做决策；

一个有说服力的报告的重点内容是数据分析表格和图表。

2、养成使用Excel的好习惯

合理设计Excel表单，为日常数据管理和处理分析打好基础；

原始数据表格与数据处理分析表格分开保存；

采用合理的方法获取外部数据。

3、自定义Excel操作界面

添加常用的操作按钮（格式按钮、增大/缩小字号按钮、选择性粘贴按钮、粘贴

数值按钮、等等）；

熟练使用常用的快捷键。

隐藏/显示功能区:

07版的EXCEL将菜单转换为功能模块显示在顶端，更加美观直接，但这样造成

可视区域变小。为在正常使用时显示数据较多，可以双击“开始”功能块，隐藏

功能区，再双击下，可以重新显示功能区（CTRL+F1）。

A1

ABCDEFGHI

1

2

3

4

图1

条件求和函数的应用

在对单元格进行统计时，经常需要根据各种条件进行求和，求和函数为“SUM”,

单条件求和函数为“SUMIF"，多条件求和函数为“SUMIFS”(07版新增。

请打开《课前测验与练习之1》

在该案例中，最后一行“总计”，需根据各产品的''小计”值统计出总和，

原公式为“=+C4+C7+C10+C13+C16+C19+C22+C25”，即手动将各单元格相加。这

样做有两点问题：

1、如果将某个产品删除，该单元格将显示错误，无法正确得到结果；

2、如果数据过多，容易加错，最终结果不准确。

因此，为了解决以上问题，可以使用SUMIF函数进行求和。

SUMIF函数

作用：对区域中符合指定条件的值求和。

图3

公式及各参数意义：SUMIF（range,criteria,[sum_range]）

Range必需。用于条件计算的单元格区域。每个区域中的单元格都必须是数字或

名称、数组或包含数字的引用。空值和文本值将被忽略。

Criteria必需。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、

单元格引用、文本或函数。

Sum_range可选。要求和的实际单元格。如果sum_range参数被省略，EXCEL会

对在range参数中指定的单元格求和。

对“总计”的求和公式使用SUMIF函数进行优化，操作步骤如下：

1）打开“SUMIF”函数；

2）在range中选择：B2:B25,加上在各字母和数值$进行绝对定位（F4）；

3）在criteria中输入要统计的单元格："小计”，注意小计的引号为英文格式；

4）在sum_range中选择：C2:C25,点击确定，完成。

DMN0

2月11月12月年度合计

15.71572.860

7,425109.635

r

:、42X144182.495

5.95262.467

612.60667.248

7小计28,51018,558129,715

8BBB0~

9GOG'17.18419,757157,380

10而17,1847G；疗，*7行；记

119.88994.063

12”,2S8119.891

1321347213.9M

14irw5

1519,443

1631.268

17DPCA3.078

18GOG6,277

19不济8.355

20GGG1.7802.0562.0S61.71432142.0221.68520.659

21产品HPQR8764326451.885131.8M1.8S411.143

22禧2.6662.4M2,7013,69932273,8763,539r31.802

23…RWQQ000000000

24769：词645!—，理,二或_4.3211876：876)21(10,293

252769766454322134J218768762110.293

|4334%,291

261,30027.04771.55668,0666777453.61683.60082,42647.42572,328765.262

*2C25rl-i

28修改的公式o[333

图4

为什么要进行绝对定位？

如果range中未进行绝对定位“$B$2:$B$25"，只选择为“B2:B25"，则在进

行公式拖拽后其他单元格值显示错误。

修政的公式(1)|91,3刈39.291161Mm27。*|71.闲⑴为绝对定位，⑵为相对定位，错误原因：相对定

位情况下，在第一个单元格内是公式range参数为:

汁113M3ml"27

B2:B25,拖拽到第二个单元格后，参数变为：C2:C25,

售改的公式(2)sa，|o|o|。！。|以此类推，计算区域不停变换。

图5

请打开《课前测验与练习之2》

“销售”中数据很多，如果仅仅是根据一个条件进行求和，比如单独按“大

区”或“省份”分类，进行“本月指标”统计，可以使用“SUMIF”函数，但如

果既要根据“大区”分类、又要根据“省份”、“性质”等要求分类进行统计，则

需要使用“SUMIFS”函数。

SUMIFS函数

作用：对区域中满足多个条件的单元格求和。

图6

公式及各参数意义：

SUMIFS(sum__range,criteria__rangel,criterial,[criteria_range2,criteria2],...)

Sum」ange：五需。对一个最多个单元格球和，包括薮值或包含数值的名称、区

域或单元格引用。忽略空白和文本值。

Criteria_rangel：必需。在其中计算关联条件的第一一区域。

Criterial：必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义

将对criteria_rangel参数中的哪些单元格求和。

可选。附加的区域及其关联条件。

Criteria_range2,criteria2,...

如何根据“销售”表单数据，求“性质大区统计表”中的辽宁省本月指标？

/AB|CDEFGH1

1性质大区门店数省份本月指标实际销售金额完成率毛利率销售成本

2东北11辽宁11

3北京

4华北16天津

5山东

6福建

7江苏

华东82

上海

8自营

9浙江

10华南10广东

11湖北

华中10

12江西

13西北7陕西

14四川

西南14

15重庆

正自营合计15000'#DIV/0!'#DIV/0!0

图7

分析：根据前几列条件：该问题可以转换为“在自营条件下，东北区辽宁省的

11家门店本月指标和为多少”求解。

对“销售”表单进行筛选，可以求和得出该值为：1390000c

SUMIFS▼(、X✓A=SUM(G2:G12)|I

AABCDEFGH

1店号▼大区“省份”所在#▼性质▼店名▼本月指标，实际销售金1

匕

2DL001家乐福新华10000057062.00

匕

3DL002家乐福中山150000130192.50

匕

DL004友好乐购12000086772.00

4匕

5SYOO1家乐福金牛

匕160000103890.00

6SY002沈阳广宜乐曲160000107766.00

匕

7SY004家乐福百联9000057502.00

匕

亘SY005家乐福北站店160000116300.00

匕

SY006家乐福吉祥11000063287.00

亘匕

SY007皇姑乐购120000112345.00

10匕

家乐福塔湾店

11SY008匕10000080036.00

12SY009华府港铁120000.73686.50

211=SUM(G2:G12)

212■该值为1390000

图8

使用“SUMIFS”函数求和，操作步骤如下：

1）打开“SUMIFS”函数;

2）在“sumjange”中选择“销售”表单中的“本月指标”歹限

3）在“criteria_rangel”选项中选择筛选条件之一，根据本题要求，应选中“销

售”中的''性质”列,此时跳出“criterial”和“criteriarange?w选项；

4)在“criteria/中输入筛选条件："自营",引号为英文格式；

5)由于还需要判断省份，因此需在“criteria」ange2”选项中，选择“销售”中

的“省份”列，并在“criteria2”中输入筛诬条件："辽宁"，引号为英文格式；

6)确定完成，结果为1390000c

ABCDEFGHiF

1性质大区门店数省份邛禺殳实际销售金额完成率毛利率销售成本

~2]I东北I11I辽宁辽宁")

III1

4

5

6

10

11

12

13

14

15

16至度查

17

18

19

20

图9

保护工作表

很多工作表中，有许多单元格为专人设计，如标题、带公式的单元格等，这

些单元格是不需要其他人输入的，因此在正常的使用中需要将这些单元格保护起

来，防止误输破坏其他数据。

结合《课前测验与练习之1》例子，保护工作表，操作步骤如下：

1)单击最左上角，选中整个工作表；

2)打开“设置单元格格式”对话框；

打开方式：

(1)选中整个表格后右键选择“设置单元格格式”；

(2)点击“开始”功能块下随意块右下角箭头；

SA会布局也蜘

ABCDEF

图10

3)选择“保护”对话框，取消“锁定”状态，确定;

图11

4)打开“定位”对话框(F5),选择“定位条件”;

14337G7二-inQMQ44QQ15719F72,860

r

166856601448573■1813227377425109,635

rF22,631*2kr

18,5017,03411,760,7,3定位:23,144182,495

F

9,2842,711—n13,6371,6215,95262,467

F

19,22610,5942,22175212,60667,243

rr

28,510’o13,305’15,858’2,373’18,558129,715

00000or0

17,18413,28513,608718,49929419,757L157,380

rrr

亿184y13,285’13,608,718,49929419,757157,380

F

9.88913,762n13.79814,2562,43394,063

11,25814,6371,75014,8—__।4,99218,7521,789’119,891

r与IE】乂亘rr

21,14714,63715,512,14,818,79033,0084,222”213,954

F

1,9207681—1,4981,49863211,825

1,798’

2.3002.3041,1522,3li□Izz1,79819.443

T走确定取消rr

4,220’2,304,1,920’2,31,498’3,2962,43031,268

76865764-w-ISCE15Z15066512r3,078

r

485431929871924323054323058973006455,277

卜Frrrrrr

8166082681,041384,5901,070584,3261,047’965657-8,355

806542,5926452,1601,7802.0S62,0561,7143,2142,0221,685’20,659

1,7921,7928764326451,885131,8541,854’11,143

rrrrrr

80,6544,3846453,952’2,656’2,488’2,701'3,5993,2273,876’3,53931,802

图12

5）在“定位条件”对话框内选择“公式”，确定;

产品客户1月2月3月4月5月6月7月8月9月10月"月12月年叟合才

OGG4337275575610.86316.618329,8494.49915,719"72360

产品APQR16.6856.6014.4857.3698.5265.1187.66311.92912,96518,1322.7377.42$r109,635

小诃I18,501’7.034r11.760,7.369r14282,15,981r24,281'11,961r22,814r22,631’2.737r23.144r182.495

BBB9.284271127207.9144,22911.8172,58213,6371.6215.952，62.467

r

产品BPQR19.22610.5948.5677671324.9W7.3992.22175212.60667t248

小计'28,5100’13,305r0,11.287r8,681r4,361,16,801r9,981,16,858’2.373’18,568*1M.715

BBB00000000000oro

产品CGGG17,1841328513.60876719.60717.94013.6839.15513,60118.49929419.757'157*

rKrT

小青17,184’13,28613.60876产19,607**17,94013,6839,15613,60118,499’豕’19,757*157J80

RQQ9.88913,7629.6313.7378.84917,70813,79814^562,433r94,063

产品DGGG11,2