在以下 Excel 表中，列中的日期 A2:A23格式为dd-mm-yyyy。B2:B23天数使用公式 写入=TEXT(A2,"ddd")。在列中C2:C23输入“Y”或“N”。在列中输入了从 2016 年 10 月到 2017 年 3 月的每个月C。我如何自动写入范围中的所有月份A2:A23，例如在列中E并显示与每个月对应的“Y/N”条目数？
例如，我希望看到列E和中给出的结果。和F中的条目是手动填充的，但我想自动填充这些条目。EF
我知道类似的公式=SUMPRODUCT(--(MONTH(A2:A23)=MONTH(E)))，但为此，我需要在列中手动写入E月份A2:A23。
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| Date | Day | Entry(Y/N) | | Month | Number of Y/N | | | | | | | | | | | | | | |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 15-10-2016 | Sat | Y | | Oct | 4 | | | | | | | | | | | | | | |
| 17-10-2016 | Mon | Y | | Nov | 5 | | | | | | | | | | | | | | |
| 19-10-2016 | Wed | Y | | Dec | 5 | | | | | | | | | | | | | | |
| 23-10-2016 | Sun | Y | | Jan | 4 | | | | | | | | | | | | | | |
| 01-11-2016 | Tue | Y | | Feb | 2 | | | | | | | | | | | | | | |
| 05-11-2016 | Sat | N | | Mar | 2 | | | | | | | | | | | | | | |
| 11-11-2016 | Fri | Y | | | | | | | | | | | | | | | | | |
| 15-11-2016 | Tue | Y | | | | | | | | | | | | | | | | | |
| 22-11-2016 | Tue | Y | | | | | | | | | | | | | | | | | |
| 05-12-2016 | Mon | Y | | | | | | | | | | | | | | | | | |
| 09-12-2016 | Fri | N | | | | | | | | | | | | | | | | | |
| 11-12-2016 | Sun | Y | | | | | | | | | | | | | | | | | |
| 23-12-2016 | Fri | Y | | | | | | | | | | | | | | | | | |
| 26-12-2016 | Mon | Y | | | | | | | | | | | | | | | | | |
| 02-01-2017 | Mon | N | | | | | | | | | | | | | | | | | |
| 08-01-2017 | Sun | Y | | | | | | | | | | | | | | | | | |
| 10-01-2017 | Tue | Y | | | | | | | | | | | | | | | | | |
| 22-01-2017 | Sun | Y | | | | | | | | | | | | | | | | | |
| 08-02-2017 | Wed | N | | | | | | | | | | | | | | | | | |
| 10-02-2017 | Fri | Y | | | | | | | | | | | | | | | | | |
| 11-03-2017 | Sat | Y | | | | | | | | | | | | | | | | | |
| 15-03-2017 | Wed | Y | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | |
+------------+-----+------------+--+-------+---------------+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
更新：让我解释一下我做了什么。在 中，我使用自定义格式 mmmE2编写并格式化了此单元格，这有助于我在 中显示，在 中，我使用了公式，此公式帮助我在 中显示 Nov ，依此类推。在 中，我编写了给出了计数 4，随后在 中，我编写了，在 中，依此类推。直到 之前一切都很好，之后和都显示零结果。我认为命令 =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+1)) 中的增量在 之后给了我错误的结果，当然，当给定增量 month 时，它不会产生 Jan=A2OctE2E3=DATE(YEAR(E2),MONTH(E2)+1,DAY(E2))E3F2=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)))F3=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+1))F4 =SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)+2))DecJanFebMarchDecDec
答案1
如果您的日期从 A2 开始，请在 E2 中的 E 列中写入：
=VALUE(TEXT(A2,"mm"))
并将其向下拖动到 E23 的最后一个日期
选择所有 E2：E23 在 F2 中复制粘贴特殊值（它将变为 F2：F23）删除 E 列选择 E2：E23（新月份）
数据选项卡，删除重复项（仅从 E 列中删除，不要扩展选择）
您将每个月都有一次，在 F2 中写入您的公式：（
=SUMPRODUCT(--(MONTH($A$2:$A$23)=E2))数组公式）
如果您只想计算每个月的“Y”，请使用：（
=SUMPRODUCT(--(MONTH($A$2:$A$23)=VALUE(F2))*(--($C$2:$C$23="Y")))数组公式）在两个公式后
按Ctrl+ Shift+Enter然后您可以将其拖动
您可以直接在 D 列中使用公式，例如：
在 D2 中写入（仅在 D2 中）：
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A2)))
在 D3 中
=IF(MONTH(A3)<>MONTH(A2),SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(A3))),"")
也是数组公式并将其向下拖动
当月份更改时，它将给出计数
更新
关于您的评论，您仍然可以使用您的公式：
=SUMPRODUCT(--(MONTH($A$2:$A$23)=MONTH(E2)))
E2 而不是 A2
如果您在 E2 =A2 中写入，则意味着 E2 仍然是日期
，您可以使用月份（E2）
但您必须插入年份的标准，除非您在 2016 年和 2017 年没有同一个月份
由于一月份受到空单元格的影响
使用以下公式，它将执行与上述相同的工作，但还会测试单元格是否为空：
=SUM(IF($A$2:$A$23<>"",IF(MONTH($A$2:$A$23)=MONTH(E2),1,0),0))
按Ctrl+ Shift+Enter数组公式并拖动它，
如果 Month(A2:A23)=Month(E2)，则返回 1，
如果为空或不同，则返回 0，
总计将加 1
答案2
两种方法：
凌乱的公式：
设置E2为=A2。设置E3为
=IF(OR(E2=0,E2=""), "", MIN(IF(A$2:A$23>=DATE(YEAR(E2),MONTH(E2)+1,1), A$2:A$23, "")))
然后按Ctrl+ Shift+ Enter。将其向下拖动/填充。这将首先产生
15-Oct-2016
1-Nov-2016
5-Dec-2016
2-Jan-2017
8-Feb-2017
11-Mar-2017
0-Jan-1900
（这里我已将单元格格式化为内置格式14-Mar-2012，即实现为[$-409]d-mmm-yyyy;@。）
E2显然是A2第一个日期。然后DATE(YEAR(E2),MONTH(E2)+1,1)计算为 2016 年 11 月 1 日；即 中的日期之后的月份的第一天E2。最后， 中的完整公式E3（如上所示）找到 中的最小/最早（MINimum）日期A2:A23≥此日期。有点令人困惑的是，这是 2016 年 11 月 1 日，因为这在您的日期列表中。但是E4是 2016 年 12 月 5 日，因为这是 ≥ 2016 年 12 月 1 日的最低日期。
E8查找最小日期 ≥ 1-Apr-2017。没有，所以我们得到MIN("")，其计算结果为 0，显示为 0-Jan-1900。这就是公式以IF(OR(E2=0,E2=""), "", …
— 开头的原因，以防止公式本身陷入困境。如果将 Column 的显示格式更改 E为[$-409]mmm yyyy;;
（删除初始d-，（可选）将第二个更改-为空格，并将最后一个更改@为第二个;），您将得到
Oct 2016
Nov 2016
Dec 2016
Jan 2017
Feb 2017
Mar 2017
这会抑制月份日期的显示，并完全抑制 1900 年 1 月条目的显示。
两个辅助列：
设置M1为=DATE(YEAR(A2), MONTH(A2), 1)。设置N2为=N1+COUNTIF(M$2:M$23, E2)。设置E2为=IFERROR(SMALL(M$2:M$23, N1+1), "")。向下拖动/填充。列 M必须向下拖动到第 23 行；其他两列只需拖到足以生成不同月份的位置。这给了我们
+----+------------+------------+-----+
| | E | M | N |
+----+------------+------------+-----+
| 1 | | | |
| 2 | 1-Oct-2016 | 1-Oct-2016 | 4 |
| 3 | 1-Nov-2016 | 1-Oct-2016 | 9 |
| 4 | 1-Dec-2016 | 1-Oct-2016 | 14 |
| 5 | 1-Jan-2017 | 1-Oct-2016 | 18 |
| 6 | 1-Feb-2017 | 1-Nov-2016 | 20 |
| 7 | 1-Mar-2017 | 1-Nov-2016 | 22 |
| 8 | | 1-Nov-2016 | 22 |
| 9 | | 1-Nov-2016 | 22 |
| 10 | | 1-Nov-2016 | 22 |
| 11 | | 1-Dec-2016 | 22 |
| 12 | | 1-Dec-2016 | 22 |
| ︙ | ︙ |
其中 Column M跟踪 Column A
并且 Column N仅重复电子表格中的总天数。
列 M清楚地显示了列中相应日期的月份的第一天A。
E2是这些值中最小的一个；SMALL(M$2:M$23, 1)。 计算列中等于此值的N2日期 （10 月有 4 个日期）并将其添加到空白（即零）。（如果需要，您可以输入一个明确的值。）然后是第 5 个最小的日期，。等等。当然，您可以再次格式化列 以抑制月份日期的显示。MN10N1E3SMALL(M$2:M$23, 5)E
我突然想到，在我输入完以上所有内容之后，我可以用INDEX而不是 来完成此操作SMALL。
笔记
上述两种解决方案都仅列出 Column 中存在的月份 A。例如，如果 Column A的日期为 2016 年 10 月、2016 年 11 月、2016 年 12 月、2017 年 2 月和 2017 年 3 月（但不包括 2017 年 1 月），则 Column E将列出 2016 年 10 月、2016 年 11 月、2016 年 12 月、2017 年 2 月和 2017 年 3 月（但不包括 2017 年 1 月）。我这样做是因为问题说，“我如何自动写出范围中的所有月份A2:A23，...？”如果您想包括每一个从 2016 年 10 月（A2）到 2017 年 3 月（A23）的月份，包括列中未出现的月份 A，您需要澄清问题。
无论哪种方式，
要计算每个月的“Y”行，请设置F2为
=SUMPRODUCT(--(MONTH($A$2:$A$99)=MONTH(E2)), --($C$2:$C$99="Y"))
并向下拖动/填充。
PS故障诊断
因此，您已经了解了问题所在：Column 中的最后八个日期 A分别是 2017 年 1 月、2 月和 3 月，因此它们的MONTH()值分别为 1、2 和 3。但是，当您在 等MONTH(A2)+3中使用 等时F5，您正在计算MONTH()值为 13、14 和 15 的行（当然，这些行不存在）。一旦您在 Column 中获得了月份列表 E，您就应该在计算 Column 时使用这些值 F。