Excel Data Analysis 简明教程

Excel Data Analysis - Data Visualization

您可以在 Excel 中以多种方式显示数据分析报告。但是，如果您的数据分析结果可以可视化为图表，以突出数据中的要点，您的受众可以快速掌握您想要在数据中展示的内容。它还对您的演示文稿样式产生良好影响。

You can display your data analysis reports in a number of ways in Excel. However, if your data analysis results can be visualized as charts that highlight the notable points in the data, your audience can quickly grasp what you want to project in the data. It also leaves a good impact on your presentation style.

在本章中，您将了解如何使用 Excel 图表和图表上的 Excel 格式化功能，使您能够重点展示数据分析结果。

In this chapter, you will get to know how to use Excel charts and Excel formatting features on charts that enable you to present your data analysis results with emphasis.

Visualizing Data with Charts

在 Excel 中，图表用于对任何数据集进行图形表示。图表是数据的可视化表示，其中数据由符号表示，例如条形图中的条形或折线图中的线。Excel 为您提供了许多图表类型，您可以选择一种适合您数据的图表，或者您可以使用 Excel 推荐的图表选项来查看根据您的数据自定义的图表，然后选择其中一个。

In Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of the data, in which the data is represented by symbols such as bars in a Bar Chart or lines in a Line Chart. Excel provides you with many chart types and you can choose one that suits your data or you can use the Excel Recommended Charts option to view charts customized to your data and select one of those.

有关图表类型的更多信息，请参阅教程 Excel 图表。

Refer to the Tutorial Excel Charts for more information on chart types.

在本章中，您将了解可以使用 Excel 图表的高亮显示数据分析结果的不同技术。

In this chapter, you will understand the different techniques that you can use with the Excel charts to highlight your data analysis results more effectively.

Creating Combination Charts

假设您从不同区域获得的 2015-2016 财年的目标利润和实际利润。

Suppose you have the target and actual profits for the fiscal year 2015-2016 that you obtained from different regions.

creating combination charts

我们对这些结果创建一个簇状柱形图。

We will create a Clustered Column Chart for these results.

clustered column chart

正如您所观察到的，在图表中难以快速可视化目标与实际之间的比较。它没有对您的结果产生真正的影响。

As you observe, it is difficult to visualize the comparison quickly between the targets and actual in this chart. It does not give a true impact on your results.

区分两种类型数据以比较值的一种更佳方法是使用组合图表。在 Excel 2013 及更高版本中，您可以将组合图表用于相同目的。

A better way of distinguishing two types of data to compare the values is by using Combination Charts. In Excel 2013 and versions above, you can use Combo charts for the same purpose.

对目标值使用垂直列，对实际值使用带标记的直线。

Use Vertical Columns for the target values and a Line with Markers for the actual values.

  1. Click the DESIGN tab under the CHART TOOLS tab on the Ribbon.

  2. Click Change Chart Type in the Type group. The Change Chart Type dialog box appears.

use vertical columns

  1. Click Combo.

  2. Change the Chart Type for the series Actual to Line with Markers. The preview appears under Custom Combination.

  3. Click OK.

change chart type

您自定义的组合图表将显示。

Your Customized Combination Chart will be displayed.

customized combination chart

正如您在图表中观察到的，目标值为列，实际值沿线标记。数据可视化变得更好，因为它还显示结果的趋势。

As you observe in the chart, the Target values are in Columns and the Actual values are marked along the line. The data visualization has become better as it also shows you the trend of your results.

但是，当两种数据值的数据范围有显着差异时，这种类型的表示效果不佳。

However, this type of representation does not work well when the data ranges of your two data values vary significantly.

Creating a Combo Chart with Secondary Axis

假设您有 2015-2016 财政年度不同地区所发货的产品数量和实际利润数据。

Suppose you have the data on the number of units of your product that was shipped and the actual profits for the fiscal year 2015-2016 that you obtained from different regions.

combo chart

如果您使用与之前相同的组合图表，您将得到以下内容 -

If you use the same combination chart as before, you will get the following −

combination chart

在图表中， No. of Units 的数据不可见，因为数据范围有显着差异。

In the chart, the data of No. of Units is not visible as the data ranges are varying significantly.

在这种情况下，您可以创建具有辅助轴的组合图表，以便主轴显示一个范围，辅助轴显示另一个范围。

