如何在Google表中过滤数据，以便只包含每个名称的最后20个项目？

发布于 2025-01-24 01:37:16 字数 314 浏览 5 评论 0原文

我有一个Google电子表格，其1000行还要多，并且每周更新，每行都包含名称，并做出了3个决定之一。我想自动化它，并在单独的表格中具有名称和数字决定的表格，每个名称的持续20个。

所需的结果看起来像此屏幕截图。

我是JavaScript的初学者，并且对我如何使用应用程序脚本进行此操作的任何想法或结构将非常欢迎。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

探春 2025-01-31 01:37:16

您可以在Sheet1！af1中使用代理商通过代理创建降级序列：

=arrayformula( 
  { 
    "Descending ordinal by agent"; 
    iferror( 1 / 
      countifs( 
        E2:E, E2:E, 
        row(E2:E), ">=" & row(E2:E) 
      ) 
    ^ -1 ) 
  } 
)

然后 insert＆gt;表格并将此公式插入新表格的单元 a1 ：

=query( 
  Sheet1!A1:AF, 
  "select E, count(E) 
   where AD is not null 
   and AF <= 20 
   group by E 
   pivot AD", 
  1 
)

该公式将按名称和决策给出计数，只查看序数为20或更少的行。请参阅新的

You can create descending ordinals by agent with this formula in cell Sheet1!AF1:

=arrayformula( 
  { 
    "Descending ordinal by agent"; 
    iferror( 1 / 
      countifs( 
        E2:E, E2:E, 
        row(E2:E), ">=" & row(E2:E) 
      ) 
    ^ -1 ) 
  } 
)

Then Insert > Sheet and insert this formula in cell A1 of the new sheet:

=query( 
  Sheet1!A1:AF, 
  "select E, count(E) 
   where AD is not null 
   and AF <= 20 
   group by E 
   pivot AD", 
  1 
)

The formula will give counts by name and decision, only looking at rows where the ordinal is 20 or less. See the new Solution sheet in your sample spreadsheet.

回复收藏 0 原文

初见 2025-01-31 01:37:16

尝试：

=INDEX({QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select Col1,count(Col1) where Col1 is not null and Col3 <= 20 
  group by Col1 pivot Col2 label Col1'Name'"), 
 {"Total"; MMULT(QUERY(QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select count(Col1) where Col1 is not null and Col3 <= 20 group by Col1 
  pivot Col2"), "offset 1", 0)*1, {1;1;1})}})

try:

=INDEX({QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select Col1,count(Col1) where Col1 is not null and Col3 <= 20 
  group by Col1 pivot Col2 label Col1'Name'"), 
 {"Total"; MMULT(QUERY(QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select count(Col1) where Col1 is not null and Col3 <= 20 group by Col1 
  pivot Col2"), "offset 1", 0)*1, {1;1;1})}})