Nhờ tối ưu thuật toán để tách khối lượng theo tháng

Liên hệ QC
NghiaKieu

NghiaKieu

Thành viên hoạt động
Tham gia
27/5/12
Bài viết
119
Được thích
83
Em đang sử dụng bảng tính sau để tách khối lượng thi công theo từng tháng nhưng với máy yếu thời gian chạy rất lâu. Rất mong các bác tối ưu thuật toán giúp để bảng tính chạy nhanh hơn ạ. Em cảm ơn ạ!

Cách tách em đang làm như sau ạ:
  • Căn cứ ngày bắt đầu và kết thúc của từng công việc tính tổng số ngày thi công thực tế theo bảng C1:p5
  • Lấy khối lượng chia tổng số ngày thi công thực tế ra khối lượng thi công trung bình 1 ngày
  • Lấy khối lượng thi công 1 ngày nhân với số ngày thi công thực tế từng tháng để được khối lượng thi công của từng tháng.
Tach vat lieu.png
 

File đính kèm

  • Tach khoi luong theo thang.xlsm
    579.7 KB · Đọc: 3
Lần chỉnh sửa cuối:
Giải pháp
NghiaKieu
Nhattanktnn đã viết:
ờ hình như cùng 1 tháng thì chưa đúng, mà giờ nghỉ làm rồi. Rảnh coi lại sau nhé!
Nhấp chuột vào đây để mở rộng...
Sau khi thử từng dòng cơ bản em đã hiểu code của anh và bổ sung thêm điều kiện cho 1 tháng rồi ạ. Thuật toán tuyệt vời quá, hơn cả mong đợi. Lần nữa cảm ơn anh, chúc anh và gia đình luôn mạnh khỏe!
Mã: 
Option Explicit

Sub TachNVL()
Dim lArr(), sArr(), dArr(), I&, J&, K&, fDate&, lDate&, sMax&, sMin&
Dim totalMonth&, totalDay#, Weight#, WperD#, dDate&
With Sheets("Tach vat lieu")
    lArr = .Range("D2:O5").Value
    sArr = .Range("E9:H" & .Cells(Rows.Count, "C").End(xlUp).Row).Value2
    sMin = 100000
    For I = 1 To UBound(sArr)
        If sArr(I, 3) < sMin And sArr(I, 3) > 41637 Then sMin = sArr(I, 3)
        If sArr(I, 4) > sMax Then sMax =...
Sắp xếp theo thời gian sắp xếp theo bầu chọn
NghiaKieu đã viết:
Em đang sử dụng bảng tính sau để tách khối lượng thi công theo từng tháng nhưng với máy yếu thời gian chạy rất lâu. Rất mong các bác tối ưu thuật toán giúp để bảng tính chạy nhanh hơn ạ. Em cảm ơn ạ!

Cách tách em đang làm như sau ạ:
  • Căn cứ ngày bắt đầu và kết thúc của từng công việc tính tổng số ngày thi công thực tế theo bảng C1:p5
  • Lấy khối lượng chia tổng số ngày thi công thực tế ra khối lượng thi công trung bình 1 ngày
  • Lấy khối lượng thi công 1 ngày nhân với số ngày thi công thực tế từng tháng để được khối lượng thi công của từng tháng.
View attachment 273257
Nhấp chuột vào đây để mở rộng...
Dữ liệu ban đầu có gì? kết quả code là vùng nào, cách tính tay như thế nào?
 
Upvote 0
HieuCD đã viết:
Dữ liệu ban đầu có gì? kết quả code là vùng nào, cách tính tay như thế nào?
Nhấp chuột vào đây để mở rộng...
Em gửi lại file đính kèm bị lỗi ạ:

  • Kết quả tách khối lượng từ I9: Số cột chia khối lượng của mỗi công việc sẽ theo số tháng thực hiện của công việc đó. Số hàng theo số lượng công việc (trong file ví dụ các công việc từ từ ô C9 đến C4055).
  • Cách tính:
    • Ví dụ công việc 1: bắt đầu từ ngày 13/03/2022 đến ngày 13/09/2022 thì chia khối lượng trong 7 tháng từ tháng 3 đến tháng 9 tức là ô I9 đến ô O9.
    • Bước 1: tính tổng số ngày thi công thực tế. Căn cứ vào ngày bắt đầu là 13/03/2022 thì tính ra tháng 3 số ngày thi công thực tế là (31-13+1)*0.97 (31 là số ngày tháng 3, 13 là ngày bắt đầu, 1 là tính là cả ngày 13, 0.97 là hệ số số ngày thi công thực tế bỏ qua ngày mưa). Các tháng 4, 5, 6, 7, 8 thi công đủ tháng thì lấy số ngày thi công thực tế trong bảng từ ô G4 đến K4 = 28+29+28+29+23 ngày. Tháng 9 tính tương tự 13*0.73. => Tổng số ngày thi công thực tế là: (31-13+1)*0.97+28+29+28+29+23+13*0.73=164.92 ngày.
    • Bước 2: Khối lượng thi công trung bình 1 ngày là: 1020.32/164.92=6.187 m2
    • Bước 3: Tính ra khối lượng từng tháng và điền vào các ô tương ứng từ I9 đến O9. Ví dụ j9 = 6.187*28 = 173.24 m2.
Hình ảnh trước khi chạy (dữ liệu có):Trước khi chạy.png
 

File đính kèm

  • Tach khoi luong theo thang.xlsm
    791 KB · Đọc: 8
Lần chỉnh sửa cuối:
Upvote 0
NghiaKieu đã viết:
Em gửi lại file đính kèm bị lỗi ạ:
Nhấp chuột vào đây để mở rộng...
Câu hỏi rõ ràng thế mà nhỉ? Bạn đặt mình là một người chưa biết gì, rồi người khác gửi cho bạn một file như vậy? Giải thích sơ sài, bạn có hiểu được không. "Dữ liệu ban đầu có gì? kết quả code là vùng nào" phần này có thể tạm đoán, nhưng "cách tính tay như thế nào" là phải có. Ghi chú thật đầy đủ chi tiết trong file thì sẽ được giúp đỡ nhanh hơn
 
Upvote 0
Nhattanktnn đã viết:
Câu hỏi rõ ràng thế mà nhỉ? Bạn đặt mình là một người chưa biết gì, rồi người khác gửi cho bạn một file như vậy? Giải thích sơ sài, bạn có hiểu được không. "Dữ liệu ban đầu có gì? kết quả code là vùng nào" phần này có thể tạm đoán, nhưng "cách tính tay như thế nào" là phải có. Ghi chú thật đầy đủ chi tiết trong file thì sẽ được giúp đỡ nhanh hơn
Nhấp chuột vào đây để mở rộng...
Em đang soạn và bổ sung như trên anh ạ!
 
Upvote 0
NghiaKieu đã viết:
Em đang soạn và bổ sung như trên anh ạ!
Nhấp chuột vào đây để mở rộng...
Dữ liệu của bạn đưa ra có cả năm 2022 và 2023, nhưng bảng số ngày trong tháng chỉ có 1. Giả sử, nếu bảng dò năm 2023 và dữ liệu có năm 2024 (năm nhuận) thì tháng 2 sẽ không phù hợp.
 
Upvote 0
Nhattanktnn đã viết:
Dữ liệu của bạn đưa ra có cả năm 2022 và 2023, nhưng bảng số ngày trong tháng chỉ có 1. Giả sử, nếu bảng dò năm 2023 và dữ liệu có năm 2024 (năm nhuận) thì tháng 2 sẽ không phù hợp.
Nhấp chuột vào đây để mở rộng...
Vâng anh. Đúng là em đang thiếu xét đến trường hợp năm nhuận. Để đơn giản quy ước luôn tháng 2 chỉ 28 ngày anh ạ. Và chỉ cần dùng 1 bảng tra cho các năm ạ.
 
