Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

**hajdajgja** · 08-18-2010, 05:20 PM

Dear All!
Như vậy là trong các bài trước tôi đã chia sẻ với các bạn về các phương pháp Trích lọc sổ cái bằng VBA. Các bài được post theo trình tự từ dễ tới khó và cũng theo trình tự đó tốc độ xử lý của các file tăng dần. Đó là ;
1/ Trich lọc bằng vòng lặp For…Next
2/ Trích lọc bằng công cụ sẵn có của Ex : Advanced Filter
3/ Trich lọc bằng Find Method
4/ Trích lọc bằng ADO
5/ Trích lọc bằng mảng Array
Có bạn hỏi tôi có còn cách trích lọc nào nữa không?
Xin thưa vẫn còn nhưng vì điều kiện thời gian không cho phép nên hẹn vào dịp khác và trong trường hợp khác.
Có bạn lại hỏi vì sao phải dùng VBA? Vậy tôi dùng các công thức của hàm Ex có được không?
Xin thưa : Dùng VBA là để cho file gọn nhẹ hơn, trich xuất nhanh hơn và file không bị phình to chiếm nhiều bộ nhớ và khi mở file sẽ rất lâu. Dùng công thức và hàm hoàn toàn được nhưng dung lượng file sẽ rất lớn và việc truy xuất nó rất chậm trễ.
Vậy dùng hàm như thế nào trong trường hợp này? Chúng ta cùng xem nhé

Tôi vẫn lấy CSDL của bài cũ và dùng hoàn toàn công thức để thực hiện việc trích lọc này
Nguyên tắc chung của phương pháp này là dùng công thức copy toàn bộ dữ liệu từ NKC sang Sổ cái với tiêu chí là những dòng nào có “dính líu” tới tài khoản sổ cái trong cột TK Nợ và TK Có của NKC thì cho hiện lên, còn nhưng dòng khác chuyển thành ô trắng. Sau khi trích lọc xong thì dùng Autofilter để ẩn các dòng trắng đi, chỉ giữ lại các dòng có dữ liệu thôi. Chúng ta cùng thực hiện nhé

P/S : Sorry. Định viết tiếp nhưng...kẹt rồi. Hẹn gặp lại các bạn vào ngày mai nhé
Thân

View more random threads:

**akzhoan** · 08-19-2010, 04:31 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Sorry vì ngày hôm qua kẹt nên không viết bài tiếp được. Hôm nay chúng ta tiếp tục nhé.

Các bạn mở file mẫu ra và để ý thấy rằng NKC bắt đầu từ dòng thứ 9, Sổ Cái bắt dầu từ dòng thứ 11. Trình tự thực hiện như sau :
1/ Tại ô A11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$B9;"")
Giải thích : Nếu ô F9 của NKC hoặc ô G9 của NKC có giá trị bằng giá trị của Tài khoản sổ cái(được xác định bằng Validation tại ô D5 của Sổ Cái) thì lấy giá trị ô B9 của NKC điền vào ô A11 của Sổ Cái, nếu không thỏa mãn thì ghi giá trị Null. Công thức này nhằm lấy giá trị của Ngày Ghi Sổ trong NKC điền vào Sổ Cái
2/ Tại ô B11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$C9;"")
Giải thích : Tương tự như trên nhằm lấy Số CT của NKC đưa sang Sổ Cái
3/ Tại ô C11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$D9;"")
Giải thích ; Tương tự như trên nhằm lấy ngày chứng từ từ NKC đưa sang Sổ Cái
4/ Tại ô D11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$E9;"")
Giải thích : Tương tự nhằm lấy dòng diễn giải từ NKC đưa sang Sổ Cái
5/ Tại ô E11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$F9=$D$5;NKC!$G9;IF(NKC!$G9=$D$5;NKC!$F9;" "))
Giải thích : Nếu ô F9 của NKC bằng giá trị của Tài khoản sổ cái, thì lấy giá trị ô G9 của NKC ghi vào ô E11 của Sổ Cái. Nếu ô G9 của NKC = TK Sổ Cái thì lấy giá trị ô F9 của NKC ghi vào ô E11 của Sổ Cái. Nếu không thỏa mãn cả hai trường hợp trên thì ghi giá trị Null. Công thức này nhằm lấy giá trị của tài khoản đối ứng của tài khoản Sổ Cái
6/ Tại ô F11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$F9=$D$5;NKC!$H9;0)
Giải thích : Nếu ô F9 của NKC = TK Sổ Cái thì lấy giá trị ô H9 của NKC ghi vào ô F11 của Sổ Cái, nếu không thỏa mãn thì ghi giá tri =0. Công thức này nhằm lấy số tiền Nợ của tài khoản Sổ Cái ghi vào ô F11 của Sổ Cái
7/ Tại ô G11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$G9=$D$5;NKC!$H9;0)
Giải thích ; Tương tự như 6/ nhằm để lấy Số tiền Có của TK Sổ Cái ghi vào ô G11 của Sổ Cái
8/ Copy dòng từ A11:G11 xuống hết dòng 999 của Sổ Cái. Mục đích là để nếu có thay đổi số dòng của NKC thì củng không ảnh hưởng tới số dòng của Sổ Cái
9/ Tại ô D1000 ghi dòng chữ : Phát Sinh Trong Kỳ
10/ Tại ô D1001 ghi dòng chữ : Số Dư Cuối Kỳ
11/ Tại ô F1000 ghi công thức : =SUBTOTAL(9;F11:F999), nhằm lấy tổng số tiền nợ phát sinh của TK Sổ Cái
12/ Tại ô G1000 ghi công thức : =SUBTOTAL(9;G11:G999), nhằm lấy tổng số tiền có phát sinh của TK Sổ Cái
13/ Tại ô F1001 ghi công thức : =IF(F10-G10+F1000-G1000>0;F10-G10+F1000-G1000;0), nhằm lây số dư nợ cuối kỳ của TK Sổ Cái
14/ Tại ô G1001 ghi công thức : =IF(F10-G10+F1000-G1000<0;ABS(F10-G10+F1000-G1000);0), nhằm lấy số dư có cuối kỳ của TK Sổ Cái
14/ Định dạng bảng tính Sổ Cái :
Quét chọn khối A11:A999, C11:C999 định dạng ngày tháng năm dd/mm/yyyy
Quét chọn khối F11:G1001, đinh dạng số, không có số 0 sau dấu phẩy, có dấu phân cách hàng ngàn
15/ Dấu hàng trắng : Đặt vệt sáng của con trỏ vào ô D9, vào Menu Data/Filter/AutoFilter, xuất hiện các mũi tên quay xuống. Chọn mũi tên tại ô D9 và chọn (NonBlanks)

