Cách tự động thêm dữ liệu trong Excel

Tôi sẽ chỉ cho bạn cách tạo các công thức và hàm tự động cập nhật khi có thêm dữ liệu được thêm vào một phạm vi trong Excel.  

Một ví dụ đơn giản là khi bạn muốn tính tổng một phạm vi sẽ có nhiều dữ liệu được thêm vào phạm vi đó theo thời gian. Bạn không muốn phải tiếp tục cập nhật các công thức và hàm của mình, vì vậy bạn muốn cập nhật các công thức và hàm một cách linh hoạt

Đây là bảng tính mẫu của chúng tôi

Bây giờ, hãy thêm một số vào danh sách và xem điều gì sẽ xảy ra

Tổng thông thường không thay đổi nhưng tổng động được cập nhật để hiển thị số tiền chính xác

(Đảm bảo tải xuống sổ làm việc cho hướng dẫn này để bạn có thể làm theo)

Đây là SUM thông thường

Đây là hàm tổng cập nhật động

=SUM(OFFSET(A2,0,0,COUNT(A:A)))

Để làm cho công thức trên tương thích với các phiên bản Excel trước Excel 2007, hãy sử dụng công thức này

=SUM(OFFSET(A2,0,0,COUNT(A1:A100)))

Điều này là do bạn không thể tham chiếu toàn bộ cột trong Excel 2003 trở về trước và A. A tham chiếu toàn bộ cột, cột A

Bây giờ, hãy phá vỡ công thức này

Về cơ bản, tôi chỉ đang sử dụng hàm OFFSET() để trả về một phạm vi mà hàm SUM() sau đó sẽ sử dụng thay vì mã hóa cứng một phạm vi thành hàm SUM()

Đây là một phần của công thức trên làm cho nó năng động

OFFSET(A2,0,0,COUNT(A:A))

Tất cả những gì bạn cần làm là thay đổi hai phần này để phù hợp với bạn

OFFSET(A2,0,0,COUNT(A:A))

Để sử dụng hàm OFFSET() để trả về một phạm vi động, chúng ta chỉ cần đặt ô đầu tiên trong phạm vi làm đối số đầu tiên, trong trường hợp này là A2

Sau đó, chúng ta cần sử dụng hàm COUNT() làm đối số thứ 4 cho hàm OFFSET() để tìm ra phạm vi của chúng ta nên lớn như thế nào. Thay đổi A. A đến bất kỳ cột nào chứa danh sách dữ liệu của bạn

Hàm COUNT() đếm tất cả các ô trong phạm vi có số. Tham chiếu phạm vi mà bạn sử dụng cho hàm COUNT() phải đủ lớn để chứa bất kỳ danh sách kích thước nào mà bạn có thể có trong tương lai để không cần phải thay đổi

Nếu bạn sử dụng Excel 2007 trở lên, chỉ cần sử dụng A. A làm đối số cho hàm COUNT(), như minh họa trong ví dụ động đầu tiên ở trên. Bằng cách này, toàn bộ cột sẽ được tham chiếu

Phạm vi động bao gồm văn bản và hơn thế nữa

Nếu bạn đang sử dụng Excel 2007 trở lên, bạn có thể sử dụng hàm COUNTA() thay cho hàm COUNT(). Hàm COUNTA() đếm tất cả các ô KHÔNG trống trong khi hàm COUNT() chỉ đếm các ô có số trong đó

Thực hành tốt nhất

Nếu bạn tạo các công thức và hàm động bằng cách sử dụng hàm OFFSET() như đã đề cập ở trên, nó sẽ gây nhầm lẫn và lộn xộn rất nhanh khi bạn tạo các công thức lớn

Một cách thường được sử dụng để giải quyết vấn đề này là sử dụng Dải ô được đặt tên tự động cập nhật khi bạn thêm giá trị mới vào dải ô. Điều này sử dụng cùng một phương pháp như chúng tôi đã minh họa ở trên, ngoại trừ hàm OFFSET() và nội dung của nó được chứa trong một Phạm vi được đặt tên và Phạm vi được đặt tên này sau đó được sử dụng trong các công thức và hàm trong bảng tính bất cứ khi nào bạn cần tham chiếu một phạm vi mà bạn muốn

Dưới đây là Hướng dẫn về Phạm vi được đặt tên động trong Excel của chúng tôi sẽ chỉ cho bạn cách thực hiện điều đó

Hàm OFFSET() là chìa khóa để tạo công thức và hàm động trong Excel. Hãy nhớ xem Hướng dẫn về hàm OFFSET của chúng tôi nếu bạn muốn được giải thích sâu hơn về cách thức hoạt động của hàm này.  

Trong Excel, việc chèn ngày tháng và dấu thời gian là một thao tác bình thường. Ở đây trong hướng dẫn này, tôi sẽ giới thiệu nhiều phương pháp chèn thủ công hoặc tự động ngày tháng và dấu thời gian vào các ô Excel bằng cách cung cấp các trường hợp khác nhau

Chèn ngày và dấu thời gian bằng phím tắt

Chèn ngày và dấu thời gian với công thức

Công thức tự động chèn dấu thời gian khi nhập dữ liệu vào cột khác

VBA để tự động chèn dấu thời gian khi nhập dữ liệu vào cột khác

Chèn ngày và dấu thời gian bằng phím tắt

Nếu bạn chỉ cần chèn ngày và dấu thời gian vào một vài ô, bạn có thể chèn thủ công bằng cách nhấn phím tắt

Chèn ngày hiện tại. Kiểm soát +

Chèn thời gian hiện tại. Dịch chuyển + Điều khiển +

Xem ảnh chụp màn hình

Lời khuyên

1. Bạn có thể định dạng đầu ra dưới dạng định dạng ngày hoặc định dạng thời gian khi bạn cần trong hộp thoại Định dạng ô

2. Nếu bạn có Kutools cho Excel, bạn có thể chèn ngày hiện tại hoặc các ngày khác ở định dạng ngày được chỉ định khi bạn cần với chức năng Chèn ngày của nó. Nhấp để tải xuống miễn phí

Chèn ngày và dấu thời gian với công thức

Nếu bạn muốn chèn ngày hoặc dấu thời gian có thể cập nhật tự động, bạn có thể sử dụng các công thức bên dưới

Chèn ngày hiện tại

=HÔM NAY()

Nhấn phím Enter và ngày hiện tại được chèn vào ô

Chèn thời gian hiện tại：

=BÂY GIỜ()

Nhấn phím Enter, ngày và giờ hiện tại được chèn vào ô

Lời khuyên

1. Bạn có thể định dạng đầu ra dưới dạng định dạng ngày hoặc định dạng thời gian khi bạn cần trong hộp thoại Định dạng ô. Chẳng hạn, bạn chỉ muốn hiển thị thời gian hiện tại, chỉ cần định dạng ô là Thời gian sau khi sử dụng công thức =NOW()

2. Nếu bạn muốn chèn ngày và giờ hiện tại vào đầu trang hoặc chân trang của trang tính, bạn có thể sử dụng chức năng Chèn thông tin sổ làm việc của Kutools cho Excel để xử lý nhanh công việc này. Nhấp để tải xuống miễn phí

Công thức tự động chèn dấu thời gian khi nhập dữ liệu vào cột khác

Giả sử có 2 cột Cột A và Cột B, bây giờ em muốn chèn dấu thời gian hiện tại vào Cột B khi đang nhập dữ liệu vào Cột A thì làm như thế nào?

Tự động chèn dấu thời gian khi nhập dữ liệu

1. Đầu tiên, bấm vào Tệp > Tùy chọn để mở hộp thoại Tùy chọn Excel, chọn Công thức trong ngăn bên trái, sau đó kiểm tra Kích hoạt phép tính lặp trong nhóm Tùy chọn tính toán. Và bấm OK

2. Trong Cột B, ví dụ, Ô B1, hãy nhập công thức này

=IF(A1<>"",IF(B1<>"",B1,NOW()),"")

sau đó kéo thanh điều khiển tự động điền xuống các ô

3. Sau đó, định dạng các ô công thức thành định dạng ngày giờ mà bạn cần trong hộp thoại Định dạng ô. giữ các ô công thức được chọn, nhấp chuột phải để hiển thị menu ngữ cảnh, chọn Định dạng ô, sau đó hộp thoại Định dạng ô bật ra, trong phần Tùy chỉnh bên dưới tab Số, nhập định dạng bạn cần vào hộp văn bản Loại và nhấp vào OK

