Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu hơn 1 triệu dòng.

[ptm0412]

Topic này chưa nên khép lại (khà khà)

Hiện tại tôi đã tạo dữ liệu mẫu chuẩn hơn:
- Ngày liên tục hơn
- Ngày theo thứ tự và rải ra trong khoảng từ 01/01/2013 đến 31/12/2013
- Danh mục 1.326 dòng
- Data 1.048.000 dòng

1. Test code và so sánh với dũ liệu cũ 65 ngàn dòng:

Tôi dùng lại thuật toán cũ, không sửa chút nào, trừ việc lấy vùng dữ liệu và vùng danh mục dài ra thôi. Kết quả: Dic nhanh hơn Collection.





Ngoài ra hiệu quả của Dic cũng cao hơn: thời gian chạy chỉ tăng 10.4 lần so với cũ, trong khi thời gian chạy collection gấp gần 21 lần so với cũ.

File nặng quá nên đang up lên mediafire: http://www.mediafire.com/view/tc45crxg0c656gz/MillionRow-VBA-Dictionary.xlsm

2. Thêm 1 bảng số dư đầu kỳ (đầu năm 2013) và test với cả việc có số dư đầu kỳ.
Hiện tôi đang dùng thuật toán cũ lấy thêm số dư đầu kỳ, chưa thay đồi thuật toán (tôi đã đưa ý tưởng thay đổi thuật toán lên rồi). KHi có kết quả tôi sẽ thông báo.

Mọi người thử tìm cách tính thêm số dư đầu kỳ vào và cho biết tốc độ ra sao.

 

[ptm0412]

Kết quả cho việc tính toán có lấy số dư đầu kỳ:
- Sử dụng lại thuật toán cũ
- Thuật toán cũ đã dự phòng việc lấy số dư nên chỉ sửa rất ít
- Thuật toán cũ được xây dựng cho dữ liệu chưa được sắp xếp, nên không tối ưu
- So với việc không lấy số dư, chỉ tốn thêm khoảng 200 ms



Mời mọi người thử sức và hãy vượt qua mức 3950 ms này trước.

 

[Lê Duy Thương]

Sao máy em bị lỗi này ???

 

[ptm0412]

Sao máy em bị lỗi này ???

Thương xóa giùm dòng code đó, biến đó không khai báo và cũng không dùng đến.

 

[vodoi2x]

Kết quả cho việc tính toán có lấy số dư đầu kỳ:
- Sử dụng lại thuật toán cũ
- Thuật toán cũ đã dự phòng việc lấy số dư nên chỉ sửa rất ít
- Thuật toán cũ được xây dựng cho dữ liệu chưa được sắp xếp, nên không tối ưu
- So với việc không lấy số dư, chỉ tốn thêm khoảng 200 ms



Mời mọi người thử sức và hãy vượt qua mức 3950 ms này trước.

Nếu dữ liệu khủng như vậy, sao PTM còn thử sức chi,

Hãy làm như bước bạn nói đặt name động - để bắt đúng khoảng thời điểm cần xét mà thôi, còn thêm nhập tồn đầu kỳ cũng không phải là vấn đề khó,

 

[ptm0412]

Nếu dữ liệu khủng như vậy, sao PTM còn thử sức chi,

Hãy làm như bước bạn nói đặt name động - để bắt đúng khoảng thời điểm cần xét mà thôi, còn thêm nhập tồn đầu kỳ cũng không phải là vấn đề khó,

Chuyện dễ làm trước mà, sửa code tí tẹo. Hơn nữa cũng là để ước tính việc thêm đầu kỳ mất bao lâu. Tôi cũng đang mò theo thuật toán kia, chuyện khó làm từ từ

 

[vodoi2x]

Chuyện dễ làm trước mà, sửa code tí tẹo. Hơn nữa cũng là để ước tính việc thêm đầu kỳ mất bao lâu. Tôi cũng đang mò theo thuật toán kia, chuyện khó làm từ từ

Vậy thử sub này với file của PTM, đã xử lý cả tồn, code áp dụng dictionary khá nhanh

PTM thử xem có đạt tốc độ không, chắc tốc độ dưới 2500mls ở máy tính bạn

(file này cơ bản giữ thuật toán cũ , có xét thêm chút đến yếu tố ngày được sắp xếp - nếu lớn hơn thì thoát FOR lun)

