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

[Âà¶K] °Q½×¤£­«½Æ²M³æ

¦^´_ 20# ANGELA
ÁÂÁÂ ANGELA
¤w¸g¸Ñ¨M°ÝÃD¤F

TOP

¦^´_ 17# gong

·PÁ gong ¤j¤j, ¤S¾Ç¨ì¤@ºØ¤èªk.

¤£¹L¥Î°ÊºA½d³ò­n¤ñ¥Î MAX §ó¦³®Ä²v.

¼Æ¾Ú¦bAÄæ

¥[»²§UÄæI, J
I2 =COUNTIF(OFFSET($A2,,,COUNTA(A:A)-1,),A2)
J1 = 0
J2 =MATCH(1,OFFSET($I$2,J1,,COUNTA(A:A)-1,),0)+J1
¤U©Ô

¤£­«ÂмƾÚ
K2 = IFERROR(INDEX(A:A,J2+1),"")
¤U©Ô

====================================
¥Î¦r¨åªk¼g¤FVBA Function UNIQUEp

N2=IFERROR(UNIQUEp(A:A,ROW()),"")
¤U©Ô

¤£ª¾­þ¦ì¤j¤j¥i§_¼g¤@¬qcode¨Ó­pºâ¤@¤U¦UºØ¤èªk©Ò»Ýªº­pºâ®É¶¡
  1. Function UNIQUEp(source As Range, num As Integer)
  2.     Dim newArray, myArray As Variant
  3.     rows_num = source.Rows.Count
  4.     'myArray = Range(Cells(source.Row, source.Column), Cells(source.Row + rows_num - 1, source.Column)).Value
  5.     myArray = Sheets(source.Parent.Name).Range(source.Address).Value
  6.    
  7.     Set Dic = CreateObject("scripting.dictionary")
  8.     For i = 1 To rows_num
  9.     If myArray(i, 1) <> "" Then
  10.         Dic((myArray(i, 1))) = ""
  11.     End If
  12.     Next
  13.    
  14.     arr = Dic.Keys
  15.    
  16.     UNIQUEp = arr(num - 1)
  17. End Function
½Æ»s¥N½X
¤£­«½Æ²M³æ Sonny.rar (20.05 KB)
ss

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-4-30 23:50 ½s¿è

¦^´_ 11# Hsieh
¦^´_ 13# gong

­è­è·Q¨ì¥t¤@ºØ¤èªk¤£¥Î MAX, LARGE µ¥°}¦C¨ç¼Æ©M°ÊºA½d³òªº¤èªk, (¥»¨Ó¥H爲MAX, LARGEµ¥°}¦C¨ç¼Æªº®Ä²v¸û§C), ¤£¹L¦n¹³ÁÙ¬O°ÊºA½d³ò¦³®Ä²v

¼Æ¾Ú¦bAÄæ

¥[»²§UÄæO
O1 = 0
O2 = IF(COUNTIF(A$2:A2,A2)=1,((8*SUM(L$1:L1)+1)^0.5-1)/2+1,"")
¤U©Ô

¤£­«ÂмƾÚ
P2 = INDEX(A:A,MATCH(ROW(A1),O:O,0))
¤U©Ô

¯uªº·Qª¾¹D­þºØ¤èªk³Ì¦³®Ä²v. ½Ð¤j¤j¼g¤@¬qcode¨Ó­pºâ¤@¤U¦UºØ¤èªk©Ò»Ýªº­pºâ®É¶¡
¤£­«½Æ²M³æ Sonny V2.rar (21.03 KB)
ss

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD