Excel 2007: поиск формул массива с использованием INDEX, а MATCH просматривает только первую строку

Question

У меня есть рабочий лист "2011-11" с серией чисел в столбце C. Каждое число в определенной валюте, идентифицируемое кодом в столбце B. Я хочу написать одну формулу (в ячейке). C14), который преобразует каждое число (из C4:C13) в фунты стерлингов (GBP) путем деления его на соответствующий обменный курс, а затем принимает сумму этих чисел.

Курсы валют хранятся на отдельном листе "GBP". Курс меняется каждый месяц, поэтому есть таблица с кодами валют, указанными по вертикали, и месяцами, указанными по горизонтали.

У меня есть формула, которая для отдельной ячейки C4 правильно ищет обменный курс:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

Поэтому я надеялся, что, изменив диапазон ссылок в первом MATCH чтобы он указывал на $B4:$B13 , вставив все это в формулу SUM и введя ее в виде формулы массива, я получу правильный ответ:

{=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

Тем не менее, хотя он уже близок, он применяет обменный курс для строки 4 к каждой строке. Помощь очень ценится! Это можно сделать, или мне нужно вставить дополнительный столбец?

Пара дополнительных заметок:

1. Я пытался использовать SUMPRODUCT вместо SUM , но это не имело никакого значения

2. Значение А1 "2011-11"

Answer 1

Мое первое предположение было частично верным, но неправильно.

Проблема 1

Значение поиска

DATE(LEFT(A1,4),MID(A1,6,2),31)

основан на значении 2011-11 и, следовательно, эквивалентен

DATE(2011,11,31)

Это даст противоречивые значения. За октябрь это дает 31 октября. На ноябрь это дает 1Dec11. Чтобы получить согласованные значения, такие как последний день любого месяца, используйте:

DATE(LEFT(A1,4),MID(A1,6,2)+1,0)

Проблема 2

Если вы скопируете исходную формулу вниз по столбцу, вы

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1))

Вы получаете "# ЗНАЧЕНИЕ!«для всех копий, потому что А1 становится В1, С1 и т. д.

Тебе нужно

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2),31),GBP!$A$3:$CE$3,1))
                 ^ ^          ^ ^        

Проблема 3

В вашей формуле массива

=SUM(C4:C13/INDEX(GBP!$A$4:$BE$42,MATCH($B4:$B13,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT(A1,4),MID(A1,6,2),31),GBP!$A$3:$CE$3,1)))}

у вас есть пять диапазонов, из которых вы хотите, чтобы Excel использовал два в качестве массивов. Я не могу найти ничего, чтобы предложить Excel, если достаточно умный, чтобы догадаться, что это то, что вы хотите.

Лучшее, что я могу предложить, это установить D4 на:

=INDEX(GBP!$A$4:$BE$42,MATCH($B4,GBP!$A$4:$A$42,0),
 MATCH(DATE(LEFT($A$1,4),MID($A$1,6,2)+1,0),GBP!$A$3:$CE$3,1))

Скопируйте эту формулу вниз по столбцу и рассчитайте сумму с помощью:

{=SUM(C4:C13/D4:D13)}

Тем не менее, я адекватный программисту Excel VBA; Я не опытный пользователь Excel. Другие могут рассказать вам, как достичь того, что вы ищете.