我想绘制某些公司股票价格与标准普尔 500 指数的对比图。我已将所有价格数据下载到 Excel 表中。
我想在 Excel 中创建折线图。一条线是固定的,即 S&P500,另一条线是我选择的公司。我知道如何使用数据验证来创建下拉列表,但我该如何做才能做到当我选择公司 A 时,我只会看到公司 A 的价格数据,而不会看到公司 B、C 等。然后当我选择公司 B 时,我只会看到公司 B 的价格数据和 S&P 价格数据。等等。S&P 线将始终可见,但各个公司的价格线只有在被选中时才会出现。
是否有简单的方法可以规范化所有数据集,使得起点始终为 100?
答案1
在 Excel 中,这实际上非常容易做到。
正确设置您的数据!在我的示例中,我有一列用于公司,还有一列用于您想要绘制图表的每个日期。显然,这可以是从一小时以下到一年的任何时间增量。
将第一个数据行设为标准普尔(或您选择的任何基线)。这是我的样本中的第一个灰色行。
跳过一行(我们稍后会返回)。这是我的样本中的第二行灰色行。
将所有公司添加到图表中并进行比较。
在跳过的行中,在第一列(我的示例中为 A3),使用数据验证列表将所有公司名称拉入下拉列表中。然后,使用 VLOOKUP 填充所有剩余列,以将选定的公司数据拉入各自的单元格。我的公式是
=VLOOKUP($A$3,$A$4:$M$7,COLUMN(B3))
使用 S&P 和目标行中的数据创建图表并按适当格式设置。
现在,您可以选择任何一家公司,并将其与标准普尔(或任何其他基准)进行比较(如果需要,包括正确的标签)。 一些想法:
- 命名范围将使得管理变得更加容易。
- 我喜欢添加所有系列并将其格式化为浅灰色。这样,除了与基线进行比较外,您还可以与所有其他竞争对手进行比较(如我的示例中所示)。
- 如果您想对数据进行规范化,最简单的方法是在原始数据点进行转换,然后绘制规范化值图表。这样您将获得原始数据、规范化数据和图表 - 再次强调,命名范围将为您管理所有这些提供神奇的帮助。
编辑:
要使用水平布局的数据,您需要更改步骤 5 中的公式中的两个内容。您将更改为函数HLOOKUP
,并且需要返回ROW
值而不是 COLUMN。新函数将是:
=HLOOKUP($S$1,$T$1:$W$13,ROW(S2))
灰色的 C 公司仍然使用数据验证从其他列中提取数据(尽管您在这张图片中看不到下拉菜单,但它与上面的垂直版本相同)。数据布局如下:
答案2
参考此链接:
http://www.get-digital-help.com/2011/05/17/excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down-list/
它的教程非常详细,并附有出色的截图。
针对您的情况进行一些修改,
- 为标准普尔添加一个常量数据范围和一个变量,如链接所示
- 选择图表类型为线(很明显)
和附加题一样,
创建另一个表格,其中每个公司的第一个单元格将始终按要求包含 100。
后续单元格应包含公式:值 = (100/第一个数据点值)*相关数据点值
现在这将生成另一个标准化值表,您可以从中绘制图表。
如果您不想让额外的数据弄乱您的工作表,您可以简单地隐藏列。