☰

如何创建一个 Excel 公式来查找矩阵中给定值的对应范围？

tag-icon • tag-icon microsoft-excel
如何创建一个 Excel 公式来查找矩阵中给定值的对应范围？

我想创建一个 Microsoft Excel 公式，查找矩阵中给定值的对应范围。

用一个通用的例子来解释

引用表

| Name | From | To |
| A    |    1 |  5 |
| B    |    6 |  8 |
| C    |    9 |  9 |

源表

| Name | Value |
|      |     0 |
|      |     4 |
|      |     9 |
  ^
  Field is getting "calculated".

结果表

| Name | Value |
|      |     0 |
| A    |     4 |
| B    |     6 |
| C    |     9 |
  ^
  Field is getting "calculated".

具体解释

在我的参考表中，我确实有频率信息，其中包括频率范围在哪个频段以及我被允许使用多少瓦特。

在我的源/结果表（相同）中，我有一个频率列表，并希望自动将频段和瓦特信息分配给它。

引用表

|  Band | Frequency Range | Watts |
| 160 m |  1.810 |  1.850 |   100 |
| 160 m |  1.890 |  2.000 |    10 |

预期结果表

             First part is source field for the look up.
             v
|  Band | Frequency Range | Watts | Description |
| 160 m |  1.811 |  1.811 |   100 | Foo Bar     |
| 160 m |  1.891 |  1.897 |    10 | Lorem ipsum |
    ^                         ^
    Field is "calculated".    Field is "calculated".

结论

如何定义（当由于范围而可能时）一个 Excel 公式，该公式从矩阵中选择正确的矩阵参考行，同时从矩阵中查找某个范围内的单个值？

该公式应该与 Excel 97 兼容，但至少与 Excel 2000 兼容。

答案1

基本上，您需要使用VLOOKUP来获取此信息。

首先将您的“来自”列复制/移动到最左边，这样您的列将按以下顺序排列：A（来自）、B（名称）、C（到）。

如果您有连续范围（例如，您可以将示例中的 1.870 视为第一组的成员），那么您的公式（在结果表的 A2 中）：=IFERROR(VLOOKUP(B2,reference_sheet!A:C,2,true),"")

如果您想在频率介于范围之间时获取空值，则公式将更加复杂（仍然根据一般示例使用参考）：

  • 首先获取忽略上限的组的行：=IFERROR(MATCH(B2,reference_sheet!A:C,1),"")- 将其放在例如 C 列中

  • 然后检查你的值是否在范围内：=IFERROR(B2<=index(reference_sheet!C:C,C2),FALSE)

TRUE现在，您在 D 列和E 列中为每个条目提供了行号，INDEX现在您可以使用该函数收集所需的数据。（也可以在没有任何辅助列的情况下完成所有操作，但在这种情况下公式将复杂得多）

答案2

如果您愿意更改参考表，使范围列成为最左边的列，那么该VLOOKUP函数应该可以满足您的要求。由于您尝试匹配范围，而不是特定值，因此您应该确保您的参考表按范围排序，并将其TRUE作为VLOOKUP第 4 个参数传递。

如果您不想重新排列参考表以将范围定位在左侧，那么您可以使用MATCH和INDEX函数的组合来实现类似的结果。

这些函数在Excel 2007及更高版本中均可用。

相关内容