Cần cách tính hàm Sumifs với dữ liệu lớn ra kết quả nhanh

Nguyễn Thành Châu · 13/8/19

Mình có file excel bên dưới tính tổng theo Sumifs nhưng gõ vào điều kiện thì khoảng 2-3 phút thì excel mới trả về kết quả. Bạn nào có cách nào hỗ trợ giúp mình có thể trả về kết quả nhanh được không? Do bảng data về sau sẽ thêm nhiều dữ liệu nữa. Mình thành thật cảm ơn các bạn nhiều lắm,mong sự giúp đỡ từ các bạn.

snow25 · 13/8/19

Nguyễn Thành Châu đã viết:
Mình có file excel bên dưới tính tổng theo Sumifs nhưng gõ vào điều kiện thì khoảng 2-3 phút thì excel mới trả về kết quả. Bạn nào có cách nào hỗ trợ giúp mình có thể trả về kết quả nhanh được không? Do bảng data về sau sẽ thêm nhiều dữ liệu nữa. Mình thành thật cảm ơn các bạn nhiều lắm,mong sự giúp đỡ từ các bạn.

Bạn sửa lại hàm của bạn dùng đến đâu công thức đến vùng đó.Đừng lấy cả cột vào như thế nó tính chẳng lâu.

Nguyễn Thành Châu · 13/8/19

snow25 đã viết:
Bạn sửa lại hàm của bạn dùng đến đâu công thức đến vùng đó.Đừng lấy cả cột vào như thế nó tính chẳng lâu.

Khi có dữ liệu mới mình sẽ dán vào ô bên dưới luôn. File này tổng hợp 1 tháng nên mình mới chọn lấy cả chọn cột. Bạn có cách nào tính nhanh hơn không hỗ trợ mình với. Mình cảm ơn.

dazkangel · 13/8/19

Bạn xem thử file này có khá hơn, với dữ liệu khủng thì mọi công thức đều khá vô ích, may ra có VBA làm trực tiếp thì ổn hơn.
Bạn chịu khó qua bên cột phụ ở sheet lấy dữ liệu kéo xuống nhé vì có thêm nó file nặng quá không tải lên đây được.

Nguyễn Thành Châu · 14/8/19

dazkangel đã viết:
Bạn xem thử file này có khá hơn, với dữ liệu khủng thì mọi công thức đều khá vô ích, may ra có VBA làm trực tiếp thì ổn hơn.
Bạn chịu khó qua bên cột phụ ở sheet lấy dữ liệu kéo xuống nhé vì có thêm nó file nặng quá không tải lên đây được.

Minh cảm ơn bạn nhiều nha, file nhanh hơn hẳn. Những đoạn code Vba trong file đó tác dụng như thế nào vậy bạn.

/

Lê Hồng Minh83 · 14/8/19

Nguyễn Thành Châu đã viết:
Minh cảm ơn bạn nhiều nha, file nhanh hơn hẳn. Những đoạn code Vba trong file đó tác dụng như thế nào vậy bạn.

/

Cái này dùng dictionary chắc ổn. Đến khoảng trưa nay nếu chưa có ai giúp đỡ, mình sẽ viết code cho bạn, giờ mình hơi bận xíu, chưa viết đc

HieuCD · 14/8/19

Nguyễn Thành Châu đã viết:
Mình có file excel bên dưới tính tổng theo Sumifs nhưng gõ vào điều kiện thì khoảng 2-3 phút thì excel mới trả về kết quả. Bạn nào có cách nào hỗ trợ giúp mình có thể trả về kết quả nhanh được không? Do bảng data về sau sẽ thêm nhiều dữ liệu nữa. Mình thành thật cảm ơn các bạn nhiều lắm,mong sự giúp đỡ từ các bạn.

Dùng VBA cho nhẹ File

Mã:

Option Explicit
Sub Tong()
  Dim i&, j&, sR&, iR&, jC&, Nv As String
  Dim Price(), Data(), tArr(), Res(), Dic As Object

  With Sheets("RESULT")
    tArr = .Range("E6:G6").Value
    Nv = .Range("B7").Value2
  End With
  ReDim Res(1 To 1, 1 To 3)
  With Sheets("Price")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 3 Then MsgBox ("Khong co du lieu"): Exit Sub
    Price = .Range("A3:E" & i).Value2
  End With
  With Sheets("DATA 1")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    Data = .Range("A2:M" & i).Value2
  End With
  Set Dic = CreateObject("scripting.dictionary")
  sR = UBound(Price)
  For i = 1 To sR
    Dic.Item(Application.Trim(Price(i, 1))) = i
  Next i
  For j = 1 To 3
    Dic.Item(UCase(tArr(1, j))) = j
  Next j
  sR = UBound(Data)
  For i = 1 To sR
    If Data(i, 13) = Nv Then
      iR = Dic.Item(Data(i, 4))
      If iR > 0 Then
        jC = Dic.Item(UCase(Data(i, 1)))
        If jC > 0 Then
          Res(1, jC) = Res(1, jC) + Data(i, 7) * Price(iR, jC + 2)
        End If
      End If
    End If
  Next i
  With Sheets("RESULT")
    .Range("E7:G7").Value2 = Res
  End With
  Set Dic = Nothing
End Sub

BuiQuangThuan · 14/8/19

HieuCD đã viết:
Dùng VBA cho nhẹ File
....

Thầy ơi. dùng value với value2 có khác nhau gì không ạ

Ba Tê · 14/8/19

buiquangthuan đã viết:
Thầy ơi. dùng value với value2 có khác nhau gì không ạ

Chắc bạn ít đọc các bài "vui" trên GPE.
Đọc bài này "vui" nè:

Chuyện vui VBA

Hì hì, sáng nay cuối tuần rãnh rỗi, ngồi hì hụi làm cái file Excel để minh họa cho chuyện vui. Cái cô kế toán nghỉ nên phải tự tay làm, mà mình thì mới học Ét xèo nữa, nên "thật nà vất vã". Chuyện như thế này, có 3 nhân vật trong chuyện, 1 là anh Excel, 2 là anh VBA, 3 là anh dùng VBA, hay còn...

www.giaiphapexcel.com

BuiQuangThuan · 14/8/19

Ba Tê đã viết:
Chắc bạn ít đọc các bài "vui" trên GPE.
Đọc bài này "vui" nè:

Chuyện vui VBA

Hì hì, sáng nay cuối tuần rãnh rỗi, ngồi hì hụi làm cái file Excel để minh họa cho chuyện vui. Cái cô kế toán nghỉ nên phải tự tay làm, mà mình thì mới học Ét xèo nữa, nên "thật nà vất vã". Chuyện như thế này, có 3 nhân vật trong chuyện, 1 là anh Excel, 2 là anh VBA, 3 là anh dùng VBA, hay còn...

www.giaiphapexcel.com

Hihi. Cám ơn Thầy ạ

HieuCD · 14/8/19

buiquangthuan đã viết:
Thầy ơi. dùng value với value2 có khác nhau gì không ạ

Value2 nhanh hơn Value 0.17 giây

Ba Tê · 14/8/19

HieuCD đã viết:
Value2 nhanh hơn Value 0.17 giây

1 giây thì "chớp mắt" mấy lần ta?
Dzọt...............................

HieuCD · 14/8/19

Ba Tê đã viết:
1 giây thì "chớp mắt" mấy lần ta?
Dzọt...............................

Nghe nói Value2 nhanh hơn Value nên làm theo không kiểm tra chênh lệch, giờ mới thử lại tiết kiệm được 1 cái chớp mắt

Nguyễn Thành Châu · 14/8/19

HieuCD đã viết:

Dùng VBA cho nhẹ File

Mã:

Option Explicit
Sub Tong()
  Dim i&, j&, sR&, iR&, jC&, Nv As String
  Dim Price(), Data(), tArr(), Res(), Dic As Object

  With Sheets("RESULT")
    tArr = .Range("E6:G6").Value
    Nv = .Range("B7").Value2
  End With
  ReDim Res(1 To 1, 1 To 3)
  With Sheets("Price")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 3 Then MsgBox ("Khong co du lieu"): Exit Sub
    Price = .Range("A3:E" & i).Value2
  End With
  With Sheets("DATA 1")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    Data = .Range("A2:M" & i).Value2
  End With
  Set Dic = CreateObject("scripting.dictionary")
  sR = UBound(Price)
  For i = 1 To sR
    Dic.Item(Application.Trim(Price(i, 1))) = i
  Next i
  For j = 1 To 3
    Dic.Item(UCase(tArr(1, j))) = j
  Next j
  sR = UBound(Data)
  For i = 1 To sR
    If Data(i, 13) = Nv Then
      iR = Dic.Item(Data(i, 4))
      If iR > 0 Then
        jC = Dic.Item(UCase(Data(i, 1)))
        If jC > 0 Then
          Res(1, jC) = Res(1, jC) + Data(i, 7) * Price(iR, jC + 2)
        End If
      End If
    End If
  Next i
  With Sheets("RESULT")
    .Range("E7:G7").Value2 = Res
  End With
  Set Dic = Nothing
