如何对每列中的值求和并按Google表格中的值总和对各列名进行排名 [英] How to sum up the values in each column and ranking each column name by sum of values in google sheet

问题描述

我有一个Google表格，其中的值正在填充

I have a google sheet with values that is getting populated

  A              B                 C           D            E             F        G      H
Top scorers   Date            Player l    Player 2      Player 3     Player 4
            13 Jan 2019            1        1            1
            20 Jan 2019            2        1                         1 

主意是:每个比赛日我将输入比赛日期和每个球员得分的进球数，如果有新球员得分，我将在该日期将他的名字放在新列和进球数中.如果当天没有球员得分，我将将该单元格留空. 然后，我想在第一列得分最高的球员"中填充得分球员的排名.预期结果将如下所示:

the idea is: each match day I will enter the date of the match and number of goals that each player scored, if new player score I will just put his name in new column and number of goal on that date. If any player not score that day, I will just leave that cell blank. Then I want to populate first column "Top scorers" with ranking of Player scored. Expected result will look like this:

     A         B                C            D            E            F        G        H
Top scorers   Date            Player l    Player 2      Player 3     Player 4
Player 1: 3   13 Jan 2019         1         1            1
Player 2: 2   20 Jan 2019         2         1                         1 
Player 3: 1
Player 4: 1

它将使用新的数据输入自动更新.我该怎么做?我看了数据透视表，但看起来很难存档此结果.

It will automatically updated with new data input. How could I make this? I have a look at Pivot Table but looks like it is hard to archive this result.

样本表.

推荐答案

根据您要完成的操作和共享的Google表格的描述.

According to the description of what you're trying to accomplish and the Google Sheet shared.

您需要将问题分解为几个子任务:

You need to break your problem down into several subtasks:

• 选择以后需要的所有范围(玩家，日期，您编写分数的范围，显示最高得分者的范围)
• 将所有游戏中的每个玩家的目标相加，得出他们的总得分
• 根据球员的总得分对他们进行排序
• 创建字符串以写入您的topscorer列
• 将这些字符串写入topscorer列

我的解决方案很冗长，但似乎可行.请随时询问是否有任何疑问或是否需要澄清.

My solution is pretty verbose but it seems to work. Please don't hesitate to ask if you have any questions or if clarifications are needed.

    function testMe() {

    var ID = ''; // ID of your Document
    var name = ''; // Name of your sheet
    var sourceSheet = SpreadsheetApp.openById(ID); // Selects your Source Spreadsheet by its id
    var ssheet = sourceSheet.getSheetByName(name); // Selects your Source Sheet by its name

    var scoreRange = ssheet.getRange(2, 3, (sourceSheet.getLastRow() -1), (sourceSheet.getLastColumn() -2)); // Selects the range in which you will enter your scores
    var dateRange = ssheet.getRange(2,2,(sourceSheet.getLastRow() -1)); // Selects the range for which player names in row 1
    var playerRange = ssheet.getRange(1,3,1,(sourceSheet.getLastColumn() -2)); // selects the range for which dates were entered in column
    var topScorerRange = ssheet.getRange(2,1,scoreRange.getNumColumns()); // selects the range where your topscorer output will end up

    var numberOfPlayers = playerRange.getNumColumns(); // Gets the number of players you've already entered in row 1
    var numberOfGames = playerRange.getNumRows(); // Gets the number of games whose dates you've already entered in Column B


    function sortAndUpdateTopScorers() {
        var array = scoreRange.getValues();
        var totalPlayers = scoreRange.getNumColumns();
        var totalGames = scoreRange.getNumRows();
        var playerScores = [];

      // iterate through the scoreRange and count up each players total score

           for (var i = 0; i < totalPlayers; i++) {
               var currentPlayer = 0;
               for (var j = 0; j < totalGames; j++) {
                  currentPlayer += array[j][i];
               }
             playerScores.push([currentPlayer]);
           }

      // Combine the names of the players and their total score in order to create the strings for your topscorers column

      for (var v = 0; v < numberOfPlayers; v++) {
        playerScores[v].push(playerRange.getValues()[0][v] + ": " + playerScores[v]);
      };

      // Sort those strings according to their score

      playerScores.sort(function(a,b) {
      return b[0]-a[0]
      });

      // Remove the score value so only the string remains in the array

      for (var x = 0; x < playerScores.length; x++) {
       playerScores[x].shift(); 
      }

      // Write the content of the array into your topscorers column

      topScorerRange.setValues(playerScores);

     };
sortAndUpdateTopScorers();
};

这篇关于如何对每列中的值求和并按Google表格中的值总和对各列名进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！