Private Sub LapSo()
    ''Code lap so th nxt
    ''Su Dung Dictionary cai tien moi co tinh ca ton, du lieu 1 trieu dong file PTM
    ''nguoi Lap: vodoi2x
    ''email: vodoi909090@yahoo.com
    '' ngay cap nhap: 19.02.2014
    
    Dim lCal
    Application.ScreenUpdating = False
        lCal = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    Dim DicH, arrRes(), soDM(), soTon()
    Dim Day1 As Long, Day2 As Long, i As Long, k As Long, p As Long, n As Long, j As Long
    Dim DicD, Ngay(), MaSoHH(), SoLG(), LoaiPhieu(), ThanhTien()
    
    
    ''Xác dinh dong cuoi cua data va nap vao mang
    With Sheet20
        k = .Cells(4, 1).End(xlDown).Row
        MaSoHH = .Range("G4:G" & k).Value2
        SoLG = .Range("H4:H" & k).Value2
        ThanhTien = .Range("K4:K" & k).Value2
        LoaiPhieu = .Range("J4:J" & k).Value2
        Ngay = .Range("B4:B" & k).Value2
        n = k - 3
    End With
    
    ''Nhap du lieu ngay tinh toan
    Day1 = Sheet26.[D1].Value2
    Day2 = Sheet26.[D2].Value2
     
    ''nhap DL tu vung DM VLSPHH va TÔN
    With Sheet1
        p = .[A10000].End(xlUp).Row
        soDM = .Range("A4:C" & p).Value2
        soTon = Range(.Cells(4, 8), .Cells(4, 8).End(xlDown)).Resize(, 3).Value2
    End With
    p = p - 3
    
    ReDim arrRes(1 To p + 10, 1 To 12) ''Mang chua ket qua gom 12 cot, 10 so ma du phong chua co trong DM VLSPHH
    Set DicH = CreateObject("Scripting.Dictionary") '' khoi tao Dictionary DicH dung de giu vi tri cua 1 MaHH trong mang arrRes
    k = UBound(soTon, 1)
    For i = 1 To k
        DicH(soTon(i, 1)) = i
        arrRes(i, 2) = soTon(i, 1)
        arrRes(i, 5) = soTon(i, 2)
        arrRes(i, 6) = soTon(i, 3)
    Next i
    
    For i = 1 To n ''Duyet tung dong chung tu cua Kho de xet ngay
        If Ngay(i, 1) <= Day2 Then
            
            p = DicH.Item(MaSoHH(i, 1))
            If p = 0 Then ''Truong hop CHUA CO MaHH trong Dictionary DicH, nen ta cong vao, va gan gia tri vao arrRes
                k = k + 1
                DicH(MaSoHH(i, 1)) = k
                arrRes(k, 2) = MaSoHH(i, 1)
                p = k
            End If '' If p = 0 Then
            
            If Ngay(i, 1) < Day1 Then       ''ton dau ky
                If LoaiPhieu(i, 1) Like "N" Then
                    arrRes(p, 5) = arrRes(p, 5) + SoLG(i, 1)
                    arrRes(p, 6) = arrRes(p, 6) + ThanhTien(i, 1)
                Else
                    arrRes(p, 5) = arrRes(p, 5) - SoLG(i, 1)
                    arrRes(p, 6) = arrRes(p, 6) - ThanhTien(i, 1)
                End If
            Else                           ''trong ky
                If LoaiPhieu(i, 1) Like "N" Then
                    arrRes(p, 7) = arrRes(p, 7) + SoLG(i, 1)
                    arrRes(p, 8) = arrRes(p, 8) + ThanhTien(i, 1)
                Else
                    arrRes(p, 9) = arrRes(p, 9) + SoLG(i, 1)
                    arrRes(p, 10) = arrRes(p, 10) + ThanhTien(i, 1)
                End If
            End If
            
        Else
            Exit For
        End If ''-------------------------------------------------------- If Ngay(i, 1) <= Day2 Then
    Next i
    Set DicH = Nothing
    ''tao DicDM de luu tru vi tri cua Ten hang hoa & Don vi tinh
    Set DicD = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(soDM)
        DicD(soDM(i, 1)) = i
    Next i
    ''tinh toan TON CUOI KY & tinh Tong GrandTotal cua cac cot Thanh tien: TonDK, NHAP, XUAT, & TON CUOI
    ''bang cach duyet cac dong cua arrRes
    p = k + 1
    
    For i = 1 To k
        arrRes(i, 1) = i
        j = DicD(arrRes(i, 2))
        If j > 0 Then
            arrRes(i, 3) = soDM(j, 2)
            arrRes(i, 4) = soDM(j, 3)
        End If
        arrRes(i, 11) = arrRes(i, 5) + arrRes(i, 7) - arrRes(i, 9)
        arrRes(i, 12) = arrRes(i, 6) + arrRes(i, 8) - arrRes(i, 10)
        
        arrRes(p, 6) = arrRes(p, 6) + arrRes(i, 6)
        arrRes(p, 8) = arrRes(p, 8) + arrRes(i, 8)
        arrRes(p, 10) = arrRes(p, 10) + arrRes(i, 10)
    Next i
    arrRes(p, 12) = arrRes(p, 6) + arrRes(p, 8) - arrRes(p, 10)
    
    ''Xuat ket qua ra Sheet
    With Sheet26.Range("b12")
        .Resize(13, 12).ClearContents
        If k Then .Resize(p, 12) = arrRes
    End With
    
    Application.Calculation = lCal
End Sub

 

[ptm0412]

Vodoi2x cho hỏi: Code trên đang tính cho khoảng thời gian từ ngày nào đến ngày nào?

 

[vodoi2x]

Vodoi2x cho hỏi: Code trên đang tính cho khoảng thời gian từ ngày nào đến ngày nào?

giờ mới để ý là
Từ ngày 01/02/13 đến 30/04/13

còn nếu tinh từ 01/02/13 đến 30/08/13 -- thì thời gian tính dài hơn: khoảng 3000mls

 

[ptm0412]

Thuật toán mới là dùng name lấy 2 vùng dữ liệu trước ngày đầu và trong kỳ báo cáo. Như vậy chỉ tính toán số dòng bằng số dòng 2 mảng đó cộng lại. Nghĩa là tính cho tháng 1 sẽ nhanh nhất, chậm dần cho đến tháng 12. nếu tính cả năm sẽ chậm nhất.

Thuật toán cũ thì chậm hơn, nhưng đều hơn, vì số dòng tính toán như nhau cho mọi khoảng thời gian,

Cả 2 thuật toán giống nhau ở chỗ giai đoạn 1 tính 2 số, giai đoạn sau tính 4 số, giai đoạn đầu nhiều dòng hơn sẽ nhanh hơn 1 chút.


Nói về thuật toán cũ:

Sao mình ngu quá, thời gian đã sắp xếp tăng dần thì hễ lớn hơn ngày cuối thoàt ra cho khỏe. Nhưng vẫn chậm hơn vodoi2x 1 chút (với 2 cột đầu với khoảng thời gian giống vodoi2x)

Sub LapSo()
    Application.ScreenUpdating = False
    Dim ListArr(), sArrID(), TmpArr(), RArr(), sArrQty(), sArrAmt(), sArrDocType(), sArrDate()
    Dim Dic1 As Object, EndR As Long, ListCt As Long
    Dim DataCt As Long, ListEndR As Long, Date1 As Long, Date2 As Long
    Dim StockArr(), EndStkR, StkDic As Object, StkCnt, KeysDic1()
    Dim i As Long, j As Long, k As Long, Check As Double, IDTest As String
    ListEndR = Sheet1.[A10000].End(xlUp).Row
    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set StkDic = CreateObject("Scripting.Dictionary")
    ''Lay danh muc vao mang
    ListArr = Sheet1.Range("A4:C" & ListEndR).Value2
    ListCt = UBound(ListArr, 1)
    '' lay hang vao mang
    EndStkR = Sheet1.[H10000].End(xlUp).Row
    StockArr = Sheet1.Range("H4:J" & EndStkR).Value
    StkCnt = UBound(StockArr, 1)
        ''Nap mang danh muc vao Dic
    For i = 1 To ListCt
        Dic1.Add ListArr(i, 1), i
    Next
    ''Xác dinh dong cuoi cua data va nap vao mang
    With Sheet20
    EndR = .Cells(4, 1).End(xlDown).Row
        sArrID = .Range("G4:G" & EndR).Value2
        sArrQty = .Range("H4:H" & EndR).Value2
        sArrAmt = .Range("K4:K" & EndR).Value2
        sArrDocType = .Range("J4:J" & EndR).Value2
        sArrDate = .Range("B4:B" & EndR).Value2
    End With
    DataCt = EndR - 3
    '' gan gia tri cho bien
    ReDim TmpArr(1 To ListCt, 1 To 7)
    Date1 = Sheet26.[D1]
    Date2 = Sheet26.[D2]
    ''Duyet mang Data
    For i = 1 To DataCt
            ' 'Xac dinh dong chua ma HH trong danh muc, gan vao cot 1 cua KQ tam
            j = Dic1.Item(sArrID(i, 1))
            TmpArr(j, 1) = j
        ''Neu ngay < ngay bat dau, tinh 2 cot ton dau
        If sArrDate(i, 1) < Date1 Then
            If sArrDocType(i, 1) = "N" Then
                ''Cong nhap
                TmpArr(j, 2) = TmpArr(j, 2) + sArrQty(i, 1)
                TmpArr(j, 3) = TmpArr(j, 3) + sArrAmt(i, 1)
            Else
                ''Tru xuat
                TmpArr(j, 2) = TmpArr(j, 2) - sArrQty(i, 1)
                TmpArr(j, 3) = TmpArr(j, 3) - sArrAmt(i, 1)
            End If
        ''Neu ngay trong khoang bao cao
        ElseIf sArrDate(i, 1) <= Date2 Then
            ''Neu loai chung tu là N, tinh 2 cot Nhap
            If sArrDocType(i, 1) = "N" Then
                TmpArr(j, 4) = TmpArr(j, 4) + sArrQty(i, 1)
                TmpArr(j, 5) = TmpArr(j, 5) + sArrAmt(i, 1)
            ''Neu loai chung tu la X, tinh 2 cot xuat
            Else
                TmpArr(j, 6) = TmpArr(j, 6) + sArrQty(i, 1)
                TmpArr(j, 7) = TmpArr(j, 7) + sArrAmt(i, 1)
            End If
        Else
            Exit For
        End If
    ''Ket thuc vong lap, Mang KQ tam co 1326 dong
    Next
    ''Khai bao  Mang KQua
    ReDim RArr(1 To ListCt, 1 To 12)
    k = 0
    KeysDic1 = Dic1.keys
        '' nap mang ton vao Dic
    For i = 1 To StkCnt
        StkDic.Add StockArr(i, 1), Array(StockArr(i, 2), StockArr(i, 3))
    Next
    ''Duyet mang KQ tam
    For i = 1 To ListCt
        ''Neu Mat hang co so du ma khong giao dich nhap xuat thi add vao tmpArr
        If IsEmpty(TmpArr(i, 1)) Then
            TmpArr(i, 1) = i
            IDTest = KeysDic1(TmpArr(i, 1) - 1)
            If StkDic.exists(IDTest) Then
                '' Gan dau ky
                TmpArr(i, 2) = StkDic.Item(IDTest)(0)
                TmpArr(i, 3) = StkDic.Item(IDTest)(1)
            End If
        Else
            IDTest = KeysDic1(TmpArr(i, 1) - 1)
            If StkDic.exists(IDTest) Then
                '' Cong them dau ky
                TmpArr(i, 2) = StkDic.Item(IDTest)(0) + TmpArr(i, 2)
                TmpArr(i, 3) = StkDic.Item(IDTest)(1) + TmpArr(i, 3)
            End If
        End If
            ''Kiem tra dong co du lieu
            Check = TmpArr(i, 2) + TmpArr(i, 3) + TmpArr(i, 4) + _
            TmpArr(i, 5) + TmpArr(i, 6) + TmpArr(i, 7)
        ''Neu co dulieu, them vao mang KQua
        If Check > 0 Then
            k = k + 1
            ''4 cot thong so Hang hoa
            RArr(k, 1) = k
            RArr(k, 2) = ListArr(i, 1)
            RArr(k, 3) = ListArr(i, 2)
            RArr(k, 4) = ListArr(i, 3)
            ''6 cot Ton, nhap, xuat
            For j = 5 To 10
               RArr(k, j) = TmpArr(i, j - 3)
            Next
            ''2 cot Ton cuoi
            RArr(k, 11) = RArr(k, 5) + RArr(k, 7) - RArr(k, 9)
            RArr(k, 12) = RArr(k, 6) + RArr(k, 8) - RArr(k, 10)
           
        End If
    Next
    '' Gan ket qua xuong sau khi xoa
    Sheet26.[B12].Resize(ListCt, 12).ClearContents
    Sheet26.[B12].Resize(k, 12) = RArr
Set Dic1 = Nothing: Set StkDic = Nothing
Application.ScreenUpdating = True
End Sub

Đã test code vodoi2x, 2 code tương đương, code của tôi chậm hơn 1 vài chục ms.

Còn thuật toán mới ra kết quả như sau,

 

[ptm0412]

Tôi lại viết VBA với thuật toán mới: Thuật toán này dưới 1500 ms tính cho 1 tháng, bất kỳ tháng nào.

- Nếu ngày bắt đầu là ngày 1 của tháng, thì không tính đầu kỳ 1 dòng dữ liệu nào. Nếu ngày đầu không phải ngày 1 của tháng, chỉ tính đầu kỳ từ ngày 1 tháng đó đến ngày trước ngày bắt đầu.
- Trong kỳ thì tất nhiên là tính từ ngày bắt đầu đến ngày kết thúc.

Kể cả nếu tính tháng 12 (từ 01/12 đến 31/12) trước đầy phải tính đủ 1 triệu dòng, bây giờ chỉ tính nội trong khoảng thời gian cần tính thôi.

Tôi giả định rằng hàng tháng, nếu ngày Date2 là ngày cuối tháng, thì ghi kết quả cuối kỳ của báo cáo xuống 1 chỗ nào đó để dành xài. và tôi giả định là đã ghi đủ 11 tháng, còn tháng 12 chưa ghi.

Giả sử có sửa xóa gì đó sẽ cần ghi lại, còn không thì thôi, nên tôi dùng 1 Msgbox để hỏi có muốn ghi hay không. Nếu không ghi thì code chạy nhanh hơn.

     'gán ton cuoi ky xuong sheet ton, neu la cuoi thang'
    If Month(Date2 + 1) > Month(Date2) Then
        MthCol = Month(Date2) * 3 + 1
        With Sheet2
            .Cells(4, MthCol).Resize(k, 1).Value = Sheet26.[C12].Resize(k, 1).Value
            .Cells(4, MthCol + 1).Resize(k, 2).Value = Sheet26.[L12].Resize(k, 2).Value
        End With
    End If

Trên thực tế với dữ liệu nhiều thì các phần mềm lớn đều ghi lại như vậy, mỗi tháng ghi 1 file. Rồi các báo cáo sau đó tìm file gần nhất để mở ra lấy số dư.

File nặng, upload lên mediafire lâu quá nên tạm chưa có file.
_____________

Đã có file: http://www.mediafire.com/download/b1eh66u6488zqqs/MillionRow-VBA-2DictionaryNew2.rar

 

[Hai Lúa Miền Tây]

Để cho dể theo dõi và tìm kiếm, em xin tách bài [THI] Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu 65,532 dòng sang đây. Mong các anh chị quan tâm đến vấn đề này thì tiếp tục thảo luận.

Với dữ liệu hơn 1 triệu dòng như thế này theo các anh chị và các bạn có thể thực hiện = ado không?

 

[Hai Lúa Miền Tây]

Để cho dể theo dõi và tìm kiếm, em xin tách bài [THI] Tạo sổ TH NXT với tốc độ nhanh nhất, dữ liệu 65,532 dòng sang đây. Mong các anh chị quan tâm đến vấn đề này thì tiếp tục thảo luận.

Với dữ liệu hơn 1 triệu dòng như thế này theo các anh chị và các bạn có thể thực hiện = ado không?

Chưa test kỹ nhưng nếu dùng ado thì tốc độ sẽ chậm hơn so với cách trên 6-7 lần.

 

[vodoi2x]

Chưa test kỹ nhưng nếu dùng ado thì tốc độ sẽ chậm hơn so với cách trên 6-7 lần.

Có phải ADO tính được luôn triệu dòng không? hay phải tách ra? HLMT làm như thế nào?

 

