Excel有任何地图还是选择功能？ [英] Does Excel have any map or select functions?

问题描述

我有一行字符串值，我想在每个字符串中执行一个 vlookup ，然后计算结果的平均值。如果这是C＃，我会做一个 Select（str => VLookup（str，dict））。Average（），有没有办法在单一excel功能？

I have a row of string values, which I'd like to do a vlookup on each and then compute the average of the results. If this were C#, I'd do a Select( str => VLookup(str,dict)).Average(), is there a way to do this in a single excel function?

我使用版本2010

推荐答案

一般来说，没有。对于您的具体情况，请注意。

In general, no. For your specific case, kind of.

说你有这样的表

a 42
b 2
c 3

如果您使用这样的数组调用VLOOKUP：

in E8:F10. If you call VLOOKUP with an array like this:

=VLOOKUP({"a","c"},E8:F10,2,FALSE)

您将收到一组值：{42,3}。 （输入公式作为数组函数... Ctrl + Shift + Enter。）所以在这种情况下可以做的地图部分。

you'll get back an array of values: {42,3}. (Enter the formula as an array function...Ctrl+Shift+Enter.) So the map part you can do in this case.

不幸的是，AVERAGE不似乎与VLOOKUP返回的数组一起使用，如果将其全部放在一个公式中，并将结果放在一个单元格中。更糟糕的是，它似乎工作，但只是使用第一个元素，即使你输入整个事情作为一个数组公式。也就是说，如果你放了：

Unfortunately, AVERAGE doesn't seem to work with the array that VLOOKUP returns if you put it all in one formula and put the result in one cell. Worse, it appears to work, but just uses the first element, even if you enter the whole thing as an array formula. That is, if you put:

=AVERAGE(VLOOKUP({"a","c"},E8:F10,2,FALSE))

即使你输入数组公式，可以回到42而不是22.5。

in cell H12, even if you enter it as an array formula, you'll get back 42 and not 22.5.

奇怪的是，将相同的公式放在两个单元格中，例如H16：I16，并将其作为数组公式输入，让您返回一个数组有两个要素：{22.5,22.5}。 （这是一个单元素数组扩展到两个单元格的概率。）所以你可以得到你需要的，而不必有一个完整的大中间数组的结果。 （使用数组代替工作表函数的非数组参数可以是wierd。参见：有没有关于使用数组参数调用的内置Excel函数的行为的文档？）

Oddly, putting the same formula in two cells, say H16:I16, and entering it as an array formula gives you back an array with two elements: {22.5, 22.5}. (It's probbaly just a one-element array getting expanded into two cells.) So you can get what you need without having to have a whole large intermediate array of results. (Using arrays in place of non-array arguments to worksheet functions can be wierd. See: Is there any documentation of the behavior of built-in Excel functions called with array arguments? )

当然，类似Excel的方法是使用中间数组，而不是尝试将其压缩成一个花式的数组公式。您将有一个要查找的字符串的列表，然后拖动一个普通的VLOOKUP（绝对参考您的查找表）在并行行/列之间，然后平均结果。

Of course, the more Excel-like way to do this is to use an intermediate array and not try to compress it into a fancy array-formula. You'd have a list of the strings you want to look up, then drag a plain VLOOKUP (with an absolute reference to your lookup table) down/across a parallel row/column, and then average the results.

这篇关于Excel有任何地图还是选择功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！