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

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?

VetMini · 14/8/19

Ba Tê đã viết:
Nếu Debug.Print a=b thì sao ta?

Thì sẽ ra True, bởi vì kiểu dữ liệu DateTime (a) và Double (b) có thể được ép về cùng dạng để so sánh với nhau.
Tuy nhiên, nếu bạn xem kỹ thì sẽ thấy bài #7 khai báo biến lấy dữ liệu va lu tu này là string.
Nếu a và b khai báo là string và A1 là 14-08-2019 thì
a = "14/08/2019"
b = "43691"
Và a=b là False

HieuCD · 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 code vào Module trong màn hình VBA
Chọn trở lại màn hình bảng tính sheet "Result", chọn menu Insert, chon5n Shapes, chọn hình Mặt cười, vẽ Shape vào bảng tính, bấm chuột phải vào Shape, chọn Assign Macro, chọn Macro tương ứng. Khi cần chạy code, bấm vào biểu tượng code

VetMini · 16/8/19

Chờ hơn 1 ngày rồi mà chả thấy ai đá động gì thêm tới cái vụ ngày và va-lu-tu.

Thử đặt A1:A5 = ngày 01/08 -> 05/08; và B1:B5 = N(A1) -> N(A5) (tức là trị của 5 ngày kia)
Bầy giờ chạy code này:
Sub t()
a = [a1:b5].Value
a2 = [a1:b5].Value2
[c1:d5] = a ' thuần Value
[e1:f5] = a2 ' Value chép từ Value2
[g1:h5].Value2 = a ' Value2 chép từ Value
[i1:j5].Value2 = a2 ' Value2 chép từ Value2
End Sub

Trong 4 cái kết quả nhận được, cái nào là cái quý vị muốn? Bản thân tôi thì chỉ chọn trường hợp thuần Value, không muốn nhìn tới 3 trường hợp kia.

Ở GPE này, phần lớn các code là lấy dữ liệu ra mảng, làm gì đó (có thể chuyển qua mảng khác) rồi chép trở lại vào một nơi khác.
Value2 là một chọn lựa rất nguy hiểm cho công việc này. Cái lợi của tốc độ không đủ bù lấp cái phiền phức - phải luôn lưu ý xem dữ liệu có chứa date.

Nếu quý vị chịu khó đọc những bài của tôi mấy năm về trước sẽ thấy tôi đã từng dùng Value2. Nhưng sau đó, nhận ra nó quá nguy hiểm cho GPE, tôi bỏ luôn.
Khi người ta nói về cái hay, cái đẹp của Value2 là người ta nói về trường hợp tổng quát. GPE là môi trường hơi đặc thù một chút.

batman1 · 16/8/19

VetMini đã viết:
Khi người ta nói về cái hay, cái đẹp của Value2 là người ta nói về trường hợp tổng quát. GPE là môi trường hơi đặc thù một chút.

Chính xác. Tôi đã lặp đi lặp lại điều này.
Mà nếu nói về tốc độ thì cũng không thể kiểu: Tôi thấy Value2 nhanh hơn Value nên tôi LUÔN dùng Value2. Không phải thế.
Thậm chí khi xét về tốc độ thì Value hay Value2 chỉ là một bước rất nhỏ khi làm một bài toán. Còn bước tìm kiếm, cộng trừ nhân chia, sao chép ... Bước này mới cải thiện tốc độ đáng kể. Thuật toán và các thủ thuật cho bước này mới đem lại sự cải thiện đáng kể. Khi xét một bài toán thì phải biết bước nào là bước cực kỳ quan trọng. Chọn "sai" phương pháp cho bước không quan trọng có thể làm tốc độ giảm đi không đáng kể nhưng chọn "sai" phương pháp cho bước quan trọng sẽ làm tốc độ giảm đi rất đáng kể. Làm gì cũng phải suy nghĩ. Không thể cứ: "phở" ngon hơn "cơm" vậy từ nay tôi sẽ chỉ ăn "phở". Không thể cứng nhắc như thế được.

Mà chuyện Value hay Value2 là quyền của mỗi người. Anh A dùng Value2 vì anh ta có quyền. Tôi không dùng Value2 vì trong trường hợp cụ thể tôi không quan trọng chuyện tốc độ ở mức "phần nghìn" s thì tôi cũng có quyền. Chả ai có quyền cười ai cả. Anh A "gom góp" từng nhịp đồng hồ còn tôi không màng thì sao có thể ai cười ai ở đây?

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

Người dùng đang xem chủ đề này

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

Đang đi tìm hòn đá

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

Đang đi tìm hòn đá

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

Đang đi tìm hòn đá

Chuyên gia GPE

Đang đi tìm hòn đá

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

Thời gian đếm ngược.