Tự động cập nhật dữ liệu trong EXCEL

  • Thread starter BachVe
  • Ngày gửi
B

BachVe

Guest
Trong Excel có một hàm mà chúng ta có thể dùng nó để tự động hóa quá trình cập nhật dữ liệu! đó là hàm OFFSET
Hàm Offset cho kết quả là nội dung của một ô.
cú pháp của hàm này như sau: OFFSET(tọa độ một ô bất kỳ, số dòng, số cột)
Lưu ý: Số dòng và số cột chứ không phải tọa độ dòng hay tọa độ cột, hay đó là khoảng cách từ ô A1 đến ô cần lấy dữ liệu. (Ta nên chọn ô bất kỳ đó là ô A1)

Ví dụ ở ô C5 có nội dung là 1/1/2004, và ta cần lấy nội dung 1/1/2004 đó.
công thức là offset($A$1,4,2)
sẽ cho kết quả là 1/1/2004
Do đó dùng hàm này để tự động cập nhật dữ liệu của các chứng từ khi lập các sổ Nhật ký chung, nhật ký đặc biệt, sổ cái... từ sheet chứng từ. :f_o :bia
 
Khóa học Quản trị dòng tiền
B

BachVe

Guest
Trong Excel có một hàm mà chúng ta có thể dùng nó để tự động hóa quá trình cập nhật dữ liệu! đó là hàm OFFSET
Hàm Offset cho kết quả là nội dung của một ô.
cú pháp của hàm này như sau: OFFSET(tọa độ một ô bất kỳ, số dòng, số cột)
Lưu ý: Số dòng và số cột chứ không phải tọa độ dòng hay tọa độ cột, hay đó là khoảng cách từ ô A1 đến ô cần lấy dữ liệu. (Ta nên chọn ô bất kỳ đó là ô A1)

Ví dụ ở ô C5 có nội dung là 1/1/2004, và ta cần lấy nội dung 1/1/2004 đó.
công thức là offset($A$1,4,2)
sẽ cho kết quả là 1/1/2004
Do đó dùng hàm này để tự động cập nhật dữ liệu của các chứng từ khi lập các sổ Nhật ký chung, nhật ký đặc biệt, sổ cái... từ sheet chứng từ. :f_o :bia
 
D

dentuy

Guest
25/3/04
7
0
0
Bạn Bạch Ve có thể hướng dấn chi tiết hơn không
 
B

BachVe

Guest
VD thế này:
Khi các nghiệp vụ sảy ra thì các bạn tập hợp vào 1 Sheet riêng, gọi đại là SheetCT đi.
Bây giờ bạn muốn lập sổ NKC chẳng hạn, có nhiều cách để lập:

1: Bạn copy dữ liệu từ SheetCT ra SheetNKC rồi tiến hành vài thao tác sao chép đơn giản. Công việc này bạn phải làm mỗi khi muốn có sổ NKC, hay khi có nghiệp vụ mới phát sinh.... và còn tùm lum thứ nữa.

2: Bạn dùng Offset. thì bạn lập một công thức chung ban đầu, sau đó mỗi khi có nghiệp vụ KT sảy ra thì bạn chỉ việc chép công thức xuống dưới và dữ liệu sẽ tự động được lấy từ SheetCT sang điền vào Sheet NKC.
Trong Sheet NKC này bạn có thể thêm vào 2 ô "ngayD" và "ngayC" để lọc dữ liệu trong một khoảng thời gian nào đó bằng hàm SUBTOTAL mà mình đã trình bày, dĩ nhiên hàm Offset cũng chỉ lấy những nghiệp vụ nào thỏa điều kiện mà thôi.

Các bạn có thể tham khảo cách lập SheetNKC ở đây:
Sheet NKC
 
Sửa lần cuối bởi điều hành viên:
B

Bình_OverAC

Over Abnormal / Crazy
14/5/04
846
10
18
42
Nha Trang
Ah mình hiểu rồi!
Hay thật!
Mình nghĩ bạn đính kèm file thi có lẻ mình hiểu nhanh hơn. Minh đã từng cực khổ chuyển từ nhật ký chung dạng thứ 2 sang dạng thứ nhât đấy. Nó phức tạp hơn kiểu này nhiều, mình đã bó tay không biết phải làm sao nên đã phải dùng tơi Macro.
Mình sẽ thử áp dụng công thức này vào thử xem sao!
Cám ơn nhiều. :cool :thank
 
