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

[µo°Ý] ½Ð°Ý¯à±N¤º¤å¤¤ªº½d³ò¤½¦¡ÂX¤j¶Ü?

[µo°Ý] ½Ð°Ý¯à±N¤º¤å¤¤ªº½d³ò¤½¦¡ÂX¤j¶Ü?

¥»©«³Ì«á¥Ñ jackson7015 ©ó 2014-7-3 08:30 ½s¿è

½Ð°Ý¦U¦ì«e½ú¡F
¦p¦ó¤~¯à±N¤U¦C¤½¦¡¤¤ªº$V$5:$V$22¡A§ó§ï¦¨V¦C¾ã¦Cªº½d³ò?
{=COUNT(FIND(TRANSPOSE($V$5:$V$22),($E10)))}

¦pªG§ï¦¨$V:$V¡A·|³y¦¨ªÅ¥Õ³B¤]¤@¨Ö­pºâ
§Ú·Q¥u­pºâV¦C¦³¤º¤åªº³¡¤À´N¦n¤F

¨C¦¸­n·s¼W¤º¤å¡A´N»Ý­n¾ã­Ó­«·s½Õ¾ã¹L
±æ¯¸¤W¤j¤j¯àÀ°¦£¡AÁÂÁÂ~

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-3 09:03 ½s¿è

¦^´_ 1# jackson7015
«Øij¨Ï¥Î©w¸q¦WºÙ¡A¨ä¡u°Ñ·Ó¨ì¡v¥ÎOFFSET¨ç¼Æ¸Ñ¨M¡C
¨Ò¦p¡G
1¡B¸ê®Æ$V$5:$V$22¡A¨äÄæ¦ì¦WºÙ¦ì©óÀx¦s®æV4
©w¸q¦WºÙ¬°¡uDATA¡v¡A¡u°Ñ·Ó¨ì¡v¿é¤J
=OFFSET(Sheet1!$V$5,,,COUNTA(Sheet1!$V:$V)-1)
2¡B1#¤§­ì¤½¦¡­×§ï¬°
{=COUNT(FIND(TRANSPOSE(DATA),($E10)))}
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# p212

·PÁÂp212¤j¤j

¤½¦¡´ú¸Õ¦¨¥\
°õ¦æ¥¿±`

©w¸q¦WºÙ¨Ï¥Î¤è¦¡¡A¥Ø«eÁÙµLªk°Ñ³z
­è¦n¥i¥H½m²ß¬Ý¬Ý

ÁÂÁÂ~

TOP

¦^´_ 1# jackson7015

{=COUNT(FIND(TRANSPOSE($V$5:$V$22),($E10)))}

¤@¡BTRANSPOSE¨ç¼Æ¦³¨S¦³¥ÎÀ³¸Ó¤£¼vÅTµª®×
¡@¥i¥H§ï¬°¦p¤U
¡@{=COUNT(FIND($V$5:$V$22,$E10))}

¤G¡B°ÊºA¸ê®Æ½d³ò¬O«Ü­«­nªº§Þ¥©¡A¥i¥Hª½±µ©ñ¦b¤½¦¡¤¤©Î¥Î¦WºÙ©w¸q¡A¥Î¦WºÙ©w¸q¥i¥HÅý¤½¦¡Â²µu¬Ý°_¨Ó²M·¡©úÁA¡A­Y¦h³BÀ³¹B¤]¥i¥H¥[³t¤½¦¡³B²z
¡@¤@¯ë¨Ï¥ÎOFFSET¨ç¼Æ¡AOFFSET(¥ª¤W¨¤¦ì¸m,¦C°¾²¾,Äæ°¾²¾,°ª«×,¼e«×)
¡@¥H¥»¨Ò¥Ü½d¬°OFFSET($V$5,0,0,COUNTA($V$5:$V$999),1)
¡@±`¨£Â²¤Æ¬°¡@OFFSET($V$5,,,COUNTA($V$5:$V$999))¡A¦C°¾²¾,Äæ°¾²¾¡@¨S¦³¶ñ¼gµø¬°¢¯¡A³Ì«á¡@¼e«×¡@¨S¦³¶ñ¼g¤º©w¬°¢°¡]¼e«×¡@«e­±¦³ , ®É­n¶ñ1¡^
¡@±a¤J¤½¦¡¤º
    {=COUNT(FIND(OFFSET($V$5,,,COUNTA($V$5:$V$999)),$E10))}
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089
·PÁÂML089ª©¤jªº¦^À³«ü¾É

¤@¡BTRANSPOSE¨ç¼Æ¦³¨S¦³¥ÎÀ³¸Ó¤£¼vÅTµª®×
¬O»¡¡A­ì¥»¹Bºâ¤è¦¡¡A·|ª½±µ¦Û¦æ±q¦C¶}©l¹Bºâ¡A©Ò¥H¤~¤£»Ý­nTRANSPOSE¨ç¼Æ¥hÂà¸m¶Ü?

¤G¡B°ÊºA¸ê®Æ½d³ò¬O«Ü­«­nªº§Þ¥©¡A¥i¥Hª½±µ©ñ¦b¤½¦¡¤¤©Î¥Î¦WºÙ©w¸q¡A¥Î¦WºÙ©w¸q¥i¥HÅý¤½¦¡Â²µu¬Ý°_¨Ó²M·¡©úÁA¡A­Y¦h³BÀ³¹B¤]¥i¥H¥[³t¤½¦¡³B²z
´ú¸Õ¤½¦¡«á¡Aµo²{¥ç¥i±N½d³ò$V$5:$V$999§ï¦¨$V:$V¥þ½d³ò

­Ó¤H¤ñ¸û³ßÅwª½Æ[¤½¦¡¡A¦]¬°¦b¨Ï¥Î¦WºÙ©w¸qªº®É­Ô¡A»Ý­n¦A¥h¬Ý¬Ý¦WºÙ©w¸q¤ºªº¤½¦¡±ø¥ó
¤£¹L¦WºÙ©w¸q¯uªº¥i¥H¸`¬Ù«Ü¦h¤½¦¡¹Bºâ

TOP

¦^´_ 5# jackson7015

¤@¡BTRANSPOSE¨ç¼Æ¦³¨S¦³¥ÎÀ³¸Ó¤£¼vÅTµª®×
¬O»¡¡A­ì¥»¹Bºâ¤è¦¡¡A·|ª½±µ¦Û¦æ±q¦C¶}©l¹Bºâ¡A©Ò¥H¤~¤£»Ý­nTRANSPOSE¨ç¼Æ¥hÂà¸m¶Ü?

¦^ÂÐ: ´N¥Ø«eªº¤½¦¡¨Ó§PÂ_¤£»Ý­n¡C


¤G¡B°ÊºA¸ê®Æ½d³ò¬O«Ü­«­nªº§Þ¥©¡A¥i¥Hª½±µ©ñ¦b¤½¦¡¤¤©Î¥Î¦WºÙ©w¸q¡A¥Î¦WºÙ©w¸q¥i¥HÅý¤½¦¡Â²µu¬Ý°_¨Ó²M·¡©úÁA¡A­Y¦h³BÀ³¹B¤]¥i¥H¥[³t¤½¦¡³B²z
´ú¸Õ¤½¦¡«á¡Aµo²{¥ç¥i±N½d³ò$V$5:$V$999§ï¦¨$V:$V¥þ½d³ò

¦^ÂÐ:§ï¦¨$V:$V¥þ½d³ò¤ñ¸û²¼ä¦ý¹Bºâ·|¤ñ¸ûºC¡A­Y¤½¦¡«Ü¦h(´X¸U­Ó)®É·|¼vÅT¡A¤Ö¶q®É¨S®t¡C


­Ó¤H¤ñ¸û³ßÅwª½Æ[¤½¦¡¡A¦]¬°¦b¨Ï¥Î¦WºÙ©w¸qªº®É­Ô¡A»Ý­n¦A¥h¬Ý¬Ý¦WºÙ©w¸q¤ºªº¤½¦¡±ø¥ó
¤£¹L¦WºÙ©w¸q¯uªº¥i¥H¸`¬Ù«Ü¦h¤½¦¡¹Bºâ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089
·PÁÂML089¤j¤jªº¤£§[«ü±Ð´£ÂI
­ì¥»¨S·Q¨ì¹Bºâ®É¶¡°ÝÃD¡A·PÁ´£ÂI
¨Ï¥Î³¡¤À½d³ò·|¤ñ¸û¾A¥Î~

ÁÂÁÂ!!

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD