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

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

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

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-6-19 11:08 ½s¿è

http://gb.twbts.com/index.php/topic,466.0.html
¯¸¥H¤W³sµ²´¿¸g°Q½×¹L¦¹¤@°ÝÃD
¨ä¤¤ªþ¥ó¤j³£¿ò¥¢,§â¥LÂk¯Ç¤@¤U¨Ñ¤j®a°Ñ¦Ò
¦³§ó¦n¤½¦¡ÅwªïÄ~Äò°Ñ»P°Q½×
¤£­«½Æ²M³æ.rar (7.78 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

¥»©«³Ì«á¥Ñ 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

¦^´_ 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

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

TOP

=IF(ROW(1:1)>MAX(B:B),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))

TOP

¥»©«³Ì«á¥Ñ charce ©ó 2012-1-3 04:01 ½s¿è

¦^´_ 17# gong
­Ó¤H¼±¥é¤W¤ègongªº¨ç¼Æ¶i¦æ½m²ß¡Aµo²{Á`¬O·|¯Ê±¼³Ì«áªº¤@µ§¸ê®Æ
°ÝÃD¬O¥X¦b¨ºÃä©O¡H¥i¤£¥i¥H«ü¥¿¤@¤U
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ OFFICE±M®a ©ó 2011-1-7 22:37 ½s¿è

³o«h¸Ì­±¦³
[­ì³Ð] Excel ¶i¶¥¿z¿ï¤j¥þ
¤£®¢¤U°Ý,¤£Â_¾Ç²ß,¤~·|¶i¨B

TOP

¥»©«³Ì«á¥Ñ gong ©ó 2010-7-2 09:13 ½s¿è

­«Âиê®Æ¦bAÄæ

¥[»²§UÄæB
B2=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,"")
¤U©Ô

¤£­«ÂмƾÚ
C2=IF(ROW()>MAX(B:B),"",INDEX(A:A,MATCH(ROW()-1,B:B,0)))
¤U©Ô

Book1.rar (2.14 KB)

ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 15# ¦Ñ®L

ÁÂÁ®Lª©¥D
§Úı±o±z³o­Ó³Ì¦n¥Î
¦Ó¥B¤£·|·í¾÷.....¤S¤£¥Î³Ü©@°Øµ¥®É¶¡...

¼Ï¯Ã¶i¶¥¿ï¨ú.jpg
2010-7-2 07:07



ÁÂÁ±z

TOP

¦^´_ 14# victorl
¦^´_ 13# gong
¦^´_ 12# asimov

©Ò¥Hµ²½×
1."­«ºâÀx¦s®æ"¬O¥¿±`²{¶H
2.©Ò¥H´î¤Ö¨Ï¥Î¤j¶qªº°}¦C¤½¦¡¡A¦³§U©ó´£¤É¤u§@®Ä²v³á
3.¯Â¬ã¨s¥i¦æ,¦p¥Î©ó¹ê°È¤W¸g±`·|¶]¤£°Ê
   ´X¥G¤£·|¥Î©ó¹ê°È¤W
   °ò¥»¤W°}¦C¤½¦¡¥i³z¹L»²§UÄæ¥i¥H»´ÃP§¹¦¨

«D±`ÁÂÁ¦U¦ì¥ý¶i¤Îª©¥Dªº¦^µª

TOP

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