2. CÁC KIẾN THỨC CẦN BIẾT ĐỐI VỚI EXCEL.
Trước tiên, bạn cần nắm lại một số kiến thức cơ bản về Excel như:
- Thao tác xử lý tập tin (tạo mới, lưu, mở, đóng, in ấn, xóa... bằng cách mở menu File, chọn lệnh tương ứng).
- Thao tác về bảng tính như chèn, xóa cột hàng, đặt tên khối, tên vùng dữ liệu, tên sheet, sao chép dữ liệu, công thức, thiết lập giá trị, font chữ mặc định...
- Thao tác sắp xếp dữ liệu, trích lọc dữ liệu cơ bản và nâng cao, trích lọc có điều kiện phức tạp, vẽ đồ thị...
- Thao tác tạo subtotal, liên kết dữ liệu từ nhiều sheet khác nhau...
- Thao tác định dạng bảng tính, dàn trang bảng tính, xử lý in ấn...
Tiếp theo, bạn cần nắm vững một số hàm cơ bản sau đây để có thể thiết lập các công thức tính toán trong Excel:
- Hàm dạng văn bản: hàm LEFT(text, n); hàm RIGHT(text, n); hàm MID(text, kt, n).
- Hàm dạng logic: hàm AND(đk1, đk2, đk3...); hàm OR(đk1, đk2, đk3...); hàm IF(đk, gt đúng, gt sai).
- Hàm dạng ngày tháng: hàm DAY(ngày); MONTH(ngày); YEAR(ngày); TODAY.
- Hàm dạng thống kê, tính tổng: hàm SUM(gt1, gt2, gt3,...); hàm MAX(gt1, gt2, gt3,...); hàm MIN(gt1, gt2, gt3,...), hàm AVERAGE(gt1, gt2, gt3,...), hàm COUNT(gt1, gt2, gt3,...).
Hàm SUMIF (range, criteria, sumrange); hàm COUNTIF(range, crirteria); hàm DSUM(database, fieldnumber, criteria); DCOUNTA(database, fieldnumber, criteria).
- Hàm dạng toán học: hàm INT(giatri / n); hàm MOD (giatri, n); hàm ROUND(giatri, n).
- Hàm dạng tra cứu: hàm VLOOKUP(lookupvalue, table array, column index number, range lookup); hàm HLOOKUP(lookupvalue, table array, column index number, range lookup).
Với những hàm đơn giản này, giả định bạn đã biết cách sử dụng trong từng trường hợp cụ thể. Ở đây, chúng ta sẽ lồng ghép các hàm này thành những công thức tính toán cụ thể để xử lý các số liệu kế toán.
n VẬN DỤNG KẾ TOÁN VÀ EXCEL ĐỂ VIẾT PHẦN MỀM QUẢN LÝ
1. Tạo 2 sổ kế toán chứa dữ liệu gốc ban đầu (Sổ Kế Toán Máy và Bảng Danh mục Tài khoản)
Đây là phần trọng tâm của chương trình, bạn phải tổ chức công tác kế toán trên Excel sao cho phù hợp với công việc kế toán của bạn để từ đó giải quyết được yêu cầu đặt ra là xử lý hoàn chỉnh số liệu kế toán cho đơn vị.
Trước tiên, bạn hãy tạo một sheet mới đặt tên là SOKTM (sổ kế toán máy) để phản ảnh, định khoản toàn bộ các nghiệp vụ kinh tế phát sinh trong kỳ của đơn vị. Đây là dữ liệu gốc để từ đó bạn sẽ dùng các hàm của Excel để xử lý số liệu sau này, do đó cần thực hiện cẩn thận, chính xác. Kết cấu của sổ này bao gồm các cột sau:
- Số seri: phản ảnh số xê ri của hóa đơn thuế GTGT (những hóa đơn không có số xê ri thì bỏ trống).
- Ngày ghi sổ: là ngày định khoản kế toán, bạn nên nhập theo định dạng chuỗi.
- Số chứng từ: lưu trữ số chứng từ, bạn nên nhập theo định dạng chuỗi.
- Ngày chứng từ: lưu trữ ngày chứng từ, bạn nên nhập theo định dạng chuỗi.
- Tên cơ sở kinh doanh: tên người mua, người bán, bạn nên nhập theo định dạng chuỗi.
- Mã số thuế: mã số thuế của người mua, người bán.
- Diễn giải: ghi trích yếu nội dung của nghiệp vụ kinh tế phát sinh.
- Tài khoản ghi nợ, tài khoản ghi có: nhập bút toán định khoản tài khoản ghi nợ, ghi có.
- Số lượng: ghi nhận số lượng phát sinh khi nhập / xuất.
- Số tiền phát sinh: phản ánh số tiền phát sinh của từng tài khoản trong các bút toán định khoản.
- Thuế GTGT, thuế suất GTGT: phản ánh số tiền thuế GTGT và thuế suất GTGT của các mặt hàng mua vào hay bán ra tương ứng.
- Kiểm tra tài khoản Nợ / Có: dùng để kiểm tra các tài khoản sử dụng đã được mở và đăng ký trong bảng danh mục tài khoản.
Tiếp theo, bạn hãy tạo một sheet mới đặt tên là BDMTK (bảng danh mục tài khoản) dùng để thống kê, tập hợp tất cả các nghiệp vụ kinh tế phát sinh đã được định khoản sẵn theo trình tự thời gian trong sổ kế toán máy. Kết cấu bảng này gồm các cột: Mã cấp, Số hiệu tài khoản, Loại tài khoản, Tên tài khoản, Số lượng tồn đầu kỳ, Số dư nợ đầu kỳ, Số dư có đầu kỳ, Tổng số phát sinh nợ, Tổng số phát sinh có, Số lượng tồn cuối kỳ, Số dư cuối kỳ, Mã cấp trung gian tài khoản.
- Cột Mã cấp: dùng để xác định vị trí của từng tài khoản trong bảng cân đối kế toán. Mã cấp bao gồm 3 cụm số, mỗi cụm 3 kí tự. Ví dụ: 100-110-111 là tiền mặt, tài khoản này nằm ở mục 100, tiểu mục 110 trong bảng cân đối kế toán, cách ký hiệu này tương tự cho những tài khoản còn lại.
- Cột Số hiệu tài khoản: mỗi tài khoản có một số hiệu riêng, tài khoản được mở chi tiết cho từng đối tượng theo dõi cụ thể của kế toán chi tiết (mỗi vật tư, mỗi hàng hóa, mỗi khách hàng...) theo nhu cầu quản lý và lập báo cáo kế toán. Tài khoản mở cho đối tượng nào thì sử dụng mã đối tượng đó ghép với số hiệu tài khoản tương ứng. Ví dụ: tài khoản 152A01, 1112USD, 131C...
- Loại tài khoản: các tài khoản có số phát sinh Nợ tăng ký hiệu N, các tài khoản có số phát sinh Có tăng ký hiệu C.
- Số lượng tồn đầu kỳ: dùng để theo dõi số lượng tồn của các tài khoản, “hàng tồn kho” cho từng hàng tồn kho.
- Số dư đầu kỳ: dùng để phản ánh số dư đầu kỳ trên từng tài khoản chi tiết đã mở.
Đặt tên vùng dữ liệu để lập công thức bằng cách bôi đen vùng dữ liệu theo cột hoặc hàng hoặc khối ô sau đó đưa con trỏ chuột vào mục Name box đặt tên và nhấn Enter hoặc chọn thông qua lệnh Insert > Name > Define (các tên này dùng để truy xuất qua các hàm của Excel).
Lập các công thức tính toán để đưa số liệu từ Sổ kế toán máy vào Bảng danh mục tài khoản (xem chi tiết ở file nguồn đính kèm).
=SUMIF(TKGHINO,B5,SOTIENPS) hoặc =SUMIF (TKGHICO,B5,SOTIENPS) hoặc =IF(OR(LEFT (B5,3)=”152",LEFT(B5,3)=”153",LEFT(B5,3)= ”155",LEFT(B5,3)=”156"),E5 + SUMIF(TKGHINO,B5, SOLUONGPS)-SUMIF (TKGHICO,B5,SOLUONGPS),””)
2. Tạo các sổ sách kế toán có liên quan:
Tiến hành xây dựng các sổ nhật ký và các sổ sách kế toán khác:
- Nhật ký thu tiền, nhật ký chi tiền, nhật ký mua hàng, nhật ký bán hàng, nhật ký chung, sổ cái, sổ quỹ tiền mặt, sổ quỹ tiền gửi ngân hàng.
- Sổ số dư và phát sinh tài khoản 131, 331, sổ chi tiết khách hàng phải thu, phải trả, sổ số dư và phát sinh tài khoản 152, 153, 155, 156, sổ chi tiết hàng tồn kho, sổ chi phí sản xuất kinh doanh tài khoản 621, 622, 627, 641, 642, 154, 632, thẻ tính giá thành sản phẩm.
- Bảng cân đối tài khoản, bảng kê hàng hóa mua vào, bán ra, tờ khai thuế GTGT, bảng cân đối kế toán, bảng báo cáo kết quả sản xuất kinh doanh phần lãi lỗ.
Ví dụ: đối với sổ nhật ký thu tiền.
Trước tiên, bạn xây dựng 1 sheet mới đặt tên là NK_THUTIEN, tại sheet này bạn tạo tên các cột dữ liệu theo mẫu, bao gồm: tên sổ, số hiệu, tài khoản, ngày ghi sổ, chứng từ (số, ngày), diễn giải, ghi nợ tài khoản, ghi có tài khoản (đối ứng) số tiền và số hiệu đi kèm. Định dạng bảng tính cho đúng yêu cầu. Kế tiếp, bạn xây dựng các công thức tính toán cho các cột số liệu (lấy từ Sổ kế toán máy) như sau:
Ngày ghi sổ:
=IF(LEFT(SoKTM!I4,3)=”111", SoKTM!C4,””)
Cột Chứng từ (số, ngày), diễn giải lập công thức tương tự chỉ khác cột giá trị trả về.
Ghi nợ tài khoản:
=IF(LEFT(SoKTM!$I4,3)=”111", SoKTM!L4,””)
Ghi có tài khoản (đối ứng):
=IF(AND(LEFT(SoKTM!$I4,3)=”111",LEFT(SoKTM!$J4,3)=NK_ Thutien!F$5),NK_Thutien!$E9,””)
Tổng cộng số tiền ghi nợ:
=SUMIF(TKGHINO,”111*”,SOTIENPS)
Số tiền ghi có (tài khoản đối ứng):
=SUMIF(TKNOCO,”111*”& F$5&”*”,SOTIENPS)
Sau khi tính toán xong 1 hàng, bạn cần sao chép công thức tính toán của nó xuống các hàng bên dưới (tùy thuộc vào số lượng nghiệp vụ kinh tế phát sinh bên Sổ kế toán máy), bạn tiến hành trích lọc dữ liệu để lấy kết quả bằng cách chọn bôi đen hàng số liệu được đánh số từ 1- 12 đến hết bảng tính, chọn tiếp menu Data > Filter > Auto Filter > chọn nút lệnh xổ xuống ở ô số 4 (diễn giải) chọn tiếp lệnh Non Blanks.
Các hàng có dữ liệu sẽ được trích lọc riêng thành 1 bảng có chứa dữ liệu. Các sổ còn lại, các bạn cũng lập tương tự (tham khảo thêm ở file gốc đính kèm).
Trên đây, chỉ là một vài hướng dẫn mang tính gợi ý, các bạn có thể tham khảo thêm tài liệu (sách tham khảo) hoặc trên các diễn đàn về kế toán máy để hiểu thêm và có thể xây dựng cho riêng mình một chương trình kế toán Excel như ý. O
Trước tiên, bạn cần nắm lại một số kiến thức cơ bản về Excel như:
- Thao tác xử lý tập tin (tạo mới, lưu, mở, đóng, in ấn, xóa... bằng cách mở menu File, chọn lệnh tương ứng).
- Thao tác về bảng tính như chèn, xóa cột hàng, đặt tên khối, tên vùng dữ liệu, tên sheet, sao chép dữ liệu, công thức, thiết lập giá trị, font chữ mặc định...
- Thao tác sắp xếp dữ liệu, trích lọc dữ liệu cơ bản và nâng cao, trích lọc có điều kiện phức tạp, vẽ đồ thị...
- Thao tác tạo subtotal, liên kết dữ liệu từ nhiều sheet khác nhau...
- Thao tác định dạng bảng tính, dàn trang bảng tính, xử lý in ấn...
Tiếp theo, bạn cần nắm vững một số hàm cơ bản sau đây để có thể thiết lập các công thức tính toán trong Excel:
- Hàm dạng văn bản: hàm LEFT(text, n); hàm RIGHT(text, n); hàm MID(text, kt, n).
- Hàm dạng logic: hàm AND(đk1, đk2, đk3...); hàm OR(đk1, đk2, đk3...); hàm IF(đk, gt đúng, gt sai).
- Hàm dạng ngày tháng: hàm DAY(ngày); MONTH(ngày); YEAR(ngày); TODAY.
- Hàm dạng thống kê, tính tổng: hàm SUM(gt1, gt2, gt3,...); hàm MAX(gt1, gt2, gt3,...); hàm MIN(gt1, gt2, gt3,...), hàm AVERAGE(gt1, gt2, gt3,...), hàm COUNT(gt1, gt2, gt3,...).
Hàm SUMIF (range, criteria, sumrange); hàm COUNTIF(range, crirteria); hàm DSUM(database, fieldnumber, criteria); DCOUNTA(database, fieldnumber, criteria).
- Hàm dạng toán học: hàm INT(giatri / n); hàm MOD (giatri, n); hàm ROUND(giatri, n).
- Hàm dạng tra cứu: hàm VLOOKUP(lookupvalue, table array, column index number, range lookup); hàm HLOOKUP(lookupvalue, table array, column index number, range lookup).
Với những hàm đơn giản này, giả định bạn đã biết cách sử dụng trong từng trường hợp cụ thể. Ở đây, chúng ta sẽ lồng ghép các hàm này thành những công thức tính toán cụ thể để xử lý các số liệu kế toán.
n VẬN DỤNG KẾ TOÁN VÀ EXCEL ĐỂ VIẾT PHẦN MỀM QUẢN LÝ
1. Tạo 2 sổ kế toán chứa dữ liệu gốc ban đầu (Sổ Kế Toán Máy và Bảng Danh mục Tài khoản)
Đây là phần trọng tâm của chương trình, bạn phải tổ chức công tác kế toán trên Excel sao cho phù hợp với công việc kế toán của bạn để từ đó giải quyết được yêu cầu đặt ra là xử lý hoàn chỉnh số liệu kế toán cho đơn vị.
Trước tiên, bạn hãy tạo một sheet mới đặt tên là SOKTM (sổ kế toán máy) để phản ảnh, định khoản toàn bộ các nghiệp vụ kinh tế phát sinh trong kỳ của đơn vị. Đây là dữ liệu gốc để từ đó bạn sẽ dùng các hàm của Excel để xử lý số liệu sau này, do đó cần thực hiện cẩn thận, chính xác. Kết cấu của sổ này bao gồm các cột sau:
- Số seri: phản ảnh số xê ri của hóa đơn thuế GTGT (những hóa đơn không có số xê ri thì bỏ trống).
- Ngày ghi sổ: là ngày định khoản kế toán, bạn nên nhập theo định dạng chuỗi.
- Số chứng từ: lưu trữ số chứng từ, bạn nên nhập theo định dạng chuỗi.
- Ngày chứng từ: lưu trữ ngày chứng từ, bạn nên nhập theo định dạng chuỗi.
- Tên cơ sở kinh doanh: tên người mua, người bán, bạn nên nhập theo định dạng chuỗi.
- Mã số thuế: mã số thuế của người mua, người bán.
- Diễn giải: ghi trích yếu nội dung của nghiệp vụ kinh tế phát sinh.
- Tài khoản ghi nợ, tài khoản ghi có: nhập bút toán định khoản tài khoản ghi nợ, ghi có.
- Số lượng: ghi nhận số lượng phát sinh khi nhập / xuất.
- Số tiền phát sinh: phản ánh số tiền phát sinh của từng tài khoản trong các bút toán định khoản.
- Thuế GTGT, thuế suất GTGT: phản ánh số tiền thuế GTGT và thuế suất GTGT của các mặt hàng mua vào hay bán ra tương ứng.
- Kiểm tra tài khoản Nợ / Có: dùng để kiểm tra các tài khoản sử dụng đã được mở và đăng ký trong bảng danh mục tài khoản.
Tiếp theo, bạn hãy tạo một sheet mới đặt tên là BDMTK (bảng danh mục tài khoản) dùng để thống kê, tập hợp tất cả các nghiệp vụ kinh tế phát sinh đã được định khoản sẵn theo trình tự thời gian trong sổ kế toán máy. Kết cấu bảng này gồm các cột: Mã cấp, Số hiệu tài khoản, Loại tài khoản, Tên tài khoản, Số lượng tồn đầu kỳ, Số dư nợ đầu kỳ, Số dư có đầu kỳ, Tổng số phát sinh nợ, Tổng số phát sinh có, Số lượng tồn cuối kỳ, Số dư cuối kỳ, Mã cấp trung gian tài khoản.
- Cột Mã cấp: dùng để xác định vị trí của từng tài khoản trong bảng cân đối kế toán. Mã cấp bao gồm 3 cụm số, mỗi cụm 3 kí tự. Ví dụ: 100-110-111 là tiền mặt, tài khoản này nằm ở mục 100, tiểu mục 110 trong bảng cân đối kế toán, cách ký hiệu này tương tự cho những tài khoản còn lại.
- Cột Số hiệu tài khoản: mỗi tài khoản có một số hiệu riêng, tài khoản được mở chi tiết cho từng đối tượng theo dõi cụ thể của kế toán chi tiết (mỗi vật tư, mỗi hàng hóa, mỗi khách hàng...) theo nhu cầu quản lý và lập báo cáo kế toán. Tài khoản mở cho đối tượng nào thì sử dụng mã đối tượng đó ghép với số hiệu tài khoản tương ứng. Ví dụ: tài khoản 152A01, 1112USD, 131C...
- Loại tài khoản: các tài khoản có số phát sinh Nợ tăng ký hiệu N, các tài khoản có số phát sinh Có tăng ký hiệu C.
- Số lượng tồn đầu kỳ: dùng để theo dõi số lượng tồn của các tài khoản, “hàng tồn kho” cho từng hàng tồn kho.
- Số dư đầu kỳ: dùng để phản ánh số dư đầu kỳ trên từng tài khoản chi tiết đã mở.
Đặt tên vùng dữ liệu để lập công thức bằng cách bôi đen vùng dữ liệu theo cột hoặc hàng hoặc khối ô sau đó đưa con trỏ chuột vào mục Name box đặt tên và nhấn Enter hoặc chọn thông qua lệnh Insert > Name > Define (các tên này dùng để truy xuất qua các hàm của Excel).
Lập các công thức tính toán để đưa số liệu từ Sổ kế toán máy vào Bảng danh mục tài khoản (xem chi tiết ở file nguồn đính kèm).
=SUMIF(TKGHINO,B5,SOTIENPS) hoặc =SUMIF (TKGHICO,B5,SOTIENPS) hoặc =IF(OR(LEFT (B5,3)=”152",LEFT(B5,3)=”153",LEFT(B5,3)= ”155",LEFT(B5,3)=”156"),E5 + SUMIF(TKGHINO,B5, SOLUONGPS)-SUMIF (TKGHICO,B5,SOLUONGPS),””)
2. Tạo các sổ sách kế toán có liên quan:
Tiến hành xây dựng các sổ nhật ký và các sổ sách kế toán khác:
- Nhật ký thu tiền, nhật ký chi tiền, nhật ký mua hàng, nhật ký bán hàng, nhật ký chung, sổ cái, sổ quỹ tiền mặt, sổ quỹ tiền gửi ngân hàng.
- Sổ số dư và phát sinh tài khoản 131, 331, sổ chi tiết khách hàng phải thu, phải trả, sổ số dư và phát sinh tài khoản 152, 153, 155, 156, sổ chi tiết hàng tồn kho, sổ chi phí sản xuất kinh doanh tài khoản 621, 622, 627, 641, 642, 154, 632, thẻ tính giá thành sản phẩm.
- Bảng cân đối tài khoản, bảng kê hàng hóa mua vào, bán ra, tờ khai thuế GTGT, bảng cân đối kế toán, bảng báo cáo kết quả sản xuất kinh doanh phần lãi lỗ.
Ví dụ: đối với sổ nhật ký thu tiền.
Trước tiên, bạn xây dựng 1 sheet mới đặt tên là NK_THUTIEN, tại sheet này bạn tạo tên các cột dữ liệu theo mẫu, bao gồm: tên sổ, số hiệu, tài khoản, ngày ghi sổ, chứng từ (số, ngày), diễn giải, ghi nợ tài khoản, ghi có tài khoản (đối ứng) số tiền và số hiệu đi kèm. Định dạng bảng tính cho đúng yêu cầu. Kế tiếp, bạn xây dựng các công thức tính toán cho các cột số liệu (lấy từ Sổ kế toán máy) như sau:
Ngày ghi sổ:
=IF(LEFT(SoKTM!I4,3)=”111", SoKTM!C4,””)
Cột Chứng từ (số, ngày), diễn giải lập công thức tương tự chỉ khác cột giá trị trả về.
Ghi nợ tài khoản:
=IF(LEFT(SoKTM!$I4,3)=”111", SoKTM!L4,””)
Ghi có tài khoản (đối ứng):
=IF(AND(LEFT(SoKTM!$I4,3)=”111",LEFT(SoKTM!$J4,3)=NK_ Thutien!F$5),NK_Thutien!$E9,””)
Tổng cộng số tiền ghi nợ:
=SUMIF(TKGHINO,”111*”,SOTIENPS)
Số tiền ghi có (tài khoản đối ứng):
=SUMIF(TKNOCO,”111*”& F$5&”*”,SOTIENPS)
Sau khi tính toán xong 1 hàng, bạn cần sao chép công thức tính toán của nó xuống các hàng bên dưới (tùy thuộc vào số lượng nghiệp vụ kinh tế phát sinh bên Sổ kế toán máy), bạn tiến hành trích lọc dữ liệu để lấy kết quả bằng cách chọn bôi đen hàng số liệu được đánh số từ 1- 12 đến hết bảng tính, chọn tiếp menu Data > Filter > Auto Filter > chọn nút lệnh xổ xuống ở ô số 4 (diễn giải) chọn tiếp lệnh Non Blanks.
Các hàng có dữ liệu sẽ được trích lọc riêng thành 1 bảng có chứa dữ liệu. Các sổ còn lại, các bạn cũng lập tương tự (tham khảo thêm ở file gốc đính kèm).
Trên đây, chỉ là một vài hướng dẫn mang tính gợi ý, các bạn có thể tham khảo thêm tài liệu (sách tham khảo) hoặc trên các diễn đàn về kế toán máy để hiểu thêm và có thể xây dựng cho riêng mình một chương trình kế toán Excel như ý. O
