当前位置:首页 > 办公 > Excel教程 > 正文内容

N多人分组完成M个项目,excel怎么统计每个人参与了哪些项目

酷网2周前 (10-10)Excel教程22

一位朋友留言,说他们项目部所有的人,每五人为一小组,完成了很多项目。现在,要论功行赏,按分组名单,统计每人参与了哪些项目。

他问有没有公式,一次完成统计。

为了好述,将数据简化如下:

最终要完成:按照表一项目分组,完成二人员参与项目统计。

公式实现

在H2单元格输入公式:

=IFERROR(INDEX($A$1:$A$7,SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1))),””),以Ctrl+Shift+Enter三键组合结束,然后公式向右向下填充,即可得到结果。

如下图:

公式实现

公式解析

{=($G2<>$B$2:$D$7)*100}

将G2的人员“王一”,依次与B2:D7姓名相比较,如果不同,返回TURE,如果相同,返回FALSE。再将结果一一乘以100,凡是不等于“王一”的,返回100,等于“王一”的,返回0。

结果如下:

{0,100,100;100,100,100;100,100,100;100,100,100;100,0,100;0,100,100 }(为方便描述,称为数组一)

如果行数较多,可以乘以更大的10000等。

{=($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7)}

将数组一结果依次与所在行相加,

返回结果:

{2,102,102;103,103,103;104,104,104;105,105,105;106,6,106;7,107,107 }(为方便描述,称为数组二)

SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1))

在数组二中,取第“COLUMN(A$1)”小的数值。A1是第一列,也就是取数值二中第1小的数值2;当公式向右填充一列,变为取第“COLUMN(B$1)”小的数值,即第2小的数值6;当公式再向右填充一列,变为取第“COLUMN(C$1)”小的数值,即第3小的数值7。

这样,得到数组:

{2;6;7;102;……}

INDEX($A$1:$A$7,SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1)))

当此公式在H2时,在A1:A7内,取出第2行的项目一;

公式向右填充一列,到I列,在A1:A7内,取出第6行的项目五;

公式再向右填充一列,到J列,在A1:A7内,取出第7行的项目六;

再往后取第102……行,是不存在的。

=IFERROR(INDEX($A$1:$A$7,SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1))),””)

用IFFERROR函数,如果查找错误,返回空值。

此公式,理解起来有一定难度,建议大家下载素材,一步一步写出来。

写的时候,注意使用“公式求值”功能对公式进行一步一步的运算,公式求值能够帮助你一步一步分析公式,如下动图:

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39597.html

分享给朋友:

相关文章

Excel中怎么批量隔行插入空行

Excel中怎么批量隔行插入空行

  Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。当我们需要在表格当中,每隔一行插入一行空白行。如果一行一行手...

跨表排名其实很简单,一个RANK函数就搞定了

跨表排名其实很简单,一个RANK函数就搞定了

单张工作表中的数据排名好说,但若在多张工作表之间进行数据排名,在不进行工作表合并的情况下如何实现? 看下面这个示例: 某小团体举办跳远、百米、铅球三项运动会,分3组进行,组内要排名并设有奖项;团体内还要进行3个组的全员排...

怎么快速创建Excel表格副本 快速创建Excel表格副本图文教程

怎么快速创建Excel表格副本 快速创建Excel表格副本图文教程

  如何快速创建Excel表格副本?Excel是微软开发的办公室软件,可以在电脑,手机上运行。可以对数据进行统计分析和计算,能绘制图表等功能。Excel表格副本该如何创建呢,下面就给大...

使用RANK函数轻松搞定并列排名

使用RANK函数轻松搞定并列排名

在Excel中,要对分数进行排名非常简单,只需要用鼠标点击排序即可完成,但是要想知道某个分数在一堆人中的排名,用这种方法就有点麻烦,尤其是在遇到存在并列排名情况的时候,这时候我们可以用RANK函数、RANK.EQ函数或者...

sumifs函数应用实例

sumifs函数应用实例

今天我们来学习一个工作中应用Sumifs()函数汇总数据的应用实例。   如下图所示的表格,我们需要知道每个产品代码在审核日期2019-12-01 之前的库存累计该怎么汇总呢?通常我们需要将日期列设置筛选<...

Excel条件格式——完全相同的行填充相同颜色

Excel条件格式——完全相同的行填充相同颜色

这是今天一位朋友提出来的问题: 能不能在一大堆数据里,将完全相同的行标出相同的颜色,以核查数据? 当然可以,用条件格式就可以解决。 方法实现 1、选中数据,【开始】——【条件格式】——【新建规则】: 2、【新建格式规则...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。