财务与会计数据处理-以Excel为工具第10章-Excel应用进阶课件

【学习目标】通过本章的学习，了解Excel进一步的应用领域和可能的发展趋势。掌握Excel与外部数据发生交互的方法；了解进一步美化Excel表格的方法；理解Excel与VBA的关系，以及简单应用；了解Excel与商务智能（BI）的发展趋势。【学习目标】通过本章的学习，了解Excel进一步的应用领域和1第一节Excel与数据分析一、 外部数据获取Excel获取外部数据的方法有很多，而大部分的财务软件也都提供了将生成的账表数据直接保存为Excel文件的功能。Excel连接外部数据的主要好处是可以方便对这些数据进行分析，而不用重复的输入或复制数据，输入或复制操作不仅耗时而且容易出错。连接到外部数据之后，还可以自动更新来自原始数据源的Excel工作簿。Excel获取外部数据的方法主要有：从文本文件中获取数据、从Access数据库中获取数据、从网站中获取数据和从MicrosoftQuery获取数据。1.从文本文件中获取数据利用Excel将数据从文本文件导入工作表中。文本导入向导可检査用户正在导入的文本文件，并能确保以用户期望的方式导入数据。第一节Excel与数据分析一、 外部数据获取2二、 数据处理数据处理是指对数据进行加工整理，形成适合数据分析的样式，它是数据分析前必不可少的阶段。数据处理的基本目的是从大量的、杂乱无章、难以理解的数据中，抽取并推导出对解决问题有价值、有意义的数据。数据处理主要包括数据清洗、数据转化、数据提取、数据计算等处理方法(如图10-X)一般拿到手的数据都需要进行一定的处理才能用于后续的数据分析工作，即使再"干净"的原始数据也需要先进行一定的处理才能使用。所以数据处理是数据分析的前提，对有效数据的分析才是有意义的。通过数据处理，将收集到的原始数据转换为可以分析的形式，并且保证数据的一致性和有效性。如果数据本身存在错误，那么即使采用最先进的数据分析方法，得到的结果也是错误的，不具备任何参考价值，基至还会误导决策。而在此前对Excel的介绍中可以看出，Excel提供的数据筛选、有效性控制、以及函数和公式等有强大的数据处理功能，能帮助用户进行快速有效的数据处理。数据清洗数据转化数据提取数据计算二、 数据处理数据清洗数据转化数据提取数据计算3三、 数据分析数据分析是指用适当的分析方法及工具，对处理过的数据进行分析，提取有价值的信息，形成有效结论的过程。其目的是把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来，总结出所研究对象的内在规律。在实际工作中，数据分析能够帮助管理者进行判断和决策，以便采取适当策略与行动。例如，企业的高层希望通过市场分析和研究，把握当前产品的市场动向，从而制订合理的产品研发和销售计划，这就必须依赖数据分析才能完成。数据是通过表格和图形的方式来呈现的，我们常说用图表说话就是这个意思。常用的数据图表包括饼图、柱形图、条形图、折线图、散点图、雷达图等，当然可以对这些图表进一步整理加工，使之变为我们所需要的图形，例如金字塔图、矩阵图、漏斗图、帕雷托图等。大多数情况下，人们更愿意接受图形这种数据展现方式，因为它能更加有效、直观地传递出数据分析者所要表达的观点。所以在一般情况下，能用图说明问题的就不用表格，能用表格说明问题的就不用文字。因此下一节将对数据可视化进行简要介绍。三、 数据分析4第二节Excel与数据可视化数据可视化(DataVisualization)，就是研究如何利用图形，展现数据中隐含的信息，发掘数据中所包含的规律。也就是利用人对形状、颜色、运动的感官敏感性，有效地传递信息帮助用户从数据中发现关系、规律和趋势。它涉及计算机图形学、图像处理、计算机视觉、计算机辅助设计等多个领域，是研究数据展现、数据处理、决策分析等一系列问题的综合技术，随着数据挖掘和大数据的兴起而进一步发展。数据可视化的工具非常多，水晶易表；GoogleFusion；ManyEyes；NodeXL等。有兴趣的读者可以到相关网站去了解。最常见的数据可视化工具就是Excel。Excel是数据可视化的利器之一，我们做完数据分析写报告时就是采用Excel绘制图表的，如第一章中提到的饼图、折线图、条形图、柱形图等常用图形。当然还可以在Excel中绘制数据地图，让我们清晰直观地了解用户、渠道等分布信息。甚至还可以结合Excel控件绘制动态图，展现数据的变化与趋势。不同的关系适合用不同的图表进行表达。第二节Excel与数据可视化数据可视化(DataVisua5

建议采用图形要表达的数据和信息饼图柱形图条形图折线图气泡图其他成分（整体的一部分）

排序（数据间比较）

时间序列（走势、趋势）

频率分布（数据频次）

相关性（数据间关系）

多重数据比较

建议采用图形要表达的数据和信息饼图柱形图条形图折线图气泡图6第三节Excel宏与VBA宏命令是一组命令的集合，用来完成某种功能。宏是由VBA（VisualBasicforApplication）程序语言编写或录制的。当使用Excel进行数据处理或者操作时，如果需要经常在Excel中重复某项任务，可以考虑使用宏自动执行该任务。VBA(VisualBasicForApplication)是一种通用的自动化语言，它可以使Excel中常用的操作步骤自动化，还可以创建自定义的解决方案。VBA好比Excel的“遥控器”，Excel中每个菜单操作命令都对应一句VBA语句，当运行一段VBA语句时，Excel将按照相应顺序执行每句VBA语句，就像VBA在对Excel进行"遥控'一样，自动执行相应的操作。VBA的“遥控”不仅能使操作变得简便，还能使你获得一些使用Excel标准命令所无法实现的功能。在Excel中使用VBA有以下几个优点。1.使固定、重复的任务程序自动化，提高工作效率。2.可进行复杂的数据处理和分析。3.可自定义Excel函数、工具栏、菜单和界面。4.最后，它还可以连接到多种数据库，并进行相应数据库操作。第三节Excel宏与VBA宏命令是一组命令的集合，用来完成某7一、 宏的录制和使用宏就是一段VBA语句的集合，VBA语句以宏的方式存放在Excel中，供调用运行。需要注意的是，宏绝不等于VBA,它只是VBA里最简单的运用，尽管许多Excel过程都可以用录制宏来完成，但是通过宏还是无法完成许多的工作，例如自定义函数、循环判断等操作。宏可通过编写VBA语句、录制宏两种方式获得。我们通常采用的方式就是先录制宏，然后在完成录制宏的基础上进行语句优化调整，得到所要的自动化处理功能。一、 宏的录制和使用8二、 VBA编程基础在Excel中，VBA语句主要在VisualBasic编辑器中编写、修改与运行。VisualBasic编辑器简称VBE,它是一个分离出来的应用程序，可以与Excel无缝链接。但是要使用VBE就必须先打开ExcelVBA模块与Excel工作薄文件一起存储，除非激活VBE窗口，否则VBA模块是看不见的。那么什么是模块呢?模块就像是存放VBA语句的地方。要怎么进入VBE窗口呢?除了通过刚才编辑宏的方式可进入VBE窗口，进入VBE窗口常用的方法还有，在【开发工具】选项卡【代码】组中，单击【VisualBasic】按钮；或按【Alt+F11】组合键。而在默认状态下Excel并没有【开发工具】选项卡作为默认选项，因此需要打开【开发工具】选项卡。打开【开发工具】选项卡步骤如下：设置【开发工具】的路径为【文件】【选项】【自定义功能区】，在右边的【主选项卡】下勾选【开发工具】前面的复选框即可。因为宏背后的本质也是VBA程序，所以录制宏后可以打开【代码】组件，査看这段动作相应的代码。而经过录制宏的步骤，对其功能有了基本了解，这样便于理解相应的宏代码。二、 VBA编程基础9第四节Excel与BI一、商务智能（BI）随着信息化时代的来临和全球经济化的发展，数据存储成本的不断下降，企业数据的总量正在以惊人的速度增长。这些数据是企业的重要资源，但目前大多数的企业并未对其做进一步的利用。统计表明，目前国内企业数据有效利用率不足7%，许多决策是在没有充分信息支持的情况下做出的。为应对日益激烈的竟争，企业需要有灵敏的感觉和快速反应的能力，以便提高反应的速度和决策的准确性。美国市场调査公司IDC的调查表明，37%的业务决策仍然主要依赖于。直觉。或材本能》田。如何充分利用这些隐藏着巨大商业价值的数据资产，提炼出有价值的信息、知识，对提高企业的智能至关重要，依赖直觉制定决策使企业管理水平远远落后于投资商务智能的领先企业，商务智能已成为领先企业与传统企业产生差异的重要因素，而且商务智能开始扩展到业务运营将使这种差异更加明显。正如前GE首席执行官JackWelch所说。一个组织获取知识以及把知识快速转化为行动的能力决定其最终的竞争优势。在这种背景下，商务智能(BusinessIntelligence,BI)逐渐得到了学术界和企业界的重视，它被认为是继ERP之后企业信息化的又一个热潮。商务智能帮助企业整合数据，并把数据转换成有用的信息，从信息中获取知识，提高企业管理决策的能力。。亿万富翁商务成功的秘密是知道一些其他人不知道的东西。。商务智能的最终目的是帮助管理者实现更有效的企业管理，做出更明智的决策，获得更大的收益。第四节Excel与BI一、商务智能（BI）10商务智能系统作为一种辅助决策的工具，为决策者提供信息、知识支持，辅助决策者改善决策水平。商务智能系统的主要功能如下。1.数据集成数据是决策分析的基础。在很多情况下，决策需要的数据零散分布在几个业务系统中，为了做出正确的经营决策，就需要把这些零散的数据收集起来，形成一个系统的整体。因此从多个异构数据源，包括内部的业务系统和外部的数据源中提取源数据，再经过一定的变换后装载到数据仓库，实现数据的集成是必要的。2.信息呈现信息呈现是指把收集的数据以报表的形式呈现出来，让用户了解到企业、市场的现状，这是商务智能的初步功能。例如，BusinessObjects(SAP)的水晶报表(CrystalReports)允许从各种数据源收集数据，使报表分析人员可以随心所欲、快速便捷地设计报表，在信息量现的方式上，除了报表、图等形式以外，还可以用其他更为直观的方式。此外，利用在线分析处理(OLAP)，也可以从多个维度观察数据。3.运营分析运营分析包括运营指标分析、运营业绩分析和财务分析等。运营指标分析是指对企业不同的业务流程和业务环节的指标进行分析，运营业绩分析是指对各部门的营业额、销售量等进行统计，在此基础上进行同期比较分析、应收分析、盈亏分析和各种商品的风险分析等。财务分析是指对利润、费用支出、资金占用以及其他经济指标进行分析，及时掌握企业在资金使用方面的实际情况，调整和降低企业成本。运营分析包括多方面的内容，如表1.2所示。4.战略决策支持战略决策支持是指根据公司各战略业务单元(StrategicBusinessUnit,SBU)的经营业绩和定位，选择一种合理的投资组合战略。由于商务智能系统集成了外部数据，如外部环境和行业信息，各战略业务单元可据此制定自身的竟争战略。此外，企业还可以利用业务运营的数据，提供营销、生产、财务和人力资源等决策支持。商务智能系统作为一种辅助决策的工具，为决策者提供信息、知识支11二、Excel工具-PowerPivot由上一节可知，BI（BusinessIntellegence）商务智能并非Excel独有的概念，而Excel工具可以帮助用户完成BI功能。PowerBI是微软最新的商业智能（BI）概念，它包含了一系列的组件和工具。PowerBI的核心理念就是让我们用户不需要强大的技术背景，只需要掌握Excel这样简单的工具就能快速上手商业数据分析及可视化。二、Excel工具-PowerPivot12财务与会计数据处理——以Excel为工具第10章-Excel应用进阶课件13工具优点缺点Access数据库数据处理能力较强使用SQL处理数据较为灵活适合SQL初学者学习SQL语句数据超千万条时性能会下降使用SQL分析数据不如透视表灵活MicrosoftQuery适合SQL初学者学习SQL语句数据处理性能弱于Access数据库PowerPivot可以处理大型数据集可以用数据透视表灵活分析数据数据处理灵活性低工具优点缺点Access数据库数据处理能力较强数据超千万条时14【本章小结】通过本章的学习，了解Excel进一步的应用领域和可能的发展趋势。掌握Excel与外部数据发生交互的方法；了解进一步美化Excel表格的方法；理解Excel与VBA的关系，以及简单应用；了解Excel与商务智能（BI）的发展趋势。【本章小结】通过本章的学习，了解Excel进一步的应用领域和15【关键名词】数据处理数据分析可视化BI【思考题】1.什么是数据处理，什么是数据分析，两者的区别与联系。2.什么是VBA，宏和VBA有什么关系。3.使用本章学到的宏和VBA知识，做一个工资条的宏。【关键名词】16【学习目标】通过本章的学习，了解Excel进一步的应用领域和可能的发展趋势。掌握Excel与外部数据发生交互的方法；了解进一步美化Excel表格的方法；理解Excel与VBA的关系，以及简单应用；了解Excel与商务智能（BI）的发展趋势。【学习目标】通过本章的学习，了解Excel进一步的应用领域和17第一节Excel与数据分析一、 外部数据获取Excel获取外部数据的方法有很多，而大部分的财务软件也都提供了将生成的账表数据直接保存为Excel文件的功能。Excel连接外部数据的主要好处是可以方便对这些数据进行分析，而不用重复的输入或复制数据，输入或复制操作不仅耗时而且容易出错。连接到外部数据之后，还可以自动更新来自原始数据源的Excel工作簿。Excel获取外部数据的方法主要有：从文本文件中获取数据、从Access数据库中获取数据、从网站中获取数据和从MicrosoftQuery获取数据。1.从文本文件中获取数据利用Excel将数据从文本文件导入工作表中。文本导入向导可检査用户正在导入的文本文件，并能确保以用户期望的方式导入数据。第一节Excel与数据分析一、 外部数据获取18二、 数据处理数据处理是指对数据进行加工整理，形成适合数据分析的样式，它是数据分析前必不可少的阶段。数据处理的基本目的是从大量的、杂乱无章、难以理解的数据中，抽取并推导出对解决问题有价值、有意义的数据。数据处理主要包括数据清洗、数据转化、数据提取、数据计算等处理方法(如图10-X)一般拿到手的数据都需要进行一定的处理才能用于后续的数据分析工作，即使再"干净"的原始数据也需要先进行一定的处理才能使用。所以数据处理是数据分析的前提，对有效数据的分析才是有意义的。通过数据处理，将收集到的原始数据转换为可以分析的形式，并且保证数据的一致性和有效性。如果数据本身存在错误，那么即使采用最先进的数据分析方法，得到的结果也是错误的，不具备任何参考价值，基至还会误导决策。而在此前对Excel的介绍中可以看出，Excel提供的数据筛选、有效性控制、以及函数和公式等有强大的数据处理功能，能帮助用户进行快速有效的数据处理。数据清洗数据转化数据提取数据计算二、 数据处理数据清洗数据转化数据提取数据计算19三、 数据分析数据分析是指用适当的分析方法及工具，对处理过的数据进行分析，提取有价值的信息，形成有效结论的过程。其目的是把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来，总结出所研究对象的内在规律。在实际工作中，数据分析能够帮助管理者进行判断和决策，以便采取适当策略与行动。例如，企业的高层希望通过市场分析和研究，把握当前产品的市场动向，从而制订合理的产品研发和销售计划，这就必须依赖数据分析才能完成。数据是通过表格和图形的方式来呈现的，我们常说用图表说话就是这个意思。常用的数据图表包括饼图、柱形图、条形图、折线图、散点图、雷达图等，当然可以对这些图表进一步整理加工，使之变为我们所需要的图形，例如金字塔图、矩阵图、漏斗图、帕雷托图等。大多数情况下，人们更愿意接受图形这种数据展现方式，因为它能更加有效、直观地传递出数据分析者所要表达的观点。所以在一般情况下，能用图说明问题的就不用表格，能用表格说明问题的就不用文字。因此下一节将对数据可视化进行简要介绍。三、 数据分析20第二节Excel与数据可视化数据可视化(DataVisualization)，就是研究如何利用图形，展现数据中隐含的信息，发掘数据中所包含的规律。也就是利用人对形状、颜色、运动的感官敏感性，有效地传递信息帮助用户从数据中发现关系、规律和趋势。它涉及计算机图形学、图像处理、计算机视觉、计算机辅助设计等多个领域，是研究数据展现、数据处理、决策分析等一系列问题的综合技术，随着数据挖掘和大数据的兴起而进一步发展。数据可视化的工具非常多，水晶易表；GoogleFusion；ManyEyes；NodeXL等。有兴趣的读者可以到相关网站去了解。最常见的数据可视化工具就是Excel。Excel是数据可视化的利器之一，我们做完数据分析写报告时就是采用Excel绘制图表的，如第一章中提到的饼图、折线图、条形图、柱形图等常用图形。当然还可以在Excel中绘制数据地图，让我们清晰直观地了解用户、渠道等分布信息。甚至还可以结合Excel控件绘制动态图，展现数据的变化与趋势。不同的关系适合用不同的图表进行表达。第二节Excel与数据可视化数据可视化(DataVisua21

建议采用图形要表达的数据和信息饼图柱形图条形图折线图气泡图其他成分（整体的一部分）

排序（数据间比较）

时间序列（走势、趋势）

频率分布（数据频次）

相关性（数据间关系）

多重数据比较

建议采用图形要表达的数据和信息饼图柱形图条形图折线图气泡图22第三节Excel宏与VBA宏命令是一组命令的集合，用来完成某种功能。宏是由VBA（VisualBasicforApplication）程序语言编写或录制的。当使用Excel进行数据处理或者操作时，如果需要经常在Excel中重复某项任务，可以考虑使用宏自动执行该任务。VBA(VisualBasicForApplication)是一种通用的自动化语言，它可以使Excel中常用的操作步骤自动化，还可以创建自定义的解决方案。VBA好比Excel的“遥控器”，Excel中每个菜单操作命令都对应一句VBA语句，当运行一段VBA语句时，Excel将按照相应顺序执行每句VBA语句，就像VBA在对Excel进行"遥控'一样，自动执行相应的操作。VBA的“遥控”不仅能使操作变得简便，还能使你获得一些使用Excel标准命令所无法实现的功能。在Excel中使用VBA有以下几个优点。1.使固定、重复的任务程序自动化，提高工作效率。2.可进行复杂的数据处理和分析。3.可自定义Excel函数、工具栏、菜单和界面。4.最后，它还可以连接到多种数据库，并进行相应数据库操作。第三节Excel宏与VBA宏命令是一组命令的集合，用来完成某23一、 宏的录制和使用宏就是一段VBA语句的集合，VBA语句以宏的方式存放在Excel中，供调用运行。需要注意的是，宏绝不等于VBA,它只是VBA里最简单的运用，尽管许多Excel过程都可以用录制宏来完成，但是通过宏还是无法完成许多的工作，例如自定义函数、循环判断等操作。宏可通过编写VBA语句、录制宏两种方式获得。我们通常采用的方式就是先录制宏，然后在完成录制宏的基础上进行语句优化调整，得到所要的自动化处理功能。一、 宏的录制和使用24二、 VBA编程基础在Excel中，VBA语句主要在VisualBasic编辑器中编写、修改与运行。VisualBasic编辑器简称VBE,它是一个分离出来的应用程序，可以与Excel无缝链接。但是要使用VBE就必须先打开ExcelVBA模块与Excel工作薄文件一起存储，除非激活VBE窗口，否则VBA模块是看不见的。那么什么是模块呢?模块就像是存放VBA语句的地方。要怎么进入VBE窗口呢?除了通过刚才编辑宏的方式可进入VBE窗口，进入VBE窗口常用的方法还有，在【开发工具】选项卡【代码】组中，单击【VisualBasic】按钮；或按【Alt+F11】组合键。而在默认状态下Excel并没有【开发工具】选项卡作为默认选项，因此需要打开【开发工具】选项卡。打开【开发工具】选项卡步骤如下：设置【开发工具】的路径为【文件】【选项】【自定义功能区】，在右边的【主选项卡】下勾选【开发工具】前面的复选框即可。因为宏背后的本质也是VBA程序，所以录制宏后可以打开【代码】组件，査看这段动作相应的代码。而经过录制宏的步骤，对其功能有了基本了解，这样便于理解相应的宏代码。二、 VBA编程基础25第四节Excel与BI一、商务智能（BI）随着信息化时代的来临和全球经济化的发展，数据存储成本的不断下降，企业数据的总量正在以惊人的速度增长。这些数据是企业的重要资源，但目前大多数的企业并未对其做进一步的利用。统计表明，目前国内企业数据有效利用率不足7%，许多决策是在没有充分信息支持的情况下做出的。为应对日益激烈的竟争，企业需要有灵敏的感觉和快速反应的能力，以便提高反应的速度和决策的准确性。美国市场调査公司IDC的调查表明，37%的业务决策仍然主要依赖于。直觉。或材本能》田。如何充分利用这些隐藏着巨大商业价值的数据资产，提炼出有价值的信息、知识，对提高企业的智能至关重要，依赖直觉制定决策使企业管理水平远远落后于投资商务智能的领先企业，商务智能已成为领先企业与传统企业产生差异的重要因素，而且商务智能开始扩展到业务运营将使这种差异更加明显。正如前GE首席执行官JackWelch所说。一个组织获取知识以及把知识快速转化为行动的能力决定其最终的竞争优势。在这种背景下，商务智能(BusinessIntelligence,BI)逐渐得到了学术界和企业界的重视，它被认为是继ERP之后企业信息化的又一个热潮。商务智能帮助企业整合数据，并把数据转换成有用的信息，从信息中获取知识，提高企业管理决策的能力。。亿万富翁商务成功的秘密是知道一些其他人不知道的东西。。商务智能的最终目的是帮助管理者实现更有效的企业管理，做出更明智的决策，获得更大的收益。第四节Excel与BI一、商务智能（BI）26商务智能系统作为一种辅助决策的工具，为决策者提供信息、知识支持，辅助决策者改善决策水平。商务智能系统的主要功能如下。1.数据集成数据是决策分析的基础。在很多情况下，决策需要的数据零散分布在几个业务系统中，为了做出正确的经营决策，就需要把这些零散的数据收集起来，形成一个系统的整体。因此从多个异构数据源，包括内部的业务系统和外部的数据源中提取源数据，再经过一定的变换后装载到数据仓库，实现数据的集成是必要的。2.信息呈现信息呈现是指把收集的数据以报表的形式呈现出来，让用户了解到企业、市场的现状，这是商务智能的初步功能。例如，BusinessObjects(SAP)的水晶报表(CrystalReports)允许从各种数据源收集数据，使报表分析人员可以随心所欲、快速便捷地设计报表，在信息量现的方式上，除了报表、图等形式以外，还可以用其他更为直观的方式。此外，利用在线分析处理(OLAP)，也可以从多个维度观察数据。3.运营分析运营分析包括运营指标分析、运营业绩分析和财务分析等。运营指标分析是指对企业不同的