如何在Google表格查询中动态增加或减少限制值 [英] How to dynamically increase or decrease limit value in Query of Google sheet

问题描述

所以我的任务是根据一些计数计算来获取最后的x行，为此，我在查询中使用了极限偏移量.要详细说明，我有一个很好用的公式

So my task is to get last x rows based on some count calculation and for that, I'm using limit offset in a query. To elaborate more, I have this formula that works fine

=COUNTIF(QUERY(A:A,"limit 5 offset "&(COUNT(A:A)-5),A1)>1

这里的问题是我不想硬编码"值为"5"，因为我的范围是根据我在另一列上使用的某些时间戳函数的计数而变化的.因此，我通过添加另一个用于检查另一列并每次更改范围的计数函数，对上述公式进行了一些更改.

The problem here is that I don't want to "Hardcode" that value of "5" since my range changes based on the count of some timestamp function I'm using on another column. SO I made some changes in above formula by adding another count function that checks the other column and changes range each time.

=COUNTIF(QUERY(A:A,"limit COUNT(FILTER(D:D,D:D>NOW()-6/24)) offset "&(COUNT(A:A)-COUNT(FILTER(D:D,D:D>NOW()-6/24)))),A1)>1 

此公式中的主要问题是此行

The main issue in this formula is this line

"limit COUNT(FILTER(D:D,D:D>NOW()-6/24)) offset "

因为我显然不能在limit + offset中使用count函数.如何更改运行限制，偏移量查询的语法?

as I can't use count function in limit+offset apparently. How can I change the syntax to run limit, offset query?

效果不佳

"limit"+COUNT(FILTER(D:D,D:D>NOW()-6/24))+"offset"

推荐答案

尝试如下:

=COUNTIF(QUERY(A:A,
 "limit "&COUNT(FILTER(D:D,D:D>NOW()-6/24))&" 
  offset "&(COUNT(A:A)-COUNT(FILTER(D:D,D:D>NOW()-6/24)))),A1)>1

这篇关于如何在Google表格查询中动态增加或减少限制值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！