Excel Data Analysis 简明教程

Excel Data Analysis - Overview

Excel 提供了简化数据分析任务的命令、函数和工具。使用 Excel，您可以避免许多耗时的和/或复杂的计算。在本教程中，您将抢先了解如何使用 Excel 执行数据分析。您将通过相关的示例、逐步了解 Excel 命令的使用方法以及每个步骤的屏幕截图。

Excel provide commands, functions and tools that make your data analysis tasks easy. You can avoid many time consuming and/or complex calculations using Excel. In this tutorial, you will get a head start on how you can perform data analysis with Excel. You will understand with relevant examples, step by step usage of Excel commands and screen shots at every step.

Ranges and Tables

您拥有的数据可以位于范围或表中。可以对数据执行某些操作，无论数据位于范围中还是位于表中。

The data that you have can be in a range or in a table. Certain operations on data can be performed whether the data is in a range or in a table.

但是，当数据位于表中而不是位于范围内时，某些操作会更有效。还有一些操作是专门针对表的。

However, there are certain operations that are more effective when data is in tables rather than in ranges. There are also operations that are exclusively for tables.

您还可以了解分析范围和表中数据的方法。您将了解如何命名范围、使用名称和管理名称。对于表中的名称也是如此。

You will understand the ways of analyzing data in ranges and tables as well. You will understand how to name ranges, use the names and manage the names. The same would apply for names in the tables.

Data Cleaning – Text Functions, Dates and Times

您需要清理从各个来源获取的数据，并在继续进行数据分析之前对其进行结构化。您将学习如何清理数据。

You need to clean the data obtained from various sources and structure it before proceeding to data analysis. You will learn how you can clean the data.

  1. With Text Functions

  2. Containing Date Values

  3. Containing Time Values

Conditional Formatting

Excel 为您提供了条件格式命令，使您可以根据预定义的条件为单元格或字体着色，并在单元格中的值的旁边放置符号。这有助于人们可视化重要值。您将了解用于有条件格式化单元格的各种命令。

Excel provides you conditional formatting commands that allow you to color the cells or font, have symbols next to values in the cells based on predefined criteria. This helps one in visualizing the prominent values. You will understand the various commands for conditionally formatting the cells.

Sorting and Filtering

在准备数据分析和/或显示某些重要数据期间，您可能必须对数据进行排序和/或筛选。您可以使用 Excel 中易于使用的排序和筛选选项执行相同操作。

During the preparation of data analysis and/or to display certain important data, you might have to sort and/or filter your data. You can do the same with the easy to use sorting and filtering options that you have in Excel.

Subtotals with Ranges

如你所知，数据透视表通常用于汇总数据。但是，子总计（带范围）是 Excel 提供的另一种允许你对数据进行分组/取消分组并使用简单步骤汇总范围中数据的特性。

As you are aware, PivotTable is normally used to summarize data. However, Subtotals with Ranges is another feature provided by Excel that will allow you to group / ungroup data and summarize the data present in ranges with easy steps.

Quick Analysis

使用 Excel 中的快速分析工具，你可以快速执行各种数据分析任务并快速查看结果的可视化内容。

With Quick Analysis tool in Excel, you can quickly perform various data analysis tasks and make quick visualizations of the results.

Understanding Lookup Functions

Excel 查询函数使你能够从大量数据中查找与已定义条件相匹配的数据值。

Excel Lookup Functions enable you to find the data values that match a defined criteria from a huge amount of data.

PivotTables

使用数据透视表，你可以汇总数据、动态准备报告，方法是更改数据透视表的内容。

With PivotTables you can summarize the data, prepare reports dynamically by changing the contents of the PivotTable.

Data Visualization

你将学习使用 Excel 图表进行多种数据可视技术。你还将学习如何创建带状图、温度计图、甘特图、瀑布图、迷你图和数据透视图表。

You will learn several Data Visualization techniques using Excel Charts. You will also learn how to create Band Chart, Thermometer Chart, Gantt chart, Waterfall Chart, Sparklines and PivotCharts.

Data Validation

可能需要在某些单元格中仅输入有效值。否则，可能导致计算不正确。利用数据验证命令，你可以轻松为单元格设置数据验证值、提示用户输入单元格内容的输入信息、根据已定义的条件验证输入的值，以及在输入不正确时显示错误信息。

It might be required that only valid values be entered into certain cells. Otherwise, they may lead to incorrect calculations. With data validation commands, you can easily set up data validation values for a cell, an input message prompting the user on what is expected to be entered in the cell, validate the values entered with the defined criteria and display an error message in case of incorrect entries.

Financial Analysis

Excel 为你提供了多个财务函数。但是，对于需要财务分析的常见问题，你可以学习如何结合使用这些函数。

Excel provides you several financial functions. However, for commonly occurring problems that require financial analysis, you can learn how to use a combination of these functions.

Working with Multiple Worksheets

在多个工作表中，你可能需要执行多个相同计算。不用在每个工作表中重复这些计算，你可以执行一次计算，并让它也出现在其他选定工作表中。你还可以将多个工作表中的数据汇总到一份报告工作表中。

You might have to perform several identical calculations in more than one worksheet. Instead of repeating these calculations in each worksheet, you can do it one worksheet and have it appear in the other selected worksheets as well. You can also summarize the data from the various worksheets into a report worksheet.

Formula Auditing

使用公式时，你可能需要检查这些公式是否按预期工作。Excel 中的公式审核命令可帮助你跟踪先例和从属值，以及检查错误。

When you use formulas, you might want to check whether the formulas are working as expected. In Excel, Formula Auditing commands help you in tracing the precedent and dependent values and error checking.

Inquire

Excel 还提供 Inquire 加载项，使你能够比较两个工作簿以识别更改、创建交互式报告以及查看工作簿、工作表和单元格之间的关系。你还可以清除工作表中的多余格式，这可能使 Excel 运行缓慢或导致文件大小过大。

Excel also provides Inquire add-in that enables you compare two workbooks to identify changes, create interactive reports, and view the relationships among workbooks, worksheets, and cells. You can also clean the excessive formatting in a worksheet that makes Excel slow or makes the file size huge.