在Google Spreadsheets上的数组公式中使用VLOOKUP

Effectively I want to give numeric scores to alphabetic grades and sum them. In Excel, putting the LOOKUP function into an array formula works:

实际上,我想给字母等级的数字分数并加以总结。在Excel中,将LOOKUP函数放入数组公式中可以:

{=SUM(LOOKUP(grades, scoringarray))}

With the VLOOKUP function this does not work (only gets the score for the first grade). Google Spreadsheets does not appear to have the LOOKUP function and VLOOKUP fails in the same way using:

使用VLOOKUP功能,这不起作用(只获得一年级的分数)。 Google Spreadsheets似乎没有LOOKUP功能,VLOOKUP以相同的方式失败:

=SUM(ARRAYFORMULA(VLOOKUP(grades, scoresarray, 2, 0)))

=ARRAYFORMULA(SUM(VLOOKUP(grades, scoresarray, 2, 0)))

Is it possible to do this (but I have the syntax wrong)? Can you suggest a method that allows having the calculation in one simple cell like this rather than hiding the lookups somewhere else and summing them afterwards?

是否可以这样做(但我的语法错误)?你能建议一种方法,允许在一个简单的单元格中进行计算,而不是将查找隐藏在其他地方并在之后对它们求和吗?

4 个解决方案

#1

I'm afraid I think the answer is no. From the help text on http://docs.google.com/support/spreadsheets/bin/answer.py?answer=71291&query=arrayformula&topic=&type=

我担心我认为答案是否定的。来自http://docs.google.com/support/spreadsheets/bin/answer.py?answer=71291&query=arrayformula&topic=&type=上的帮助文字

The real power of ARRAYFORMULA comes when you take the result from one of those computations and wrap it inside a formula that does take array or range arguments: SUM, MAX, MIN, CONCATENATE,

当你从其中一个计算中获取结果并将其包含在一个确实采用数组或范围参数的公式中时,ARRAYFORMULA的真正强大之处在于:SUM,MAX,MIN,CONCATENATE,

As vlookup takes a single cell to lookup (in the first argument) I don't think you can get it to work, without using a separate range of lookups.

由于vlookup需要单个单元格进行查找(在第一个参数中),我不认为你可以让它工作,而不使用单独的查找范围。

#2

Google Spreadsheets does not appear to have the LOOKUP function

Google Spreadsheets似乎没有LOOKUP功能

Presumably not then but it does have now:

可能不是那么,但现在确实有:

grades Sheet1!A2:A4
scoringarray Sheet1!A2:B4

Grade Sheet!A2:A4 scoringarray Sheet1!A2:B4

#3

I still can't see the formulae in your example (just values), but that is exactly what I'm trying to do in terms of the result; obviously I can already do it "by the side" and sum separately - the key for me is doing it in one cell.

我仍然无法在你的例子中看到公式(只是值),但这正是我在结果方面要做的事情;显然我已经可以“在旁边”并且分别总结 - 我的关键是在一个单元格中进行。

I have looked at it again this morning - using the MATCH function for the lookup works in an array formula. But then the INDEX function does not. I have also tried using it with OFFSET and INDIRECT without success. Finally, the CHOOSE function does not seem to accept a cell range as its list to choose from - the range degrades to a single value (the first cell in the range). It should also be noted that the CHOOSE function only accepts 30 values to choose from (according to the documentation). All very annoying. However, I do now have a working solution in one cell: using the CHOOSE function and explicitly listing the result cells one by one in the arguments like this:

我今天早上再次查看它 - 使用MATCH函数进行数组公式中的查找工作。但是INDEX功能却没有。我也尝试过使用OFFSET和INDIRECT而没有成功。最后,CHOOSE函数似乎不接受单元格范围作为其可供选择的列表 - 范围降级为单个值(范围中的第一个单元格)。还应注意,CHOOSE功能仅接受30个值可供选择(根据文档)。一切都很烦人。但是,我现在在一个单元格中有一个工作解决方案:使用CHOOSE函数并在参数中逐个显示结果单元格,如下所示:

=ARRAYFORMULA(SUM(CHOOSE(MATCH(D1:D8,Lookups!$A$1:$A$3,0),
                                     Lookups!$B$1,Lookups!$B$2,Lookups!$B$3)))

Obviously this doesn't extend very well but hopefully the lookup tables are by nature quite fixed. For larger lookup tables it's a pain to type all the cells individually and some people may exceed the limit of 30 cells.

显然,这并没有很好地扩展,但希望查找表本质上是非常固定的。对于较大的查找表,单独键入所有单元格是很痛苦的,有些人可能超过30个单元格的限制。

I would certainly welcome a more elegant solution!

我当然欢迎更优雅的解决方案!

#4

I know this thread is quite old, but I'd been struggling with this same problem for some time. I finally came across a solution (well, Frankenstiened one together). It's only slightly more elegant, but should be able to work with large data sets without trouble.

我知道这个帖子很老了,但是我一直在努力解决同样的问题。我终于找到了一个解决方案(好吧,Frankenstiened一起)。它只是稍微优雅一点,但应该能够毫无困难地处理大型数据集。

The solution uses the following:

该解决方案使用以下内容:

=ARRAYFORMULA(SUM(INDIRECT(ADDRESS(MATCH(), MATCH())))

as a surrogate for the vlookup function.

作为vlookup函数的代理。

I hope this helps someone!

我希望这可以帮助别人!

#1