201803091713Excel篇:如何算出組距人數中之各組男女人數-->FREQUENCY及SUMPRODUCT

題目:共有18位人員, 年齡性別如下, 請以公式算出21~30、31~40、41~50、51~60、61~70各組距有幾人? 男女分別又為幾人?

       

解答:

選取 G2:G6 儲存格,
並輸入公式 =FREQUENCY(C2:C19,E2:E6)
再按下Ctrl+Shift+Enter 即得。

另外, 

H2 =SUMPRODUCT(($B$2:$B$19="F")*($C$2:$C$19>=21)*($C$2:$C$19<=30))
H3 =SUMPRODUCT(($B$2:$B$19="F")*($C$2:$C$19>=31)*($C$2:$C$19<=40))
H4 =SUMPRODUCT(($B$2:$B$19="F")*($C$2:$C$19>=41)*($C$2:$C$19<=50))
H5 =SUMPRODUCT(($B$2:$B$19="F")*($C$2:$C$19>=51)*($C$2:$C$19<=60))
H6 =SUMPRODUCT(($B$2:$B$19="F")*($C$2:$C$19>=61)*($C$2:$C$19<=70))

I2 =SUMPRODUCT(($B$2:$B$19="M")*($C$2:$C$19>=21)*($C$2:$C$19<=30))
I3 =SUMPRODUCT(($B$2:$B$19="M")*($C$2:$C$19>=31)*($C$2:$C$19<=40))
I4 =SUMPRODUCT(($B$2:$B$19="M")*($C$2:$C$19>=41)*($C$2:$C$19<=50))
I5 =SUMPRODUCT(($B$2:$B$19="M")*($C$2:$C$19>=51)*($C$2:$C$19<=60))
I6 =SUMPRODUCT(($B$2:$B$19="M")*($C$2:$C$19>=61)*($C$2:$C$19<=70))

 

沒有上一則|日誌首頁|沒有下一則
回應
聯絡方式:日誌悄悄話/FB訊息
請尊重智慧財產。
若有引用,也請標明出處喔,謝謝
貓貓一籮筐粉絲團
關鍵字
累積 | 今日
loading......
    沒有新回應!
FunP哈部落