我正在尝试创建一个 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/散点图类型。您仍然可以使用数据透视表作为数据源,只需手动执行即可。
- 选择一个空白单元格
Insert > Charts > Scatter > Scatter with Straight Lines and Markers
- 对于每个数据系列,手动设置:
- 系列名称 = 数据透视表列标签
- X 系列 = 数据透视表日期
- Y 系列 = 透视表适用列的工资值总和
- 按上述方法调整
Hidden and Empty Cells
设置
你的图表看起来应该像这样:
它几乎与数据透视图相同,但不需要额外的填充值和生成的空白系列。