☰

电子表格查找并排序最常出现的名字

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
电子表格查找并排序最常出现的名字

我有一个如下所示的电子表格：

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

A 列中有一组姓名。这些姓名在以下 C 至 G 列中以随机顺序分别出现一次。

我怎样才能生成像我在第 I 列中手动创建的结果？我想知道哪些名字在 C 列至 G 列的第 1-10 行中出现次数最多，然后哪些名字的平均行数最高。

我手动发现“姓名 C6”出现次数最多，共出现 4 次。然后我又找到了 4 个出现次数在前 10 名中有 3 次的姓名，并按最佳平均行数对它们进行排序，如 L 列所示。

可以使用公式或类似的东西来完成吗？可以在 Excel 或 Google Docs 中完成。我会手动完成，但当列数开始达到 10 列或更多时，就会变得非常繁琐。

任何帮助都将不胜感激！谢谢 :)

答案1

此代码将创建您想要的表，其中包含如下所示的数据。

代码

/**
 * 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）。

例子

我为您创建了一个示例文件：我的表
添加以下代码工具 > 脚本编辑器，按下保存按钮，您就可以开始了。

答案2

这里有一个关联到包含此解决方案实现的实时公共电子表格，以下是供参考的屏幕截图。以下是问题描述：

给定一个名称列表，其中对这些名称进行了 5 次重新排列，请按（名称出现在前 10 名中的次数 [升序]）对名称进行排序，然后按（名称前 10 名排名的平均值 [降序]）进行排序。

电子表格截图

解释 - 列

  • A 列是一个姓名列表
  • B 列是该名称的 Top 10 命中次数
  • 专栏 CG是名字的随机重新排列
  • H 列包含四个最高值乙
  • 第一栏拥有以下名称的数量H值
  • J 列是姓名的行号大号
  • K 列是寻找下一个具有相同名称的范围米价值
  • LM 柱按名称的值排序
  • 第 N 列用途吉姆计算每个名字的平均值
  • 色谱柱操作确认名称是按值排序然后按平均值排序的！！

解释 - 方程式

‘五‘表示复制/粘贴至列的其余部分

‘>‘表示复制/粘贴至指定行的其余部分

  • A - 手动输入的名称（带有一些示例名称，以表明其有效）
  • B - 计算等于
    • B1五=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 的最大值
    • 氢气=max($B$1:$B$60)
    • H4五=max(FILTER($B$1:$B$60,$B$1:$B$60<H2))
  • I - 计算与相邻 H 值相等的 B 值
    • I2五=countif($B$1:$B$60,"="&H2)
  • J - 如果此 L 名称的值 (x) 小于前一个 (y)，则在 B 中找到 x 的第一个匹配项的行，否则在 K 中找到的范围中查找 y 的第一个匹配项并偏移前一个 J 的值
    • J2=MATCH(M2,$B$1:$B$60,0)
    • J3五=If(M3<M2,Match(M3,$B$1:$B$60,0), Match(M3,INDIRECT(K2),0)+J2)
  • K - 从 J 中取出行并将其放入一个范围
    • K2五="B"&J2+1&":B60"
  • LM - 按 B 降序对 A1 到 B27 进行排序
    • L2=SORT(A1:B27,2,False)

  • N - 如果此 L 名称出现在 C 的前 10 名中，则查找其所在的行，否则为 0。如果此 L 名称出现在 D 的前 10 名中，则查找其所在的行，否则为 0。等等。现在将它们加在一起并除以 L 名称出现的次数以获得平均值

    • 氮气五

      =(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 - 按 M 升序对 LN 进行排序，然后按 N 降序对 LN 进行排序

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

相关内容