Поиск в электронной таблице и сортировка наиболее часто встречающихся имен

Question

У меня есть таблица, которая выглядит следующим образом:

https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing

В столбце А есть список имен. Эти имена появляются один раз в случайном порядке в каждом из следующих столбцов от C до G.

Как я могу получить результат, который я создал вручную в столбце I? Я хотел бы знать, какие имена наиболее часто встречаются в строках 1–10 в столбцах C – G, а затем какие имена имеют наилучшее среднее число строк.

Я вручную обнаружил, что "Имя C6" появилось больше всего, с 4 появлениями. Затем я нашел еще четыре имени, которые имели три появления в первой десятке, и отсортировал их по их наилучшему среднему числу строк, как показано в столбце L.

Можно ли это сделать с помощью формулы или чего-то подобного? Это может быть в Excel или Google Docs. Я бы сделал это вручную, но когда становится 10 или более столбцов, это становится очень внушительным.

Любая помощь будет принята с благодарностью! Спасибо :)

Answer 1

Вот ссылка на живую общедоступную электронную таблицу, в которой есть реализация этого решения, и вот скриншот для справки. Вот описание проблемы:

Учитывая список имен с 5 перестановками этих имен, отсортируйте имена по (количеству раз, которое имя появляется в Топ-10 [по возрастанию]), а затем по (среднему из Топ-10 мест размещения [по убыванию]) ,

Пояснение - столбцы

• Колонка А представляет собой список имен
• Столбец B - это число 10 лучших хитов для этого имени.
• Столбец CG - это случайные перестановки имен
• Столбец H содержит четыре самых высоких значения B
• Столбец I имеет количество имен, которые имеют значения H
• Столбец J - это номер строки Имени в L
• Столбец K - это диапазон для поиска следующего имени с тем же значением M
• Столбец LM имеет имена, отсортированные по их значениям
• Столбец N использует JM для вычисления среднего для каждого имени
• Столбец OQ - это имена, отсортированные по значению, а затем в среднем !!

Пояснение - Уравнения

« v » обозначает копирование / вставку в оставшуюся часть столбца

« > » обозначает копирование / вставку в оставшуюся часть указанной строки

• A - Вручную введенные имена (с некоторыми примерами имен, чтобы показать, что это работает)
• B - Подсчитывает количество лучших 10, которые равны
  • B1 v =countif($C$1:$G$10,A1)
• CG - переупорядочивает имена случайным образом [не знаю почему]
  • C1 > =sort($A$1:$A$27,arrayFormula(randbetween(sign(row($A$1:$A$27)),1000000)),true)
• H - отфильтровать все значения, превышающие предыдущий максимум, а затем найти максимум B
  • H2 =max($B$1:$B$60)
  • H4 v =max(FILTER($B$1:$B$60,$B$1:$B$60<H2))
• I - Подсчитать значения B, равные соседнему значению H
  • I2 v =countif($B$1:$B$60,"="&H2)
• J - Если значение этого имени L (x) меньше предыдущего (y), найдите строку первого совпадения x в B, в противном случае найдите первое совпадение y в диапазоне, найденном в K, и сместите на величину предыдущий J
  • J2 =MATCH(M2,$B$1:$B$60,0)
  • J3 v =If(M3<M2,Match(M3,$B$1:$B$60,0), Match(M3,INDIRECT(K2),0)+J2)
• K - Возьми ряд от J и сделай его в диапазоне
  • K2 v ="B"&J2+1&":B60"
• LM - Сортировка от А1 до B27, по B в порядке убывания
  • L2 =SORT(A1:B27,2,False)

• N - Если это имя L появляется в Топ-10 C, найдите строку, в которой оно находится, иначе 0. Если это имя L появляется в первой десятке D, найдите строку, в которой оно находится, еще 0 и т.д. Теперь сложите их вместе и поделите на количество раз, когда имя L появляется, чтобы получить среднее значение.

  • N2 V

    =(IF(Countif($C$1:$C$10,"="&L2)>0, Match(L2,$C$1:$C$10,0), 0)
     +IF(Countif($D$1:$D$10,"="&L2)>0, Match(L2,$D$1:$D$10,0), 0)
     +IF(Countif($E$1:$E$10,"="&L2)>0, Match(L2,$E$1:$E$10,0), 0)
     +IF(Countif($F$1:$F$10,"="&L2)>0, Match(L2,$F$1:$F$10,0), 0)
     +IF(Countif($G$1:$G$10,"="&L2)>0, Match(L2,$G$1:$G$10,0), 0))/M2
    

• OQ - Сортировка LN по M возрастанию, а затем N по убыванию

  • O2 =SORT(L2:N28,2,False,3,True)

Answer 2

Этот код создаст нужную таблицу с данными, как показано ниже.

Код

/**
 * Create a specific table
 *
 * @param {range} names The range with names
 * @param {range} data The range of all the data
 * @return A specific table
 * @customfunction
 */
function myTable(names, data) {
  var output = [];
  for(var i = 0, iLen = names.length; i < iLen; i++) {
    var name = names[i][0], nameCount = 0, sum = 0, rows = [];
    for(var j = 0; j < 10; j++) {      
      for(var k = 0, kLen = data[0].length; k < kLen; k++) {
        if(names[i][0] == data[j][k]) {
          nameCount++;
          rows.push(j+1);
        }
      }
    }
    if(nameCount != 0) {
      sum = rows.reduce(function(a, b) {return a + b;});
      output.push([name, nameCount, rows.join(), sum/nameCount]);
    }    
  }
  return output.sort(function(a, b) {return b[1] - a[1];}).slice(0,5);
}

Скриншоты

данные

исход

Разъяснения

Диапазон имен будет использоваться для начала итерации с. В начале диапазона данных устанавливается счетчик для этого конкретного имени плюс массив, который будет содержать номера строк.
Если имя было найдено более одного раза (!= 0), затем суммируем номера строк и добавляем данные в выходной массив. Этот массив в свою очередь сортируется с использованием второго столбца и отображаются только первые 6 строк.

Заметка

Я не смог воспроизвести результаты вашего листа. Скорее всего, потому что данные обновляются при открытии каждый раз. Пример, который я создал, содержит статические данные. Замечания, сделанные в сценарии, выполняют функцию. Когда пользовательская функция выбрана на листе, всплывающая подсказка содержит эту информацию (JsDoc).

пример

Я создал файл примера для вас: myTable
Добавьте код в Инструменты> Редактор скриптов, нажмите кнопку сохранения, и вы готовы к работе.