与日期有关的统计，不能用数据透视表，VB，只用函数公式

yes_cn

求助各位大神，如图，特点：

• 每行的日期并不连续，从远到近做红绿字记录，会跨年一直记录下去；
• 每个日期只会对应一个红字或绿字
  

求解：

• 某年某月绿字出现次数；
• 绿字最多月出现次数（找到并算出单月最多次数）。
  

要求：不能用数据透视表，VB；只用函数公式
附件

fsw0812

=SUMPRODUCT((YEAR(A3:A101)=YEAR(F5))*(MONTH(A3:A101)=MONTH(F5))*(C3:C101<>""))

limonet

=SUMPRODUCT((MONTH(A3:A101)=11)*(C3:C101<>""))

yes_cn

fsw0812 发表于 2020-12-24 12:53
=SUMPRODUCT((YEAR(A3:A101)=YEAR(F5))*(MONTH(A3:A101)=MONTH(F5))*(C3:C101""))

谢谢，第1问解决了。

yes_cn

limonet 发表于 2020-12-24 13:53
=SUMPRODUCT((MONTH(A3:A101)=11)*(C3:C101""))

谢谢你的思路。
需要调整一下，不然跨年后有相同的月份数，就乱了。

hcm19522

2020年11月绿字出现次数：=SUMPRODUCT((TEXT(A3:A101,"EM")=TEXT(F5,"EM"))*(C3:C101>0))

绿字单月最多现次数：{=MAX(MMULT(N(TEXT(A3:A101,"EM")-TRANSPOSE(TEXT(A3:A101,"EM")*(C3:C101>0))=0),Z3:Z101+1))

绿字单月最多现次数 年月：{=TEXT(RIGHT(MAX(MMULT(N(TEXT(A3:A101,"EM")-TRANSPOSE(TEXT(A3:A101,"EM")*(C3:C101>0))=0),Z3:Z101+1)*10^8+TEXT(A3:A101,"EMM")),8)*1+191100,"0年00月")

yes_cn

hcm19522 发表于 2020-12-24 14:17
2020年11月绿字出现次数：=SUMPRODUCT((TEXT(A3:A101,"EM")=TEXT(F5,"EM"))*(C3:C101>0))

绿字单月最多 ...

老大，我把你的公式代进表格，没有出正确结果，你再帮看一下呢。

另外，"EM"和第二个公式里出现的Z3:Z101能解释一下吗？
偶是新人，请多指教~~~

yes_cn

hcm19522 发表于 2020-12-24 14:17
2020年11月绿字出现次数：=SUMPRODUCT((TEXT(A3:A101,"EM")=TEXT(F5,"EM"))*(C3:C101>0))

绿字单月最多 ...

估计你"EM"是“年月”的意思，我把它改成"YM"，
第一个公式出正确结果了。
=SUMPRODUCT((TEXT(A3:A101,"YM")=TEXT(F5,"YM"))*(C3:C101>0))

第二个公式有引用Z列，我这是空白，还是错误。
=MAX(MMULT(N(TEXT(A3:A101,"YM")-TRANSPOSE(TEXT(A3:A101,"YM")*(C3:C101>0))=0),Z3:Z101+1))

第三个公式，出现结果是：1931年11月
{=TEXT(RIGHT(MAX(MMULT(N(TEXT(A3:A101,"YM")-TRANSPOSE(TEXT(A3:A101,"YM")*(C3:C101>0))=0),Z3:Z101+1)*10^8+TEXT(A3:A101,"YMM")),8)*1+191100,"0年00月")}

hcm19522

yes_cn 发表于 2020-12-24 14:32
老大，我把你的公式代进表格，没有出正确结果，你再帮看一下呢。

另外，"EM"和第二个公式里出现的Z3:Z ...

C3:C101>0 改 C3:C101<>"" 試試   測試是 OK
數組公式 :複製公式 不含 "=" ,貼上後加 "="   ;最前 "{" 代表 shift+ctrl+enter 三鍵一起按所產生 ,非程式裡面

yes_cn

hcm19522 发表于 2020-12-24 15:05
C3:C101>0 改 C3:C101"" 試試   測試是 OK
數組公式 :複製公式 不含 "=" ,貼上後加 "="   ;最前 "{" 代 ...

你的Z3:Z101，Z列是什么？
第三个公式我确用了数组 shift+ctrl+enter生成，月份对了，年份不对呢。