Thực hành Power query sử dụng M-code căn bản trong kế toán

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Ứng dụng bài số 9 chủ đề Hàm và ngôn ngữ M trong Power query, với file dữ liệu kế toán chuẩn của năm 2008, hãy xem bài thực hành lập sổ quỹ vừa tiền mặt, vừa ngân hàng trong 1 sheet
Sử dụng 2 name ở ô H2 và E3 với H2 chọn giữa tài khoản 111 và 112, E3 chọn tháng, sau đó nhấn refresh
M-Code:
PHP:
let
    MonthRep= Excel.CurrentWorkbook(){[Name="MonthQuy"]}[Content]{0}[Column1] ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKQuy"]}[Content]{0}[Column1] ,
    Source = Excel.Workbook(File.Contents("E:\Data\ThanhMy\MY BOOK\MCode-PowerQuery\Data-Exercise1.xlsx"), null, true),
    Data_DefinedName = Source{[Item="Data",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"loaict", type text}, {"sct", type text},
         {"diengiai", type text}, {"TkNo", type text}, {"TKCo", type text}, {"Stien", Int64.Type}, {"Serie", type text}, 
         {"hoadon", Int64.Type}, {"ngaygoc", type date}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text}, 
         {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([TkNo] = AcctRep or [TKCo] = AcctRep)and Date.Month([date]) = MonthRep),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "TKDU", each if [TkNo] = AcctRep then [TKCo] else [TkNo]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Thu", each if [TkNo] = AcctRep then [Stien] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Chi", each if [TKCo]=AcctRep then [Stien] else 0),
   
    #"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"loaict", "diengiai", "TkNo", "TKCo", "Stien", "Serie", "hoadon", "ngaygoc", "Msthue", "Mskh", "HD", "LoaiHD", "VATRate", "MaCP"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "sct", "date", "TenKH", "Noidung", "TKDU", "Thu", "Chi"})
in
    #"Reordered Columns"
1605446425694.png
1605443842100.png

M-code
 

File đính kèm

Lần chỉnh sửa cuối:

mihhoang

Thành viên mới
Tham gia ngày
8 Tháng năm 2014
Bài viết
1
Được thích
0
Điểm
363
Em vừa đọc xong M-code căn bản thì có luôn phần thực hành. Thanks Anh rất nhiều!
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài tập 2 cũng ứng dụng name trên sheet để làm biến cho M-code, sử dụng như parameter của query
Kết quả là sổ chi tiết tài khoản như trong file, dữ liệu vẫn là file Data-Exercise1.xlsx ở bài 1
Có 3 name được sử dụng: Ngày bắt đầu, ngày kết thúc của báo cáo, và tài khoản kế toán cần báo cáo.
Chọn tài khoản ở E1, sửa ngày bắt đầu và kết thúc ở E2 và G2

M Code:
PHP:
let
    DateFrom= Date.From( Excel.CurrentWorkbook(){[Name="DFrom"]}[Content]{0}[Column1]) ,
    DateTo= Date.From(Excel.CurrentWorkbook(){[Name="DTo"]}[Content]{0}[Column1]) ,
    AcctRep = Excel.CurrentWorkbook(){[Name="TKCT"]}[Content]{0}[Column1] ,
    Source = Excel.Workbook(File.Contents("E:\Data\ThanhMy\MY BOOK\MCode-PowerQuery\Data-Exercise1.xlsx"), null, true),
    Data_DefinedName = Source{[Item="Data",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"loaict", type text}, {"sct", type text},
         {"diengiai", type text}, {"TkNo", type text}, {"TKCo", type text}, {"Stien", Int64.Type}, {"Serie", type text},
         {"hoadon", Int64.Type}, {"ngaygoc", type date}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text},
         {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([TkNo] = AcctRep or [TKCo] = AcctRep)and [date]>= DateFrom and [date]<=DateTo),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "TKDU", each if [TkNo] = AcctRep then [TKCo] else [TkNo]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "StienNo", each if [TkNo] = AcctRep then [Stien] else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "StienCo", each if [TKCo]=AcctRep then [Stien] else 0),

    #"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"loaict", "diengiai","TenKH", "TkNo", "TKCo", "Stien", "Serie", "hoadon", "ngaygoc", "Msthue", "Mskh", "HD", "LoaiHD", "VATRate", "MaCP"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "sct", "date",  "Noidung", "TKDU", "StienNo", "StienCo"})
in
    #"Reordered Columns"
1605525533538.png

1605525548380.png
 

File đính kèm

Lần chỉnh sửa cuối:

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Đặc điểm và lưu ý của 2 bài thực hành Sổ quỹ và Sổ chi tiết tài khoản:
1. Sử dụng name của sheet làm tham số lọc cho query
Tuỳ tình huống cần báo cáo sẽ đặt những name trên sheet để lọc theo yêu cầu: Lọc theo tháng, lọc trong khoảng thời gian
- Nếu muốn 1 query cho 2 báo cáo khác nhau có cùng cấu trúc (quỹ tiền mặt và quỹ ngân hàng) thì tạo 1 tuỳ chọn trên sheet để đặt biến (như từng làm với VBA, ADO, ...). Có thể dùng combobox, validation list, ... nhưng nên dùng combobox của form thay vì combobox của activeX controls, lý do là linkedCell của nó cho giá trị số dễ sử dụng trong điều kiện if.
- Nếu 1 query cho 1 báo cáo nhiều tài khoản như Sổ chi tiết tài khoản, và nếu sử dụng combobox để chọn tài khoản thì lại dùng combobox của activeX control. Lý do là linked cell sẽ chứa 1 giá trị của trường dữ liệu chứ không phải số 1, 2, 3, ... Khi so sánh để lọc trong query sẽ so sánh được trực tiếp với giá trị của name.

2. Sử dụng phát biểu cấu trúc if ... then ... else
Để ý cách dùng if để gộp cột như cột tài khoản đối ứng, hoặc dùng if để tách cột như cột số tiền thành tiền thu/chi, phát sinh nợ/ có

3. Các bước cần thực hiện kế tiếp nhau
- Các bước lọc các cột khác nhau nên thực hiện liên tiếp nhau, nếu làm bằng tay sẽ chỉ sinh ra 1 step, nếu viết bằng MCode dùng and và or cũng chỉ sinh ra 1 step
- Các bước tạo custom column cũng nên thực hiện liên tiếp nhau, để M-code được liền mạch. Trừ cột index

4. Dùng index column đánh số thứ tự
Lưu ý chỉ insert index column phía sau các step khác, trừ step reorder column (sắp thứ tự cột). Lý do đánh thứ tự xong rồi mới lọc, hoặc sắp xếp, thì cột Index sẽ bị mất số và/ hoặc mất thứ tự (đảo lộn thứ tự)

5. Xoá cột thừa và sắp thứ tự cột
Hai bước này nên thực hiện mỗi bước chỉ 1 lần vào cuối trước khi kết thúc M-code: Xoá trước, và kết thúc M-code bằng sắp xếp cột.
 
Lần chỉnh sửa cuối:

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài thực hành 3: Sổ chi tiết công nợ
Vui lòng tải lại file Data Data-Exercise1.xlsx trong bài này do có thay đổi về danh mục đối tác (thêm cột phân loại tài khoản công nợ)
Chọn loại công nợ khách hàng hay nhà cung cấp ô E1, chọn 1 đối tác trong combobox, chọn ngày bắt đầu và kết thúc của báo cáo.
Bài này có 2 query cần thực hiện:
- Query DMDT lọc danh mục khách hàng theo name TKCN (131/ 331) chỉ lấy 2 cột làm source cho combobox, do mong muốn combobox không chọn nhầm khách hàng khi muốn xem chi tiết công nợ nhà cung cấp và ngược lại
- Query ChitietCN lọc theo mã đối tác là name MaDT, theo ngày và theo tài khoản kế toán. Lý do vẫn phải lọc theo tài khoản kế toán là rạch ròi công nợ mua/ bán của cùng 1 đối tác, hoặc bán/ cho vay của cùng 1 đối tác

Do có 2 query nên
- nếu chỉ thay đổi đối tác, thì refresh query báo cáo
- nếu có thay đổi tài khoản công nợ, thì phải refresh danh mục trong sheet DM để chọn lại đối tác đúng loại trong combobox, sau đó mới refresh query báo cáo
Các lưu ý cho query không có gì mới so với bài trên


1605625107396.png

1605625309911.png
 

File đính kèm

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài thực hành 4: Chi tiết nhập xuất vật tư hàng hoá
Tải file Data ở dưới đây do có thêm cột mã VT, số lượng và đơn giá nhập xuất
Cũng tương tự bài thực hành 3, bài này sử dụng 2 query:
- query DMVT (danh mục vật tư/ hàng hoá), lọc theo tài khoản vật tư. Query này dùng làm dữ liệu nguồn cho combobox chọn mặt hàng
- Query báo cáo ChiTietVT lọc theo mặt hàng, ngày bắt đầu báo cáo, ngày kết thúc của báo cáo
Lưu ý khi thay đổi tài khoản kế toán của vật tư hàng hoá, phải refresh trước DMVT để có thể chọn đúng mặt hàng, sau đó mới refresh ChiTietVT
Lưu ý thêm:
Để ý các bước tạo 4 cột tách ra từ 2 cột số lượng & thành tiền, khi tự viết M-code sẽ đặt tên bước theo ý mình chứ không bị theo dạng custom1, custom2, ...
M-Code:
1605712175984.png

Các bước có tên theo ý mình:

1605712233514.png

Báo cáo:

1605712269422.png
 

File đính kèm

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài thực hành 5: Bảng kê hoá đơn mua vào, bán ra
1. Sử dụng trong bài này:
- Biến InOut lấy từ name, có 2 giá trị là 1 và 2 chia cho loại mua vào hoặc bán ra, kết quả của việc chọn combobox loại form controls
- Cấu trúc 2 if then else lồng nhau: Nếu InOut =1 hoặc = 2 thì dựa vào TKNo/ TKco là bao nhiêu để lấy tiền hàng, tiền thuế tách ra 2 cột
- Sau khi tiền hàng tiền thuế tách ra 2 cột nhưng vẫn nằm 2 dòng, thì cần GroupBy để gộp cứ 2 dòng thành 1.

2. Lưu ý cho bài này
- Khi GroupBy phải lưu ý rằng tất cả các trường dùng để group phải có giá trị giống nhau từng cặp một theo mỗi hoá đơn, nhất là trường diễn giải. Do đó các cột thừa phải được xoá trước khi Group.
- Step add index cũng phải thực hiện sau cuối, sau cả groupBy và sau sort để số thứ tự không ngắt quãng và không đảo lộn.

1605793206707.png
 

File đính kèm

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài tập 6: Quản trị chi phí
Theo dõi thấy càng các bài về sau càng ít người đọc và càng ít lượt tải file, mặc dù các câu M-code thuộc dạng căn bản làm tay từng bước và sửa chút đỉnh, chưa đụng đến function là thứ khó nhằn.
Không biết là tại khó hay tại dở, nên tôi viết bài này xong rồi tạm thời ngưng, và bài này không đưa file lên nữa mà chỉ ghi chú cách làm.

1. Yêu cầu
Từ dữ liệu ở file Data bài trên, tạo query ra bảng dữ liệu chi phí sao cho ra được báo cáo Pivot table như hình

2. Cách thực hiện
- Merge 2 table Data1 và DMCP để có tên chi phí
- Lọc tài khoản TkNo chỉ lấy tài khoản bắt đầu là "6"
- Xoá cột thừa

Nếu ai muốn làm mà có khó khăn muốn hỏi thì đừng ngại, hỏi ngay trong chủ đề này hay mở chủ đề mới đều được.

1605877638989.png

1605877655357.png
 

excel_lv1.5

Thành viên tích cực
Tham gia ngày
20 Tháng mười 2017
Bài viết
805
Được thích
1,399
Điểm
360
Bài tập 6: Quản trị chi phí
Theo dõi thấy càng các bài về sau càng ít người đọc và càng ít lượt tải file, mặc dù các câu M-code thuộc dạng căn bản làm tay từng bước và sửa chút đỉnh, chưa đụng đến function là thứ khó nhằn.
Không biết là tại khó hay tại dở, nên tôi viết bài này xong rồi tạm thời ngưng, và bài này không đưa file lên nữa mà chỉ ghi chú cách làm.

1. Yêu cầu
Từ dữ liệu ở file Data bài trên, tạo query ra bảng dữ liệu chi phí sao cho ra được báo cáo Pivot table như hình

2. Cách thực hiện
- Merge 2 table Data1 và DMCP để có tên chi phí
- Lọc tài khoản TkNo chỉ lấy tài khoản bắt đầu là "6"
- Xoá cột thừa

Nếu ai muốn làm mà có khó khăn muốn hỏi thì đừng ngại, hỏi ngay trong chủ đề này hay mở chủ đề mới đều được.

View attachment 249740

View attachment 249741
Tôi cũng muốn tham gia vào nhưng mấy bài của bạn thuộc cơ bản tính chờ bạn đưa các vấn đề cao hơn tý rồi thảo luận, thôi để tôi đưa các vấn đề cùng thảo luận:
- Bài này tôi gửi lại file kết quả Cách 1 sử dụng cách khác bạn một chút (đẩy từ Power query ra bảng 1), dùng power pivot ra bảng 2 nhưng không merge ở source gốc mà tạo relationship (về tốc độ Dax trong modeling nhanh hơn M rất nhiều do dùng cơ chế columnar storage, M vẫn xử lý từng phần tử trong mảng) và cách của bạn (Cách 2)

Tôi đưa thêm một vấn đề thảo luận: làm cách nào để filter một Table với một danh sách cho trước (filter chính xác, filter một phần hoặc filter bắt đầu với chuỗi...)
ví dụ: file Data của bạn tôi cần filter các tài khoản nợ là : 133,156,3338,3331,334,511,911,515,635,142,311 sử dụng M nhưng không dùng cách thủ công .. or..or..or... vì danh sách đó có thể tạo ra từ code không biết trước
 

File đính kèm

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
- Bài này tôi gửi lại file kết quả Cách 1 sử dụng cách khác bạn một chút (đẩy từ Power query ra bảng 1), dùng power pivot ra bảng 2 nhưng không merge ở source gốc mà tạo relationship (về tốc độ Dax trong modeling nhanh hơn M rất nhiều do dùng cơ chế columnar storage, M vẫn xử lý từng phần tử trong mảng)
Trước tiên xin gởi lời cám ơn bạn rất nhiều
Tôi biết cách này, trong tài liệu tôi cũng có viết về nó. Nhưng loạt bài này liên quan và ứng dụng các bài trong chủ đề M-code nên tôi dùng cách 2 và đề nghị người dùng làm cách 2
Tôi đưa thêm một vấn đề thảo luận: làm cách nào để filter một Table với một danh sách cho trước (filter chính xác, filter một phần hoặc filter bắt đầu với chuỗi...)
ví dụ: file Data của bạn tôi cần filter các tài khoản nợ là : 133,156,3338,3331,334,511,911,515,635,142,311 sử dụng M nhưng không dùng cách thủ công .. or..or..or... vì danh sách đó có thể tạo ra từ code không biết trước
Mời các bạn khác tham gia, cũng không khó lắm.
Trong khi chờ các bạn khác tham gia, tôi giải thích về dự định ban đầu khi mở chủ đề này:
- Tôi sẽ đưa các bài theo thứ tự từ dễ đến khó
- Mỗi bài sẽ có thêm những câu lệnh M mới hơn
- Bài kế tiếp (7, 8, 9) là 3 bài tổng hợp nhập xuất tồn, tổng hợp công nợ và bảng cân đối phát sinh. Ba bài này tôi đã chuẩn bị được 1, 2 bài còn lại cũng nhanh thôi.
- Các bài từ 1 đến 4 tôi đang để ngỏ 2 cột số dư cuối (cộng trừ dần phát sinh tăng/ giảm sau mỗi dòng) để làm cho phần khó hơn (sử dụng function hoặc DAX). Bạn @excel_lv1.5 có thể phụ tôi làm 4 bài đó không, vì tôi chưa chuẩn bị?
 
Lần chỉnh sửa cuối:

excel_lv1.5

Thành viên tích cực
Tham gia ngày
20 Tháng mười 2017
Bài viết
805
Được thích
1,399
Điểm
360
Trước tiên xin gởi lời cám ơn bạn rất nhiều
Tôi biết cách này, trong tài liệu tôi cũng có viết về nó. Nhưng loạt bài này liên quan và ứng dụng các bài trong chủ đề M-code nên tôi dùng cách 2 và đề nghị người dùng làm cách 2

Mời các bạn khác tham gia, cũng không khó lắm.
Trong khi chờ các bạn khác tham gia, tôi giải thích về dự định ban đầu khi mở chủ đề này:
- Tôi sẽ đưa các bài theo thứ tự từ dễ đến khó
- Mỗi bài sẽ có thêm những câu lệnh M mới hơn
- Bài kế tiếp (7, 8, 9) là 3 bài tổng hợp nhập xuất tồn, tổng hợp công nợ và bảng cân đối phát sinh. Ba bài này tôi đã chuẩn bị được 1, 2 bài còn lại cũng nhanh thôi.
- Các bài từ 1 đến 4 tôi đang để ngỏ 2 cột số dư cuối (cộng trừ dần phát sinh tăng/ giảm sau mỗi dòng) để làm cho phần khó hơn (sử dụng function hoặc DAX). Bạn @excel_lv1.5 có thể phụ tôi làm 4 bài đó không, vì tôi chưa chuẩn bị?
Chưa hiểu cách tính, ví dụ bài #1, bạn cho tôi mẫu kết quả số dư sổ quỹ được tính như thế nào
1605884449075.png
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Chưa hiểu cách tính, ví dụ bài #1, bạn cho tôi mẫu kết quả số dư sổ quỹ được tính như thế nào
Tôi gởi bạn file cách tính, diễn giải như sau:
1. Tồn quỹ tiền mặt & ngân hàng
- Tồn quỹ chỉ 1 bên nợ, kết quả âm là lỗi thu chi của kế toán, không quan tâm
- Tồn đầu kỳ (đầu ngày 1 của tháng báo cáo): Bằng số dư đầu năm 111/112 trong query SDTK5 + tổng PS nợ (thu) trước tháng báo cáo - tổng PS có (chi) trước tháng báo cáo, đã tạo query SoduDKTK (theo tháng)
- Cột tồn của query báo cáo: tồn quỹ sau khi thu chi = tồn trước thu chi + thu - chi (đầu ngày 1 là cách tính trên) (công thức trong file)
2. Số cái chi tiết
- Có thể dư nợ hoặc có, và chỉ dư 1 trong 2 bên
- Dư đầu kỳ nợ bằng dư nợ cuối ngày trước của start date báo cáo = dư nợ đầu năm - dư có đầu năm + PS nợ trước kỳ báo cáo - PS có trước kỳ BC, nếu âm thì trả về 0
- Dư đầu kỳ có bằng dư có cuối ngày trước của start date báo cáo = dư có đầu năm - dư nợ đầu năm + PS có trước kỳ báo cáo - PS nợ trước kỳ BC, nếu âm thì trả về 0
- Cột dư nợ và dư có của query báo cáo: như công thức trong file
3. Chi tiết công nợ
Tương tự sổ cái chi tiết nhưng chi tiết là đối tượng công nợ (khách hàng, nhà cung cấp) chứ không phải tài khoản kế toán
Số dư công nợ đầu năm là query SDCN3
4. Chi tiết vật tư hàng hoá
Tương tự sổ quỹ nhưng 2 cột tồn số lượng và tồn thành tiền. Âm là lỗi của kho chưa nhập đã xuất, không quan tâm
Số dư đầu năm là query SDVT7


Xin cám ơn bạn trước
 

File đính kèm

Lần chỉnh sửa cuối:

excel_lv1.5

