Excel Data Analysis 简明教程

Advanced Data Analysis - Overview

Excel 提供了几种命令、功能和工具，这些命令、功能和工具可以轻松完成复杂的数据分析任务。借助 Excel，您可以轻松执行各种复杂的计算。在本教程中，您将了解 Excel 中多功能的数据分析工具。您将逐步了解数据分析，其中包含相关示例、说明以及每一步的操作截图。

Excel provides several commands, functions and tools that make your complex data analysis tasks easy. Excel lets you perform various complex calculations with ease. In this tutorial, you will understand the versatile data analysis tools of Excel. You will understand data analysis with relevant examples, step by step instructions and screen shots at every step.

Data Consolidation

您可能必须合并来自各种源的数据并展示一份报告。数据可以位于同一工作簿或不同工作簿的工作表中。借助 Excel 数据工具“合并”，您可以用一些简单的步骤执行此操作。

You might have to consolidate the data from various sources and present a report. The data could be in the worksheets of the same workbook or in different workbooks. With Excel data tool Consolidate, you can perform this in a few easy steps.

What-If Analysis

假设分析为您提供了处理以下数据分析情况的工具 −

What-If Analysis provides you tools to handle the following data analysis situations −

  1. Find the input values that result in a specified value. The result could be set up as a formula with the input values as variables. By varying the values of the input variables, Excel provides the solution with the Goal Seek Tool.

  2. Find the possible output values by varying the values of one or two variables. The result could be set up as a formula with one or two input values as variables. By varying the values for the input variables, Excel provides the solution with the Data Table Tool.

  3. Find the possible output values that are a result of varying the values of more than two variables. The result could be set up as a formula with the input values as variables. By varying the values for the input variables, Excel provides the solution with the Scenario Manager Tool.

Optimizing with Excel Solver Add-in

求解器用来处理复杂的目标寻址情况。在这种情况下，除了输入和输出之外，还将在可能输入值上施加已定义的约束或限制。此外，求解器用来得出最佳解决方案。

Solver is used to handle complex goal seek situations. In such cases, in addition to the inputs and outputs, there will be defined constraints or limits imposed on the possible input values. Further, Solver is used to result in an optimal solution.

Excel 具有一个求解器加载项，它可以帮助您解决此类复杂问题。

Excel has a Solver Add-in that helps you solve such complex problems.

Importing Data into Excel

您的数据分析可能依赖于各种外部数据源。在 Excel 中，您可以从不同数据源导入数据，例如 Microsoft Access 数据库、Web 页面、文本文件、SQL Server 表、SQL Server 分析立方体、XML 文件等。

Your data analysis might depend on various external data sources. In Excel, you can import data from different data sources, such as Microsoft Access Database, Web Pages, Text Files, SQL Server Table, SQL Server Analysis Cube, XML File, etc.

您可以同时从数据库导入任意数量的数据表。当您从关系数据库（例如 Access）导入多个表时，表之间现有的关系也会保留在 Excel 中。在导入数据的同时，您还可以选择基于该数据创建数据透视表、数据透视图或 Power View 报表。

You can import any number of data tables simultaneously from a database. When you are importing multiple tables from a relational database such as Access, the existing relationships among the tables will be retained in Excel also. While importing the data, you can also optionally create a PivotTable or PivotChart or Power View report based on that data.

您可以只创建与数据源的数据连接，或将数据导入到 Excel 中。如果您将数据导入到 Excel 中，则这些数据表将被添加到 Excel 中的数据模型中。

You can just create a data connection with a data source, or import the data into Excel. If you import the data into Excel, the data tables are added to the Data Model in Excel.

Data Model

Excel 中的数据模型用来集成当前工作簿中的多个表的数据和/或来自导入数据的表的数据和/或通过数据连接连接到工作簿的数据源的数据。数据模型在数据透视表、数据透视图、PowerPivot 和 Power View 报告中以透明的方式使用。

Data Model in Excel is used to integrate data from multiple tables in the current workbook and / or from the imported data and / or from the data sources connected to the workbook through data connections. Data model is used transparently in PivotTable, PivotChart, PowerPivot and Power View reports.

  1. You can create a Data Model while importing data, or from the Excel tables in the workbook.

  2. The data tables in the Data Model can be viewed either in Data View or Diagram View.

  3. With a Data Model, you can create relationships among the data tables.

  4. You can either use the Create Relationship command or just click and drag and connect the fields in the two tables that define the relationship in the diagram view of the Data Model.

Exploring Data with PivotTable

当您可以将数据模型与数据透视表集成在一起时，您可以通过整理、连接、总结和报告来自几个不同源的数据来执行大量的数据分析。当您可以从外部数据源导入表并创建一个数据透视表时，当连接的数据源中的数据更新时，可以自动更新数据透视表中的值。

As you can integrate the Data Model with a PivotTable, you can do extensive data analysis by collating, connecting, summarizing and reporting data from several different sources. As you can import tables from external data sources and create a PivotTable, it is possible to have automatic updates of the values in the PivotTable whenever the data in the connected data sources is updated.

您可以使用多个表中的字段创建一个数据透视表，前提是这些表已经定义了关系。如果不存在关系，Excel 会提示您创建一个，并且您可以从数据透视表本身创建。您定义的这个关系反映在数据模型中。

You can create a PivotTable with the fields from multiple tables, provided the tables have relationships defined. If a relationship does not exist, Excel prompts you to create one and you can do so from the PivotTable itself. The relationship that you so define is reflected in the Data Model.

Exploring Data with PowerPivot

您可以使用 PowerPivot 访问、分析和报告来自各种数据源的数据。PowerPivot 可以帮助您轻松处理大型数据并生成引人入胜的分析报告。

You can use PowerPivot to access, analyze and report data from various data sources. PowerPivot can help you handle large data with ease and produce fascinating analysis reports.

PowerPivot 为您提供了管理数据模型、将 Excel 表添加到数据模型、在数据表中添加计算字段、定义 KPI 等命令。

PowerPivot provides you commands to manage the Data Model, add Excel tables to Data Model, to add calculated fields in the Data Tables, to define KPIs, etc.

Exploring Data with Power View

Power View 提供对大型数据的交互式探索、可视化和分析。由于其多功能可视化选项，您一定可以找到为您的数据提供完美平台的数据，您可以在其中探索数据、汇总和报告。

Power View provides interactive exploration, visualization and analysis of large data. Owing to its versatile visualization options, you can definitely find the one that gives your data the perfect platform wherein you can explore the data, summarize and report.

从表格到地图，它只是可视化您的数据，对其进行筛选、分析和交互式报告的玩法。此外，您可以在同一 Power View 工作表上有多个可视化效果，当您单击其中任何一个数据点的某个数据点时，这些效果会反映和突出显示值。

Ranging from Tables to Maps, it is just a play for you to visualize your data, filter it, analyze it, and report it interactively. Moreover, you can have multiple visualizations on the same Power View sheet that reflect and highlight values, when you click on a data point in any one of them.

您可以使用表格、矩阵、卡片、不同类型的图表、倍数、地图和磁贴在 Power View 中探索数据。一旦您获得实际经验，您将对这些不同视图的多功能性着迷。这是因为它很容易制作突出显示重要值并动态切换视图的交互式报告。

You can explore data in Power View with a table, a matrix, a card, different chart types, multiples, maps and tiles. You will get fascinated with the versatility of these different views once you get hands-on experience. This is because it is easy to produce interactive reports highlighting significant values and dynamically switching across the views.

Exploring Data with Hierarchies

如果您的数据具有层次结构，它们可以在 Power View 中反映的数据模型中定义，或在 Power View 本身中构建层次结构。

If your data has hierarchies, they can be either defined in the Data Model that is reflected in the Power View or build the hierarchies in Power View itself.

一旦定义层次结构，您就可以向上钻取和向下钻取层次结构，显示所需数据。

Once a hierarchy is defined, you can drill-up and drill-down the hierarchy, displaying the required data.

Aesthetic Power View Reports

您可以根据要在 Power View 中显示的内容来制定报告布局。您可以添加反映公司徽标或企业视图的背景图像。或者，您可以设置报告的背景格式，使其看起来更优雅。

You can arrive at a report layout based on what you want to present in Power View. You can add a background image that reflects your company logo or your corporate view. Optionally, you can format the background of the report to give it an elegant look.

您可以为您的报告选择最能描述您的数据的主题。您可以更改字体和文字大小，以便您的报告易于阅读。

You can select a theme for your report that best portrays your data. You can change the font and text size so that your report becomes easily readable.

Key Performance Indicators (KPIs)

关键绩效指标通常用于评估绩效。在 Excel 中，您可以在 PowerPivot 或 Power View 中定义和描绘 KPI。KPI 的图形化呈现会提升您的报表。

Key Performance Indicators are commonly used to gauge the performance. In Excel, you define and portray KPIs in PowerPivot or Power View. The graphical presentation of KPIs will uplift your reports.