作者：常敏,付俊辉,等
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel数据处理与分析试读：
前言
学习 Excel 的难点不在于你能掌握多少功能命令，而是掌握重点知识的熟练程度和丰富的经验积累。许多用户从一开始接触 Excel 就恨不得把 Excel 的所有功能命令都学一个遍，以为这样就会成为“高手”了，于是买来一本厚厚的说明书式的教材开始“啃”，然而一年半载过去了，书还没看完，能记下的东西更是寥寥无几。其实，学好 Excel 不在于知识点的“多”和“全”，而在于知识点的“专”和“精”，我们只需要熟练掌握 Excel 中最常用的一些功能，即可解决大部分问题。
本书以帮助职场人士用轻松的方式快速掌握 Excel 使用方法为目标，依据作者多年的职场经历和培训教学经验，优选出在企业工作中有学习价值的 Excel 知识和功能，设计出一套经济有效的学习路线，将其中关键的要领呈现在读者面前。与此同时，针对 Excel 使用当中常见的不良习惯和错误方式，书中也提供了更科学合理的思路和建议，尽量让读者少走弯路。主要针对希望快速提升 Excel 软件操作水平的读者，适合于统计人员、财会人员、销售人员以及数据管理人员等作为自学参考用书。
本书全面、系统地讲解了 Excel 在数据处理与分析中的日常应用，结构独具匠心，以应知应会的知识点为主线，以实际案例和应用技巧为主题，有助于职场人士解决实际工作中的问题，提高工作效率，培养 Excel 应用能力。本书内容丰富，深度和广度兼顾，可以帮助职场中经常和 Excel 打交道的人士提升日常工作中的 Excel 应用技能，也适合 Excel初学者作为一本快速入门图书来阅读。
不要指望这本书解决所有关于 Excel 的问题，但你可以找到极速、便捷使用 Excel 解决问题的思路。本书作者
本书由多年从事办公软件研究及培训的专业人员编写，他们拥有非常丰富的实践及教学经验，并已编写和出版过多本相关书籍。我们真切希望读者在阅读本书之后，可以开拓视野，增长实践操作经验，并从书中学习和总结操作的经验与规律，达到灵活运用的水平。鉴于编者水平有限，书中有纰漏和考虑不周之处在所难免，欢迎读者予以批评、指正，以便我们日后能编写更好的图书。编者2018年9月
第1章 你真的认识Excel吗
※做个数据分析专家
※走出数据源表制作误区
当老板说：“嘿，给我一份 Excel 数据分析报告”的时候，Excel 菜鸟们也许会睁大眼睛一脸茫然，在心里大叫：“噢，那是啥？!Excel 能做报告？！数据分析报告长什么样啊？!”这是因为菜鸟们还没有认识到Excel的“真面目”。
1.1 做个数据分析专家
“数据分析”是什么？最直接的理解，数据分析就是对数据进行分析。“数据分析”的目的是什么？就是选用适当的统计分析方法加工处理收集到的数据，从杂乱无章的数据中提炼出有意义的信息，并进一步地总结出研究对象中隐藏的规律。要做一个Excel高手，首先要成为一个数据分析专家。
1.1.1 五步完成数据分析
成为数据分析专家很难吗？No！只要掌握了数据分析五大步骤，即使考一个“数据分析师”资格证书，也可以成为一名合格的Excel表格制作与数据分析专家。1.数据分析五大步骤
根据实际工作经验不难总结出，在数据分析的过程中包括了五大步骤，下面就来看看到底是哪五大步骤吧。
步骤1 明确数据分析的目的和内容。
步骤2 收集数据。
步骤3 处理数据。
步骤4 分析数据。
步骤5 展现数据。
这五大步骤其实是一种经验总结或者说工作思路，可以指导菜鸟如何进行数据分析，帮助高手提高数据分析工作的效率。白纸黑字看起来太累，下面利用 Excel 的SmartArt图形来对其做进一步的讲解。2.交出合格的数据分析报告
按照数据分析五大步骤进行操作，可以使 Excel 菜鸟迅速化身高手，轻松完成数据分析，但是老板需要的可不是一份干巴巴的数据分析结果。
当老板说：“给我一份数据分析报告”的时候，不用感到茫然无措。所谓“数据分析报告”其实就是对整个数据分析过程做一个总结，将数据分析的起因、过程、结果和建议以报告的形式完整地呈现出来，为决策者提供参考。搞定了数据分析，自然能够轻松搞定“分析报告”。
如何做出一份合格的数据分析报告？这很简单。首先确立一个结构分明、层次清晰的框架，然后以图文并茂的形式，生动、直观地将报告内容展现给阅读者，使阅读者可以轻松理解报告中提出的问题、分析的过程和结论，以及提供的建议。下面列举了一个数据分析报告的基本框架范例。
总的来说，要做好一份数据分析报告，需要做到以下几点。
报告框架结构清晰，内容主次分明。
图文并茂，使阅读者形象、直观地看到并理解报告内容。
对分析目标做出明确的结论。
针对提出的问题，提供具有可行性的建议或解决方案。
1.1.2 数据分析常用指标与术语
某领导总结说：“今年做得不错，去年2万件的销量今年提高到4万件，翻了两番。另外，我们引进的新技术，让成本得到了有效的控制，从30万元降低到15万元，足足下降了一倍！”如此“专业”的发言，引来会议室里一阵偷笑。
要知道，在进行数据分析的时候，常常会用到“番数”“倍数”“比例”“比率”等分析指标和术语，想做一个真正的 Excel 数据分析专家，就得学习并理解这些常用的分析指标和术语，避免成为“砖家”贻笑大方。1.相对数与绝对数
相对数与绝对数是数据分析中常用到的综合指标。
按照专业的说法，其中绝对数反映的是“客观现象总体在一定时间、地点条件下的总规模、总水平”，或者表现为“在一定时间、地点条件下数量的增减变化”。例如人们常说的人口总数、GDP等就是绝对数。
而相对数，是将两个有联系的指标进行对比计算得出的数值，用来反映“客观现象之间的数量联系程度”。相对数常以倍数、百分数、成数等表示。
相对数的基本计算公式为：
在相对数的计算中，用来与基础数值进行对比的指标数值被称为“比较数值”，即“比数”；用作对比标准的指标数值被称为“基础数值”，即“基数”。例如，说“今年的销售额是去年的2倍”，其中“今年的销售额”是比数，“去年的销售额”是基数，用比数除以基数后，得到的相对数以“2倍”表示。
提示：在使用相对数时，要注意指标的可比性，同时可以和绝对数（总量）指标结合使用。2.平均数
平均数在日常生活中应用广泛。比如，数学老师计算出某班学生的平均成绩，以此为指标，判断哪些学生的成绩高于平均分需要继续保持，哪些学生的成绩低于平均分需要加强指导。
在这里提到的平均数是算术平均数，是将一组数据求和后除以数据的个数得到的数值。算术平均数是十分重要的基础指标，它能够代表总体的一般水平，将总体内各单位的数量差异抽象化，掩盖各单位的差异。
提示：除了算术平均数，还有几何平均数、调和平均数等，在日常生活中提到的“平均数”通常都是指算术平均数。3.番数与倍数
番数与倍数都属于相对数。
倍数一般用来反映数量的增长情况或者上升幅度，是一个数据除以另一个数据得到的商。如“去年2万件的销量今年提高到4万件”,4万件÷2万件=2，所以今年的销量是去年的2倍。
番数是指原数量的2的n次方倍（2n）。如“去年2万件的销量今1年提高到4万件”，销量翻了一番为原数量的2倍（2），而“翻了两23番”则表示数量为原数量的4倍（2）,“翻了三番”即是8倍（2），以此类推。
提示：“成本得到了有效的控制，从30万元降低到15万元，足足下降了一倍！”是错误的说法。倍数不适用于标书数量的减少或下降，可以改用百分数表示，如“下降了50%”。4.百分点与百分比
百分比也是一种相对数，也叫百分数或者百分率，它可以表示一个数是另一个数的百分之多少。其计算公式为：百分比=（比数÷基数）×100%。如，成本“从30万元降低到15万元”，成本下降了50%。
百分点是指在以百分数形式表示的情况下，不同时期的相对指标的变动幅度，1个百分点=1%。如，“今年公司利润为43%，与去年的32%相比，提高了11个百分点”。5.频数与频率
频数属于绝对数，是指一组数据中个别数据重复出现的次数。如某公司有员工60人，其中男员工有24人，女员工有36人，那么按男女性别进行分组，男员工的频数为24，女员工的频数为36。
频率用于反映某类别在总体中出现的频繁程度，一般用百分数表示，是一种相对数。其计算公式为：频率=（某组类别次数÷总次数）×100%。如在某公司的60名员工中，24名男员工出现的频率为（24÷60）×100%=40%;36名女员工出现的频率为（36÷60）×100%=60%。6.比例与比率
比例与比率都属于相对数。
其中，比例用于反映总体的构成和结构，表示总体中各部分的数值占全部数值的比重。如，某公司有员工60人，其中男员工有24人，女员工有36人，那么公司中男员工的比例为24:60，女员工的比例为36:60。需要注意的是，比例的基数是全体员工人数。
而比率用于反映一个整体中各部分之间的关系，是不同类别的数值的对比。如，公司男员工有24人，女员工有36人，则公司男女员工比率为24:36。比率这一指标常常被用在社会经济领域。7.同比与环比
常常能看到这样的描述，“公司第一季度销售额达到60万，同比增长20%。”或者“2月份销售量环比增长5%”。
其中，同比是与上一个统计周期的同期进行对比，如2013年第一季度与2012年第一季度销售额对比；环比是用现在的统计周期和上一个统计周期进行比较，如2013年2月与2013年1月销售量对比。通过同比指标可以反映出事物发展的相对情况；同比和环比指标可以反映事物逐期发展的情况。
1.2 走出数据源表制作误区
在本书中，将数据分析中合适的基础数据表称为“数据源表”，而对于什么是“数据源表”?“数据源表”又来自何处？由于每个用户所处的情况各不相同，“数据源表”可能出自于ERP系统，可能出自财务软件，也可能完全是手工输入的数据。
因为很少有人讨论“数据源表”的管理，所以很多用户并不明白“数据源表”的重要性。这一认知很容易导致数据资料的混乱，比如对应一项数据指标往往有若干个表格，真正做分析时，又没有一个能用。所以，就会不停地做重复的工作，使加班成为常态。
本节将介绍“数据源表”的制作及管理的一些基本理念，介绍一些用户常犯的错误，带领大家走出“数据源表”的制作误区。
1.2.1 清理电脑中的4大类表格
每个用户的电脑中都有大量的 Excel 文件，这些文件的来源不同，用户也不同，而管理这些 Excel 文件，是一件令人头疼的事情。其实，电脑中那么多的表格，我们大致可以分为4大类，即原始数据表、数据源表、计算分析表和结果报告表。这4大类表格及其逻辑使用过程，也可以用下图来表示。
原始数据表：他人提供，或自行编制的原始数据，表格格式应尽量接近于分析所需数据源表的要求，一般需加工后才能用于数据分析。
数据源表：他人提供，或自行编制的用于数据分析的基础数据表，对于表格的格式有一定的要求，但这类表格一般不对外报送。
计算分析表：自行编制的分析用工作底稿，一般不对外报送。
结果报告表：对外提供的分析结果报表，格式无要求。
这四大类的表格，地位会根据其所处的位置而变化。例如他人提供的表格，对他人而言就是结果报告表，而对于报告使用者来说，可能是数据源表，也可能仅仅是原始数据表。
即使对于同一个用户的同一份表格，也可能因为不同的工作内容而处在不同的位置，比如A工作中的数据源表，在B工作中就有可能仅仅是原始数据表。
要制作出一份设计合理的表格，首先需要界定这份表格在他人电脑中的身份地位，因为对于原始数据表、数据源表、结果报告表的格式要求是不一样的。
下面，通过一个案例来理解一下表格的分类及编制要求。1.忙碌的销售助理
某企业从事化妆品的生产与销售，在全国各地有数十家直营专柜，分别由6位业务员负责。业务员每天上午10点之前都会提供前一天各人分管专柜的销售日报表给公司的专柜销售助理，日报表的统一格式如下图所示。
当专柜销售助理收到日报表后，将文件名统一为“业务员的名字-日期”的格式，例如2016年7月1日收到业务员A上报的日报表，就将文件名改为业务员A-2016.7.1，然后存放在硬盘中，按业务员的姓名、年、月设置的文件夹下，例如业务员A在2016年7月1日～7月5日上报的文件全部在“商场销售日报表/业务员A/2016年/7月”这个文件夹下。
销售助理如此分类，觉得表格格式统一，文件整理得井井有条。可是，当真正要用时，却会发现如此分类会大大增加工作量。
这位销售助理的真实情况是，经常遇到加班作表，分析专柜数据的情况。而加班的原因，总是抱怨老板的要求多变，分析的要求不一致。例如，这个月要看区域的销售情况，下个月要根据业务员分析，有时候要看总的销售额变动，有时候又要分析促销的情况，当遇到这些要求时，销售助理每次都要重新整理表格，导致加班。2.理解4大类表格
为了找出发生这种问题的关键，首先要理解4大类的表格。
如果读者以前并没有接触过数据分析工作，仅通过前面的例子，也很难找出表格体系存在的问题。其实，这个例子是一个很典型的“数据源表”缺失的例子。
我们首先看一下业务员提供的销售日报表，因为格式是统一规定的，所以业务员每天上报这样的表格并没有问题。
然后再看销售助理的文件管理方式，文件统一格式命令，按业务员的姓名、年、月设置文件夹来存放，虽然不是最佳方案，也不能说有大错。
关键的问题是，管理阶层要看的资料，并不是每个业务员提供的单个销售日报表，而是要看汇总的分析资料，如果只是根据已有的资料简单汇总，并不能满足管理层的需要。
在上文中，我们看到在销售助理电脑中的一份份销售日报表，对于提供者业务员来说是“结果报告表”，但是对于销售助理来说，仅仅是其从事专柜销售分析工作的“原始数据表”，并没有可以直接用于分析的“数据源表”。她工作的实质是每次接到新的分析任务后，根据“原始数据表”临时组织数据表用于当次的分析，再据以编制“结果报告表”，其工作流程图如下。
例如，要分析重百专柜2014年和2015年按产品大类的销售分析，助理就会做出如下的表格。
为了做上图中的表格，需要整理如下图所示的销售额数据表，如果数据过多，甚至需要用计算器及手工计算填列数据。
如果下次的分析要求变了，改为分析新世纪专柜2014年与2015年参加促销的产品销售额比较情况，就会发现上次整理的销售额数据已经完全无用，需要再重新组织含促销金额的数据表，如下图所示。
根据以上的分析，我们可以得知，这位销售助理的工作效率比较低，经常加班的原因是没有一个可通用于管理层对于专柜各项数据分析要求的“数据源表”。
知道了问题的源头，接下来就看看应该怎么解决这一类问题。
一个理想的数据源表应该只有一张工作表，而不是本例中的多个文件夹下的多个工作簿。当然，这只是一种理想的状态，在实际工作中，我们可以用两种方法来达到这种理想的状态：
通过合理地组织原始数据表，使其达到或接近“数据源表”的要求。
对“原始数据表”及时汇总加工，自行打造“数据源表”。
以上两种解决方法，一般需要结合使用，而本例中则需要先改变原始数据表的格式，使其达到数据源表的要求，然后再对原始数据表加以汇总。
1.2.2 汇总数据到一个工作表
假设每位业务专员提供的表格都符合销售助理分析所需的数据源表的要求，但这些表格分散在多层文件夹下的多个工作簿中，而每个工作簿中又有多个工作表，如下图所示。此时需要汇总这么多工作簿中的数据不是一件容易的事情，无论是使用复制粘贴的方法或者是函数、透视表、VBA都不行。
对于普通的Excel用户而言，不管用什么方法都不容易把数据汇总到一个工作表，这并不是 Excel 的功能缺失，而是因为问题出在原始数据的组织上，原始数据人为地放在多个工作簿中，将简单的问题复杂化了。下面，我们就来解决这个问题，把数据汇总。1.从多个工作表到一个工作表
在汇总数据时，如果销售助理在表格中增加一个字段“商场”，那么，每个业务员每天只需要报送一张工作表，而非之前在一个工作簿中存放若干个工作表。增加“商场”字段后的“商场销售日报表”如下图所示。2.从多个工作簿到一个工作表
如果再增加一个“日期”字段，业务员报送的资料将从“商场销售日报表”变成“商场销售日报表汇总”，规定将2016年7月的这一个月的数据放在同一个工作表中，那么“商场销售日报表/业务员 A/2016年/7月”这个文件夹中，将只剩下一个文件，文件中只有一个工作表。增加“日期”字段后的“商场销售日报表汇总”如下图所示。3.从多个文件夹到一个工作表
现在，我们还可以继续整合工作表，比如将每个业务员一年的数据放在一个工作表中，再增加一个“业务员”字段列表，然后整合需要分析的年、月数据，这样可以将原来多层文件夹下多个工作簿中的数据存放在一张工作表中。增加“业务员”字段后的“商场销售日报表汇总”如下图所示。
1.2.3 补齐分析必需字段
经过以上的调整，每个业务员每年只需要提供一张表格就可以了，但是，这张表格离数据源表的要求还有距离。回顾一下销售助理的困惑，是因为老板的要求多变，每一次分析的要求都可能不一样。例如，这个月要看区域的销售情况，下个月要根据业务员分析，有时候要看总的销售额变动，有时候又要分析促销的情况。
此时，对应管理层的分析要求，已经制作完成的表格中，有了“业务员”“销售额”“促销”等资料。但是“销售区域”的资料，如南区、北区等并没有在表格中有所体现。所以，解决方法是可以在表格中增加“区域”字段，完成后如下图所示。
在增加字段时，除了老板的要求之外，还需要有一定的前瞻性。例如，现在并没有提出按“省份”分析的要求，但是考虑到组织架构，今后完全可能会提出，所以在准备数据源表时，可以把“省份”字段加上去。增加省份字段后的效果如下图所示，而用户也可以根据自己公司的情况，设置其他字段。
1.2.4 规范字段设置
对于下面的这张表格，也许你认为已经具备了管理层分析所要求的所有字段元素，但即使如此表达，还存在很多问题，例如“销售方式”栏。
分析一张表格的设置是否合理，首先要看这张表格的身份地位，看其属于4大类表格中的哪一种。而上表中“销售方式”部分如果作为“结果报告表”，则不能简单地评论其是否规范，也许报表的使用者就喜欢用这种格式，但如果是将上表作为数据源表的一部分，就会导致如下诸多缺陷：
字段记录缺失，在销售方式栏下有很多空格；
有两层字段名称，而第2层字段名称（正常销售、公司促销、商场促销）本身就是第1层字段（销售方式）下的属性。
如果没有第2个问题的存在，而只需要处理第1个字段记录缺失的问题，最常用的方法是将窗格部分全部用其他符号填充，如“○”，操作方法如下。
步骤1 选中L5:N14单元格区域。
步骤2 按下“F5”键调出“定位”对话框，单击“定位条件”按钮，在弹出的“定位条件”对话框中选中“空值”选项，然后单击“确定”按钮。
步骤3 输入符号“○”，然后按下“Ctrl+Enter”组合键，即可快速填充所有空值。
但是，这个例子并不只是字段记录缺失的问题，而是把字段的属性值作为字段名，最佳的解决方案应该是将属性值放到该放的位置上。例如，将“销售方式”作为字段，原2层字段名称则作为“销售方式”字段的属性，修改后的商场销售日报表汇总如下图所示。
1.2.5 去除多余的表格名称
对于要向外报送的表格，很多用户习惯为表格设置一个表格名称和制表日期，以便于表格使用者了解其阅读表格的主题，如下图所示。
但是，如果表格的用途是“数据源表”，那么表格的名称不仅没有必要，还会给数据分析带来麻烦。例如，我们使用 SQL 语句查询其他工作表的数据时，Excel 会默认工作表中的第1行为表头，但是在本例中，表头则是在第3行。处理这一类问题的方法很简单，直接把表格名称和日期行删除即可，让工作表的第1行为表头，效果如下图所示。
1.2.6 使用单层表头
作为向外报送的“结果报告表”，主要的特点是把问题表述清楚，使报表使用者能够接受，而表格的形式并不是关键。但是作为“数据源表”，因为Excel默认工作表中的第1行为表头，那么如果是制作“数据源表”，则应该使用单层表头。
在本例中，不仅存在双层表格，还用到了合并单元格，如果取消合并单元格设置，效果如下图所示。
当取消合并单元格后，第二排的若干个单元格的内容为空白。这是因为合并单元格的值仅存在于合并区域中的某一个单元格，而系统默认为左上角的单元格。在这个例子中，I1和 J1单元格之前是合并在一起的，但取消合并之后，只有 I1单元格有内容。
如果不对多层表格加以处理，Excel默认使用第1行为表头，会造成在J列的字段名称缺失，且使I列使用“单价”作为字段名，这并不符合这个数据源表的实际情况，因为I列实际需要的字段名应该为“零售价”。
处理的方法是，使用单层表头，无论是多层表头或是斜线表头，全部取消，修改后的“数据源表”如下图所示。
试读结束[说明：试读内容隐藏了图片]