Я не могу заставить прерывистый диапазон входных ячеек, таких как (A2:A2;A4:A5) работать в функциях с двумя аргументами (и, возможно, с несколькими аргументами) в Excel: следующее не выдает никакого сообщения об ошибке, но выдает #VALUE
=COVARIANCE.S((A2:A2;A4:A5);(B2:B2;B4:B5))
Как я мог заставить это работать?
Вы можете создать массив или числа с формой индекса массива:
INDEX(A:A,N(IF(A2:A5<>"",ROW(A2:A5))))
Это вернет каждую ячейку, которая не является нулем A2:A5, как массив к формуле.
Это работает для некоторой формулы, но не для всех.
Это работает на Slope и Intercept
=INTERCEPT(INDEX(A:A,N(IF(A2:A5<>"",ROW(A2:A5)))),INDEX(B:B,N(IF(A2:A5<>"",ROW(A2:A5)))))
=SLOPE(INDEX(A:A,N(IF(A2:A5<>"",ROW(A2:A5)))),INDEX(B:B,N(IF(A2:A5<>"",ROW(A2:A5)))))
(Второй набор должен показать правильный вывод)
Как вы можете видеть, он пропускает 99 в B3 и возвращает только 1,2,3 в других ячейках.
Мы также можем настроить его, чтобы пропустить не числовой:
INDEX(A:A,N(IF(ISNUMBER(A2:A5),ROW(A2:A5))))
Идея состоит в том, что он создает массив номеров строк и передает его в INDEX, который затем возвращает все числа в этой строке.
Будучи формулой массива, при выходе из режима редактирования нужно будет использовать Ctrl-Shift-Enter вместо Enter.
Эта формула просто не относится к прерывистому диапазону входных ячеек, она представляется как расчет с непрерывными диапазонами. Он вернул ошибку #VALUE потому что вы, возможно, удовлетворили форму, но определение общей формулы неверно. Если у вас мало данных для расчета, вы всегда можете сделать это вручную.
Смотрите этот пример:
В файле примеров вы можете увидеть общие формулы и небольшой пример, подобный вашему.
Вы всегда можете скопировать данные на другой лист и произвести необходимые вычисления, если чувствуете, что оказались в ловушке или не хватает времени, чтобы закончить то, что вам нужно.