B
BachVe
Guest
Sổ Nhật ký chung.
Ví dụ về hàm OFFSET.
Sổ NKC có kết cấu gồm: Ngày, Số, Diễn giải, TK, Nợ, Có.
Chúng ta sẽ lập sổ này, lấy dữ liệu từ sheetCT. Có một điểm lưu ý là ở SheetCT mỗi CT được lập trên 1 dòng, còn ở SheetNKC mỗi chứng từ lại được lập thành 2 dòng.
VD: SheetCT:
SheetNKC:
Các bước cần thực hiện như sau:
1. Xác định dòng của CT đầu tiên trong sheetCT để lập cột phụ. (Cột phụ được sử dụng cho hàm Offset)
2. Xác định dòng của CT cuối cùng trong sheetCT để tính xem có bao nhiêu CT trong sheetCT. (Trong VD này giả sử có 10 chứng từ). Việc này để xác định vị trí phải đặt dòng tổng cộng ở dòng nào.
3. Lập công thức tính vị trí của dòng tổng cộng: (10*2)+3=23
số 10 nghĩa là 10 chứng từ.
số 2 để làm cho mỗi chứng từ được thể hiện trên 2 dòng.
số 3 chính là dòng CT đầu tiên trong sheetNKC.
Trong thực tế có hàng trăm CT chứ không phải 10 do đó dùng hàm dò tìm MATCH để tính xem có bao nhiêu CT.
(*Không được tự nhập hằng số thay cho hàm Match, bởi vì khi thêm CT mới vào sheetCT thì ở sheetNKC dữ liệu sẽ tự động cập nhật sai)
Thủ thuật là điền một số đặc biệt vào dòng cuối cùng trong sheetCT sau mẩu tin cuối cùng, và ở cột A, ở đây xin chọn là 999.
Công thức tìm 999 là:
MATCH(999,CT!A:A,0)=12
0 là mã dò tìm chính xác.
=> Tổng số CT= (12-1)-1=10
Số 1 cuối cùng là vị trí dòng của dòng tiêu đề: Ngày, Số, Diễn giải…. trong sheetCT, có thể khi lập sheetCT, các bạn không đặt nó ngay dòng 1.
Tổng quát vị trí dòng tổng cộng:
=((Match(999,CT!A:A,0)-1-row(CT!dòng tiêu đề))*2)+row(NKC!dòng CT đầu tiên)
4. Tạo cột phụ như trên, để một CT được ghi trên 2 dòng.
Cách lập cột phụ: Trong VD trên, CT đầu tiên trong sheetCT là ở dòng 2.
Do đó trước tiên cần phải điền số 2 vào A3 à A4 như trong VD ở sheetNKC.
Công thức ô A5=If(A3=A4,A4+1,A4)
5. Lập CT cho các cột: Ngày, Số, Diễn giải:
Dùng hàm OFFSET để lấy dữ liệu bên sheetCT
Ngày ở ô B3=Offset(CT!$A$1,A3-1,0).
Do lấy ô A1 làm mốc nên khoảng cách từ ô A1 đến ô Ngày của CT đầu tiên trong sheetCT là (A3-1) dòng và 0 cột. (A3 chính là ô ở cột phụ).
Các cột Số, Diễn giải cũng tương tự, chỉ khác ở chỗ khoảng cách đến ô A1 là 1 và 2 cột.
Số ở ô C3=Offset(CT!$A$1,A3-1,1).
Diễn giải ở ô D3=Offset(CT!$A$1,A3-1,2).
6. Lập công thức cho các cột TK, Nợ, Có cũng dựa vào cột phụ và dùng hàm Offset, vấn đề là lấy TK bên nợ hay bên có, và số tiền được ghi ở cột nợ hay cột có. Chỉ cần sử dụng thêm hàm IF là xử lý được.
7. Trình tự thực hiện lập sổ NKC:
Điền các dòng tiêu đề.
Lập công thức tìm vị trí dòng tổng cộng và di chuyển dòng tổng cộng đến ví trí đó.
Lập các công thức cho cột phụ và các cột khác.
Sao chép các công thức đến dòng liền kề với dòng tổng cộng khi nãy.
Mỗi khi thêm CT mới vào sheetCT thì ở sheetNKC sẽ tự động thay đổi vị trí dòng tổng cộng, ta chỉ cần kéo dòng tổng cộng đến vị trí mới và sao chép các công thức.
*Sau khi hoàn thành cần phải dấu cột phụ đi bằng chức năng hide column.
*Muốn xem những chứng từ được lập trong khoảng thời gian nào đó thì thêm ô NgayD và NgayC.
Và thêm cột phụ lập công thức so sánh 2 ô trên với ô Ngày, Sau đó dùng Auto Filter để lọc, kết hợp với hàm SUBTOTAL.
Cột phụ này nên nằm cách bảng NKC 1 cột trống thì dùng Auto Filter mới được.
*Nên đùng Auto Filter hơn là Advanced Filter.
Xong! một sổ nhật ký chung hoàn chỉnh, tuyệt đẹp.
:bia
Ví dụ về hàm OFFSET.
Sổ NKC có kết cấu gồm: Ngày, Số, Diễn giải, TK, Nợ, Có.
Chúng ta sẽ lập sổ này, lấy dữ liệu từ sheetCT. Có một điểm lưu ý là ở SheetCT mỗi CT được lập trên 1 dòng, còn ở SheetNKC mỗi chứng từ lại được lập thành 2 dòng.
VD: SheetCT:
SheetNKC:
Các bước cần thực hiện như sau:
1. Xác định dòng của CT đầu tiên trong sheetCT để lập cột phụ. (Cột phụ được sử dụng cho hàm Offset)
2. Xác định dòng của CT cuối cùng trong sheetCT để tính xem có bao nhiêu CT trong sheetCT. (Trong VD này giả sử có 10 chứng từ). Việc này để xác định vị trí phải đặt dòng tổng cộng ở dòng nào.
3. Lập công thức tính vị trí của dòng tổng cộng: (10*2)+3=23
số 10 nghĩa là 10 chứng từ.
số 2 để làm cho mỗi chứng từ được thể hiện trên 2 dòng.
số 3 chính là dòng CT đầu tiên trong sheetNKC.
Trong thực tế có hàng trăm CT chứ không phải 10 do đó dùng hàm dò tìm MATCH để tính xem có bao nhiêu CT.
(*Không được tự nhập hằng số thay cho hàm Match, bởi vì khi thêm CT mới vào sheetCT thì ở sheetNKC dữ liệu sẽ tự động cập nhật sai)
Thủ thuật là điền một số đặc biệt vào dòng cuối cùng trong sheetCT sau mẩu tin cuối cùng, và ở cột A, ở đây xin chọn là 999.
Công thức tìm 999 là:
MATCH(999,CT!A:A,0)=12
0 là mã dò tìm chính xác.
=> Tổng số CT= (12-1)-1=10
Số 1 cuối cùng là vị trí dòng của dòng tiêu đề: Ngày, Số, Diễn giải…. trong sheetCT, có thể khi lập sheetCT, các bạn không đặt nó ngay dòng 1.
Tổng quát vị trí dòng tổng cộng:
=((Match(999,CT!A:A,0)-1-row(CT!dòng tiêu đề))*2)+row(NKC!dòng CT đầu tiên)
4. Tạo cột phụ như trên, để một CT được ghi trên 2 dòng.
Cách lập cột phụ: Trong VD trên, CT đầu tiên trong sheetCT là ở dòng 2.
Do đó trước tiên cần phải điền số 2 vào A3 à A4 như trong VD ở sheetNKC.
Công thức ô A5=If(A3=A4,A4+1,A4)
5. Lập CT cho các cột: Ngày, Số, Diễn giải:
Dùng hàm OFFSET để lấy dữ liệu bên sheetCT
Ngày ở ô B3=Offset(CT!$A$1,A3-1,0).
Do lấy ô A1 làm mốc nên khoảng cách từ ô A1 đến ô Ngày của CT đầu tiên trong sheetCT là (A3-1) dòng và 0 cột. (A3 chính là ô ở cột phụ).
Các cột Số, Diễn giải cũng tương tự, chỉ khác ở chỗ khoảng cách đến ô A1 là 1 và 2 cột.
Số ở ô C3=Offset(CT!$A$1,A3-1,1).
Diễn giải ở ô D3=Offset(CT!$A$1,A3-1,2).
6. Lập công thức cho các cột TK, Nợ, Có cũng dựa vào cột phụ và dùng hàm Offset, vấn đề là lấy TK bên nợ hay bên có, và số tiền được ghi ở cột nợ hay cột có. Chỉ cần sử dụng thêm hàm IF là xử lý được.
7. Trình tự thực hiện lập sổ NKC:
Điền các dòng tiêu đề.
Lập công thức tìm vị trí dòng tổng cộng và di chuyển dòng tổng cộng đến ví trí đó.
Lập các công thức cho cột phụ và các cột khác.
Sao chép các công thức đến dòng liền kề với dòng tổng cộng khi nãy.
Mỗi khi thêm CT mới vào sheetCT thì ở sheetNKC sẽ tự động thay đổi vị trí dòng tổng cộng, ta chỉ cần kéo dòng tổng cộng đến vị trí mới và sao chép các công thức.
*Sau khi hoàn thành cần phải dấu cột phụ đi bằng chức năng hide column.
*Muốn xem những chứng từ được lập trong khoảng thời gian nào đó thì thêm ô NgayD và NgayC.
Và thêm cột phụ lập công thức so sánh 2 ô trên với ô Ngày, Sau đó dùng Auto Filter để lọc, kết hợp với hàm SUBTOTAL.
Cột phụ này nên nằm cách bảng NKC 1 cột trống thì dùng Auto Filter mới được.
*Nên đùng Auto Filter hơn là Advanced Filter.
Xong! một sổ nhật ký chung hoàn chỉnh, tuyệt đẹp.
:bia