Bây giờ khi bạn nhập dữ liệu vào Cột A, ngày giờ hiện tại sẽ được chèn vào Cột B

Tự động chèn và cập nhật dấu thời gian khi ô thay đổi ở cột khác

Nếu bạn muốn tự động chèn dấu thời gian trong khi nhập ô, đồng thời, nếu mục nhập thay đổi, dấu thời gian đã chèn sẽ được cập nhật, bạn có thể sử dụng công thức bên dưới

=IF(A1<>"",IF(AND(B1<>"",CELL("địa chỉ")=ADDRESS(ROW(A1),COLUMN(A1))),NOW(),IF(CELL("địa chỉ

A1 là ô bạn sẽ nhập dữ liệu, B1 là ô công thức bạn muốn chèn dấu thời gian

Kéo bộ điều khiển tự động điền xuống các ô mà bạn sử dụng

Sau đó, định dạng các ô công thức thành định dạng ngày giờ mà bạn cần trong hộp thoại Định dạng ô. giữ các ô công thức được chọn, nhấp chuột phải để hiển thị menu ngữ cảnh, chọn Định dạng ô, sau đó hộp thoại Định dạng ô bật ra, trong phần Tùy chỉnh, bên dưới tab Số, nhập định dạng bạn cần vào hộp văn bản Loại, nhấp vào OK

VBA để tự động chèn dấu thời gian khi nhập dữ liệu vào cột khác

Nếu bạn quen với mã VBA, bạn có thể làm như dưới đây

1. Nhấp chuột phải vào tab trang tính bạn sử dụng, sau đó chọn Xem mã từ menu ngữ cảnh

2. Sau đó, trong cửa sổ Microsoft Visual Basic for Applications, dán mã bên dưới

VBA. Tự động chèn dấu thời gian

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyKutools20190919
Dim xRInt As Integer
Dim xDStr As String
Dim xFStr As String
On Error Resume Next
xDStr = "A" 'Data Column
xFStr = "B" 'Timstamp Column
If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then
       xRInt = Target.Row
       Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")
End If
End Sub

3. Sau đó lưu mã này. Từ bây giờ, chỉ cần bạn nhập dữ liệu hoặc thay đổi dữ liệu ở Cột A, dấu thời gian mới sẽ được chèn vào Cột B

Ghi chú. bạn có thể thay đổi cột A và B và mm/dd/yyyy hh. mm. định dạng thời gian ss trong mã VBA để phù hợp với nhu cầu thực sự của bạn

Nếu bạn muốn sử dụng chức năng đã xác định, bạn có thể làm như bên dưới

1. Giữ phím Alt + F11 để bật cửa sổ Microsoft Visual Basic for Applications. Và nhấp vào Chèn > Mô-đun để chèn một mô-đun trống

2. Dán mã bên dưới vào mô-đun mới. Sau đó lưu mã và quay lại trang tính

Function FormatDate(xRg As Range)
'UpdatebyKutools20190919
On Error GoTo Err_01
If xRg.Value <> "" Then
    FormatDate = Format(Now, "mm/dd/yyyy hh:mm:ss")
Else
    FormatDate = ""
End If
Exit Function
Err_01:
    FormatDate = "Error"
End Function

3. Trong ô sẽ được chèn dấu thời gian, hãy nhập công thức này

=FormatDate(F1)

F1 là ô bạn sẽ nhập dữ liệu hoặc thay đổi dữ liệu. Sau đó kéo thanh điều khiển tự động điền xuống các ô

Bây giờ ngày giờ hiện tại sẽ được chèn nếu ô F1 đã nhập dữ liệu hoặc cập nhật

Các hoạt động khác (Bài viết) liên quan đến DateTime

Chuyển đổi ngày được lưu dưới dạng văn bản thành ngày trong Excel
Đôi khi, khi bạn sao chép hoặc nhập ngày từ các nguồn dữ liệu khác vào ô Excel, ngày có thể được định dạng và lưu dưới dạng văn bản. Và ở đây tôi giới thiệu các thủ thuật để chuyển đổi những ngày được lưu trữ dưới dạng văn bản này thành ngày chuẩn trong Excel.

Cộng/trừ nửa năm/tháng/giờ cho ngày hoặc giờ trong Excel
Việc cộng năm, tháng hoặc giờ cho ngày hoặc giờ là điều bình thường trong công việc hàng ngày trên Excel của chúng ta. Bạn đã bao giờ thử thêm nửa năm, tháng hoặc giờ vào ngày hoặc giờ chưa? .

Dấu thời gian trung bình trong ngày trong Excel
Ví dụ: bạn đã ghi lại dấu thời gian đăng nhập của mỗi lần một người dùng cụ thể truy cập trang web trong Excel và bây giờ bạn muốn tính trung bình các dấu thời gian này cho

Tính giờ giữa các thời điểm sau nửa đêm trong Excel
Giả sử bạn có một bảng thời gian để ghi lại thời gian làm việc của mình, thời gian ở Cột A là thời gian bắt đầu của ngày hôm nay và thời gian ở Cột B . Thông thường, nếu bạn tính chênh lệch múi giờ giữa hai thời điểm bằng cách trừ trực tiếp "=B2-A2" thì sẽ không hiển thị kết quả chính xác

Các bài viết khác về DateTime

Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

• Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); . Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Hợp nhất ô/hàng/cột và giữ dữ liệu; . Ngăn chặn các ô trùng lặp; . Combine Duplicate Rows and Sum/Average.. Prevent Duplicate Cells; Compare Ranges...
• Chọn Hàng trùng lặp hoặc Duy nhất ; . Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Sao chép chính xác Nhiều ô mà không thay đổi tham chiếu công thức; . Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Chèn nhanh và yêu thích Công thức , Phạm vi, Biểu đồ và Ảnh; . Encrypt Cells with password; Create Mailing List and send emails...
• Trích xuất văn bản , Thêm văn bản, Xóa theo vị trí, Xóa khoảng trắng ; . Convert Between Cells Content and Comments...
• Siêu bộ lọc (lưu và áp dụng lược đồ bộ lọc cho các trang tính khác); . Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Kết hợp Sổ làm việc và Trang tính ; . Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Nhóm bảng tổng hợp theo số tuần, ngày trong tuần, v.v. Hiển thị các ô đã mở khóa, bị khóa theo các màu khác nhau; . Highlight Cells That Have Formula/Name...

Đọc thêm. Tính năng đầy đủ Bản dùng thử miễn phí 30 ngày. Mua, tựa vào, bám vào.  

Office Tab - mang giao diện theo thẻ vào Office và giúp công việc của bạn dễ dàng hơn nhiều

• Bật chỉnh sửa và đọc theo tab trong Word, Excel, PowerPoint , Publisher, Access, Visio và Project.
• Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới
• Tăng năng suất của bạn lên 50% và giảm hàng trăm lần nhấp chuột cho bạn mỗi ngày

Đọc thêm. Tính năng đầy đủ Bản dùng thử miễn phí 30 ngày. Mua, tựa vào, bám vào.  

Cũ nhất đầu tiên

Sắp xếp nhận xét theo

Cũ nhất đầu tiên

mới nhất đầu tiên

Nhận xét ( 6 )

Chưa có xếp hạng nào. Hãy là người đầu tiên xếp hạng

xxkd

tầm 1 tháng trước

#40908

Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web

hàm cập nhật mỗi khi bạn mở tệp excel
hàm này cũng cập nhật dữ liệu trước đó khi bạn chèn hàng.

Đáp lại

0

0

Nắng     xxkd

tầm 1 tháng trước

#40961

Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web

Chào bạn, bạn thử bấm vào tab Công thức và vào nhóm Tính toán bấm vào Tùy chọn tính toán > Thủ công thì công thức sẽ không tự động cập nhật

Đáp lại

Báo cáo

0

0

xkd     Sunny

khoảng 3 tuần trước

#41135

Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web

Cảm ơn bạn rất nhiều vì sự giúp đỡ của bạn. Thật không may, cài đặt thủ công sẽ dừng các công thức khác của tôi trong bảng, điều này không tốt theo quan điểm của tôi

Đáp lại

0

0

Celeste

tầm 1 tháng trước

#41084

Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web

Trong các ô nơi dấu thời gian sẽ hiển thị, thay vào đó, văn bản màu đỏ cho biết Dấu thời gian sẽ hiển thị. Không có giá trị ở định dạng mm/dd/yyyy hh. mm. ss có thể nhìn thấy