In such cases, you can create a combination chart with secondary axis, so that the primary axis displays one range and the secondary axis displays the other.

  1. Click the INSERT tab.

  2. Click Combo in Charts group.

  3. Click Create Custom Combo Chart from the drop-down list.

custom combo chart

插入图表对话框出现，其中组合突出显示。

The Insert Chart dialog box appears with Combo highlighted.

对于图表类型，选择 -

For Chart Type, choose −

标记线用于单位数量系列

. Line with Markers for the Series No. of Units

聚类柱用于实际利润系列

. Clustered Column for the Series Actual Profits

勾选单位数量系列右侧的辅助轴框并点击确定。

. Check the Box Secondary Axis to the right of the Series No. of Units and click OK.

图表预览将出现在自定义组合中。

A preview of your chart appears under Custom Combination.

custom combination

您的组合图表将显示在辅助轴上。

Your Combo chart appears with Secondary Axis.

combo chart with secondary axis

您可以在主轴上看到实际利润值，在辅轴上可以看到单位数。

You can observe the values for Actual Profits on the primary axis and the values for No. of Units on the secondary axis.

图表中的一个显著观察结果是第 3 季度，其中售出的单位数更多，但获得的实际利润更低。这可能应该归因于为增加销售而产生的促销成本。第 4 季度的情况有所改善，销售额略有下降，实际利润大幅上升。

A significant observation in the above chart is for Quarter 3 where No. of Units sold is more, but the Actual Profits made are less. This could probably be assigned to the promotion costs that were incurred to increase sales. The situation is improved in Quarter 4, with a slight decrease in sales and a significant rise in the Actual Profits made.

Discriminating Series and Category Axis

假设您想预测 2013-2016 年间获得的实际利润。

Suppose you want to project the Actual Profits made in Years 2013-2016.

discriminating series and category axis

为此数据创建一个簇状柱形图。

Create a clustered column for this data.

clustered column

您观察到，数据可视化效果不佳，因为没有显示年份。您可以通过将年份更改为类别来克服此问题。

As you observe, the data visualization is not effective as the years are not displayed. You can overcome this by changing year to category.

删除数据范围中的标题年份。

Remove the header year in the data range.

remove header year

现在，年份被视为一个类别而不是一个系列。您的图表如下所示 −

Now, year is considered as a category and not a series. Your chart looks as follows −

year as category

Chart Elements and Chart Styles

图表元素为您的图表提供了更多描述，从而帮助更直观地可视化数据。

Chart Elements give more descriptions to your charts, thus helping visualizing your data more meaningfully.

  1. Click the Chart

图表右上角附近会出现三个按钮 −

Three buttons appear next to the upper-right corner of the chart −

  1. Chart Elements

  2. Chart Styles

  3. Chart Filters

有关这些按钮的详细说明，请参阅 Excel 图表教程。

For a detailed explanation of these, refer to Excel Charts tutorial.

  1. Click Chart Elements.

  2. Click Data Labels.

click data labels

  1. Click Chart Styles

  2. Select a Style and Color that suits your data.

select style and color

您可以使用趋势线以图形方式显示数据中的趋势。您可以在图表中将趋势线延伸到实际数据之外，以预测未来值。

You can use Trendline to graphically display trends in data. You can extend a Trendline in a chart beyond the actual data to predict future values.

trendline

Data Labels

Excel 2013 及更高版本为您提供了多种选项来显示数据标签。您可以选择一个数据标签，按您喜欢的方式设置格式，然后使用克隆当前标签将格式复制到图表中的其他数据标签。

Excel 2013 and later versions provide you with various options to display Data Labels. You can choose one Data Label, format it as you like, and then use Clone Current Label to copy the formatting to the rest of the Data Labels in the chart.

图表中的数据标签可以具有效果、不同的形状和大小。

The Data Labels in a chart can have effects, varying shapes and sizes.

还可以使用“插入数据标签字段”将单元格的内容显示为数据标签的一部分。

It is also possible to display the content of a cell as part of the Data Label with Insert Data Label Field.

data label

Quick Layout

您可以使用快速布局通过选择一个预定义的布局选项来快速更改图表总体布局。

You can use Quick Layout to change the overall layout of the chart quickly by choosing one of the predefined layout options.

  1. Click the chart.

  2. Click the DESIGN tab under CHART TOOLS.

  3. Click Quick Layout.

将显示不同的可能布局。在布局选项上移动时，图表布局将更改为特定的选项。

Different possible layouts will be displayed. As you move on the layout options, the chart layout changes to that particular option.

