☰

如何创建以下数据的 Excel 图表

tag-icon • tag-icon microsoft-excel charts microsoft-excel-2016
如何创建以下数据的 Excel 图表

我正在尝试创建一个 Excel 折线图，以动态呈现以下数据（这只是一个示例，数据集更大并且变化很大）：

Name      | Salary change date | Salary
---------------------------------------
John Doe  | 02/01/2021         | $3,500
John Doe  | 07/01/2021         | $3,700
Lucy Liu  | 05/05/2020         | $3,900
Lucy Liu  | 08/07/2021         | $3,950

我想要一个包含多个系列的折线图，每个系列代表一个人，横轴表示时间，纵轴表示薪水，这样我就可以看到所有人随时间变化的薪水。

我尝试了两种方法：经典折线图和数据透视表 + 数据透视图组合。前者不知怎么的根本无法工作。后者几乎有效，但我只能让它在一个数据集上起作用，其中 John Doe 的所有日期都早于 Lucy Liu 的任何日期，但这显然不能解决我的问题。

（我正在运行的确切版本是 Version 2202 Build 16.0.14931.20128 - 我假设它是 Microsoft Excel 2016。）

答案1

大多数 Excel 图表问题的主要挑战是整理数据，以便 Excel 可以创建所需的图表。对于您的情况，您需要：

  • 横轴显示日期（不是时间 - 一个重要的区别）
  • 纵轴显示工资值
  • 每行（系列）显示一个人的工资

首先，确保你的约会确实Excel 中的日期。

日期

接下来重新排列数据，使每行（系列）位于单个列（或行）中。最简单的方法是创建数据透视表。将数据更改为表格Insert > Table

桌子

然后，使用该表作为数据透视表的源Insert > Pivot Table，

  • 列 = 名称
  • 行 = 工资变动日期
  • 价值 = 工资总和

数据透视表

从这里，您可以创建一个数据透视图（与数据透视表绑定的特殊图表），它看起来像这样：

数据透视图

有两个主要问题：露西的数据点之间缺少线以及日期点的间距不一致。

要纠正差距，您需要调整对话框Hidden and Empty Cells中的图表设置Select Data Source。其默认为Gaps，您需要将其更改为Connect data points with line。

没有间隙

另一个问题更具挑战性。在折线图中，Excel 将横轴显示为分类轴 - 这意味着每个条目都具有相同的权重。因此，即使第一个日期和第二个日期（2020-05-05 和 2021-02-01）之间相隔 8 个月，第二个日期和第三个日期（2021-02-01 和 2021-07-01）之间相隔 5 个月，横轴上的距离相同 - 每个刻度 1 个刻度。

要纠正此问题，您需要用空日期值填充数据（如果足够接近，则填充到月份级别；如果很重要，则填充到日期级别）。生成的表格和数据透视表可能如下所示：

空格

图表看起来如下：

带有填充日期的图表

更好的选择是使用 XY/散点图（因为两个轴值都显示为连续数据，这更适合于您上下文中的日期）。不幸的是，数据透视图不能使用 XY/散点图类型。您仍然可以使用数据透视表作为数据源，只需手动执行即可。

  1. 选择一个空白单元格
  2. Insert > Charts > Scatter > Scatter with Straight Lines and Markers
  3. 对于每个数据系列，手动设置：
  • 系列名称 = 数据透视表列标签
  • X 系列 = 数据透视表日期
  • Y 系列 = 透视表适用列的工资值总和
  1. 按上述方法调整Hidden and Empty Cells设置

你的图表看起来应该像这样：

xy 图表

它几乎与数据透视图相同，但不需要额外的填充值和生成的空白系列。

相关内容