Tổng hợp số lượng từng mã hàng theo từng tháng (2 người xem)

[Huyenfsv]

Mình có 1 file này nhờ cả nhà giúp đỡ. Mình muốn tổng hợp số lượng từng mã hàng theo từng tháng

 

[Maika8008]

Mình có 1 file này nhờ cả nhà giúp đỡ. Mình muốn tổng hợp số lượng từng mã hàng theo từng tháng

Cụ thể là cột nào? Có đến năm bảy cột ngày tháng, không thấy mã hàng mà chỉ thấy Item Name

 

[VetMini]

Trên nguyên tắc, bất cứ cái gì có cụm từ "tổng hợp theo..." thì là công việc ủa Pivot Table.
Nếu Pivot không được thì dùng Power Query sàng lọc dữ liệu trước. Bài này đăng ở góc Micorsoft 365 thì không có lý do gì để nói "không có Power Query"

 

[Maika8008]

Trên nguyên tắc, bất cứ cái gì có cụm từ "tổng hợp theo..." thì là công việc ủa Pivot Table.
Nếu Pivot không được thì dùng Power Query sàng lọc dữ liệu trước. Bài này đăng ở góc Micorsoft 365 thì không có lý do gì để nói "không có Power Query"

Chắc chủ thớt không biết hỏi ở đâu nên đăng đại box 365

 

[ptm0412]

Cụ thể là cột nào? Có đến năm bảy cột ngày tháng, không thấy mã hàng mà chỉ thấy Item Name

Mã hàng là cột N (Buyer Item Code). Không phải năm bảy cột ngày mà là 31 cột ngày và 31 cột số lượng tương ứng. Đây là dạng dữ liệu theo chiều ngang.

Chắc chủ thớt không biết hỏi ở đâu nên đăng đại box 365

Tác giả đăng đại vào trong chủ đề hàm mảng mới trong excel 365, tôi tách ra thành chủ đề mới.

 

[ptm0412]

Mã hàng là cột N (Buyer Item Code). Không phải năm bảy cột ngày mà là 31 cột ngày và 31 cột số lượng tương ứng. Đây là dạng dữ liệu theo chiều ngang.

Dữ liệu ngang kiểu này Power query làm mệt chết, nên viết VBA sau đó mới Pivot table

Sub TransformColumns()
Dim SArr(), RArr(), LastRw As Long, DateDeli As Long
Application.ScreenUpdating = False
LastRw = Sheet1.[A100000].End(xlUp).Row
SArr = Sheet1.Range("A2:CJ" & LastRw).Value2
ReDim RArr(1 To UBound(SArr, 1) * 31, 1 To 20)
For i = 1 To UBound(SArr, 1)
    For j = 16 To 76 Step 2
        If SArr(i, j) <> "" And SArr(i, j + 1) > 0 Then
            m = m + 1
            For k = 1 To 15
                RArr(m, k) = SArr(i, k)
            Next
            DateDeli = SArr(i, j)
            RArr(m, 16) = DateSerial(2000 + Left(DateDeli, 2), Mid(DateDeli, 3, 2), Right(DateDeli, 2))
            RArr(m, 17) = SArr(i, j + 1)
            RArr(m, 18) = SArr(i, 79)
            RArr(m, 19) = SArr(i, 80)
            RArr(m, 20) = SArr(i, 81)
        End If
    Next
Next
Sheet2.[A2:T100000].ClearContents
Sheet2.[A2].Resize(m, 20) = RArr
Application.ScreenUpdating = True
End Sub

 

[hocexcel_1991]

Làm thử PowerQuery và PivotTable

 

[ptm0412]

Làm thử PowerQuery và PivotTable

Nhìn M code thấy ngợp quá, tôi cũng làm Power query, cũng UnPivot xong Pivot lại, nhưng tà đạo 1 chút.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Unit", "Buyer", "Revision", "Item Name (Specification)",
        "Supplier Item Code", "Buyer Item Code", "Packing Style", "Remarks", "Forecast No.", "Forecast Date", "Standard L/T"}, "Attribute", "Value"),
    ExtractedText = Table.TransformColumns(UnpivotedColumns, {{"Attribute", each Text.BeforeDelimiter(_, " "), type text}}),
    AddedIndex = Table.AddIndexColumn(ExtractedText, "Index", 1, 1, Int64.Type),
    AddedCustom = Table.AddColumn(AddedIndex, "Check", each if Number.IsOdd([Index]) then [Index] * 2 else ([Index]-1) * 2),
    RemovedIndex = Table.RemoveColumns(AddedCustom,{"Index"}),
    PivotTable=Table.Pivot(RemovedIndex,{"Delivery","Forecast"},"Attribute","Value",List.Sum),
    ChangedType = Table.TransformColumnTypes(PivotTable,{{"Check", Int64.Type}, {"Delivery", type text}, {"Forecast", type number}}),
    RemovedCheck = Table.RemoveColumns(ChangedType,{"Check"}),
    AddedDate = Table.AddColumn(RemovedCheck, "Delivery Date",
         each #date(
         Number.FromText(Text.Start([Delivery],2))+2000,
         Number.FromText(Text.Middle([Delivery],2,2)),
         Number.FromText(Text.End([Delivery],2))),type date)
in
    AddedDate

Cột Delivery date tôi chuyển thành dạng Date chứ không lấy riêng Year và Month, Pivot table kết quả tôi add cả year vào column , bởi nếu không add vào mà xem hết các năm thì các tháng giống nhau của cả các năm cũng bị cộng lại chung.

 

[hocexcel_1991]

Nhìn M code thấy ngợp quá

Em đang đang học nên vừa làm vừa google , thấy sử dụng hàm M khó hơn nhiều so với excel nên em làm chủ yếu là thao tác bằng tay thôi.

 

[ptm0412]

Em đang đang học nên vừa làm vừa google , thấy sử dụng hàm M khó hơn nhiều so với excel nên em làm chủ yếu là thao tác bằng tay thôi.

Tôi cũng làm tay 70 - 80%, xong xoá bớt step, gộp steps, hoặc sửa tay chút đỉnh. Tôi ngợp code của bạn ở thủ thuật Group thành 2 table, nhưng tôi học bạn vụ Table.Pivot và dùng thủ thuật khác dễ hiểu hơn

 

[VetMini]

Em đang đang học nên vừa làm vừa google , thấy sử dụng hàm M khó hơn nhiều so với excel nên em làm chủ yếu là thao tác bằng tay thôi.

Thời buổi bi giờ kỹ thuật thay đổi liền liền.
20 năm trước CSDL còn nằm trong vòng huyền bí. Chỉ có chuyên gia mới đụng vào.
10 năm trước thì nhiều người đã biết nguyên lý làm việc
5 năm trước thì các truy vấn đơn giản đã là "điều phải biết"
Ngày nay, truy vấn không còn là vấn đề gì nữa. Cái mà dân làm việc cần học là cái ý nghĩa của dữ liệu, và nhìn được là nó sẽ cho mình những gì.
Power Query hay M gì gì chỉ là công cụ.
Mục đích cuối của bạn là thị trường thành phố X. Công cụ đưa hàng của bạn đến X, bằng tầu, bằng siêu xa lộ, hay bằng máy bay chỉ là một phần của tính lãi lỗ, khả thi.