Thành viên tích cực
Tham gia ngày
20 Tháng mười 2017
Bài viết
805
Được thích
1,399
Điểm
360
Tôi gởi bạn file cách tính, diễn giải như sau:
1. Tồn quỹ tiền mặt & ngân hàng
- Tồn quỹ chỉ 1 bên nợ, kết quả âm là lỗi thu chi của kế toán, không quan tâm
- Tồn đầu kỳ (đầu ngày 1 của tháng báo cáo): Bằng số dư đầu năm 111/112 trong query SDTK5 + tổng PS nợ (thu) trước tháng báo cáo - tổng PS có (chi) trước tháng báo cáo, đã tạo query SoduDKTK (theo tháng)
- Cột tồn của query báo cáo: tồn quỹ sau khi thu chi = tồn trước thu chi + thu - chi (đầu ngày 1 là cách tính trên) (công thức trong file)
2. Số cái chi tiết
- Có thể dư nợ hoặc có, và chỉ dư 1 trong 2 bên
- Dư đầu kỳ nợ bằng dư nợ cuối ngày trước của start date báo cáo = dư nợ đầu năm - dư có đầu năm + PS nợ trong kỳ báo cáo - PS có trong kỳ BC, nếu âm thì trả về 0
- Dư đầu kỳ có bằng dư có cuối ngày trước của start date báo cáo = dư có đầu năm - dư nợ đầu năm + PS có trong kỳ báo cáo - PS nợ trong kỳ BC, nếu âm thì trả về 0
- Cột dư nợ và dư có của query báo cáo: như công thức trong file
3. Chi tiết công nợ
Tương tự sổ cái chi tiết nhưng chi tiết là đối tượng công nợ (khách hàng, nhà cung cấp) chứ không phải tài khoản kế toán
Số dư công nợ đầu năm là query SDCN3
4. Chi tiết vật tư hàng hoá
Tương tự sổ quỹ nhưng 2 cột tồn số lượng và tồn thành tiền. Âm là lỗi của kho chưa nhập đã xuất, không quan tâm
Số dư đầu năm là query SDVT7


Xin cám ơn bạn trước
Tôi làm cái sổ quỹ mấy cái kia chắc làm tương tự,
- Tạo function tính số dư đầu kì cho mỗi giai đoạn
- Không xét yếu tố năm do dữ liệu chỉ có 1 năm, có thể tùy biến lại nếu có nhiều năm
Bạn nên đưa Data Source lên Cloud như Google drive, Onedrive, Sharepoint... shared rồi connect Data qua web, như vậy khỏi gửi file data source, chỉ cần gửi file connect rồi refresh bình thườngng ,bạn gửi cả 2 lên người đọc không biết hay chỉnh source lại trong PQ rất mất thời gian. File tôi gửi lại Data source tôi để trên Google Drive.
 

File đính kèm

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Tôi làm cái sổ quỹ mấy cái kia chắc làm tương tự,
- Tạo function tính số dư đầu kì cho mỗi giai đoạn
- Không xét yếu tố năm do dữ liệu chỉ có 1 năm, có thể tùy biến lại nếu có nhiều năm
Bạn nên đưa Data Source lên Cloud như Google drive, Onedrive, Sharepoint... shared rồi connect Data qua web, như vậy khỏi gửi file data source, chỉ cần gửi file connect rồi refresh bình thườngng ,bạn gửi cả 2 lên người đọc không biết hay chỉnh source lại trong PQ rất mất thời gian. File tôi gửi lại Data source tôi để trên Google Drive.
Cám ơn bạn, còn lại bạn từ từ làm hoặc bận thì thôi cũng được.
Data source tôi dùng 1 file duy nhất cho tất cả query từ đầu về sau (có sai sót về 3 cột vật tư hàng hoá). Còn về sửa source trong PQ thì tôi coi như bài tập nhỏ giống như thí dụ 1 của bài dùng Name. Tuy nhiên bạn góp ý đúng hơn.
 

Hau151978

Thành viên tích cực
Tham gia ngày
19 Tháng mười 2011
Bài viết
1,375
Được thích
1,281
Điểm
560
Tôi đưa thêm một vấn đề thảo luận: làm cách nào để filter một Table với một danh sách cho trước (filter chính xác, filter một phần hoặc filter bắt đầu với chuỗi...)
ví dụ: file Data của bạn tôi cần filter các tài khoản nợ là : 133,156,3338,3331,334,511,911,515,635,142,311 sử dụng M nhưng không dùng cách thủ công .. or..or..or... vì danh sách đó có thể tạo ra từ code không biết trước
Em tạo 1 table điều kiện cần filter, sử dụng "wildcard" mức độ đơn giản chỉ xét ở cuối điều kiện, "?" đại diện cho 1 ký tự, "%" đại diện cho n ký tự (n>=0). Ví dụ 111% sẽ đại diện cho 111, 1111, 1112...
Mã:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"loaict", type text}, {"sct", type text}, {"diengiai", type text}, {"TkNo", type text}, {"TKCo", Int64.Type}, {"Stien", Int64.Type}, {"Serie", type text}, {"hoadon", Int64.Type}, {"ngaygoc", type datetime}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text}, {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}, {"MSVT", type text}, {"Sluong", type number}, {"Dgia", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(Table.ToList(Criteria),[TkNo],
                                        (x as text, y as text) => 
                                            if Text.End(x,1) ="%" 
                                            then Text.StartsWith(y, Text.Start(x,Text.Length(x)-1)) 
                                            else 
                                            if Text.End(x,1) ="?"
                                            then Text.Start(x,Text.Length(x)-1) = Text.Start(y,Text.Length(y)-1)
                                            else x=y))
in
    #"Filtered Rows"
 

File đính kèm

excel_lv1.5

Thành viên tích cực
Tham gia ngày
20 Tháng mười 2017
Bài viết
805
Được thích
1,399
Điểm
360
Em tạo 1 table điều kiện cần filter, sử dụng "wildcard" mức độ đơn giản chỉ xét ở cuối điều kiện, "?" đại diện cho 1 ký tự, "%" đại diện cho n ký tự (n>=0). Ví dụ 111% sẽ đại diện cho 111, 1111, 1112...
Mã:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"loaict", type text}, {"sct", type text}, {"diengiai", type text}, {"TkNo", type text}, {"TKCo", Int64.Type}, {"Stien", Int64.Type}, {"Serie", type text}, {"hoadon", Int64.Type}, {"ngaygoc", type datetime}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text}, {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}, {"MSVT", type text}, {"Sluong", type number}, {"Dgia", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(Table.ToList(Criteria),[TkNo],
                                        (x as text, y as text) =>
                                            if Text.End(x,1) ="%"
                                            then Text.StartsWith(y, Text.Start(x,Text.Length(x)-1))
                                            else
                                            if Text.End(x,1) ="?"
                                            then Text.Start(x,Text.Length(x)-1) = Text.Start(y,Text.Length(y)-1)
                                            else x=y))
in
    #"Filtered Rows"
Cám ơn bạn, ý tôi là muốn tính làm riêng từng trường hợp, chứ không gộp chung như vậy, gộp chung thì không bao quát hết tất cả trường hợp, một cách viết gộp chung:
Mã:
let
    List_fileter={"112","33","111"},
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"loaict", type text}, {"sct", type text}, {"diengiai", type text}, {"TkNo", type text}, {"TKCo", Int64.Type}, {"Stien", Int64.Type}, {"Serie", type text}, {"hoadon", Int64.Type}, {"ngaygoc", type datetime}, {"Msthue", type text}, {"Mskh", type text}, {"TenKH", type text}, {"HD", type logical}, {"LoaiHD", type text}, {"VATRate", Int64.Type}, {"Noidung", type text}, {"MaCP", type text}, {"MSVT", type text}, {"Sluong", type number}, {"Dgia", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(List.Transform(List_fileter,(txt)=>Text.Contains([TkNo],txt)),true))
in
    #"Filtered Rows"
- Nếu cần filter chính xác thì cần List.Contains(List_fileter,[TkNo])
- Nếu cần filter một phần thì cần thì làm như code trên
- Nếu cần filter bắt đầu List.Contains(List.Transform(List_fileter,(txt)=> Text.StartsWith([TkNo],txt)),true))

Đặt vấn đề khác: M không có hàm countif, hãy tạo ra hàm countif và countifs (hàm countifs cho phép đếm nhiều điều kiện)
Bài đã được tự động gộp:

Cám ơn bạn, còn lại bạn từ từ làm hoặc bận thì thôi cũng được.
Data source tôi dùng 1 file duy nhất cho tất cả query từ đầu về sau (có sai sót về 3 cột vật tư hàng hoá). Còn về sửa source trong PQ thì tôi coi như bài tập nhỏ giống như thí dụ 1 của bài dùng Name. Tuy nhiên bạn góp ý đúng hơn.
Tôi gửi phần làm 4 trường hợp, các trường hợp gần như tương tự nhau
Tôi chuyển Data source sang Onedrive, do google drive bị giới hạn request
 

