[µo°Ý] ¦p¦ó¨Ì·Ó¦~ÄÖ¼h¤À¨k¤k­Ó¼Æ?

wenwu748

¦p¦ó¥Î¨ç¼Æ¨Ì·Ó¦~ÄÖ¼h¤À¨k¤k­Ó¼Æ¤ÎªA°È¦~¸ê­pºâ¦U¶¥¬qªº­Ó¼Æ?
¸ê®Æ©ú²Ó.rar (8.01 KB)

¤U¸ü¦¸¼Æ: 12

2014-1-10 08:38

p212

¦^´_ 1# wenwu748
1¡B²Ä3¦C«D¨ã©T©w³W«h¡A¬G¡u«ö¦~ÄÖ¡v³¡¤À¤§¤½¦¡¦p¤U¡A½Ð°Ñ¦Ò¡I
Àx¦s®æU4=SUMPRODUCT(($D$4:$D$33=U$3)*($M$4:$M$33*1<12))
Àx¦s®æV4=SUMPRODUCT(($D$4:$D$33=V$3)*($M$4:$M$33*1<12))
Àx¦s®æW4=SUMPRODUCT(($D$4:$D$33=W$3)*($M$4:$M$33*1<17)*($M$4:$M$33*1>=12))
Àx¦s®æX4=SUMPRODUCT(($D$4:$D$33=X$3)*($M$4:$M$33*1<17)*($M$4:$M$33*1>=12))
Àx¦s®æY4=SUMPRODUCT(($D$4:$D$33=Y$3)*($M$4:$M$33*1<30)*($M$4:$M$33*1>=17))
Àx¦s®æZ4=SUMPRODUCT(($D$4:$D$33=Z$3)*($M$4:$M$33*1<30)*($M$4:$M$33*1>=17))
Àx¦s®æAA4=SUMPRODUCT(($D$4:$D$33=AA$3)*($M$4:$M$33*1<50)*($M$4:$M$33*1>=30))
Àx¦s®æAB4=SUMPRODUCT(($D$4:$D$33=AB$3)*($M$4:$M$33*1<50)*($M$4:$M$33*1>=30))
Àx¦s®æAC4=SUMPRODUCT(($D$4:$D$33=AC$3)*($M$4:$M$33*1<65)*($M$4:$M$33*1>=50))
Àx¦s®æAD4=SUMPRODUCT(($D$4:$D$33=AD$3)*($M$4:$M$33*1<65)*($M$4:$M$33*1>=50))
Àx¦s®æAE4=SUMPRODUCT(($D$4:$D$33=AE$3)*($M$4:$M$33*1>=65))
Àx¦s®æAF4=SUMPRODUCT(($D$4:$D$33=AF$3)*($M$4:$M$33*1>=65))
2¡B¨ä¾l¡u«ö±Ð¨|µ{«×¡v¡B¡u«ö¾·~§O¡v¡B¡u«öªA°È¦~¸ê¤À¡v½Ð¤ñ·Ó¡u«ö¦~ÄÖ¡v­×§ï¤½¦¡§Y¥i¡C

wenwu748

ÁÂÁÂP212ª©¥D¥i¥H¥Î¤F¡A¤£¹L«öªA°È¦~¸ê³¡¥÷¸Õ¤£¥X¨Ó¡A¥i§_½Ð¦AÀ°¦£¤@¤U¡AÁÂÁ¡C

samwang

¦^´_ 3# wenwu748

Àx¦s®æ BC4=SUMPRODUCT(--($N$4:$N$33<1))
Àx¦s®æ BD4=SUMPRODUCT(($N$4:$O$33>=1)*($N$4:$N$33<3))
Àx¦s®æ BE4=SUMPRODUCT(($N$4:$O$33>=3)*($N$4:$N$33<5))
Àx¦s®æ BF4=SUMPRODUCT(($N$4:$O$33>=5)*($N$4:$N$33<10))
Àx¦s®æ BG4=SUMPRODUCT(--($N$4:$N$33>10))

wenwu748

ÁÂÁÂsamwangª©¥D§Ú´N¬O¤Ö¤F--¤~¸Õ¤£¥X¨Ó

ML089

¦^´_ 4# samwang
¦^´_ 2# p212
¦^´_ 1# wenwu748
   

°ÝÃD:¦~ÄÖ¼h¤À¨k¤k­Ó¼Æ¤ÎªA°È¦~¸ê­pºâ¦U¶¥¬qªº­Ó¼Æ?

¦^ÂÐ:¥i¥H¥Î FREQUENCY ¨ç¼Æ¨Ó­pºâ¦U½d³ò¤º¥X²{ªº¦¸¼Æ¡C

¸Ñµª1:¨D¦~ÄÖ¼h¤À¨k¤k­Ó¼Æ
      U4 =INDEX(FREQUENCY(($D$4:$D$50=U3)*$M$4:$M$50,{0,11,17,29,49,64}),COLUMN(D4)/2)
      ¥k©Ô½Æ»s¦ÜAF4


¸Ñµª2:­pºâ¦U¶¥¬qªA°È¦~¸êªº­Ó¼Æ
      BC4 =INDEX(FREQUENCY($N$4:$N$50,{0,2,4,9}),COLUMN(A4))
      ¥k©Ô½Æ»s¦ÜBG4



FREQUENCY ¨ç¼Æ»¡©ú¦p¤U

­pºâ¬Y¤@­Ó½d³ò¤ºªº­È¥X²{ªº¦¸¼Æ¡A¨Ã¶Ç¦^¤@­Ó««ª½¼Æ­È°}¦C¡C¨Ò¦p¡A¥Î FREQUENCY ¨Ó­pºâ¬Y¨Ç½d³ò¤ºªº¦Ò¸Õ¦¨ÁZ¦U¦³´X­Ó¤H¡C¥Ñ©ó FREQUENCY ¦^°}¦C¡A¦]¦¹¥²¶·¿é¤J¬°°}¦C¤½¦¡¡C

»yªk

FREQUENCY(data_array,bins_array)

Data_array     ¬O¤@­Ó­n­pºâÀW²vªº¼Æ­È°}¦C©Î¼Æ­È°Ñ·Ó¦ì§}¡C¦pªG data_array ¤£§t¸ê®Æ¡A«h FREQUENCY ¶Ç¦^¤@­Ó¹sªº°}¦C¡C

Bins_array     ¬O¤@­Ó°}¦C©Î¤@­Ó°Ï¶¡ªºÀx¦s®æ½d³ò°Ñ·Ó¦ì§}¡A¥Î¨Ó¦s©ñ data_array ùتº¼Æ­È¤À²Õ¤§µ²ªG¡C¦pªG bins_array ¨S¦³¼Æ­È¡A«h FREQUENCY ¶Ç¦^ data_array ¤¤¤¸¯Àªº­Ó¼Æ¡C

wenwu748

EXCEL¯u¬O¦n¥Î ¡A¤èªk¤£¦Pºâ¥X¨Óªºµ²ªG¬O¤@¼Ëªº¡A·PÁÂML089ª©¥Dªº«ü¾É¡C