Kế toán trên Excel 3: Sổ cái

Tạo số thứ tự tương đối theo vùng

ex-stttudong

Công thức nhập trong ô B2: = ROW()-ROW(oDau)

Khi sao chép công thức này xuống sẽ tạo thứ tự tự động (tính từ oDau) như trong hình. Ta có thể xóa bớt ô hoặc copy thêm xuống, thứ tự luôn đảm bảo.

Bổ sung trên sheet CT

Trên sheet CT bổ sung:

oDongDau =IF(ISNA(MATCH(oNgayDau;vCTNgayGhiSo;0));IF(ISNA(MATCH(oNgayDau;vCTNgayGhiSo;1));1;MATCH(oNgayDau;vCTNgayGhiSo;1)+1);MATCH(oNgayDau;vCTNgayGhiSo;0))

 

oDongCuoi =MATCH(oNgaycuoi;vCTNgayGhiSo;1)

 

Sheet Sổ cái – Đặt tên là SoCai

ex-socai

Công thức:

+ Ngày tháng ghi sổ

=INDEX(vCTNgayGhiSo;oDongDau+ROW()-ROW(oDau))

+ Công thức cho các cột Ngày chứng từ, Số chứng từ, Diễn giải tương tự như Ngày tháng ghi sổ. Chỉ cần đổi tên vùng.

+ Số hiệu TK đối ứng

=IF(INDEX(vCTTKNo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTTKCo;oDongDau+ROW()-ROW(oDau)); IF(INDEX(vCTTKCo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTTKNo;oDongDau+ROW()-ROW(oDau));””))

Cột này cũng là cột để lọc loại bỏ các dòng chứng từ không liên quan đến tài khoản đang xem xét. Các dòng không liên quan sẽ có TK đối ứng trống (Blank) do đó khi thực hiện lọc bỏ dấu chọn bên cạnh dòng Blank trong điều kiện lọc của cột này.

+ Số phát sinh Nợ:

=IF(INDEX(vCTTKNo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTSoPhatSinh;oDongDau+ROW()-ROW(oDau));0)

+ Số phát sinh Có:

=IF(INDEX(vCTTKCo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTSoPhatSinh;oDongDau+ROW()-ROW(oDau));0)

+ Số dư Nợ đầu kỳ:

=VLOOKUP($C$8;vCDSPS;3;0)

+ Các số dư còn lại và số phát sinh tương tự như Số dư Nợ đầu kỳ.

Advertisements

9 thoughts on “Kế toán trên Excel 3: Sổ cái

  1. Bạn oi minh không hiểu chô phần thiết lập sổ cái
    Công thức nhập trong ô B2: = ROW()-ROW(oDau) ý nghĩ gì
    oDongDau =IF(ISNA(MATCH(oNgayDau;vCTNgayGhiSo;0));IF(ISNA(MATCH(oNgayDau;vCTNgayGhiSo;1));1;MATCH(oNgayDau;vCTNgayGhiSo;1)+1);MATCH(oNgayDau;vCTNgayGhiSo;0))

    oDongCuoi =MATCH(oNgaycuoi;vCTNgayGhiSo;1)
    trong sheet CT mình làm không được (mình hiểu la đếm dòng dữ liệu)
    Nhờ bạn hổ trợ giúp. Nếu bạn viết rồi cho mình xin file excel mẫu cảm ơn bạn nhiều
    mail của mình: nhungdoantg@gmail.com

    • oDongDau = Ô dòng đầu – xác định dòng của chứng từ đầu tiên trên bảng CT của kỳ đang xem xét (kỳ được xác định trong ô oNgayDau và oNgayCuoi). Ta sẽ chỉ lấy các chứng từ bắt đầu từ dòng đầu (có số hiệu nằm trong oDongDau) trên bảng chứng từ (CT) sang Sổ cái chứ không lấy hết từ đầu bảng chứng từ (CT) để giảm lượng dữ liệu không cần thiết.

      Công thức ROW()- ROW(oDau) tính ra thứ tự của dòng hiện tại so với dòng đầu tiên trong sổ. Do trong Excel không có hàm nào tự động tăng dần nên phải dùng cách này. Dựa trên thứ tự này cộng với oDongDau sẽ lấy được từng chứng từ của kỳ hiện tại sang sổ cái,…

      oDongCuoi không dùng.

  2. Thầy ơi cho em hỏi chỗ công thức này em k hiểu lắm:
    + Số hiệu TK đối ứng

    =IF(INDEX(vCTTKNo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTTKCo;oDongDau+ROW()-ROW(oDau)); IF(INDEX(vCTTKCo;oDongDau+ROW()-ROW(oDau))=$C$8;INDEX(vCTTKNo;oDongDau+ROW()-ROW(oDau));””))

    Chỗ Row()-row(odau) là mình viết theo công thức đó hay là lấy giá trị thẳng của ô thứ tự tương ứng đã được nhập công thức như vậy rồi. Nếu là viết theo công thức đó thì row(odau) là lấy giá trị odau là ô nào vậy ạ

  3. Thầy ơi cho em hỏi phần sổ chi tiết thanh toán với người mua/ người bán. Chổ phần tài khoản là mình chỉ được để là 2 Tk 131 và 331 hay là mình tạo list data validation gồm tất cả các Tk trong vùng Số hiệu Tk luôn thầy. Tại em thấy nếu chỉ để 2 Tk như vậy thì công thức nó hơi phức tạp. Dạ em cảm ơn thầy nhiều. Hihi

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Đăng xuất / Thay đổi )

Connecting to %s