quick layout

选择您喜欢的布局。图表将显示所选布局。

Select the layout you like. The chart will be displayed with the chosen layout.

Using Pictures in Column Charts

使用图片代替列，可以更强调您的数据展示。

You can create more emphasis on your data presentation by using a picture in place of columns.

  1. Click on a Column on the Column Chart.

  2. In the Format Data Series, click on Fill.

  3. Select Picture.

  4. Under Insert picture from, provide the filename or optionally clipboard if you had copied an image earlier.

using pictures

您选择图片会出现在图表中的列中。

The picture you have chosen will appear in place of columns in the chart.

place pictures

Band Chart

您可能需要显示来自不同区域的产品的客户调查结果。带状图适合此目的。带状图是线形图，带有阴影区域，可显示组数据的上限和下限。

You might have to present customer survey results of a product from different regions. Band Chart is suitable for this purpose. A Band Chart is a Line Chart with an added shaded area to display the upper and lower boundaries of groups of data.

假设您东部和西部地区的客户调查结果按月分布为 -

Suppose your customer survey results from the east and west regions, month wise are −

band chart

< 50% 的数据为低，50% - 80% 为中等，> 80% 为高。

Here, in the data < 50% is Low, 50% - 80% is Medium and > 80% is High.

使用带状图，您可以如下显示调查结果 -

With Band Chart, you can display your survey results as follows −

display survey results

根据您的数据创建一个线形图。

Create a Line Chart from your data.

create line chart

将图表类型改为 -

Change the chart type to −

  1. East and West Series to Line with Markers.

  2. Low, Medium and High Series to Stacked Column.

change the chart type

您的图表如下所示。

Your chart looks as follows.

chart looks

  1. Click on one of the columns.

  2. Change gap width to 0% in Format Data Series.

formating data series

您将获得带状而不是列。

You will get Bands instead of columns.

bands

让图表更美观 -

To make the chart more presentable −

  1. Add Chart Title.

  2. Adjust Vertical Axis range.

  3. Change the colors of the bands to Green-Yellow-Red.

  4. Add Labels to bands.

最终结果是具有定义边界和跨带状显示调查结果的带状图。可以快速且清楚地从图表中看出，虽然西部地区的调查结果令人满意，但东部地区的调查结果在上一季度下降，需要引起注意。

The final result is the Band Chart with the defined boundaries and the survey results represented across the bands. One can quickly and clearly make out from the chart that while the survey results for the region West are satisfactory, those for the region East have a decline in the last quarter and need attention.

band charts

Thermometer Chart

如果需要表示目标值和实际值，你可以轻松地在 Excel 中创建温度计图表，该图表可以直观地显示这些值。

When you have to represent a target value and an actual value, you can easily create a Thermometer Chart in Excel that emphatically shows these values.

使用温度计图表，你可以以下列方式显示数据——

With Thermometer chart, you can display your data as follows −

thermometer chart

按照以下方式排列数据——

Arrange your data as shown below −

arrange your data

  1. Select the data.

  2. Create a Clustered Column chart.

clustered charts

你会观察到，右侧的条形图就是目标。

As you observe, the right side Column is Target.

  1. Click on a Column in the chart.

  2. Click on Switch Row/Column on the Ribbon.

target

  1. Right click on the Target Column.

  2. Click on Format Data Series.

  3. Click on Secondary Axis.

secondary axis

你会观察到主要轴和次要轴的范围不同。

As you observe the Primary Axis and Secondary Axis have different ranges.

  1. Right click the Primary Axis.

  2. In the Format Axis options, under Bounds, type 0 for Minimum and 1 for Maximum.

  3. Repeat the same for Secondary Axis.

primary and secondary axis have different ranges

主要轴和次要轴都将设置为 0% - 100%。目标列隐藏实际列。

Both Primary Axis and Secondary Axis will be set to 0% - 100%. The Target Column hides the Actual Column.

  1. Right click the visible column (Target)

  2. In the Format Data Series, select No fill for FILLSolid line for BORDERBlue for Color

target column

  1. In Chart Elements, unselect Axis → Primary HorizontalAxis → Secondary VerticalGridlinesChart Title

  2. In the chart, right click on Primary Vertical Axis

  3. In Format Axis options, click on TICK MARKS

  4. For Major type, select Inside

format axis

  1. Right click on the Chart Area.

  2. In the Format Chart Area options, select No fill for FILLNo line for BORDER

chart area

调整图表区域大小，获得温度计形状。

Resize the chart area, to get the shape of a thermometer.

thermometer shape

获得您的温度计图表，显示实际值和目标值。您可以通过一些格式设置使此温度计图表更加引人注目。

You got your thermometer chart, with the actual value as against target value being shown. You can make this thermometer chart more impressive with some formatting.

  1. Insert a rectangle shape superimposing the blue rectangular part in the chart.

  2. In Format Shape options, select − Gradient fill for FILLLinear for Type1800 for Angle

  3. Set the Gradient stops at 0%, 50% and 100%.

  4. For the Gradient stops at 0% and 100%, choose the color black.

  5. For the Gradient stop at 50%, choose the color white.

format shape

  1. Insert an oval shape at the bottom.

  2. Format shape with same options.

结果是我们开始使用的温度计图表。

The result is the Thermometer Chart that we started with.

thermometer chart result

Gantt Chart

甘特图是一个图表，其中一系列水平线显示在特定时间段内完成的工作量，相对于这些时间段计划的工作量。

A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time in relation to the amount of work planned for those periods.

在 Excel 中，您可以通过自定义堆积柱形图类型来创建甘特图，以便它描述任务、任务持续时间和层次结构。Excel 甘特图通常使用天作为水平轴上的时间单位。

In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type so that it depicts tasks, task duration, and hierarchy. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis.

请考虑以下数据，其中该列：

Consider the following data where the column −

  1. Task represents the Tasks in the project

  2. Start represents number of days from the Start Date of the project

  3. Duration represents the duration of the Task

请注意，任何任务的开始都是前一个任务的开始时间加上持续时间。当任务属于分层级结构时就是这种情况。

Note that Start of any Task is Start of previous Task + Duration. This is the case when the Tasks are in hierarchy.

gantt chart

  1. Select the data.

  2. Create Stacked Bar Chart.

stacked bar chart

  1. Right-click on Start Series.

  2. In Format Data Series options, select No fill.

format data series

  1. Right-click on Categories Axis.

  2. In Format Axis options, select Categories in reverse order.

select categories

  1. In Chart Elements, deselect LegendGridlines

  2. Format the Horizontal Axis to Adjust the rangeMajor Tick Marks at 5 day intervalsMinor Tick Marks at 1 day intervals

  3. Format Data Series to make it look impressive

  4. Give a Chart Title

chart title

Waterfall Chart

瀑布图是小型的和大企业最流行的可视化工具之一。瀑布图通过剖析正负贡献的累积效应，是展示如何达成净值（如净收入）的理想之选。

Waterfall Chart is one of the most popular visualization tools used in small and large businesses. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions.

Excel 2016 提供瀑布图类型。如果您仍使用较早版本的 Excel，则可以使用堆积柱形图创建瀑布图。

Excel 2016 provides Waterfall Chart type. If you are using earlier versions of Excel, you can still create a Waterfall Chart using Stacked Column Chart.

柱形以彩色编码，以便您可以快速识别正数和负数。初始值和最终值柱形从横轴开始，而中间值则是浮动柱形。由于这种外观，瀑布图也称为桥形图。

The columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns. Because of this look, Waterfall Charts are also called Bridge Charts.

考虑以下数据。

Consider the following data.

waterfall chart

  1. Prepare the data for Waterfall Chart

  2. Ensure the column Net Cash Flow is to the left of the Months Column (This is because you will not include this column while creating the chart)

  3. Add 2 columns – Increase and Decrease for positive and negative cash flows respectively

  4. Add a column Start - the first column in the chart with the start value in the Net Cash Flow

  5. Add a column End - the last column in the chart with the end value in the Net Cash Flow

  6. Add a column Float – that supports the intermediate columns

  7. Compute the values for these columns as follows

compute values

  1. In the Float column, insert a row in the beginning and at the end. Place n arbitrary value 50000. This just to have some space to the left and right of the chart

数据将如下所示。

The data will be as follows.

float column

  1. Select the cells C2:H18 (Exclude Net Cash Flow column)

  2. Create Stacked Column Chart

stacked column chart

  1. Right click on the Float Series.

  2. Click Format Data Series.

  3. In Format Data Series options, select No fill.

select no fill

  1. Right click on Negative Series.

  2. Select Fill Color as Red.

negative series

  1. Right click on Positive Series.

  2. Select Fill Color as Green.

