主页 / computer / 问题 / 1552787
Accepted
Monomeeth
Asked: 2020-05-18 22:13:45 +0800 CST

使用 Excel 中的 WEEKNUM 函数根据 7 天块计算周数，主要用于澳大利亚财政年度

  • 772

TLDR

有没有办法WEEKNUM在 Excel 中使用该函数来简单地根据 7 天块计算周数，而不考虑第一个日期在哪个工作日？

语境

我正在处理 Excel 中的一些非常大的数据文件（每个文件包含超过 800,000 行数据）。

每个文件中的两列用于计算周数（一个基于Calendar Year计算周数，另一个基于Financial Year计算周数）。在这种情况下，财政年度是从 7 月 1 日到 6 月 30 日。

在这两种情况下，“年”的值都基于同一Form_Finalised_Date列，周数是根据实际的 7 天计算的（即，与 1 月 1 日的工作日完全无关（对于日历年）或7 月 1 日是（在财政年度的情况下）。

作为解释，对于日历年，这意味着：

  • 1 月 1 日至 7 日将永远是第 1 周
  • 1 月 8 日至 14 日将永远是第 2 周
  • 1 月 15 日至 21 日将永远是第 3 周
  • 等等。

并且，在财政年度的情况下：

  • 7 月 1 日至 7 日将永远是第 1 周
  • 7 月 8 日至 14 日将永远是第 2 周
  • 7 月 15 日至 21 日将永远是第 3 周
  • 等等。

我目前的日历年公式 - 它是准确的，但是......

就计算日历年周数的公式而言，我必须满足以下条件：

=IF([@[Calendar Year]]=2015,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2016,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2017,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2018,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2019,WEEKNUM([@[Form_Finalised_Date]],12),
IF([@[Calendar Year]]=2020,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2021,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2022,WEEKNUM([@[Form_Finalised_Date]],16),
IF([@[Calendar Year]]=2023,WEEKNUM([@[Form_Finalised_Date]],17),
IF([@[Calendar Year]]=2024,WEEKNUM([@[Form_Finalised_Date]],11),
IF([@[Calendar Year]]=2025,WEEKNUM([@[Form_Finalised_Date]],13),
IF([@[Calendar Year]]=2026,WEEKNUM([@[Form_Finalised_Date]],14),
IF([@[Calendar Year]]=2027,WEEKNUM([@[Form_Finalised_Date]],15),
IF([@[Calendar Year]]=2028,WEEKNUM([@[Form_Finalised_Date]],16),
"N/A"))))))))))))))

如您所见，为了获得日历年周的预期结果，我必须确定 2015 年至 2028 年间每一年的 1 月 1 日是哪个工作日，这样我就可以为该年添加相应的Return_type参数。

然而，虽然这有效，但它：

  • 不能适应计算财政年度周数（至少我还没有找到办法）
  • 似乎不必要地长，因为我不得不写它以允许接下来的八年尝试和未来证明它！

相反，我希望有一个更简单的公式，它只查看Form_Finalised_Date列中的日期并通过有效地从 1 月 1 日开始计算周数，并将每 7 天计算为一周，依此类推。IF这样，随着数据源中出现更多年份，就无需手动添加更多功能。

我当前的财政年度公式 - 简洁，但不是 100% 准确

在花了很多时间尝试调整我的日历年公式来计算财政年度的周数之后，我最终放弃了，并想出了一些似乎与我需要的相当接近的东西。这个公式如下：

=WEEKNUM([@[Form_Finalised_Date]]-(1 + DATE(YEAR([@[Form_Finalised_Date]]),6,30)-DATE(YEAR([@[Form_Finalised_Date]]),1,1)))

但是，该公式并不总是计算出正确的结果（下面的屏幕截图中有更多详细信息）。

在此处输入图像描述

在上面的例子中：

  • C列显示了我上面计算日历年周数的很长公式的结果。结果是准确的，但是由于我使用该IF函数来指定根据年份的计算，因此它确实并不理想，因为这意味着我们需要在获取不同年份的数据时手动更改公式。我似乎也无法将其用于计算财政年度周数。
  • E 列显示了计算财政年度周数的预期结果（记住财政年度从 7 月 1 日到 6 月 30 日）。
  • F 列显示了我目前计算财政年度周数的公式的结果，但它并不总是产生正确的结果（见红色圆圈显示不正确的计算）。

问题

简而言之，有没有一种方法可以在 Excel 中使用 WEEKNUM 函数来简单地根据 7 天的时间块计算周数。在我的特定情况下，我需要能够从日历年的 1 月 1 日和财政年的 7 月 1 日开始执行此操作。

笔记：

  • 期望的结果是在 7 天的时间段上计算周数，而不考虑 1 月 1 日或 7 月 1 日所在的工作日。
  • 由于我的日历年公式有效（尽管有点笨拙），我最关心的是获得一个准确计算财政年度周数的公式。
microsoft-excel worksheet-function

1 个回答

  1. Best Answer

    您可以计算自特定开始日期以来的日期和星期，作为当前日期与您指定的开始日期之间的差异。

    我创建了这个：

    在此处输入图像描述

    在顶部，我有两个命名的单元格：

    1. cal_year_start_month
    2. fin_year_start_month

    日期列只包含数据。

    “日期年份”公式：

    =YEAR([@date])

    '日历年的第一个日期' 公式：

    =DATE([@[year of date]],cal_year_start_month,1)

    “日期”公式是“日期”和“日历年的第一个日期”之间的差异：

    =DAYS([@date],[@[first date of calendar year]])+1

    “一年中的一周”是：

    =ROUNDUP([@[day of year]]/7,0)

    “财政年度的第一天”是：

    =DATE(IF(MONTH([@date])<fin_year_start_month,[@[year of date]]-1,[@[year of date]]),fin_year_start_month,1)

    “财政年度日”是：

    =DAYS([@date],[@[first day of financial year]])+1

    “财政年度的一周”是：

    =ROUNDUP([@[day of financial year]]/7,0)

    'week 1' 列就在那里，所以我可以过滤数据以粘贴上面的屏幕截图：

    =OR([@[week of year]]=1,[@[week of financial year]]=1)

    如果你愿意，你可以将它们组合成...

    '日历年的一周'：

    =ROUNDUP((DAYS([@date],DATE(YEAR([@date]),cal_year_start_month,1))+1)/7,0)

    “财政年度的一周”：

    =ROUNDUP((DAYS([@date],DATE(IF(MONTH([@date])<fin_year_start_month,YEAR([@date])-1,YEAR([@date])),fin_year_start_month,1))+1)/7,0)
    • 1

相关问题