办公软件操作及实用03-Excel报表管理与数据分析

第三部分Excel报表管理与数据分析
Excel什么意思
Excel 的来历
Microsoft推出了它的第一款电子制表软件-Multiplan，但在当时的MS DOS应用领域，却败给了Lotus 1-2-3。

这个事件促使了Excel的诞生，比尔盖茨召集了微软最高的软件专家奋斗三年，研发出了Excel。

作为目前使用最广泛的电子制表软件，Excel 经历十多次版本升级之后，功能早已不局限于制表。

一、Excel主要功能和实用技巧
（一）熟悉Excel功能区
以报表管理的过程为导向，可以方便用户快速掌握Excel的操作界面和主要功能。

（一）熟悉Excel操作界面
“开始”功能选项卡区主要属于在制表的基础功能。

其中需要注意的是数据分类、表格样式和条件格式。

2.各个功能选项卡区---插入
“插入”功能选项卡区的按钮，主要是在表格中插入一个新的对象，这个对象可以是一个特殊的符号、艺术字或者特殊公式，又或者是一张汇总表，也可能是明细表，或者是一张插图、图表分析工具等等。

3.各个功能选项卡区---页面布局
“页面布局”主要设置纸质表格的主题风格和页面打印相关功能，例如纸张方向、打印区域、打印顶端标题行等等。

4.各个功能选项卡区---公式
“公式”功能选项卡区的功能，主要用于数据的运算。

需要注意各类函数库、如何定义名称以及监视窗口。

5.各个功能选项卡区---数据
“数据”功能选项卡，主要用于进行数据的导入、更新、数据的排序筛选以及各种数据分析。

重点掌握如何获取外部数据、高级筛选、分类汇总、以及数据有效性、合并计算、假设分析等数据工具的使用。

“审阅”，就是报表的审校和阅读，重点关注批注的使用，单元格区域、工作表、工作簿的保护。

7.各个功能选项卡区---视图
“视图”，顾名思义，就是看到的图像，这里指的是Excel工作区的显示方式。

常用的功能包括工作簿视图、冻结窗格、并排查看、切换窗口和宏。

8.各个功能选项卡区---工具
工具选项卡区，只有选中相应的对象时才会出现，比如表工具、图片工具、绘图工具、图表工具、数据透视表工具等等。

主要是该对象的属性设置和相关工具按钮。

各个功能选项卡区
（二）实用技巧
技巧一快速输入当前日期
进行数据记录时，常常需要录入当前日期。

技巧二快速输入当前时间
进行数据记录时，Ctrl+Shift+；可以录入当前小时、分、秒。

技巧三快速输入本年度任意一天
技巧四规范录入日期型数据
技巧五特殊格式的日期（一）
步骤：选中单元格→点鼠标右键→单元格格式→日期
技巧六特殊格式的日期（二）
步骤：选中单元格→点鼠标右键→单元格格式→自定义→yyyy.m.d 技巧七特殊格式的日期（三）
步骤：选中单元格→点鼠标右键→单元格格式→日期→星期三技巧八系列日期的快速填充
步骤：选中单元格→移动鼠标至右下角呈十字控制柄→拖拽技巧九日期的运算（一）
输入日期后，向上拖拽
技巧十日期的运算（二）
利用公式进行日期计算，后一日期减去前一日期，如果是当前日期，使用日期函数today（）学习思路日期的相关技巧思维导图
案例综合演示日期的技巧
技巧十一快速输入字符串
在数据录入过程中，直接在单元格中输入12位及其以上位数的数字后，系统会自动使用科学计数法；输入到16位以及以上数字时，后面的数字自动会变成0。

为了准确输入身份证号、银行卡号等信息，必须输入文本字符串，而不能输入数字。

技巧十二设置文字方向
步骤：选中单元格→点鼠标右键→单元格格式→对齐→方向
技巧十三自动换行
步骤：选中单元格→点鼠标右键→单元格格式→对齐→文本控制
技巧十四强制换行
Alt+Enter
技巧十五斜线与单元格分割
在制表过程中，如果单元格中添加一条斜线，通过单元格格式中的边框设置即可完成，如果两条或者两条以上斜线，通常使用形状来进行绘制。

