如何制作一个下拉列表以便...（查看详细信息）

Question

我想绘制某些公司股票价格与标准普尔 500 指数的对比图。我已将所有价格数据下载到 Excel 表中。

我想在 Excel 中创建折线图。一条线是固定的，即 S&P500，另一条线是我选择的公司。我知道如何使用数据验证来创建下拉列表，但我该如何做才能做到当我选择公司 A 时，我只会看到公司 A 的价格数据，而不会看到公司 B、C 等。然后当我选择公司 B 时，我只会看到公司 B 的价格数据和 S&P 价格数据。等等。S&P 线将始终可见，但各个公司的价格线只有在被选中时才会出现。

是否有简单的方法可以规范化所有数据集，使得起点始终为 100？

Answer 1

在 Excel 中，这实际上非常容易做到。

1. 正确设置您的数据！在我的示例中，我有一列用于公司，还有一列用于您想要绘制图表的每个日期。显然，这可以是从一小时以下到一年的任何时间增量。

2. 将第一个数据行设为标准普尔（或您选择的任何基线）。这是我的样本中的第一个灰色行。

3. 跳过一行（我们稍后会返回）。这是我的样本中的第二行灰色行。

4. 将所有公司添加到图表中并进行比较。

5. 在跳过的行中，在第一列（我的示例中为 A3），使用数据验证列表将所有公司名称拉入下拉列表中。然后，使用 VLOOKUP 填充所有剩余列，以将选定的公司数据拉入各自的单元格。我的公式是

   =VLOOKUP($A$3,$A$4:$M$7,COLUMN(B3))

6. 使用 S&P 和目标行中的数据创建图表并按适当格式设置。

现在，您可以选择任何一家公司，并将其与标准普尔（或任何其他基准）进行比较（如果需要，包括正确的标签）。 一些想法：

• 命名范围将使得管理变得更加容易。
• 我喜欢添加所有系列并将其格式化为浅灰色。这样，除了与基线进行比较外，您还可以与所有其他竞争对手进行比较（如我的示例中所示）。
• 如果您想对数据进行规范化，最简单的方法是在原始数据点进行转换，然后绘制规范化值图表。这样您将获得原始数据、规范化数据和图表 - 再次强调，命名范围将为您管理所有这些提供神奇的帮助。

编辑： 要使用水平布局的数据，您需要更改步骤 5 中的公式中的两个内容。您将更改为函数HLOOKUP，并且需要返回ROW值而不是 COLUMN。新函数将是：

=HLOOKUP($S$1,$T$1:$W$13,ROW(S2))

灰色的 C 公司仍然使用数据验证从其他列中提取数据（尽管您在这张图片中看不到下拉菜单，但它与上面的垂直版本相同）。数据布局如下：

Answer 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/
它的教程非常详细，并附有出色的截图。

针对您的情况进行一些修改，

1. 为标准普尔添加一个常量数据范围和一个变量，如链接所示
2. 选择图表类型为线（很明显）

和附加题一样，

创建另一个表格，其中每个公司的第一个单元格将始终按要求包含 100。
后续单元格应包含公式：值 = (100/第一个数据点值)*相关数据点值
现在这将生成另一个标准化值表，您可以从中绘制图表。
如果您不想让额外的数据弄乱您的工作表，您可以简单地隐藏列。