excel - Excel 中具有可变单元格的计算

Question

我以为我对 Excel 的理解已经足够好了，直到我的老板让我在 excel 中做一些涉及 MATCH 的事情和 INDEX对于我的生活，我无法弄清楚这些功能是如何工作的。也许更合适的是，我无法弄清楚它们在我正在查看的电子表格中是如何工作的。我会仔细地引导你完成我正在尝试做的事情。

我首先创建一个没有问题的下拉列表。下拉列表是本质上是概率表的列表。为了跳过一些不相关的数学运算，每个表都有一个由表中所有数字生成的数字，该数字将用于电子表格其他部分的某些计算中。这些表格中的每一个还将具有与下拉列表中的条目匹配的名称(顶部单元格)。

[如果有人知道如何格式化这个问题，以便更容易理解我的问题，请随时告诉我如何。我不知道如何为 excel 格式化 stackexchange 问题值得任何东西。]

所以最终，我有两个正在执行计算的主要表格。计算本身无关紧要。相关的是，我需要能够在计算中添加一个值，该值可以根据从下拉列表中选择的内容而改变。所以让我试着举个例子。

假设我有这张 table :

|   Month  | Balance | Interest Rate |
|:--------:|:-------:|:-------------:|
|  January |  100.00 |       1%      |
| February |  101.00 |       1%      |
|   March  |  102.01 |       1%      |
|   April  |  103.03 |       1%      |
|    May   |  104.06 |       1%      |
|   June   |  105.10 |       1%      |

我希望利率取决于下拉列表，这样也许我已经设置了从单元格表生成下拉列表的位置，如下所示:

| Interest Rate |
|:-------------:|
|      Low      |
|     Medium    |
|      High     |

我有三个标有“低”、“中”和“高”的表。这些表格中的每一个都将执行一些计算以获得最终的利率结果，该结果将是显示在顶部表格中的数字。因此，如果我选择“高”，我的表格将看起来像这样。

|   Month  | Balance | Interest Rate |
|:--------:|:-------:|:-------------:|
|  January |  100.00 |       5%      |
| February |  105.00 |       5%      |
|   March  |  110.25 |       5%      |
|   April  |  115.76 |       5%      |
|    May   |  121.55 |       5%      |
|   June   |  127.63 |       5%      |

我很确定我需要索引和匹配函数来做到这一点。我什至会输入一个与我正在做的事情接近的公式，但我似乎无法破译一切是如何运作的。
=INDEX($U$13:$BM$416,MATCH(D12,$T$13:$T$416,0),MATCH($A$13,$U$11:$BM$11,0) + 1) * SUM(P:P)
我知道当您看不到工作表时这并没有多大意义，但是该公式的作用非常接近我需要做的事情。我想我最终的问题是是否有人会帮助我了解如何在 Excel 中完成此任务？

编辑:这是一个更好的一瞥

假设我有这 3 个表格，它们显示了各种不同事物的利率(例如汽车贷款、抵押贷款、信用卡)。 “######”只是显示这些单元格中有用于计算底部数字的值(0.01、0.03 等)。还可以说，这 3 个数据表在 excel 中的范围是 A1:I6 .

|              |   DataTable 1   |               |              |   DataTable 2   |               |              |   DataTable 3   |               |
|:------------:|:---------------:|:-------------:|:------------:|:---------------:|:-------------:|:------------:|:---------------:|:-------------:|
| Low,Interest | Medium,Interest | High,Interest | Low,Interest | Medium,Interest | High,Interest | Low,Interest | Medium,Interest | High,Interest |
|--------------|-----------------|---------------|--------------|-----------------|---------------|:-------------|-----------------|---------------|
|    #######   |     #######     |    #######    |    #######   |     #######     |    #######    |    #######   |     #######     |    #######    |
|    #######   |     #######     |    #######    |    #######   |     #######     |    #######    |    #######   |     #######     |    #######    |
|     0.01     |       0.03      |      0.05     |     0.02     |       0.04      |      0.06     |     0.10     |       0.20      |      0.30     |

