Chuyên Đề : Trích Lọc Sổ Cái

xuanthanh

Member
Hội viên mới
Vừa qua có rất nhiều bạn viết thư và nhắn tin hỏi tôi về các phương pháp trich lọc sổ cái từ bảng kê các chứng từ phát sinh hoặc từ nhật ký chung và nhờ giúp dùm trong các file cụ thể
Để "rộng đường dư luận" và cũng là để trao đổi và học tập thêm, nay tôi mở topic này đưa ra một ví dụ(một file cụ thể) để chúng ta cùng trao đổi và luận bàn nhé
Ai có chung mục đích xin nhấn Thank một cái để chúng ta cùng bàn

Dưới đây là file mẫu
 

Đính kèm

  • Trich Loc So Cai(File Mau).xls
    65.5 KB · Lượt xem: 2,604
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 1 : Trích Lọc bằng vòng lặp For...Next

Xin được mở đầu bằng phương pháp trích lọc đơn giản nhất
Để trích lọc, có nhiều phương án. ở đây xin giới thiệu với các bạn một phương pháp trích lọc đơn giản nhất : Dùng vòng lặp For…Next
Nguyên tắc của phương án này là xét trong vùng dữ liệu của bảng Nhật ký chung từ dòng đầu tiên tới dòng cuối cùng trong cột tài khoản có và tài khoản nợ, nếu ô nào trùng với mã hiệu tài khoản sổ cái thì chép tất cả sang sổ cái. Nếu TK Nợ = TK Sổ cái thì lấy TK Có ghi vào cột TKDU, Số tiền ghi vào cột Số tiền nợ và ngược lại
Sau đó tính tổng phát sinh và số dư cuối kỳ
Phần Mã hiệu tài khoản sổ cái và tên tài khoản, các bạn dùng Validation và hàm VLOOKUP để làm. Làm tương tự cho số dư đầu kỳ
Phần số dư cuối kỳ, các bạn làm theo nguyên tắc sau :
Nếu Tổng = Dư Nợ Đầu Kỳ-Dư Có Đầu Kỳ+PS Nợ-PS Có>0 thi gi giá trị Tong vào cột Dư Nợ cuối kỳ. Ngược lại thì lấy giá trị tuyệt đối của Tong và ghi vào Dư Có cuối kỳ
Hạn chế của file trích lọc theo kiểu này là nếu dữ liệu nhiều thì thời gian trích lọc sẽ rất lâu
Các bạn xem file đính kèm sẽ rõ
Lưu ý khi mở file :
Mở file Ex trắng, vào MeNu Tools/Macro/Security chọn Medium. Đóng Ex
Mở File Trích lọc, chọn Enable Macro và xem cách đặt công thức chung và các Name (Mở Insert/Name/Define để xem cách đạt name)
Nhấn Alt + F11 để xem code
Thân
 

Đính kèm

  • Trich Loc So Cai Bang ForNext.xls
    87.5 KB · Lượt xem: 1,584
Sửa lần cuối:
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 2 : Trích Lọc bằng công cụ sẵn có của Excel : Advanced Filter...

Khi trích lọc bằng Advanced Filter(Data/Filter/Advanced Filter..), chúng ta đặc biệt chú ý mấy điểm sau
1/ Hàng tiêu đề nhất thiết phải có và không được dùng Merge cell
2/ Đặt vùng điều kiện phải phù hợp với yêu cầu trích lọc, nếu không dữ liệu trích lọc sẽ không đúng
Với bài toán cụ thể này, vùng điều kiện của ta là vùng A1-B3. Tại sao vậy? Vì điều kiện trích lọc của ta bao gồm cả tài khoản nợ và tài khoản có theo kiểu hoặc là nợ hoặc là có đều đúng
Nội dung của phương pháp này như sau:
1/ Chọn toàn bộ vùng dữ liệu của bảng NKC, bao gồm cả hàng tiêu đề
2/ Vào menu Data/Filter/Advanced Filter ghi vùng điều kiện lọc vào ô Criteria Range
3/ Nhấn nút OK
4/ Chép dữ liệu sau lọc sang Sheet SOCAI và xử lý dữ liệu sau khi trích lọc
Các bạn xem file sau sẽ rõ
 

Đính kèm

  • Trich Loc So Cai Bang AF.xls
    86 KB · Lượt xem: 1,957
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Hay quá, còn những cách nào anh viết hướng dẫn giúp cho tụi em học ít kinh nghiệm, mai mốt ra trường mà áp dụng.
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Hay quá, còn những cách nào anh viết hướng dẫn giúp cho tụi em học ít kinh nghiệm, mai mốt ra trường mà áp dụng.

Ủa? Chú học CDXD mà, liên quan gì tới KT?
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 3 : Trích lọc bằng Find Methode

