计算出现次数

进击的猪人

计算某一列去重数据的同时，包含某个值的次数，然后在这里的基础上，同时满足其他列“是”的条件计数，最后满足只是其中某个月份的计数

博爱风雨同舟

=SUM((ISNUMBER(FIND(A3,表1!O2:O4678))*MATCH(表1!O2:O4678,表1!O2:O4678,)=ROW(1:4677))/1)

kuangben8

去重后判断出现的次数：=COUNT(FIND(A3,UNIQUE(表1!O:O)))。其他的没明白啥意思。

进击的猪人

kuangben8 发表于 2024-4-28 14:31
去重后判断出现的次数：=COUNT(FIND(A3,UNIQUE(表1!O:O)))。其他的没明白啥意思。

就是去重后匹配到之后，还要满足p或者q或者u列任一有“是”的条件的计数

kuangben8

进击的猪人 发表于 2024-4-28 17:16
就是去重后匹配到之后，还要满足p或者q或者u列任一有“是”的条件的计数

去重后，数据已经不在是原来的数据了，再去匹配其他列的数据就不正确了吧？

进击的猪人

kuangben8 发表于 2024-4-28 21:35
去重后，数据已经不在是原来的数据了，再去匹配其他列的数据就不正确了吧？

去重后的数据都是唯一的，多个筛选条件应该是可以的

jisijie630

=COUNT(0/FIND(A3,表1!O$2:O$6000)/(MATCH(表1!O$2:O$6000,表1!O:O,)=ROW(表1!O:O)))

=COUNT(0/(MATCH(表1!O$2:O$6000,表1!O:O,)=ROW(表1!O:O))/FIND(A3,表1!O$2:O$6000)/(T(OFFSET(表1!P$1,ROW($1:$5999),{0,1,5}))="是"))

=COUNT(0/(MATCH(表1!O$2:O$6000,表1!O:O,)=ROW(表1!O:O))/FIND(A3,表1!O$2:O$6000)/(表1!AD$2:AD$6000="1月"))

=COUNT(0/(MATCH(表1!O$2:O$6000,表1!O:O,)=ROW(表1!O:O))/FIND(A3,表1!O$2:O$6000)/(T(OFFSET(表1!P$1,ROW($1:$5999),{0,1,5}))="是")/(表1!AD$2:AD$6000="1月"))

进击的猪人

jisijie630 发表于 2024-4-29 13:39
=COUNT(0/FIND(A3,表1!O$2:O$6000)/(MATCH(表1!O$2:O$6000,表1!O:O,)=ROW(表1!O:O)))

=COUNT(0/(MATCH( ...

你好用你的公式算了，值是错的

mykitami

B3公式
=LET(a,UNIQUE(HSTACK(表1!$O$2:$U$4678,表1!$AD$2:$AD$4678)),b," "&SUBSTITUTE(TRIM(TAKE(a,,1)),","," , ")&" ",SUM((LEN(b)-LEN(SUBSTITUTE(b," "&TRIM($A3)&" ",)))/LEN(" "&TRIM($A3)&" ")))

C3公式
=LET(z,UNIQUE(HSTACK(表1!$O$2:$U$4678,表1!$AD$2:$AD$4678)),a,FILTER(z,MMULT(N(CHOOSECOLS(z,2,3,7)="是"),{1;1;1})),b," "&SUBSTITUTE(TRIM(TAKE(a,,1)),","," , ")&" ",SUM((LEN(b)-LEN(SUBSTITUTE(b," "&TRIM($A3)&" ",)))/LEN(" "&TRIM($A3)&" ")))

D3公式
=LET(z,UNIQUE(HSTACK(表1!$O$2:$U$4678,表1!$AD$2:$AD$4678)),a,FILTER(z,TAKE(z,,-1)=$D$1),b," "&SUBSTITUTE(TRIM(TAKE(a,,1)),","," , ")&" ",SUM((LEN(b)-LEN(SUBSTITUTE(b," "&TRIM($A3)&" ",)))/LEN(" "&TRIM($A3)&" ")))

E3公式
=LET(z,UNIQUE(HSTACK(表1!$O$2:$U$4678,表1!$AD$2:$AD$4678)),a,FILTER(z,MMULT(N(CHOOSECOLS(z,2,3,7)="是"),{1;1;1})*(TAKE(z,,-1)=$E$1)),b," "&SUBSTITUTE(TRIM(TAKE(a,,1)),","," , ")&" ",SUM((LEN(b)-LEN(SUBSTITUTE(b," "&TRIM($A3)&" ",)))/LEN(" "&TRIM($A3)&" ")))

进击的猪人

mykitami 发表于 2024-5-1 17:47
B3公式
=LET(a,UNIQUE(HSTACK(表1!$O$2:$U$4678,表1!$AD$2:$AD$4678)),b," "&SUBSTITUTE(TRIM(TAKE(a,,1)) ...

您好，用公式带入了进去，答案还是有一定的偏差的