我在 中有一个下拉列表A8 其中包含数据表 1、数据表 2 和数据表 3 的值。

假设我有另一个表( Range is K1:M14 )，看起来像这个问题中的第一个表。

|   Month   | Balance | Medium Interest |
|:---------:|:-------:|:---------------:|
|  January  | $100.00 |        3%       |
|  February | $103.00 |        3%       |
|   March   | $106.09 |        3%       |
|   April   | $109.27 |        3%       |
|    May    | $112.55 |        3%       |
|    June   | $115.93 |        3%       |
|    July   | $119.41 |        3%       |
|   August  | $122.99 |        3%       |
| September | $126.68 |        3%       |
|  October  | $130.48 |        3%       |
|  November | $134.39 |        3%       |
|  December | $138.42 |        3%       |

我写了一个公式来确定 3% 如何进入“中等兴趣”列。

=INDEX($A$6:$I$6,MATCH($A$8,$A$1:$I$1,0),MATCH($M$2,$A$2:$I$2,0))

当我在下拉列表中选择数据表 1 时，它会起作用。它正确放置了 3%，这是数据表 1 的中等利率，但是当我选择其他 2 个数据表中的任何一个时，我得到一个无效的单元格引用错误。这基本上是我在真实电子表格中需要做的事情。

Answer 1

不确定我能说出你想要的，但我至少可以解释MATCH和 INDEX以及您的公式对它们的作用-希望就足够了!
MATCH(what,in_where,match_type)将返回 what 的索引在数组/范围内 in_where基于match_type . “最佳” match_type是 0 - 就像你的例子一样 - 这意味着“完全匹配”。其他选项是 1对于“小于”和 -1对于“大于” - 两者都需要您 in_where要排序...

所以你第一个例子MATCH(D12,$T$13:$T$416,0)正在寻找 D12 中的确切值在 $T$13:$T$416 范围内.
INDEX(in_where,row,column)将返回数组/范围内的值 in_where在行 row和列column .

在您的示例中，您正在查看范围 $U$13:$BM$416与 MATCH 给出的行/列es...第一个MATCH正在寻找 D12大致在 T 列中第二个是寻找A13大致在行11 . (即，看起来您的“表格”在第 11 行有标题，在 T 列有“键”，您正在搜索它们位置的交集。)+1列中将正确对齐 MATCH 返回的索引和 INDEX 的列号...

没有看到这一点(例如，像保管箱或屏幕抓取这样的文件)很难说更多 - 但我希望这会有所帮助!

顺便说一句 - INDEX & MATCH作为组合也可以很好地替代VLOOKUP如果您的“键”列位于您想要的“值”列的右侧(或者如果您有一个宽表并且其中的任何更改强制重新计算负载)

更新基于问题的第二部分
新示例给出了公式:=INDEX($A$6:$I$6,MATCH($A$8,$A$1:$I$1,0),MATCH($M$2,$A$2:$I$2,0))INDEX将返回给定行/列的值。在此示例中，您的“表格”是单行 $A$6:$I$6所以你只需要给行1在这里-您不是在查看网格，而只是在列表中。

因此，您希望在右侧 DataTable(从下拉列表中选择)中找到兴趣级别(表顶部的低/中/高)的兴趣值。有几种方法可以做到这一点，具体取决于您拥有的控制...

创建一个真实的数据网格，其侧面为低/中/高，顶部为 1/2/3，内部为 % - 然后使用 INDEX按原计划... MATCH行/列的选择

如果这有点多，那么如何为您的表创建一个新的“复合键”......例如第 2 行可能包含 1_Low、1_Medium、1_High、2_Low 等。然后您只使用一次搜索，但使用连接键:=INDEX($A$6:$I$6,1,MATCH($A$8 & "_" & $M$1,$A$2:$I$2,0))

否则，您首先需要在第一个标题行中找到“DataTable 1”并使用它来限制搜索利率水平标题的范围......这有点复杂，并且更多地取决于您的详细信息表(例如，它们的列数是否都相同 - 低/中/高 - 或者是否有一些非常高)