Như vậy là ta đã hoàn thành việc thiết lập công thức tính cho Sổ Cái. Bây giờ muốn lập cho sổ cái nào, bạn chỉ việc chọn TK tại ô D5 bằng Validation, sau đó vào ô D9 để chọn NonBlanks là xong. Để tự động hóa việc chọn này, ta có thể dung một macro đơn giản sau đây :
Nhấn Alt+F11, kich đúp tên Sheet SoCai trong phần bên trái và ghi câu lệnh sau :

Mã nguồn PHP:


[COLOR=#000000]
[/COLOR][COLOR=#007700]Private [/COLOR][COLOR=#0000BB]Sub Worksheet_Change[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]ByVal Target [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700])
    If [/COLOR][COLOR=#0000BB]Target[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Address [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"[/COLOR][COLOR=#0000BB]$D[/COLOR][COLOR=#DD0000]$5" [/COLOR][COLOR=#0000BB]Then
        Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"D9"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Select
        Selection[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]AutoFilter Field[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#0000BB]4[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Criteria1[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#DD0000]"<>"
    [/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]If
[/COLOR][COLOR=#0000BB]End Sub[/COLOR]

Như vậy cứ mổi lần chọn xong TK Sổ Cái ở ô D5 thì bảng tính tự tính và che hàng trắng

Các bạn có thể tham khảo file tại đây :

http://www.mediafire.com/?4h3t9cec94abbub

Các bạn tự kiểm tra dung lượng của file này và các file trước rồi tự nhận xét nhé
Thân

**ebvseo** · 08-19-2010, 08:22 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Cám ơn vì bài của bạn. Bạn xem xem có các ham lọc hay tìm kiếm cho hinh thức kế toán chứng từ ghi sổ không gửi cho mình nha( nếu có) Gmail Thuquynh2461@gmail.com

**ndk2303** · 08-20-2010, 05:10 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

mình có một NKC giờ muốn tạo sổ cái tài khoản mà không muốn lọc, xong cópy như thế thủ công và mất thời gian lắm ai có cách gì chỉ giúp mình với nhé emai: lethao1901985@yahoo.com.vn

**lamerjapan** · 08-23-2010, 12:11 AM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Gửi bởi thaobimbim

mình có một NKC giờ muốn tạo sổ cái tài khoản mà không muốn lọc, xong cópy như thế thủ công và mất thời gian lắm ai có cách gì chỉ giúp mình với nhé emai: lethao1901985@yahoo.com.vn

Không lọc làm sao ra sổ cái hả bạn?
Chỉ cần tạo công thức một lần và dùng ...mãi mãi
Nếu muốn dùng VBA thì tham khảo ở các bài đã post
Thân
-----------------------------------------------------------------------------------------

Gửi bởi Thuquynh2461

Cám ơn vì bài của bạn. Bạn xem xem có các ham lọc hay tìm kiếm cho hinh thức kế toán chứng từ ghi sổ không gửi cho mình nha( nếu có) Gmail Thuquynh2461@gmail.com

Bạn gửi cái CTGS đó vào mail của tôi, tôi giúp khi có thể giúp được
Mail của tôi : bachdanggiang_2006@yahoo.com
Thân

**kientrogia24h** · 08-23-2010, 06:07 AM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Mình là hội viên mới nên không theo dõi các fiel trước của Xuân Thanh(cách lọc và làm sổ trên exel) bạn có thể gửi lại các fiel đó lại cho minh để minh theo dõi bài của bạn được không? Giúp mình nhé cảm ơn Xuân Thanh trước nha
Đia chỉ Mail mình là : Cuontheochieugio_chi@yahoo.com

**dunk90** · 08-23-2010, 03:53 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

mình cũng là hội viên mới nên cũng không theo dõi file excel trước chị có thể gửi cho em với ko? Mail của em là trinh.thuy04@gmail.com
cám ơn chị trước nha

**ngtuananh1122** · 08-23-2010, 04:07 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Dear xuanthanh!
Mình mới tham gia diễn đàn nên cũng chưa biết về các bài trước của bạn về "Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel". Bạn có thể gửi các file này cho mình được ko? Địa chỉ email của mình là sacu862000@yahoo.com.
Cảm ơn bạn trước nhé.

**kimthua33** · 08-23-2010, 04:30 PM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Gửi bởi xuanthanh

Sorry vì ngày hôm qua kẹt nên không viết bài tiếp được. Hôm nay chúng ta tiếp tục nhé.

Các bạn mở file mẫu ra và để ý thấy rằng NKC bắt đầu từ dòng thứ 9, Sổ Cái bắt dầu từ dòng thứ 11. Trình tự thực hiện như sau :
1/ Tại ô A11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$B9;"")
Giải thích : Nếu ô F9 của NKC hoặc ô G9 của NKC có giá trị bằng giá trị của Tài khoản sổ cái(được xác định bằng Validation tại ô D5 của Sổ Cái) thì lấy giá trị ô B9 của NKC điền vào ô A11 của Sổ Cái, nếu không thỏa mãn thì ghi giá trị Null. Công thức này nhằm lấy giá trị của Ngày Ghi Sổ trong NKC điền vào Sổ Cái
2/ Tại ô B11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$C9;"")
Giải thích : Tương tự như trên nhằm lấy Số CT của NKC đưa sang Sổ Cái
3/ Tại ô C11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$D9;"")
Giải thích ; Tương tự như trên nhằm lấy ngày chứng từ từ NKC đưa sang Sổ Cái
4/ Tại ô D11 của Sổ Cái, ta gõ công thức :
=IF(OR(NKC!$F9=$D$5;NKC!$G9=$D$5);NKC!$E9;"")
Giải thích : Tương tự nhằm lấy dòng diễn giải từ NKC đưa sang Sổ Cái
5/ Tại ô E11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$F9=$D$5;NKC!$G9;IF(NKC!$G9=$D$5;NKC!$F9;" "))
Giải thích : Nếu ô F9 của NKC bằng giá trị của Tài khoản sổ cái, thì lấy giá trị ô G9 của NKC ghi vào ô E11 của Sổ Cái. Nếu ô G9 của NKC = TK Sổ Cái thì lấy giá trị ô F9 của NKC ghi vào ô E11 của Sổ Cái. Nếu không thỏa mãn cả hai trường hợp trên thì ghi giá trị Null. Công thức này nhằm lấy giá trị của tài khoản đối ứng của tài khoản Sổ Cái
6/ Tại ô F11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$F9=$D$5;NKC!$H9;0)
Giải thích : Nếu ô F9 của NKC = TK Sổ Cái thì lấy giá trị ô H9 của NKC ghi vào ô F11 của Sổ Cái, nếu không thỏa mãn thì ghi giá tri =0. Công thức này nhằm lấy số tiền Nợ của tài khoản Sổ Cái ghi vào ô F11 của Sổ Cái
7/ Tại ô G11 của Sổ Cái, ta gõ công thức :
=IF(NKC!$G9=$D$5;NKC!$H9;0)
Giải thích ; Tương tự như 6/ nhằm để lấy Số tiền Có của TK Sổ Cái ghi vào ô G11 của Sổ Cái
8/ Copy dòng từ A11:G11 xuống hết dòng 999 của Sổ Cái. Mục đích là để nếu có thay đổi số dòng của NKC thì củng không ảnh hưởng tới số dòng của Sổ Cái
9/ Tại ô D1000 ghi dòng chữ : Phát Sinh Trong Kỳ
10/ Tại ô D1001 ghi dòng chữ : Số Dư Cuối Kỳ
11/ Tại ô F1000 ghi công thức : =SUBTOTAL(9;F11:F999), nhằm lấy tổng số tiền nợ phát sinh của TK Sổ Cái
12/ Tại ô G1000 ghi công thức : =SUBTOTAL(9;G11:G999), nhằm lấy tổng số tiền có phát sinh của TK Sổ Cái
13/ Tại ô F1001 ghi công thức : =IF(F10-G10+F1000-G1000>0;F10-G10+F1000-G1000;0), nhằm lây số dư nợ cuối kỳ của TK Sổ Cái
14/ Tại ô G1001 ghi công thức : =IF(F10-G10+F1000-G1000<0;ABS(F10-G10+F1000-G1000);0), nhằm lấy số dư có cuối kỳ của TK Sổ Cái
14/ Định dạng bảng tính Sổ Cái :
Quét chọn khối A11:A999, C11:C999 định dạng ngày tháng năm dd/mm/yyyy
Quét chọn khối F11:G1001, đinh dạng số, không có số 0 sau dấu phẩy, có dấu phân cách hàng ngàn
15/ Dấu hàng trắng : Đặt vệt sáng của con trỏ vào ô D9, vào Menu Data/Filter/AutoFilter, xuất hiện các mũi tên quay xuống. Chọn mũi tên tại ô D9 và chọn (NonBlanks)

Như vậy là ta đã hoàn thành việc thiết lập công thức tính cho Sổ Cái. Bây giờ muốn lập cho sổ cái nào, bạn chỉ việc chọn TK tại ô D5 bằng Validation, sau đó vào ô D9 để chọn NonBlanks là xong. Để tự động hóa việc chọn này, ta có thể dung một macro đơn giản sau đây :
Nhấn Alt+F11, kich đúp tên Sheet SoCai trong phần bên trái và ghi câu lệnh sau :

Mã nguồn PHP:


[COLOR=#000000]
[/COLOR][COLOR=#007700]Private [/COLOR][COLOR=#0000BB]Sub Worksheet_Change[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]ByVal Target [/COLOR][COLOR=#007700]As [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700])
    If [/COLOR][COLOR=#0000BB]Target[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Address [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"[/COLOR][COLOR=#0000BB]$D[/COLOR][COLOR=#DD0000]$5" [/COLOR][COLOR=#0000BB]Then
        Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"D9"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Select
        Selection[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]AutoFilter Field[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#0000BB]4[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]Criteria1[/COLOR][COLOR=#007700]:=[/COLOR][COLOR=#DD0000]"<>"
    [/COLOR][COLOR=#0000BB]End [/COLOR][COLOR=#007700]If
[/COLOR][COLOR=#0000BB]End Sub[/COLOR]

Như vậy cứ mổi lần chọn xong TK Sổ Cái ở ô D5 thì bảng tính tự tính và che hàng trắng

Các bạn có thể tham khảo file tại đây :

http://www.mediafire.com/?4h3t9cec94abbub

Các bạn tự kiểm tra dung lượng của file này và các file trước rồi tự nhận xét nhé
Thân

Cảm ơn bạn. Bài viết rất ý nghĩa.
Mình đã làm được.:rachoa:

**seolenchoi** · 09-11-2010, 03:26 AM

Ðề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Mình học thương mại nên cũng máu kế toán, mình vào xem cho biết thôi! khi nào có đủ kiến thức thì mình post lên. Ok?

Chủ đề: Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

Công cụ Chủ đề

Tìm Chủ đề

Hiển thị

Trích lọc Sổ Cái : Dùng Công Thức và Các Hàm Excel

View more random threads:

Quyền viết bài

Mách quý ông cách rèn luyện giúp điều khiển khả năng "xuất binh"

Mô tả về đặc điểm của âm hộ giả silicon dành cho quý ông

Khu dự án River Park thiết bị hiện đại

Khu dự án Valencia Riverside tuyến metro đi qua

Bí quyết sử dụng tay trong chuyện ấy như thế nào để...

Khu nhà phố cao cấp Sol Villas cư dân nhân văn

Mẫu mộ hình tháp phật giáo bằng đá tự nhiên tại long an

Verosa Park Biệt thự thiết kế rất đẹp không gian thoáng

Tìm hiểu về Popper có phải ma túy gây nghiện không? (9)

Căn hộ chung cư Picity Sky Park khu bậc nhất