数值归类到区间

金色和谐家园

各位老师好：

不用辅助列，如何进行数值相除后的值，进行多条件归类。
万分感谢
工作簿1.rar (7.97 KB, 下载次数: 46)

2025-12-8 10:29 上传

点击文件名下载附件

袁勇

1. =COUNT(1/(($E$8:$E$18=LOOKUP("座",$I$2:$I2))*(OFFSET($F$8:$F$18,($J2="24年"),)=$J2)*(OFFSET($J$8:$J$18,($J2="24年")*1,)>0+10%*(COLUMN(A1)=3))*(OFFSET($J$8:$J$18,($J2="24年")*1,)<=10%+10%*(COLUMN(A1)=3))))

复制代码

ming365

1. =LET(
   
2. f,LAMBDA(z,SCAN(,z,LAMBDA(x,y,IF(y>0,y,x)))),
   
3. IFERROR(MAP(f(E8:E19),F8:F19,H8:H19/G8:G19,LAMBDA(x,y,z,XLOOKUP(z,{10,20},VLOOKUP(x&y,HSTACK(f(I2:I5)&J2:J5,K2:N5),{2,4}),,1))),""))

复制代码

quqiyuan

=COUNT(0/((LOOKUP("座",$I$2:$I2)&$J2=LOOKUP(ROW($E$8:$E$19),ROW($E$8:$E$19)/($E$8:$E$19<>""),$E$8:$E$19)&$F$8:$F$19)*(($H$8:$H$19/$G$8:$G$19)>0.1*IF(COLUMN(A$1)=1,0.1,1))))-M2

fgq5910

=LET(
    filledE, SCAN(, E8:E19, LAMBDA(x, y, IF(y > "", y, x))),
    filledI, SCAN(, I2:I5, LAMBDA(x, y, IF(y > "", y, x))),
    lookupTable, HSTACK(filledI & J2:J5, K2:N5),
    safeRatio, IFERROR(H8:H19 / G8:G19, 0),
    MAP(filledE, F8:F19, safeRatio, LAMBDA(x, y, z,
        XLOOKUP(z, {10, 20}, VLOOKUP(x & y, lookupTable, {2, 4}, FALSE), "未匹配", 1)
    ))
)

hxj119

合并单元格显著增加工作量

1. =LET(a,F8:F19,arr,DROP(REDUCE("",a,LAMBDA(t,r,LET(c,OFFSET(r,,-1,),d,OFFSET(r,,2)/OFFSET(r,,1),VSTACK(t,IF(r="",{"","",""},HSTACK(IF(c="",OFFSET(r,-1,-1),c),r,(d>0)*(d<=10%),(d>10%)*(d<=20%))))))),1),res,GROUPBY(TAKE(arr,,2),TAKE(arr,,-2),SUM,0,0),SORT(DROP(res,1),{1,2},{1,-1}))

复制代码

良良

取消合并单元格后，O2:P2区域数组：
=INDEX(FREQUENCY(IF((E$8:E$19=I2)*(F$8:F$19=J2)*(F$8:F$19>0),H$8:H$19/G$8:G$19),{0,10,20}%),{2,3})

ericxzhou

hugeinfo

=LET(f,LAMBDA(x,FILTER(x,F8:F19<>"")),a,HSTACK(f(SCAN(,E8:E19,LAMBDA(x,y,IF(y<>"",y,x)))),f(F8:F19)),b,XLOOKUP(f(H8:H19/G8:G19),{10;20}%,{1;2},,1),DROP(PIVOTBY(a,b,b,COUNTA,,0,,0),1))

金色和谐家园

各位老师，能否在这个公式基础上改一下。各位的公式，看不懂。万分感谢
SUMPRODUCT((LOOKUP(ROW($8:$19),IF($E$8:$E$19="","",ROW($8:$19)),$E$8:$E$19)="甲")*($F$8:$F$19="25年")*(G$8:G$19>0)*(H$8:H$19/G8:G19>0)*(H$8:H$19/G8:G19<=10%))