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

[µo°Ý] ½Ð°Ý·íA1¿é¤J¼Æ¦r4®É§ä¥X¤U¦C¸ê®Æ¦P¼Æ¦rªº¤½¦¡

[µo°Ý] ½Ð°Ý·íA1¿é¤J¼Æ¦r4®É§ä¥X¤U¦C¸ê®Æ¦P¼Æ¦rªº¤½¦¡

¥»©«³Ì«á¥Ñ PD961A ©ó 2010-7-13 14:33 ½s¿è

½Ð°Ý¦U¦ì¥ý¶i
·íA1¿é¤J¼Æ¦r4®É..¦p¦ó§ä¥X¤U¦C¸ê®Æ¦P¼Æ¦rªº¤½¦¡
ÁÂÁÂ..



1.rar (5.58 KB)

½Ð°Ý¦U¦ì¥ý¶i
·íA1¿é¤J¼Æ¦r4®É..¦p¦ó¿z¿ï¥X¤U¦C¸ê®Æ¦P¼Æ¦rªº¤½¦¡
ÁÂÁÂ..
PD961A µoªí©ó 2010-7-12 19:03



§A·Q°Ýªº¬O®æ¦¡¤Æ±ø¥ó¹ï§a
twbts20100712conditionalformating.zip (8.22 KB)

¦ba2 ³]­Ó¤½¦¡
=A2=$A$1
µM«á§Û¨ì·Q­nªº¨ä¥L®æ¤l
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 2# kimbal


    ÁÂÁÂ¥ý¶iªº¦^¤å
¥i¥H¦A½Ð°Ý±z
³]©w®æ¦¡¤Æ±ø¥óÃC¦â·s¼W¥u¦³3¦C(Äæ)A~C
­Y­n±qA~HÄæ³£¬O¦P¼Ë¥i¥H¿é¤J¼Æ¦r«á±o¨ìÃC¦âÅܤÆ
½Ð°Ý­n«ç»ò§ï¤½¦¡?©Î¬O³]©w®æ¦¡....
ÁÂÁ±z..

TOP

¦^´_  kimbal


    ÁÂÁÂ¥ý¶iªº¦^¤å
¥i¥H¦A½Ð°Ý±z
³]©w®æ¦¡¤Æ±ø¥óÃC¦â·s¼W¥u¦³3¦C(Äæ)A~C
­Y­n±qA~H ...
PD961A µoªí©ó 2010-7-13 07:51



excel 2003©Î¥H«eªº ®æ¦¡¤Æ±ø¥ó ¥u¥i¥H ¦³3ºØ±ø¥ó, ¦A¥[±ø¥óªº¸Ü­n¥Î¤Wvba ¼g¤@­Ó ®æ¦¡¤Æ±ø¥ó ¥X¨Ó¤F
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_  kimbal


    ÁÂÁÂ¥ý¶iªº¦^¤å
¥i¥H¦A½Ð°Ý±z
³]©w®æ¦¡¤Æ±ø¥óÃC¦â·s¼W¥u¦³3¦C(Äæ)A~C
­Y­n±qA~H ...
PD961A µoªí©ó 2010-7-13 07:51



    ³o®æ¦¡¤Æ±ø¥ó¥D­n¥Î³~¬°¦ó?
alexwin

TOP

¦^´_ 5# alexwin


    ¥D­n­nªí¹Fªº·N«ä¬O
·í²Ä¤@¦CA1.B.C~°²³]¨ìH1
¿é¤J¼Æ¦r(¹w³]¬°1~100)
¦ý¬O©³¤Uªº¸ê®Æ·|Åܰʦì¤l(¸ê®Æ·|¦]¬°¤H¬°¿é¤J¦Ó²£¥Í­«½Æªº¦a¤è)
¦]¦¹·í²Ä¤@¦C¿é¤J¤µ¤Ñ§@¦nªº¼Æ¦r¦@8Äæ(~HÄæ).....(§Æ±æ¥i¥H¤£­­¨î¨ìHÄæ)
EX:31.32.33.34.35.36.37¿é¤J§¹´N¥i¥Hª¾¹D©³¤U­þ­Ó¸ê®Æ¸ò¤W­±³o¨Ç¼Æ¦r­«½Æ¤F

ÁÂÁÂ..
¥t¥~¤£¦n·N«ä..°ÝÃD§Ú¤w¸g²¾¨ìµ{¦¡°Ï¥hµo°Ý¤F...
ÁÂÁ±z..

TOP

¦^´_ 6# PD961A

¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  alexwin


    ¥D­n­nªí¹Fªº·N«ä¬O
·í²Ä¤@¦CA1.B.C~°²³]¨ìH1
¿é¤J¼Æ¦r(¹w³]¬°1~100)
¦ý¬O©³¤U ...
PD961A µoªí©ó 2010-7-14 18:14



    ¦pªG¥u¬O¤@ºØÃC¦âªº¥Îhsieh¤j¤W­±ªº´N¥i¥H¤F~
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 8# kimbal
¦^´_ 7# Hsieh


  
ÁÂÁ kimbal¥ý¶i¤Î Hsiehª©¥D

¥ý¶iªº¦^¤å¾A¥Î©ó" ¦pªG¥u¬O¤@ºØÃC¦âªº¥Îhsieh¤j¤W­±ªº´N¥i¥H¤F"
©Ò¥H·í§Ú­n¿ï¥Î3­Ó¥H¤WªºÃC¦â®É2003ªºª©¥»¬O.¤£¾A¥Î
¦]¦¹§Ú§â¦¹¥DÃD¤w²¾¨ìµ{¦¡°Ï¥hµo°Ý¤F
ÁÂÁÂ2¦ìªº¦^¤å

TOP

¦^´_ 1# PD961A


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹¥DÃD½m²ß°}¦C. ¨Ï¥Î¤j¤p·|µø»Ý­n°ÊºA¼W¥[ªº°}¦C»P ¦r¨å,
¾Ç²ß¨ì«Ü¦hª¾ÃÑ,ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, T, Y, Z, A, xR As Range, xU As Range
'¡ô«Å§iÅܼÆ:(Brr,T,Y,Z,A)¬O³q¥Î«¬ÅܼÆ,(xR,xU)¬OÀx¦s®æÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Set Z = CreateObject("System.Collections.ArrayList")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¨Ï¥Î¤j¤p·|µø»Ý­n°ÊºA¼W¥[ªº°}¦C
Set xR = [A2:H17]: Brr = xR
'¡ô¥OxR³oÀx¦s®æÅܼƬO [A2:H17]Àx¦s®æ,
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥HxRÅܼÆÀx¦s®æ­È±a¤J°}¦C¸Ì

Set xU = [A1]: T = Val(xU)
'¡ô¥OxU³oÀx¦s®æÅܼƬO [A1]Àx¦s®æ,
'¥OT³o³q¥Î«¬ÅܼƬO xUÅܼÆÀx¦s®æ­ÈÂনªº·s¼Æ¦r

For Each A In Brr
'¡ô³]³v¶µ°j°é!¥OA³o³q¥Î«¬ÅܼƬO Brr°}¦C¸Ìªº¤@­Ó°}¦C­È
   A = Format(A, "0000")
   '¡ô¥OAÅܼƬO ¥|¦ì¼Æªº¼Æ¦r½X¦r¦ê(­Y¤£¨¬¥|½X!©ó«e¤è¥H0¸É¨¬)
   If A <> vbNullString And Not Z.contains(A) Then Z.Add (A)
   '¡ô¦pªGAÅܼƤ£¬O ªø«×¬°¹sªº¦r¦ê,¦Ó¥BAÅܼƤ£¦bZ°}¦C¸Ì?
   '¦pªG±ø¥ó¦¨¥ß´N§â AÅܼƯǤJZ°}¦C¸Ì

Next
Z.Sort
'¡ô¥OZ°}¦C°µ¶¶±Æ§Ç
For Each A In Z: Y(Val(A)) = 0: Next
'¡ô³]³v¶µ°j°é!±NZ°}¦C¸Ìªº­È·íkey,item¬O0,¯Ç¤JY¦r¨å¸Ì
For Each A In xR
'¡ô³]³v¶µ°j°é!¥OAÅܼƬOxRÅܼÆÀx¦s®æ¤¤ªº¤@®æ
   If Val(A) = T Then Set xU = Union(xU, A)
   '¡ô¦pªGAÅܼƭÈÂন¼Æ­È«á = TÅܼÆ!´N±NAÅܼƯǤJxUÀx¦s®æ¶°¸Ì
   Y(Val(A)) = Y(Val(A)) + 1
   '¡ô¥OAÅܼƭÈÂà¼Æ­È·íkey,item¬O item¦Û¨­­È+1
Next
xR.Interior.ColorIndex = xlNone: xU.Interior.ColorIndex = 6
'¡ô¥OxRÅܼÆÀx¦s®æ©³¦â³]¬° µL©³¦â:¥OxUÅܼÆÀx¦s®æ©³¦â³]¬° ¶À¦â
[L:M].ClearContents: [L1:M1] = [{"¼Æ¦r", "¥X²{¦¸¼Æ"}]
'¡ô¥O[L:M]Àx¦s®æ²M°£¤º®e:¥O[L1:M1]³o¨â®æ¥H¡ô°}¦C¨â¦r¦ê±a¤J
[L2].Resize(Y.Count, 1) = Application.Transpose(Y.keys)
'¡ô¥O[L2]ÂX®i¦V¤UY¦r¨åkey¼Æ¶q¼ÆªºÀx¦s®æ,
'¥HY¦r¨åkeysÂà¸m«á±a¤JÀx¦s®æ

[M2].Resize(Y.Count, 1) = Application.Transpose(Y.items)
'¡ô¥O[M2]ÂX®i¦V¤UY¦r¨åkey¼Æ¶q¼ÆªºÀx¦s®æ,
'¥HY¦r¨åitemsÂà¸m«á±a¤JÀx¦s®æ

Set Y = Nothing: Set Z = Nothing: Set xR = Nothing
Set xU = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD