Как подсчитать долю без промежуточной колонки?

Question

[Илья]

Есть зачетная таблица игроков. В каждом событии игроки набирают очки. Нужно подсчитать средний процент каждого игрока за месяц. То есть, вклад каждого игрока по каждому событию делим на количество событий. Столбцы с событиями будут добавляться по мере появления событий, их конечное количество неизвестно.

Процент (вклад каждого игрока в конкретное событие) вычисляется в отдельных столбцах:
=ArrayFormula(C2:C/SUM(C2:C))
=ArrayFormula(D2:D/SUM(D2:D))

Есть формула массива для подсчета среднего арифметического по столбцам с процентами. (E1:1)
=ARRAYFORMULA(SUMIF(IF(COLUMN(E1:1);ROW(E2:E));ROW(E2:E);E2:E)/COLUMNS(E1:1))

Задача: избавиться от промежуточных вычислений (столбцов с процентами), чтобы в формуле массива в ячейке A2 использовались для расчета не столбцы с процентами E:F, а столбцы с исходными данными C:D.

Пример таблицы

Answer 1

[Alexander Ivanov]

Ну, наверное, это MMULT результатов MMULT

=INDEX(
  MMULT(
    N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
    MATCH(2;1/(B2:B<>"");1);
    MATCH(2;1/(C1:1<>"");1)));
    SEQUENCE(MATCH(2;1/(C1:1<>"");1);1)^0)/MATCH(2;1/(C1:1<>"");1)
)

Пример и полезные ссылки в Таблице.

Техника MATCH(2;1/(B2:B<>"");1) может быть избыточна в том смысле, что это поиск последнего действительного значения для обрезки. Если вы будете содержать последовательности заголовков событий и игроков без пробелов, то действительно следующее

=INDEX(
  MMULT(
    N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
    COUNTA(B2:B);
    COUNTA(C1:1)));
    SEQUENCE(COUNTA(C1:1);1)^0)/COUNTA(C1:1)
)

Диаграмма на скрине приведена неслучайно. Она показывает, что общий вес расчета равен 100%, как проверка.

С скриптом не стал заморачиваться, но, доверяя Григорий Боев, думаю, что пользовательская функция при должной проработке будет более гибкой. Выбор зависит от конечной цели и циклов проработки результатов и будущих улучшений Таблицы.

Answer 2

[Григорий Боев]

Используйте пользовательскую функцию.

/**
* Считает средний процент каждого игрока за месяц
* Telegram - @ProgrammerForever
*
* @param {range} data Исходные данные
* @return Средний процент каждого игрока за месяц
*/
function calc(data){
  let sums = data[0]
                .map((_, col) => data.map(row => row[col]))
                .map(c=>c.reduce((a,v)=>a+=v,0));
  let fractions = data.map((row,r)=>row.map((v,c)=>v/sums[c]));
  return fractions.map(row=>row.reduce((a,v)=>a+=v,0));
};

Comments

[Илья]

Простите, я не разбираюсь в этом, поэтому не получилось отредактировать ваш код. То, что вы предложили, работает, но считает сумму процентов по столбцам, а не среднее (необходимо разделить возвращаемое значение на количество столбцов). Не понимаю как это добавить.

Ваш вариант

[Григорий Боев]

Илья,

function calc(data){
  let sums = data[0]
                .map((_, col) => data.map(row => row[col]))
                .map(c=>c.reduce((a,v)=>a+=v,0));
  let fractions = data.map((row,r)=>row.map((v,c)=>v/sums[c]));
  return fractions.map(row=>row.reduce((a,v)=>a+=v,0)/row.length);
};