技巧十六分列
步骤：选中单元格→数据→分列
技巧十七特殊字符
步骤：插入→特殊符号
技巧十八输入欧元符号
步骤：选中单元格→点鼠标右键→单元格格式→会计专用
技巧十九艺术字
步骤：插入→艺术字
技巧二十左中右自由取字符
LEFT/MID/RIGHT函数
学习思路文本的相关技巧思维导图
案例综合演示文本的技巧
技巧二十一利用超链接建立报表关联
步骤：选中单元格→点鼠标右键→超链接技巧二十二数据的隐身法
技巧二十三数据的导入
步骤：数据→获取外部数据
技巧二十四删除重复数据
步骤：数据→删除重复数据
技巧二十五查看公式，理清数据的来龙去脉
技巧二十六快速录入分数
步骤：先输入0和空格→然后输入分数
技巧二十七添加千分位符
步骤：选中单元格→单元格格式→数值
技巧二十八将数据转化为万
步骤：选中单元格→单元格格式→自定义→ 0!.0,"万"
技巧二十九奇妙的F4
在Excel中，通过按F4键，可以重复上一步操作；也可以将公式的引用方式进行切换。

技巧三十奇妙的F5
利用F5键的定位功能，可以对满足条件的单元格区域进行批量处理。

案例综合演示数据的技巧
二、Excel制表与美化
（一）熟悉Excel数据元素以及其编辑方式
案例演示：四大元素的编辑
（二）单元格样式与套用表格格式
单元格样式相当于将设置好单元格格式的表格作为模板，把模板中的单元格字体、对齐方式、背景色等格式，应用到目标单元格或单元格区域。

套用表格格式是指利用Excel 2007提供的各种表格格式，快速设置表格的标题、表格的边框以
及背景色等属性，从而实现表格的美观，并且能够保证同一类表格有统一的表现形式。

案例演示：单元格样式与套用表格格式
（三）条件格式
条件格式是指利用数据条、色阶、图标集等方式，显示报表中数据的分布状况，突出异常值。

案例演示：利用条件格式显示数据分布状况
（四）记录单
只有在存在报表的列标题之后，才能打开记录单。

这时候，记录单会按列标题来作为字段名称，用户按照字段来录入相应的数据。

注意：空白报表不能使用记录单，记录单需要自定义快速访问工具栏。

（五）选择性粘贴
选择性粘贴可以将选定并复制的内容，按照不同的方式有选择性地进行粘贴、运算或者转置。

（六）数据有效性
在财务表格中常常会出现各种财务项目，应用的数据类型和数据范围不同。

在Excel 2007中，通过对数据单元格的数据有效性进行设置，用户如果输入不符合输入条件的数据，系统会进行提示和报错。

通过限制输入条件，能够有限地减少数据录入的失误。

步骤：选中单元格区域→数据→数据有效性
如何设置数据有效性
数据有效性条件指的是允许录入数据的类型以及范围；
输入信息指的是数据录入时，鼠标滑过设定数据有效性单元格所显示的提示信息；
出错警告指的是当用户没有按照有效性条件输入数据时，弹出的错误信息。

案例演示数据有效性
圈释无效数据
对于尚未输入数据的单元格区域，设置数据限制录入条件可以减少数据录入的失误。

对于现有的数据，也就是已经录入完成的数据，我们同样可以利用数据有效性，将其中不符合有效性条件的数据找出来，并标注红色的圈；当数据修改正确后，红色的圈会自动消失。

案例演示圈释无效数据
（七）数据的保护
1.保护工作簿
保护工作簿是对工作簿的结构和窗口进行限制，防止其他用户进行工作表的编辑和窗口的缩放等操作。

步骤：审阅→保护工作簿
2.保护工作表
保护工作表可以防止其他人员对工作表进行更改。

在工作表被保护起来之后，工作表的单元格、行列都将不能被编辑。

只有输入正确的密码，才能解除工作表的保护状态。

3.保护单元格
单元格的保护主要是通过设定单元格是否锁定和隐藏公式，来设定用户对单元格中的数据的阅读和修改。

需要注意的是，单元格的保护必须和工作表的保护结合起来使用才能有效。

4.允许用户编辑区域
允许用户编辑区域是对选定的单元格进行加密，只有用户输入正确的密码之后，才能进行数据编辑。

步骤：审阅→允许用户编辑区域
案例综合演示数据的保护
Excel报表管理与数据分析
主要功能与实用技巧
制表与美化
图表分析工具
数据汇总与分析
公式和函数
三、Excel图表分析工具
图表是Excel重要的数据分析工具，可以直观地表现枯燥的数据信息，并反映各种财务数据的走向、趋势以及数据之间的差异，便于用户进行分析和处理。

图表分析工具是建立在Excel数据报表数据源基础上，根据具体财务分析目的，通过图形、数据轴、数据标签等元素，对数据情况进行呈现的工具。

步骤：插入→图表
（一）图表元素
图表元素就是构成图表的各个组成元素，通常包括了图表的标题、坐标轴、图例、数据标签、数据表、坐标轴标题、网格线以及趋势线等等。

