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

[µo°Ý] ¦p¦ó§Q¥ÎVBA«öÁä¡A¨Ó§ä¥X¹H¤Ï³W«hªº¸¹½X¡C

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-19 09:07 ½s¿è

¸É¥R»¡©ú:
1. ¤£¥[¤J§Ç¸¹¥i¤£¥i¥H?
´¡¤J§Ç¸¹, ¥H«K«ì´_­ìª¬,
°£¿ùªº¥Øªº¤£¬O¥´¶Ã­ìªí®æ,
¦]­ìVBA§Q¥ÎExcel ªº±Æ§Ç¥\¯à,
    [A1].Resize(LstR2, 10).Sort _
            Key1:=[C1], Order1:=xlAscending, _
            Key2:=[G1], Order1:=xlAscending, _
            Header:=xlYes
·|¥´¶Ã­ìªí®æ, ¬G±Æ§Ç«e¥ý¥[¤J§Ç¸¹.
¦pÄæA¦³­«­n¸ê®Æ¥i²¾¨ì¥L³B, ¦pÄæK
­Y²¾¨ìÄæK,[K1]¥iÁä¤J"§Ç¸¹", ­ìVBA¥i§ï¦¨
    [A1].Resize(LstR2, 11).Sort _
            Key1:=[C1], Order1:=xlAscending, _
            Key2:=[G1], Order1:=xlAscending, _
            Header:=xlYes
¤Î±N
'´¡¤J§Ç¸¹, ¥H«K«ì´_­ìª¬
Sub ´¡¤J§Ç¸¹(LstR As Integer)
    Dim I As Integer
    For I = 2 To LstR
        Cells(I, 1) = I
    Next
End Sub
§ï¦¨
    For I = 2 To LstR
        Cells(I, 11) = I
    Next
¤Î±N
    '«ì´_­ìª¬, ¤è«K¬d®Ö
    [A1].Resize(LstR2, 9).Sort _
            Key1:=[A1], Order1:=xlAscending, _
            Header:=xlYes
§ï¦¨
    [A1].Resize(LstR2, 11).Sort _
            Key1:=[k1], Order1:=xlAscending, _
            Header:=xlYes
³Ì«á¥[¤J
    [K1:K65536] = ""
¤©¥H²M°£.

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-19 09:34 ½s¿è

¦^´_ 9# RCRG
"¤£¹LI24¦ü¥G¤£¸Ó¥X²{"¨ì´Á¤é²§±`2"¡A¬d¬Ý¤@¤U­ì¦]¦ü¥Gyen¤j§â»s³y¤éªº"®É¶¡"¤]¤ñ¹ï¶i¥h¤F¡I»s³y¤é¥u»Ý¤ñ¹ï"¤é´Á"¡A«á­±®É¶¡¥i¥H©¿²¤"
¤Ï¥¿ "»s³y¤é" ¬Û¦P, "¨ì´Á¤é" ¤£¦P, ´N¤@©w·|¹H¤Ï "³W«h1",
¦¹®É­n¤£­n "§â»s³y¤éªº"®É¶¡"¤]¤ñ¹ï¶i¥h" ´NµLÃöºò­n¤F,
³oºØ±¡§Îªº¹H¤Ï "³W«h2" ¥² ¹H¤Ï "³W«h1",
¦]§Q¥ÎExcel¥»¨­ªº±Æ§Ç¥\¯à,
    [A1].Resize(LstR2, 11).Sort _
            Key1:=[C1], Order1:=xlAscending, _
            Key2:=[G1], Order1:=xlAscending, _
            Header:=xlYes
«ÜÃøÁ׶}®É¶¡ªº°ÝÃD(°£«D¥t¥[¸É§UÄæ, ¥ý§âÄæG¥Î¤½¦¡ =int(G2) ¥þ³¡¥h±¼®É¶¡, §ï¦¨¤é´Á).

TOP

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-12-19 11:21 ½s¿è

¦^´_ 3# ­ã´£³¡ªL

¦^´_ 12# yen956

ÁÂÁ¨â¦ì¼ö¤ß¤S±M·~ªº¸Ñµª¡A¤µ¤Ñ¦Ü¤½¥q¹ê»Ú´ú¸Õ¤ß±o¦p¤U¡G

20151219_¦p¦ó§Q¥ÎVBA«öÁä¡A¨Ó§ä¥X¹H¤Ï³W«h¸¹½X.rar (14.99 KB)
    ¦p¤W­±ÀɮסA¦AÁ|­Ó½d¨Ò8¡A­ã¤jªº§e²{¤è¦¡¤£ª¾¯à§_¥u¦b60©M61¦CÅã¥Ü²§±`¡A³o¼Ë§Ú¤ñ¸û¤£·|²´ªá¼º¶ÃXD¡C

¨â¦ì¤j¤j¤£ª¾¯à§_À°§Ú¥[´X¶µ­×§ï
1. Åã¥Ü²§±`ªºÄæ¦ì§ï¨ìAÄæ©MBÄæ¡A¦p¤U¹Ï1
2. ¨S¸ê®Æ ©Î ¶K¤W¸ê®Æ¤£²Å¦X«¬¦¡ ªÌ¡A¼u¥Xµøµ¡»¡©ú(¦]¬°§Ú¶Ã¶ñ¸ê®Æ·|¥X²{¿ù»~µøµ¡¡A¦p¤U¹Ï2)

¹Ï1


¹Ï2


¨ä¥L¤j­PÀ³¸Ó´N¨S¬Æ»ò°ÝÃD¤F¡A¦A¦¸ÁÂÁ¨â¦ì¤j¤j¡I

TOP

«Øij:
1. ¥t·s¼W¤@¤u§@ªí, ¤º¥u¦³¸¹½X¡B«~¦W(¤è«K¿é¤J¦³®Ä´Á­­)¡B¦³®Ä´Á­­ ¤TÄæ
2. ¸¹½X¬O°ß¤@ªº, ¥B¶·¥Ñ¤p¨ì¤j±Æ¦C(EXCEL 2003¥²¶·, ¥H«áª©¥»¤£²M·¡), ¦³®Ä´Á­­¥H¤Ñ¬°³æ¦ì.
§Q¥Î¶i¶¥¿z¿ï, ¤£¿ï­«ÂÐ, §Y¥i±o¸¹½X¬O°ß¤@ªº, ¾Þ§@¦p¤U¹Ï:
test.gif
3. ¶ñ¤J¦³®Ä´Á­­(³æ¦ì:¤Ñ), §Y¤T­Ó¤ëÀ³¿é¤J90,
4. ¦^¨ì¥D­¶(½d¨Ò»¡©ú­¶),¦b E2 ¿é¤J¤½¦¡, ¦V¤U©Ô
=IF(C2="","",INT(G2)+LOOKUP(C2,Sheet3!A:A,Sheet3!C:C))
(Sheet3 ¬O¤u§@ªí¦WºÙ)
¤j¥\§i¦¨, ¦p¦¹«Oµý "¨ì´Á¤é" 絶¹ï¤£·|¥X¿ù!!

TOP

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-12-20 08:56 ½s¿è

¦^´_ 14# yen956


    ½Ð±Ð¤@¤Uyen956¤j¡A§Úµo²{¤F¤@­ÓÄY­«ªº°ÝÃD¡A

¤£¸Ó¥X²{²§±`ªº³ºµM¶]¥X¤@°ï²§±`¡A¦]¬°³ºµM·|¥h¤ñ¹ï¤£¦P¸¹½X(¥¿±`À³¸Ó¬O¦P¸¹½X¤U¥h¤ñ¸û)
¡A­Ë¬O¥Î­ã¤jª©¥»ªº«o¨S¦³³o­Ó°ÝÃD¡A¦³³Òyen956¤jÀ°§Ú¬Ý¤@¤U¦n¶Ü? ÁÂÁ¡I

20151220_¦p¦ó§Q¥ÎVBA«öÁä¡A¨Ó§ä¥X¹H¤Ï³W«h¸¹½X.rar (17.49 KB)

°ÝÃDÀɮצp¤W¡A¥i¥Hªº¸Ü­×§ïVBA«á¡A¦AÀ°§Ú¥þ³¡­«PO¤@¦¸Åý§Ú§ì¨ú¦n¶Ü? ¦A¦¸ÁÂÁ¤F¡I

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-12-20 11:03 ½s¿è

Sub ÀË´ú1()
Dim xD, xD1, R&, T$, TM1, TM2, i&, TT$
R = [C65536].End(xlUp).Row
[A:B].ClearContents
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
For i = 2 To R
¡@¡@T = Range("C" & i): TM1 = Range("E" & i): TM2 = Range("G" & i)
¡@¡@If T = "" Or IsDate(TM1) = 0 Or IsDate(TM2) = 0 Then GoTo 101
¡@
¡@¡@TM1 = Int(TM1): TM2 = Int(TM2)
¡@¡@If TM1 < xD(T) Then xD1(T & TM1) = "2.¨ì´Á¤é¥¼±Æ§Ç"
¡@¡@xD(T) = TM1
¡@
¡@¡@If xD(T & TM2) = 0 Then xD(T & TM2) = TM1
¡@¡@If xD(T & TM2) <> TM1 Then xD1(T & TM2) = "1.¨ì´Á¤é²§±`": GoTo 101
101: Next
¡@
For i = 2 To R
¡@¡@TT = ""
¡@¡@T = Range("C" & i): TM1 = Range("E" & i): TM2 = Range("G" & i)
¡@¡@If T = "" And TM1 = "" And TM2 = "" Then GoTo 102
¡@
¡@¡@If T = "" Then TT = "¡þ1.¸¹½X"
¡@¡@If Not IsDate(TM1) Then TT = TT & "" & "¡þ2.¨ì´Á¤é"
¡@¡@If Not IsDate(TM2) Then TT = TT & "¡þ3.»s³y¤é"
¡@¡@If TT <> "" Then Range("B" & i) = "*½ÐÀˬd_" & Mid(TT, 2) & "": GoTo 102
¡@
¡@¡@TM1 = Int(TM1): TM2 = Int(TM2)
¡@¡@If xD1(T & TM1) <> "" Then Range("A" & i) = xD1(T & TM1)
¡@¡@If xD1(T & TM2) <> "" Then Range("A" & i) = xD1(T & TM2)
102: Next
End Sub

TOP

©êºp!!Á{¬É­È¨S§ì·Ç, ¤w­×§ï, ½Ð¦A´ú¸Õ¬Ý¬Ý!!
  1. '´¡¤J§Ç¸¹, ¥H«K«ì´_­ìª¬
  2. Sub ´¡¤J§Ç¸¹(LstR As Integer)
  3.     Dim I As Integer
  4.     For I = 2 To LstR
  5.         Cells(I, 1) = I
  6.     Next
  7. End Sub
  8. Sub test()
  9.     Dim LstR As Integer, LstR2 As Integer, sR As Integer, I As Integer, cnt As Integer
  10.     Dim minDate As Date
  11.     [J2:K65536] = ""
  12.     '²M°£©³¦â
  13.     [J:K].Interior.ColorIndex = xlNone
  14.     LstR2 = Cells(Rows.Count, 3).End(xlUp).Row
  15.     ´¡¤J§Ç¸¹ LstR:=LstR2      '´¡¤J§Ç¸¹, ¥H«K«ì´_­ìª¬
  16.     [A1].Resize(LstR2, 10).Sort _
  17.             Key1:=[C1], Order1:=xlAscending, _
  18.             Key2:=[G1], Order1:=xlAscending, _
  19.             Header:=xlYes
  20.     LstR = Cells(Rows.Count, 3).End(xlUp).Row
  21.     sR = 1
  22.     Do
  23.         sR = sR + 1
  24.         If sR = 2 Then GoTo Next1:
  25.         cnt = sR
  26.         Do
  27.             '³W«h1.¸¹½X¬Û¦P¥B»s³y¤é¬Û¦P, ¦ý¨ì´Á¤é¤£¦P
  28.             If Cells(sR, 3) = Cells(sR - 1, 3) And Int(Cells(sR, 7)) = Int(Cells(sR - 1, 7)) And Cells(sR, 5) <> Cells(sR - 1, 5) Then
  29.                 Cells(sR - 1, 9) = "¨ì´Á¤é²§±`1"
  30.                 Cells(sR - 1, 9).Interior.ColorIndex = 8
  31.                 Cells(sR, 9) = "¨ì´Á¤é²§±`1"
  32.                 Cells(sR, 9).Interior.ColorIndex = 8
  33.             End If
  34.             sR = sR + 1
  35.         Loop Until Cells(sR, 3) <> Cells(sR - 1, 3) Or sR > LstR    'ª½¨ì ¸¹½X¤£¦P ©Î ¸ê®Æµ²§À
  36.         '³W«h2. ¨ì´Á¤é¤]¥²¶·±Æ§Ç
  37.         If sR - cnt <= 1 Then GoTo Next1:
  38.         For I = cnt To sR - 2
  39.             minDate = Application.Min(Cells(I + 1, 5).Resize(sR - I - 1, 1))
  40.             If Cells(I, 5) > minDate Then
  41.                 Cells(I, 10) = "¨ì´Á¤é²§±`2"
  42.                 Cells(I, 10).Interior.ColorIndex = 38
  43.             End If
  44.         Next
  45. Next1:
  46.     Loop Until sR >= LstR  'ª½¨ì¸ê®Æµ²§À
  47.     '«ì´_­ìª¬, ¤è«K¬d®Ö
  48.     [A1].Resize(LstR2, 10).Sort _
  49.             Key1:=[A1], Order1:=xlAscending, _
  50.             Header:=xlYes
  51. End Sub
½Æ»s¥N½X
p.s.
±q »s³y¤é ºë·Ç¨ì"¤ÀÄÁ"¬Ý¨Ó, »s³y¤é¬O¥Ñ¹q¸£¦Û°Ê¿é¥Xªº,
¦ý ¨ì´Á¤é ¬°¦ó¤£¥Î 14# F ªº«Øij, §ï¥Î¤½¦¡¦Û°Ê¿é¤J,
¥H´î¤Ö¤H¬° Key In ¤Wªº¿ù»~?
¨ä¹ê, ¦³¹H¤Ï³W«h1¤Î³W«h2 ªº¿ù»~, «ç¥i¯à¨S¦³ ¨ì´Á¤é<»s³y¤é ªº¤H¬° Key In ¤Wªº¿ù»~!?
test.gif

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-12-20 11:46 ½s¿è

¦^´_ 16# ­ã´£³¡ªL
¤j¤j©êºp!!¨Ó¤£¤Î¥Î¤j¤j·sª©´ú¸Õ´N¤W¶Ç!!

TOP

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-12-22 12:27 ½s¿è

¦^´_ 18# yen956


    ·PÁÂyen956¤j­×§ï¸Ñµª¡A¤£¹L­×§ï§¹¤Ï¦ÓÅܦ¨¡A¦pªG§â¤é´Á§ï¬°¥¿½T¡A¦n¹³¤]¤£·|§â­ì¥»ªº "²§±`1&2" ¦r²´²M°£¤F©O¡I

Ãö©ó"¥H´î¤Ö¤H¬° Key In ¤Wªº¿ù»~?"¡A¨ä¹ê¾ã½g¸ê®Æ³£·|¬O¥Î½Æ»s¹L¨Óªº¡A­ì«h¤W¤£·|¦³»~KEY¡A¦³»~·|ªº¦a¤èÀ³¸Ó¬O§Ú¦Û¤vÀÉ®×Á|¨Ò¤£¦n¡A¦ýÁÙ¬O¥ýÁÂÁÂY¤j´£¿ô­ò¡I

TOP

¦^´_ 19# RCRG
¨S¿ù, ¦]¿ù»~°T®§§ï©ñ¨ì³B, ²Ä11¦C§Ñ¤F§ï, ±N
    [J2:K65536] = ""
§ï¦¨
    [H2:J65536] = ""
§Y¥i.

p.s.
"¨ä¹ê¾ã½g¸ê®Æ³£·|¬O¥Î½Æ»s¹L¨Óªº¡A­ì«h¤W¤£·|¦³»~KEY"
¨º´N¬O­ì½Z´N¿ù?!§Oªº³æ¦ì°e¹L¨Óªº?¨º¤S¦ó¥²°£¿ù?

TOP

        ÀR«ä¦Û¦b : §Ñ¥\¤£§Ñ¹L¡A§Ñ«è¤£§Ñ®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD