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

¦p¦ó§ä¥X­«½Æ¸ê®Æ

¦p¦ó§ä¥X­«½Æ¸ê®Æ

§Ú¥u·|¦b¨C¦æ¸ê®Æ®Ç¥ÎCOUNTIF ­pºâ¦h©ó1ªº¸ê®Æ, ¦A¥Î¿z¿ï±N­«½Æ¸ê®Æ§ä¥X
¦ý¦]¬°¦³¹L¸U¦æ¸ê®Æ¦Ó·|¦nºC,
½Ð°Ý·|¦³Â²³æ¤èªk¥i¥H¿z¿ï¥X­«½Æ¸ê®Æ©Î½Æ»s¨ì¥t¤@¤u§@ªí¶Ü?
½Ð«ü±Ð

¥»©«³Ì«á¥Ñ p212 ©ó 2013-12-21 14:50 ½s¿è

¦^´_ 1# enoch
¦p¹Ï¡AÀx¦s®æA2-A17§t¦³­«½Æ¸ê®Æ¡A©óÀx¦s®æB2¿é¤J°}¦C¤½¦¡¡G
=IFERROR(INDEX(A:A,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$17),)),"")
¦V¤U½Æ»s¤½¦¡§Y¥i§ä¥X¤À¥¬©óÀx¦s®æA2-A17ªº°ß¤@­È¡A½Ð°Ñ¦Ò¡I

screenshot_10.jpg (113.15 KB)

screenshot_10.jpg

TOP

¦^´_ 1# enoch
¦p2#ªº¹Ï¥Ü ²Ä¤@­Ó¤u§@ªí ¿z¿ï¤£­«½Æªº¸ê®Æ (Unique:=True) ¨ì²Ä2­Ó¤u§@ªí
  1. Option Explicit
  2. Sub Ex()
  3.     Sheets(1).Range("A1:A17").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets(2).Range("A1"), Unique:=True
  4. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥ÎCOUNTIF«ÜºC
«Øij¥Î ¶i¶¥¿z¿ï - ¤Ä¿ï ¤£­«½Æ¸ê®Æ - ¤Ä¿ï ½Æ»s¨ì¨ä¥L¦a¤è
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦hÁ¦U¦ì«e½ú«ü±Ð, ¦ý­YªG§Ú·Q§ä¥X¨º¨Ç¸ê®Æ¬O­«½Æ©O?
¦]­«½Æ¥Nªí¤F¸ê®Æ¿é¤J¥X¿ù, ­YªGµo²{­«½Æ, §ä¥X­«½Æ¸ê®Æ
½Ð¦A«ü±Ð

TOP

±Æ§Ç«á¦A³B²z·|¤ñ¸û§Ö
¦p¦³¸Uµ§¸ê®Æ¡A¥ÎCOUNTIF·|«ÜºC¡A«Øij¥ý¥Î±Æ§Ç«á¦A³B²z·|¤ñ¸û§Ö

¨Ò¦p AÄæ ±Æ§Ç«á
©ó B2¿é¤J¤½¦¡ =IF(OR(A2=A1,A2=A3),1,0) ÂI¥k¤U¶Â¤èÂI©¹¤U¥þ³¡½Æ»s
1ªí¬O­«ÂСA0ªí¥Ü¨S¦³­«ÂÐ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦pªG¤£±Æ§Ç

B2 =IF(MATCH(A2,A:A,)=ROW(),0,1)
0ªí¥Ü¨S¦³­«ÂСA1ªí¬O¦³­«ÂÐ

¦ý¦¹¤½¦¡¥u¬O¼Ð¥Ü²Ä¤Gµ§¥H«á­«Âиê®Æ¬° 1 ¡A¶i¦æ¿z¿ï 1 «á¡A¤@¯ëª½±µ§R°£¡A©Î°Å¤U¶K¦Ü¥LÄæ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089


±Æ§Ç«á¦A³B²z·|¤ñ¸û§Ö
¦p¦³¸Uµ§¸ê®Æ¡A¥ÎCOUNTIF·|«ÜºC¡A«Øij¥ý¥Î±Æ§Ç«á¦A³B²z·|¤ñ¸û§Ö

¨Ò¦p AÄæ ±Æ§Ç«á
©ó B2¿é¤J¤½¦¡ =IF(OR(A2=A1,A2=A3),1,0) ÂI¥k¤U¶Â¤èÂI©¹¤U¥þ³¡½Æ»s
1ªí¬O­«ÂСA0ªí¥Ü¨S¦³­«ÂÐ
   
¥H¤W­«½Æ¶µ¥þ³¡¼Ð¥Ü¬° 1

­Y»Ý­n²Ä¤G¶µ¥H«á­«ÂмХܬ°1®É
B2 =IF(A2=A1,1,0)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD