Tài liệu tự học Excel và thực hành tổ chức số liệu kế toán trên Excel

n Menu Tools. - Chọn ô, vùng cần bảo vệ. - Chọn Menu \FormatCell … để gọi hộp thoại FormatCell và chọn nhãn Protection. + Locked: Bật/tắt tính năng khoá dữ liệu. + Hidden: Bật/tắt tính năng che dấu dữ liệu. - Nhấp Ok để chấp nhận. Thiết lập chế độ bảo mật - Chọn Menu Tools\ Protection\Protect Sheet… Hộp thoại Protect Sheet xuất hiện: Chọn các chức năng trong khung Allow all Users of this Worksheet to để xác lập bảo mật cho ô, dòng và cột. - Nhập mật mã vào khung Password to Unprotect Sheet. - Nhấp OK, một hộp thoại xuất hiện. - Nhập lại mật mã mới vừa nhập, nếu nhập sai Excel sẽ báo lỗi và cho nhập lại. - Nhấn OK để hoàn tất quá tình thiết lập. Chú ý: Khi áp dụng lệnh này, bảng tính của bạn chỉ có xem mà không thể thực hiện được các lệnh trên bảng tính này. Bỏ chế độ bảo mật - Chọn Menu Tools\ Protection\ Unprotect Sheet… Chương trình sẽ cung cấp cho bạn hộp thoại để bạn đăng nhập mật mã cho việc hủy bỏ. - Nhập mật mã đã nhập trước. - Nhấp OK để bỏ chế độ bảo vệ. Bạn cần nhập đúng mật mã của bảng tính mà bạn đã gán thì chương trình mới thực hiện việc huỷ bỏ. 1.10.5. In ấn Định dạng trang giấy để in Bạn cần định dạng cho trang in trước khi thực hiện việc in. - Từ Menu Bar vào File\Page Setup… Hộp thoại Page Setup hiện lên màn hình, từ hộp thoại này chọn Tab Page + Orienbạntion: xác định hướng trang giấy để in dữ liệu. Portrait: in dữ liệu ra theo chiều dọc của trang giấy. Landscape: dữ liệ ra theo chiều ngang của trang giấy. + Scaling: xác định tỷ lệ dữ liệu in ra giấy và thay đổi in ra khít với chiều ngang hay chiều dọc của giấy. Có các chức năng cho bạn chọn như sau: Adjust to: thay đổi tỷ lệ của dữ liệu khi in ra giấy, giá trị này nằm trong khoảng từ 10% đến 400%, mặc định là 100%. Fit to: thay đổi vừa khít theo chiều ngang của giấy. Wide by: thay đổi vừa khít theo chiều dọc của trang giấy. Pager Size: lựa chọn các khổ giấy. Nhấp vào mũi tên hình tam giác để bật danh sách các khổ giấy và chọn một khổ giấy thích hợp. Print Quality: lựa chọn chất lượng in (chất lượng in có nghĩa là số chấm điểm trên một Inch, số này càng lớn thì khi in ra dữ liệu sẽ mịn hơn), nhấp vào mũi tên hình tam giác để chọn số của chất lượng in. Thông thường người bạn chọn 600dpi. Fist Page Number: chỉ định đánh số trang đầu tiên cho bảng tính có nhiều số trang. Nhấp các nút Print, Print Preview và Option để in bảng tính, xem bảng tính trước khi in và các tuỳ chọn trong khi in. - Chọn lựa các định dạng trong hộp thoại cho thích hợp với từng tài liệu. - Chọn xong nhấp OK Căn lề giấy cho trang in Từ Menu Bar vào File\Page Setup… Hộp thoại Page Setup hiện lên màn hình, từ hộp thoại này chọn Tab Margins (hình 2.139). + Top: định khoảng cách từ mép trên của trang giấy đến nội dung dữ liệu cần in. Trong Excel mặc định cho khoảng cách này là 1Inch. + Header: định khoảng cách của tiêu đề đầu trang. Trong Excel mặc định cho khoảng cách này là 0.5Inch. + Left: định khoảng cách từ mép trái của trang giấy đến nội dung của dữ liệu cần in. Trong Excel mặc định cho khoảng cách này là 0.75 Inch. + Hộp Right: định khoảng cách từ mép phải của trang giấy đến nội dung của dữ liệu cần in. Trong Excel mặc định cho khoảng cách này là 0.75 Inch. + Hộp Bottom: định khoảng cách từ mép dưới của trang giấy đến nội dung của dữ liệu cần in. Trong Excel mặc định cho khoảng cách này là 1 Inch. + Hộp Footer: định khoảng cách của tiêu đề cuối trang. Trong Excel mặc định cho khoảng cách này là 0.5Inch. + Chức năng Horizonbạnlly: Nếu nhấp chọn chức năng này thì Excel sẽ in dữ liệu ở giữa tờ giấy theo chiều ngang, ngược lại không chọn thì Excel sẽ in dữ liệu bên trái tờ giấy. + Chức năng Vertically: Nếu nhấp chọn chức năng này thì Excel sẽ in dữ liệu ở giữa tờ giấy theo chiều dọc, ngược lại không chọn thì Excel sẽ in dữ liệu bên trái tờ giấy. Nhấp các nút Print, Print Preview và Option để in bảng tính, xem bảng tính trước khi in và các tuỳ chọn trong khi in. - Điều chỉnh các chức năng trong hộp thoại cho thích hợp với từng tài liệu. - Chọn xong nhấp OK để áp dụng. In các tiêu đề đầu và cuối trang. - Từ Menu Bar vào File\Page Setup… Hộp thoại Page Setup hiện lên màn hình, từ hộp thoại này chọn nhãn Header/Footer (hình 2.131). + Header và Footer: chương trình đề nghị sử dụng mục có sẵn dùng để làm tiêu đề đầu hay cuối trang. Nhấp vào mũi tên hình tam giác để bật danh sách, chọn trong danh sách này một mục để dùng làm tiêu đề đầu hay cuối trang. + Custom Header: tạo tiêu đề đầu trang tuỳ ý. Nhấp vào nút Custom Header hộp thoại sau hiện lên màn hình như sau: Các biểu tượng trong hộp thoại Biểu tượng Mã Công dụng Biểu tượng này dùng để định dạng Font chữ cho tiêu đề, &[Page] In số trang hiện hành &[Page] In tổng số trang của bảng tính. &[Date] Hiển thị ngày hiện hành &[Time] Hiển thị giờ hiện hành &[Path]&[File] Hiển thị tên ổ đĩa chứa tập tin bảng tính và tên tập tin bảng tính đang sử dụng. &[File] Hiển thị tên tập tin bảng tính đang sử dụng. &[Tab] Hiển thị tên bảng tính hiện hành. &[Picture] Có tác dụng lấy hình ảnh từ bên ngoài vào. Có tác dụng chỉnh sửa hình ảnh được đưa vào ở bước trên. + Left Section: Khung này cho phép bạn nhập dữ liệu bất kỳ (chữ, số…) và nó sẽ hiển thị nội dung trong khung này lên góc trên bên trái của trang giấy. + Center Section: Khung này cho phép bạn nhập dữ liệu bất kỳ (chữ, số…) và nó sẽ hiển thị nội dung trong khung này lên giữa trang giấy. + Right Section: nhập dữ liệu bất kỳ (chữ, số…) và nó sẽ hiển thị nội dung trong khung này lên góc trên bên phải của trang giấy. + Custom Footer: Tương tự nút Custom Header. Nhấp các nút Print Preview và Option để in bảng tính, xem bảng tính trớc khi in và các tuỳ chọn trong khi in. - Lựa chọn các chức năng trong hộp thoại cho thích hợp với từng tài liệu. - Chọn xong nhấp OK để áp dụng. Lựa chọn bảng tính khi in - Chọn bảng tính cần xem trước - Chọn một trong những cách sau để thể hiện việc xem trước khi in bảng tính. - Từ Menu Bar vào File\Print Preview. - Nhập vào biểu tượng Print Preview trên thanh công cụ Standard. - Chọn nút Print Preview trong các hộp thoại Print và Page Setup. + Next: Nhấp vào nút này để xem trang kế sau trang hiện hành. + Previous: : Nhấp vào nút này để xem trang kế trước trang hiện hành. + Zoom: Phóng to hay thu nhỏ trang in trên màn hinh. + Print: Dùng để in bảng tính ra giấy. + Setup: Nhấp vào nút này sẽ hiển thị hộp thoại Page Setup. + Margins: Hiển thị các đường kẻ của các lề và các cột để bạn chỉnh các lề, chiều rộng các cột bằng cách nhấp chuột kéo rê tại lề hay các cột. + Page Break Preview: Nhấp vào nút này thì toàn bộ dữ liệu sẽ được phân trang và hiển thị ở chế độ soạn thảo bình thường. Để bỏ chế độ này: vào lại chế độ xem bảng tính trước khi in (Print Preview) và nhấp vào nút Normal View. + Close: Dùng màn hình xem bảng tính trước khi in và trở về màn hình trang bảng tính. + Help: Hướng dẫ về các nút lệnh trong màn hình xem bảng tính trước khi in. Thực hiện in bảng tính - Bật hộp thoại Print chọn một trong những các sau: - Từ Menu Bar vào File\Print… - In không định dạng: Nhấp vào biểu tượng Print trên thanh công cụ Sbạnndard - Nhấn Ctrl+ P. + Name: chọn lựa máy in bảng tính ra giấy. Nhấp vào mũi tên hình tam giác để bật danh sách các loại máy in và chọn một loại máy in thích hợp để in. + Properties: Nút này dùng để điều chỉnh các thông số cho máy in. + Print to File: in dữ liệu vào một File khác. Khi chọn chức năng này và nhấp OK thì xuất hiện hộp thoại Print to File, trong hộp thoại này bạn hãy đặt tên và định vị nơi để lưu tập tin mới, chọn xong hãy nhấn Ok để in dữ liệu tập File mới. + Print Range: Có các chức năng sau: + All: in tất cả các trang có trong bảng tính và bắt đầu từ trang đầu tiên của bảng tính. + Pages (s): in theo số trang được chỉ định sẵn trong hai hộp sau: + From: Chứa trang bắt đầu được in ra, nhấp chuột vào hai mũi tên trong hộp From để xác định số trang đầu hoặc bạn chèn con nháy vào hộp và nhập vào số trang đầu từ bàn phím. + To: Chứa trang cuối cùng được in ra, nhấp chuột vào hai mũi tên trong hộp To để xác định số trang cuối hoặc bạn chèn con nháy vào hộp và nhập vào số trang cuối từ bàn phím. + Print What: Selection: in theo khối đã chọn. Active Sheet (s): theo bảng tính đã chọn. Entire Workbook: in ra toàn bộ cửa sổ bảng tính đã chọn. + Copies: Number of Copies: in nhiều bảng như nhau. Nhấp chuột vào hai mũi tên trong hộp Number of Copies để xác định số bản in hoặc bạn chèn con nháy vào hộp và nhập vào số bản in từ bàn phím. Collate: hỗ trợ cho việc in nhiều bản sao. Nếu nhấp chọn chức năng này có nghĩa là in toàn bộ dữ liệu trong bảng tính rồi mới lặp lại và in bản sao thứ nhất của tài liệu cũng từ trang đầu đến trang cuối, tiếp tục in cho các bản sao tiếp theo. Ngược lại không chọn chức năng này thì Excel điều khiển in trang đầu tiên của tài liệu với tất cả số bản sao của trang đầu, in xong trang đầu và bản sao của trang đầu rồi mới in tới trang thứ hai và số bản sao của trang thứ hai, tiếp tục đến trang thứ ba… + Preview: xem bảng tính trước khi in. - Chọn các chức năng trong hộp thoại xong, nhấp OK để in bảng tính ra giấy, nhấp Cancel để huỷ bỏ lệnh. Phần 2 – Tổ chức số liệu kế toán trên Excel Phần này sẽ giúp các bạn làm kế toán trên Excel từ cách tổ chức dữ liệu đến việc tạo các sổ kế toán tổng hợp và chi tiết. Để nắm được phần này yêu cầu bạn phải nắm khá vững về nghiệp vụ kế toán doanh nghiệp và có một số kiến thức cơ bản về Microsoft office excel chủ yếu là phần đã được giới thiệu ở phần trên. Trước tiên ta tạo 1 Sheet chứa danh sách các sổ kế toán, các bảng biểu và các báo cáo của công ty để dễ dàng quản lý hơn, từ Sheet này bạn có thể rất nhanh chóng nhảy tới các Sheet khác bằng cách nhấp chuột. Cách làm như sau: - Khởi động Excel, chương trình sẽ tự động mở ra một Wookbook mới với mặc định là 3 Sheet trắng. Bạn vào Menu File\Save để lưu lại với tên KtExcel. 2.1. Sổ Nhật ký chung Đây là sổ lưu trữ tất cả các nghiệp vụ phát sinh của doanh nghiệp. Dữ liệu từ sổ này sẽ được sử dụng để tạo nên hầu hết tất cả các sổ sách và báo cáo của doanh nghiệp. 2.1.1. Thiết kế sổ Nhật ký chung Bước 1: Mở File KtExcel đã thiết kế ở trên, ở Sheet trắng bên cạnh Sheet Menu ta đổi tên thành SoNKC bằng cách nhấp chuột phải lên trên tên Sheet sau đó chọn Rename rồi nhập tên sổ. Bước 2: Thiết kế sổ Nhật ký chung Bạn di chuột về góc phía trên bên trái của sheet và nhập vào tên công ty. Sau đó di chuột xuống dòng bên dưới nhập vào tiêu đề của sổ sao cho đẹp mắt và dễ nhìn. Tiếp theo bạn tạo mẫu sổ theo mẫu dưới đây: 2.1.2. Cách nhập dữ liệu vào sổ Nhật ký chung Khi công ty có các chứng từ kế toán phát sinh như hóa đơn mua hàng, bán hàng, phiếu nhập kho, phiếu xuất kho… ta lần lượt nhập vào Sổ nhật ký chung với các thông tin có trên chứng từ kế toán: - Ngày tháng: Nhập ngày ghi sổ của chứng từ kế toán - Chứng từ: + Số hiệu: Nhập vào số hiệu của chứng từ kế toán + Ngày, tháng: Ngày tháng của chứng từ kế toán - Diễn giải: Nhập nội dung của chứng từ kế toán - TK Nợ: Nhập số hiệu tài khoản bên nợ của nghiệp vụ kế toán phát sinh trên chứng từ. - TK Có: Nhập số hiệu tài khoản bên có của nghiệp vụ kế toán phát sinh trên chứng từ. - Số lượng: Nhập số lượng hàng hóa, dịch vụ phát sinh trên chứng từ. - Số tiền phát sinh: Nhập số tiền hàng hóa, dịch vụ phát sinh trên chứng từ. Lưu ý: Với mỗi dòng trên Sổ nhật ký chung ta chỉ nhập một định khoản, với nghiệp vụ kế toán nhiều hơn một định khoản thì mỗi định khoản sẽ được nhập vào một dòng. Ví dụ: Hóa đơn thanh toán tiền điện thoại cho Viễn thông Hà Nội số 012345 ngày 10/01/2009 số tiền 500.000đ, thuế GTGT 50.000đ. Với nghiệp vụ này trong Sổ nhật ký chung bạn phải nhập thành hai dòng như sau: Nợ TK 642 500.000 Có TK 111 500.000 Và một dòng ghi nhận thuế GTGT đầu vào: Định khoản: Nợ TK 133 50.000 Có TK 111 50.000 Trong Sổ nhật ký chung sẽ được nhập vào như sau: Cuối tháng khi đã nhập hết các chứng từ phát sinh trong tháng ta sẽ làm các bút toán kết chuyển để xác định kết quả lãi lỗ. Ở hàng nhập nghiệp vụ kết chuyển cuối kỳ tại cột Số tiền phát sinh: - Với nghiệp vụ kết chuyển thuế GTGT được khấu trừ: =SUMIF($E$5:$E27;133;$G$5:$G27)+BCDSPS!D13 Trong đó BCDSPS!D13 chính là địa chỉ ô lưu số dư đầu kỳ của Tài khoản 133 trong Bảng cân đối số phát sinh sẽ được giới thiệu chi tiết sau. - Với nghiệp vụ kết chuyển doanh thu: Ví dụ với nghiệp vụ kết chuyển cuối kỳ là Nợ TK 511 Có TK 911 Ta nhập vào công thức sau: =SUMIF($F$7:$F25; 511; $H$7:$H25) Tương tự với bút toán kết chuyển Nợ TK 515/Có TK 911 ta cũng làm tương tự chỉ cần thay đổi điều kiện tính tổng. - Với các bút toán kết chuyển chi phí: Ví dụ với nghiệp vụ kết chuyển là Nợ TK 911 Có TK 642 Ta nhập vào công thức sau: =SUMIF($E$7:$E25;642;$G$7:$G25) Tương tự với các bút toán kết chuyển chi phí khác ta cung làm tương tự chỉ cần thay đổi điều kiện tính tổng. - Với bút toán kết chuyển cuối cùng là kết chuyển lỗ, lãi: + Nếu lãi: Tại hàng nhập bút toán kết chuyển lãi ta nhập vào cột Số tiền phát sinh (ví dụ là ô G32) công thức: =SUMIF($F$7:$F31;911;$G$7:$G31)- SUMIF($E$7:$E31;911;$G$7:$G31) + Nếu lỗ: Tại hàng nhập bút toán kết chuyển lãi ta nhập vào cột Số tiền phát sinh (ví dụ là ô G32) công thức: =SUMIF($E$7:$E31;911;$G$7:$G31) SUMIF($F$7:$F31;911;$G$7:$G31) Lưu ý: Tùy theo số lượng chứng từ phát sinh mà vùng tham chiếu và vùng tính tổng trong các hàm trên sẽ có sự thay đổi. Hàm trên chỉ có tính chất tham khảo. 2.2. Bảng cân đối số phát sinh 2.2.1. Thiết kế Danh mục tài khoản Trước khi thiết kế Bảng cân đối số phát sinh, ta sẽ tạo một bảng danh mục tài khoản để tiện cho các công việc về sau. Bảng danh mục tài khoản được thiết kế như sau: - Thêm một sheet vào trước sheet SoNKC, đổi tên sheet đó thành DMTK. Thiết kế bảng danh mục tài khoản như sau: Tiếp đó bạn nhập các tài khoản và tên tài khoản tương ứng vào cột TK và Tên TK. Ở cột Tên sổ cái bạn nhập bằng chữ in và thêm vào trước tên tài khoản chữ “SỔ CÁI”. Ví dụ: Tương ứng với tài khoản 111 thì Tên sổ cái là “SỔ CÁI TK 111 - TIỀN MẶT”. Mục đích của việc này là khi ta thiết kế sổ cái cho các tài khoản thì ứng với mỗi tài khoản thì tên của sổ cái sẽ được lấy ra từ đây để ta đỡ mất công nhập đi nhập lại. 2.2.2. Thiết kế Bảng cân đối số phát sinh Chuyển sang sheet bên cạnh sheet SoNKC sau đó đổi tên sheet này thành BCDSPS. Thiết kế Bảng cân đối số phát sinh trên sheet BCDSPS như sau: 2.2.3. Xử lý dữ liệu trong Bảng cân đối số phát sinh Tại cột Mã TK ta lần lượt nhập theo thứ tự trong bảng danh mục tài khoản các tài khoản phát sinh trong kỳ kế toán. Tại cột Tên Tài khoản ta nhập vào công thức sau: =IF(TYPE(VLOOKUP($C9;TAIKHOAN;2;0))=16;0; VLOOKUP($C9;TAIKHOAN;2;0)) Hàm này sẽ trả về tên của các tài khoản tương ứng với mã tài khoản được nhập trong cột Mã TK. Di chuột về góc dưới bên phải của ô vừa nhập công thức đến lúc chữ thập màu trắng chuyển thành màu đen (Fill handle), giữ và kéo chuột đến hết hàng có số hiệu tài khoản tương ứng ở bên cột Mã TK. Trong hàm nhập tại cột Tên Tài khoản ở phần trên TAIKHOAN là một bảng tham chiếu thuộc bảng Danh mục tài khoản, sử dụng kiểu bảng tham chiếu này rất tiện lợi vì bất cứ khi nào muốn dùng bảng tham chiếu này bạn chỉ việc gõ đúng tên bảng tham chiếu này trong các hàm cần sử dụng đến bảng tham chiếu. Bảng tham chiếu này được tạo như sau: Vào menu Insert/Name/Define… Hộp thoại Define Name xuất hiện Trong hộp Names in workbook nhập tên bảng tham chiếu. Trong hộp Refers to nhập vào dấu bằng (=) Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn vùng tham chiếu trên bất cứ sheet nào có dữ liệu mà bạn muốn tạo vùng tham chiếu. Ở đây ta tạo vùng tham chiếu ở bảng Danh mục tài khoản thuộc sheet DMTK. Bảng tham chiếu TAIKHOAN ở đây sẽ là A4:C83 trong bảng Danh mục tài khoản trong sheet DMTK. Nhấp chuột vào OK. Bảng tham chiếu này sẽ còn được sử dụng nhiều trong các hàm ở các sổ kế toán sau. - Cột Số dư đầu kỳ: + Bên Nợ: Nhập số dư Nợ đầu kỳ của các tài khoản có số dư nợ. Số dư Nợ đầu kỳ này chính là số dư nợ cuối kỳ trước. + Bên Có: Nhập số dư Có đầu kỳ của các tài khoản có số dư có. Số dư Có đầu kỳ này chính là số dư Có cuối kỳ trước. Lưu ý: Nếu đây là Bảng cân đối phát sinh đầu tiên bạn lập thì bạn phải nhập số dư nợ đầu kỳ của các tài khoản phát sinh vào. Nhưng nếu bạn đã có Bảng cân đối phát sinh của kỳ trước thì bạn chỉ việc lấy dữ liệu từ bảng cân đối phát sinh của kỳ kế toán trước bằng cách nhập vào cột Số dư đầu kỳ: Bên Nợ: =VLOOKUP(C9;[KtExcel1.xls]BCDSPS!C$9:I$39;6;0) Bên Có: =VLOOKUP(C9;[KtExcel1.xls]BCDSPS!C$9:I$39;7;0) Trong đó KtExcel1.xls là file sổ kế toán của kỳ kế toán trước. Sau đó dung Fill handle kéo xuống hết bảng cân đối tài khoản. - Cột Số phát sinh trong kỳ: + Bên Nợ: Nhập vào ô F9 hàm sau: =SUMIF(SoNKC!$E$7:$E$32;C9;SoNKC!$G$7:$G$32) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. + Bên Có: Nhập vào ô G9 hàm sau: =SUMIF(SoNKC!$F$7:$F$32;C9;SoNKC!$G$7:$G$32) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. - Cột Số dư cuối kỳ: + Bên Nợ: Nhập vào ô H9 hàm sau: =IF((D9+F9)-(E9+G9)<0;0;(D9+F9)-(E9+G9)) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. + Bên Có: Nhập vào ô I9 hàm sau: =IF((E9+G9)-(D9+F9)<0;0;(E9+G9)-(D9+F9)) Sau đó dùng Fill handle kéo xuống hết Bảng cân đối tài khoản. Tại hàng cuối của Bảng cân đối số phát sinh ở cột Số dư đầu kỳ bên Nợ nhập hàm: =SUM(D9:D39) Sau đó dùng Fill handle kéo sang ngang cho hết Bảng cân đối số phát sinh. Bạn phải lưu ý rằng một điều rất cơ bản của Bảng cân đối số phát sinh là: Số dư đầu kỳ bên Nợ = Số dư đầu kỳ bên Có Số phát sinh trong kỳ bên Nợ = Số phát sinh trong kỳ bên Có Số dư cuối kỳ bên Nợ = Số dư cuối kỳ bên Có Vì vậy khi bạn thấy tổng bên Nợ và bên Có của cột Số dư đầu kỳ, Số phát sinh trong kỳ hoặc Số dư cuối kỳ thì phải kiểm tra lại ngay. 2.3. Sổ cái tài khoản 2.3.1. Thiết kế sổ cái tài khoản Ta sẽ có 3 mẫu sổ cái: - Sổ cái tài khoản có số dư bên Nợ - Sổ cái tài khoản có số dư bên Có - Sổ cái tài khoản không có số dư Sổ cái có số dư bên nợ Đổi tên sheet 3 thành SoCai1. Sổ cái tài khoản có số dư bên Nợ sẽ có mẫu như sau: Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong Sổ cái như sau: Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu Data/Filter/AutoFilter. Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - Số dư Nợ: Số dư nợ của tài khoản trên sổ cái hiện hành - SDĐK: Số dư đầu kỳ - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ - SDCK: Số dư cuối kỳ Sổ cái có số dư bên Có Thêm một sheet bên sheet SoCai1, đặt tên là SoCai2. Sổ cái tài khoản có số dư bên Có sẽ có mẫu như sau: Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - Số dư Có: Số dư Có của tài khoản trên sổ cái hiện hành - SDĐK: Số dư đầu kỳ - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ - SDCK: Số dư cuối kỳ Sổ cái không có số dư Thêm một sheet bên sheet SoCai2, đặt tên là SoCai3. Sổ cái tài khoản không có số dư có mẫu như sau: Trong đó: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ cái hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản trên sổ cái hiện hành. - Ghi Có: Tài khoản đối ứng bên Có của tài khoản trên sổ cái hiện hành. - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ Lưu ý: Bạn phải dùng AutoFilter cho vùng dữ liệu trong các Sổ cái như sau: Chọn cả vùng sẽ chứa dữ liệu trong sổ cái sau đó vào menu Data/Filter/AutoFilter. 2.3.2. Xử lý dữ liệu trên sổ cái tài khoản Ta thấy 3 mẫu sổ cái trên khá giống nhau và cách xử lý dữ liệu trên cả 3 sổ này cũng vậy, chúng chỉ hơi khác nhau một chút về cách lấy dữ liệu về số dư đầu và cuối từ một cơ sở dữ liệu khác (cụ thể là từ Bảng cân đối số phát sinh). Sổ cái có số dư bên Nợ Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như sau: Nhấp chuột vào ô A7 sau đó vào menu Format/Cells. Hộp thoại Format Cells xuất hiện ta chọn trong hộp Category mục Custom sau đó trong hộp danh sách định dạng bên cạnh ta chọn định dạng là ba dấu chấm phẩy (;;;). Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô I8 (Số dư nợ đầu kỳ) ta nhập vào hàm sau: =VLOOKUP($A$7,BCDSPS!$C$9:$I$39,2,0) Hàm này sẽ trả về số dư nợ đầu kỳ của tài khoản mà bạn đã nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet BCDSPS. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!$G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!$H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. - Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =I8+G40-H40 Hàm này sẽ trả về số dư Nợ cuối kỳ của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàng. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). Sổ cái có số dư bên Có Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô I8 (Số dư Có đầu kỳ) ta nhập vào hàm sau: =VLOOKUP($A$7,BCDSPS!$C$9:$I$39,3,0) Hàm này sẽ trả về số dư Có đầu kỳ của tài khoản mà bạn đã nhập vào ô A7 được lấy từ Bảng cân đối tài khoản trong sheet BCDSPS. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. - Tại ô I41 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =I8+G40-H40 Hàm này sẽ trả về số dư Có cuối kỳ của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàng. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). Sổ cái không có số dư Nhập vào ô A7 tài khoản trên sổ cái mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái có số dư bên Nợ. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. Trên dòng tiêu đề sổ ta nhập vào hàm sau: =VLOOKUP($A$7,TAIKHOAN,3,0) Hàm này sẽ trả về tên của sổ cái tương ứng với tài khoản mà bạn đã nhập vào ô A7. - Tại ô A10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!A7," ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D10 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!E7,"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!F7,"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G10 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7,SoNKC!G7,"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H10 nhập vào hàm sau: =IF(SoNKC!$F7=$A$7,SoNKC!H7,"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. Đặt chuột vào ô A10, kéo và giữ chuột đến ô H10, dùng Fill handle kéo chuột xuống hết Sổ cái tài khoản. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. - Tại ô H40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(H9:H39) Hàm này sẽ trả về tổng phát sinh có của tài khoản đã nhập vào ô A7. Lưu ý: Mẫu sổ cái thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàm trong hàng đó. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). 2.4. Sổ chi tiết tài khoản Sổ chi tiết tài khoản là sổ chi tiết của một tài khoản, nhìn vào sổ chi tiết tài khoản ta có thể biết số phát sinh của các tài khoản con của tài khoản đó. Tổng phát sinh của các tài khoản con của một tài khoản bằng số phát sinh của tài khoản đó. 2.4.1. Thiết kế sổ chi tiết tài khoản Thêm vào một sheet bên cạnh sheet SoCai3 đặt tên là SCT_Tài khoản tương ứng, ví dụ SCT_642. Sổ chi tiết tài khoản bao gồm các cột sau: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. + Ngày: Ngày chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - Diễn giải: Nội dung chứng từ kế toán phát sinh mà nghiệp vụ phản ánh có tài khoản trên sổ chi tiết hiện hành. - TK Đối ứng: Tài khoản đối ứng với tài khoản trên sổ chi tiết hiện hành trong nghiệp vụ kế toán phát sinh. - Mã CP: Mã tài khoản con của tài khoản trên sổ chi tiết hiện hành. - Ghi nợ TK: Giá trị phát sinh bên Nợ của tài khoản trên sổ chi tiết hiện hành. Tùy thuộc là sổ chi tiết của tài khoản nào ta sẽ ghi số hiệu tài khoản đó. Ví dụ: Ghi nợ TK642. - Các cột Mã của tài khoản con: Tùy thuộc vào tài khoản trên sổ chi tiết có những tài khoản con nào mà sẽ có bấy nhiêu cột. Ví dụ như tài khoản 642 có các cột như: + CPPB: Chi phí trả trước phân bổ vào chi phí quản lý trong kỳ. + CPKH: Chi phí khấu hao tài sản cố định trích vào chi phí quản lý trong kỳ. + CPMN: Chi phí dịch vụ mua ngoài phục vụ quản lý trong kỳ. + CPDT: Chi phí điện thoại phục vụ nhu cầu quản lý trong kỳ. + CPK: Chi phí bằng tiền khác phục vụ quản lý trong kỳ. + CPTL: Chi phí tiền lương nhân viên quản lý. … Lưu ý: Đặt tên của các cột này phải giống hệt mã tài khoản con của tài khoản trên sổ chi tiết hiện hành. Những mã này được nhập trên cột Mã KH trong Sổ nhật ký chung tương ứng với các nghiệp vụ phát sinh có tài khoản phản ánh là tài khoản trên sổ chi tiết. - Ghi chú: Để ghi chú những gì cần thiết. - SPSTK: Số phát sinh trong kỳ - Cộng SPS: Cộng số phát sinh trong kỳ Chọn toàn bộ vùng dữ liệu trong sổ chi tiết sau đó vào menu Data/Filter/Autofilter Vậy là ta đã thiết lập xong chức năng lọc Autofilter cho sổ chi tiết. 2.4.2. Xử lý dữ liệu trong sổ chi tiết tài khoản Lưu ý trước tiên khi làm sổ chi tiết tài khoản là ta phải nhập mã cho tài khoản của sổ chi tiết trên Sổ nhật ký chung. Điều này rất dễ dàng, chỉ cần khi nhập dữ liệu vào Sổ nhật ký chung, với các nghiệp vụ phát sinh có tài khoản phản ánh mà sau này cần mở sổ chi tiết ta chỉ việc nhập thêm mã cho tài khoản đó vào cột Mã KH trong Sổ nhật ký chung. Cột này khi in sẽ được ẩn đi, ta chỉ sử dụng cột này cho mục đích xử lý dữ liệu được dễ dàng. Dữ liệu trên sổ chi tiết đều được lấy từ Sổ nhật ký chung. Ở đây sẽ giới thiệu Sổ chi tiết tài khoản 642 – Chi phí quản lý kinh doanh. Các sổ chi tiết khác làm tương tự, chỉ hơi khác một chút nhưng nếu hiểu cách làm của sổ chi tiết 642 bạn sẽ dễ dàng làm được các sổ chi tiết tài khoản khác. Nhập vào ô A7 tài khoản sổ chi tiết mà bạn muốn xem. Ô A7 được định dạng như ô A7 của Sổ cái. Định dạng này sẽ giúp ẩn dữ liệu trong ô A7 đi không xuất hiện trên sổ nhưng vẫn tồn tại. - Tại ô A9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7;SoNKC!$F7=$A$7);SoNKC!A7;" ") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô B9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!B7," ") Hàm này sẽ trả về số hiệu của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô C9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!C7," ") Hàm này sẽ trả về ngày tháng của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô D9 nhập vào hàm sau: =IF(OR(SoNKC!$E7=$A$7,SoNKC!$F7=$A$7),SoNKC!D7," ") Hàm này sẽ trả về nội dung của chứng từ phát sinh mà nghiệp vụ phản ánh có chứa tài khoản đã nhập vào ô A7. - Tại ô E9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!$F7;IF(SoNKC!$F7=$A$7; SoNKC!$E7;"")) Hàm này sẽ trả về tài khoản đối ứng của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô F9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!I7;"") Hàm này sẽ trả về mã của tài khoản trên Sổ chi tiết đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô G9 nhập vào hàm sau: =IF(SoNKC!$E7=$A$7;SoNKC!$G7;"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản đã nhập vào ô A7, được lấy từ sổ Nhật ký chung. - Tại ô H9 nhập vào hàm sau: =IF($F9=H$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPPB, được lấy từ sổ Nhật ký chung. - Tại ô I9 nhập vào hàm sau: =IF($F9=I$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPKH, được lấy từ sổ Nhật ký chung. - Tại ô J9 nhập vào hàm sau: =IF($F9=J$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPMN, được lấy từ sổ Nhật ký chung. - Tại ô K9 nhập vào hàm sau: =IF($F9=K$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPDT, được lấy từ sổ Nhật ký chung. - Tại ô L9 nhập vào hàm sau: =IF($F9=L$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPK, được lấy từ sổ Nhật ký chung. - Tại ô M9 nhập vào hàm sau: =IF($F9=M$5;$G9;"") Hàm này sẽ trả về số phát sinh của tài khoản có mã CPTL, được lấy từ sổ Nhật ký chung. - Tại ô N9 nhập vào hàm sau: =IF(SoNKC!F7=$A$7;SoNKC!H7;"") Hàm này sẽ trả về số phát sinh bên có của tài khoản trên Sổ chi tiết, được lấy từ sổ Nhật ký chung. - Tại ô G40 (tùy theo Sổ nhật ký chung có bao nhiêu nghiệp vụ phát sinh mà chỉ số cột của ô này sẽ thay đổi) nhập vào hàm sau: =SUM(G9:G39) Hàm này sẽ trả về tổng phát sinh nợ của tài khoản đã nhập vào ô A7. Dùng Fill handle kéo từ ô G40 đến ô N40. Đây là hàng tính tổng số phát sinh của các tài khoản con của tài khoản trên sổ chi tiết. Riêng ô N40 là tổng phát sinh bên Có của tài khoản trên sổ chi tiết đã nhập vào ô A7. Lưu ý: Mẫu sổ chi tiết thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Đặt chuột vào ô A9, giữ và kéo chuột đến ô N9, dùng Fill handle kéo đến hết sổ. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàm trong những hàng đó. Bạn di chuột đến nút có hình tam giác ở bên phải ô D10, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). 2.5. Sổ tổng hợp, chi tiết công nợ phải thu, phải trả Trước khi tạo sổ tổng hợp và chi tiết công nợ phải, phải trả ta sẽ thiết kế 1 bảng danh mục khách hàng và 1 bảng danh mục nhà cung cấp để tiện cho việc theo dõi công nợ. 2.5.1. Bảng danh mục khách hàng - Bên cạnh sheet DMTK thêm vào 1 sheet và đặt tên sheet là DMKH. - Thiết kế một bảng Danh mục khách hàng như bảng sau: - Với mỗi khách hàng bạn hãy tạo một mã để phân biệt và tuyệt đối không được có mã trùng nhau. - Nhập mã khách hàng vào cột Mã KH và tên khách hàng vào cột Tên khách hàng. - Sau khi tạo xong bảng Danh mục khách hàng ta sẽ tạo một bảng tham chiếu là bảng Danh mục khách hàng này để tiện sử dụng. Cách tạo bảng tham chiếu như sau: - Vào menu Insert/Name/Define… Hộp thoại Define Name xuất hiện Trong hộp Names in workbook nhập tên bảng tham chiếu. Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn vùng tham trong bảng Danh mục tài khoản. Nhấp chuột vào nút đóng màu đỏ của hộp Define Name – Refers to. Nhấp chuột vào OK. Vậy là ta đã có một bảng tham chiếu có tên là DMKH. 2.5.2. Bảng danh mục nhà cung cấp - Bên cạnh sheet DMKH thêm vào 1 sheet và đặt tên sheet là DMNCC. - Thiết kế bảng Danh mục nhà cung cấp như sau: - Với mỗi nhà cung cấp bạn hãy tạo một mã để phân biệt và tuyệt đối không được có mã trùng nhau. - Nhập mã nhà cung cấp vào cột Mã NCC và tên nhà cung cấp vào cột Tên nhà cung cấp. - Sau khi tạo xong bảng Danh mục nhà cung cấp ta sẽ tạo một bảng tham chiếu là bảng Danh mục nhà cung cấp này để tiện sử dụng. Cách tạo bảng tham chiếu như sau: - Vào menu Insert/Name/Define… Hộp thoại Define Name xuất hiện Trong hộp Names in workbook nhập tên bảng tham chiếu. Nhấp chuột vào nút ở cuối hộp Refers to, sau đó bạn hãy chọn vùng tham trong bảng Danh mục nhà cung cấp. Nhấp chuột vào nút đóng màu đỏ của hộp Define Name – Refers to. Nhấp chuột vào OK. Vậy là ta đã có một bảng tham chiếu có tên là DMNCC. 2.5.3. Sổ tổng hợp công nợ phải thu Thiết kế sổ tổng hợp công nợ phải thu Thêm vào một sheet đặt tên là BTH131. Sổ tổng hợp công nợ phải thu bao gồm các cột sau: - STT: Số thứ tự khách hàng có phát sinh công nợ. - Mã khách: Mã khách hàng có công nợ. - Tên khách hàng: Tên khách hàng có công nợ. - Số dư đầu kỳ: + Nợ: Số dư Nợ TK 131 chi tiết theo từng khách hàng khách hàng (Số còn phải thu đầu kỳ) + Có: Số dư Có TK 131 chi tiết theo từng khách hàng (Số tiền ứng trước của khách hàng ở thời điểm đầu kỳ) - Số phát sinh trong kỳ: + Nợ: Phát sinh Nợ TK 131 chi tiết theo từng khách hàng (nghiệp vụ bán hàng hóa, dịch vụ). + Có: Phát sinh Có TK 131 chi tiết theo từng khách hàng (Thu tiền bán hàng hóa dịch vụ) - Số dư cuối kỳ: + Nợ: Số dư Nợ TK 131 chi tiết theo từng khách hàng khách hàng (Số còn phải thu cuối kỳ) + Có: Số dư Có TK 131 chi tiết theo từng khách hàng (Số tiền ứng trước của khách hàng ở thời điểm cuối kỳ) Mẫu Sổ tổng hợp công nợ phải thu của khách hàng như sau: Xử lý dữ liệu trên Sổ tổng hợp công nợ phải thu - Nhập vào Ô B9 những mã khách hàng có công nợ. - Nhập vào Ô C9 hàm sau: =Vlookup(B9; DMKH; 2; 0) Hàm này sẽ trả về tên khách hàng (DMKH là bảng tham chiếu đã được hướng dẫn lập ở trên). - Nhập vào Ô D9 hàm sau: =VLOOKUP($B9;'[KtExcel1.xls]BTH131'!$B$9:$I$16;7;0) Trong đó: KtExcel1.xls là file kế toán của kỳ kế toán trước (cấu trúc giống hệt kỳ này). Hàm này sẽ trả về giá trị số dư Nợ đầu kỳ chi tiết theo từng khách hàng lấy từ Bảng tổng hợp công nợ phải thu của kỳ kế toán trước (cột Số dư cuối kỳ bên Nợ) - Ô E9 không cần nhập gì cả, bình thường số dư bên Có của tài khoản 131 là khoản tiền người mua hàng ứng trước. Nhưng ở đây nếu số dư bên Nợ đầu kỳ của tài khoản công nợ phải thu mà âm thì ta sẽ tự hiểu đó là do người mua hàng ứng tiền trước. - Ô F9 sẽ lấy dữ liệu từ sổ chi tiết của từng khách hàng. Giả sử tại ô B9 mã khách là THIENTAN (Cty CP Thiên Tân), Công ty CP Thiên Tân có sổ chi tiết khách hàng là CT131_THIENTAN thì tại ô F9 ta sẽ nhập hàm sau: =CT131_THIENTAN!G41 Trong đó G41 là ô tổng phát sinh bên Nợ của tài khoản 131 chi tiết theo khách hàng là Công ty CP Thiên Tân. - Ô G9 sẽ lấy dữ liệu từ sổ chi tiết của từng khách hàng. Giả sử tại ô B9 mã khách là THIENTAN (Cty CP Thiên Tân), Công ty CP Thiên Tân có sổ chi tiết khách hàng là CT131_THIENTAN thì tại ô G9 ta sẽ nhập hàm sau: =CT131_THIENTAN!H41 Trong đó H41 là ô tổng phát sinh bên Có của tài khoản 131 chi tiết theo khách hàng là Công ty CP Thiên Tân. - Ô H9 nhập vào công thức sau: =D9+F9-G9 Công thức này sẽ trả về số dư Nợ cuối kỳ của tài khoản 131 chi tiết theo từng khách hàng. Nếu giá trị trả về là 1 số âm thì ta có thể hiểu đó là số dư bên Có của tài khoản 131 (người mua ứng trước) chi tiết theo từng khách hàng. 2.5.4. Sổ chi tiết công nợ phải thu Thiết kế Sổ chi tiết công nợ phải thu Thêm vào một sheet bên cạnh sheet đặt tên là CT131_Mã khách hàng. Ví dụ: CT131_THIENTAN. Mỗi khách hàng sẽ được làm trên 1 sheet riêng. Sổ chi tiết tài khoản bao gồm các cột sau: - Ngày tháng: Ngày tháng ghi sổ của chứng từ kế toán có phát sinh công nợ phải thu của khách hàng trên Sổ chi tiết công nợ hiện hành. - Chứng từ: + SH: Số hiệu chứng từ kế toán có phát sinh công nợ phải thu của khách hàng trên sổ chi tiết công nợ hiện hành. + Ngày: Ngày chứng từ kế toán có phát sinh công nợ phải thu của khách hàng trên sổ chi tiết công nợ hiện hành. - Diễn giải: Nội dung chứng từ kế toán có phát sinh công nợ phải thu của khách hàng trên sổ chi tiết công nợ hiện hành. - Ghi Nợ: Tài khoản đối ứng bên Nợ của tài khoản công nợ của khách hàng trên Sổ chi tiết công nợ hiện hành. - Ghi Có: Tài khoản đối ứng bên có của tài khoản công nợ của khách hàng trên Sổ chi tiết công nợ hiện hành. - Số phát sinh: + Nợ: Số phát sinh bên Nợ của tài khoản công nợ của khách hàng trên Sổ chi tiết công nợ hiện hành. + Có: Số phát sinh bên Nợ của tài khoản công nợ của khách hàng trên Sổ chi tiết công nợ hiện hành. - Số dư: Số dư đầu, sau mỗi lần phát sinh công nợ, số dư cuối tài khoản công nợ của khách hàng trên Sổ chi tiết công nợ hiện hành. Mẫu Sổ chi tiết công nợ phải thu của khách hàng như sau: Sau khi tạo mẫu sổ xong, bôi đen toàn bộ vùng dữ liệu của sổ chi tiết công nợ phải thu sau đó vào menu Data/Filter/ AutoFilter để thiết lập chức năng lọc dữ liệu AutoFilter. Xử lý dữ liệu trên Sổ chi tiết công nợ phải thu Dữ liệu trên sổ chi tiết đều được lấy từ Sổ nhật ký chung. - Tại ô G3 nhập vào mã khách hàng trên Sổ chi tiết công nợ phải thu. Ví dụ ở sheet CT131_THIENTAN thì sẽ nhập vào ô G3 mã khách hàng là THIENTAN. - Tại ô C3 nhập vào hàm sau: =VLOOKUP($G$3;DMKH;2;0) Trong đó DMKH là bảng tham chiếu trong bảng Danh mục khách hàng đã hướng dẫn lập ở phần trên. Hàm này sẽ trả về tên của khách hàng có mã đã được nhập vào ô G3. =VLOOKUP($G$3;BTH131!$B$9:$I$16;3;0) Hàm này sẽ trả về số dư đầu kỳ của tài khoản 131 chi tiết theo khách hàng có mã đã được nhập vào ô G3. - Tại ô A6 nhập vào số hiệu tài khoản công nợ phải thu là 131. Ô A6 được định dạng như sau: Nhấp chuột vào ô A6 sau đó vào menu Format/Cells. Hộp thoại Format Cells xuất hiện ta chọn trong hộp Category mục Custom sau đó trong hộp danh sách định dạng bên cạnh ta chọn định dạng là ba dấu chấm phẩy (;;;). Định dạng này sẽ giúp ẩn dữ liệu trong ô A6 đi không xuất hiện trên sổ nhưng vẫn tồn tại. - Tại ô A9 nhập vào hàm sau: =IF(OR(AND(SoNKC!$I7=$G$3;SoNKC!$E7=$A$6);AND(S oNKC!$I7=$G$3;SoNKC!$F7=$A$6));SoNKC!$A7;"") Hàm này sẽ trả về ngày tháng ghi sổ của chứng từ kế toán mà nghiệp vụ phản ánh có tài khoản 131 chi tiết theo khách hàng có mã khách đã nhập vào ô G3. - Tại ô B9 nhập vào hàm sau: =IF(OR(AND(SoNKC!$I7=$G$3;SoNKC!$E7=$A$6);AND(S oNKC!$I7=$G$3;SoNKC!$F7=$A$6));SoNKC!$B7;"") Hàm này sẽ trả về số hiệu của chứng từ kế toán mà nghiệp vụ phản ánh có tài khoản 131 chi tiết theo khách hàng có mã khách đã nhập vào ô G3. - Tại ô C9 nhập vào hàm sau: =IF(OR(AND(SoNKC!$I7=$G$3;SoNKC!$E7=$A$6);AND(S oNKC!$I7=$G$3;SoNKC!$F7=$A$6));SoNKC!$C7;"") Hàm này sẽ trả về ngày tháng phát sinh của chứng từ kế toán mà nghiệp vụ phản ánh có tài khoản 131 chi tiết theo khách hàng có mã khách đã nhập vào ô G3. - Tại ô D9 nhập vào hàm sau: =IF(OR(AND(SoNKC!$I7=$G$3;SoNKC!$E7=$A$6);AND(S oNKC!$I7=$G$3;SoNKC!$F7=$A$6));SoNKC!$D7;"") Hàm này sẽ trả về nội dung diễn giải của chứng từ kế toán mà nghiệp vụ phản ánh có tài khoản 131 chi tiết theo khách hàng có mã khách đã nhập vào ô G3. - Tại ô E9 nhập vào hàm sau: =IF(AND(SoNKC!$F7=$A$6;SoNKC!$I7=$G$3); SoNKC!$E7;"") Hàm này sẽ trả về tài khoản đối ứng bên Nợ của tài khoản công nợ phải thu chi tiết theo khách hàng. - Tại ô F9 nhập vào hàm sau: =IF(AND(SoNKC!$E8=$A$6;SoNKC!$I8=$G$3); SoNKC!$F8;"") Hàm này sẽ trả về tài khoản đối ứng bên Có của tài khoản công nợ phải thu chi tiết theo khách hàng. - Tại ô G9 nhập vào hàm sau: =IF(AND(SoNKC!$E7=$A$6;SoNKC!$I7=$G$3); SoNKC!$G7;"") Hàm này sẽ trả về giá trị phát sinh bên Nợ của tài khoản công nợ phải thu chi tiết theo khách hàng. - Tại ô H9 nhập vào hàm sau: =IF(AND(SoNKC!$F7=$A$6;SoNKC!$I7=$G$3); SoNKC!$H7;"") Hàm này sẽ trả về giá trị phát sinh bên Có của tài khoản công nợ phải thu chi tiết theo khách hàng. - Tại ô I9 nhập vào công thức sau: = I7+G9-H9 Tại ô I10 nhập vào công thức sau: = I9+G10-G11 Đặt chuột vào ô I10 dùng Fill handle kéo hết sổ. - Tại ô G41 nhập hàm sau: =SUM(G9:G40) Hàm này trả về tổng phát sinh Nợ của tài khoản công nợ chi tiết theo khách hàng. - Tại ô H41 nhập hàm sau: =SUM(H9:H40) Hàm này trả về tổng phát sinh Có của tài khoản công nợ chi tiết theo khách hàng. Lưu ý: Mẫu sổ chi tiết thiết kế phải có số dòng nhiều hơn hoặc tốt nhất là bằng số hàng chứa dữ liệu bên Sổ nhật ký chung. Điều này để chắc chắn rằng sổ cái sẽ lấy đầy đủ dữ liệu. Đặt chuột vào ô A9, giữ và kéo chuột đến ô H9, dùng Fill handle kéo đến hết sổ. Sau khi kéo Fill handle Sổ cái sẽ có những hàng không có dữ liệu xen lẫn những hàng có dữ liệu. Những hàng không có dữ liệu là do không thỏa mãn điều kiện của hàm trong những hàng đó. Bạn di chuột đến nút có hình tam giác ở bên phải ô D8, nhấp chuột vào đó sẽ có một danh sách những nội dung diễn giải các nghiệp vụ phát sinh. Di chuột đến dòng cuối cùng có chữ (Nonblanks), nhấp chuột vào đó. Sau khi nhấp chuột vào Nonblanks thì AutoFilter sẽ lọc ra và chỉ cho hiển thị những dòng nào có dữ liệu (không trống). (Các sổ chi tiết công nợ của các khách hàng khác làm tương tự)