positive series

  1. Right click on Start Series.

  2. Select Fill Color as Grey.

  3. Right click on End Series.

  4. Select Fill Color as Grey.

  5. Delete the Legend.

delete legend

  1. Right click on any Series

  2. In Format Data Series options, select Gap Width as 10% under Series Options

series options

为图表命名。瀑布图将显示。

Give the Chart Title. The Waterfall Chart will be displayed.

deplays waterfall chart

Sparklines

火花线是放置在单个单元格中的微型图表，每个单元格表示您选择中的数据一行。它们提供了一种快速查看趋势的方法。

Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends.

您可以使用快速分析工具添加火花线。

You can add Sparklines with Quick Analysis tool.

  1. Select the data for which you want to add Sparklines.

  2. Keep an empty column to the right side of the data for the Sparklines.

sparkline

快速分析按钮显示在您选定数据的右下角。

Quick Analysis button appears at the bottom right of your selected data.

quick analysis button at bottom

  1. Click on the Quick Analysis button. The Quick Analysis Toolbar appears with various options.

quick analysis toolbar options

单击 SPARKLINES 。显示的图表选项基于数据，可能有所不同。

Click SPARKLINES. The chart options displayed are based on the data and may vary.

click sparklines

单击 Line 。每行的折线图显示在数据右侧的列中。

Click Line. A Line Chart for each row is displayed in the column to the right of the data.

line

PivotCharts

数据透视表用于以图形方式汇总数据并探索复杂的数据。

Pivot Charts are used to graphically summarize data and explore complicated data.

数据透视表以与标准图表相同的方式显示数据系列、类别和图表轴。此外，它还直接在图表中提供交互式筛选控件，以便您可以快速分析您数据的一个子集。

A PivotChart shows Data Series, Categories, and Chart Axes the same way a standard chart does. Additionally, it also gives you interactive filtering controls right on the chart so that you can quickly analyze a subset of your data.

当您在巨大的数据透视表中具有数据，或许多包含文本和数字的复杂工作表数据时，数据透视表很有用。数据透视表可以帮助您理解这些数据。

PivotCharts are useful when you have data in a huge PivotTable, or many complex worksheet data that includes text and numbers. A PivotChart can help you make sense of this data.

您可以从以下位置创建数据透视表

You can create a PivotChart from

  1. A PivotTable.

  2. A Data Table as a standalone without PivotTable.

PivotChart from PivotTable

遵循以下步骤创建枢纽图表 −

To create a PivotChart follow the steps given below −

  1. Click the PivotTable.

  2. Click ANALYZE under PIVOTTABLE TOOLS on the Ribbon.

  3. Click on PivotChart. The Insert Chart dialog box appears.

insert chart

从选项列中选择“簇状柱形”。

Select Clustered Column from the option Column.

select clustered column

单击确定。显示枢纽图表。

Click OK. The PivotChart is displayed.

display pivotchart

枢纽图表有三个筛选器——地区、销售人员和月份。

The PivotChart has three filters – Region, Salesperson and Month.

  1. Click the Region Filter Control option. The Search Box appears with the list of all Regions. Check boxes appear next to Regions.

  2. Select East and South options.

filter

筛选后的数据显示在枢纽图表和数据透视表上。

The filtered data appears on both the PivotChart and the PivotTable.

pivotchart without pivottable

PivotChart without a PivotTable

您可以在不创建数据透视表的情况下创建单独的枢纽图表。

You can create a standalone PivotChart without creating a PivotTable.

  1. Click the Data Table.

  2. Click the Insert tab.

  3. Click PivotChart in Charts group. The Create PivotChart window appears.

  4. Select the Table/Range.

  5. Select the Location where you want the PivotChart to be placed.

您可以在现有工作表本身的单元格中，或者在新的工作表中进行选择。单击确定。

You can choose a cell in the existing worksheet itself, or in a new worksheet. Click OK.

choose cell

空的枢纽图表、空的数据透视表和用于构建枢纽图表的枢纽图表字段列表同时出现。

An empty PivotChart and an empty PivotTable appear along with the PivotChart Field List to build the PivotChart.

empty pivottable

  1. Choose the Fields to be added to the PivotChart

  2. Arrange the Fields by dragging them into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES

  3. Use the Filter Controls on the PivotChart to select the Data to be placed on the PivotChart

Excel 将自动创建一个耦合的数据透视表。

Excel will automatically create a coupled PivotTable.

coupled pivottable