Заменить пользовательскую функцию в Google Таблицах стандартными функциями

У меня есть диапазон ячеек, и я хочу накопить, сколько раз столбец имеет максимальное значение для данной строки.

Образец:

       headers ->  a    b   c   d   e   f   g   h
                   0    0   12  18* 1   0   0   0
                   30*  0   15  25  0   0   0   0
                   35   0   19  31  0   0   31  50*
                   40   10  19  31  0   2   5   55*

ожидал:

 #max val per row-> 1   0   0   1   0   0   0   2

Максимальные значения отмечены звездочкой. Столбец a получает 1 балл, потому что он имеет максимальное значение во второй строке данных, столбец d также получает 1 балл, потому что он имеет максимальное значение в первой строке данных, а столбец h получает 2 балла, потому что он имеет максимальное значение в третьей строке. и четвертые строки данных. Остальные столбцы не имеют максимального значения ни в одной строке, поэтому им присваивается 0.

Только для одной строки я могу скопировать эту формулу для каждого столбца, и она будет делать это, но мне нужно что-то, что применяет максимальное количество строк COUNTIF(B2:B10, MAX($B2:$B10)).

Я написал этот скрипт приложений Google, но мне не нравится его отзывчивость (видеть «Загрузка ...» в ячейке почти на секунду - это отчасти раздражает по сравнению с быстротой, которую вы получаете с собственными функциями):

function countMaxInRange(input) {
  return [input.map(function(row) {
    var m = Math.max.apply(null, row);
    return row.map(function(x){return x === m && 1 || 0});
  }).reduce(function(a, b){
    var s = Array(a.length);
    for (var i = 0; i < a.length; i++) {
      s[i] = (a[i] + b[i]) || 0;
    }
    return s;
  })];
}

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

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


google-sheets google-apps-script custom-function
person fortran    schedule 27.10.2016    source источник
comment
Возможно, вам будет полезен этот инструмент для форматирования текста в виде таблиц ASCII: senseful.github.io / web-tools / text-table (я не внес изменения из-за моего предыдущего комментария).   -  person Rubén    schedule 28.10.2016
comment
Описание верное (и код js делает именно то, что задумано), представьте это как соревнование, где каждый столбец - участник, а каждая строка - день, а ячейка данных - это набранные баллы. Я хочу знать, сколько раз каждый участник выигрывал ежедневные заплывы.   -  person fortran    schedule 28.10.2016

Ответы (1)


arrow_upward
1
arrow_downward

ФОРМУЛА

=ArrayFormula(TRANSPOSE(
MMULT(
N(TRANSPOSE(NamedRange1)
=
INDEX(
QUERY(TRANSPOSE(NamedRange1),
"SELECT "&JOIN(",",("MAX(Col"&TRANSPOSE(ROW(NamedRange1))-INDEX(ROW(NamedRange1),1)+1)&")"
)),
2)
),
SIGN(ROW(NamedRange1))
)
))

где NamedRange1 - именованный диапазон, относящийся к диапазону.

Условное форматирование:

  • Применить к диапазону: A1:H4
  • Пользовательская формула: =A1=MAX($A1:$H1)

Объяснение

Резюме

Приведенная выше формула не требует дополнительных столбцов, просто чтобы установить диапазон как именованный диапазон. В формуле использовалось NamedRange1, но ее можно настроить в соответствии с вашими предпочтениями.

Результатом является 1 x n массив, где n - количество столбцов NamedRange1. В каждом столбце будет указано количество случаев появления максимальных значений по строкам в соответствующем столбце.

Избранные хаки

  • ARRAYFORMULA возвращает массив значений.
  • Диапазоны больше 1 x 1 обрабатываются как массивы.
  • Использование массива в качестве аргумента с некоторыми функциями и операторами работает аналогично циклу. В этом случае эта функция используется для создания оператора SQL для получения максимального значения каждого столбца входных данных. Обратите внимание, что входные данные для QUERY - это транспонирование NamedRange1.
  • N приводит значения ИСТИНА / ЛОЖЬ к 1/0 соответственно.
  • MMULT используется для суммирования по строкам

Примечание: +0, показанный на изображении, был вставлен, чтобы заставить Google Таблицы сохранять структурные линии, введенные при редактировании формулы, без структурных линий, потому что, если в формулу не вносятся существенные изменения, структурные линии автоматически удаляются из-за формулы / результата. функция кеширования Google Таблиц.

Ссылка

Использование MMULT, автор - Адам Ласк.

person Rubén    schedule 27.10.2016
comment
Я думаю, что это правильное направление, но это пока не совсем работает ... Я добавляю образец набора данных в вопрос, чтобы вы могли сами увидеть, что ожидается и что он выдает. - person fortran; 28.10.2016
comment
возможно, я мог бы использовать формулу массива с моим первоначальным подходом к выполнению countif для каждой строки - person fortran; 28.10.2016
comment
Тогда это проблема понимания, вы считаете, сколько раз повторяется максимальное значение для каждого столбца. Я хотел, сколько раз столбец имеет максимальное значение для данной строки. - person fortran; 28.10.2016
comment
@fortran: Я обновил формулу и расширил объяснение в соответствии с новой формулой, но основные идеи остались прежними. - person Rubén; 29.10.2016