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

1、通过本章的学习，了解通过本章的学习，了解Excel进一步的应用领进一步的应用领域和可能的发展趋势。掌握域和可能的发展趋势。掌握Excel与外部数据与外部数据发生交互的方法；了解进一步美化发生交互的方法；了解进一步美化Excel表格表格的方法；理解的方法；理解Excel与与VBA的关系，以及简单的关系，以及简单应用；了解应用；了解Excel与商务智能（与商务智能（BI）的发展趋）的发展趋势。势。第1页，共16页。一、一、外部数据获取外部数据获取Excel获取外部数据的方法有很多，而大部分的财务软件也都提供获取外部数据的方法有很多，而大部分的财务软件也都提供了将生成的账表数据直接保存为了将生成的账

2、表数据直接保存为Excel文件的功能。文件的功能。Excel连接外部连接外部数据的主要好处是可以方便对这些数据进行分析，而不用重复的输数据的主要好处是可以方便对这些数据进行分析，而不用重复的输入或复制数据，输入或复制操作不仅耗时而且容易出错。连接到外入或复制数据，输入或复制操作不仅耗时而且容易出错。连接到外部数据之后，还可以自动更新来自原始数据源的部数据之后，还可以自动更新来自原始数据源的Excel工作簿。工作簿。Excel获取外部数据的方法主要有：从文本文件中获取数据、从获取外部数据的方法主要有：从文本文件中获取数据、从Access数据库中获取数据、从网站中获取数据和从数据库中获取数据、从网

3、站中获取数据和从Microsoft Query获取数据。获取数据。1.从文本文件中获取数据从文本文件中获取数据利用利用Excel将数据从文本文件导入工作表中。文本导入向导可检査将数据从文本文件导入工作表中。文本导入向导可检査用户正在导入的文本文件，并能确保以用户期望的方式导入数据。用户正在导入的文本文件，并能确保以用户期望的方式导入数据。第2页，共16页。二、二、数据处理数据处理数据处理是指对数据进行加工整理，形成适合数据分析的样式，它数据处理是指对数据进行加工整理，形成适合数据分析的样式，它是数据分析前必不可少的阶段。数据处理的基本目的是从大量的、是数据分析前必不可少的阶段。数据处理的基本目

4、的是从大量的、杂乱无章、难以理解的数据中，抽取并推导出对解决问题有价值、杂乱无章、难以理解的数据中，抽取并推导出对解决问题有价值、有意义的数据。数据处理主要包括数据清洗、数据转化、数据提取、有意义的数据。数据处理主要包括数据清洗、数据转化、数据提取、数据计算等处理方法数据计算等处理方法(如图如图10-X)一般拿到手的数据都需要进行一定一般拿到手的数据都需要进行一定的处理才能用于后续的数据分析工作，即使再的处理才能用于后续的数据分析工作，即使再干净干净的原始数据也的原始数据也需要先进行一定的处理才能使用。所以数据处理是数据分析的前提，需要先进行一定的处理才能使用。所以数据处理是数据分析的前提，对

5、有效数据的分析才是有意义的。对有效数据的分析才是有意义的。通过数据处理，将收集到的原始数据转换为可以分析的形式，并且通过数据处理，将收集到的原始数据转换为可以分析的形式，并且保证数据的一致性和有效性。如果数据本身存在错误，那么即使采保证数据的一致性和有效性。如果数据本身存在错误，那么即使采用最先进的数据分析方法，得到的结果也是错误的，不具备任何参用最先进的数据分析方法，得到的结果也是错误的，不具备任何参考价值，基至还会误导决策。而在此前对考价值，基至还会误导决策。而在此前对Excel的介绍中可以看出，的介绍中可以看出，Excel提供的数据筛选、有效性控制、以及函数和公式等有强大的提供的数据筛选

6、、有效性控制、以及函数和公式等有强大的数据处理功能，能帮助用户进行快速有效的数据处理。数据处理功能，能帮助用户进行快速有效的数据处理。数据清洗数据转化数据提取数据计算第3页，共16页。三、三、数据分析数据分析数据分析是指用适当的分析方法及工具，对处理过的数据进行分析，提数据分析是指用适当的分析方法及工具，对处理过的数据进行分析，提取有价值的信息，形成有效结论的过程。其目的是把隐藏在一大批看似取有价值的信息，形成有效结论的过程。其目的是把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来，总结出所研究对象的内在杂乱无章的数据背后的信息集中和提炼出来，总结出所研究对象的内在规律。在实际工作中，

7、数据分析能够帮助管理者进行判断和决策，以便规律。在实际工作中，数据分析能够帮助管理者进行判断和决策，以便采取适当策略与行动。例如，企业的高层希望通过市场分析和研究，把采取适当策略与行动。例如，企业的高层希望通过市场分析和研究，把握当前产品的市场动向，从而制订合理的产品研发和销售计划，这就必握当前产品的市场动向，从而制订合理的产品研发和销售计划，这就必须依赖数据分析才能完成。须依赖数据分析才能完成。数据是通过表格和图形的方式来呈现的，我们常说用图表说话就是这个数据是通过表格和图形的方式来呈现的，我们常说用图表说话就是这个意思。常用的数据图表包括饼图、柱形图、条形图、折线图、散点图、意思。常用的数

8、据图表包括饼图、柱形图、条形图、折线图、散点图、雷达图等，当然可以对这些图表进一步整理加工，使之变为我们所需要雷达图等，当然可以对这些图表进一步整理加工，使之变为我们所需要的图形，例如金字塔图、矩阵图、漏斗图、帕雷托图等。的图形，例如金字塔图、矩阵图、漏斗图、帕雷托图等。大多数情况下，人们更愿意接受图形这种数据展现方式，因为它能更加大多数情况下，人们更愿意接受图形这种数据展现方式，因为它能更加有效、直观地传递出有效、直观地传递出数据分析者所要表达的观点。所以在一般情况下，能用图说明问题的就数据分析者所要表达的观点。所以在一般情况下，能用图说明问题的就不用表格，能用表格说明问题的就不用文字。因此

9、下一节将对数据可视不用表格，能用表格说明问题的就不用文字。因此下一节将对数据可视化进行简要介绍。化进行简要介绍。第4页，共16页。数据可视化数据可视化(Data Visualization)，就是研究如何利用图形，展现，就是研究如何利用图形，展现数据中隐含的信息，发掘数据中所包含的规律。也就是利用人对形数据中隐含的信息，发掘数据中所包含的规律。也就是利用人对形状、颜色、运动的感官敏感性，有效地传递信息帮助用户从数据中状、颜色、运动的感官敏感性，有效地传递信息帮助用户从数据中发现关系、规律和趋势。它涉及计算机图形学、图像处理、计算机发现关系、规律和趋势。它涉及计算机图形学、图像处理、计算机视觉、

10、视觉、计算机辅助设计等多个领域，是研究数据展现、数据处理、决策分计算机辅助设计等多个领域，是研究数据展现、数据处理、决策分析等一系列问题的综合技术，随着数据挖掘和大数据的兴起而进一析等一系列问题的综合技术，随着数据挖掘和大数据的兴起而进一步发展。数据可视化的工具非常多，水晶易表步发展。数据可视化的工具非常多，水晶易表；Google Fusion；Many Eyes；NodeXL 等。有兴趣的读者可以到相关网站去了解。等。有兴趣的读者可以到相关网站去了解。最常见的数据可视化工具就是最常见的数据可视化工具就是Excel。Excel是数据可视化的利器之是数据可视化的利器之一，我们做完数据分析写报告时

11、就是采用一，我们做完数据分析写报告时就是采用Excel绘制图表的，如第绘制图表的，如第一章中提到的饼图、折线图、条形图、柱形图等常用图形。当然还一章中提到的饼图、折线图、条形图、柱形图等常用图形。当然还可以在可以在Excel中绘制数据地图，让我们清晰直观地了解用户、渠道中绘制数据地图，让我们清晰直观地了解用户、渠道等分布信息。甚至还可以结合等分布信息。甚至还可以结合Excel控件绘制动态图，展现数据的控件绘制动态图，展现数据的变化与趋势。不同的关系适合用不同的图表进行表达变化与趋势。不同的关系适合用不同的图表进行表达。第5页，共16页。建议采用图形建议采用图形要表达的数据和信息要表达的数据和信

12、息饼图柱形图条形图折线图气泡图其他成分（整体的一部分）成分（整体的一部分）排序（数据间比较）排序（数据间比较）时间序列（走势、趋势）时间序列（走势、趋势）频率分布（数据频次）频率分布（数据频次）相关性（数据间关系）相关性（数据间关系）多重数据比较多重数据比较第6页，共16页。宏命令是一组命令的集合，用来完成某种功能。宏是由宏命令是一组命令的集合，用来完成某种功能。宏是由VBA（Visual Basic for Application）程序语言编写或录制的。当使用）程序语言编写或录制的。当使用Excel进行数据处进行数据处理或者操作时，如果需要经常在理或者操作时，如果需要经常在Excel中重复某

13、项任务，可以考虑使用宏中重复某项任务，可以考虑使用宏自动执行该任务。自动执行该任务。VBA(Visual Basic For Application)是一种通用的自动化语言，它可以使是一种通用的自动化语言，它可以使Excel中常用的操作步骤自动化，还可以创建自定义的解决方案。中常用的操作步骤自动化，还可以创建自定义的解决方案。VBA好好比比Excel的的“遥控器遥控器”，Excel中每个菜单操作命令都对应一句中每个菜单操作命令都对应一句VBA语句，语句，当运行一段当运行一段VBA语句时，语句时，Excel将按照相应顺序执行每句将按照相应顺序执行每句VBA语句，就像语句，就像VBA在对在对Exc

14、el进行进行遥控遥控一样，自动执行相应的操作。一样，自动执行相应的操作。VBA的的“遥控遥控”不不仅能使操作变得简便，还能使你获得一些使用仅能使操作变得简便，还能使你获得一些使用Excel标准命令所无法实现标准命令所无法实现的功能。的功能。在在Excel中使用中使用VBA有以下几个优点。有以下几个优点。1.使固定、重复的任务程序自动化，提高工作效率。使固定、重复的任务程序自动化，提高工作效率。2.可进行复杂的数据处理和分析。可进行复杂的数据处理和分析。3.可自定义可自定义Excel函数、工具栏、菜单和界面。函数、工具栏、菜单和界面。4.最后，它还可以连接到多种数据库，并进行相应数据库操作。最后

15、，它还可以连接到多种数据库，并进行相应数据库操作。第7页，共16页。一、一、宏的录制和使用宏的录制和使用宏就是一段宏就是一段VBA语句的集合，语句的集合，VBA语句以宏的方式存语句以宏的方式存放在放在Excel中，供调用运行。需要注意的是，宏绝不等中，供调用运行。需要注意的是，宏绝不等于于VBA,它只是它只是VBA里最简单的运用，尽管许多里最简单的运用，尽管许多Excel过过程都可以用录制宏来完成，但是通过宏还是无法完成程都可以用录制宏来完成，但是通过宏还是无法完成许多的工作，例如自定义函数、循环判断等操作。许多的工作，例如自定义函数、循环判断等操作。宏可通过编写宏可通过编写VBA语句、录制宏

16、两种方式获得。我们语句、录制宏两种方式获得。我们通常采用的方式就是先录制宏，然后在通常采用的方式就是先录制宏，然后在完成录制宏的基础上进行语句优化调整，得到所要的完成录制宏的基础上进行语句优化调整，得到所要的自动化处理功能。自动化处理功能。第8页，共16页。二、二、VBA编程基础编程基础在在Excel中，中，VBA语句主要在语句主要在Visual Basic编辑器中编写、修改与运行。编辑器中编写、修改与运行。Visual Basic编辑器简称编辑器简称VBE,它是一个分离出来的应用程序，可以与它是一个分离出来的应用程序，可以与Excel无缝链接。但无缝链接。但是要使用是要使用VBE就必须先打开

17、就必须先打开Excel VBA模块与模块与Excel工作薄文件一起存储，除非激工作薄文件一起存储，除非激活活VBE窗口，否则窗口，否则VBA模块是看不见的。那么什么是模块呢模块是看不见的。那么什么是模块呢?模块就像是存放模块就像是存放VBA语句的地方。语句的地方。要怎么进入要怎么进入VBE窗口呢窗口呢?除了通过刚才编辑宏的方式可进入除了通过刚才编辑宏的方式可进入VBE窗口，进入窗口，进入VBE窗窗口常用的方法还有，在口常用的方法还有，在【开发工具开发工具】选项卡选项卡【代码代码】组中，单击组中，单击【Visual Basic】按钮；或按按钮；或按【Alt+F11】组合键。组合键。而在默认状态下

18、而在默认状态下Excel并没有并没有【开发工具开发工具】选项卡作为默认选项，因此需要打开选项卡作为默认选项，因此需要打开【开发工具开发工具】选项卡。选项卡。打开打开【开发工具开发工具】选项卡步骤如下：设置选项卡步骤如下：设置【开发工具开发工具】的路径为的路径为【文件文件】【选选项项】【自定义功能区自定义功能区】，在右边的，在右边的【主选项卡主选项卡】下勾选下勾选【开发工具开发工具】前面的复前面的复选框即选框即可。可。因为宏背后的本质也是因为宏背后的本质也是VBA程序，所以录制宏后可以打开程序，所以录制宏后可以打开【代码代码】组件，査看这组件，査看这段动作相应的代码。而经过录制宏的步骤，对其功能

19、有了基本了解，这样便于理段动作相应的代码。而经过录制宏的步骤，对其功能有了基本了解，这样便于理解相应的宏代码。解相应的宏代码。第9页，共16页。一、商务智能（一、商务智能（BI）随着信息化时代的来临和全球经济化的发展，数据存储成本的不断下降，企业数随着信息化时代的来临和全球经济化的发展，数据存储成本的不断下降，企业数据的总量正在以惊人的速度增长。这些数据是企业的重要资源，但目前大多数的据的总量正在以惊人的速度增长。这些数据是企业的重要资源，但目前大多数的企业并未对其做进一步的利用。统计表明，目前国内企业数据有效利用率不足企业并未对其做进一步的利用。统计表明，目前国内企业数据有效利用率不足7%，

20、许多决策是在没有充分信息支持的情况下做出的。为应对日益激烈的竟争，许多决策是在没有充分信息支持的情况下做出的。为应对日益激烈的竟争，企业需要有灵敏的感觉和快速反应的能力，以便提高反应的速度和决策的准确性。企业需要有灵敏的感觉和快速反应的能力，以便提高反应的速度和决策的准确性。美国市场调査公司美国市场调査公司IDC的调查表明，的调查表明，37%的业务决策仍然主要依赖于。直觉。或的业务决策仍然主要依赖于。直觉。或材本能材本能田。如何充分利用这些隐藏着巨大商业价值的数据资产，提炼出有价值田。如何充分利用这些隐藏着巨大商业价值的数据资产，提炼出有价值的信息、知识，对提高企业的智能至关重要，依赖直觉制定

21、决策使企业管理水平的信息、知识，对提高企业的智能至关重要，依赖直觉制定决策使企业管理水平远远落后于投资商务智能的领先企业，商务智能已成为领先企业与传统企业产生远远落后于投资商务智能的领先企业，商务智能已成为领先企业与传统企业产生差异的重要因素，而且商务智能开始扩展到业务运营将使这种差异更加明显。正差异的重要因素，而且商务智能开始扩展到业务运营将使这种差异更加明显。正如前如前GE首席执行官首席执行官Jack Welch所说。一个组织获取知识以及把知识快速转化为行所说。一个组织获取知识以及把知识快速转化为行动的能力决定其最终的竞争优势动的能力决定其最终的竞争优势。在。在这种背景下，商务智能这种背景

22、下，商务智能(Business Intelligence,BI)逐渐得到了学术界和企业界的重视，它被认为是继逐渐得到了学术界和企业界的重视，它被认为是继ERP之后企业之后企业信息化的又一个热潮。商务智能帮助企业整合数据，并把数据转换成有用的信息，信息化的又一个热潮。商务智能帮助企业整合数据，并把数据转换成有用的信息，从信息中获取知识，提高企业管理决策的能力。亿万富翁商务成功的秘密是知从信息中获取知识，提高企业管理决策的能力。亿万富翁商务成功的秘密是知道一些其他人不知道的东西。商务智能的最终目的是帮助管理者实现更有效的道一些其他人不知道的东西。商务智能的最终目的是帮助管理者实现更有效的企业管理，

23、做出更明智的决策，获得更大的收益。企业管理，做出更明智的决策，获得更大的收益。第10页，共16页。商务智能系统作为一种辅助决策的工具，为决策者提供信息、知识支持，辅助决策者改商务智能系统作为一种辅助决策的工具，为决策者提供信息、知识支持，辅助决策者改善决策水平。商务智能系统的主要功能善决策水平。商务智能系统的主要功能如下。如下。1.数据集成数据集成数据是决策分析的基础。在很多情况下，决策需要的数据零散分布在几个业务系统中，数据是决策分析的基础。在很多情况下，决策需要的数据零散分布在几个业务系统中，为了做出正确的经营决策，就需要把这些零散的数据收集起来，形成一个系统的整体。因此为了做出正确的经营

24、决策，就需要把这些零散的数据收集起来，形成一个系统的整体。因此从多个异构数据源，包括内部的业务系统和外部的数据源中提取源数据，再经过一定的变换从多个异构数据源，包括内部的业务系统和外部的数据源中提取源数据，再经过一定的变换后装载到数据仓库，实现数据的集成是必要的。后装载到数据仓库，实现数据的集成是必要的。2.信息呈现信息呈现信息呈现是指把收集的数据以报表的形式呈现出来，让用户了解到企业、市场的现状，这是商务智能的初步功能。信息呈现是指把收集的数据以报表的形式呈现出来，让用户了解到企业、市场的现状，这是商务智能的初步功能。例如，例如，Business Objects(SAP)的水晶报表的水晶报表

