Hướng dẫn cách lọc ra tài khoản cấp nhỏ nhất từ bảng danh mục tài khoản
Tại sao cần phải lọc ra tài khoản cấp nhỏ nhất trong bảng danh mục tài khoản? Chắc hẳn bạn sẽ có thắc mắc này khi bắt đầu đọc bài viết này. Câu trả lời là: Khi định khoản kế toán trong sổ nhật ký chung, chúng ta phải định khoản theo tài khoản chi tiết nhất, hay nói cách khác là theo tài khoản cấp nhỏ nhất. Và nếu không lọc riêng ra 1 danh sách những tài khoản cấp nhỏ nhất này, chúng ta rất dễ nhầm lẫn, sai sót khi định khoản nghiệp vụ kế toán, đặc biệt là khi làm việc trên Excel.
Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách thực hiện việc 3 yêu cầu sau:
- Phát hiện tài khoản nào là tài khoản cấp nhỏ nhất trong bảng danh mục tài khoản kế toán
- Lọc (tách) riêng những tài khoản cấp nhỏ nhất đó ra 1 danh sách
- Đặt danh sách vừa lọc được vào DropDown List (danh sách chọn) trong sổ Nhật ký chung, phần tài khoản
Cách làm cụ thể như sau:
Trước hết các bạn cần tải về Danh mục tài khoản kế toán theo thông tư 200.
Chúng ta sẽ thực hành dựa vào bảng danh mục này.
Xem nhanh
Phát hiện tài khoản nào là tài khoản cấp nhỏ nhất trong bảng danh mục tài khoản kế toán
1. Phân tích yêu cầu:
Trước khi thực hiện công việc, chúng ta cần phải hiểu rõ: Tài khoản cấp nhỏ nhất là gì, hay Tài khoản cấp nhỏ nhất có đặc điểm gì.
Tài khoản cấp nhỏ nhất KHÔNG PHẢI là tài khoản cấp 1, hay tài khoản cấp 2.
Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:
Nếu tài khoản cấp 1 không có tài khoản cấp 2 thì tài khoản đó là tài khoản cấp nhỏ nhất.
Nếu tài khoản cấp 1 có tài khoản cấp 2 thì các tài khoản cấp 2 của tài khoản đó là tài khoản cấp nhỏ nhất
Nếu tài khoản có cả tài khoản cấp 2, cả tài khoản cấp 3 thì những tài khoản cấp 2 nào có tài khoản cấp 3 thì các tài khoản cấp 3 sẽ là tài khoản cấp nhỏ nhất.
=> Hiểu cách khác là TÀI KHOẢN CHI TIẾT NHẤT. Nếu không có chi tiết thì lấy chính tài khoản ở cấp đó.
2. Hàm, công thức sử dụng
a. Biện luận để tìm hàm phù hợp
Trong cột Số tài khoản (cột A) bao gồm cả tài khoản tổng hợp (3 ký tự) và cả tài khoản chi tiết (cấp 2 = 4 ký tự, cấp 3 = 5 ký tự).
Nguyên tắc của tài khoản chi tiết: 3 ký tự đầu là ký tự của tài khoản tổng hợp, các ký tự sau là dành cho chi tiết. Như vậy tài khoản có chi tiết thì tại các tài khoản chi tiết luôn có chứa các ký tự của tài khoản tổng hợp (cấp trên).
=> Để xét tại mỗi dòng tài khoản có chứa ký tự của tài khoản cấp trên nó hay không, chúng ta sẽ đếm xem số ký tự của tài khoản tại dòng đang xét xuất hiện bao nhiêu lần trong toàn bộ cột Số tài khoản.
Để thỏa mãn yêu cầu này, chúng ta phải xét tới công thức mảng:
- Đo độ dài số ký tự của mỗi tài khoản
- Xét toàn bộ mảng số tài khoản theo độ dài của tài khoản đang xét.
b. Hàm sử dụng
Sử dụng hàm SUMPRODUCT: là hàm thỏa mãn cả yêu cầu tính được trong mảng, dùng để đếm.
Hàm SUMPRODUCT được viết như sau:
Xét tại ô A4 = tài khoản 111
SUMPRODUCT((LEFT($A$4:$A$315,LEN(A4))=LEFT(A4,LEN(A4)))*1)
- A4:A315 là toàn bộ cột Số tài khoản. Vùng này cần cố định để khi áp dụng sang các tài khoản khác vẫn chỉ tham chiếu tới vùng này.
- Len(A4) là đo độ dài số ký tự tại ô A4.
- LEFT($A$4:$A$315,LEN(A4)) là phần bên trái của mảng Số tài khoản, độ dài số ký tự cần xét bằng số ký tự ô A4 (3 ký tự)
- LEFT(A4,LEN(A4)) là phần bên trái của ô A4, độ dài số ký tự cần xét bằng số ký tự ô A4 (3 ký tự) (toàn bộ số ký tự trong ô A4)
Nếu mỗi lần 2 đối tượng này bằng nhau thì sẽ tính là 1 đơn vị.
Kết quả thu được = 4
Vì tài khoản 111 có 3 tài khoản cấp 2, với 3 tài khoản này đều có phần bên trái, tính theo 3 ký tự là 111. Cộng thêm bản thân tài khoản 111 nên tổng số tài khoản có bắt đầu 3 ký tự 111 là 4 tài khoản.
Áp dụng tương tự cho toàn bộ các tài khoản khác, chúng ta có kết quả như sau:
- Nếu kết quả = 1: Chỉ xuất hiện 1 lần, đó là tài khoản chi tiết nhất, cấp nhỏ nhất
- Nếu kết quả >1: Xuất hiện nhiều hơn 1 lần, đó không phải tài khoản chi tiết nhất, có tài khoản cấp nhỏ hơn.
Lọc (tách) riêng những tài khoản cấp nhỏ nhất đó ra 1 danh sách
Việc lọc (tách) này chúng ta có thể thực hiện theo 2 cách:
- Cách copy / paste: Sử dụng Auto Filter, lọc ở cột D những giá trị = 1. Sau đó copy và paste các kết quả lọc vào vị trí kết quả. Cách này thường sử dụng, các bạn có thể tự làm.
- Cách lọc nhanh bằng Advanced Filter: Chúng ta hãy cùng tìm hiểu cách này nhé, đảm bảo giảm được rất nhiều thao tác tay, giúp thực hiện việc này nhanh hơn, chính xác hơn.
Bước 1: Xây dựng điều kiện lọc
Căn cứ kết quả ở bước trên, chúng ta có ở cột D (tiêu đề cột là Lọc), những giá trị = 1 chính là những giá trị tương ứng ở cột Số TK mà chúng ta muốn lấy
Điều kiện cho Advanced Filter như sau:
- Phần tiêu đề: Chữ Lọc (giống tiêu đề ở cột D) (ô G1)
- Nội dung: số 1 (ô G2)
Vùng điều kiện như sau:
Vì chúng ta chỉ muốn lọc kết quả là Số tài khoản, Tên tài khoản, do đó chúng ta sẽ xác định chỉ tiêu kết quả cần lọc theo tên tiêu đề của 2 cột: Tài khoản (G3), Tên tài khoản (H3)
Bước 2: Thực hiện thao tác Advanced Filter
Trong tab Data, chúng ta bấm chọn Advanced Filter
Trong cửa sổ Advanced Filter chúng ta thực hiện như sau:
- Copy to another location: Đưa kết quả ra vị trí khác (tách, lọc riêng ra 1 danh sách khác)
- List range: Vùng dữ liệu cần lọc (Toàn bộ bảng danh mục tài khoản, bao gồm cả cột D – cột lọc – chứa kết quả ở bước 1)
- Criteria range: Vùng điều kiện lọc (đã xây dựng ở trên)
- Copy to: kết quả cần trích xuất ra đâu. Cụ thể ở đây chúng ta cần trích xuất theo 2 chỉ tiêu tại ô G3 và H3 (đã xác định ở trên)
Bấm ok
Kết quả thu được chính là danh sách mà chúng ta cần trích lọc:
Nhược điểm của phương pháp này là chỉ có thể áp dụng Advanced Filter để trích xuất kết quả trên cùng 1 sheet, không được đặt vùng kết quả ra sheet khác. Nếu muốn trích ra Sheet khác, chúng ta có thể sử dụng cách Copy / Paste
Đặt danh sách vừa lọc được vào DropDown List trong sổ NKC
Tạo mới Sheet NKC, trong đó các bạn tự định dạng theo mẫu sổ NKC theo thông tư 200 (vì chúng ta đang làm theo danh mục tài khoản thông tư 200)
Tại 2 cột TK Nợ, TK Có trong Sheet NKC, chọn tab Data > Data Validation
Trong cửa sổ Data Validation:
- Mục Allow: chọn List
- Source: Chọn tới danh mục Số tài khoản đã trích lọc ở bước 2 (Sheet DM_TK, ô G4LG196)
Như vậy chúng ta đã có thể định khoản theo tài khoản cấp nhỏ nhất một cách dễ dàng và chính xác rồi.
Tải về file kết quả tại đây: http://bit.ly/2Hs8Yk3
Chúc các bạn học tốt cùng Học Excel Online!
Làm kế toán tổng hợp trên Excel rất cần thiết, bởi trên Excel bạn có thể nắm được cách tổ chức, quản lý dữ liệu kế toán, đồng thời tự tay xây dựng công thức lên các báo cáo tài chính. Khi đó bạn có thể dễ dàng kiểm tra kết quả, biết được những thay đổi dữ liệu trong sổ NKC sẽ ảnh hưởng như thế nào tới kết quả trong các báo cáo.