Lần chỉnh sửa cuối:
Upvote 0
Upvote 0
Hoàng Tuấn 868 đã viết:
Hôm nào không mưa theo dự tính thì mang bơm nước ra phun cho khớp kế hoạch thôi.
Nhấp chuột vào đây để mở rộng...
Số ngày mưa theo tháng là số liệu của các năm trước (tra bảng), đưa vào để kế hoạch có thể sát với thực tế hơn thôi chứ không thể đúng 100%. Cái này có ý nghĩa nhiều để phân bổ khối lượng thi công trong mùa mưa cho hợp lý hơn thôi ạ.
 
Upvote 0
NghiaKieu đã viết:
Số ngày mưa theo tháng là số liệu của các năm trước (tra bảng), đưa vào để kế hoạch có thể sát với thực tế hơn thôi chứ không thể đúng 100%. Cái này có ý nghĩa nhiều để phân bổ khối lượng thi công trong mùa mưa cho hợp lý hơn thôi ạ.
Nhấp chuột vào đây để mở rộng...
Thường là tạm tính nhỉ.
 
Upvote 0
NghiaKieu đã viết:
Vâng anh. Kế hoạch với ngành nghề ảnh hưởng nhiều bởi thời tiết thì thường sai khác nhiều với thức tế lắm anh, và còn nhiều yếu tố khác ảnh hưởng đến kế hoạch nữa.
Nhấp chuột vào đây để mở rộng...
Chưa xử lý vấn đề tháng 2 năm nhuận, đang làm theo bảng tra:
Mã: 
Option Explicit

Sub TachNVL()
Dim lArr(), sArr(), dArr(), I&, J&, K&, fDate&, lDate&, sMax&, sMin&
Dim totalMonth&, totalDay#, Weight#, WperD#, dDate&
With Sheets("Tach vat lieu")
    lArr = .Range("D2:O5").Value
    sArr = .Range("E9:H" & .Cells(Rows.Count, "C").End(xlUp).Row).Value2
    sMin = 100000
    For I = 1 To UBound(sArr)
        If sArr(I, 3) < sMin Then sMin = sArr(I, 3)
        If sArr(I, 4) > sMax Then sMax = sArr(I, 4)
    Next
    totalMonth = Month(sMax) + (Year(sMax) - Year(sMin)) * 12 - Month(sMin) + 1
    ReDim dArr(1 To UBound(sArr) + 2, 1 To totalMonth)
    For I = 1 To totalMonth
        dArr(1, I) = Year(DateSerial(Year(sMin), Month(sMin) + I - 1, 1))
        dArr(2, I) = (Month(sMin) + I - 2) Mod 12 + 1
    Next
    For I = 1 To UBound(sArr)
        Weight = sArr(I, 1)
        fDate = sArr(I, 3)
        lDate = sArr(I, 4)
        totalDay = 0: WperD = 0
        For J = Month(fDate) To Month(lDate) + (Year(lDate) - Year(fDate)) * 12
            K = (J - 1) Mod 12 + 1
            totalDay = totalDay + lArr(3, K)
        Next
        totalDay = totalDay - (Day(fDate) - 1) * lArr(4, Month(fDate)) - (DateSerial(Year(lDate), Month(lDate) + 1, 0) - lDate) * lArr(4, Month(lDate))
        WperD = Weight / totalDay
        For J = 1 To totalMonth
            dDate = DateSerial(dArr(1, J), dArr(2, J), 1)
            If dDate > DateSerial(Year(fDate), Month(fDate), 1) And dDate < DateSerial(Year(lDate), Month(lDate), 1) Then
                dArr(I + 2, J) = WperD * lArr(3, dArr(2, J))
            ElseIf dDate = DateSerial(Year(fDate), Month(fDate), 1) Then
                dArr(I + 2, J) = WperD * (DateSerial(Year(fDate), Month(fDate) + 1, 0) - fDate + 1) * lArr(4, dArr(2, J))
            ElseIf dDate = DateSerial(Year(lDate), Month(lDate), 1) Then
                dArr(I + 2, J) = WperD * Day(lDate) * lArr(4, dArr(2, J))
                Exit For
            End If
        Next
    Next
    .Range(.Cells(7, "I"), .Cells(Rows.Count, Columns.Count)).ClearContents
    .Range("I7").Resize(UBound(dArr), UBound(dArr, 2)) = dArr
