EzTech

File chi tiết hoá đơn từ cổng thuế: gom mỗi hoá đơn 1 dòng, tách thuế suất bằng Power Query

Power QueryNâng cao Video 3:19
File chi tiết hoá đơn từ cổng thuế: gom mỗi hoá đơn 1 dòng, tách thuế suất bằng Power Query
Video của bài này đang được cập nhật.

Bài toán

File chi tiết hoá đơn tải từ cổng thuế: một hoá đơn trải ra mấy dòng hàng, tổng thanh toán chỉ ở dòng đầu, thuế suất 8% chỗ ghi 0,08 chỗ ghi 8%; lọc 8% rồi cộng là sót dòng.

Làm xong được gì

Bảng kê mỗi hoá đơn một dòng, tiền hàng tách 8% · 10% · KCT, cột chênh với tổng thanh toán toàn 0; tháng sau thả file mới vào thư mục rồi Refresh All.

Dùng trong bài

From FolderTrimFill DownReplace ValuesPivot ColumnCustom Column

File thực hành gồm

  • KeToan\HoaDonMuaVao\Chi tiet HD mua vao T07-2026.xlsx, Chi tiet HD mua vao T08-2026.xlsx

    Hai file chi tiết hoá đơn mua vào tháng 7 và tháng 8 (10 và 11 hoá đơn, 17 và 16 dòng hàng). Sheet ChiTiet: 4 dòng đầu là tên danh sách, khoảng ngày, MST người mua và một dòng trống; dòng 5 là tên cột. Mỗi dòng hàng một dòng, hai cột tổng chỉ có số ở dòng đầu của hoá đơn, số hoá đơn dính dấu cách ở đầu, thuế suất 8% chỗ ghi 0.08, chỗ ghi 8%.

  • KeToan\HoaDonT09\Chi tiet HD mua vao T09-2026.xlsx

    File tháng 9, 12 hoá đơn, 19 dòng hàng. Dùng ở bước cuối.

  • KeToan\Bang ke HD mua vao 2026.xlsx

    Bảng kê đã dựng sẵn, mỗi hoá đơn một dòng. Mở ra bấm Data › Refresh All là chạy, dùng để so kết quả.

Giải nén gói file vào ổ C:\ để ra đúng thư mục C:\KeToan. Query trong Bang ke HD mua vao 2026.xlsx trỏ vào C:\KeToan\HoaDonMuaVao.

Các bước làm

  1. 1

    Lấy dữ liệu từ thư mục hoá đơn

    Mở một file Excel trắng. Vào Data › Get Data › From File › From Folder, dán C:\KeToan\HoaDonMuaVao rồi bấm Open. Ở hộp xem trước bấm mũi tên ở nút Combine, chọn Combine & Transform Data. Ở hộp Combine Files bấm sheet ChiTiet rồi OK.

  2. 2

    Dọn mấy dòng đầu trên file mẫu

    Trong Power Query Editor, ở khung Queries bấm Transform Sample File. Vào Home › Remove Rows › Remove Top Rows, gõ 3 rồi OK: lúc mở Excel đã tự đưa dòng đầu lên làm tiêu đề, còn 3 dòng nằm trên dòng tên cột. Bấm Home › Use First Row as Headers. Excel tự thêm bước Changed Type, bấm dấu X ở khung Applied Steps để xoá bước này.

  3. 3

    Sang bảng gộp, bỏ các cột không cần

    Ở khung Queries bấm query HoaDonMuaVao. Bước Changed Type cuối cùng, do Excel thêm lúc Combine, cũng bấm dấu X để xoá. Bấm tiêu đề cột Source.Name, giữ Shift bấm cột STT, bấm Delete. Bấm tiêu đề cột Tên hàng hóa, dịch vụ, giữ Shift bấm cột Đơn giá, bấm Delete. Bốn cột tên hàng, đơn vị, số lượng, đơn giá bị bỏ, bảng kê chỉ cần tiền.

  4. 4

    Trim cột số hoá đơn

    Bấm tiêu đề cột Số hóa đơn, vào Transform › Format › Trim. Dấu cách ở đầu số hoá đơn bị cắt.

  5. 5

    Fill Down hai cột tổng

    Bấm tiêu đề cột Tổng tiền thuế, giữ Shift bấm cột Tổng tiền thanh toán, vào Transform › Fill › Down. Dòng hàng nào của hoá đơn cũng có đủ hai số tổng.

  6. 6

    Gom thuế suất 8% về một kiểu ghi

    Bấm tiêu đề cột Thuế suất, bấm biểu tượng kiểu dữ liệu ở đầu tiêu đề, chọn Text. Chuột phải một ô ghi 0.08, chọn Replace Values. Ô Value To Find đã điền sẵn 0.08, ô Replace With gõ 8%, OK.

  7. 7

    Pivot Column theo thuế suất

    Vẫn chọn cột Thuế suất, vào Transform › Pivot Column. Ô Values Column chọn Thành tiền chưa thuế, phần Advanced options để Sum, OK. Nhờ Fill Down ở bước trước, các dòng của cùng một hoá đơn giống hệt nhau nên Pivot gom lại mỗi hoá đơn một dòng. Tiền hàng tách thành ba cột 8%, 10%, KCT.

  8. 8

    Thêm cột Chênh để tự kiểm

    Vào Add Column › Custom Column, đặt tên Chênh và gõ công thức dưới đây: tổng thanh toán trừ tiền thuế, trừ ba cột tiền hàng. Ba cột tiền hàng gói trong List.Sum để ô trống được tính là 0.

    [Tổng tiền thanh toán] - [Tổng tiền thuế] - List.Sum({[#"8%"], [#"10%"], [KCT]})
  9. 9

    Đưa bảng kê về Excel

    Bấm Home › Close & Load. Sheet mới HoaDonMuaVao có 21 dòng, mỗi dòng một hoá đơn của tháng 7 và tháng 8. Cột Chênh toàn số 0. Cột Tổng tiền thanh toán cộng lại 137.352.920. Cột 8% đã gom cả những dòng ghi 0.08.

  10. 10

    Tháng sau thả file và Refresh

    Kéo file Chi tiet HD mua vao T09-2026.xlsx từ thư mục HoaDonT09 sang thư mục HoaDonMuaVao. Về Excel bấm Data › Refresh All. Bảng kê thành 33 dòng, thêm 12 hoá đơn tháng 9. Tổng tiền thanh toán lên 201.728.440, cột Chênh vẫn bằng 0.

Lưu ý khi làm với file thật

  • Lọc tay trên file gốc theo thuế suất 8% thì sót các dòng ghi 0.08. Phải đổi cột sang Text và Replace Values trước khi Pivot.
  • Chưa Fill Down hai cột tổng mà Pivot ngay thì các dòng của cùng một hoá đơn khác nhau, không gom về một dòng được.

Excel 2019, 2021, 365 trên Windows có menu giống hệt trong bài. Excel 2016 cũng có Power Query nhưng menu hơi khác.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học
Chat ZaloGọi điệnMessengerTìm đường