请教统计问题

yvll

请教统计问题，谢谢！

跳跃统计.rar (9.7 KB, 下载次数: 26)

2024-8-24 18:52 上传

点击文件名下载附件

良良

AH4=SUM(SUMIF(J$2:AF$2,TRIM(MID(SUBSTITUTE(AH$1,",",REPT(" ",50)),ROW($1:$10)*50-49,50)),J4:AF4))
AH17=SUM(SUMIF(J$15:AF$15,UNIQUE(TOROW(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1}),1),J17:AF17))
三键，下拉。

leepoison

1. =BYROW(J4:AF7,LAMBDA(x,SUMPRODUCT(BYCOL(--(TOCOL(--TEXTSPLIT($AH$1,","))=$J$2:$AF$2),SUM),x)))

复制代码

第二个问题跟第一个问题有区别吗

yvll

良良 发表于 2024-8-24 19:30
AH4=SUM(SUMIF(J$2:AF$2,TRIM(MID(SUBSTITUTE(AH$1,",",REPT(" ",50)),ROW($1:$10)*50-49,50)),J4:AF4))
A ...

非常感谢您！
第一问对，第二问得出的都是0，请您看一下。

剑指E

1. =SUM(INDEX(J4:AG4,TEXTSPLIT($AH$1,",")))
   
2. =SUM(XLOOKUP(UNIQUE(TOCOL(TEXTSPLIT($AH$16,",")+{1;0;-1})),$J$15:$AF$15,J17:AF17,0))

复制代码

良良

yvll 发表于 2024-8-24 19:39
非常感谢您！
第一问对，第二问得出的都是0，请您看一下。

开个火车：

AH17：

1. =SUM(SUMIF(J$15:AF$15,INDEX(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1},N(IF(1,INT(SMALL(IF(FREQUENCY(IFERROR(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1},),IFERROR(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1},)),INT(ROW($3:$32)/3)/1%+MOD(ROW($3:$32),3)+1),ROW($1:$30))/100))),N(IF(1,MOD(SMALL(IF(FREQUENCY(IFERROR(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1},),IFERROR(TRIM(MID(SUBSTITUTE(AH$16,",",REPT(" ",50)),ROW($1:$10)*50-49,50))+{-1,0,1},)),INT(ROW($3:$32)/3)/1%+MOD(ROW($3:$32),3)+1),ROW($1:$30)),100)))),J17:AF17))

复制代码

高山磐石

AH4,   =DROP(REDUCE(0,J4:J7,LAMBDA(X,Y,VSTACK(X,SUM(CHOOSECOLS(OFFSET(Y,,,1,23),VALUE(TEXTSPLIT(AH1,",",))))))),1)

AH17

=DROP(REDUCE(0,J4:J7,LAMBDA(X,Y,VSTACK(X,SUM(CHOOSECOLS(OFFSET(Y,,,1,23),LET(XXX,SORT(UNIQUE(VALUE(TOCOL(LET(NO,TEXTSPLIT(AH16,","),VSTACK(DROP(TRANSPOSE(REDUCE(0,NO,LAMBDA(X,Y,VSTACK(X,Y+{1,-1})))),,1),NO)))))),FILTER(XXX,XXX<>0))))))),1)

ww87725244

=SUM(OFFSET($I4,,TEXTSPLIT($AH$1,",")))
=SUM(OFFSET($I17,,UNIQUE(TOCOL(--TEXTSPLIT($AH$16,",")+{-1;0;1}))))

高山磐石

剑指E 发表于 2024-8-24 19:47

TEXTSPLIT($AH$16,",")+{1;0;-1})
这个牛，学习了

我走我流

第二问  =SUM(SUBTOTAL(9,OFFSET(I17,,IF(FREQUENCY(TEXTSPLIT($AH$16,",")+{0;1;-1},$J$15:$AF$15),TRANSPOSE($J$15:$AF$15)))))

用365函数

=SUM(SUBTOTAL(9,OFFSET(I17,,UNIQUE(TOCOL(TEXTSPLIT($AH$16,",")+{0;1;-1})))))