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

[µo°Ý] ½Ð°Ý¥ý¶i¦³¨S¦³¥i¥H¥Î¥¨¶°©ÎVB¸Ñ¨M³o°ÝÃD¡H

[µo°Ý] ½Ð°Ý¥ý¶i¦³¨S¦³¥i¥H¥Î¥¨¶°©ÎVB¸Ñ¨M³o°ÝÃD¡H

¤å¨ã¦WºÙ        ¼Æ¶q
®Ñ§¨        10
®Ñ§¨        20
°v®Ñ¾÷        2
°v®Ñ¾÷        4
®Ñ®M        30
®Ñ§¨        30
°v®Ñ¾÷        9
®Ñ®M        50
       
       
¥X³fÁ`¶µ¥Ø       
3

°ÝÃD½Ð¨D¥ý¶i¸Ñ¨M

T1.jpg

¤£¥ÎVBA¡A¥Î¼Ï¯Ã¤ÀªRªí§Y¥i¸Ñ¨M¡I

TOP

¦^´_ 1# spensers
ªì¾Ç¤½¦¡, ¸Õ¸Õ¬Ý:
°²©w§Aªº¸ê®Æ¦b A1:B9,
C2 ¤½¦¡
=IF(COUNTIF(A2:A9,A2)>1,0,1) ©Î =IF(COUNTIF(A2:A9,A2)>1,"",1)
¦V¤U½Æ»s
C10 ¤½¦¡
=SUM(C2:C9)
À³¸Ó¦³§ó²±¶ªº¤½¦¡, ´N½Ð½Ñ¦ì¤j¤j«ü±Ð°Õ

TOP

¦^´_ 1# spensers

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
¥¼©R¦W.png
2014-3-26 14:02
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# spensers
¸Õ¸Õ¬Ý¡G
  1. '¦³Ãö Dictionary ª«¥óªº»¡©ú½Ð°Ñ¾\
  2. 'Dictionaryª«¥óªº»{ÃÑ»PÀ³¥Î
  3. 'http://gb.twbts.com/index.php?topic=510.0
  4. '§Ú¤]¬O²Ä¤@¦¸¨Ï¥Î Dictionary, ÁÙ¦bºN¯Á¤¤
  5. '¤U¦C VBA À³¸Ó¦X¥G§Aªº»Ý¨D

  6. '°²©w§Aªº¸ê®Æ¦b A:A
  7. Private Sub CommandButton1_Click()
  8.     Dim RngA, Cel As Range
  9.     Set d = CreateObject("Scripting.Dictionary")
  10.     Set RngA = [A2].Resize([A2].End(xlDown).Row - 1, 1)
  11.    
  12.     '¹ï©ó RngA ªº¨C¤@­ÓÀx®æ¨Ó»¡
  13.     For Each Cel In RngA
  14.         
  15.         '­Y Cel ªº­È¤£¦b Dictionary ¸Ì­±
  16.         If Not d.exists(Cel.Value) Then
  17.         
  18.            '±N Cel ªº­È¦s¤J Dictionary
  19.            d.Add Cel.Value, Cel.Value
  20.         End If
  21.     Next
  22.    
  23.     '¥X³fÁ`¶µ¥Ø = d.Count
  24.     [A13] = d.Count
  25. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# Hsieh
ÁÂÁª©¤jªº«üÂI,
­ì¨Ó:
=COUNTIF(A2:A9,A2:A9)
¥i¨D¥X ­«ÂÐ ªº¼Æ¶q,¦Ó
=1/COUNTIF(A2:A9,A2:A9)
¥iºâ¥X¬Y¶µªº 1/n, ¥þ³¡ªº 1/n ¥[°_¤Sµ¥©ó 1,
©Ò¥HÁ`©M´Nºâ¥X¨Ó¤F, ¯u¯«!! ÁÂÁÂ!!

TOP

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
ss

TOP

·PÁ¡A¥ý¶i­Ìªº¦^ÂСA¤u§@¦£¡A²{¦b¤~µªÁ¡A©êºp¡C

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD