Nhập xuất tồn kho bằng Power query

[Hoàng Nhật Phương]

Công việc của tôi ít có phải sử dụng Dic nên không kinh nghiệm mấy. Tôi có thử sử dụng Power query Table.Pivot, chắc chậm hơn code của anh, và chưa biết 500 ngàn dòng của tác giả chạy sẽ chậm đến mức nào

Tạo danh mục kho để làm tham số cho hàm Table.Pivot, kết quả ở sheet Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source,{"SL_Hang_Dat_Mua", "SL_Dang_Tren_Duong_Ve",  "UnitId", "Ma_Kich_Thuoc", "Ma_Kieu_Dang", "SL_Thuc_Co",
        "SL_Dat_Truoc_Cho_Khach", "Ma_Hang", "Ma_Con", "Ma_Mau", "Ten_Cua_Hang", "Ma_Vi_Tri_Trong_Kho", "Ma_Thung_Hang", "Trang_Thai_Ma_Hang",
        "BarCode", "Ma_Khu_Vuc", "Ten_Khu_Vuc"}),
    ReorderColumns = Table.ReorderColumns(RemovedColumns,{"GROUPBRANDS", "BRAND", "THEME",   "MA_VT", "Ten_Hang","Gia_Le","Ma_Kho", "SL_Hang_Co_The_Dat_Duoc"}),
    RenamedColumns = Table.RenameColumns(ReorderColumns,{{"SL_Hang_Co_The_Dat_Duoc", "SL"}}),
    PivotData = Table.Pivot(RenamedColumns,List.Buffer(DMKho[Kho]),"Ma_Kho","SL",List.Sum)
in
    PivotData

Con thấy chú Mỹ & bạn @excel_lv1.5 sử dụng cái món "Power query" này rất cao siêu.
Vậy chú cho con tham khảo thêm cách dùng "Power query" ở bài này với ạ:

Truy vấn trong SQL?

Nếu tài khoản của bạn của bạn chỉ được quyền Read thì việc tạo Table sẽ không được, nhưng Table tạm vẫn được, khi bạn không sử dụng SQL nữa Table đó sẽ tự xóa, tôi chỉnh lại code trên bằng việc sử dụng Table tạm SELECT TDK.[MA_HANG],TDK.[KHO_LUU_TRU],TDK.[LOTNO], TDK.[SO_LUONG] [TONDAUKY], 0...

www.giaiphapexcel.com

 

[ptm0412]

đi chùa ở Nghệ An

Tiện thể đi chùa thì sau khi đảnh lễ cúng dường chư Phật và Bồ Tát xong, đừng xin hộ trì tiền bạc mà hãy cầu xin ban cho trí huệ, đặng đọc thông kinh tạng, thứ nữa đọc hiểu bài lão chết tiệt. Chứ qua chục bài rồi mà chỗ sai không đọc không hiểu không xem, cứ đòi xem lại kết quả hoài.

 

[Hoàng Nhật Phương]

Làm gì có dòng nào chứa công thức?
Cách xử lý là phải xử lý từ gốc dữ liệu. Ra excel là phải chuẩn. Không chỉ chuẩn về cấu trúc, mà còn phải chuẩn về ràng buộc quan hệ, ràng buộc mandatory (trường bắt buộc nhập), ràng buộc về quy trình (mỗi tháng phải có 1 lần kiểm kê điều chỉnh), ràng buộc về tính liên tục và kế thừa.

Con chào chú Mỹ,
Con cảm ơn chú đã quan tâm đến dữ liệu con gửi ạ.
Chú tải file bài nào đó ạ, con vừa tải về xem thấy có công thức mà chú Mỹ:

 

[ptm0412]

Chú tải file bài nào đó ạ, con vừa tải về xem thấy có công thức mà chú Mỹ:

Tôi tải từ Mediafire Nhưng công thức đó có quan trọng không nêu dữ liệu sai? Và nếu công thức cũng sai?

 

[Hoàng Nhật Phương]

Tôi tải từ Mediafire Nhưng công thức đó có quan trọng không nêu dữ liệu sai? Và nếu công thức cũng sai?

Ơ công thức sai ạ chú Mỹ,
Con làm theo nhu cầu cần & so sánh của đồng nghiệp lúc đấy thôi ạ chứ thực sự con cũng không phân tích đúng sai thế nào ạ.
Còn về dữ liệu nhập suất có thể chênh lệch trong quá trình thì hoàn toàn có thể xảy ra vì do hệ thống mới mọi người chưa thao tác quen nên mới cần tríc xuất ra báo cáo để so sánh chú ạ.Khi nào có thời gian chú có thể phân tích rõ hơn trên dữ liệu của con được không ạ, có thể con sẽ kiểm tra và lấy lại dữ liệu logic hơn ạ.
Ủa con tải file tải từ Mediafire theo link chú gửi đúng là vẫn có công thức đó chú ạ.

 

[ptm0412]

Ơ công thức sai ạ chú Mỹ,
Khi nào có thời gian chú có thể phân tích rõ hơn trên dữ liệu của con được không ạ, có thể con sẽ kiểm tra và lấy lại dữ liệu logic hơn ạ.

Công thức sai:
1. Công thức 2 cột nhập xuất chẳng ngó ngàng gì đến L2 và N2 là ngày bắt đầu và ngày kết thúc, lấy tất tần tật, dù cho L2 đang là 1/10/2020
2. Công thức cột tồn đầu lấy ngày L2-1, nếu L2 không phải ngày đầu tháng thì sao?
3. Công thức cột SL xuất: H3 =SUMIFS(XN!F:F,XN!A:A,A3,XN!B:B,B3,XN!E:E,">="&2)-SUMIFS(XN!F:F,XN!A:A,A3,XN!E:E,3,XN!D,B3): >=2 bao gồm 3, trừ đi 3 làm gì? xuất bán/ dùng (loại 2) và xuất chuyển kho (loại 3) cũng đều phải trừ kho, chứ sao lại trừ ra?
Phân tích rõ hơn:
Có biết giá phân tích là bao nhiêu, và giá tư vấn là bao nhiêu 1 giờ không? Chưa xiêu lòng thì chưa nói chiện nhá!!

 

[Hoàng Nhật Phương]

Công thức sai:
1. Công thức 2 cột nhập xuất chẳng ngó ngàng gì đến L2 và N2 là ngày bắt đầu và ngày kết thúc, lấy tất tần tật, dù cho L2 đang là 1/10/2020
2. Công thức cột tồn đầu lấy ngày L2-1, nếu L2 không phải ngày đầu tháng thì sao?
3. Công thức cột SL xuất: H3 =SUMIFS(XN!F:F,XN!A:A,A3,XN!B:B,B3,XN!E:E,">="&2)-SUMIFS(XN!F:F,XN!A:A,A3,XN!E:E,3,XN!D,B3): >=2 bao gồm 3, trừ đi 3 làm gì? xuất bán/ dùng (loại 2) vá xuất chuyển kho (loại 3) cũng phải trừ kho chứ sao lại trừ ra?
Phân tích rõ hơn:
Có biết giá phân tích là bao nhiêu, và giá tư vấn là bao nhiêu 1 giờ không? Chưa xiêu lòng thì chưa nói chiện nhá!!

Ặc, thế chú khoan vôi xiêu đã ạ.
Để con tự phân tích theo gợi ý và xác nhận với đồng nghiệp của con thêm ạ, vì con hay hóng hớt bon chen vào việc của người khác nên có gì cái là mọi người đều ới con,mà ới là cứ thế con làm theo yêu cầu cần thời điểm đó chứ con chưa biết đúng sai logic thế nào chú ạ.

 

[Hoàng Nhật Phương]

Công thức sai:
1. Công thức 2 cột nhập xuất chẳng ngó ngàng gì đến L2 và N2 là ngày bắt đầu và ngày kết thúc, lấy tất tần tật, dù cho L2 đang là 1/10/2020
2. Công thức cột tồn đầu lấy ngày L2-1, nếu L2 không phải ngày đầu tháng thì sao?
3. Công thức cột SL xuất: H3 =SUMIFS(XN!F:F,XN!A:A,A3,XN!B:B,B3,XN!E:E,">="&2)-SUMIFS(XN!F:F,XN!A:A,A3,XN!E:E,3,XN!D,B3): >=2 bao gồm 3, trừ đi 3 làm gì? xuất bán/ dùng (loại 2) và xuất chuyển kho (loại 3) cũng đều phải trừ kho, chứ sao lại trừ ra?
Phân tích rõ hơn:
Có biết giá phân tích là bao nhiêu, và giá tư vấn là bao nhiêu 1 giờ không? Chưa xiêu lòng thì chưa nói chiện nhá!!

Dạ chú con vừa xem lại là như thế này ạ,

1.Do lỗi công thức từ dòng 3 là sai rồi chú, công thức mới là ở dòng 2 chú ạ, con cũng đã báo cáo với chú ở bài 13 ạ.

..
Với lại dữ liệu này từ dòng 3 con quên chưa kéo công thức từ dòng 2 xuống chú ạ.
...

2. Vâng cột dữ liệu tồn đầu không có ngày con lấy từ trong database chỉ có năm và tháng "202009" (yyymm) nên con minh họa tháng trước tháng cần trích xuất dữ liệu & thường mọi người luôn lấy từ đầu tháng chú ạ.
3.Cái kiểu kho(phân loại) con cũng không nhớ nhưng lần đó các bạn đồng nghiệp barp qui định gì không tính nên con mới loại ra miễn sao khớp số theo ý các bạn ấy là được chú ạ.

 

[ptm0412]

3.Cái kiểu kho(phân loại) con cũng không nhớ nhưng lần đó các bạn đồng nghiệp barp qui định gì không tính nên con mới loại ra miễn sao khớp số theo ý các bạn ấy là được chú ạ.

Một là số theo ý các bạn đúng mà làm sai, 2 là ý các bạn ý sai nốt.
Có 3 loại là:
- Loại 1: Nhập kho và tăng số lượng kho lưu trữ
- Loại 2: Xuất kho và giảm số lượng kho lưu trữ
- Loại 3: Chuyển kho: Phải có kho đi và kho đến: Giảm kho lưu trữ và tăng kho KHO_NHAP.

Vậy thì
- Cột Nhập bao gồm loại 1 Kho lưu trữ + loại 3 kho nhập: công thức dòng 2 đúng
- Cột xuất bao gồm xuất dùng/ bán loại 2, và xuất chuyển kho loại 3, cả 2 loại giảm kho lưu trữ. Hết. Không trừ bớt gì cả.
Trí tuệ mới xin được đó, đem ra suy nghĩ đi:
Công thức cột nhập: (đã đúng)

G2 =SUMIFS(XN!F:F,XN!A:A,A2,XN!B:B,B2,XN!E:E,1,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)
+SUMIFS(XN!F:F,XN!A:A,A2,XN!E:E,3,XN!D:D,B2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)

Công thức cột xuất:

H2 =SUMIFS(XN!F:F,XN!A:A,A2,XN!B:B,B2,XN!E:E,">="&2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)
- SUMIFS(XN!F:F,XN!A:A,A2,XN!E:E,3,XN!D:D,B2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)

Hai dòng dưới của 2 công thức giống y hệt nhau. Tại sao cột xuất tính SL xuất rồi lại trừ bớt số lượng đã tính trong nhập?
Bởi vậy cột xuất mới âm tè le.

 

[Hoàng Nhật Phương]

Một là số theo ý các bạn đúng mà làm sai, 2 là ý các bạn ý sai nốt.
Có 3 loại là:
- Loại 1: Nhập kho và tăng số lượng kho lưu trữ
- Loại 2: Xuất kho và giảm số lượng kho lưu trữ
- Loại 3: Chuyển kho: Phải có kho đi và kho đến: Giảm kho lưu trữ và tăng kho KHO_NHAP.

Vậy thì
- Cột Nhập bao gồm loại 1 Kho lưu trữ + loại 3 kho nhập: công thức dòng 2 đúng
- Cột xuất bao gồm xuất dùng/ bán loại 2, và xuất chuyển kho loại 3, cả 2 loại giảm kho lưu trữ. Hết. Không trừ bớt gì cả.
Trí tuệ mới xin được đó, đem ra suy nghĩ đi:
Công thức cột nhập: (đã đúng)

G2 =SUMIFS(XN!F:F,XN!A:A,A2,XN!B:B,B2,XN!E:E,1,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)
+SUMIFS(XN!F:F,XN!A:A,A2,XN!E:E,3,XN!D:D,B2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)

Công thức cột xuất:

H2 =SUMIFS(XN!F:F,XN!A:A,A2,XN!B:B,B2,XN!E:E,">="&2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)
- SUMIFS(XN!F:F,XN!A:A,A2,XN!E:E,3,XN!D:D,B2,XN!G:G,">="&L$2,XN!G:G,"<="&N$2)

Hai dòng dưới của 2 công thức giống y hệt nhau. Tại sao cột xuất tính SL xuất rồi lại trừ bớt số lượng đã tính trong nhập?
Bởi vậy cột xuất mới âm tè le.

Con chào chú Mỹ,
Con thấy chú phân tích cũng rất logic về mục xuất ạ (rất có thể là như vậy rồi chú).
Nếu đúng như vậy chắc là chú Mỹ đã xiêu rồi nhỉ,lúc nào chú có thời gian và hứng chú ra tay chú nhé.
Con cảm ơn chú ạ.

 

[ptm0412]

Con thấy chú phân tích cũng rất logic về mục xuất ạ (rất có thể là như vậy rồi chú).
Nếu đúng như vậy chắc là chú Mỹ đã xiêu rồi nhỉ

Phân tích: Không phải "rất có thể như vậy" mà là "chắc chắn như vậy". Tôi đã từng thiết kế bảng dữ liệu với cột Loại như trên để chỉ dùng 1 bảng cho 3 giao diện nhập liệu của 3 loại chứng từ nhập xuất chuyển. Tuy nhiên tôi không dùng 1, 2, 3 không có tính minh hoạ, mà dùng N, X, C hoặc I, O, T (Input, Output và Transfer)
Cấu trúc trong file mới chỉ dùng cho báo cáo kho của thủ kho, chưa dùng được cho báo cáo kho của kế toán, cũng như chưa liên kết được kế toán: công nợ, giá thành, chi phí. (hoặc đã có nhưng chưa lấy ra hết)

Xiêu: Còn khuya mới xiêu. Dữ liệu phải chuẩn mới nói đến chuyện xem xét tiếp hay không, chuẩn rồi mới xem xét có xiêu không.
- Bảng tồn đầu kỳ phải có tính liên tục và có tính kế thừa (giải thích rồi, không giải thích thêm)
- Bảng dữ liệu nhập xuất phải liên tục và đầy đủ, chuyển kho phải có thông tin kho nhận.

Công thức Sheet kết quả dù đúng dù sai cũng bỏ. Lý do số lượng dòng vừa dư vừa thiếu