图表元素
图表元素由图表标题、图例、坐标轴、数据标签等构成。

（二）图表类型
图表类型包括柱形图、条形图、折线图等11个大的类别，每个类别中又分为多个小的类别。

（三）图表特性和适用范围
选择何种类型的图表，我们必须要从自身进行数据分析的目的出发，在结合各个类型图表的功能和特点，选择最能够表现数据分析目的的图表。

图表特性和适用范围
（四）图表的绘制
使用图表形象化财务数据，是在选择需要进行分析的数据单元格区域后，利用Excel的图表功
能，选择“插入”功能选项卡下的图表工具，根据图表的特性和数据分析的目的，用适当的图表类型，并设计其图表样式和呈现效果，从而将数据特性用图形表现出来。

图表的绘制
案例演示：利用图表工具绘制柱形图
案例演示：利用图表工具绘制饼图
（五）图表的设计和美化
在创建好一个图表后，我们还可以通过使用图表工具，对图表显现形式进行编辑。

例如修改图表的布局，为图表添加数据标签，以及为图表添加趋势线、网格线等等。

案例演示：2 Y 轴的综合图表
（六）图表与Word、PPT的数据联动
在日常工作中，我们绘制完成的数据图表之后，很多时候，并不是将图表放置在Excel工作簿中，而是放置到Word文档或者PPT幻灯片报告中。

这时候，一定要注意保持Excel报表与Word文档或者PPT幻灯片报告数据联动。

一旦Excel报表数据发生变化，数据自动更新到Word文档或者PPT幻灯片报告中。

通过复制和粘贴，并建立链接
四、数据汇总与分析
数据的处理和分析管理是一个严谨的工作。

如果我们花费了大量的时间和人力物力，最终却获取的是一个错误的分析结果，不仅仅是资源的巨大浪费，而且会导致错误的决策和管理。

灵活使用数据分析工具，不仅仅能够快速了解数据的分布、汇总状况，更能够掌握重要数据的来龙去脉。

（一）数据的排序
Excel 2007为用户提供了将数据按照一定规律排序的功能。

用户可以使用升序或者降序的方式，将数据进行排序。

注意：当表格中存在合并单元格，并且合并单元格的大小不相同时，不能对表格中的数据进行排序。

步骤：数据→排序
案例演示：数据的排序（快速排序和自定义排序）
（二）数据的筛选
在数量记录繁多的报表中，我们常常需要将一些重要的数据由针对性地筛选出来，单独进行查阅和分析。

Excel 2007的数据筛选功能可以帮助大家，根据一定的条件，将这些满足条件的数据有选择地进行显示，同时隐藏其他不满足条件的数据。

步骤：数据→筛选
不同类型的筛选方式
对于不同类型的数据，筛选的方式也有所不同。

例如对于文本，Excel提供了文本筛选，可以按照包含文字进行筛序；对于日期，Excel提供了日期筛选，可以按照周、月份、季度、年等不同条件进行筛选。

高级筛选
对于多个条件的高级筛选，多个条件是否同时满足，涉及到“逻辑与”和“逻辑或”的相互关系。

“逻辑与”是多个条件同时满足，而“逻辑或”只需要满足多个条件之一就可以了。

案例演示：数据的筛选（快速筛选和高级筛选）
（三）数据的分类汇总
面对大量数据记录时，如何将数据进行分门别类地进行汇总分析，条理清晰地把握经济活动的运行状态？Excel 2007提供了将表格数据进行分类汇总的功能，用户可以对特定项目，灵活选择汇总方式以及需要汇总的数据字段。

步骤：数据→分类汇总
案例演示：数据的分类汇总
（四）数据透视
数据透视表是Excel中的一种交互式工作表，可以根据用户的需要，依照不同的关系数据来提
取、组织和分析数据。

步骤：插入→数据透视表
数据透视表的特性
数据透视表建立在明细数据记录基础之上。

集合了排序、筛选和分类汇总的功能，并生成汇总表格，集中体现了Excel强大的数据分析功能。

相对于Excel 2003，Excel 2007的透视表功能更加直观，也更便于用户对数据的提取和分析。

数据透视表的界面
数据透视表操作界面主要由汇总表区域和数据透视表字段列表构成。

数据透视表的排序、筛选和分类汇总功能
点击行标签的下拉菜单，可对数据进行排序和筛选；
点击左侧的“＋”“－”可以对数据展开和折叠。

案例演示：利用数据透视表汇总销售数据
数据透视图
数据透视图是以数据透视表的汇总表为数据源，建立的具有动态分析能力的图表。

数据透视图兼有数据透视表工具和图表工具的功能，能够根据数据分析的目的和需要，直观呈现数据信息。

