Hướng dẫn cách lọc danh sách tài khoản chi tiết theo tài khoản tổng hợp trong Excel

Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn kỹ thuật Excel giúp lọc danh sách tài khoản chi tiết theo tài khoản tổng hợp. Đây là nội dung trong khóa học KTH101 – Kế toán tổng hợp từ A đến Z của Học Excel Online. Các bạn hãy tìm hiểu và vận dụng vào Sổ chi tiết trên Excel nhé.

Xem nhanh

Cách lọc danh sách tài khoản chi tiết theo tài khoản tổng hợp trong Excel

Mục đích

Trong danh mục tài khoản trên Excel, chúng ta có danh sách tài khoản như sau:

Và trong Sổ chi tiết, yêu cầu của chúng ta là tạo ra danh sách tài khoản chi tiết tương ứng theo tài khoản tổng hợp được chọn.

Ví dụ: Khi chọn tài khoản tổng hợp là 133 => Danh sách tài khoản chi tiết sẽ gồm 2 tài khoản chi tiết là 1331 và 1332 như trong hình:

Mục đích khá rõ ràng phải không nào? Chúng ta cùng tìm hiểu cách làm là sử dụng Advanced Filter trong Excel nhé.

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:

Sử dụng Advanced Filter lọc danh sách tài khoản chi tiết

Advanced Filter có đặc điểm là giúp chúng ta có thể kết hợp đồng thời việc lọc và trích xuất dữ liệu. Các bước thực hiện Advanced Filter như sau:

Bước 1: Tạo vùng điều kiện

Advanced Filter khá đặc biệt ở việc thiết lập vùng điều kiện. Nguyên tắc là: Mỗi điều kiện sẽ gắn với 1 tên tiêu đề cột dữ liệu.

Do đó vùng điều kiện chúng ta thiết lập như sau:

Nội dung điều kiện: là tài khoản tổng hợp chọn trong ô F2 (trong hình ví dụ trên)
Cột chứa điều kiện: cột Số TK (cách viết như tại ô A2)

Do tài khoản chi tiết là những tài khoản có các ký tự đầu giống như tài khoản tổng hợp (khác ở các ký tự phía sau), do đó điều kiện là Số tài khoản tổng hợp gắn thêm dấu *

Bước 2: Thao tác Advanced Filter

Thực hiện Advanced Filter như sau:

List range: Vùng danh mục tài khoản (nếu Sổ chi tiết đặt tại Sheet khác thì chúng ta mở Advanced Filter tại Sheet đó, rồi chọn List range trong sheet chứa Danh mục tài khoản)
Criteria range: Vùng điều kiện của Advanced Filter đã thiết lập ở bước 1
Copy to: Vùng kết quả cần trích xuất (là vùng danh mục tài khoản chi tiết. Nội dung tiêu đề các cột kết quả phải giống với tên tiêu đề của bảng danh mục tài khoản)

Khi bấm OK, chúng ta có kết quả như sau:

Bước 3: Sử dụng kết quả của Advanced Filter

Chúng ta thấy trong kết quả trên, danh mục tài khoản được lọc ra gồm cả tài khoản tổng hợp và tài khoản chi tiết. Trong đó tài khoản tổng hợp luôn nằm ở dòng đầu tiên. Các dòng tiếp theo là tài khoản chi tiết.

Chúng ta thiết lập Data Validation trong ô chọn tài khoản chi tiết (ô F3) như sau:

Chọn ô F3
Chọn chức năng Data Validation > List
Thiết lập công thức trong Source như sau:

=OFFSET($H$9,0,0,COUNTA($H$9:$H$26))

Hàm OFFSET là 1 hàm trả về kết quả mảng linh động:

Bắt đầu từ ô H9 (là ô bắt đầu của danh sách tài khoản chi tiết trong kết quả của Advanced Filter)
Hai số 0 tiếp theo: không thay đổi về dòng, cột tính tại điểm bắt đầu là ô H9
Hàm COUNTA: Đếm số ô chứa giá trị (không phải ô trống) trong vùng H9:H26 (có thể mở rộng hơn nữa)

=> kết quả của công thức chính là giá trị trong những ô không phải ô trống trong cột H, bắt đầu từ dòng 9 (ô H9). Đây chính là danh sách tài khoản chi tiết chúng ta cần lấy.

Xem thêm: Hướng dẫn cách tạo danh sách động trong Excel với hàm OFFSET

Bước 4: Tự động hóa Advanced Filter mỗi khi thay đổi tài khoản tổng hợp

Để có thể tự động hóa cho thao tác Advanced Filter, chúng ta sử dụng thêm tính năng Record Macro và Sử dụng Macro trong VBA như sau:

Đầu tiên ghi lại macro sử dụng Advanced Filter: Mở thẻ Developer > chọn Record > Thực hiện lại thao tác Advanced Filter

Kết quả thu được Macro có nội dung như sau:

Sub Macro1()

Range(“A2:B232”).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(“H3:H4”), CopyToRange:=Range(“H7:I7”), Unique:=False

End Sub

Trong đó chú ý các vùng Range chính là tọa độ của các đối tượng của Advanced Filter mà chúng ta vừa chọn:

Range(“A2:B232”).AdvancedFilter : vùng dữ liệu gốc (List Range)
Action:=xlFilterCopy : hành động thực hiện là copy (chọn Copy to another location)
CriteriaRange:=Range(“H3:H4”) : vùng điều kiện (Criteria)
CopyToRange:=Range(“H7:I7”) : Vùng kết quả (Copy to)

Tiếp theo chúng ta gọi sự kiện Worksheet_Change tại Sheet chứa Sổ chi tiết, gọi tên Macro1 (là macro ghi lại thao tác Advanced Filter vừa thu được ở trên)