У меня есть рабочая книга Excel, в которой есть листы, содержащие столбцы, в которых требуется вычислить среднее значение 7 из 25 наименьших значений, исключая любые значения с нулевым значением, например, ячейки со значением больше 1. В приведенном ниже примере используется столбец Z, а значения содержатся в строках с 13 по 38. В ряде строк сумма значений в столбце Z равна 0.
Формула у меня такая: =СУММПРОИЗВ(НАИМЕНЬШИЙ(Z$13:Z$38,{1,2,3,4,5,6,7}))/7
Это работает, но только если, например, строки, возвращающие 0, либо пусты, либо содержат «-».
Это создает проблему, так как строки содержат формулу, которая является суммой других ячеек в этой строке. Например, Z20 содержит формулу, которая является суммой M20 и W20, которые являются суммой D20:L20 и N20:V20 соответственно. Хотя D20:L20 и N20:V20 не заполнены, сумма в M20 и W20 обе возвращает 0. Ячейки D20:L20 и N20:V20 будут заполнены только позднее.
Вводя "-" или нажимая Delete для удаления 0, я также удаляю формулу. Затем возникает необходимость повторно вводить формулу при заполнении ячеек, что возможно, но не идеально из-за количества задействованных листов и потенциальной ошибки.
Есть ли решение этой проблемы?
Спасибо,
Ф
решение1
Одним из обходных путей является изменение других формул, например, для Z20, вместо использования
=М20+В20
использовать
=ЕСЛИ((M20+W20)=0,"-",(M20+W20))
который установит ячейку равной «-» всякий раз, когда формула возвращает ноль.