File đính kèm

Lần chỉnh sửa cuối:

Hau151978

Thành viên tích cực
Tham gia ngày
19 Tháng mười 2011
Bài viết
1,375
Được thích
1,281
Điểm
560
Hàm CountIf khó xét hết trường hợp lắm bác. Các dấu <, >, <>, <=, >=, = rồi lại wild card nữa...
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
- Nếu cần filter chính xác thì cần List.Contains(List_fileter,[TkNo])
- Nếu cần filter một phần thì cần thì làm như code trên
- Nếu cần filter bắt đầu List.Contains(List.Transform(List_fileter,(txt)=> Text.StartsWith([TkNo],txt)),true))
Trường hợp dễ hơn và có thể tuỳ chỉnh dễ dàng trên sheet là có 1 list các mục cần lọc (kiểu chính xác), và list này có thể thay đổi tuỳ ý người dùng. Cách áp dụng cho trường hợp này là tạo list trên sheet, tạo riêng query cho nó và merge inner join với query chính
Tôi gửi phần làm 4 trường hợp, các trường hợp gần như tương tự nhau
Tôi chuyển Data source sang Onedrive, do google drive bị giới hạn request
Cám ơn bạn. Tôi đang nghiền ngẫm bài #13 của bạn và đang viết giải thích cho các hàm đã dùng trong bài đó, và cách đưa hàm tự tạo vào query. Tài liệu hàm M tôi viết năm ngoái chưa có.
 

ptm0412

Bad Excel Member
Thành viên BQT
Super Moderator
Tham gia ngày
4 Tháng mười một 2007
Bài viết
10,029
Được thích
29,958
Điểm
1,910
Tuổi
58
Nơi ở
Gò Vấp
Bài thực hành 7: Bảng cân đối phát sinh
File đính kèm liên kết đến OneDrive nên không cần sửa nguồn, theo góp ý của bạn @excel_lv1.5. Tuy nhiên là link mới chứ không dùng link của @excel_lv1.5 do file đó bị trùng name và name lỗi (worksheet name) nhiều, có thể do copy sheet.

Bài này thực hiện gồm các bước:
1. Tính số dư đầu kỳ của tháng báo cáo
Trong dữ liệu chỉ có số dư đầu năm lấy từ số dư 31/12/2007 chuyển sang làm số dư 2008. Nếu tạo bảng cân đối phát sinh tháng 3 thì phải tính được số dư cuối tháng 2 làm số ư đầu tháng 3
- Lấy phát sinh nợ trước kỳ báo cáo: Previous PS nợ (trước ngày đầu tháng hoặc tháng nhỏ hơn tháng báo cáo)

1606141345478.png

- Lấy phát sinh có trước kỳ báo cáo: Previous PS có

1606141417071.png

- Lập cân đối phát sinh từ 01/01/2008 đến hết tháng trước tháng báo cáo, merge 3 bảng số dư đầu năm và 2 bảng trên. loại join là left join, xoá cột chỉ lấy số dư cuối

1606141492679.png

2. Lập cân đối phát sinh kỳ báo cáo:
Tương tự như bước trên nhưng thay vì tháng nhỏ hơn kỳ báo cáo thì bằng kỳ báo cáo.
- Lấy phát sinh nợ trong kỳ (current PS nợ)
- Lấy phát sinh có trong kỳ (current PS có)
- Dùng số dư cuối của bảng cân đối mục 1 làm đầu kỳ báo cáo, join với 2 bảng phát sinh trên
Kết quả:

1606141812371.png

3. Ghi chú:
Có sử dụng hàm Table.SelectColumn để chỉ lấy các cột cần lấy, khỏi mất công xoá. Cú pháp cũng đơn giản, viết tay được.
Có sử dụng hàm Table.SelectRows thay cho Filter vì dễ viết hơn.
Sử dụng Left join để lấy hết các tài khoản trong danh mục dù có số dư đầu kỳ hay không, hoặc có phát sinh hay không, theo quy định của báo cáo.
 

File đính kèm

Lần chỉnh sửa cuối:
Top Bottom