Среднее вычисление для нескольких несмежных столбцов с критериями

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

Я решил проблему для среднего врача (столбец M,N,O), используя формулу 1., но не могу выяснить для столбцов Q,R,S.

Таблица данных

Формула 1

=SUM(C3,F3,I3)/SUM(IF(C3=0,0,1),IF(F3=0,0,1),IF(I3=0,0,1))

Формула 2

=AVERAGEIF(iferror(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),iferror(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0),iferror(AVERAGEIFS(I3:24,B3:B24,B3,I3:24,"<>0"),0))
Источник

2 ответа

(Изменить: Альтернативное решение, более интуитивное, в конце этого ответа.)
Это должно сделать свое дело. Вставьте следующее в Q3:

=IFERROR(SUM(($B$3:$B$11=$B3)*(C$3:C$11+F$3:F$11+I$3:I$11))/SUM(($B$3:$B$11=$B3)*((C$3:C$11<>0)+(F$3:F$11<>0)+(I$3:I$11<>0))),0)

Нажмите Ctrl-Shift-Enter, чтобы сделать формулу массива.

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

Как это устроено:

  • Первый тест на равенство =$B3 против массива столбцов Specialty создает логический массив из одного столбца. Если текущая строка формулы Specialty совпадает со строкой столбца Specialty, массив будет содержать TRUE в этом ряду созданного логического массива и FALSE иначе.
  • Второй массив из одного столбца создается путем объединения трех массивов из одного столбца, по одному на каждый месяц текущего рейтинга. Это делается путем добавления июль + август + сентябрь вместе строка за строкой, чтобы создать массив из одного столбца общей суммы за три месяца каждого врача для данного рейтинга.
  • Соответствующие значения строк в этих двух массивах затем умножаются вместе.
    • любой FALSE логическое значение автоматически преобразуется Excel в ноль при использовании в математическом выражении. Это обнуляет специальности, которые не равны строке формулы. TRUE логические значения преобразуются в единицу, которая затем умножается на суммирование рейтинга для соответствующей строки, сохраняя значение суммы за 3 месяца для этой специальности в этой строке. Результатом является массив с нулем в строках, не являющийся частью строки формулы Specialty.
  • Затем результирующий массив суммируется с SUM,
  • Эта сумма делится на аналогичное суммирование.
  • Тот же массив логических фильтров создается для фильтрации специализаций, не эквивалентных строке спецификаций формул.
  • На этот раз каждый элемент второго вычисленного массива представляет собой количество месяцев в этой конкретной строке, которые имеют ненулевое значение. Это достигается путем выполнения "не нулевого теста" <>0 для каждого элемента в каждом из трех отдельных столбцов массивов данных (один массив данных за каждый месяц данного рейтинга).
  • Получив три логических массива из одного столбца, элементы данной строки каждого массива складываются вместе, чтобы создать вычисляемый второй массив. Поскольку это логические значения, возвращаемые выражением для проверки ненулевого значения, Excel снова автоматически преобразует TRUE значения к одному и тому FALSE Значения ноль, когда попросили сделать это арифметическое сложение.
  • Этот массив из одного столбца со значениями в каждой строке в диапазоне от 0 до 3 затем умножается, строка за строкой, на массив логических фильтров, обнуляя все строки в массиве, которые не совпадают со специализацией текущей строки формулы (как описано ранее),
  • Снова результирующий массив суммируется для получения делителя, используемого для вычисления среднего.
  • IFERROR заменяет деление на ноль, ошибки на ноль.
[Править] Альтернативное решение. Более интуитивная формула, которая не является CSE:

=IFERROR(( SUMIFS( C$3:C$12, $B$3:$B$12, $B3) + SUMIFS( F$3:F$12, $B$3:$B$12, $B3) + SUMIFS( I$3:I$12, $B$3:$B$12, $B3) ) / ( COUNTIFS( $B$3:$B$12, $B3, C$3:C$12, "<>0", C$3:C$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, F$3:F$12, "<>0", F$3:F$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, I$3:I$12, "<>0", I$3:I$12, "<>")), 0)

Источник

Вы можете использовать эту формулу массива также:

{=ROUND(SUM(IF($F$25:$F$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$F$25:$F$34),IF($G$25:$G$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$G$25:$G$34),IF($H$25:$H$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$H$25:$H$34),0))))/3,2)}

  • Поскольку это формула CSE, завершите ее с помощью Ctrl + Shift + Enter.

Отредактировано:

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

=ROUND(IFERROR(SUMIFS($T$2:$T$11,$S$2:$S$11,$S$1,$T$2:$T$11,">0")+SUMIFS($U$2:$U$11,$S$2:$S$11,$S$1,$U$2:$U$11,">0")+SUMIFS($V$2:$V$11,$S$2:$S$11,$S$1,$V$2:$V$11,">0"),0)/(COUNTIFS($S$2:$S$11,$S$1,$T$2:$T$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$U$2:$U$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$V$2:$V$11,">0")),2)

NB

  • Диапазон данных S2:V11 ,
  • Критерии в ячейке S1 ,
  • При необходимости измените ссылки на ячейки в обеих формулах.
Источник
Другие вопросы по тегам