Bắt hoá đơn đầu vào nhập trùng bằng Excel: tô đỏ ngay khi gõ

ExcelCơ bản Video 1:36

Bài toán

Một hoá đơn đầu vào nhập hai lần là kê khai trùng; tô trùng theo số hoá đơn thì đỏ nửa bảng, vì hai nhà cung cấp trùng số hoá đơn là chuyện thường.

Làm xong được gì

Chỉ hoá đơn trùng thật (cùng mã số thuế người bán, ký hiệu, số) mới đỏ, và đỏ ngay lúc gõ.

Dùng trong bài

TEXTTRIMConditional Formatting (Duplicate Values)

File thực hành gồm

BangKeMuaVao_T9.xlsx
Sheet Mua vào T9, bảng kê hoá đơn mua vào tháng 9 đã là Table (tên MuaVao, A6:I34), gồm 28 tờ: 25 tờ dán từ danh sách tải về và 3 tờ gõ tay từ email ở cuối bảng. Mã số thuế của các tờ tải về lưu thành số nên mất số 0 đầu. Cột J còn trống.
BangKeMuaVao_T9_dap_an.xlsx
Bản đã có cột Khoá và tô trùng, mở ra để so kết quả.

Các bước làm

1
Thử tô trùng theo cột số hoá đơn
Bấm tiêu đề cột D để chọn cả cột số hoá đơn. Vào Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values, để kiểu tô mặc định rồi Enter. Có 14 ô đỏ, tức 7 cặp. Xem ô D9 và D14: cùng số 128 nhưng của hai người bán khác nhau, vì người bán nào cũng đánh số lại từ 1 đầu năm. Bấm Ctrl+Z để bỏ kiểu tô này.
2
Thêm cột Khoá
Bấm ô J6 (ô trống ngay bên phải dòng tiêu đề của bảng), gõ Khoá rồi Enter. Bảng tự nới thêm cột J, con trỏ xuống ô J7.
3
Ghép khoá từ mã số thuế, ký hiệu và số hoá đơn
Ô J7 gõ công thức dưới đây. TEXT với mười số 0 trả lại số 0 đầu của mã số thuế. Tiếp theo nối ký hiệu ở cột C. Số hoá đơn ở cột D bọc TRIM để bỏ dấu cách thừa. Enter, bảng tự chép công thức xuống tới J34. Cùng người bán, cùng ký hiệu, cùng số thì mới ra cùng một khoá.
=TEXT(F7,"0000000000")&C7&TRIM(D7)
4
Tô trùng theo cột Khoá
Bấm tiêu đề cột J để chọn cả cột, dòng gõ thêm sau này cũng được tô. Vào lại Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values rồi Enter. Chỉ còn 4 ô đỏ: J18, J22, J32 và J34.
5
Đọc hai cặp trùng
Dòng 22 trùng dòng 34: tờ tải về ghi mã số thuế 361981057, tờ gõ tay ghi 0361981057. Dòng 18 trùng dòng 32: số hoá đơn 87 ở tờ gõ tay dính một dấu cách phía trước. Cả hai cặp đều là tờ gõ tay từ email trùng với tờ đã có trong danh sách tải về. Tiền thuế của hai tờ gõ thừa ở I32 và I34 cộng lại 9.888.000.
6
Thử gõ lại một tờ đã có
Bấm ô C35, ngay dưới dòng cuối của bảng. Gõ C26TGS, Tab, 2417, Tab, Tab, 3736079038 rồi Enter. Bảng tự nới xuống dòng 35 và ô J35 tự có công thức. J35 và J27 (tờ số 2417 đã có ở dòng 27) cùng đỏ ngay khi vừa Enter.

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

Khoá gồm mã số thuế người bán đủ 10 số, ký hiệu và số hoá đơn.
Số hoá đơn lưu dạng chữ có số 0 đầu ("00001203") thì thay TRIM(D7) bằng TEXT(D7,"0").
Bảng kê có nhiều mẫu số (1, 2...) thì nối thêm cột mẫu số vào khoá.

TEXT, TRIM và Duplicate Values có từ Excel 2010: Excel 2016, 2019, 2021, 365 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