动态图的三维命名范围

Question

我有一张表格，其中包含一个日历年中每天的 10 个变量的值。请参阅附件以供参考。

我正在尝试使用以下选项创建折线图：

1. 通过 5 个差异下拉列表选择 5 个变量中的任意一个（由静态命名范围填充 -完毕）
2. 选择开始日期（包含表中的所有日期的命名范围 -完毕）
3. 选择结束日期（命名范围，日期从上方开始开始日期-完毕）
4. 完成上述操作后，我希望图表显示选定变量在开始日期和结束日期之间的时间段内的 5 条线。-（未完成/无法弄清楚如何实施）

我正在寻找非 VBA/宏解决方案，因为最终用户不擅长使用它在将来进行更改。

Answer 1

关键是将MATCH()功能与OFFSET()职能结合起来。

我制作了一个电子表格来演示这个解决方案，并在下面放上了它的链接。

我编写的单个公式是数组公式，如下所示：

{=OFFSET(Offset_Start, MATCH(Variable_Selector, Variable_Col, 0), MATCH(Start_Date, Date_Row, 0)): OFFSET(Offset_Start, MATCH(Variable_Selector, Variable_Col, 0), MATCH(End_Date, Date_Row, 0))}

下面是它的工作原理

• 这种构造称为动态范围，其中公式返回由函数而不是绝对地址定义的范围。在这种情况下，我们使用来OFFSET()定义公式返回的范围的开始和结束。注意:两个OFFSET()函数之间的。
• Offset_Start位于数据的左上角。在您的屏幕截图中，这是单元格A2
• 由于变量按行排列，您可以使用MATCH()来查找哪一行与所选变量匹配
• 由于日期以列的形式显示，因此您可以使用它MATCH()来查找哪一列与所选日期（开始或结束）匹配
• 确保使用MATCH()第三个参数0，以便返回精确匹配
• OFFSET()使用MATCH()函数找到的行（第二个参数）和列（第三个参数）返回与输入变量和日期相对应的单元格。
• 这是一个数组公式，占据与数据长度相同的行。最终找到的值右侧的所有单元格将是#N/A

我使用 FormulaChop 生成上述公式（完整披露：我编写了 FormulaChop）。这里是 FormulaChop 输出的屏幕截图。这里是一个电子表格的链接，演示了其工作原理。