H

hai_chaukt

Guest
Từ hàm offset,Dsum,dlookup,if...và sử dụng một số macro ta có thể tự động hàon toàn công việc kế toán, từ công nợ chi tiết cho đến tổng hợp và báo cáo kế toán,kể cả tự động tính kết quả SXKD,tôi đã làm được một chương trình này để lam kế toán rất hay nhưng thú thực Excel chỉ áp dụng được cho đơn vị có nghiệp vụ kế toán không lớn thì nó chạy nhanh, nhưng nếu xử lý đối với những DN có nghiệp vụ phát sinh lớn thì không dùng được nên tôi phải chuyển qua ACCess tốt hơn nhiều và cũng.... oai hơn.
 
T

Tuanktcdcn

Lão già ham vui
18/6/04
549
52
28
47
Hà Nội
www.bluesofts.net
To: Hai_ChauKT
Đồng tình với một số ý kiến của bạn. Vấn đề còn phụ thuộc rất nhiều vào cách viết cũng như thuật toán của bạn nữa, nếu không thì Access hay Excel cũng dở dở...mà thôi.
Nếu viết tốt trên EXCEL sẽ là thành công lớn hơn Access vì có "tính mở" cho ngưới sử dụng.
 
T

thanhnhan53

Guest
20/7/03
66
0
0
Và thêm nữa là khi nhập dữ liệu EXCEL nhanh hơn ACCESS nhiều, không tin các bạn cứ thử đi . hihihihi . Cám ơn Tuanktcdcn
 
A

AuDuongPhong

Guest
21/9/04
4
0
0
118
The hell
Tới hôm nay Âu mỗ mới biết hàm này, hay lắm.

Mấy bồ lưu ý: Nếu dùng công thức sau cũng lấy được nội dung của một ô:
ô A4 = A10

Công thức trên cho kết quả ở ô A4 là nội dung của ô A10.
Nhưng nếu bạn chép công thức xuống thì sẽ là A5=A11; A6=A12; A7=A13
Nó phải tuần tự như vậy.

Hàm Offset độc hơn công thức trên ở chỗ có thể kết hợp nó với các hàm: INDIRECT, ADDRESS để tạo nên một công thức siêu đẳng.

Chẳng hạn khi bạn muốn lập sổ cái 111
thì bạn phải lấy được vị trí của các chứng từ có TK111 trong SheetCT, nhưng những CT này đâu có nằm liên tục, mà nó sẽ bị ngắt quãng, rải rác.
Khi đó bạn sẽ phải dùng Offset, kết hợp INDIRECT, ADDRESS, MATCH để lấy được những CT đó.

Tôi nhớ là đã đọc được bài của bạn về mấy hàm trên rồi.
 
Sửa lần cuối:
A

AuDuongPhong

Guest
21/9/04
4
0
0
118
The hell
Bạn phải hỏi BachVe chớ. Tôi chỉ mới biết thôi mừ.

Nè: Hàm offset có công dụng để lấy nội dung của một ô được chưa? gần giống công thức sau: =A8

muốn lập SC 111 thì bạn phải lấy được toạ độ dòng, cột của những CT có TK phát sinh là 111, OK?
A sorry: nghiệp vụ chứ ko phải CT
Nhưng mà những NV đó lại nằm rải rác giữa hàng trăm NV khác.
Nếu dùng công thức như bình thường là "=A8" thì khi chép công thức xuống, chẳng lẽ bạn lấy cả những NV khác à?

Do đó hề hề, bạn buộc phải dùng Offset thôi.
Muốn dùng nó, trước hết phải dùng MATCH, để lấy toạ độ dòng của những NV đó. Khi đó mới có thể xài OFFSET được.
Nhưng hàm MATCH chỉ có thể tìm được 1 NV đầu tiên mà nó gặp trong mảng thôi, do đó, hi hi...
Bạn lại phải làm cho cái mảng trong hàm MATCH có thể co giãn được, để loại bớt những NV đã tìm thấy ra khỏi mảng.
Và bạn lại buộc phải dùng thêm INDIRECT và ADDRESS.

Sao đả thông kinh mạch chưa, Tài xoa bóp giải huyệt của Âu mỗ có mát tay không?
 
X

xungdanhanhhung

Guest
6/2/05
268
2
0
Hanoi
Tức quá, tớ vẫn chưa biết ứng dụng của hàm offset là thế nào nhỉ?? Mà sao vào cái tham khảo SheetNKC mãi không được. Khi lập sổ cái các tài khoản mình chưa có cách nào hơn là dùng lọc để lấy các nghiệp vụ nợ, có của cùng 1 tài khoản rồi copy số thứ tự ở cột số thứ tự sheet vào dữ liệu (dữ liệu sẽ tự chui vào vì sheet này có sẵn hàm vlookup rùi). Thủ công quá, giá có cách nào hay hơn?
 
IChiNoVa

IChiNoVa

Phu cà phê
2/4/05
134
4
18
Sòn Gài
www.xommuaban.vn
Mình có một file mẫu về hàm này: bao gồm lập sổ NKC, sổ cái, sổ nhật ký đặc biệt, bảng cân đối số phát sinh, bảng cân đối kế toán.

Các sổ đều được lập tự động, dùng hàm OFFSET, nếu có nghiệp vụ phát sinh thêm, các bạn chỉ cần nhập NV đó vào SheetCT, rồi sao chép công thức ở các sổ NKC và sổ cái xuống.
 
  • Like
Reactions: hoaithu899
F

ForestC

Guest
11/1/05
377
1
0
44
E'rywhere
À lâu lắm rồi topic này không được ai post bài, nay MaiKa vào lại thì sự thể đã khác so với bài gần đây nhất mà MaiKa post trong cùng topic này: tóm lại là tớ đã kinh thông về offset và tớ cảm thấy nó (offset) đáng yêu vô cùng. Tớ dùng nó trong rất nhiều việc: Phiếu thu chi nhập xuất, tìm kiếm doanh nghiệp trong danh sách các dn, tập hợp chứng từ cho Bảng kê & Nhật ký (Hình thức kế toán NKCT) ... Quả là một hàm có công dụng mạnh, nhặt nhạnh hơi bị thông mình.
 
T

TuananhBG

Guest
4/9/04
24
1
0
Bac Giang
Hiện nay tôi vẫn lấy sổ liệu, nội dung chứng từ từ sổ NKC sang sổ cái bằng cách dùng hàm Subtotal. Nhưng qua đây nhờ các bạn chỉ dùm chi tiết hơn để tôi không còn phải làm thủ công nhưng hiện nay mà có thể tự động như các bạn nói. Xin cảm ơn các bạn trước
 
levanduyet

levanduyet

Welcome
16/10/04
537
19
18
HCM
my.opera.com
TuananhBG nói:
Hiện nay tôi vẫn lấy sổ liệu, nội dung chứng từ từ sổ NKC sang sổ cái bằng cách dùng hàm Subtotal. Nhưng qua đây nhờ các bạn chỉ dùm chi tiết hơn để tôi không còn phải làm thủ công nhưng hiện nay mà có thể tự động như các bạn nói. Xin cảm ơn các bạn trước
To: Bạn TuananhBG,
Bạn có thể gởi file của bạn và yêu cầu của bạn tôi có thể xem và viết chương trình giúp bạn.
Thân,
Lê Văn Duyệt
 
L

lien_aps

Guest
2/4/04
28
0
0
Tôi đọc topic này nhưng vẫn chưa thông lắm, mọi người có thể post lên một file ứng dụng của hàm offset để tôi có thể download về tìm hiểu lại có được không?
 
D

Do_Ngoc_Anh2002

Guest
7/5/05
32
1
0
Hanoi
Tôi đang làm kế toán bằng EXCEL nhưng mới chỉ dùng hàm SUMIF để tính tổng các phát sinh Nợ, Có đối ứng với một TK trong cả kỳ hạch toán. Do đó sổ cái của tôi chỉ ở dạng tổng hợp, tôi cần một sổ cái chi tiết từng nghiệp vụ theo này tháng.
Bạn ICHINOVA nói có File mẫu nào đó, chuyển cho mọi người cùng xem với.
 
  • Like
Reactions: mung1234

Xem nhiều

Webketoan Zalo OA