Включение несмежной ячейки в диапазон (серию) в формулу XIRR

Question

Я использую формулу XIRR следующим образом:

=XIRR(E$11:E17,B$11:B17)

но нужно добавить дополнительную несмежную ячейку в диапазон значений и дат.

Моя первая попытка:

=XIRR((E$11:E17,H17),(B$11:B17,B17))

привело к #VALUE

Я попытался сделать это с помощью функции CHOOSE следующим образом:

=XIRR(CHOOSE({1,2},E$11:E17,H17),CHOOSE({1,2},B$11:B17,B17))

Но это не работает для получения правильных результатов.

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

=XIRR(CHOOSE({1,2,3},E11,E12,H13),CHOOSE({1,2,3},B11,B12,B13))

Спасибо за вашу помощь.

Answer 1

Вы можете попробовать что-то похожее на это:

=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))

Answer 2

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

Хитрость в том, что функция FREQUENCY возвращает массив, который имеет на один элемент больше, чем входной массив. Я избавлюсь от полного объяснения этой функции здесь, так как файл справки хорошо работает, но, поскольку он реализован в этом случае, он возвращает массив, подобный {0;0;0;1}. Когда выполняется операция 1-{}, у нас остается массив {1;1;1;0}.

Этот массив теперь вводится в функцию IF вместе с массивом значений (смежных ячеек) для оценки для элементов, равных 1 (в массиве сверху), и одного значения (ячейка выброса) для оценки для элементов, равных до 0. Таким образом, получается желаемый массив, который будет использоваться в качестве входных данных для формулы XIRR.

Примечания: функция FREQUENCY не должна использовать один из массивов значений в качестве входных данных. Требуется только массив числовых значений на один элемент меньше, чем требуемый выходной массив. Вы можете создать скрытый столбец, заполненный нулями, и использовать его при необходимости несколько раз по всему листу. При использовании 0, первое значение в функции FREQUENCY может быть любым значением больше 0. Например, 1 облегчает чтение. 9^9 был использован в качестве сколь угодно большого значения.

Вы можете повторить этот процесс, чтобы построить массив отдельных ячеек со всего листа.