End Sub

MÌnh cảm ơn bạn nhiều lắm.

Nguyễn Thành Châu · 14/8/19

HieuCD đã viết:

Dùng VBA cho nhẹ File

Mã:

Option Explicit
Sub Tong()
  Dim i&, j&, sR&, iR&, jC&, Nv As String
  Dim Price(), Data(), tArr(), Res(), Dic As Object

  With Sheets("RESULT")
    tArr = .Range("E6:G6").Value
    Nv = .Range("B7").Value2
  End With
  ReDim Res(1 To 1, 1 To 3)
  With Sheets("Price")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 3 Then MsgBox ("Khong co du lieu"): Exit Sub
    Price = .Range("A3:E" & i).Value2
  End With
  With Sheets("DATA 1")
    i = .Range("A" & Rows.Count).End(xlUp).Row
    If i < 2 Then MsgBox ("Khong co du lieu"): Exit Sub
    Data = .Range("A2:M" & i).Value2
  End With
  Set Dic = CreateObject("scripting.dictionary")
  sR = UBound(Price)
  For i = 1 To sR
    Dic.Item(Application.Trim(Price(i, 1))) = i
  Next i
  For j = 1 To 3
    Dic.Item(UCase(tArr(1, j))) = j
  Next j
  sR = UBound(Data)
  For i = 1 To sR
    If Data(i, 13) = Nv Then
      iR = Dic.Item(Data(i, 4))
      If iR > 0 Then
        jC = Dic.Item(UCase(Data(i, 1)))
        If jC > 0 Then
          Res(1, jC) = Res(1, jC) + Data(i, 7) * Price(iR, jC + 2)
        End If
      End If
    End If
  Next i
  With Sheets("RESULT")
    .Range("E7:G7").Value2 = Res
  End With
  Set Dic = Nothing
End Sub

Bạn cho mình hỏi ngu cái nha, Sau dán code vào thì làm như thế nào nữa bạn. Mình không biết gì về VBA luôn.

nguyensatran · 14/8/19

Nguyễn Thành Châu đã viết:
Bạn cho mình hỏi ngu cái nha, Sau dán code vào thì làm như thế nào nữa bạn. Mình không biết gì về VBA luôn.

Bạn dán được rồi thì nhấn F5 thử nhé

VetMini · 14/8/19

Ba Tê đã viết:
1 giây thì "chớp mắt" mấy lần ta?
Dzọt...............................

HieuCD đã viết:
Nghe nói Value2 nhanh hơn Value nên làm theo không kiểm tra chênh lệch, giờ mới thử lại tiết kiệm được 1 cái chớp mắt

Tiết kiệm được 1 chớp mắt, và kéo thớt dài được thêm mấy bài

befaint · 14/8/19

buiquangthuan đã viết:
dùng value với value2 có khác nhau gì không

Không xét gì nhanh/ chậm, với dữ liệu DateTime có sự khác nhau rõ ràng.

VetMini · 14/8/19

befaint đã viết:
Không xét gì nhanh/ chậm, với dữ liệu DateTime có sự khác nhau rõ ràng.
...

Thiệt ra cái vụ "va lu tu" này ở GPE đã từng nói qua cách đây mấy năm.
Theo tiếng Tây thì nó là "Old news".

Chỉ là lần này, người khơi chuyện có cái danh bự quá cho nên nhiều người chú ý.

Ba Tê · 14/8/19

befaint đã viết:
Không xét gì nhanh/ chậm, với dữ liệu DateTime có sự khác nhau rõ ràng.

View attachment 222806

Nếu Debug.Print a=b thì sao ta?

Cần cách tính hàm Sumifs với dữ liệu lớn ra kết quả nhanh

Thành viên mới

File đính kèm

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

Thành viên mới

<New Horizons>

File đính kèm

Thành viên mới

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

Chuyên gia GPE

❆❆❆❆❆❆❆❆❆❆❆❆

Cạo Rồi Khỏi Gội

❆❆❆❆❆❆❆❆❆❆❆❆

Chuyên gia GPE

Cạo Rồi Khỏi Gội

Chuyên gia GPE

Thành viên mới

Thành viên mới

Thành viên mới

Ăn cùng góc phố

|||||||||||||

Ăn cùng góc phố

Cạo Rồi Khỏi Gội

Facebook

Group