Công thức thay thế công thức IF để file chạy nhanh hơn.

thuhiennguyen1888 · 30/11/14

Chào cả nhà,
File của mình hiện đang dùng công thức với hàm IF ở các cột từ P - Y: Ví dụ tại P4=IF(E4=$A$4,$B$4,IF(E4=$A$5,$B$5,IF(E4=$A$6,$B$6, IF(E4=$A$7,$B$7,IF(E4=$A$8,$B$8,IF(E4=$A$9,$B$9, IF(E4=$A$10,$B$10,IF(E4=$A$11,$B$11,IF(E4=$A$12,$B$12, IF(E4=$A$13,$B$13,IF(E4=$A$14,$B$14,IF(E4=$A$15,$B$15, IF(E4=$A$16,$B$16,IF(E4=$A$17,$B$17,IF(E4=$A$18,$B$18, IF(E4=$A$19,$B$19,IF(E4=$A$20,$B$20,IF(E4=$A$21,$B$21, IF(E4=$A$22,$B$22,IF(E4=$A$23,$B$23,IF(E4=$A$24,$B$24, IF(E4=$A$25,$B$25,IF(E4=$A$26,$B$26,IF(E4=$A$27,$B$27, IF(E4=$A$28,$B$28,"")))))))))))))))))))))))))
Tuy nhiên vì dữ liệu nhiều, phải dùng công thức trên ở 10 cột & ở tất các dòng (hơn 1 triệu dòng).
Nếu chạy công thức ở 1 cột thì hết 30 phút, chạy ở 2 cột cùng lúc thì hết nửa ngày, còn nếu chạy ở 10 cột cùng lúc thì chắc phải vài ngày.
Vậy rất mong các bạn giúp cho công thức nào, kể cả VBA để mình có thể chạy file nhanh hơn.
Cám ơn các bạn.

hophoang · 30/11/14

thuhiennguyen1888 đã viết:
Chào cả nhà,
File của mình hiện đang dùng công thức với hàm IF ở các cột từ P - Y: Ví dụ tại P4=IF(E4=$A$4,$B$4,IF(E4=$A$5,$B$5,IF(E4=$A$6,$B$6, IF(E4=$A$7,$B$7,IF(E4=$A$8,$B$8,IF(E4=$A$9,$B$9, IF(E4=$A$10,$B$10,IF(E4=$A$11,$B$11,IF(E4=$A$12,$B$12, IF(E4=$A$13,$B$13,IF(E4=$A$14,$B$14,IF(E4=$A$15,$B$15, IF(E4=$A$16,$B$16,IF(E4=$A$17,$B$17,IF(E4=$A$18,$B$18, IF(E4=$A$19,$B$19,IF(E4=$A$20,$B$20,IF(E4=$A$21,$B$21, IF(E4=$A$22,$B$22,IF(E4=$A$23,$B$23,IF(E4=$A$24,$B$24, IF(E4=$A$25,$B$25,IF(E4=$A$26,$B$26,IF(E4=$A$27,$B$27, IF(E4=$A$28,$B$28,"")))))))))))))))))))))))))
Tuy nhiên vì dữ liệu nhiều, phải dùng công thức trên ở 10 cột & ở tất các dòng (hơn 1 triệu dòng).
Nếu chạy công thức ở 1 cột thì hết 30 phút, chạy ở 2 cột cùng lúc thì hết nửa ngày, còn nếu chạy ở 10 cột cùng lúc thì chắc phải vài ngày.
Vậy rất mong các bạn giúp cho công thức nào, kể cả VBA để mình có thể chạy file nhanh hơn.
Cám ơn các bạn.

Excell nhớ đâu tới 1 triệu dòng đâu ta.mà dùng nhiều if như trên liệu enter có được không

thuhiennguyen1888 · 30/11/14

hophoang đã viết:
Excell nhớ đâu tới 1 triệu dòng đâu ta.mà dùng nhiều if như trên liệu enter có được không

Excell 2010 có hơn 1 triệu dòng mà. Công thức đấy vẫn enter bình thường. Nếu dùng với dữ liệu ít (vài nghìn dòng) thì file vẫn chạy nhanh.

hophoang · 30/11/14

thuhiennguyen1888 đã viết:
Excell 2010 có hơn 1 triệu dòng mà. Công thức đấy vẫn enter bình thường. Nếu dùng với dữ liệu ít (vài nghìn dòng) thì file vẫn chạy nhanh.

Vậy mình không biết,mình sử dụng hàng cùi bắp
Bạn đưa dữ liệu thật bạn lên đi.

Hau151978 · 30/11/14

Công thức có thể rút gọn thành =IFERROR(VLOOKUP(E4,$A$4:$B$28,2,0),""). Không hiểu với 1M dòng thì bạn viết công thức IF kiểu gì?

viendinhngoc · 30/11/14

đề ngị đọc lại các hàm trong excel chứ đây là hàm tìm kiếm bình thường mà

thuhiennguyen1888 · 30/11/14

Hau151978 đã viết:
Công thức có thể rút gọn thành =IFERROR(VLOOKUP(E4,$A$4:$B$28,2,0),""). Không hiểu với 1M dòng thì bạn viết công thức IF kiểu gì?

Cám ơn Bạn, công thức Bạn giúp cho đã làm File chạy nhanh đáng kể, nhanh gấp nhiều lần khi mình dùng công thức với hàm IF.
Mình sẽ copy & paste công thức này đến dòng cuối cùng của Bảng tính (hơn 1 triệu dòng), chứ không phải là trong 1 công thức sẽ liệt kê hơn 1 triệu dòng.

Quang_Hải · 30/11/14

thuhiennguyen1888 đã viết:
Chào cả nhà,
File của mình hiện đang dùng công thức với hàm IF ở các cột từ P - Y: Ví dụ tại P4=IF(E4=$A$4,$B$4,IF(E4=$A$5,$B$5,IF(E4=$A$6,$B$6, IF(E4=$A$7,$B$7,IF(E4=$A$8,$B$8,IF(E4=$A$9,$B$9, IF(E4=$A$10,$B$10,IF(E4=$A$11,$B$11,IF(E4=$A$12,$B$12, IF(E4=$A$13,$B$13,IF(E4=$A$14,$B$14,IF(E4=$A$15,$B$15, IF(E4=$A$16,$B$16,IF(E4=$A$17,$B$17,IF(E4=$A$18,$B$18, IF(E4=$A$19,$B$19,IF(E4=$A$20,$B$20,IF(E4=$A$21,$B$21, IF(E4=$A$22,$B$22,IF(E4=$A$23,$B$23,IF(E4=$A$24,$B$24, IF(E4=$A$25,$B$25,IF(E4=$A$26,$B$26,IF(E4=$A$27,$B$27, IF(E4=$A$28,$B$28,"")))))))))))))))))))))))))
Tuy nhiên vì dữ liệu nhiều, phải dùng công thức trên ở 10 cột & ở tất các dòng (hơn 1 triệu dòng).
Nếu chạy công thức ở 1 cột thì hết 30 phút, chạy ở 2 cột cùng lúc thì hết nửa ngày, còn nếu chạy ở 10 cột cùng lúc thì chắc phải vài ngày.
Vậy rất mong các bạn giúp cho công thức nào, kể cả VBA để mình có thể chạy file nhanh hơn.
Cám ơn các bạn.

Nếu hơn 1 triệu dòng thì VBA còn muốn đơ huống chi là công thức
Thử thủ tục này coi sao

PHP:

Sub Buzhidao()
Dim Data(), Sarr(), Res(), I, J, Dic As Object
Set Dic = CreateObject("scripting.dictionary")
With Sheet1
   Data = .Range(.[A4], .[B1048576].End(3)).Value
   Sarr = .Range(.[E4], .[N1048576].End(3)).Value
   ReDim Res(1 To UBound(Sarr), 1 To UBound(Sarr, 2))
   For I = 1 To UBound(Data)
      Dic(Data(I, 1)) = Data(I, 2)
   Next
   For I = 1 To UBound(Sarr)
      For J = 1 To UBound(Sarr, 2)
         If Dic.exists(Sarr(I, J)) Then
            Res(I, J) = Dic.Item(Sarr(I, J))
         End If
      Next
   Next
   .[P4].Resize(UBound(Res), UBound(Res, 2)) = Res
End With
End Sub

Hau151978 · 30/11/14

thuhiennguyen1888 đã viết:
Excell 2010 có hơn 1 triệu dòng mà. Công thức đấy vẫn enter bình thường. Nếu dùng với dữ liệu ít (vài nghìn dòng) thì file vẫn chạy nhanh.

Bạn thử với code này. Dữ liệu cột A cần được sắp xếp.
Mở file, bấm nút "Tạo dữ liệu test" để khởi tạo dữ liệu. Bấm nút "Tính toán 1" hoặc "Tính toán 2" để xem và so sánh xem code nào nhanh hơn?

Mã:

Option Explicit
Dim a1()
Sub FillRange()
    Dim arr(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim arr(1 To maxrow, 1 To 10)
    Randomize
    For i = 1 To maxrow
        For j = 1 To 10
            arr(i, j) = Int(Rnd * 24 + 1)
        Next
    Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Range("E4").Resize(maxrow, 10) = arr
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Sub TinhToan1()
    Dim a2(), a3(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim a2(1 To maxrow, 1 To 10)
    ReDim a3(1 To maxrow, 1 To 10)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    a1 = Range("A4:B28").Value2
    a2 = Range("E4:N" & (maxrow + 3)).Value2
    For i = 1 To maxrow
        For j = 1 To 10
            a3(i, j) = SearchArray(a2(i, j))
        Next
    Next
    Range("P4").Resize(maxrow, 10) = a3
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Sub TinhToan2()
    Dim a2(), a3(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim a2(1 To maxrow, 1 To 10)
    ReDim a3(1 To maxrow, 1 To 10)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    a1 = Range("A4:B28").Value2
    a2 = Range("E4:N" & (maxrow + 3)).Value2
    For i = 1 To maxrow
        For j = 1 To 10
            a3(i, j) = QSearchArray(a2(i, j))
        Next
    Next
    Range("P4").Resize(maxrow, 10) = a3
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Function SearchArray(a)
    'search
    Dim i&
    SearchArray = ""
    For i = 1 To 25
        If a1(i, 1) = a Then
            SearchArray = a1(i, 2)
            Exit Function
        ElseIf a1(i, 1) > a Then
            Exit Function
        End If
    Next
End Function
Function QSearchArray(a)
    Dim i&, u_index&, l_index&
    l_index = 1
    u_index = 25
    QSearchArray = ""
    Do
        i = Int((u_index + l_index) / 2)
        If a1(i, 1) = a Then
                QSearchArray = a1(i, 2)
                Exit Function
            ElseIf a1(i, 1) > a Then
                u_index = i
            Else
                l_index = i
        End If
    Loop Until u_index = l_index
End Function

Kiều Mạnh · 30/11/14

Hau151978 đã viết:

Bạn thử với code này. Dữ liệu cột A cần được sắp xếp.
Mở file, bấm nút "Tạo dữ liệu test" để khởi tạo dữ liệu. Bấm nút "Tính toán 1" hoặc "Tính toán 2" để xem và so sánh xem code nào nhanh hơn?

Mã:

Option Explicit
Dim a1()
Sub FillRange()
    Dim arr(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim arr(1 To maxrow, 1 To 10)
    Randomize
    For i = 1 To maxrow
        For j = 1 To 10
            arr(i, j) = Int(Rnd * 24 + 1)
        Next
    Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Range("E4").Resize(maxrow, 10) = arr
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Sub TinhToan1()
    Dim a2(), a3(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim a2(1 To maxrow, 1 To 10)
    ReDim a3(1 To maxrow, 1 To 10)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    a1 = Range("A4:B28").Value2
    a2 = Range("E4:N" & (maxrow + 3)).Value2
    For i = 1 To maxrow
        For j = 1 To 10
            a3(i, j) = SearchArray(a2(i, j))
        Next
    Next
    Range("P4").Resize(maxrow, 10) = a3
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Sub TinhToan2()
    Dim a2(), a3(), maxrow&, i&, j&
    maxrow = 1000000
    ReDim a2(1 To maxrow, 1 To 10)
    ReDim a3(1 To maxrow, 1 To 10)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    a1 = Range("A4:B28").Value2
    a2 = Range("E4:N" & (maxrow + 3)).Value2
    For i = 1 To maxrow
        For j = 1 To 10
            a3(i, j) = QSearchArray(a2(i, j))
        Next
    Next
    Range("P4").Resize(maxrow, 10) = a3
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Function SearchArray(a)
    'search
    Dim i&
    SearchArray = ""
    For i = 1 To 25
        If a1(i, 1) = a Then
            SearchArray = a1(i, 2)
            Exit Function
        ElseIf a1(i, 1) > a Then
            Exit Function
        End If
    Next
End Function
Function QSearchArray(a)
    Dim i&, u_index&, l_index&
    l_index = 1
    u_index = 25
    QSearchArray = ""
    Do
        i = Int((u_index + l_index) / 2)
        If a1(i, 1) = a Then
                QSearchArray = a1(i, 2)
                Exit Function
            ElseIf a1(i, 1) > a Then
                u_index = i
            Else
                l_index = i
        End If
    Loop Until u_index = l_index
End Function

Mình test thử chạy Chậm quá máy đơ luôn

Hau151978 · 30/11/14

kieu manh đã viết:
Mình test thử chạy Chậm quá máy đơ luôn

Đầu tiên tạo dữ liệu test, sau đó bấm 1 trong 2 nút kia, thời gian khoảng 1 phút/nút, máy mình core i5, ram 4G.

Kiều Mạnh · 30/11/14

Hau151978 đã viết:
Đầu tiên tạo dữ liệu test, sau đó bấm 1 trong 2 nút kia, thời gian khoảng 1 phút/nút, máy mình core i5, ram 4G.

hèn chi máy của mình cấu hình bằng 1/3 thôi

gtri · 30/11/14

thuhiennguyen1888 đã viết:
Chào cả nhà,
File của mình hiện đang dùng công thức với hàm IF ở các cột từ P - Y: Ví dụ tại P4=IF(E4=$A$4,$B$4,IF(E4=$A$5,$B$5,IF(E4=$A$6,$B$6, IF(E4=$A$7,$B$7,IF(E4=$A$8,$B$8,IF(E4=$A$9,$B$9, IF(E4=$A$10,$B$10,IF(E4=$A$11,$B$11,IF(E4=$A$12,$B$12, IF(E4=$A$13,$B$13,IF(E4=$A$14,$B$14,IF(E4=$A$15,$B$15, IF(E4=$A$16,$B$16,IF(E4=$A$17,$B$17,IF(E4=$A$18,$B$18, IF(E4=$A$19,$B$19,IF(E4=$A$20,$B$20,IF(E4=$A$21,$B$21, IF(E4=$A$22,$B$22,IF(E4=$A$23,$B$23,IF(E4=$A$24,$B$24, IF(E4=$A$25,$B$25,IF(E4=$A$26,$B$26,IF(E4=$A$27,$B$27, IF(E4=$A$28,$B$28,"")))))))))))))))))))))))))
Tuy nhiên vì dữ liệu nhiều, phải dùng công thức trên ở 10 cột & ở tất các dòng (hơn 1 triệu dòng).
Nếu chạy công thức ở 1 cột thì hết 30 phút, chạy ở 2 cột cùng lúc thì hết nửa ngày, còn nếu chạy ở 10 cột cùng lúc thì chắc phải vài ngày.
Vậy rất mong các bạn giúp cho công thức nào, kể cả VBA để mình có thể chạy file nhanh hơn.
Cám ơn các bạn.

Góp vui thêm đoạn code.
Viết theo cấu trúc của file đính kèm

PHP:

Public Sub DoanMo()
Dim Nguon, Dich, Kq(), d As Long, c As Long
Nguon = Sheet1.Range("B4", Sheet1.Range("B1048576").End(xlUp))
Dich = Sheet1.Range("E4", Sheet1.Range("N1048576").End(xlUp))
ReDim Kq(1 To UBound(Dich), 1 To UBound(Dich, 2))

For d = 1 To UBound(Kq)
For c = 1 To UBound(Kq, 2)
Kq(d, c) = Nguon(Dich(d, c), 1)
Next c
Next d

Sheet1.Range("P4").Resize(UBound(Kq), UBound(Kq, 2)).Value = Kq

End Sub

----
Có lẽ chủ thớt cho file 1tr dòng lên để test thử xem sao

Quang_Hải · 30/11/14

hophoang đã viết:
Vậy mình không biết,mình sử dụng hàng cùi bắp
Bạn đưa dữ liệu thật bạn lên đi.

Chắc cú đang xài Office 2003 rồi nên mới vậy. Update lên 2010 sớm đi.

Công thức thay thế công thức IF để file chạy nhanh hơn.

thuhiennguyen1888

Thành viên chính thức

File đính kèm

hophoang

Thành viên tiêu biểu

thuhiennguyen1888

Thành viên chính thức

hophoang

Thành viên tiêu biểu

Hau151978

Thành viên tích cực

viendinhngoc

Thành viên hoạt động

thuhiennguyen1888

Thành viên chính thức

File đính kèm

Quang_Hải

Thành viên gạo cội

Hau151978

Thành viên tích cực

File đính kèm

Kiều Mạnh

I don't program, I beat code into submission!!!

Hau151978

Thành viên tích cực

Kiều Mạnh

I don't program, I beat code into submission!!!

gtri

Thành viên tích cực

Quang_Hải

Thành viên gạo cội

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian