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

Thảo luận trong 'Ứng dụng Excel' bắt đầu bởi BachVe, 15 Tháng ba 2004.

58,744 lượt xem

  1. BachVe

    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
     
    #1
  2. BachVe

    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
     
    #2
  3. dentuy

    dentuy Thành viên sơ cấp

    Bài viết:
    7
    Đã được thích:
    0
    Bạn Bạch Ve có thể hướng dấn chi tiết hơn không
     
    #3
  4. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    Nha Trang
    Chưa hiểu ứng dụng của hàm này!!
     
    #4
  5. BachVe

    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
     
    Last edited by a moderator: 5 Tháng chín 2004
    #5
  6. Bình_OverAC

    Bình_OverAC Over Abnormal / Crazy

    Bài viết:
    845
    Đã được thích:
    7
    Nơi ở:
    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
     
    #6
  7. hai_chaukt

    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.
     
    #7
  8. Tuanktcdcn

    Tuanktcdcn Lão già ham vui

    Bài viết:
    548
    Đã được thích:
    49
    Nơi ở:
    Hà Nội
    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.
     
    #8
  9. thanhnhan53

    thanhnhan53 Thành viên hoạt động

    Bài viết:
    66
    Đã được thích:
    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
     
    #9
  10. AuDuongPhong

    AuDuongPhong Thành viên sơ cấp

    Bài viết:
    4
    Đã được thích:
    0
    Nơi ở:
    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.
     
    Last edited: 1 Tháng tư 2005
    #10
  11. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    Bạn AuDuongPhong có thể hướng dẫn cụ thể hơn được không?
     
    #11
  12. AuDuongPhong

    AuDuongPhong Thành viên sơ cấp

    Bài viết:
    4
    Đã được thích:
    0
    Nơi ở:
    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?
     
    #12
  13. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    E'rywhere
    hic, chẳng mát tay tý nào, cái này chắc chỉ có chị Dung hoặc anh Thanh Nhàn xoa huyệt may ra MaiKa mới kinh thông được !
     
    #13
  14. xungdanhanhhung

    xungdanhanhhung Thành viên hoạt động

    Bài viết:
    268
    Đã được thích:
    0
    Nơi ở:
    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?
     
    #14
  15. IChiNoVa

    IChiNoVa Phu cà phê

    Bài viết:
    134
    Đã được thích:
    4
    Nơi ở:
    Sòn Gài
    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.
     
    #15
    hoaithu899 thích bài này.
  16. ForestC

    ForestC Thành viên hoạt động

    Bài viết:
    377
    Đã được thích:
    1
    Nơi ở:
    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.
     
    #16
  17. TuananhBG

    TuananhBG Thành viên hoạt động

    Bài viết:
    24
    Đã được thích:
    0
    Nơi ở:
    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
     
    #17
  18. levanduyet

    levanduyet Welcome

    Bài viết:
    535
    Đã được thích:
    11
    Nơi ở:
    HCM
    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
     
    #18
  19. lien_aps

    lien_aps Thành viên sơ cấp

    Bài viết:
    28
    Đã được thích:
    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?
     
    #19
  20. Do_Ngoc_Anh2002

    Do_Ngoc_Anh2002 Thành viên sơ cấp

    Bài viết:
    32
    Đã được thích:
    1
    Nơi ở:
    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.
     
    #20
    mung1234 thích bài này.

Chia sẻ trang này