excel - 带有可切换范围的索引和匹配

Question

我有一个简单的INDEX&MATCH Excel 2016 中的成本分配公式。Total cost在 D4分布于 COST DISTRIBUTION表，基于 MARKETING 中设置的比率或 SERVICE表。目前 COST DISTIBUTION 中的公式表是:

=$D$4*INDEX(Marketing_ratio;MATCH(F3;Marketing_area;0))

使用此设置，公式为 固定 至 MARKETING表成本分配逻辑，我 不要希望它成为。在某些情况下，我想使用 SERVICE 中的分配比率。表(可能还有其他几个)，它看起来像:

=$D$4*INDEX(Service_ratio;MATCH(F3;Service_area;0))

理想情况下，我想在 Distribution key 中使用一些值字段来调整公式中正确的命名范围。例如。 - 仍然使用类似的 INDEX&MATCH函数，但更改公式转向的范围。

这可以做到吗？理想情况下没有宏，但是如果 Excel 原生公式不够用，也可以使用宏。

Answer 1

您可以使用 CHOOSE/MATCH 的组合来返回所需的范围...

=$D$4*INDEX(CHOOSE(MATCH($D$10,{"Marketing","Service"},0),Marketing_ratio,Service_ratio),MATCH(F3,CHOOSE(MATCH($D$10,{"Marketing","Service"},0),Marketing_area,Service_area),0))

...其中 D10 包含分发 key ，例如 Marketing 或 Service。