[µo°Ý] §ì¨ú¤W¥«ÂdÁÙ­ìªÑ»ù«á¡A¦p¦ó½Õ¾ã¤é´Á±Æ§Ç¤Î¬D¥X«e5¦~ªº¨C¦~°ª§CÂI

tmwcykixe

¥»½g«Y°Ñ¦Òhttp://ric1565.blogspot.tw/2015/02/blog-post_24.html .
½Ð±Ð¦U¦ì¥ý¶i¡A¸g§ì¨úªÑ»ù¸ê®Æ«á¡A¤é´ÁÀ³¦p¦ó±N³Ìªñªº¤é´Á¥Ñ¤W©¹¤U±Æ§Ç¡A¨Ã¦p¦ó¬D¥X«e5¦~ªº¨C¦~ªÑ»ù³Ì°ª¤Î³Ì§CÂI¡C

µ{¦¡½X¦p¤U¡G
Sub ComBoxInit()
  With ComboBox2
    .List = Array("¤é½u", "ÁÙ­ì¤é½u")
    .Text = "²M³æ"
  End With
End Sub
Private Sub ComboBox2_Change()

End Sub
Private Sub TextBox1_Change()

End Sub
Private Sub CommandButton1_Click()
    Dim web
    Dim URL As String, code As String
    Dim i As Long, j As Integer, d As Integer
    If TextBox1.Text = "" Then Exit Sub
    code = TextBox1.Text
    URL = "http://jsstock.wls.com.tw/Z/ZC/ZCW/CZKC1.djbcd?c=402&b=" & IIf(ComboBox2.Text = "ÁÙ­ì¤é½u", "A", _
             IIf(ComboBox2.Text = "¦W¥Ø¶g½u", "W", "M")) & "&a=" & code
    Set web = CreateObject("Microsoft.XMLHTTP")
    web.Open "get", URL, False
    web.send
   
    ReDim arr(1 To Int((Len(web.responseText) - Len(Replace(web.responseText, ",", ""))) / _
    (Len(web.responseText) - Len(Replace(web.responseText, " ", "")) + 1)) + 1, 1 To 6)
    For i = 1 To 6
        For j = 1 To Int((Len(web.responseText) - Len(Replace(web.responseText, ",", ""))) / _
    (Len(web.responseText) - Len(Replace(web.responseText, " ", "")) + 1)) + 1
    arr(j, i) = Application.Index(Split(Application.Index(Split(web.responseText, " "), 1, i), ","), 1, j)
        Next j
    Next i
   
    Do Until Application.WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0
      ActiveSheet.Cells.Delete
      ActiveSheet.Cells.Clear
    Loop
    Range(Cells(2 + 1, 1 + 1), Cells(2 + j - 1, 1 + i - 1)) = arr
    Cells(2, 2) = "¤é´Á"
    Cells(2, 3) = "¶}½L"
    Cells(2, 4) = "³Ì°ª"
    Cells(2, 5) = "³Ì§C"
    Cells(2, 6) = "¦¬½L"
    Cells(2, 7) = "¦¨¥æ¶q"
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("B3:" & Application.WorksheetFunction.Index(Split(ActiveSheet.UsedRange.Address, ":"), 1, 2))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    If Cells(65536, 2).End(xlUp).Row > 2 Then
        For d = 1 To Year(Cells(3, 2)) - Year(Cells(65536, 2).End(xlUp))
            Cells(2 + d, 9) = Year(Cells(3, 2)) - (d - 1)
            Cells(2, 12) = 3
            Cells(2 + d, 12).Formula = "=Match(" & (DateSerial(Year(Date) - d, 12, 31) + 0.1) * 1 & ",B:B,-1)"
            Cells(2 + d, 10) = Application.WorksheetFunction.Max(Range(Cells(Cells(1 + d, 12), 4), Cells(Cells(2 + d, 12), 4)))
            Cells(2 + d, 11) = Application.WorksheetFunction.Min(Range(Cells(Cells(1 + d, 12), 5), Cells(Cells(2 + d, 12), 5)))
        Next d
    End If
    Cells(3, 9) = "¦~«×"
    Cells(4, 9) = "³Ì°ª"
    Cells(5, 9) = "³Ì§C"
    Columns(12).Delete
    With ActiveSheet
        .Columns("C:F").NumberFormatLocal = "0.00"
        .Columns("J:K").NumberFormatLocal = "0.00"
        .Columns("B:B").NumberFormatLocal = "yyyy/mm/dd"
        .UsedRange.Columns.AutoFit
        .Cells(1, 1).Select
        .Rows("1:2").RowHeight = 30
End Sub

¤U¸ü (86.61 KB)

2015-4-21 17:01

[attach]20760[/attach]

joey0415

¦^´_ 2# tmwcykixe

¼gªº¯u¦n¡I¤S¾Ç¨ì¦nªF¦è

Selection.FormulaArray­ì¨Ó¬O¨ç¼Æ¤¤ªº{ }
µ{¦¡¤¤ªº402À³¬O¸ê®Æªø«×¡A­Y§ï¬°1000¤]¥i¥H§ì¨ì1000µ§®@¡I

¤£¹L¸ê®Æ¶q¤@¤j¡A³t«×´N·|ºC¤FÂI

´£¨Ñ¤@­Ó¤è¦VÀ³¸Ó·|§Ö¤@¨Ç

§â¸ê®Æªí·í¦¨¤@­Ó¸ê®Æ®w(excel)¤¤¦³³o­Ó¥\¯à¡A§Q¥Îsql»yªk¤¤ªºgroup by»P maxÀ³¸Ó·|§Ö«Ü¦h¡A´Nºâ¬O¤Q¸Uµ§À³¸Ó¤]·|¤ñ³o­Ó§Ö

¦³¾÷·|¥æ¬y¤@¤U

tmwcykixe

±ß¤W¬ðµo°_·Q¡A¥H¼g¨ç¼Æªº¤èªk¨Ó¸Õ¸Õ¬Ý¡A¨S·Q¨ì¥i¦æ¡A³Ì°ª»ù¥i¥H¥Î¥H¤U¤èªk¬D¥X.
­ì­Ë¼Æ²Ä9¦æ¥H«á§ï¦¨¦p¤U,
With ActiveSheet
        .Columns("C:F").NumberFormatLocal = "0.00"
        .Columns("B:B").NumberFormatLocal = "yyyy/m/d"
        .UsedRange.Columns.AutoFit
        .Cells(1, 1).Select
        .Rows("1:2").RowHeight = 30
   
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "2015"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "2014"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "2013"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "2012"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "2011"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "2010"
    Range("B2:G1500").Select
    Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
        False
    Range("J4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=J3,³Ì°ª,))"
    Range("K4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=K3,³Ì°ª,))"
    Range("L4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=L3,³Ì°ª,))"
    Range("M4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=M3,³Ì°ª,))"
    Range("N4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=N3,³Ì°ª,))"
    Range("O4").Select
    Selection.FormulaArray = "=MAX(IF(YEAR(¤é´Á)=O3,³Ì°ª,))"
  End With
End Sub