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

如何创建以下数据的 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 图表

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

相关内容