Sao kê ngân hàng tải về: nhiều dòng đầu, số âm trong ngoặc, ngày dạng chữ
Đầu bài
File sao kê tải từ ngân hàng: 8 dòng đầu là tiêu đề và logo, cột số tiền ghi (1.500.000) cho khoản chi, ngày là chữ, có dòng tổng chen giữa. Dán vào là mọi hàm tính đều ra 0.
Các bước làm
- 1
Cắt bỏ phần đầu và phần cuối trước
Xoá dòng logo, dòng tiêu đề gộp, dòng tổng cuối. Dòng tiêu đề THẬT phải là dòng đầu tiên của bảng dữ liệu — không xong bước này thì Ctrl+T và Pivot đều hỏng.
- 2
Bắt dòng tổng chen giữa
Ngân hàng hay chèn dòng cộng theo ngày. Lọc bằng =IF(ô_ngày="", "dòng tổng", "") hoặc lọc theo nội dung chứa "TOTAL", xoá hết trước khi cộng — không thì doanh số bị đếm hai lần.
- 3
Số âm trong ngoặc thành số thật
=IF(LEFT(TRIM(ô),1)="(", -VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ô,"(",""),")",""),".","")), VALUE(SUBSTITUTE(ô,".",""))). Kiểm bằng =COUNT(cột) phải bằng số dòng.
- 4
Ngày dạng chữ về ngày thật
Chọn cột → Data → Text to Columns → Finish thường xử lý được ngay. Không được thì =DATE(RIGHT(ô,4), MID(ô,4,2), LEFT(ô,2)). Ngày thật thì căn PHẢI, đó là cách kiểm.
- 5
Tách nội dung ra cột dùng được
Nội dung chuyển khoản thường chứa mã khách hoặc số hoá đơn. Ctrl+E (Flash Fill) gõ mẫu 2-3 dòng là Excel tự bóc phần còn lại; không đều thì dùng TEXTBEFORE/TEXTAFTER.
- 6
Đóng thành Table và chốt tổng
Ctrl+T, rồi so tổng phát sinh Nợ trừ Có với chênh lệch số dư đầu – cuối trên chính sao kê. Bằng nhau mới là bóc đúng.
- 7
Ghi lại các bước để tháng sau khỏi mò
Cùng ngân hàng thì file tháng nào cũng lỗi y hệt. Để một sheet ghi thứ tự thao tác, hoặc làm bằng Power Query một lần rồi tháng sau chỉ Alt+F5.
Phím dùng trong bài
Bẫy hay dính
Cột số tiền sau khi sửa vẫn căn TRÁI nghĩa là còn dạng chữ, dù nhìn đã ra con số bình thường. =SUM(cột) ra 0 mà không báo lỗi gì. Luôn kiểm bằng =COUNT(vùng) chứ đừng tin mắt.
Cùng nhóm ngân hàng, thanh toán & ngoại tệ
- Đối chiếu sao kê ngân hàng với sổ tiền gửi, tìm khoản treo hai bênSố dư sao kê 4,182 tỷ, sổ 112 ghi 4,205 tỷ. Lệch 23 triệu. Trong tháng có hơn 400 giao dịch, phải tìm ra khoản nào ngân hàng đã ghi mà mình chưa, khoản nào mình ghi mà ngân hàng chưa về.
- Đánh giá lại số dư gốc ngoại tệ cuối kỳ và chênh lệch tỷ giáCuối năm có số dư USD ở tiền gửi, phải thu và phải trả. Chuẩn mực bắt đánh giá lại theo tỷ giá cuối kỳ, mà tỷ giá dùng cho tài sản khác tỷ giá dùng cho nợ phải trả. Làm sai là kiểm toán trả về.
- Sổ ngoại tệ: tỷ giá lúc thu, tỷ giá lúc chi và lãi lỗ tỷ giá đã thực hiệnTài khoản USD có tiền vào ra suốt năm, mỗi lần một tỷ giá. Khi chi ra phải ghi giảm theo tỷ giá GHI SỔ của số ngoại tệ đó, chênh với tỷ giá thực tế là lãi lỗ. Phần mềm tính bình quân, mình cần kiểm lại.
- Lịch thanh toán nhà cung cấp: hạn tín dụng và chiết khấu thanh toán sớmCó 90 hoá đơn phải trả, mỗi nhà cung cấp một hạn tín dụng, vài bên cho chiết khấu nếu trả trong 10 ngày. Tiền có hạn, cần biết tuần này trả cái nào để vừa không quá hạn vừa ăn được chiết khấu.