查找列值Excel [英] Find column value Excel

问题描述

我有一个在Excel中看起来像这样的数据:

I have a data that looks like this in Excel:

A   17  8,5 5,666666667 4,25    3,4 2,833333333 2,428571429
B   5   2,5 1,666666667 1,25    1   0,833333333 0,714285714
C   5   2,5 1,666666667 1,25    1   0,833333333 0,714285714
G   4   2   1,333333333 1       0,8 0,666666667 0,571428571

我想找到一个代表特定值的字母.例如，如果我搜索"17"，我想获得输出"A"，如果我正在寻找0,8，我想得到"G".谁能帮我吗?

I want to find a the letter representing a specific value. For instance, if I search for "17" I want to get the output "A" and if I am looking for 0,8 I want to get "G". Can anyone help me?

(实际上，我想从最大到最小找到数据中的所有值并找到对应的字母)

(In reality I want to find sort all the values in the data from the largest to the lowest and find the corresponding letter for the value)

推荐答案

=INDEX($A$1:$A$4,AGGREGATE(14,6,ROW($B$1:$H$4)/(L3=$B$1:$H$4),1))

假设您的示例数据在A1:H4范围内

assuming your example data is in the A1:H4 Range

当您可以找到重复的值时，此公式将返回最大行号中的一个.如果需要第一行中的值，可以将14更改为15.

When you have duplicate value that can be found this formula will return the one in the largest row number. If you want the value in the first row number you can change the 14 to 15.

=INDEX($A$1:$A$21,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21),1))

选项2

=INDEX($A$18:$A$21,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21),1)-ROW($A$18)+1)

移动时遇到的问题很可能是因为范围参考发生了变化.从聚合检查返回的行号必须是索引范围内的行.因此，选项1可使索引范围较大，但要匹配汇总结果中所有可能的行.方法2将范围缩小为仅包含您的数据，并调整结果以减去数据之前的行数，因此它实际上是指您要查看的索引范围内的哪一行.

The problem you had when you moved it was most likely because of your range reference changes. The row number that is returned from the aggregate check has to be a row within the index range. So option one keep the index range large but matches all possible rows from the aggregate results. Option two reduces the range to just your data and adjust the results to subtract the number of rows before your data so its really referring to which row within the index range you want to look at.

感谢Scott Craner指出，在聚合函数之外，完整的列引用是完全可以接受的，您还可以使用:

Thanks to Scott Craner indicating the full column reference is perfectly acceptable outside the aggregate function, you could also use:

=INDEX($A:$A,AGGREGATE(14,6,ROW($B$18:$L$21)/(L3=$B$18:$L$21‌​),1)

这篇关于查找列值Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！