Axis title

EXCEL 在工作中的应用

制表、数据处理及宏应用

此文仅属朱永明个人所有， 未经本人的书面许可，其它任何机 构或个人不得擅自传阅、引用或复制。

本次讨论的范围

说明

EXCEL 的高级应用 如何提高数 据处理的效率

如何提高表格编制 速度

如何制做漂亮的表格

•

•

•

•

•

•

•

•

•

如何制作漂亮的表格

•说明

EXCEL 的高级应用 如何提高数 据处理的效率

如何提高表格编制 速度

如何制做漂亮的表格

•

•

•

•

•

•

•

•

•

专业人士制作的电子表格

存货盘点表

说明:

***产品分为普通装和礼品装, 普通装每件20小盒, 礼品装每件10大盒, 折合20小盒.

存货的帐面记录是以"小盒"为单位.

以下所有资料来自于03.6.30仓库清单, 见<5490>系列. 无重大盘点差异.

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 <5490>M系列 500 20 10,000 112 20 2,248 2,248

长春库 253 20 5,060 352 20 7,040 7,040

西安库 325 20 6,500 38 20 760 760

马山库 20 20 400 3,864 20 77,270 77,270

上海库 <5490-Sh>M 31 20 620 21 20 420 420

武汉库 <5491-Wu>M 71 20 1,416 1 20 20 20

在途商品

-总库到分库 <5490-Transit-1>M 38 20 760 15 20 300 300 -分库到销售办事处 <5490-Transit-2>M 22 20 440 13 20 260 260

Total 25,196 88,318 88,318

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30存货的仓库清单.

在途商品, 未经盘点, 但是已经取得03.6.30的存货清单, 由于数量较小, 加以忽略.

普通装 礼品装

如果换作我们来 做这张表,会是怎 样?

德勤审计工作底稿

• ^{整张报表很少用表格线} 却非常清晰、明了

• ^{作为一个局外人我们可} 以在最短的时间了解报表 的内容和所要表达的意图

我们作的表格一般是这样的

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 112 20 2,248 13,697

长春库 20 20

西安库 2 20 40 3 20 60 100

马山库 20 20 400 3,864 20 77,270 178,967

上海库 20 20

武汉库 71 20 1,416 1 20 20 1,436

在途商品 -总库到分库

20 20

-分库到销售办事处 _{20 20}

Total 11,856 79,598 194,200

说明:

***产品分为普通装和礼品装, 普通装每件20小盒, 礼品装每件10大盒, 折合20小盒.

存货的帐面记录是以"小盒"为单位.

以下所有资料来自于03.6.30仓库清单, 见<5490>系列. 无重大盘点差异.

存货盘点表

普通装 礼品装

在途商品, 未经盘 点, 但是已经取得 03.6.30的存货清 单, 由于数量较小,

加以忽略.

通过以上我方做的03.6.30存货毁损汇总和客户做的存货毁损汇总比较, 确认两者基本一致, 所以我们认为客户做的 存货毁损汇总是可靠的.

类别 仓库名称

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30 存货的仓库清单.

按照我们的习惯 做出的表格一般 是这样的.

同专业人士作的

比有什么区别吗?

与我们的表相比，专业人士的报表格式更清晰简捷

存货盘点表

说明:

***产品分为普通装和礼品装, 普通装每件20小盒, 礼品装每件10大盒, 折合20小盒.

存货的帐面记录是以"小盒"为单位.

以下所有资料来自于03.6.30仓库清单, 见<5490>系列. 无重大盘点差异.

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 <5490>M系列 500 20 10,000 112 20 2,248 2,248

长春库 253 20 5,060 352 20 7,040 7,040

西安库 325 20 6,500 38 20 760 760

马山库 20 20 400 3,864 20 77,270 77,270

上海库 <5490-Sh>M 31 20 620 21 20 420 420

武汉库 <5491-Wu>M 71 20 1,416 1 20 20 20

在途商品

-总库到分库 <5490-Transit-1>M 38 20 760 15 20 300 300 -分库到销售办事处 <5490-Transit-2>M 22 20 440 13 20 260 260

Total 25,196 88,318 88,318

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30存货的仓库清单.

在途商品, 未经盘点, 但是已经取得03.6.30的存货清单, 由于数量较小, 加以忽略.

通过以上我方做的03.6.30存货毁损汇总和客户做的存货毁损汇总比较, 确认两者基本一致, 所以我们认为客户做的存货毁损 汇总是可靠的.

普通装 礼品装

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 112 20 2,248 13,697

长春库 20 20

西安库 2 20 40 3 20 60 100

马山库 20 20 400 3,864 20 77,270 178,967

上海库 20 20

武汉库 71 20 1,416 1 20 20 1,436

在途商品

-总库到分库 20 20

-分库到销售办事处 20 20

Total 11,856 79,598 194,200

说明:

存货盘点表

普通装 礼品装

在途商品, 未经盘 点, 但是已经取得 03.6.30的存货清 单, 由于数量较小,

加以忽略.

类别 仓库名称

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30 存货的仓库清单.

即使看不清表格的内 容,我们依然能够清楚 的了解报表的层次结 构。

而这张表我们看到了

什么呢。象蜘蛛网一

样的表格线，没有规

律的空间分隔，我们

不知该从何处看起。

从报表内容和结构来看，专业人士的表格同样清晰

存货盘点表

说明:

***产品分为普通装和礼品装, 普通装每件20小盒, 礼品装每件10大盒, 折合20小盒.

存货的帐面记录是以"小盒"为单位.

以下所有资料来自于03.6.30仓库清单, 见<5490>系列. 无重大盘点差异.

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 <5490>M系列 500 20 10,000 112 20 2,248 2,248

长春库 253 20 5,060 352 20 7,040 7,040

西安库 325 20 6,500 38 20 760 760

马山库 20 20 400 3,864 20 77,270 77,270

上海库 <5490-Sh>M 31 20 620 21 20 420 420

武汉库 <5491-Wu>M 71 20 1,416 1 20 20 20

在途商品

-总库到分库 <5490-Transit-1>M 38 20 760 15 20 300 300 -分库到销售办事处 <5490-Transit-2>M 22 20 440 13 20 260 260

Total 25,196 88,318 88,318

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30存货的仓库清单.

在途商品, 未经盘点, 但是已经取得03.6.30的存货清单, 由于数量较小, 加以忽略.

通过以上我方做的03.6.30存货毁损汇总和客户做的存货毁损汇总比较, 确认两者基本一致, 所以我们认为客户做的存货毁 损汇总是可靠的.

普通装 礼品装

表头及说明区域

数据区域

需要着重表示的数 据,也是本表所反应 的焦点

对项目进 行解释 对项目进 行分类

对本报表进行总结

我们来分析一下,如何看报表的

存货表

项目 数量 单价 金额 … 合计

上海库

30 10.2 306 450

无锡库

30 10.2 306 450

济南库

30 10.2 306 450

马山库

30 10.2 306 450

… 合计

… 3,255 说明:脑白金产品分为普通装和礼品装, 普通装每 件20小盒, 礼品装每件10大盒, 折合20小盒. 存货表

30 10.2 306 450

无锡库

30 10.2 306 450

济南库

30 10.2 306 450

马山库

30 10.2 306 450

…

合计

… 3,255

说明:脑白金产品分为普通装和礼品装, 普通装每 件20小盒, 礼品装每件10大盒, 折合20小盒.

视角

先上后下，先左后右

对于一张报表,一般是从左上角 向右下角看.

先看特殊，后看一般

一些特珠的标记会影响我们的 视线,使之先看标记部分.比如 字体大于或异于其他部分,间隔 ,下划线等

先大后小

最后看到的是位于下方的小字 体内容.

结论

制作漂亮的表格1

第一步:在空白表格中录入数据

不用考虑格式、公式等 问题，将需要的内容录 入表中。

存货盘点表

合计 件数 盒/件 盒数 件数 盒/件 盒数 盒数 济南库 500 20 10,000 22 20 440 10,440 长春库 350 20 7,000 352 20 7,040 14,040 西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280 武汉库 71 20 1,416 12 20 240 1,656 在途商品 18 20 360 33 20 660 1,020 -总库到分库 55 20 1,100 182 20 3,640 4,740 -分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

如有经验，在录入时即 可同时考虑到整体格式 的布局。

制作漂亮的表格2

第二步: 根据项目类别将不同项目间距扩大，相同项目间距缩小

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440

长春库 350 20 7,000 352 20 7,040 14,040

西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 55 20 1,100 182 20 3,640 4,740

-分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

表名同表体扩大间距 不同分类项目的行间距 间距扩大

不同项目的列间距扩大

如果行列项目无法分类

，间隔之间须扩大距离

也可以用表格线区分项 目类别

制作漂亮的表格21

间距大小为什么这么重要？

先上后下，先左后右：对于 一张报表,一般是从左上角向

右下角看.

先看特殊，后看一般：在这 一过程中一些特珠的标记会 影响我们的视线,使之先看标 记部分.比如字体大于或异于 其他部分,间隔,下划线等 先大后小：最后看到的是字 体小、位于下方的文字.

结论：依据视线的特点,我们 应当将最重要的放在左上角, 对于报表的核心数据应用下 划线标出,把不重要的放在最 下面。

先上后下，先左后右：对于一张报 表,一般是从左上角向右下角看.

先看特殊，后看一般：在这一过程 中一些特珠的标记会影响我们的视 线,使之先看标记部分.比如字体大 于或异于其他部分,间隔,下划线等

先大后小：最后看到的是字体小、

位于下方的文字.

结论：依据视线的特点,我们应当 将最重要的放在左上角,对于报表 的核心数据应用下划线标出,把不 重要的放在最下面。

A 行间距相等的一段文字 B 行间距不等的一段文字

A、B文档对比 与A文档相比，B 文档段落清晰，

便于阅读、理解

。

文字越多的文档

间距的重要性越

突出。

制作漂亮的表格3

第三步：对于重点区域用不同的符号进行标注

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440

长春库 350 20 7,000 352 20 7,040 14,040

西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 55 20 1,100 182 20 3,640 4,740

-分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

重点区域的标注方式：

1、下划线或上下划线 2、字体大小或字型 3、前景或背景颜色

…

注意：

无论采用哪种方式标注 重点或特殊区域，不能 过多或过乱，影响整体 济南库

长春库 西安库 马山库 上海库 武汉库 在途商品 -总部到分库 -分库到销售办

下划线 背景色

制作漂亮的表格31

过多的标注结果是怎样的？

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440

长春库 350 20 7,000 352 20 7,040 14,040

西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 55 20 1,100 182 20 3,640 4,740

-分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440

长春库 350 20 7,000 352 20 7,040 14,040

西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 55 20 1,100 182 20 3,640 4,740

-分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440 长春库 350 20 7,000 352 20 7,040 14,040 西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 55 20 1,100 182 20 3,640 4,740 -分库到销售办事处 22 20 440 33 20 660 1,100

Total 26,416 25,040 51,456

普通装 礼品装

过多的标注不但无法起到画龙点晴的 作用，还会使整张报表变得复杂。

制作漂亮的表格4

第四步 : 设置单元格式

存货盘点表

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 500 20 10,000 22 20 440 10,440

长春库 350 20 7,000 352 20 7,040 14,040

西安库 253 20 5,060 223 20 4,460 9,520

马山库 20 20 400 13 20 260 660

上海库 32 20 640 382 20 7,640 8,280

武汉库 71 20 1,416 12 20 240 1,656

在途商品 18 20 360 33 20 660 1,020

-总库到分库 ^{55 20 1,100 182 20 3,640 4,740}

-分库到销售办事处 ^{22 20 440 33 20 660 1,100}

Total 26,416 25,040 51,456

普通装 礼品装

标题应使用较大的字体 不重要的附注信息使用 小字体

表体部分使用统一字体

，形成固定的风格。

数字要使用数字格式，

并用千分位符。

建议英文及数字使用

“Arial”字体，中文使用“

宋体”

建议正文使用9号字体

制作漂亮的表格41

不同字型、字体对比

20号 宋体 隶书 楷体 黑体

16号 宋体 隶书 楷体 黑体

14号 宋体 隶书 楷体 黑体

12 号 宋体 隶书 楷体 黑体

11 号 宋体 隶书 楷体 黑体

10 号 宋体 隶书 楷体 黑体

9号

8号

“宋体” 较为严谨，更适于报表正文使用。从电 脑的显示系统来看，该字体显示也最清晰。

“黑体” 较为庄重，可以用于标题或需特别强调 的区域。

“隶书” 和 “楷体” 源于书法，有一定的艺术特 征，不太适用于报表体系。

在字体的选择上

正文字体在能够看清楚的基础上，尽量选用小 字体。一方面可以增加单位面积反映的内容，

同时也不影响整体效果。

标题字体一般应大于正文字体一倍左右，保证 明显区别于正文，但不易过大，以免影响整体

字型 字体

制作漂亮的表格42

单元格式设置注意事项

仅从单元格式上隐去小数点,并不表示取整 ,错误的理解可能导致数据计算的误差.

1233.22

1,233

<>

通过格式设置后隐去小数点 1,233.00

123445666 123,445,666 数字格式设置千分位，使得更好识别 同样字号,数字会显得更大,建议数字较 文字设置小一号

注意设置单元格式的功能运用

文本型

日期 特殊

自定义

如果要录入“0325332”最好将单元格设置成文 本型,否则首位0丢失.

如果单元格设置为文本型用公式求和是得不 到值的.

05-1-1,2004年1月1日,…

中文大写数字格式 : 参佰…

邮格式 : 000632

自动设置各类录入格式 : 04年3月,04年03月 自动设置计量单位 : 29盒,5件

制作漂亮的表格5

第五步 : 插入标注和说明

存货盘点表

说明:

***产品分为普通装和礼品装, 普通装每件20小盒, 礼品装每件10大盒, 折合20小盒.

存货的帐面记录是以"小盒"为单位.

以下所有资料来自于03.6.30仓库清单, 见<5490>系列. 无重大盘点差异.

合计

件数 盒/件 盒数 件数 盒/件 盒数 盒数

济南库 <5490>M系列 500 20 10,000 112 20 2,248 2,248

长春库 253 20 5,060 352 20 7,040 7,040

西安库 325 20 6,500 38 20 760 760

马山库 20 20 400 3,864 20 77,270 77,270

上海库 <5490-Sh>M 31 20 620 21 20 420 420

武汉库 <5491-Wu>M 71 20 1,416 1 20 20 20

在途商品

-总库到分库 <5490-Transit-1>M 38 20 760 15 20 300 300 -分库到销售办事处 <5490-Transit-2>M ^{22 20 440 13 20 260 260}

Total 25,196 88,318 88,318

主要仓库, 已经盘点, 无重大差异.

零星仓库, 未经盘点, 但是已经取得03.6.30存货的仓库清单.

在途商品, 未经盘点, 但是已经取得03.6.30的存货清单, 由于数量较小, 加以忽略.

通过以上我方做的03.6.30存货毁损汇总和客户做的存货毁损汇总比较, 确认两者基本一致, 所以我们认为客户做的存货毁 损汇总是可靠的.对于这些存货, 建议在合并中, 在抵消全部内部销售利润的基础上, 按照无锡工厂的生产成本价转销(作为 营业外支出).

普通装 礼品装

说明的几种方式

1、通过特殊的文字或序号 标注,并在报表的下端或通 过其他文件对项目进行解 释

2、与正文对应位置的项目 进行解释可通过设置相同的 背景色或其他格式

3、直接的文字说明

资产负债表

项目 年初数 年末数 …

银行存款 a1 应收帐款 a2 存货 a3

…

合计

a1 应收帐款余额表

客户 期初数 本期 …

客户A b1 客户B b2 客户C b3

…

合计

制作漂亮的表格51

合理的使用标注，使报表更易读懂

利用标注，可以快速的查询报表的每项 数据，直至了解其来源

b3 客户C应收帐款明细帐

客户 期初数 本期 …

客户A c1 客户B c2 客户C c3

…

合计

b2 客户B应收帐款明细帐

客户 期初数 本期 …

客户A c1 客户B c2 客户C c3

…

合计

b1 客户A应收帐款明细帐

客户 借方 贷方 …

1月1日 销售 1月2日 回款 1月1日 销售

…

制作漂亮的表格52

编制报表时少用批注

广告计划

项目 计划 实际 完成率 电视 310 310 100%

报纸 300 330 110%

促销 200 220 110%

… 合计

计划300，

后又追加 10

计划300，

后又追加 10

使用批注

显示批注

隐藏批注 降低报表可读性，不易于理解 影响报表的整体外观

推荐使用 广告计划

项目 计划 实际 完成率 电视 310 (a) 310 100%

报纸 300 330 110%

促销 200 220 110%

… 合计

(a) 原计划300，后追加100

广告计划

变更

项目 计划 变更 后计划 实际 完成率 电视 300 10 310 310 100%

报纸 300 300 330 110%

促销 200 200 220 110%

…

合计

漂亮的报表

制作漂亮的表格6

第六步 : 设置打印格式

设置打印比例，只要能看清，尽量打在一张表上 设置固定打印行或列，勿需在表中多次插入首行、列

设置单色打印，屏蔽打印背景色 利用模拟显示，调整行列边距

设置页眉页角，标出打印日期、版本号、页号等

设置打印区间，屏蔽不需打印的内容

制作漂亮的表格

回顾！

录入文字

拉开间距

设置表格线

设置单元格

说明事项

设置打印格式

设置密码、保存文件、设

置版本号

如何提高数据录入及处理的速度

说明

EXCEL 的高级应用 如何提高数 据处理的效率

如何提高表格编制 速度

如何制做漂亮的表格

•

•

•

•

•

•

•

•

•

提高数据录入及处理速度的几种方法

‰ 利用快捷键

‰ 应用函数

‰ 应用模板

利用快捷键提高录入速度

‰ 利用快捷键

‰ 应用函数

‰ 应用模板

25

使用快捷键

操纵菜单 选择区域

编辑区域

调用程序

Alt+菜单名后的（字母）

Ctrl+End [ Home . End . Page up .page Down]

Alt + →[ ↑.↓.← ] Ctrl + →[ ↑.↓.← ]

F2、F3、F4、F5

Ctrl+S [ C.V.X.P]

用键盘操作的优点

• 定位准确

• 节省时间

• 便于操作

详见<EXCEL快捷键大全.doc>

应用函数

‰ 利用快捷键

‰ 应用函数

‰ 应用模板

对函数的理解

数据 规则 结果

函数

A3=200 IF(A3>100,40,0)

D3

D3=40

函数的作用 ：将指定的数据按照一定的规则转化为需要的结果

函数分类及使用

菜单-插入-函数

这里有详细的功能及使用 说明，可以参照说明学习 使用。

这里是详细的函数分类和

名称。其中常用函数、文

本函数、日期函数、逻辑

函数应用较广。

函数应用需掌握的基本技能

A2 A2,A3 A2,A3:A4

A2,A3:B4,C:D

‘SHEET1’!A2,A3:A4

‘SHEET1:SHEET2’!A3:A4

[C:\月报表]!SHEET1!A2,A3:A4 区域选择

+、-、*、/、>、<、<>、!、

计算帐号

$A$3

特殊应用

函数的使用方法

=IF ( logical_test,value_if_true,value_if_false)

这是Excel帮助中对IF函数的解释

执行真假值判断，并根据指定条件进行逻辑判断评价的不同结果返回不同值

函数名 对目标单元格进行判断 符合条件的值 不符合条件的值

每个函数都会有一个函数名 均有一对括号将参数括起 参数间用逗号分隔

在单元格内等号作为函数的开始 函数可以进行嵌套

参数（可以是公式、函数、值）

函数的应用1

D2=C2/B2

…

…

公式

如何消除公式中的特殊符号?

A B C D

1 产品 数量 金额 平均单价

2 脑白金 250 32000 128 3 黄金搭档儿童型 230 32000 139

4 黄金搭档女士型 #DIV/0!

下面是一张简单的销售统计表，表中平均单价是事先编辑的公式

D2=IF(B2=0 , 0 , C2/B2)

IF 函数

函数的应用1续

下面是一张简单的销售统计表，表中平均单价是事先编辑的公式

如果B2=0

那么D2=0

否则D2=C2/B2

A B C D

1 产品 数量 金额 平均单价

2 脑白金 250 32000 128

3 黄金搭档儿童型 230 32000 139

4 黄金搭档女士型

C2=IF((B2-800<0) , 0 , IF(…) )

函数的应用1续

下面是一张简易的工资表，用IF函数嵌套使用计算个人所得税

A B C D

1 产品 应发工资 扣税 税后工资

2 王** 1500 10 1,490

3 李** 1800 25 1,775

4 张** 3200 215 2,985

IF((B2-800<500),(B2-800)*5%, IF(…) ) IF(…)

IF(…)

IF((B2-800<2000),(B2-800)*10%-25, IF(…) ) IF(…)

…七层

函数的应用2

广告费用明细表

媒体 类别 摘要 金额 … 无锡日报 报纸

无锡晚报 报纸 无锡有线 电视 无锡电视 报纸

…

合计 32,500

广告费用汇总表

媒体类别 金额 … 备注 报纸

电视 促销

… 合计

？

有一份媒体广告费用明细 表，现在想生成一张汇总 表，有什么办法？

通常做法

- 将明细表按类别排序 - 将明细表分类汇总

- 将汇总数据录入汇总表

函数的应用2续

广告费用明细表

媒体 类别 摘要 金额 … 无锡日报 报纸

无锡晚报 报纸 无锡有线 电视 无锡电视 报纸

…

合计

广告费用汇总表

媒体类别 金额 … 备注 报纸 =sumif(…) 电视 =sumif(…) 促销 =sumif(…)

… 合计

SUMIF函数

在汇总表中使用该函数，汇总广告费 用明细表中满足一定条件的数据

=SUMIF(广告费用明细表!C4:C34,"=报纸",E4:E34)

表名, 用!与区域相连

类别区域

类别满足条件

金额区域

函数的应用3

1月广告费用明细表

媒体 类别 摘要 金额 … 无锡日报 报纸

无锡晚报 报纸 无锡有线 电视 无锡电视 报纸

…

合计 32,500

2月广告费用明细表

媒体 类别 摘要 金额 … 无锡日报 报纸

无锡有线 电视 无锡电视 报纸

…

合计 33,200

3月广告费用明细表

媒体 类别 摘要 金额 … 无锡日报 报纸

无锡晨报 报纸 无锡晚报 报纸 无锡有线 电视 无锡电视 报纸

…

合计 32,500

2004年广告费用汇总表

媒体 1月 2月 3月 … 全年 无锡日报

无锡晨报 无锡晚报 无锡有线 无锡电视

…

现需根据有每月的各媒体广告费用 明细表，汇总单个媒体全年的广告 费用。但每月媒体的次序、数量均 不同，怎么操作？

…

函数的应用3续

一般的操作程序

将各月报表排序

将各月报表拷贝至一张表中

按照顺序插入行直至各月完全对应

使用数据库将各表建立关联

将月报表插入一列，加入月份，并 将各月报表拷入汇总表中排序

建议使用

VLOOKUP函数

搜索报表区域内满足条件

的单元格式，并返回选定

的单元格

函数的应用3续

汇总表 1月明细表

=VLOOKUP(A2,1月明细表!A2:B4,2,False)

需要查找的文字

需要查找的区域 包括返回值区域

返回值所在的查 找区域中的列数

False为大致查找 True为精确查找

A B

1

2

3

4

A B C D E

1

…

2

=VLOOKUP(A2,'1 月明细表'!A3:B4,2,FALSE) 3

4

函数的应用3续

详见F203

VLOOKUP应用的延伸

考虑一下，是否可以设计一种方法，录 入编码，名称就可以自动带出来。

经销商编码表

编码 名称

0510001 无锡三禾药业

0510002 宜兴玉女保健品公司

…

销售统计表

经销商编码 名称 数量 金额

0510001 0510002

录入编码，自动带出经 销商名称

录入编码，自动带出经

销商名称

函数的应用4

C2 =CONCATENATE(A2,”公司”,B2) C2 =无锡公司无锡日报

C3 =LEFT(C2,2) =无锡

C4 =RIGHT(C2,4) =无锡日报

文本操作函数

A B C

1

2

3

4

函数的应用回顾

IF

SUMIF

VLOOKUP

CONCATENATE

函数的应用关键在于了解函数的基本功能和使用方 法，并不断的应用

文本操作类 查找引用 计算统计

逻辑判断

MID

SUMPRODUCT NOW、DAY

NOT\OR

试一下，看看怎么用

今天研究的范围

应用模板

‰ 利用快捷键

‰ 应用函数

‰ 应用模板

保存模板 锁定报表

设置单元格式 设置公式

模板的作用编制程序

模板作用

编制程序

•提高表格编制速度

•规范表格格式

设计格式 按照正常报表 编制程序设计 报表格式.

设置表内公式 及表间公式

合理使用宏及 函数

添加表间连 接,方便选择 报表.

设置校验公式

利用名称定义将需 要录入的单元格式 定义名称

设置单元格背景色 ,使录入部分与锁 定部分颜色反差 将需录入的单元格 锁定解开

锁定工作表

锁定工作薄

共享工作薄

另存为文件

另存为模板

应用模板应掌握的技能

数据的有效性

数据-有效性

共享工作薄

工具-共享工作薄

工具-修订-突出显示修订

引用和连接

插入-超级链接

限制录入的区间或格式 建立下拉式菜单

根据不同区域切换中英文法 录入提示及出错提示

通过设置可实现多人同时操作同一表格式 通过设置可实现对每个人修改信息的查询

通过设置可实现报表、工作薄

应用模板应掌握的技能续

区域选择规则 A2

A2,A3 A2,A3:A4

A2,A3:B4,C:D

‘SHEET1’!A2,A3:A4

‘SHEET1:SHEET2’!A3:A4

应用公式

Sum、Average、count、min

定义名称

插入-名称-定义

Ctrl+G

快速选择单元格

如何提高数据分析的效率

说明

EXCEL 的高级应用 如何提高数 据处理的效率

如何提高表格编制 速度

如何制做漂亮的表格

•

•

•

•

•

•

•

•

•

如何处理超宽报表

利用分组技术将报表不同项目分割开，使 其根据需要自动伸缩

数据-组及分级显示-组合

冻结窗格

窗口-冻结窗格

将同一张报表分成不同的明细 表，并附汇总表

EXCEL报表最宽为255列，过宽的报表对内存的消耗较大。

a b c …

c b

a

a1 a2 a3 a4 … -

a b + +

如何处理超长报表

冻结窗格

使用筛选及分组技术 使用分类汇总技术

用表格线、行间距、不同色框将不同类 别或等距离区域从视觉角度分割开

EXCEL报表最多可达65555行。EXCEL的特点在于数据处理方便，分析功

三维或多维表格

回款统计表

办事处 黄金搭档 脑白金 … 合计 上海

苏州 南通 无锡 常州

… 合计

4月 3月

2月 1月

第一维（产品）

第二维（机构）

第三维（时间）

这是一套常见的报表体系，统计表内 容是分月编制的办事处回款，与之类 似的如分办事处的广告费用、经营费 用统计表。表中可以看到各办事处每 一种产品的回款数据

如果需求改变了，如何取得下列数据？

1、每个办事处各月的回款

2、各类产品每个月的回款

对三维报表的理解

时间 区

域

产 品

1月 2月

脑白金

黄金搭档 苏州

无锡

这是一个标准的三 维表格，从不同的 角度观察可以得到 不同的数据。

以月份为单位，各办 事处各产品的数据 以办事处为单位，

各月各产品的数据

如何处理三维表格

第一步 降低维度

4月 3月

2月

1月 1-3月

工作表的概念

每个单元格为是三维表的一条

数据库概念

每个单元格是三维表的最小颗粒

回款统计表

办事处 黄金搭档 脑白金 … 合计 上海

苏州 南通 无锡 常州

… 合计

回款统计表

办事处 产品类别 月份 … 合计 上海

苏州 南通 无锡 常州

… 合计

如何处理三维表格

第二步 使用数据透视表

1-3月

回款统计表

办事处 月份 产品类别 … 合计 上海

苏州 南通 无锡 常州

… 合计

数据透视表

数据-数据透视表和图表报告

如何处理三维表格

第三步 根据需要可以得到各种表

图表应用 ^线型图

38

69

97

132

170

320

39 52 67

84

107

136 228

14 10 23 0 50 100 150 200 250 300 350

1996 1997 1998 1999 2000 2001 2002E 2003E Worldwide

United States

用于一个或多个项

按照时间序列排列

的对比分图

图表应用 ^柱型图

•4

•7

•16

•25

•32

0 5 10 15 20 25 30 35

1998 1999 2000 2001 2002E

Axis title

Axis title

50 40

30 20

10 Label 1

Label 2 Label 3 Label 4 Label 5 Title

Unit of measure

一个或多个项目的对比

也可以按照时间序列排

列,但时间不易过长

图表应用 ^饼型图

应用于一个主体各组成 部分所占的比例

Category A 40%

Category B 24%

Category C 16%

Category D 9%

Category E 7%

Category F

4%

图表应用 ^{饼型图的延伸}

Category A 40%

Category B 24%

Category C 16%

Category D 9%

Category E 7%

Category F

4%

图表应用 ^分布图

-30%

-20%

-10%

0%

10%

20%

30%

40%

-20% -15% -10% -5% 0% 5% 10% 15% 20% 25%

反映样本分布

的图表

图表应用 ^注意要素

• 去除不必要的修饰

• 起一个可理解的名称

• 填加横轴和纵轴名称

• 一张图表不要反映过多的问题

黄金搭档

- 5,000 10,000 15,000 20,000 25,000 30,000 35,000

苏州 无锡 南京 南通 常州 淮安

黄金搭档 黄金搭档3月回款统计表

- 10,000 20,000 30,000 40,000 50,000

南京 淮安 南通 苏州 无锡 常州

办事处

回款额

EXCEL的高级应用

说明

EXCEL 的高级应用 如何提高数 据处理的效率

如何提高表格编制 速度

如何制做漂亮的表格

•

•

•

•

•

•

•

•

•

如何理解宏

录制宏 EXCEL根据操作编 译成一段程序代码

执行宏

VBA程序代码 VBA程序代码

调用程序代码自 动完成原录制的 工作

因此要想真正运用好,就应该掌握一些VBA的基本常识!

VBA概述

VBA 全称叫 VISUAL BASIC APPLICATION

它是VISUAL BASIC语言的简化版.

对于专业人员来说,它太简单,简直不屑一故

对于办公应用人员来说,它又太复杂,简直就是天书

VBA可以驱动EXCEL,执行手工操作的几乎所有内容

VBA并不复杂,不象其他的应用程序需要有专业背景.只需要掌握简 单的规则,并通过录制宏和简单的修改,就可编写一小段程序代码,并 大大的提高工作效率.

如果我们只满足手工操作EXCEL的所有功能，那我们处理一些重 复性的工作时就只能象机器人一样进行机械的操作。庆幸的是，

OFFICE的开发者们为大家准备了一个方法，那就是VBA

VB的核心:面象对象的应用程序

对象 属性 方法

Worksheet 工作薄 Sheet 工作表 Cell 单元格

Name 名称 Font 字体 Fomula 公式

Add 添加 Delete 删除 Clear 清除 Select 选择

Sheets(“sheet1”).name=“1月报表” 将SHEET1报表命名为“1月报表”

Cell(3,3).select 选择C3单元格

如何调用EXCEL表的对象

对象 选择一个对象 当前对象

工作薄 工作表 单元格

Windows(“工作薄名.xls”). Activate Sheets(“工作表名”).select

Range(“A2”).Select Cell(2,1).select

ActiveWorkbook Activesheet Activecell Selection

添加一个对象

Workbooks.Add Sheets.Add ---

能够认识VBA中对于EXCEL对象的命名规则,就可

以基本读懂程序代码,为我们驾御VBA奠定了基础

学习使用VBA

录制一段宏

查看程序代码,学 习VBA语言

认识VBA的语言 结构

工具-宏-录制新宏>手工操作>停止录制

Alt+F11或工具-宏-Visual basic 编辑器 查看代码 每个宏都是由一段VB代码组成的.

学习VBA可以通过“录制-查看”的过程不断学习

一段程序一般包括如下结构

DIM Mydim as string’分号后的文字为解释,不是程序语言 Sub 示例程序()

…

End sub

Funcation 自定义函数名称(…) as …

…

End sub

对于自定义函数,可以象标准函数一样,在程序中调用

对于程序需要调用宏来执行程序,宏名称就是SUB后跟随的文字.

VBA实例举例

Sub 新建一个工作表() Workbooks.add

Activeworkbook.name=“vba实战”

Activeworkbook.save End sub

‘--- Sub 插入一个工作表()

Sheets.add

Activesheet.name=“1月报表”

End sub

‘--- Function 乘方函数(num as interger) as interger

乘方函数 =Num*num End Function

‘--- Sub 自动编辑公式()

Range(“A1”).formula=“=B2+Sheet2!B2”

Range(“B2”).Value=321 Range(“c2”).Value=231

语法 If … then Else if

… Else

… End if

Do until …

… Loop

For I=1 to …

… Next

定义变量

DIM j as integer Dim Myline as string

讨 论