蔡高八斗 发表于 2021-11-17 12:13:00

求助！公式实现多列多条件去重统计

需要在限定D列和E列条件下，统计ABC三列去重后发明人数量

十指 发表于 2021-11-21 07:24:56

上传压缩后的Excel文件，说明需求，模拟结果

愛如指尖沙 发表于 2022-1-30 02:19:44

附件如上，请查收！

阮寳 发表于 2022-1-31 19:27:35

I3=SUM(IFERROR(MMULT(($D$2:$D$522<=2019)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,"<=2019",$E$2:$E$522,$H3)),0))

J3=SUM(IFERROR(MMULT(($D$2:$D$522=2020)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,2020,$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,2020,$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,2020,$E$2:$E$522,$H3)),0))

K3=SUM(IFERROR(MMULT(($D$2:$D$522=2021)*($E$2:$E$522=$H3),{1,1,1})/CHOOSE({1,2,3},COUNTIFS($A$2:$A$522,$A$2:$A$522,$D$2:$D$522,2021,$E$2:$E$522,$H3),COUNTIFS($B$2:$B$522,$B$2:$B$522,$D$2:$D$522,2021,$E$2:$E$522,$H3),COUNTIFS($C$2:$C$522,$C$2:$C$522,$D$2:$D$522,2021,$E$2:$E$522,$H3)),0))

都是数组公式下拉，技穷了，期待高手更好的思路

用户李明 发表于 2022-2-11 05:49:41

我就不会MM函数 我的天 就跟看天书一样空着的城市怎么弄

丁叮町 发表于 2022-2-20 15:30:16

I3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600<2019)*($E$2:$E$600=$H3)))

J3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600>=2019)*($D$2:$D$600<2020)*($E$2:$E$600=$H3)))

K3 数组公式
=COUNT(0/(N(MATCH($A$2:$A$600&$B$2:$B$600&$C$2:$C$600,$A$2:$A$600&$B$2:$B$600&$C$2:$C$600,)=ROW($1:$599))*($D$2:$D$600>=2020)*($D$2:$D$600<2021)*($E$2:$E$600=$H3)))
页: [1]
查看完整版本: 求助！公式实现多列多条件去重统计