ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ­pºâ­«½Æ¶µªº­Ó¼Æ»PSUBTOTAL¿z¿ï¤p­p¦@¥Îªº°ÝÃD

[µo°Ý] ­pºâ­«½Æ¶µªº­Ó¼Æ»PSUBTOTAL¿z¿ï¤p­p¦@¥Îªº°ÝÃD

¦]¬°»Ý­n­pºâ¤£­«½ÆªºARTICLE NUMBER¼Æ¥Ø, ¦p¤U¹ÏÄæD¡A©Ò¥H¥Î¤Fªþ¹Ïªº¤½¦¡¡A¦ý¬O´NµLªk¨Ï¥ÎSUBTOTALªº¿z¿ï¥\¯à¡A½Ð°Ý¦U¦ì°ª¤â¦³¦ó¤èªk¡H

°}¦C¤½¦¡¡G
=COUNT(0/(MATCH(D14:D629,IF(SUBTOTAL(3,OFFSET(D1,ROW(D14:D629)-1,)),D14:D629),)=ROW(D14:D629)-13))

TOP

ÁÂÁ¸ѵª¡A¦ý¬O¦n¹³­pºâ¤£¤F¡C¥i§_À°¦£Àˬd¤@¤U¡HÁÂÁ¡C

Sample.rar (31.8 KB)

TOP

¦^´_ 3# pin_apple


{=COUNT(0/IF(SUBTOTAL(3,OFFSET(B1,ROW(B5:B620)-1,)),MATCH(A5:A620,A:A,)=ROW(5:620))) }
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

=COUNT(0/(MATCH(A5:A620,IF(SUBTOTAL(3,OFFSET(A1,ROW(A5:A620)-1,)),A5:A620),)=ROW(A5:A620)-4))

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-3-7 22:08 ½s¿è

¦^´_ 4# ML089


MATCH(A5:A620,A:A,) ¥u§ì²Ä¤@µ§, ­Y²Ä¤@µ§¬O«D¿z¿ï¶µ¦ÓÁôÂÃ, À³·|¤Öºâ¤@µ§???
±N¿z¿ï(2016/4/22)«áªºAÄæ¶K¨ì¥t¤@¤u§@ªí, ¿z¿ï¤£­«ÂЧY¥i¬Ý¥X,

TOP

¦^´_ 6# ­ã´£³¡ªL
¦^´_ 1# pin_apple

§A¦Ò¼{ªº¤ñ¸û©P¨ì

­×¥¿¥HFREQUENCY³B²z¡A¤£¥Î°}¦C¤½¦¡¿é¤J¡Aª`·N¤½¦¡¨Ï¥Î®É½d³ò621¦C»Ý­n¥[1¬°620+1=621
=COUNT(0/FREQUENCY(ROW(5:621),SUBTOTAL(3,OFFSET(A1,ROW(A5:A620)-1,))*MATCH(A5:A620,A:A,)))-1
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD