计算Excel中多列的匹配项

nnsrf1az  于 2023-11-20  发布在  其他
关注(0)|答案(5)|浏览(132)

有一个表,其中包含月份和当月活跃用户的文本ID
| 一月|二月|三月|
| --|--|--|
| abx8bn| xcwq23MO| wesXT23n|
| Rtb8ui| abx8bn| abx8bn|
我需要计算有多少用户在1月活跃,在2月也活跃,其中-有多少是活跃在3月。换句话说,有多少ID在1月和2月是相同的,有多少ID在3月与他们匹配。什么是最好的方法?
预期输出就像一个漏斗,例如。
| | |
| --|--|
| 一月|10000名用户|
| 二月|5600 (满分10000)|
| 三月|2900 (共5600人)|
COUNTIFS()似乎不起作用。
=COUNT(MATCH())可以工作,但只适用于两列。

tjvv9vkg

tjvv9vkg1#

这种方法如何,基于公式:

=IF(ISERROR(MATCH(D3;B$3:B$8;0));"";D3)

字符串
以下是一张截图:


的数据
第2行包含第3行的公式文本,第10行包含第9行的公式文本。
在列“D”中,使用UNIQUE()函数显示了1月份存在的所有用户(如果您确定1月份只有一个用户条目,则可以用=A3替换)。
在下一列(2月份的列)中,Match()函数与IF(ISERROR())结合使用,用于检查2月份的可用性。
下一列(3月)是有趣的一列:因为你只对3月的条目感兴趣,这也存在于1月和2月,你可以简单地依赖于你已经在列“E”中的结果,并在此基础上进一步构建,导致提到的截图。
为什么最后一个专栏如此有趣?
好吧,你可以继续为一年中的下一个月创建列,一旦完成,你只需要编写一月和二月的函数,至于接下来的几个月,你可以拖动到下一列(“F3”和“F2”一样,向右拖动)。

zd287kbt

zd287kbt2#

跨列计数...的匹配项的匹配项

=LET(data,A1:L21,
    GetMatches,LAMBDA(data1,data2,LET(s,TOCOL(data2,1),
        TOCOL(XLOOKUP(TOCOL(data1,1),s,s),3))),
    s,SEQUENCE(COLUMNS(data)),
    h,TOCOL(TAKE(data,1)),
    d,DROP(data,1),
    mu,IFNA(DROP(REDUCE("",s,LAMBDA(rr,r,LET(
        c,CHOOSECOLS(d,r),
        HSTACK(rr,IF(r=1,c,
            GetMatches(CHOOSECOLS(rr,r),c)))))),,1),""),
    mc,BYCOL(mu,LAMBDA(r,SUM(--(r<>"")))),
    mr,BYROW(s,LAMBDA(r,LET(
        c,CHOOSECOLS(mc,r),
        IF(r=1,c&" users",c&" (out of "&CHOOSECOLS(mc,r-1)&")")))),
HSTACK(h,mr))

字符串


的数据


fnx2tebb

fnx2tebb3#

谢谢大家的回答!
绕了一个大圈子。
x1c 0d1x的数据
1.将所有唯一ID放在一列中
1.编写一个公式,检查ID是否在相应的月份
=IFERROR(VLOOKUP($A2;'User IDs by month'!A:A;1;FALSE)=$A2;FALSE)为1月=IFERROR(VLOOKUP($A2;'User IDs by month'!B:B;1;FALSE)=$A2;FALSE)为2月等。
1.然后在一个单独的列中使用一个简单的if公式,检查ID是否在您需要的所有月份中
=IF(AND($B2=TRUE;$C2=TRUE;$D2=TRUE;$E2=TRUE);1;0) 4个月
1.填写所有ID,然后将列中的1相加
Excel踢和尖叫试图这样做50万行,但嘿,它的工作

puruo6ea

puruo6ea4#

您可以尝试:


的数据
E2中的公式:

=BYCOL(REDUCE(A2:A8,B1:C1,LAMBDA(x,y,LET(z,TAKE(x,,-1),HSTACK(x,FILTER(z,COUNTIF(INDEX(B2:C8,,MATCH(y,B1:C1,0)),z)))))),LAMBDA(c,ROWS(TOCOL(c,3))))

字符串

qltillow

qltillow5#

你也可以这样做(但前提是每个月的用户名都是唯一的):

=LET(range,A2:C8,
seq,SEQUENCE(1,COLUMNS(range)),
rows,ROWS(range),
MAP(seq,LAMBDA(c,LET(pRange,INDEX(range,1,1):INDEX(range,rows,c),SUM(--(COUNTIF(pRange,pRange)=c))/c))))

字符串

=LET(range,A2:C8,
seq,SEQUENCE(1,COLUMNS(range)),
rows,ROWS(range),
MAP(seq,LAMBDA(c,LET(pRange,INDEX(range,1,1):INDEX(range,rows,c),qRange,CHOOSECOLS(range,c),SUM(--(COUNTIF(pRange,qRange)=c))))))


的数据
要对非唯一列执行此操作,您不能以相同的方式使用Countif,但可以定义一个函数myCountIf:

=LAMBDA(array1,array2,MAP(array2, LAMBDA(c, IF(c = "", 0, SUM(--(array1 = c))))))


和使用

=LET(range,A2:C8,
seq,SEQUENCE(1,COLUMNS(range)),
uniques,DROP(REDUCE("",seq,LAMBDA(a,c,IFERROR(HSTACK(a,UNIQUE(INDEX(range,,c))),""))),,1),
MAP(seq,LAMBDA(c,SUM(--(myCountIf(TAKE(uniques,,c),INDEX(uniques,,c))=c)))))

相关问题