[Hai Lúa Miền Tây]

Có phải ADO tính được luôn triệu dòng không? hay phải tách ra? HLMT làm như thế nào?

Tính luôn, không cần tách ra đâu anh.

 

[Cá ngừ F1]

Giải pháp dùng Power Query cho bài toán Xuất Nhập Tồn này so với Dictionary thì đúng là một vực - một trời.
Tiếp theo những góp ý của Tỷ tỷ @ngocmaipretty, hy vọng đã đúng.

Làm quen với Google Drive – Một nơi cho tất cả các tệp của bạn

Google Drive là một cách miễn phí để sao lưu các tệp của bạn và dễ dàng truy cập chúng từ mọi điện thoại, máy tính bảng hoặc máy tính. Bắt đầu với 15GB bộ nhớ của Google – miễn phí.

drive.google.com

Bài đã được tự động gộp: 26/11/20

Tính luôn, không cần tách ra đâu anh.

Dùng ADO chắc thuật toán cũng giống Power Query anh nhỉ?

 

[Cá ngừ F1]

Dùng Power Query tốc độ có thể không bằng Dictionary, tuy nhiên, dùng PQ làm online đổi nguồn rất cơ động.
Nguồn có thể để ở máy nào đó trên mạng Lan, hoặc trên one driver, google driver...
File báo cáo nhẹ tênh (Click thực hiện để chạy báo cáo), không phải đeo cả cái dữ liệu nặng nề.
Chỉ là chế thêm từ tài liệu thực hành của sư phụ @ptm0412, rất mong mọi người có thể góp ý để Query này chạy nhanh hơn.
Em cảm ơn.

 

[Hau151978]

Dùng Power Query tốc độ có thể không bằng Dictionary, tuy nhiên, dùng PQ làm online đổi nguồn rất cơ động.
Nguồn có thể để ở máy nào đó trên mạng Lan, hoặc trên one driver, google driver...
File báo cáo nhẹ tênh (Click thực hiện để chạy báo cáo), không phải đeo cả cái dữ liệu nặng nề.
Chỉ là chế thêm từ tài liệu thực hành của sư phụ @ptm0412, rất mong mọi người có thể góp ý để Query này chạy nhanh hơn.
Em cảm ơn.

Code của em bị lặp nhiều quá, ví dụ đã có table KHO, NGAY1, NGAY2 rồi thì trong các bảng NHẬP XUẤT không cần tải lại các bảng này nữa. Tuy vậy code chậm chủ yếu do tính toán quá nặng. CPU core i3 của máy anh có khi tới 100%, chắc cần cải tiến code này ở TongHop.
Table KHO:

let
    Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="KHO"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO_CT", type text}, {"NGAY_CT", type date}, {"MA_KH", type text}, {"MA_NB", type text}, {"MA_NV", type any}, {"KHO", type text}, {"MA_VLSPHH", type text}, {"SLG", Int64.Type}, {"DON_GIA", Int64.Type}, {"LOAI_PHIEU", type text}, {"THANH_TIEN", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MA_VLSPHH", Order.Ascending}, {"NGAY_CT", Order.Ascending}})
    //#"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"NGAY_CT", type date}})
in
    #"Sorted Rows"

Table NhapTrongKyBC

let
    //DateFrom= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="NGAY1"]}[Data]{0}[Column1],
    //DateTo= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="NGAY2"]}[Data]{0}[Column1],
    DateFrom=NGAY1{0}[DateFrom],
    DateTo=NGAY2{0}[DateTo],    
    //Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="KHO"]}[Data],
    //#"Changed Type" = Table.TransformColumnTypes(Source,{{"SO_CT", type text}, {"NGAY_CT", type date}, {"MA_KH", type text}, {"MA_NB", type text}, {"MA_NV", type any}, {Source, type text}, {"MA_VLSPHH", type text}, {"SLG", Int64.Type}, {"DON_GIA", Int64.Type}, {"LOAI_PHIEU", type text}, {"THANH_TIEN", Int64.Type}}),
    //#"Sorted Rows" = Table.Sort(#"Changed Type",{{"MA_VLSPHH", Order.Ascending}, {"NGAY_CT", Order.Ascending}}),
    //#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([NGAY_CT] >= DateFrom and [NGAY_CT] <= DateTo and [LOAI_PHIEU] = "N")),
    #"Filtered Rows" = Table.SelectRows(KHO, each ([NGAY_CT] >= DateFrom and [NGAY_CT] <= DateTo and [LOAI_PHIEU] = "N")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"MA_VLSPHH"}, {{"SLNhapTrongKyBC", each List.Sum([SLG]), type nullable number},{"STNhapTrongKyBC", each List.Sum([THANH_TIEN]), type nullable number}})
in
    #"Grouped Rows"

 

[ngocmaipretty]

rất mong mọi người có thể góp ý để Query này chạy nhanh hơn.

Ngày bắt đầu và ngày kết thúc báo cáo thì lấy trên file báo cáo, chứ sao lại lấy trên file dữ liệu nguồn?
Đã vậy sửa Ngày bắt đầu và ngày kết thúc để ra báo cáo mới thì cả query lẫn ngày bị refresh y như cũ

 

[Cá ngừ F1]

Code của em bị lặp nhiều quá, ví dụ đã có table KHO, NGAY1, NGAY2 rồi thì trong các bảng NHẬP XUẤT không cần tải lại các bảng này nữa. Tuy vậy code chậm chủ yếu do tính toán quá nặng. CPU core i3 của máy anh có khi tới 100%, chắc cần cải tiến code này ở TongHop.
Table KHO:

let
    Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="KHO"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO_CT", type text}, {"NGAY_CT", type date}, {"MA_KH", type text}, {"MA_NB", type text}, {"MA_NV", type any}, {"KHO", type text}, {"MA_VLSPHH", type text}, {"SLG", Int64.Type}, {"DON_GIA", Int64.Type}, {"LOAI_PHIEU", type text}, {"THANH_TIEN", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"MA_VLSPHH", Order.Ascending}, {"NGAY_CT", Order.Ascending}})
    //#"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"NGAY_CT", type date}})
in
    #"Sorted Rows"

Table NhapTrongKyBC

let
    //DateFrom= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="NGAY1"]}[Data]{0}[Column1],
    //DateTo= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="NGAY2"]}[Data]{0}[Column1],
    DateFrom=NGAY1{0}[DateFrom],
    DateTo=NGAY2{0}[DateTo],    
    //Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=9D22195809ABDE05%215116&authkey=ANC6dtzABL4Eq6k&em=2"), null, true){[Name="KHO"]}[Data],
    //#"Changed Type" = Table.TransformColumnTypes(Source,{{"SO_CT", type text}, {"NGAY_CT", type date}, {"MA_KH", type text}, {"MA_NB", type text}, {"MA_NV", type any}, {Source, type text}, {"MA_VLSPHH", type text}, {"SLG", Int64.Type}, {"DON_GIA", Int64.Type}, {"LOAI_PHIEU", type text}, {"THANH_TIEN", Int64.Type}}),
    //#"Sorted Rows" = Table.Sort(#"Changed Type",{{"MA_VLSPHH", Order.Ascending}, {"NGAY_CT", Order.Ascending}}),
    //#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([NGAY_CT] >= DateFrom and [NGAY_CT] <= DateTo and [LOAI_PHIEU] = "N")),
    #"Filtered Rows" = Table.SelectRows(KHO, each ([NGAY_CT] >= DateFrom and [NGAY_CT] <= DateTo and [LOAI_PHIEU] = "N")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"MA_VLSPHH"}, {{"SLNhapTrongKyBC", each List.Sum([SLG]), type nullable number},{"STNhapTrongKyBC", each List.Sum([THANH_TIEN]), type nullable number}})
in
    #"Grouped Rows"

Cảm ơn anh Hậu, e đọc tài liệu và bài tập thực hành vẫn còn lơ tơ mơ quá ạ.

Ngày bắt đầu và ngày kết thúc báo cáo thì lấy trên file báo cáo, chứ sao lại lấy trên file dữ liệu nguồn?
Đã vậy sửa Ngày bắt đầu và ngày kết thúc để ra báo cáo mới thì cả query lẫn ngày bị refresh y như cũ

Tỷ tỷ mắng chuẩn, ngày báo cáo cần phải lấy từ sheet báo cáo mới cơ động, ko thể lấy từ Data, e sẽ sửa ngay, sửa ngay.