Defined Function vs Formula Array, tiện lợi vs tốc độ

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi adam_tran, 15 Tháng tám 2005.

3,239 lượt xem

  1. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    Topic này tớ lập ra để cùng nhau trao đổi về những cách sử dụng phối hợp các công thức của Excel và VBA sao cho CSDL đơn giản và tối ưu tốc độ.
    Mấy ngày nay tôi tập coding các hàm để thay thế các Formula Array đang dùng như sau:
    Các Formula Array và Defined Function:
    {=SUM(IF(MaVL=$H2;IF(maKH=$G$1;IF(Ngay>=$G$2;IF(Ngay<=$G$3;SoL;0);0);0);0))}

    Function Tonghop(xMaVL As Range, xMaKH As Range, xNgay As Range, xSo As Range, maVL As String, maKH As String, Ngay1 As Date, Ngay2 As Date)
    Dim i As Long
    Tonghop = 0
    For i = 1 To xMaVL.Rows.Count
    If xMaVL(i) = "" Then Exit Function
    If (xMaVL(i) = maVL) And (xMaKH(i) = maKH) And (xNgay(i) >= Ngay1) And (xNgay(i) <= Ngay2) _
    Then Tonghop = Tonghop + xSo(i)
    Next i
    End Function

    Nếu các Name MaSL, MaKH mở đến 3000 dòng ... trong khi CSDL của tớ có dưới 300 dòng (records), hàm Tonghop chạy nhanh hơn Formula Array. (Nhờ thủ tục Exit Function nên vòng lặp kết thúc khi đến dòng cuối cùng không có dữ liệu). Nhưng nếu dữ liệu lên đến 2000 records thì hàm Tonghop chạy chậm như rùa (máy P4 1.5Gb, RAM 128MB)
    Như vậy, theo tớ đánh giá:
    - Formula Array chạy nhanh hơn dùng Defined Function trên vòng lặp for đến 10 lần (với cấu hình máy như trên, cùng số dòng).
    - Nếu các Name apply sẳn với nhiều dòng trống, Formula Arry sẽ chạy chậm, do đó nên có 1 macro tự động apply các Name trên một vùng có dữ liệu, tránh mở xuống dưới quá nhiều dòng trống không cần thiết.
    - Có giải thuật nào tăng tốc cho vòng lặp for trên không? So sánh For với Select? Lệnh Exit For là lệnh kết thúc cả vòng lặp, nhưng nếu muốn bỏ qua vòng hiện tại, và tiếp tục những lần tiếp theo thì dùng lệnh gì?
     
    #1
  2. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    50
    Nơi ở:
    Hà Nội
    Trong hàm trên bạn dùng lệnh "Exit Function" tức là sẽ thoát khỏi hàm "Tonghop" khi có MãVL ở dòng thứ I="" . Như vậy nếu ở dòng I+n mà MãVL<>"" thì sao? Vì thế khai báo "Exit Function" là sai.
    I chữa lại nhưa sau:

    Function Tonghop(xMaVL As Range, xMaKH As Range, xNgay As Range, xSo As Range, maVL As String, maKH As String, Ngay1 As Date, Ngay2 As Date, Optional Byval DaSapxep as Boolean=False)
    Dim i As Long
    Dim bTonghop
    bTonghop=0
    For i = 1 To xMaVL.Rows.Count
    If xMaVL(i) = "" Then
    If DaSapxep and bTonghop<> 0 then
    Exit For
    End If
    Else
    If (xMaVL(i) = maVL) And (xMaKH(i) = maKH) And (xNgay(i) >= Ngay1) And (xNgay(i) <= Ngay2) _
    Then bTonghop = bTonghop + xSo(i)
    End If
    Next i

    Tonghop=bTonghop
    End Function


    Viết một hàm tính như "Tonghop" trong môi trường VBA chỉ đạt được hiệu quả như vậy mà thôi. Người ta thường chỉ viết một hàm khi nó cần cung cấp cho một chỉ tiêu đơn lẻ (VD một mã khách hàng) và nhằm giảm bới khai báo các tham số cho ngắn gọn dễ dùng, còn nếu cần lập chỉ tiêu cho một bảng dữ liệu (n khách hàng) thì việc dùng hàm "Tonghop" để Copy cho nhiều khách hàng là một sai lầm, nên dùng công thức mảng của EXCEL ( I khẳng định dùng công thức mảng tối ưu hơn hàm "Tonghop").
    Một giải pháp tối ưu tốt hơn việc viết hàm hay dùng công thức EXCEL là ta viết riêng một thủ tục (Sub):
    Giả sử CSDL có 1000 dòng, để tính số liệu 1 KH (khách hàng) bạn phải dùng 1 hàm "Tonghop" EXCEL thực hiện khoảng >=1000 phép tính, bảng số liệu cần tính có Tồn đầu/Nợ|Có; Phát sinh/Nợ|Có Như vậy 1 KH bạn phải dùng 4 hàm "Tonghop" à EXCEL phải thực hiện số phép tính khoảng >=4*1000 tức 4000 phép tính (Nếu dùng công thức mảng thì cũng như vậy thôi). Nếu bạn có 100 KH tức bạn sẽ Copy từ KH thứ nhất->KH thứ 100 vậy là EXCEL bạn sẽ phải dùng 4*100=400 hàm "Tonghop", EXCEL phải thực hiện 400*1000=400 000 phép tính. Với yêu cầu trên bạn muốn máy chạy trong bao lâu? Bây giờ giải pháp chỉ là mua máy tính với cấu hình cao hơn mà thôi!
    Giải pháp ở đây là: Chỉ dùng duy nhất một vòng lặp For i=1 to 1000, trong mỗi lần lặp phải tính một lúc cho 4 cột vào đúng mã KH (phải loại trừ KH=""). Với cách phân tích trên thì dù bạn có 1000000000000xxx KH thì bạn cũng chỉ cần một vòng lặp duy nhất (tức khỉ trong khoảng 1000 phép tính). Nếu bạn dùng 1000000000000xxx công thức mảng hay hàm "Tonghop" thì chắc máy tính của bạn đã bốc khói mất rồi.
    Trên chỉ là thuật toán I chỉ ra còn việc coding nó bạn tự làm sẽ tốt hơn khi còn đang học VBA hay lập trình CSDL.
    Select ...End Select <-> If ...End If
    For... Next gần như Do .... While
    Bạn nhấn F1 trong môi trường VBA chắc sẽ rõ hơn.
     
    #2
  3. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    Đúng rồi, phải coding và thực tế nhiều mới có kinh nghiệm. Để tăng tốc cho Excel, tớ cần chú ý đến 2 vấn đề sau:
    - Các thuật toán trong các hàm chuẩn của Excel là DLL viết bằng ngôn ngữ nhị phân nào đó... có tốc độ nhanh hơn khoảng 10 lần so với hàm tương tự viết bằng code VBA.
    - Tính các phép tính trong thuật toán của mình khi coding để suy ra Code của mình nhanh hay chậm.
    Cám ơn bạn nhiều!
     
    #3
  4. Tran Chau

    Tran Chau Thành viên sơ cấp

    Bài viết:
    149
    Đã được thích:
    0
    Nơi ở:
    TP Ho Chi Minh
    Có tác giả nước ngoài lại khuyên không nên vội vả nhảy vào vòng lặp, i đã đọc nhưng chỉ hiểu được 1 chút chút, post các bạn thâm cứu
    Excel VBA Using Loops in Excel
    Don't get caught in the Loop:
    Đừng dùng vội trong Loop
    To put it bluntly I very often avoid Loops, they are far too sloooow in many cases. A common mistake we all make when first
    Don't get caught in the Loop:

    To put it bluntly I very often avoid Loops, they are far too sloooow in many cases. A common mistake we all make when first learning VBA is to use Loops when we really shouldn't..Take the simple example below for instance. It Loops through a range and places the word "Blank" in each blank cell within a used range, i.e it assumes the last occupied cell is D500


    Sub WrongWay()
    Dim Bcell As Range
    For Each Bcell In Range("A1:D500")
    If IsEmpty(Bcell) Then Bcell = "Blank"
    Next Bcell
    End Sub

    Now compare the above code to this one:
    Sub RightWay()
    If WorksheetFunction.CountA(Range("A1:D500")) = 0 Then
    MsgBox "All cells are empty", vbOKOnly, "OzGrid.com"
    Exit Sub
    End If
    On Error Resume Next
    Range("A1:D500").SpecialCells(xlCellTypeBlanks) = "Blank"
    On Error GoTo 0
    End Sub

    Not only is it much easier to type, but it will run much much quicker.

    Next time you have a VBA task, don't rush in with a Loop. Step back and give some serious thought to Excels built in functions. Some of my favourites to use are: SpecialCells, AutoFilter, Find, AdvancedFilter and Subtotals to name but a few.Once you start to make use of these you will always think twice before using a Loop of any kind.

    Instead of a Loop, try inserting a Column and placing a formula in the required range that makes the check on the cells. Use a number for a Yes and text for a No, then use SpecialCells to do the rest. I can promise you there is nearly always a built in feature that will execute at least 100 times quicker than a Loop. You just need to step outside the box!

    Here is another comparison to stress my point!:
    Place the text "Find Me" into cell IV65536 and run this code:


    Sub NoLoop()
    If WorksheetFunction.CountIf(Cells, "Find Me") = 0 Then
    MsgBox "You didn't type 'Find Me'", vbOKOnly, "OzGrid.com"
    Exit Sub
    End If

    Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False).Activate

    End Sub

    Now if you have at least 5 minutes to spare, try this code that uses a Loop:

    Sub WithLoop()
    Dim rCell As Range

    For Each rCell In Cells
    If rCell.Value = "Find Me" Then
    rCell.Activate
    Exit For
    End If
    Next rCell
    End Sub

    To stop the Loop, push Ctrl+Break or Esc. Now that has to at least make you try alternatives for Loops!

    Now sometimes a Loop might be the only way or the best way (not too often though). If this is the case we should restrict our range to only the cells we need. The example below will change the font color of all negative value cells to yellow and the backgroud to red for an entire Worksheet. Truth be known I would use conditional formatting! Before it permforms the loop though it restricts the range to only numeric cells.

    Sub FastestLoop()
    Dim rCcells As Range, rFcells As Range
    Dim rAcells As Range, rLoopCells As Range

    'Set variable to all used cells
    Set rAcells = ActiveSheet.UsedRange
    rAcells.Select
    On Error Resume Next 'In case of no formula or constants.
    'Set variable to all numeric constants
    Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers)
    'Set variable to all numeric formulas
    Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers)

    'Determine which type of numeric data (formulas, constants or none)
    If rCcells Is Nothing And rFcells Is Nothing Then
    MsgBox "You Worksheet contains no numbers"
    End
    ElseIf rCcells Is Nothing Then
    Set rAcells = rFcells 'formulas
    ElseIf rFcells Is Nothing Then
    Set rAcells = rCcells 'constants
    Else
    Set rAcells = Application.Union(rFcells, rCcells) 'Both
    End If
    On Error GoTo 0

    'Loop through needed cells only see if negative
    For Each rLoopCells In rAcells
    If rLoopCells.Value < 0 Then
    With rLoopCells
    .Interior.ColorIndex = 6
    .Font.ColorIndex = 3
    End With
    End If
    Next rLoopCells
    End Sub


    --------------------------------------------------------------------------------
    Here is another way to speed up a loop that makes use of Excel's COUNTIF function. The code could be modified to suit almost any situation very easily. This particular Procedure Bolds all instances of the "Cat" in Column "A" of the active sheet.
    Sub BoldCat()
    Dim iLoop As Integer
    Dim rNa As Range
    Dim i As Integer

    iLoop = WorksheetFunction.CountIf(Columns(1), "Cat")
    Set rNa = Range("A1")

    For i = 1 To iLoop
    Set rNa = Columns(1).Find(What:="Cat", After:=rNa, _
    LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True)
    rNa.Font.Bold=True

    Next i

    End Sub

    What Loops Are Good For

    The examples below here show what loops are good for, in this case it is looping through a text string and passing back the positions of / within the string to an array. The array (in this case) would result in {6,11,19,22}. Each number representing the position of each /
    Sub GetIt()
    Dim i As Integer
    Dim strText As String
    Dim iPositions() As Integer
    Dim iStart As Integer
    Dim iLoop As Integer

    strText = "Ihave/four/OfThese/In/Me"

    'Find out how many "/" are within the string by subtracting _
    Len("Ihave/four/OfThese/In/Me") from Len("IhavefourOfTheseInMe") _
    This will result in four. We then take 1 because the first element _
    in an Array is always zero, unless told otherwise.
    iLoop = Len(strText) - Len _
    (Application.Substitute(strText, "/", "")) - 1

    'Tell the array how many elements it is to hold.
    ReDim iPositions(iLoop) As Integer
    iStart = 1

    For i = 0 To iLoop 'loop four times
    'Parse the position of the nth "/" starting from iStart.
    iPositions(i) = InStr(iStart, strText, "/")

    'Add one to the found position, for next InStr to start from.
    iStart = iPositions(i) + 1

    MsgBox "Number " & i + 1 & " '/' is in position " & _
    iPositions(i), vbInformation, "OzGrid.com"
    Next i

    End Sub
    ........
    Tìm tiếp tại http://www.ozgrid.com/VBA/VBALoops.htm
     
    #4
  5. workman

    workman Thành viên sơ cấp

    Bài viết:
    372
    Đã được thích:
    0
    Nơi ở:
    Ho Chi Minh
    Site của bạn gửi hay lắm. Ko chỉ có phần lập trình mà các hướng dẫn về function mà mảng cũng cực kỳ dễ hiểu và thiết thực. Cám ơn bạn nhiều.
     
    #5
  6. adam_tran

    adam_tran Steel Partner

    Bài viết:
    1,373
    Đã được thích:
    32
    Nơi ở:
    Goooogle
    Lại có thêm một cao thủ nữa, lần này chóng mặt mà học...
    Bác workman lên lịch đi, rồi gọi cho em.

    Lập trình thì có nhiểu giải thuật hay, cái hay ở đây là biết dùng những công cụ có sẳn, phối hợp với nhau sao cho bài toán gọn nhẹ và tốc độ xử lý nhanh. Chưa đọc cái này thì biết quái thế nào là SpecialCells, mà biết rồi chưa đọc cái của người khác thì ứng dụng thế quái nào được...
    Bởi vậy, không thầy , không sách đố mày làm nên...
     
    Last edited: 18 Tháng tám 2005
    #6

Chia sẻ trang này