案例演示：利用数据透视图进行数据分析
五、公式和函数
在Excel报表中，为了快速进行数据的统计和分析，我们需要使用公式和函数。

Excel公式是进行数值计算的等式。

从广义上来说，Excel函数其实也属于公式，是一些预定义的公式，它们使用参数的特定数值按特定的顺序或结构进行计算。

公式的运算
（一）公式
相对引用和绝对引用
单元格的引用通常是为了使用某个单元格的公式，而对单元格进行标识的方式。

引用单元格能够通过所标识的单元格来快速获得公式对数据的运算。

（二）函数
函数是一些已经定义好的公式，Excel中的大多数函数是常用公式的简写形式。

函数通过参数接收数据，输入的参数应放到函数名后并且用括号括起来。

函数的格式
函数格式＝函数名（参数1，参数2，…）
常用函数
SUM函数
“sum”在英语中表示“总数、总和、求和”的意思，SUM函数是用来计算某一个或多个单元格区域中所有数字的总和的求和函数。

AVERAGE函数
“average”在英语中表示“平均、平均数”的意思，AVERAGE函数是用来计算指定数据集合中所有数值平均值的函数
其语法结构为：
AVERAGE（number1,number2,…）
MAX和MIN函数
“max”/“min”是“maximum”和“minimum”的缩写。

在英语中分别表示“最大量”和“最小量”的意思，分别用来返回指定区域内所有数值的最大值和最小值。

其语法结构为：
MAX（number1,number2,…）
MIN（number1,number2,…）
LARGE和SMALL函数
“large”/“small”是分别表示“大的”和“小的”的意思，分别用来返回指定区域内所有数值的第k个最大值和最小值。

其语法结构为：
LARGE（array，k）（k<n）
SMALL（array，k）（k<n）
“rank”在英语中表示“等级、排列”的意思，用来返回指定区域内所有数值的大小排序的情况。

其语法结构为：
RANK（number，ref，order）
number 需要进行排位的数值
ref 数字列表数组
order 表示对ref进行排序的方式
为0或省略时表示降序，为1表示升序
IF函数
“if”在英语中表示“如果，假设”的意思，用来判断真假值，再根据逻辑判断的真假值返回不同结果的函数。

其语法结构为：
IF（logical_test,value_if_true,value_if_false）
logical_test 判断的方式，返回值或表达式
value_if_true 表示判断结果为真时返回的值
value_if_false 表示判断结果为假时返回的值
案例演示：常用函数的应用
嵌套函数
嵌套函数，就是指在某些情况下，某一函数作为另一函数的参数使用，也就是说函数中还有函数。

在EXCEL经常使用到IF函数的嵌套函数，也就是根据不同的条件，对返回值进行不同的处理比如销售业绩或者成绩的评定、员工工资表中个税的计算等等。

案例演示：嵌套函数
公式和函数的常见错误分析
在使用公式和函数进行计算时，有时会在单元格中看到“#NAME?”、“#VALUE?”等信息，这些都是使用公式和函数过程中，出现错误后返回的错误值，正确分析这些错误信息，有利于我们用好公式和函数。

1.#DIV/O!
当公式被零除时，将会产生错误值#DIV/O!。

原因一：在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用。

注意：如果运算对象是空白单元格，Excel将此空值当作零值。

解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。

原因二：输入的公式中包含明显的除数零，例如：＝5/0。

解决方法：将零改为非零值。

2.#VALUE!
当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。

原因一：在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。

解决方法：确认公式或函数所需的运算符或参数正确。

原因二：赋予需要单一数值的运算符或函数一个数值区域。

解决方法：将数值区域改为单一数值。

修改数值区域，使其包含公式所在的数据行或列。

3.#####!
原因一：如果单元格所含的数字、日期或时间比单元格宽。

解决方法：单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。

原因二：单元格的日期时间公式产生了一个负值，就会产生#####!错误。

解决方法：EXCEL使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#####!错误。

4.#NAME?
在公式中使用了Excel不能识别的文本时将产生错误值#NAME?。

原因一：删除了公式中使用的名称，或者使用了不存在的名称。

解决方法：确认使用的名称确实存在。

到公式选项卡中的名称管理器中检查该名称是否存在。

原因二：名称的拼写错误。

解决方法：修改拼写错误的名称。

原因三：在公式中输入文本时没有使用双引号。

解决方法：Excel将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。

5.#REF!
当单元格引用无效时将产生错误值#REF!。

原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。

解决方法：更改公式或者在删除或粘贴单元格之后，立即单击“撤消”按钮，以恢复工作表中的单元格。

6.#N/A
原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。

解决方法：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入"#N/A"，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。