Hướng dẫn cách lập công thức lấy dữ liệu từ Nhật ký chung sang Sổ cái Excel

Làm thế nào để có thể lấy dữ liệu từ Nhật ký chung sang Sổ cái trên Excel? Đây hẳn là băn khoăn của bạn khi làm kế toán tổng hợp trên Excel. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn cách lập công thức lấy dữ liệu từ Nhật ký chung sang Sổ cái Excel chỉ với các hàm Excel cơ bản như hàm IF, hàm OR. Tại cuối bài viết sẽ có file để các bạn có thể tải về để xem cụ thể hơn. Các bước thực hiện như sau:

1. Chuẩn bị mẫu Sổ cái

Các bạn cần chuẩn bị sẵn 1 mẫu Sổ cái để có thể bắt đầu xây dựng công thức. Các bạn có thể xem thêm các bài viết sau:

Sau khi đã thực hiện xong các bước thì chúng ta sẽ có 1 mẫu Sổ cái như sau:

2. Nguyên tắc lấy dữ liệu từ NKC sang Sổ cái

Để đảm bảo dữ liệu từ Nhật ký chung lấy sang Sổ cái là chính xác thì chúng ta sẽ phải tuân thủ 1 vài nguyên tắc như sau:

  • Các điều kiện về dữ liệu phải tổ chức rõ ràng, đảm bảo đúng về loại dữ liệu. Trong đó chú ý ô Số tài khoản (ô I1) là loại dữ liệu nào (Text hay Number). Loại dữ liệu này phải thống nhất giống với loại dữ liệu trong cột Danh mục tài khoản (để tham chiếu tên tài khoản) và với loại dữ liệu trong cột TK Nợ, TK Có của Nhật ký chung.
  • Các cột dữ liệu trong Sổ cái phải tổ chức rõ ràng, không trộn ô trong vùng Sổ phát sinh trong kỳ của Sổ cái
  • Số dòng của Sổ cái phải bằng với Số dòng của Nhật ký chung (nếu Nhật ký chung có 1000 dòng thì Sổ cái cũng phải có 1000 dòng) để đảm bảo lấy đủ nội dung. Dòng bắt đầu của Sổ cái sẽ lấy tương ứng theo dòng bắt đầu trong sổ Nhật ký chung.
  • Tài khoản đối ứng trong Sổ cái phải theo nguyên tắc: Nếu tài khoản Sổ cái nằm ở cột TK Nợ trong sổ NKC thì tài khoản đối ứng sẽ là tài khoản trong cột TK Có trong sổ NKC trên cùng 1 dòng.
  • Số phát sinh phải ghi vào cột Nợ hoặc cột Có tương ứng theo tài khoản Sổ cái nằm ở cột TK Nợ hay TK Có trong sổ NKC, ghi theo số tiền tương ứng trong cột Số tiền của Nhật ký chung.

3. Xây dựng công thức trên Excel

Trong Sổ cái chúng ta đặt công thức cho từng cột, bắt đầu từ dòng 12 (ngay sau dòng Số phát sinh trong kỳ) như sau:

a. Xét với cột Tài khoản đối ứng trước, vì cột này phức tạp nhất.

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:

Theo nguyên tắc tài khoản đối ứng và điều kiện dữ liệu của Sổ cái, yêu cầu với cột này là:

  • Nếu tài khoản Sổ cái (ô I1) trùng với TK Nợ trong NKC và Ngày tháng ghi sổ trong NKC nằm trong khoảng Từ ngày… (ô I2) và Đến ngày… (ô I3) của Sổ cái thì lấy theo tài khoản bên cột TK Có, nếu không phải thì xét trường hợp tiếp theo

=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!H8,…)

  • Nếu tài khoản Sổ cái (ô I1) trùng với TK Có trong NKC và Ngày tháng ghi sổ trong NKC nằm trong khoảng Từ ngày… (ô I2) và Đến ngày… (ô I3) của Sổ cái thì lấy theo tài khoản bên cột TK Nợ, nếu không thỏa mãn trường hợp này (và trường hợp trước đó) thì trả về kết quả rỗng

=IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!G8,””)

Cột tài khoản đối ứng phải xét cả 2 trường hợp này nên sẽ lồng 2 hàm IF lại với nhau như sau:

E12=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!H8,IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!G8,””))

Trong đó

  • Cột G trong NKC là cột TK Nợ, cột H trong NKC là cột TK Có
  • Cột A trong NKC là cột Ngày ghi sổ

b. Xét với cột Số phát sinh Nợ và Số phát sinh Có

Dựa trên nguyên tắc của cột TK đối ứng ta có:

  • Nếu tài khoản Sổ cái (ô I1) trùng với TK Nợ trong NKC và Ngày tháng ghi sổ trong NKC nằm trong khoảng Từ ngày… (ô I2) và Đến ngày… (ô I3) của Sổ cái thì lấy số tiền vào cột Số phát sinh Nợ của Sổ cái, nếu không phải thì lấy giá trị = 0

F12=IF(AND(NKC!G8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!I8,0)

  • Nếu tài khoản Sổ cái (ô I1) trùng với TK Có trong NKC và Ngày tháng ghi sổ trong NKC nằm trong khoảng Từ ngày… (ô I2) và Đến ngày… (ô I3) của Sổ cái thì lấy số tiền vào cột Số phát sinh Có của Sổ cái, nếu không phải thì lấy giá trị = 0

G12=IF(AND(NKC!H8=$I$1,NKC!A8>=$I$2,NKC!A8<=$I$3),NKC!I8,0)

Trong đó:

  • Cột I trong NKC là cột Số tiền

c. Các cột khác

Các cột này chỉ phụ thuộc vào việc có phát sinh trong cột Tài khoản đối ứng (cột E) hay không.

Do đó chỉ cần dùng hàm IF để biện luận: Nếu giá trị tương ứng ở cột E là rỗng thì trả kết quả là rỗng, còn không rỗng thì lấy tương ứng theo cột đó trong sổ Nhật ký chung

A12=IF($E12=””,””,NKC!A8)

B12=IF($E12=””,””,NKC!B8)

C12=IF($E12=””,””,NKC!C8)

D12=IF($E12=””,””,NKC!D8)

Kết quả như sau:

Lưu ý: Tất cả các tham chiếu tới các ô I1, I2, I3 trong Sổ cái đều phải cố định để khi sao chép công thức xuống các dòng tiếp theo phía dưới thì không làm thay đổi tham chiếu tới các vị trí này.

Áp dụng công thức tại dòng 12 cho tất cả các dòng còn lại trong Sổ cái (filldown công thức, phím tắt Ctrl + D, từ dòng 12 tới toàn bộ các dòng phát sinh của Sổ cái)

4. Lọc dữ liệu phát sinh trong Sổ cái

Sau khi hoàn thiện bước 3 thì chúng ta có thể thấy dữ liệu xuất hiện trong Sổ cái không phải lúc nào cũng liền kề nhau, mà phụ thuộc vào vị trí phát sinh trong sổ Nhật ký chung. Do đó để có thể loại bỏ các dòng trống (dữ liệu không tương ứng với điều kiện Sổ cái) thì chúng ta tạo thêm 1 cột phụ để lọc bỏ các dòng trống này, chỉ giữ lại các dòng có phát sinh.

Vị trí lọc có thể bắt đầu từ dòng tiêu đề cột (dòng 9) trong đó:

  • Các vị trí luôn luôn xuất hiện trên Sổ cái như Số dư đầu kỳ, số phát sinh trong kỳ, cộng số phát sinh, số dư cuối kỳ…) thì không cần áp dụng công thức, đặt ngay dấu xác nhận luôn hiển thị khi lọc (ví dụ là ký tự x)
  • Các dòng phát sinh sẽ xét theo công thức: nếu dữ liệu ở cột E là rỗng thì lấy kết quả rỗng, dữ liệu không phải rỗng (có phát sinh) thì sẽ hiển thị khi lọc, tức là trả về giá trị x

Công thức tại ô H12 = IF(E12=””,””,”x”)

Áp dụng AutoFilter trên cột H, tính từ ô H9 tới toàn bộ sổ cái.

Kết quả như sau

Như vậy chúng ta đã có thể hoàn thành xong công thức lấy dữ liệu từ Nhật ký chung sang Sổ cái rồi.

Xem thêm: Hướng dẫn cách tự động cập nhật sổ cái trong Excel với VBA

Tải về file đính kèm: http://bit.ly/2JgjzTh

Ngoài ra Học Excel Online xin giới thiệu tới các bạn khóa học Trọn bộ kỹ năng nghiệp vụ kế toán tổng hợp online trên Excel. Khóa học này sẽ giúp bạn bổ sung và hoàn thiện kiến thức kế toán tổng hợp, đồng thời xây dựng được trọn bộ file Excel để làm kế toán tổng hợp từ 1 file excel trắng. Chi tiết xem tại:

Tác giả: duongquan211287

Bài viết liên quan