Динамически построить диапазон для использования в формуле XIRR

Question

У меня есть лист в этой форме:

Fund | Date      | Amount
A    | 10-Jan-05 | -5000
A    | 10-Feb-05 | -5000
A    | 08-Oct-13 | 12500
B    | 10-Sep-05 | -5000
B    | 10-Oct-05 | -5000
B    | 10-Nov-05 | -5000
B    | 08-Oct-13 | 22500

Я ищу выходной столбец, который предоставляет XIRR для каждого фонда. Функция XIRR принимает значения и даты в качестве диапазонов. Мне нужен способ динамического построения диапазона, используя имя фонда в качестве критерия поиска.

Fund | XIRR
A    | ...
B    | ...

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

Answer 1

Вы можете использовать OFFSET, чтобы получить правильные диапазоны, например, эту формулу

=XIRR(OFFSET(C$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)),OFFSET(B$2,MATCH(A11,A$2:A$8,0)-1,0,COUNTIF(A$2:A$8,A11)))

смотреть скриншот