25、(Crystal Reports)允许从各种数据源收集数据，使报表分析人员可以随允许从各种数据源收集数据，使报表分析人员可以随心所欲、快速便捷地设计报表，在信息量现的方式上，除了报表、图等形式以外，还可以用其他更为直观的方式。心所欲、快速便捷地设计报表，在信息量现的方式上，除了报表、图等形式以外，还可以用其他更为直观的方式。此外，利用在线分析处理此外，利用在线分析处理(OLAP)，也可以从多个维度观察数据。，也可以从多个维度观察数据。3.运营分析运营分析运营分析包括运营指标分析、运营业绩分析和财务分析等。运营指标分析是指对企业不同的业务流程和业务环节运营分析包括运营指标分析、运营业绩分析和财务

26、分析等。运营指标分析是指对企业不同的业务流程和业务环节的指标进行分析，运营业绩分析是指对各部门的营业额、销售量等进行统计，在此基础上进行同期比较分析、应的指标进行分析，运营业绩分析是指对各部门的营业额、销售量等进行统计，在此基础上进行同期比较分析、应收分析、盈亏分析和各种商品的风险分析等。财务分析是指对利润、费用支出、资金占用以及其他经济指标进行收分析、盈亏分析和各种商品的风险分析等。财务分析是指对利润、费用支出、资金占用以及其他经济指标进行分析，及时掌握企业在资金使用方面的实际情况，调整和降低企业成本。运营分析包括多方面的内容，如表分析，及时掌握企业在资金使用方面的实际情况，调整和降低企业成

27、本。运营分析包括多方面的内容，如表1.2所示。所示。4.战略决策支持战略决策支持战略决策支持是指根据公司各战略业务单元战略决策支持是指根据公司各战略业务单元(Strategic Business Unit,SBU)的经营业绩和定位，选择一种合理的的经营业绩和定位，选择一种合理的投资组合战略。由于商务智能系统集成了外部数据，如外部环境和行业信息，各战略业务单元可据此制定自身的投资组合战略。由于商务智能系统集成了外部数据，如外部环境和行业信息，各战略业务单元可据此制定自身的竟争战略。此外，企业还可以利用业务运营的数据，提供营销、生产、财务和人力资源等决策支持。竟争战略。此外，企业还可以利用业务运营

28、的数据，提供营销、生产、财务和人力资源等决策支持。第11页，共16页。二、二、Excel工具工具-PowerPivot由上一节可知，由上一节可知，BI（Business Intellegence）商务智能并非商务智能并非Excel独有的概念，而独有的概念，而Excel工具工具可以帮助用户完成可以帮助用户完成BI功能。功能。Power BI是微软是微软最新的商业智能（最新的商业智能（BI）概念，它包含了一系）概念，它包含了一系列的组件和工具列的组件和工具。Power BI的核心理念就是的核心理念就是让我们用户不需要强大的技术背景，只需要让我们用户不需要强大的技术背景，只需要掌握掌握Excel这样

29、简单的工具就能快速上手商业这样简单的工具就能快速上手商业数据分析及可视化。数据分析及可视化。第12页，共16页。第13页，共16页。工具工具优点优点缺点缺点AccessAccess数据数据库库数据处理能力较强使用SQL处理数据较为灵活适合SQL初学者学习SQL语句数据超千万条时性能会下降使用SQL分析数据不如透视表灵活Microsoft Microsoft QueryQuery适合SQL初学者学习SQL语句数据处理性能弱于Access数据库PowerPivotPowerPivot可以处理大型数据集可以用数据透视表灵活分析数据数据处理灵活性低第14页，共16页。通过本章的学习，了解通过本章的学习

30、，了解Excel进一步的应用领进一步的应用领域和可能的发展趋势。掌握域和可能的发展趋势。掌握Excel与外部数据与外部数据发生交互的方法；了解进一步美化发生交互的方法；了解进一步美化Excel表格表格的方法；理解的方法；理解Excel与与VBA的关系，以及简单的关系，以及简单应用；了解应用；了解Excel与商务智能（与商务智能（BI）的发展趋）的发展趋势。势。第15页，共16页。【关键名词关键名词】数据处理数据处理 数据分析数据分析 可视化可视化 BI【思考题思考题】1.什么是数据处理，什么是数据分析，两者的区什么是数据处理，什么是数据分析，两者的区别与联系。别与联系。2.什么是什么是VBA，宏和，宏和VBA有什么关系。有什么关系。3.使用本章学到的宏和使用本章学到的宏和VBA知识，做一个工资条的知识，做一个工资条的宏。宏。第16页，共16页。