Để trích lọc, ta cũng có thể dung chức năng tìm kiếm trong Excel, cụ thể là ta dùng Find Methode (Edit Find… hoặc Ctr +F)
Mục đích của phương pháp này là dùng Find tìm kiếm trong hai cột tài khoản có và tài khoản nợ của NKC xem có tài khoản nào trùng với TK sổ cái thì nhặt đưa sang sổ cái. Về nguyên tắc chung, nó cũng gần giống như cách tìm bằng vòng lặp For…Next nhưng về tốc độ xử lý và tìm kiếm thì nhanh hơn rất nhiều

Mời các bạn xem file sẽ rõ
 

Đính kèm

  • Trich Loc So Cai Bang Find Method.xls
    94 KB · Lượt xem: 1,362
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 3 : Trích lọc bằng Find Methode

Để trích lọc, ta cũng có thể dung chức năng tìm kiếm trong Excel, cụ thể là ta dùng Find Methode (Edit Find… hoặc Ctr +F)
Mục đích của phương pháp này là dùng Find tìm kiếm trong hai cột tài khoản có và tài khoản nợ của NKC xem có tài khoản nào trùng với TK sổ cái thì nhặt đưa sang sổ cái. Về nguyên tắc chung, nó cũng gần giống như cách tìm bằng vòng lặp For…Next nhưng về tốc độ xử lý và tìm kiếm thì nhanh hơn rất nhiều

Mời các bạn xem file sẽ rõ

Theo Mình cách này là nhanh nhất trong 3 cách bạn vừa nêu.
Còn cách dùng ADO thì còn tuyệt nữa, rất tiết mình thì chỉ biết ứng dụng chứ không biết viết code, hy vọng bạn cho mình thêm bài 4 bằng phương thức ADO.

Thân.
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Theo Mình cách này là nhanh nhất trong 3 cách bạn vừa nêu.
Còn cách dùng ADO thì còn tuyệt nữa, rất tiết mình thì chỉ biết ứng dụng chứ không biết viết code, hy vọng bạn cho mình thêm bài 4 bằng phương thức ADO.

Thân.

Tất nhiên còn nhiều cách trích lọc, trong đó có cả trích băng ADO. dần dần tôi sẽ post lên để các bạn tham khảo
Thân
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 4 : Trích lọc bằng ADO

Để trích lọc được bằng ADO, ta phải dùng phương thức kết nối với CSDL. Thực hiện như sau : Trong màn hình VBE(Nhấn Alt+F11), vào menu Tools/Reference kích chọn Microsoft ActiveX Data Objects 2.8 Library
Nguyên tắc chung của phương pháp này là
1/ Tạo một string kết nối tới file dữ liệu nguồn
2/ Định nghĩa một biến Recordset kiểu ADODB và mở một recordset mới
3/ Tạo một biến SQL để chứa dữ liệu trả về
4/ Mở Recordset theo dữ liệu trả về của câu lệnh SQL trong file dữ liệu được kết nối
5/ Chép (copy) dữ liệu của recordset đó vào Sổ Cái
6/ Xử lý dữ liệu sau khi đã được đưa vào Sổ cái
7/ Giải phóng bộ nhớ

Các bạn xem file sau sẽ rõ
 

Đính kèm

  • Trich Loc So Cai Bang ADO.rar
    27.3 KB · Lượt xem: 935
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 5 : Trích lọc bằng mảng Array

Nguyên tắc của trích lọc này là định nghĩa các biến mảng rồi tìm các điều kiện thỏa mãn điều kiện của bài toán và gán vào các mảng tương ứng. Cuối cùng chép các dữ liệu từ mảng ra Sổ cái, xóa biến mảng, giải phóng bộ nhớ và xử lý dữ liệu sau trích lọc
Các bạn có thể tham khảo tại đây

http://www.mediafire.com/?6hernytmnffs15g

Thân mến
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Bài 1 : Trích Lọc bằng vòng lặp For...Next

Xin được mở đầu bằng phương pháp trích lọc đơn giản nhất
Để trích lọc, có nhiều phương án. ở đây xin giới thiệu với các bạn một phương pháp trích lọc đơn giản nhất : Dùng vòng lặp For…Next
Nguyên tắc của phương án này là xét trong vùng dữ liệu của bảng Nhật ký chung từ dòng đầu tiên tới dòng cuối cùng trong cột tài khoản có và tài khoản nợ, nếu ô nào trùng với mã hiệu tài khoản sổ cái thì chép tất cả sang sổ cái. Nếu TK Nợ = TK Sổ cái thì lấy TK Có ghi vào cột TKDU, Số tiền ghi vào cột Số tiền nợ và ngược lại
Sau đó tính tổng phát sinh và số dư cuối kỳ
Phần Mã hiệu tài khoản sổ cái và tên tài khoản, các bạn dùng Validation và hàm VLOOKUP để làm. Làm tương tự cho số dư đầu kỳ
Phần số dư cuối kỳ, các bạn làm theo nguyên tắc sau :
Nếu Tổng = Dư Nợ Đầu Kỳ-Dư Có Đầu Kỳ+PS Nợ-PS Có>0 thi gi giá trị Tong vào cột Dư Nợ cuối kỳ. Ngược lại thì lấy giá trị tuyệt đối của Tong và ghi vào Dư Có cuối kỳ
Hạn chế của file trích lọc theo kiểu này là nếu dữ liệu nhiều thì thời gian trích lọc sẽ rất lâu
Các bạn xem file đính kèm sẽ rõ
Lưu ý khi mở file :
Mở file Ex trắng, vào MeNu Tools/Macro/Security chọn Medium. Đóng Ex
Mở File Trích lọc, chọn Enable Macro và xem cách đặt công thức chung và các Name (Mở Insert/Name/Define để xem cách đạt name)
Nhấn Alt + F11 để xem code
Thân

ANh ơi, tham số "i" trong VBE của anh là gì đó ạ??? em chưa hiểu lắm
 
Re: Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Thanks bác. E cũng phải nghiên cứu đã. Chưa quen
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

xuan thanh ơi.
bạn nói rõ từng bước từ khi lập sổ NKC đến khi lập sổ cái giúp mình được không? (dùng Advanced Filter...)
mình làm rồi nhưng ko dc.
giúp mình nhé.
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Em chào anh, Em là hội viên mới trên diễn đàn. Em đã xem bài biết của anh về trích lọc sổ cái bằng Advanced Filter..., em đã cóp file mẫu của anh về và cũng tự tạo một file mới để làm thử.
Tuy nhiên em chỉ có thể làm ra được 1 sổ cái trên từng sheet 1 riêng rẽ từ nhật ký chung đó. Mà trong bài mẫu của anh, em thấy tất cả các sổ cái đều để trên 1 sheet.
Trên sheet " Sổ cái", mình có thể lọc trực tiếp từng tài khoản 1 bằng cách chọn mã hiệu tài khoản, sau đó nhấn vào biểu tương " Loc Advance.." nổi trên phần trên Sổ cái. Đến phần đó em thực sự mắc mà chưa nghĩ ra cách làm tiếp theo.
Anh có thể hướng dẫn chi tiết hơn cho em về phần đó được không ạ>
E cảm ơn anh rất nhiều.
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

đề tài hay lắm !mình xem mà ko sao thực hành được ,khó ghê nha ?Bạn có thể cho anh em dân kế toán công thức trích lập nói trên đơpực không nè ?cám ơn bạn nhiều về đề tài này nhé!
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Em chào anh, Em là hội viên mới trên diễn đàn. Em đã xem bài biết của anh về trích lọc sổ cái bằng Advanced Filter..., em đã cóp file mẫu của anh về và cũng tự tạo một file mới để làm thử.
Tuy nhiên em chỉ có thể làm ra được 1 sổ cái trên từng sheet 1 riêng rẽ từ nhật ký chung đó. Mà trong bài mẫu của anh, em thấy tất cả các sổ cái đều để trên 1 sheet.
Trên sheet " Sổ cái", mình có thể lọc trực tiếp từng tài khoản 1 bằng cách chọn mã hiệu tài khoản, sau đó nhấn vào biểu tương " Loc Advance.." nổi trên phần trên Sổ cái. Đến phần đó em thực sự mắc mà chưa nghĩ ra cách làm tiếp theo.
Anh có thể hướng dẫn chi tiết hơn cho em về phần đó được không ạ>
E cảm ơn anh rất nhiều.

Đọc kỹ lại bài 2 nhé

xuanthanh nói:
Phần Mã hiệu tài khoản sổ cái và tên tài khoản, các bạn dùng Validation và hàm VLOOKUP để làm. Làm tương tự cho số dư đầu kỳ


---------- Post added at 05:37 ---------- Previous post was at 05:22 ----------

đề tài hay lắm !mình xem mà ko sao thực hành được ,khó ghê nha ?Bạn có thể cho anh em dân kế toán công thức trích lập nói trên đơpực không nè ?cám ơn bạn nhiều về đề tài này nhé!

Xem tại đây nếu muốn dung hàm

http://www.danketoan.com/forum/exce...i-dung-cong-thuc-va-cac-ham-excel.144233.html
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

Đề tài hay quá. Đặt cục gạch từ từ đọc!
Thanks!
 
Ðề: Chuyên Đề : Trích Lọc Sổ Cái

hoa cả mắt chả đọc được oánh dấu cái lúc khác đọc hề
 

CẨM NANG KẾ TOÁN TRƯỞNG


Liên hệ: 090.6969.247

KÊNH YOUTUBE DKT

Cách làm file Excel quản lý lãi vay

Đăng ký kênh nhé cả nhà

SÁCH QUYẾT TOÁN THUẾ


Liên hệ: 090.6969.247

Top