Hỏi vê việc file tính bị chậm khi dùng hàm Max(IF()

[heyhey1994]

Chào các anh chị trong diễn đàn,
Hiện em có viết 1 file tính nhưng khi chạy nó chạy rất lâu, sau khi tìm hiểu thì em biết nó lâu do em dùng 1 hàm Max(IF() ở cột W sheet Cotlechtamxien, do không biết file dữ liệu dài bao nhiêu nên em lấy đến ô 100000. Em không biết xử lý thế nào để tìm dòng cuối, nhờ anh chị chị giúp bằng code hoặc bằng công thức cũng được ạ

=(MAX(IF(((LAYDATA!$A$18:$A$100000=A18)*(LAYDATA!$B$18:$B$100000=B18)),LAYDATA!$O$18:$O$100000)))

Em xin cảm ơn ạ.

 

[Thóc Sama]

Chào các anh chị trong diễn đàn,
Hiện em có viết 1 file tính nhưng khi chạy nó chạy rất lâu, sau khi tìm hiểu thì em biết nó lâu do em dùng 1 hàm Max(IF() ở cột W sheet Cotlechtamxien, do không biết file dữ liệu dài bao nhiêu nên em lấy đến ô 100000. Em không biết xử lý thế nào để tìm dòng cuối, nhờ anh chị chị giúp bằng code hoặc bằng công thức cũng được ạ

=(MAX(IF(((LAYDATA!$A$18:$A$100000=A18)*(LAYDATA!$B$18:$B$100000=B18)),LAYDATA!$O$18:$O$100000)))

Em xin cảm ơn ạ.

Bạn thử code sau trong sự kiện Activesheet [Cotlechtamxien]

Private Sub Worksheet_Activate()
    Dim Dic As Object, Key, Lr&, i&, Arr(), Res(), k&
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("LAYDATA")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Arr = .Range("A18:O" & Lr).Value
        ReDim Res(1 To UBound(Arr), 1 To 1)
        For i = 1 To UBound(Arr)
            Key = Arr(i, 1) & "|" & Arr(i, 2)
            If Not Dic.exists(Key) Then
                Dic.Add (Key), Arr(i, 15)
            Else
                If Dic.Item(Key) < Arr(i, 15) Then Dic.Item(Key) = Arr(i, 15)
            End If
        Next i
    End With
    With Sheets("Cotlechtamxien")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Arr = .Range("A18:B" & Lr).Value
        For i = 1 To UBound(Arr)
            k = k + 1
            Key = Arr(i, 1) & "|" & Arr(i, 2)
            If Dic.exists(Key) Then
                Res(k, 1) = Dic.Item(Key)
            End If
        Next i
        .Range("W18:W" & Lr).ClearContents
        .Range("W18").Resize(k, 1).Value = Res
    End With
    Set Dic = Nothing
End Sub

 

[heyhey1994]

Bạn thử code sau trong sự kiện Activesheet [Cotlechtamxien]

Private Sub Worksheet_Activate()
    Dim Dic As Object, Key, Lr&, i&, Arr(), Res(), k&
    Set Dic = CreateObject("Scripting.Dictionary")
    With Sheets("LAYDATA")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Arr = .Range("A18:O" & Lr).Value
        ReDim Res(1 To UBound(Arr), 1 To 1)
        For i = 1 To UBound(Arr)
            Key = Arr(i, 1) & "|" & Arr(i, 2)
            If Not Dic.exists(Key) Then
                Dic.Add (Key), Arr(i, 15)
            Else
                If Dic.Item(Key) < Arr(i, 15) Then Dic.Item(Key) = Arr(i, 15)
            End If
        Next i
    End With
    With Sheets("Cotlechtamxien")
        Lr = .Range("A" & Rows.Count).End(xlUp).Row
        Arr = .Range("A18:B" & Lr).Value
        For i = 1 To UBound(Arr)
            k = k + 1
            Key = Arr(i, 1) & "|" & Arr(i, 2)
            If Dic.exists(Key) Then
                Res(k, 1) = Dic.Item(Key)
            End If
        Next i
        .Range("W18:W" & Lr).ClearContents
        .Range("W18").Resize(k, 1).Value = Res
    End With
    Set Dic = Nothing
End Sub

Dạ code của anh xài ok lắm, mà còn có 1 chút là sau khi em click vào ô cột W em xem số, khi ấn enter thì mấy ô liên quan lại bị lỗi VALUE, phải vào run code lại ạ?

 

[Maika8008]

Chào các anh chị trong diễn đàn,
Hiện em có viết 1 file tính nhưng khi chạy nó chạy rất lâu, sau khi tìm hiểu thì em biết nó lâu do em dùng 1 hàm Max(IF() ở cột W sheet Cotlechtamxien, do không biết file dữ liệu dài bao nhiêu nên em lấy đến ô 100000. Em không biết xử lý thế nào để tìm dòng cuối, nhờ anh chị chị giúp bằng code hoặc bằng công thức cũng được ạ

=(MAX(IF(((LAYDATA!$A$18:$A$100000=A18)*(LAYDATA!$B$18:$B$100000=B18)),LAYDATA!$O$18:$O$100000)))

Em xin cảm ơn ạ.

Dùng hàm Offset để tạo name động rồi lấy name đó dùng cho công thức.

 

[VetMini]

Dùng hàm Offset để tạo name động rồi lấy name đó dùng cho công thức.

Dùng array ($A$18:$A$100000=A18)*(B$18:$B$100000=B18) cho dữ liệu khủng thì chả có bảng tính nào chịu nổi.
Cách cải tiến (được chút xíu thôi) là dùng các bước sau đây:
1. Đặt mấy cái cột ấy vào name (tĩnh chứ không phải động) - name tĩnh giảm được thời gian tra cứu
2. Đặt cột phụ - cột phụ giảm đợc thời gian Excel gọi hàm (gọi chứ khong phải tính toán, dữ liệu khủng thì tính vẫn phải lâu.
3. Nếu có thể thì dùng Table
4. Nếu khả năng cao hơn thì cho vào Data Model và sử dụng Power Query

Nói chung thì dữ liệu đến hằng trăm nghìn dòng thì phải mướn chuyên viên cao tay nghề hơn một chút, tối thiểu cũng phải có chút ít kiến thức về lọc sạch dữ liệu (data cleaning) và moi móc dữ liệu (Data Mining).
Ở đây, các cơ quan coi như tay mơ, trả chừng 5-10 triệu/tháng, cũng có khả năng làm vuệc này.

 

[heyhey1994]

Dùng hàm Offset để tạo name động rồi lấy name đó dùng cho công thức.

Dùng array ($A$18:$A$100000=A18)*(B$18:$B$100000=B18) cho dữ liệu khủng thì chả có bảng tính nào chịu nổi.
Cách cải tiến (được chút xíu thôi) là dùng các bước sau đây:
1. Đặt mấy cái cột ấy vào name (tĩnh chứ không phải động) - name tĩnh giảm được thời gian tra cứu
2. Đặt cột phụ - cột phụ giảm đợc thời gian Excel gọi hàm (gọi chứ khong phải tính toán, dữ liệu khủng thì tính vẫn phải lâu.
3. Nếu có thể thì dùng Table
4. Nếu khả năng cao hơn thì cho vào Data Model và sử dụng Power Query

Nói chung thì dữ liệu đến hằng trăm nghìn dòng thì phải mướn chuyên viên cao tay nghề hơn một chút, tối thiểu cũng phải có chút ít kiến thức về lọc sạch dữ liệu (data cleaning) và moi móc dữ liệu (Data Mining).
Ở đây, các cơ quan coi như tay mơ, trả chừng 5-10 triệu/tháng, cũng có khả năng làm vuệc này.

Em cảm ơn hai anh ạ.
Em đã thử tự làm 1 hàm đơn giản để thay thế cho công thức này như sau thì thấy nó chạy nhanh hơn:

Function AS_Col(Storyvalue As Variant, ColumnValue As Variant) As Double
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim i As Long
    Dim maxValue As Double
    Set ws = ThisWorkbook.Sheets("LAYDATA")
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    maxValue = -1000
    For i = 18 To LastRow
        If ws.Cells(i, "A").Value = Storyvalue And ws.Cells(i, "B").Value = ColumnValue Then
            If ws.Cells(i, "O").Value > maxValue Then
                maxValue = ws.Cells(i, "O").Value
            End If
        End If
    Next i
    AS_Col = maxValue
End Function

Nhiều khi em cũng muốn giống anhVetMini nói là công ty sẽ thuê một chuyên viên cao tay nghề hơn để lập file tính, nhưng khổ nỗi là em làm bên xây dựng, thuần về kỹ thuật nhiều, nếu thuê một bạn không có chuyên môn về xây dựng thì công ngồi giải thích lại lý thuyết dùng cho file tính như thế nào cũng hơi mệt. Và với việc trả lương cho 1 người chỉ để viết 1 file tính cho hay cho chuyên nghiệp thì chắc sếp em không chiu Mà tiện thì anh cho em hỏi, nếu thật sự cần viết file tính về xây dựng như em kể trên, thì em có thể liên lạc với công ty nào ạ?