End With
End Sub
 
Upvote 0
Nhattanktnn đã viết:
Chưa xử lý vấn đề tháng 2 năm nhuận, đang làm theo bảng tra:
Mã: 
Option Explicit

Sub TachNVL()
Dim lArr(), sArr(), dArr(), I&, J&, K&, fDate&, lDate&, sMax&, sMin&
Dim totalMonth&, totalDay#, Weight#, WperD#, dDate&
With Sheets("Tach vat lieu")
    lArr = .Range("D2:O5").Value
    sArr = .Range("E9:H" & .Cells(Rows.Count, "C").End(xlUp).Row).Value2
    sMin = 100000
    For I = 1 To UBound(sArr)
        If sArr(I, 3) < sMin Then sMin = sArr(I, 3)
        If sArr(I, 4) > sMax Then sMax = sArr(I, 4)
    Next
    totalMonth = Month(sMax) + (Year(sMax) - Year(sMin)) * 12 - Month(sMin) + 1
    ReDim dArr(1 To UBound(sArr) + 2, 1 To totalMonth)
    For I = 1 To totalMonth
        dArr(1, I) = Year(DateSerial(Year(sMin), Month(sMin) + I - 1, 1))
        dArr(2, I) = (Month(sMin) + I - 2) Mod 12 + 1
    Next
    For I = 1 To UBound(sArr)
        Weight = sArr(I, 1)
        fDate = sArr(I, 3)
        lDate = sArr(I, 4)
        totalDay = 0: WperD = 0
        For J = Month(fDate) To Month(lDate) + (Year(lDate) - Year(fDate)) * 12
            K = (J - 1) Mod 12 + 1
            totalDay = totalDay + lArr(3, K)
        Next
        totalDay = totalDay - (Day(fDate) - 1) * lArr(4, Month(fDate)) - (DateSerial(Year(lDate), Month(lDate) + 1, 0) - lDate) * lArr(4, Month(lDate))
        WperD = Weight / totalDay
        For J = 1 To totalMonth
            dDate = DateSerial(dArr(1, J), dArr(2, J), 1)
            If dDate > DateSerial(Year(fDate), Month(fDate), 1) And dDate < DateSerial(Year(lDate), Month(lDate), 1) Then
                dArr(I + 2, J) = WperD * lArr(3, dArr(2, J))
            ElseIf dDate = DateSerial(Year(fDate), Month(fDate), 1) Then
                dArr(I + 2, J) = WperD * (DateSerial(Year(fDate), Month(fDate) + 1, 0) - fDate + 1) * lArr(4, dArr(2, J))
            ElseIf dDate = DateSerial(Year(lDate), Month(lDate), 1) Then
                dArr(I + 2, J) = WperD * Day(lDate) * lArr(4, dArr(2, J))
                Exit For
            End If
        Next
    Next
    .Range(.Cells(7, "I"), .Cells(Rows.Count, Columns.Count)).ClearContents
    .Range("I7").Resize(UBound(dArr), UBound(dArr, 2)) = dArr
End With
End Sub
Nhấp chuột vào đây để mở rộng...
Tốc độ trên cả tuyệt vời bác ạ. Ấn cái xong luôn. Em vừa thử thì thấy 1 lỗi nhỏ một số dòng chia ra khối lượng lớn hơn, bác xem lại giúp em ạ. Cảm ơn bác!
 

File đính kèm

  • Lệch khối lượng.png
    Lệch khối lượng.png
    185.7 KB · Đọc: 13
  • Tach khoi luong theo thang.xlsm
    791.1 KB · Đọc: 9
