☰

查找二维范围内的最大值并返回列标题

tag-icon • tag-icon microsoft-excel-2010 range lookup
查找二维范围内的最大值并返回列标题

我一直在努力寻找一种解决方案，以在二维范围内找到最大数字，然后查找最大数字所属的列标题。

例如：
示例表

我正在寻找一个公式来查找范围内的最大数字A2到C5（在本例中9669）。然后查找该数字所属的列（在​​本例中人物C），然后返回该值。

对于一维范围来说似乎足够简单，但我找不到任何可以处理二维的东西。

任何帮助将不胜感激。

答案1

正如微软自己所说：“那么，您想成为真正的 Excel 高级用户吗？在这种情况下，您需要知道如何使用数组公式”。

解决方案包括三个步骤：

  1. 定义一个寻找最大值的矩阵，
  2. 将该矩阵转换为 1 和 0，
  3. 将该矩阵与包含列号的向量相乘。

步骤1：作为一个实验，尝试选择一个等于表格大小的范围，然后输入=<table range> = MAX(<table range>)并确认CTRLSHIFTENTER（输入数组公式的默认键）。

第2步：双减运算符将TRUE/FALSE值转换为 0（表示 FALSE）和 1（表示 TRUE）。

步骤3：使用SUMPRODUCT和COLUMN乘以数组并创建带有列号的向量。

所以你的公式看起来像：（SUMPRODUCT(--( <table range> = MAX( <table range> )) * COLUMN( <table range> ))当然，<table range>用你的表的范围或名称替换。

请注意，此公式返回绝对工作表中找到的最大值的列号。因此，工作表中的 E 列为 5。如果您想要相对的表格中的数字，减去表格左上角单元格的列，再加一。因此，公式变为：SUMPRODUCT(--( <table range> =MAX( <table range> )) * COLUMN( <table range> )) - COLUMN( <left top cell of table> ) + 1

还请注意，最后一个公式使用了数组公式，但其本身不是数组，而是单个值。因此，要输入此公式，只需单击 即可ENTER。

我的工作示例：
使用 SUMPRODUCT 获取 Excel 数组中最大值的列号

答案2

非常好的回答，并且很好地提及了这个--概念。

但是，--没有必要，因为对相等性测试生成的 {TRUE/FALSE} 数组进行任何乘法（或加法等）都会将其内容从 {TRUE/FALSE} 更改为 {1/0}。因此，乘以列号即可解决该问题，而无需使用--。

此外，现在挑剔一下，SUM()可以很好地代替SUMPRODUCT()。只有一个项目需要操作，所以实际上没有数组“产品”方面。无论如何，它都没有以其“自然形式”使用（两个操作数，而不是一个），而只是围绕内部运作的胡须，这使得从内部运作的结果中可以理解。SUM()可以很好地做到这一点，更容易输入，而且“看起来并不吓人”。

很好地使用了COLUMN()。为了将其概括化，以便当通过插入或删除列将范围向右或向左推时使其扩展或收缩，可以使用(COLUMN(B2:D4)-COLUMNS($A2:B4)-1)。

HLOOKUP()最后，使用上面的列标题行中的一项即可找到所要求的信息。

如果按照要求执行最后一项，则上面的列计算可以简化为COLUMN(B2:D4)使用HLOOKUP()A2:D2 作为查找范围（在本例中为 A2:D2），而不是自然需要的 B2:D2。这样做意味着无需进行上述调整，因此... 容易得多。

相关内容