Формула Excel - иногда формула массива, иногда нет

Question

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

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

=AVERAGE(IF('dataSheet1'!$B:$B=1,IF('dataSheet1'!$I:$I<>"",'dataSheet1'!$I:$I,""),""))

Если у меня есть эта формула в dataSheet1 , все работает нормально. Если у меня есть эта формула в summary таблице, мне нужно ввести ее как формулу массива, и я должен указать точные диапазоны, чтобы она работала. Есть ли способ (кроме добавления среднего в dataSheet , а затем ссылки на результат в summary), чтобы заставить формулу работать, не превращая ее в формулу массива?

Answer 1

Average уже исключает пустые значения. Вот почему вы можете взять среднее значение I:I и не получить почти 0 (потому что это в основном пустые), но, включив проверку B:B вы можете сделать что-то вроде этого:

=SUMIF(dataSheet1!B:B,1,dataSheet1!I:I)/COUNTIF(dataSheet1!B:B,1)