Upvote 0
NghiaKieu đã viết:
Tốc độ trên cả tuyệt vời bác ạ. Ấn cái xong luôn. Em vừa thử thì thấy 1 lỗi nhỏ một số dòng chia ra khối lượng lớn hơn, bác xem lại giúp em ạ. Cảm ơn bác!
Nhấp chuột vào đây để mở rộng...
ờ hình như cùng 1 tháng thì chưa đúng, mà giờ nghỉ làm rồi. Rảnh coi lại sau nhé!
 
Upvote 0
Nhattanktnn đã viết:
ờ hình như cùng 1 tháng thì chưa đúng, mà giờ nghỉ làm rồi. Rảnh coi lại sau nhé!
Nhấp chuột vào đây để mở rộng...
Sau khi thử từng dòng cơ bản em đã hiểu code của anh và bổ sung thêm điều kiện cho 1 tháng rồi ạ. Thuật toán tuyệt vời quá, hơn cả mong đợi. Lần nữa cảm ơn anh, chúc anh và gia đình luôn mạnh khỏe!
Mã: 
Option Explicit

Sub TachNVL()
Dim lArr(), sArr(), dArr(), I&, J&, K&, fDate&, lDate&, sMax&, sMin&
Dim totalMonth&, totalDay#, Weight#, WperD#, dDate&
With Sheets("Tach vat lieu")
    lArr = .Range("D2:O5").Value
    sArr = .Range("E9:H" & .Cells(Rows.Count, "C").End(xlUp).Row).Value2
    sMin = 100000
    For I = 1 To UBound(sArr)
        If sArr(I, 3) < sMin And sArr(I, 3) > 41637 Then sMin = sArr(I, 3)
        If sArr(I, 4) > sMax Then sMax = sArr(I, 4)
    Next
    totalMonth = Month(sMax) + (Year(sMax) - Year(sMin)) * 12 - Month(sMin) + 1
    ReDim dArr(1 To UBound(sArr) + 2, 1 To totalMonth)
    For I = 1 To totalMonth
        dArr(1, I) = Year(DateSerial(Year(sMin), Month(sMin) + I - 1, 1))
        dArr(2, I) = (Month(sMin) + I - 2) Mod 12 + 1
    Next
    For I = 1 To UBound(sArr)
        Weight = sArr(I, 1)
        fDate = sArr(I, 3)
        lDate = sArr(I, 4)
        totalDay = 0: WperD = 0
        For J = Month(fDate) To Month(lDate) + (Year(lDate) - Year(fDate)) * 12
            K = (J - 1) Mod 12 + 1
            totalDay = totalDay + lArr(3, K)
        Next
        totalDay = totalDay - (Day(fDate) - 1) * lArr(4, Month(fDate)) - (DateSerial(Year(lDate), Month(lDate) + 1, 0) - lDate) * lArr(4, Month(lDate))
        WperD = Weight / totalDay
            For J = 1 To totalMonth
                dDate = DateSerial(dArr(1, J), dArr(2, J), 1)
                If dDate = DateSerial(Year(fDate), Month(fDate), 1) And DateSerial(Year(fDate), Month(fDate), 1) = DateSerial(Year(lDate), Month(lDate), 1) Then
                    dArr(I + 2, J) = Weight
                ElseIf dDate > DateSerial(Year(fDate), Month(fDate), 1) And dDate < DateSerial(Year(lDate), Month(lDate), 1) Then
                    dArr(I + 2, J) = WperD * lArr(3, dArr(2, J))
                ElseIf dDate = DateSerial(Year(fDate), Month(fDate), 1) Then
                    dArr(I + 2, J) = WperD * (DateSerial(Year(fDate), Month(fDate) + 1, 0) - fDate + 1) * lArr(4, dArr(2, J))
                ElseIf dDate = DateSerial(Year(lDate), Month(lDate), 1) Then
                    dArr(I + 2, J) = WperD * Day(lDate) * lArr(4, dArr(2, J))
                    Exit For
                End If
            Next
    Next
    .Range(.Cells(7, "I"), .Cells(Rows.Count, Columns.Count)).ClearContents
    .Range("I7").Resize(UBound(dArr), UBound(dArr, 2)) = dArr
End With
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
Giải pháp
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

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

ongke0711
  • Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
8 9 10
Trả lời
194
Đọc
10K
HeSanbi
HeSanbi
OverAC
Gặp gỡ giao lưu tại Hà Nội, chiều ngày 10/11/2025. cùng Ptm0412, OverAC và các anh chị thân thiết.
    • Thích
    • Yêu thích
Trả lời
4
Đọc
116
viethung78
V
Hai Lúa Miền Tây
Nhập liệu, truy vấn dữ liệu qua Internet = VB.Net
    • Thích
2
Trả lời
26
Đọc
10K
phamduyhoang2907
P
P
Connection string cho SQL Express 2005
Trả lời
7
Đọc
7K
phamduyhoang2907
P
T
Xin trợ giúp code về dò tìm dữ liệu
Trả lời
4
Đọc
172
CHAOQUAY
C
HeSanbi
  • Question
Lỗi VBA: Cài đặt thông số ForceVBALoadFromSource trong Registry để VBA biên dịch không bị lỗi
Trả lời
2
Đọc
85
HeSanbi
HeSanbi
Quyen182
  • Question
Xin giúp code VBA để chèn ảnh có sẵn trong Excel vào phần comment
Trả lời
0
Đọc
60
Quyen182
Quyen182
B
  • Question
Xin hướng dẫn tách 1 vùng dữ liệu sang thành nhiều sheet
Trả lời
2
Đọc
67
Hoàng Tuấn 868
H
Toanke063
  • Question
Xin hỏi code vba chuyển 1 file pdf bất kì thành ảnh
Trả lời
7
Đọc
153
cantl
C
HeSanbi
  • Đã giải quyết
ZaloExcel v2.91: Gửi tin nhắn, hình ảnh, bảng, biểu đồ, tập tin, tạo nhóm, thu thập tin nhắn
    • Thích
    • Cảm ơn
    • Yêu thích
2 3
Trả lời
55
Đọc
13K
HeSanbi
HeSanbi
Bebooby
Tách mã sản phẩm trong chuỗi thông tin
Trả lời
2
Đọc
63
NT Ngoc Yen
N
thlpro
Truy Vấn Công thức từ Sheet này Qua Sheet Khác
Trả lời
8
Đọc
170
thlpro
thlpro
N
  • Question
VBA - copy các sheets unhide và dạng giá trị theo vùng xác định
Trả lời
3
Đọc
108
huhumalu
H
S
Lý do công thức bị mờ tại ô Function
    • Cười
Trả lời
2
Đọc
74
sakurasumo
S
GPE-Trợ Lý
Đăng ký tham dự hội thảo tại Hà Nội: Power Query - Công cụ quản lý dữ liệu
    • Thích
    • Cảm ơn
    • Yêu thích
Trả lời
5
Đọc
655
GPE-Trợ Lý
GPE-Trợ Lý
N
  • Question
Làm ơn viết giùm mình macro làm tròn giá trị ạ
    • Cười
Trả lời
14
Đọc
140
ptm0412
ptm0412
CNKyNguyen
Giảm dung lượng của file excel
    • Cảm ơn
    • Cười
    • Thích
Trả lời
2
Đọc
249
CNKyNguyen
CNKyNguyen
hunganh.nguyen
Xin giúp code tách dòng tổng thành những dòng chi tiết với giá trị là 1
Trả lời
9
Đọc
420
vostro 1500
vostro 1500
P
Sự cố về hàm SUMIF
Trả lời
8
Đọc
241
yeudoi
yeudoi
OverAC
Thông báo nội dung xấu và bài viết vi phạm nội quy.
19 20 21
Trả lời
416
Đọc
41K
vanle33
vanle33
Xem thêm…

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

Back
Top Bottom