(https://docs.google.com/spreadsheets/d/1Wpiq6Vn8XtvjXxTN6BlynGQZyqIrnua4cau5BsaUKSA/edit)

在工作表1 中，我有一批比赛数据单据的集合，如图所示。在工作表2 中，我为每位玩家设有一个带有统计信息的分区，如图所示。我创建了如下公式，这样如果新增了玩家，我就不需要再为玩家数据手动新增分区：

TRANSPOSE(UNIQUE(FILTER('Match Records'!$A$3:$A,'Match Records'!$A$3:$A<>"Date:",'Match Records'!$A$3:$A<>"Players:",'Match Records'!$A$3:$A<>"",'Match Records'!$A$3:$A<>"-")))

然而，我遇到的问题在于，由于玩家数据表是两列宽，我只能看到每两个玩家中的一个。我更愿意保持表格两列宽，但不知道该如何让我的公式考虑到这一点。下面给出示例比赛单据和玩家表（以Markdown展示）：

解决方案

以下的公式，可以在“预期结果”标签页中的单元格 A13 找到，与您的预期结果相匹配。它用一个动态数组取代了你原本的多条单独公式，并使用 REDUCE 作为动态数组，随着 Match Records 中数据的新增或删除会自动展开和收缩：

=LET(
   players, 'Match Records'!A:A,
   placements, 'Match Records'!B:B,
   times, 'Match Records'!G:G,
   winners, 'Match Records'!I:I,
   players_deck_stats, 'Match Records'!K:N,

   players.unique, UNIQUE(
     FILTER(players, players<>"", 1 - REGEXMATCH(players, ":|-"))
   ),

   REDUCE(
     TOROW(, 1),
     players.unique,
     LAMBDA(result, player, LET(

       AVG_PLACEMENT_INDEX, 1,
       USES_INDEX, 2,
       PLAYER_INDEX, 3,
       DECK_INDEX, 4,

       player_deck_stats, FILTER(players_deck_stats, INDEX(players_deck_stats, , PLAYER_INDEX) = player),

       best_avg_placement, MIN(INDEX(player_deck_stats, , AVG_PLACEMENT_INDEX)),
       most_uses, MAX(INDEX(player_deck_stats, , USES_INDEX)),

       num_of_games, COUNTIF(players, player),
       wins, COUNTIFS(players, player, placements, "=1"),
       losses, COUNTIFS(players, player, placements, ">1"),
       win_rate, wins / num_of_games,

       avg_win_time, IFERROR(AVERAGEIFS(times, winners, player)),
       avg_placement, IFERROR(TRUNC(AVERAGEIFS(placements, players, player), 2)),

       best_deck, XLOOKUP(
         best_avg_placement, 
         INDEX(player_deck_stats, , AVG_PLACEMENT_INDEX), 
         INDEX(player_deck_stats, , DECK_INDEX), 
       ),

       most_used_deck, XLOOKUP(
         most_uses, 
         INDEX(player_deck_stats, , USES_INDEX), 
         INDEX(player_deck_stats, , DECK_INDEX), 
       ),

       HSTACK(
         result, 
         {
           player, player;
           "# of Games:", num_of_games;
           "Wins:", wins;
           "Losses:", losses;
           "Win Rate:", win_rate;
           "Avg. Win Time:", avg_win_time;
           "Avg. Placement", avg_placement;
           "Best Deck:", best_deck;
           "Most Used Deck:", most_used_deck
         }
       )

     ))
   )
)

工作原理

1. 数据准备： LET 定义了查找范围，并通过过滤 A:A 中的噪声来生成一个干净的唯一玩家列表（players.unique），使用 1-REGEXMATCH 来排除包含 : 或 - 的字符串。

2. 循环： REDUCE 以一个空数组（TOROW(,1)）开始，逐一遍历每个唯一玩家。

3. 统计计算： 在循环中的当前玩家，公式会计算他们的具体指标（比赛场次、胜负率、平均用时，以及最佳牌组和最常用牌组）。

4. 结果生成： 最后，HSTACK 将一列硬编码的文本标签与该玩家计算得到的一列变量配对在一起。这个两列的块会不断水平地追加到来自先前迭代累积的 result 上，直到所有玩家都处理完为止。

如果你想能够输入每行块的数量（INPUT_BLOCKS_PER_ROW），请改用下面的公式：

=LET(
   players, 'Match Records'!A:A,
   placements, 'Match Records'!B:B,
   times, 'Match Records'!G:G,
   winners, 'Match Records'!I:I,
   players_deck_stats, 'Match Records'!K:N,

   INPUT_BLOCKS_PER_ROW, 4,

   players.unique, UNIQUE(FILTER(players, players<>"", 1 - REGEXMATCH(players, ":|-"))),
   total_players, ROWS(players.unique),

   BLOCKS_PER_ROW, MIN(INPUT_BLOCKS_PER_ROW, total_players),
   total_rows, ROUNDUP(total_players / BLOCKS_PER_ROW),

   REDUCE(TOCOL(, 1), SEQUENCE(total_rows, 1, 1, BLOCKS_PER_ROW), LAMBDA(prev_v, i, LET(
     row_of_blocks, REDUCE(TOROW(, 1), SEQUENCE(BLOCKS_PER_ROW, 1, i), LAMBDA(prev_h, j, 
       IF(j > total_players, 
         prev_h, 
         LET(
           AVG_PLACEMENT_INDEX, 1, USES_INDEX, 2, PLAYER_INDEX, 3, DECK_INDEX, 4,

           player, INDEX(players.unique, j),
           player_deck_stats, FILTER(players_deck_stats, INDEX(players_deck_stats, , PLAYER_INDEX) = player),
           best_avg_placement, MIN(INDEX(player_deck_stats, , AVG_PLACEMENT_INDEX)),
           most_uses, MAX(INDEX(player_deck_stats, , USES_INDEX)),

           num_of_games, COUNTIF(players, player),
           wins, COUNTIFS(players, player, placements, "=1"),
           losses, COUNTIFS(players, player, placements, ">1"),
           win_rate, wins / num_of_games,
           avg_win_time, IFERROR(AVERAGEIFS(times, winners, player)),
           avg_placement, IFERROR(TRUNC(AVERAGEIFS(placements, players, player), 2)),

           best_deck, XLOOKUP(
             best_avg_placement, 
             INDEX(player_deck_stats, , AVG_PLACEMENT_INDEX), 
             INDEX(player_deck_stats, , DECK_INDEX), 
           ),

           most_used_deck, XLOOKUP(
             most_uses, 
             INDEX(player_deck_stats, , USES_INDEX), 
             INDEX(player_deck_stats, , DECK_INDEX), 
           ),

           cur_block, {
             player, player;
             "# of Games:", num_of_games;
             "Wins:", wins;
             "Losses:", losses;
             "Win Rate:", win_rate;
             "Avg. Win Time:", avg_win_time;
             "Avg. Placement", avg_placement;
             "Best Deck:", best_deck;
             "Most Used Deck:", most_used_deck
           },

           HSTACK(prev_h, cur_block)
         )
       )
     )),
     IFNA(VSTACK(prev_v, IF(i = 1, TOCOL(, 1), ), row_of_blocks))
   )))
)