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

­×§ïµ{¦¡

­×§ïµ{¦¡

¦]Àɮ׶V¨Ó¶V¤j, ¥Øªº¸ê®Æ¤v¨ì¤F115000µ§, ¦Ó¨C­Ó¤ëªº¨Ó·½¸ê®Æ¦Ü¤Ö³£¦³9000¦hµ§, ¥Î¥H¤Uªºµ{¦¡¨Órun, ¨C¦¸³£­n¶]2~3¤p®É.
¤£ª¾°ª¤â­Ì¬O§_¥i¥H±Ð¾É­×§ïµ{¦¡,¥i¥HÅý¸ê®Æªº¿é¤J³t«×¥i¥H§Ö¤@¨Ç ?


Public put_column, row_clear, row_index, put_row, row_search As Long
Sub §ì¸ê®Æ()
put_column = Sheets("§@·~°Ï").Range("b1").Value
Sheets("Summary").Activate
ans0 = MsgBox("½T©w¬OLoad¦Ü¡i" & put_column & "¡jÄæ¶Ü¡H", vbYesNo, "½Ð½T»{")
If ans0 <> 6 Then

    Sheets("§@·~°Ï").Activate
    Range("b1").Select
    ans2 = MsgBox("½Ð­«·s¿é¤J¥¿½TªºÄæ¦ì¡I")
    End
End If
ans = MsgBox("­n²MªÅ ¡i" & put_column & "¡jÄ檺¸ê®Æ¶Ü¡H" & Chr(13) & Chr(13) & "¡i¬O¡j¡÷ ²MªÅ¡A¦A©ñ¤J¼Æ¾Ú" & Chr(13) & Chr(13) & "¡i§_¡j¡÷ ¤£²MªÅ¡A¼Æ¾ÚÄ~Äò²Ö¥[", vbYesNo, "½Ð½T»{")
If ans = 6 Then
    row_clear = 3
    While Cells(row_clear, 1).Value <> ""
        row_clear = row_clear + 1
    Wend
    Range(put_column & Format(3) & ":" & put_column & Format(row_clear)).ClearContents
    load_data
Else
    load_data
End If
End Sub
Sub load_data()
row_index = 2
'±qdataªº²Ä¤G¦C¶}©l´M§ä,¤@ª½§ä¦ÜTotal¬°¤î
While Left(Sheets("data").Cells(row_index, 9).Value, 5) <> "Total"
    Prod = Sheets("data").Cells(row_index, 3).Value
    If Prod = "Electro-Dip" Or Prod = "Electro" Then
        '±N¥|­ÓÄæ¦ì³sµ²°_¨Ó
        data_four_column = Sheets("data").Cells(row_index, 2).Value & Sheets("data").Cells(row_index, 3).Value & Sheets("data").Cells(row_index, 4).Value & Sheets("data").Cells(row_index, 9).Value
        '¦pªGSummaryªºa3Àx¦s®æ¬OªÅ¥Õ
        If Sheets("Summary").Cells(3, 1).Value = "" Then
        '´Nª½±µ±N§ä¨ìªº²Ä¤@µ§¸ê®Æ©ñ¶i²Ä¤T¦C
            Sheets("Summary").Cells(3, 1).Value = Sheets("data").Cells(row_index, 2).Value
            Sheets("Summary").Cells(3, 2).Value = Sheets("data").Cells(row_index, 3).Value
            Sheets("Summary").Cells(3, 3).Value = Sheets("data").Cells(row_index, 4).Value
            Sheets("Summary").Cells(3, 4).Value = Sheets("data").Cells(row_index, 9).Value
            Sheets("Summary").Range(put_column & Format(3)).Value = Sheets("data").Cells(row_index, 10).Value
        '§_«h
        Else
            '±q²Ä¤T¦C¶}©l§ä°_
            row_search = 3
            got_it = False
            '¤@ª½§ä¨ìªÅ¥Õ¬°¤î
            While Sheets("Summary").Cells(row_search, 1).Value <> ""
                '±NSummaryªº¥|­ÓÄæ¦ì³sµ²°_¨Ó
                four_column = Sheets("Summary").Cells(row_search, 1).Value & Sheets("Summary").Cells(row_search, 2).Value & Sheets("Summary").Cells(row_search, 3).Value & Sheets("Summary").Cells(row_search, 4).Value
                '¦pªG¦bdata§ä¨ìªº¸ê®Æ¸ò¦bSummary§ä¨ìªº¬Û¦P,´N§â¼Æ¶q¬Û¥[
                If data_four_column = four_column Then
                    got_it = True
                    Sheets("Summary").Range(put_column & Format(row_search)).Value = Sheets("Summary").Range(put_column & Format(row_search)).Value + Sheets("data").Cells(row_index, 10).Value
                End If
                    row_search = row_search + 1
            Wend
            '¦pªG¨S¦³§ä¨ì¬Û¦Pªº
            If got_it = False Then
                '´N¦b³Ì«á¤@¦C·s¼W¤@µ§
                Sheets("Summary").Cells(row_search, 1).Value = Sheets("data").Cells(row_index, 2).Value
                Sheets("Summary").Cells(row_search, 2).Value = Sheets("data").Cells(row_index, 3).Value
                Sheets("Summary").Cells(row_search, 3).Value = Sheets("data").Cells(row_index, 4).Value
                Sheets("Summary").Cells(row_search, 4).Value = Sheets("data").Cells(row_index, 9).Value
                Sheets("Summary").Range(put_column & Format(row_search)).Value = Sheets("data").Cells(row_index, 10).Value
                row_search = row_search + 1
            End If
        End If
    End If
    row_index = row_index + 1
Wend
ans = MsgBox("¤w§¹¦¨!")
End Sub

µ{¦¡­×§ï.zip (435.06 KB)

fangac

¦^´_ 1# fangsc
­×¥¿µ{¦¡©ó¶}ºÝ´Nµ¹­È
  1. Sub load_data()
  2. row_index = 2       '±qdataªº²Ä¤G¦C¶}©l´M§ä,¤@ª½§ä¦ÜTotal¬°¤î
  3. row_search = 3      '±q²Ä¤T¦C¶}©l§ä°_   
  4. While Left(Sheets("data").Cells(row_index, 9).Value, 5) <> "Total"
  5. '
  6. '
½Æ»s¥N½X
  1. '§_«h
  2.         Else
  3.             '±q²Ä¤T¦C¶}©l§ä°_
  4.             row_search = 3                                 '***  ¨C¤@¦¸³£±q²Ä¤T¦C§ä°_  ¬O³o¸Ì®ö¶O®É¶¡ ****
  5.             got_it = False
  6.             '¤@ª½§ä¨ìªÅ¥Õ¬°¤î
  7.             While Sheets("Summary").Cells(row_search, 1).Value <> ""
  8.                 '±NSummaryªº¥|­ÓÄæ¦ì³sµ²°_¨Ó
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE

·PÁª©¥Dªº«ü¾É, ¦ý³o¼Ë§ï¦n¹³¥u¬O±N¨Ó·½¸ê®Æ±q¥Øªº¤u§@ªíªº³Ì«á¤@¦C¶}©lload¸ê®Æ
¨Ã¨S¦³¥h¤ñ¹ï4­Ókey­È¦pªG¬Û¦Pªº¸Ü,´N©ñ¶i«ü©wªºÄæ¦ì, ¦pªG§ä¤£¨ì4­Ókey­È,´N¨ì¥Ø«e¸ê®Æªº³Ì«á¤@¦C·s¼W¤@µ§.
           ±NSummaryªº¥|­ÓÄæ¦ì³sµ²°_¨Ó
           ¦pªG¦bdata§ä¨ìªº¸ê®Æ¸ò¦bSummary§ä¨ìªº¬Û¦P,´N§â¼Æ¶q¬Û¥[
           ¦pªG¨S¦³§ä¨ì¬Û¦Pªº
            ´N¦b³Ì«á¤@¦C·s¼W¤@µ§

µ{¦¡­×§ï.zip (435.06 KB)

fangac

TOP

¦^´_ 3# fangsc
ªþÀɤ¤¨S Sheets("data") ³o¤u§@ªí

TOP

¦^´_ 4# GBKEE

¤£¦n·N«ä,½Ð¨£ªþÀÉ. ÁÂÁÂ.

µ{¦¡­×§ï.zip (585.35 KB)

fangac

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-10-10 19:22 ½s¿è

¦^´_ 5# fangsc
¸Õ¸Õ¬Ý
  1. Sub load_data()
  2.     Dim row_index As Integer,row_search As Integer, D As Object, K As Variant, T As Date
  3.     T = Time
  4.     Set d = CreateObject("SCRIPTING.DICTIONARY")         '¦r¨åª«¥ó
  5.     With Sheets("data")
  6.         row_index = 2
  7.         '±qdataªº²Ä¤G¦C¶}©l´M§ä,¤@ª½§ä¦ÜTotal¬°¤î
  8.         While Left(.Cells(row_index, 9).Value, 5) <> "Total"
  9.             'Prod = Sheets("data").Cells(row_index, 3).Value
  10.             If .Cells(row_index, 3) = "Electro-Dip" Or .Cells(row_index, 3) = "Electro" Then
  11.                 '±N¥|­ÓÄæ¦ì³sµ²°_¨Ó
  12.                 data_four_column = .Cells(row_index, 2) & ",," & .Cells(row_index, 3) & ",," & .Cells(row_index, 4) & ",," & .Cells(row_index, 9)
  13.                 If d.EXISTS(data_four_column) Then   '¦r¨åª«¥ó ÃöÁä¦r (key) ¦s¦b
  14.                     d(data_four_column) = d(data_four_column) + .Cells(row_index, 10).Value
  15.                 Else
  16.                     d(data_four_column) = .Cells(row_index, 10).Value
  17.                     ''¦r¨åª«¥ó ÃöÁä¦r(key)¥[¤J ¨Ã«ü©w ¶µ¥Ø (item)
  18.                 End If
  19.             End If
  20.             row_index = row_index + 1
  21.         Wend
  22.     End With
  23.      With Sheets("Summary")
  24.         row_search = 3
  25.         If .Cells(3, 1).Value = "" Then  '¦pªGSummaryªºa3Àx¦s®æ¬OªÅ¥Õ
  26.            For Each K In d.KEYS          '¨ú¥X¦r¨åª«¥ó ÃöÁä¦r
  27.             .Cells(row_search, 1).Resize(, 4) = Split(K, ",,")
  28.             .Range(put_column & row_search) = d(K)  '¨ú¥X¦r¨åª«¥óªºitem(¶µ¥Ø)
  29.             row_search = row_search + 1
  30.            Next
  31.         Else
  32.             While .Cells(row_search, 1) <> ""
  33.                 K = Join(Application.Transpose(Application.Transpose(.Cells(row_search, 1).Resize(, 4).Value)), ",,")
  34.                 If d.EXISTS(K) Then        '¦r¨åª«¥ó ÃöÁä¦r (key) ¦s¦b
  35.                     .Range(put_column & row_search) = .Range(put_column & row_search) + d(K)
  36.                     d.Remove K             '¦r¨åª«¥ó ²¾°£ ÃöÁä¦r (key)
  37.                 End If
  38.                 row_search = row_search + 1
  39.             Wend
  40.             If d.Count > 1 Then     '¨S¤ñ¹ï¨ìªº¸ê®Æ
  41.                For Each K In d.KEYS
  42.                     .Cells(row_search, 1).Resize(, 4) = Split(K, ",,")
  43.                     .Range(put_column & row_search) = d(K)
  44.                     row_search = row_search + 1
  45.                 Next
  46.             End If
  47.         End If
  48.     End With
  49.     MsgBox (Format(Time - T, "nn¤ÀSS¬í") & " ¤w§¹¦¨!")
  50. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# GBKEE

·PÁª©¥D,­ì¥»­nrun 2~3¤p®É,²{¦b¥u­n3¬í.¹ê¦b¼F®`. ¯uªº¦n¤ñ¬O兎¤l¸ò¯QÀtªº³t«×¬Û¤ñ.¥O¤H±æ¹Ð²ö¤Î.
¤£¹L­n¬ÝÀ´§Aªºµ{¦¡, §ÚÁÙ­n»{¯uªº¬ãŪ.
¦]¬°¥Ø¦a¤u§@ªíªº¦C¸ê®Æ¤Ó¤j,µo¥Í"·¸¦ì"ªº¿ù»~°T®§, ©Ò¥H§Ú±N row_search As Integer §ï¬° Long.
·PÁÂ!!
fangac

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-10-11 09:31 ½s¿è

¦^´_ 5# fangsc
  1. Sub ex()
  2. Dim d As Object, SumColumn$, n$, i%, A As Range, Mystr$, ky As Variant, Ay(), ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. 10
  5. SumColumn = InputBox("¿é¤JÄæ¦ì", , "AN")
  6. If SumColumn = "" Then GoTo 10
  7. [B1] = SumColumn
  8. With Sheets("Summary")
  9.   For Each A In .Range(.[A3], .[A2].End(xlDown))
  10.      Mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 4))), ",")
  11.      d(Mystr) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, .Cells(A.Row, SumColumn).Value)
  12.   Next
  13.   With Sheets("data")
  14.     For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
  15.     If InStr(A.Offset(, 2), "Electro") > 0 Then
  16.        Mystr = A.Offset(, 1) & "," & A.Offset(, 2) & "," & A.Offset(, 3) & "," & A.Offset(, 8)
  17.        If IsEmpty(d(Mystr)) Then
  18.        d(Mystr) = Array(A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, A.Offset(, 8).Value, A.Offset(, 9).Value)
  19.        Else
  20.        ar = d(Mystr)
  21.        ar(UBound(ar)) = ar(UBound(ar)) + A.Offset(, 9)
  22.        d(Mystr) = ar
  23.        End If
  24.     End If
  25.     Next
  26.   End With
  27.   Ay = Application.Transpose(Application.Transpose(d.items))
  28.   .[A3].Resize(d.Count, 4) = Ay
  29.   .Cells(3, SumColumn).Resize(d.Count, 1) = Application.Index(Ay, , 5)
  30. End With
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh

·PÁª©¥D,¯uªº¶W¯Å¼F®`ªº§Ö,·Q»¡«ö¤U°õ¦æÁä,¥i¥H®³°_ªM¤l³Ü¤f¤ô,­þª¾ªM¤lÁÙ¨S¨ì¼LÃä,µ{¦¡´N¶]§¹¤F.
µ{¦¡¬Ý°_¨ÓÁÙ¬OÃøÀ´, ­n»{¯uªºª¦¤å¤F.  ÁÂÁ®@!!
fangac

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD