Excel 2010财务与会计实战应用宝典

第1篇 Excel——高效的表格与数据处理软件

01

Chapter

快速制作专业报表 ·····················2

1.1 自动输入常用信息 ·············································· 3

1.1.1 快速输入相同的数据 ·····································4

1.1.2 快速输入序列················································5

财会应用 快速为会计科目编号 ··························7

1.2 制作精美的表格 ·················································· 9

1.2.1 快速画边框 ·················································10

1.2.2 自定义表格边框 ·········································· 11

1.2.3 两栏式表头的制作 ······································12

1.3 样式的快速套用 ················································ 13

1.3.1 套用内置的样式 ··········································14

1.3.2 新建样式为模板 ··········································15

1.4 在分析数据时使用色彩 ····································· 17

1.4.1 条件格式中色彩的应用 ·······························18

财会应用 使用图标集快速检测利润最高/

最低涨幅 ··········································19

1.4.2 排序和筛选中色彩的使用····························20

财会应用 用红色标记预算超支的数据 ·············20

1.5 快速查找数据 ··················································· 22

1.5.1 使用“搜索”框筛选数据····························22

1.5.2 利用表格实现不考虑位置的数据筛选··········23

用数据有效性设计表单 ···········25

2.1 数据有效性的设置 ············································ 26

2.1.1 设置数据有效性允许的条件 ························27

财会应用 防止输入周末日期 ···························29

2.1.2 设置提示信息··············································32

2.1.3 设置出错警告··············································33

财会应用 限制重复录入科目编号 ····················34

财会应用 制作月份下拉列表 ···························36

2.2 定位、复制和删除数据有效性 ·························· 37

2.2.1 定位到包含数据有效性的单元格 ·················38

2.2.2 复制数据有效性 ··········································40

2.2.3 删除数据有效性 ··········································41

2.3 数据有效性如何使工作变得高效 ······················ 42

2.3.1 制作下拉列表减少手工录入操作 ·················43

2.3.2 创建关联列表··············································44

2.3.3 快速校验数据清单中无效数据 ····················46

财务人员的帮手——

公式与函数 ·····························47

3.1 认识公式 ·························································· 48

3.1.1 公式的定义 ·················································49

3.1.2 公式的输入、编辑与复制····························49

3.2 方便的引用 ······················································· 53

3.2.1 A1和R1C1引用样式 ····································54

3.2.2 单元格的相对和绝对引用····························55

3.2.3 引用其他工作区域 ······································57

3.3 认识名称 ·························································· 60

3.3.1 名称引用的优点 ··········································60

3.3.2 定义名称的方法 ··········································61

财会应用 快速定义区域名称 ···························62

3.3.3 名称的应用使引用变得更有效 ····················62

3.4 关于函数 ·························································· 65

3.4.1 函数的定义 ·················································66

3.4.2 财务函数 ·····················································66

3.4.3 函数的输入与编辑 ······································67

3.4.4 嵌套函数的使用 ··········································70

财会应用 对应收款余额计算公式分步求值 ······72

本书针对财务与会计从业人员所需求的各种数据统计、表格制作、报表设计和图表展示，汇编出最实
用、最贴近实际工作的知识和技巧，全面系统地介绍了Excel的技术特点和应用方法，深入揭示隐藏于高
效办公背后的原理概念，并配合大量典型的应用实例，帮助读者全面掌握Excel在财务与会计工作中的应
用技术。
本书以解决实际工作中的常见问题为目标，不仅分专题详解Excel的应用，还设计了经验分享、常见
问题、专家点拨和财会应用等单元，帮助读者更快更好地理解内容、抓住精髓，从而胜任工作。全书共分
为 3 篇 17 章。第 1 篇为基础篇，内容包括快速制作专业的报表，用数据有效性设计表单，公式与函数
的知识与应用，用数据透视表快速重组数据，快速生成令老板满意的图表以及用规划求解与方案管理分析
数据等6个章节。 第2篇为实例篇，介绍如何使用Excel解决财会工作中的常见问题，包括财务工作常
用单据设计，凭证填制、账簿登记与总账处理，往来账款核算与管理，会计报表的编制，工资核算，固定
资产核算，财务预测，投资决策，筹资决策和成本费用分析等10个章节。第3篇为软件的协同篇，介绍
如何实现Excel与Access、PowerPoint以及文本格式数据的协同使用。
本书配套的CD光盘内容极其丰富，除了包含所有实例的原始文件和可直接套用的最终文件外，还有
播放时间长达432分钟的79个重点操作实例的视频教学录像，具有极高的学习价值和使用价值。