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

Question

Я пытаюсь составить ежеквартальный отчет с номерами для каждого врача и для специальности. я хотел использовать 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))

Answer 1

(Изменить: Альтернативное решение, более интуитивное, в конце этого ответа.)
Это должно сделать свое дело. Вставьте следующее в 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)

Answer 2

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

{=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.

• При необходимости измените ссылки на ячейки в формуле.