Google 表格：有没有一种方法可以快速/轻松地将日期范围添加到约 300 个单元格，每个单元格具有不同的计数公式？

发布于 2025-01-19 13:15:24 字数 1265 浏览 0 评论 0原文

我正在使用countifs函数来添加许多不同的条件 - 我需要帮助来简化过程，以便每次都需要太多的手动格式。

“这是假设电子表格的屏幕示声。”

这是假设电子表格的屏幕截图。这是一个假设的情况，可以帮助我传达我的问题。假设我正在与3个客户，Macy's，Abercrombie和Gap一起工作，以填补几个公开职位。我们正在审查多个候选人。当我审查它们并获得批准后，我在判决列（e）中选择“是”。处理后，我在F列中选择了是。如果我不批准它们，则在列中选择不。依此类推。

“这是我的电子表格” ，

所以现在我想跟踪我批准和处理多少个候选人每个开放位置的每个客户。这是我的电子表格。我已经使用了以下电子表格中的Countifs函数，称为“评论文档”，如下所示：

第2列，第2列 - 计算已批准但尚未处理的Abercrombie的销售助理：

=COUNTIFS(
    'Review Document'!$B:$B,"abercrombie", 
    'Review Document'!$C:$C, "sales associate",
    'Review Document'!$E:$E,"yes",
    'Review Document'!$F:$F,"no")

我本质上为每个客户提供了此操作，对于C列和D。想象一下，单一角色与不同的公司和角色有〜300行 - 公式文本每次都会改变以计数“位置”和“公司”。

我想做的是找到一种自动将日期范围应用于所有这些单元格的简便方法，而无需手动添加每个公式的日期标准。例如，在第一个电子表格中，有2月，3月和4月的日期。有没有办法在我的第二个电子表格上应用日期范围，以便仅计算我指定的日期？例如 - 对该表中的所有单元格应用一些日期范围，以便仅当日期为2/15/2022-3/31-2022时才计算？我最终希望能够快速更改日期范围，而无需手动将日期标准添加到300个单元格中，然后每次我想在不同日期范围内看到数字时将其更改。我正在修补有条件的格式，但我还没有弄清楚。

谢谢！

原文

I am using the countifs function to add up a lot of different conditions - I need help to simplify the process so that it doesn't require so much manual formatting every time.

Here is a screenshot of a hypothetical spreadsheet.

Here is a screenshot of a hypothetical spreadsheet. Here is a hypothetical scenario that will help convey my question. Let's say I am working with 3 clients, Macy's, abercrombie, and gap, to fill several open positions. We are reviewing multiple candidates. When I have reviewed them and approved, I select "yes" in the verdict column (E). When they have been processed, I selected yes in the F column. If I do not approve them, I select No in the column. So on and so forth.

Here is my spreadsheet for that

So now I'd like to keep track of how many candidates I've approved and processed for each client for each open position. Here is my spreadsheet for that. I have used the countifs function from the previous spreadsheet, called "Review Document" as follows:

Column C, Row 2 - counting sales associate for abercrombie who have been approved and not yet processed:

=COUNTIFS(
    'Review Document'!$B:$B,"abercrombie", 
    'Review Document'!$C:$C, "sales associate",
    'Review Document'!$E:$E,"yes",
    'Review Document'!$F:$F,"no")

I essentially do this for every single client, for every single role, for both column C and D. Imagine that there are ~300 rows with different companies and roles - The formula text changes every time to count if "position" and "company".

What I would like to do is now find an easy way to automatically apply a date range to all of these cells, without having to manually add a date criterion for every single formula. For example, in the first spreadsheet, there are dates in Feb, Mar, And April. Is there a way to apply a date range on my second spreadsheet so that it only counts the dates I specify? E.G. - apply some date range to ALL cells in that sheet so that it only counts if the date is 2/15/2022-3/31-2022? I would ultimately like to be able to change the date range quickly without having to manually add a date criterion to 300 cells, and then change it every time I want to see the numbers for a different date range. I was tinkering with conditional formatting but I haven't figured it out.

Thanks!

分享到QQ

分享到微博

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

发布评论

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

柠檬色的秋千 2025-01-26 13:15:25

使用：

=INDEX(QUERY(QUERY({A2:A, PROPER(B2:C), 
 IF((E2:E="yes")*(F2:F<>"yes"), 1, 0), 
 IF((E2:E="yes")*(F2:F= "yes"), 1, 0)}, 
 "select Col2,Col3,sum(Col4),sum(Col5) 
  where Col1 is not null "&
 IF(J1="",," and Col1 >= date '"&TEXT(J1, "yyyy-mm-dd")&"'")&
 IF(J2="",," and Col1 <= date '"&TEXT(J2, "yyyy-mm-dd")&"'")&" 
  group by Col2,Col3"), 
 "offset 1", ))

use:

=INDEX(QUERY(QUERY({A2:A, PROPER(B2:C), 
 IF((E2:E="yes")*(F2:F<>"yes"), 1, 0), 
 IF((E2:E="yes")*(F2:F= "yes"), 1, 0)}, 
 "select Col2,Col3,sum(Col4),sum(Col5) 
  where Col1 is not null "&
 IF(J1="",," and Col1 >= date '"&TEXT(J1, "yyyy-mm-dd")&"'")&
 IF(J2="",," and Col1 <= date '"&TEXT(J2, "yyyy-mm-dd")&"'")&" 
  group by Col2,Col3"), 
 "offset 1", ))