Chia sẻ
13 tháng một 2022
Có hai cách để Excel tự động làm mới các kết nối dữ liệu và tính toán lại một trang tính. Những thủ thuật này rất quan trọng với loại dữ liệu Cổ phiếu và Tiền tệ trong Excel 365 cũng như các tình huống khác
Thông thường Excel sẽ tự cập nhật khi bạn thay đổi giá trị ô. Ngày nay, có những trường hợp các ô thay đổi giá trị nhưng Excel KHÔNG cập nhật trang tính. Nói cách khác, Excel hiện đại đã thay đổi theo những cách mà Microsoft chưa hoàn toàn thích ứng với
Lý tưởng nhất là Excel sẽ có một cài đặt tổng thể để làm mới trang tính sau mỗi 'n' giây hoặc phút. Vào năm 2022, Microsoft cuối cùng đã thêm một số tùy chọn tự động làm mới Chứng khoán/Tiền tệ nhưng chúng chưa hoàn thiện và bị hạn chế
Bài viết này có HAI cách khác nhau để buộc cập nhật trong Excel. Một là cách tiếp cận macro 'truyền thống', hai là thủ thuật mới hơn sử dụng PowerQuery kết hợp với một macro
Một trong hai tùy chọn cung cấp cho bạn nhiều quyền kiểm soát hơn đối với việc cập nhật so với bổ sung 'đánh dấu vào ô' đơn giản của Microsoft cho Excel 365
Loại dữ liệu chứng khoán
Với Loại dữ liệu chứng khoán, khả năng cập nhật tự động quan trọng hơn. Người dùng sẽ muốn bảng tính của họ tự động lấy giá mới nhất, điều mà các bản phát hành xem trước hiện tại không thể làm được. Thay vì có một 'ticker' tự động đẹp mắt, chúng tôi sẽ nhấp vào 'Làm mới' để nhận giá mới nhất
Chứng khoán, Tiền tệ và các loại dữ liệu được liên kết khác là những con thú tò mò. Chúng là các kết nối dữ liệu với các nguồn bên ngoài nhưng KHÔNG xuất hiện dưới dạng Kết nối dữ liệu Excel. Điều đó có nghĩa là bạn không thể thiết lập tính năng làm mới dữ liệu tự động như với các kết nối dữ liệu thông thường. Trên thực tế, không có kiểm soát tiếp xúc nào đối với Chứng khoán, Tiền tệ hoặc các loại dữ liệu được liên kết khác
NOW[] và các chức năng dễ bay hơi khác
Hàm NOW[] cập nhật ngày giờ mới nhất bất cứ khi nào Excel tính toán lại trang tính. Nhưng nếu không có gì để làm điều đó xảy ra, Now[] không thay đổi giá trị. Một số yếu tố bên ngoài là cần thiết để Excel cập nhật Now[] và phần còn lại của trang tính. Nói cách khác, bạn sẽ có thể lướt qua một trang tính và biết nó đang ở trang tính thứ hai nhưng điều đó là không thể với Excel 'out of the box'
Microsoft gọi NOW[] và các hàm tương tự là 'dễ bay hơi' vì giá trị của chúng có thể thay đổi ngay cả khi không có ô nào khác thay đổi. Các hàm biến động khác là Today[], Randbetween[], Offset[] và Indirect[]. Trong một số trường hợp, Info[], Cell[] và SumIf[] cũng có thể biến động
Các chức năng tùy chỉnh VBA cũng có thể được gắn thẻ là không ổn định bằng cách sử dụng dòng này trong mã chức năng
Application.Volatile
Dòng đó làm cho hàm chạy bất cứ lúc nào Excel cập nhật/tính toán lại trang tính
Mẹo nhỏ là tạo một macro Excel buộc các kết nối dữ liệu phải cập nhật, sau đó đánh dấu chức năng đó là Dễ bay hơi để nó sẽ chạy bất cứ khi nào sổ làm việc được cập nhật
Thêm thận trọng
Có thể bạn muốn trang tính của mình cập nhật tự động để đề phòng?
Tự động cập nhật mã
Phương pháp tiêu chuẩn buộc cập nhật tự động Excel là một đoạn mã VBA ngắn. Đây là những gì chúng tôi sử dụng, có nhiều biến thể trên cùng một chủ đề. Mã đầy đủ ở cuối bài viết
Có ba chức năng
RefreshAllDataConn
thực hiện làm mới các kết nối dữ liệu [ Workbooks[ThisWorkbook. Tên]. RefreshAll ] và chúng tôi đã thêm hai dòng, tùy chọn, để hiển thị lần cuối cùng được làm mới trên thanh trạng thái dưới cùng
Nếu bạn muốn cẩn thận hơn, hãy thêm dòng để buộc tính toán lại một cách rõ ràng. Là ActiveSheet. Tính toán hoặc Ứng dụng cực trị. Tính toán đầy đủ [sử dụng ít, điều này sẽ làm chậm một trang tính lớn]
Tự động làm mới
chạy phụ RefreshAllDataConn mỗi phút hoặc bất kỳ giá trị nào bạn đặt trên dòng Ứng dụng. OnTime Now + TimeValue[“00. 01. 00”], “Tự động làm mới”
Sổ làm việc_Mở
một hàm Excel dựng sẵn chạy tự động khi trang tính được mở. Trong trường hợp này, nó bắt đầu AutoRefresh
Giải pháp kết nối dữ liệu PowerQuery
Nhược điểm của phương pháp VBA là. bảng tính xlsm là cần thiết [bảng tính Excel được bật macro]. Có thể xảy ra sự cố khi chia sẻ tệp đã bật macro do lo ngại về bảo mật
Sự xuất hiện của PowerQuery/Nhận và Chuyển đổi có nghĩa là có một cách khác để buộc tính toán lại trang tính. Đó là một giải pháp thay thế và không hoàn hảo, nhưng có thể thực hiện được và không cần trang tính hỗ trợ macro
Nói tóm lại, hãy đảm bảo rằng có thiết lập truy vấn dữ liệu với tính năng tự động làm mới. Nếu không có kết nối dữ liệu, hãy thêm một kết nối nhỏ vào trang tính
Sau khi bạn có truy vấn tự động làm mới, trang tính bao gồm bất kỳ hàm biến động nào cũng sẽ được làm mới
Lý tưởng nhất là các loại dữ liệu Chứng khoán và Địa lý cũng nên làm mới
Mọi truy vấn dữ liệu Excel đều có một số tùy chọn làm mới trong thuộc tính Truy vấn. Hầu hết trong số họ mặc định TẮT
Làm mới sau mỗi nnn phút – mặc định tắt với 60 phút được đề xuất
Làm mới dữ liệu khi mở tệp
Bật làm mới nền
Làm mới kết nối này trên Refresh All
Giải pháp thay thế tự động làm mới là tạo một kết nối dữ liệu nhỏ và thực tế không đáng kể. Sau đó định cấu hình kết nối dữ liệu đó để cập nhật mỗi phút hoặc bất cứ lúc nào bạn muốn. Điều đó sẽ buộc bảng tính cập nhật bao gồm các hàm dễ bay hơi được đề cập ở trên
Một số phiên bản của giải pháp thay thế này thêm liên kết đến tệp csv nhỏ trên cùng một máy tính. Chúng tôi đã nhận được kết quả tương tự bằng cách sử dụng kết nối dữ liệu từ một bảng trong trang tính
Tạo một bảng nhỏ với một ô duy nhất. Ô có thể có bất kỳ thứ gì nhưng chúng tôi tạo một ô có NOW[] trong đó, vì những lý do chúng tôi sẽ giải thích sau
Chọn bảng sau đó chọn Dữ liệu. Lấy dữ liệu. Từ các nguồn khác. từ Bảng/Phạm vi. Mục menu chính xác có thể khác nhau tùy thuộc vào phiên bản Excel của bạn
Khi Trình soạn thảo Truy vấn mở ra, chỉ cần Đóng và Tải nó. Trong ngăn kết nối dữ liệu, bạn sẽ thấy một truy vấn
Nhấp chuột phải vào truy vấn, chọn thuộc tính để xem cài đặt chúng tôi cần
Đặt tốc độ làm mới phù hợp với bạn
Để cho gọn gàng, chúng ta di chuyển bảng nguồn [phải] vào cùng trang tính tại truy vấn đã tải [trái]. Bởi vì chúng tôi đã sử dụng NOW[] trong ô của bảng nguồn, nên sẽ dễ dàng biết khi nào/nếu trang tính được làm mới
Ví dụ mã VBA
Sub RefreshAllDataConn[] ' Refresh all Data Connections. ' This should include Stock and Currency data types, even though they aren't listed. Workbooks[ThisWorkbook.Name].RefreshAll ' Show update time on status bar to confirm. ' comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now[] End Sub Sub AutoRefresh[] ' to run a Refresh All on the workbook every n minutes RefreshAllDataConn ' Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue["00:01:00"], "AutoRefresh" ' this is a simple example. There's no coded way to exit this function. End Sub Private Sub Workbook_Open[] ' Starts the automatic refresh when the workbook is opened, ' commented out as a precaution. ' AutoRefresh End Sub
Dữ liệu Chứng khoán / Tiền tệ của Excel [đại loại là] được tự động làm mới
Tạo mục lục hoặc danh sách trang tính Excel tự động
Hoàn thành giải pháp Excel NetworkDays[] với các ngày lễ và kỳ nghỉ
Tham gia Office for Mere Mortals ngay hôm nay
Office for Mere Mortals là nơi hàng nghìn người thu thập các mẹo và thủ thuật hữu ích cho Word, Excel, PowerPoint và Outlook
Hãy thử một lần. Bạn có thể hủy đăng kí bất kì lúc nào. Chúng tôi chưa bao giờ gửi thư rác hoặc bán địa chỉ kể từ khi chúng tôi bắt đầu hơn hai mươi năm trước