按等级、年龄、性别计总数和计性别为女的数

ztjliuchunlin

A-D列为数据区，右边的二维表为统计区，求公式

yfan17

J3单元格公式，下拉到J11，复制，粘贴到L,N,P,R列，统计性别增加条件。H列公式则减少一个条件。

1. =COUNTIFS($C$2:$C$59,">="&TEXTBEFORE(J$1,"-"),$C$2:$C$59,"<="&TEXTAFTER(J$1,"-"),$D$2:$D$59,"="&TEXTBEFORE($G3,"级"))

复制代码

yfan17

35以下修改为0-35，可以统一使用下面的公式，如I3单元格：

1. =COUNTIFS($B$2:$B$59,"=女",$C$2:$C$59,">="&TEXTBEFORE(H$1,"-"),$C$2:$C$59,"<="&TEXTAFTER(H$1,"-"),$D$2:$D$59,"="&TEXTBEFORE($G3,"级"))

复制代码

ztjliuchunlin

yfan17 发表于 2024-10-29 01:19
J3单元格公式，下拉到J11，复制，粘贴到L,N,P,R列，统计性别增加条件。H列公式则减少一个条件。

感谢帮助，我重新更新了附件，版主的代码可以重新更新下吗？

yfan17

多条件复杂计数.rar (11.74 KB, 下载次数: 5)

2024-10-29 07:26 上传

点击文件名下载附件

mykitami

H3公式
=TEXT(DROP(REDUCE("",H1:S1,LAMBDA(x,y,LET(a,--TEXTSPLIT(LOOKUP("座",$H$1:y),"-",,1)+{0,1},HSTACK(x,IF(y<>"",BYROW(COUNTIFS(D:D,G3:G11,C:C,"<"&a)*{-1,1},SUM),BYROW(COUNTIFS(D:D,$G$3:$G$11,B:B,RIGHT(OFFSET(y,1,)),C:C,"<"&a)*{-1,1},SUM)))))),,1),"#")

pjiang

一个countifs全部搞定了。

ericxzhou

Python+Pandas:
import pandas as pd

df = pd.read_excel('Sample533.xlsx', usecols='a:d')
df['年龄段'] = pd.cut(df['年龄'], bins=[0,35,40,45,50,55,59], labels=['0-35','36-40','41-45','46-50','51-55','56-59'])
df1 = df.groupby(['等级', '年龄段']).agg(总数=pd.NamedAgg('人员姓名', 'count'), 其中女=pd.NamedAgg('性别', lambda x: (x == '女').sum()))
df1 = df1.unstack().swaplevel(0, 1, axis=1).sort_index(axis=1, ascending=[True, False]).fillna(0)
df1

hxj119

一个公式搞定

1. =DROP(REDUCE("",G3:G11,LAMBDA(m,n,VSTACK(m,DROP(REDUCE("",TOCOL($H$1:$S$1,1),LAMBDA(x,y,LET(a,TEXTSPLIT(y,"-"),t,$B$2:$B$59,s,$C$2:$C$59,d,$D$2:$D$59,res,COUNTIFS(s,">="&INDEX(a,1),s,"<="&INDEX(a,2),d,n),sex,COUNTIFS(s,">="&INDEX(a,1)/1,s,"<="&INDEX(a,2)/1,d,n,t,"女"),HSTACK(x,HSTACK(res,sex))))),,1)))),1)

复制代码

你是那块小饼干

1. =COUNTIFS($B$2:$B$59,"女",$D$2:$D$59,5)

复制代码

1. =COUNTIFS($B$2:$B$59,"女",$C$2:$C$59,"<=35",$D$2:$D$59,5)

复制代码