Есть ли в Excel карта или функции выбора?

Question

У меня есть ряд строковых значений, которые я хотел бы сделать vlookup на каждом, а затем вычислить среднее значение результатов. Если бы это был C#, я бы сделал Select( str => VLookup(str,dict)).Average()Есть ли способ сделать это в одной функции Excel?

Я использую версию 2010

Answer 1

В общем нет. Для вашего конкретного случая, вроде.

Скажем, у вас есть такой стол

a 42
b 2
c 3

в E8:F10. Если вы вызываете VLOOKUP с таким массивом:

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

вы получите массив значений: {42,3}. (Введите формулу в виде функции массива...Ctrl+Shift+Enter.) Так что часть карты вы можете сделать в этом случае.

К сожалению, AVERAGE, похоже, не работает с массивом, который возвращает VLOOKUP, если вы поместите все это в одну формулу и поместите результат в одну ячейку. Хуже того, кажется, что он работает, но использует только первый элемент, даже если вы вводите все это как формулу массива. То есть если поставить:

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

в ячейке H12, даже если вы введете ее как формулу массива, вы получите 42, а не 22,5.

Как ни странно, если поместить одну и ту же формулу в две ячейки, скажем, H16:I16, и ввести ее как формулу массива, то получится массив с двумя элементами: {22.5, 22.5}. (Вероятно, это просто одноэлементный массив, расширяемый в две ячейки.) Таким образом, вы можете получить то, что вам нужно, без необходимости иметь целый большой промежуточный массив результатов. (Использование массивов вместо аргументов, не являющихся массивами, для функций листа может быть странным. См.: Есть ли документация о поведении встроенных функций Excel, вызываемых с аргументами массива?)

Конечно, более Excel-подобный способ сделать это - использовать промежуточный массив, а не пытаться сжать его в причудливую формулу массива. У вас будет список строк, которые вы хотите найти, затем перетащите простой VLOOKUP (с абсолютной ссылкой на вашу таблицу поиска) вниз / поперек параллельной строки / столбца, а затем усредните результаты.