Tách hoá đơn bán ra theo thuế suất 0%, 5%, 8%, 10% để soát tờ khai thuế GTGT bằng Excel

ExcelCơ bản Video 1:51

Bài toán

Danh sách hoá đơn bán ra tải từ cổng thuế chỉ có tiền trước thuế và tiền thuế, không có cột thuế suất; muốn soát tờ khai theo từng mức thuế lại ngồi chia từng dòng, giữ Ctrl click cộng, click hụt là chọn lại từ đầu.

Làm xong được gì

Bảng doanh thu và tiền thuế theo bốn mức 0%, 5%, 8%, 10%; tờ gộp hai thuế suất tự tô màu, dòng lệch so với danh sách đúng bằng tiền mấy tờ đó.

Dùng trong bài

ROUNDSUMIFSConditional Formatting (công thức COUNTIF)

File thực hành gồm

HoaDonBanRa_T9.xlsx
Sheet Bán ra T9, danh sách 62 hoá đơn bán ra tháng 9 (dòng 8 tới 69) đúng hình dạng danh sách tải về: tên cột hai dòng (6 và 7), không có cột thuế suất. Cột J để trống sẵn, các cột không dùng (K tới T) đã ẩn, bên phải kẻ sẵn bảng bốn mức thuế suất ở V6:X12.
HoaDonBanRa_T9_dap_an.xlsx
Bản đã gắn công thức và tô màu, mở ra để so kết quả.

Các bước làm

1
Đặt tên cột Thuế suất
Bấm ô J6 (ô gộp J6:J7, cột trống ngay sau cột tiền thuế), gõ Thuế suất rồi Enter. Con trỏ xuống ô J8.
2
Tính thuế suất từng tờ bằng ROUND
Ô J8 gõ công thức dưới đây: tiền thuế chia tiền chưa thuế, làm tròn hai số lẻ. Cột đã định dạng sẵn phần trăm nên ô hiện 8%. Bấm lại J8 rồi nháy đúp nút fill (ô vuông nhỏ ở góc dưới phải), công thức chạy tới J69.
=ROUND(I8/H8,2)
3
Cộng tiền chưa thuế theo từng mức
Sang bảng bốn mức kẻ sẵn. Ô W8 gõ SUMIFS: cộng cột tiền chưa thuế, điều kiện là cột thuế suất bằng mức ở đầu dòng. Enter, bấm lại W8 rồi kéo nút fill xuống W11.
=SUMIFS(H:H,J:J,V8)
4
Cộng tiền thuế theo từng mức
Ô X8 làm y hệt, chỉ đổi cột H thành cột I, rồi kéo xuống X11. Bảng nhỏ giờ có đủ tiền hàng và tiền thuế của bốn mức, riêng mức 8% khoảng 3,35 tỷ tiền hàng.
=SUMIFS(I:I,J:J,V8)
5
Dòng lệch so với danh sách
Ô W12 lấy tổng cả danh sách trừ bốn mức. Bôi W12:X12 rồi bấm Ctrl+R để chép sang cột tiền thuế. W12 ra 239.125.000: còn tờ nằm ngoài bốn mức.
=SUM(H:H)-SUM(W8:W11)
6
Tô màu tờ nằm ngoài bốn mức
Gõ A8:J69 vào Name Box rồi Enter để chọn cả danh sách. Vào Home › Conditional Formatting › New Rule, chọn Use a formula to determine which cells to format, gõ công thức dưới đây. Bấm Format, sang mục Fill chọn màu đỏ nhạt, OK hai lần. COUNTIF đếm thuế suất của dòng trong bảng bốn mức, bằng 0 thì dòng đó được tô. Dòng 13 và 28 đỏ lên, thuế suất 9%: mỗi tờ gộp hàng 8% với hàng 10%.
=COUNTIF($V$8:$V$11,$J8)=0
7
Kiểm hai tờ gộp với dòng lệch
Bấm ô H13, giữ Ctrl bấm thêm H28. Số Sum ở thanh trạng thái là 239.125.000, bằng đúng ô W12. Mở bản chi tiết của hai tờ này ra tách riêng phần 8% và phần 10% là đủ số để dò tờ khai.

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

Hàng không chịu thuế (KCT) cũng ra tỉ lệ 0 như hàng 0%: tách riêng trước khi cộng.
Tờ gộp nhiều thuế suất: mở danh sách hoá đơn chi tiết (từng dòng hàng) để tách.

Các hàm trong bài có từ Excel 2010: Excel 2016, 2019, 2021, 365 đều làm được y hệt.

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