Bài viết này minh họa cách tạo một cơ sở dữ liệu trong Excel cập nhật đó tự động với sự trợ giúp của 4 phương pháp hữu ích. Đây là một tính năng quan trọng cần có khi làm việc với dữ liệu động. Khi cơ sở dữ liệu phụ thuộc vào dữ liệu từ một nguồn khác, việc cập nhật tự động theo dữ liệu nguồn là rất quan trọng. Hãy đi sâu vào các ví dụ để làm quen với các phương pháp.
4 phương pháp hữu ích để tạo cơ sở dữ liệu tự động cập nhật trong Excel
1. Trích xuất dữ liệu từ Web để tạo cơ sở dữ liệu cập nhật tự động trong Excel
Công việc : Trích xuất Dự báo thời tiết trong 14 ngày cho New York, Hoa Kỳ từ web và tạo cơ sở dữ liệu Excel sẽ cập nhật tự động .
Phân tích vấn đề :Chúng tôi đã tìm thấy một số trang web cung cấp dự báo thời tiết trực tuyến . Đối với ví dụ này, chúng tôi sẽ sử dụng dự báo cho New York , Hoa Kỳ từ liên kết trang web https://www.timeanddate.com/weather/usa/new-york/ext . Chúng tôi có thể sao chép bảng trong ảnh chụp màn hình sau vào trang tính Excel để tạo một cơ sở dữ liệu . Nhưng thật không may, cơ sở dữ liệu không đi để cập nhật theo vào dữ liệu trang web trừ khi chúng tôi kết nối nó tới liên kết nguồn. Nếu cơ sở dữ liệu được kết nối tới liên kết nguồn , Excel tạo điều kiện chúng tôi với các tùy chọn để cập nhật cơ sở dữ liệu theo cách thủ công và tự động .
Giải pháp :Chúng tôi cần làm theo các bước bên dưới để tự động cập nhật cơ sở dữ liệu dự báo thời tiết của mình.
Bước 1:Kết nối với Web
- Bắt đầu vào tab Dữ liệu trong Ruy-băng Excel .
- Nhấp trên Từ web từ tab Nhận &chuyển đổi.
- Trong hộp thoại , dán URL từ nơi chúng tôi muốn trích xuất dữ liệu cho cơ sở dữ liệu của chúng tôi và nhấn OK .
- Trong Bộ điều hướng cửa sổ, chúng ta có thể thấy các bảng tìm thấy trên trang web . Với sự trợ giúp của Chế độ xem bảng , chúng tôi có thể thấy dữ liệu được trích xuất ở dạng bảng .
- Để chỉnh sửa bảng dữ liệu được trích xuất trước khi tải nó vào cơ sở dữ liệu Excel , nhấp trên Dữ liệu chuyển đổi tùy chọn.
Bước 2:Chuyển đổi dữ liệu
- Trong Power Query Editor , chúng tôi có thể xóa các cột không mong muốn . Để làm điều đó, hãy nhấp chuột phải trên tiêu đề cột và chọn Xóa tùy chọn.
- Chúng tôi đã xóa một số cột và chỉ giữ lại 6 cột để nhập vào cơ sở dữ liệu .
- Bây giờ, từ Trang chủ , nhấp vào tab Đóng &tải nút .
- Chọn Đóng và tải tùy chọn .
Bước 3:Nhập dữ liệu vào cơ sở dữ liệu Excel
- Trong Nhập dữ liệu cửa sổ, nhấp vào trên tùy chọn trang tính hiện có và chọn ô bắt đầu để nhập dữ liệu và nhấp vào OK để thực hiện lệnh.
- Chúng tôi đã tạo thành công một cơ sở dữ liệu Excel với dữ liệu được kết nối tới nguồn trang web .
Bước 4:Bật tính năng tự động cập nhật
- Nhấp trên bảng cơ sở dữ liệu .
- Bắt đầu vào Dữ liệu trong Ruy-băng Excel .
- Nhấp trên Làm mới tất cả nút.
- Chọn Thuộc tính kết nối tùy chọn .
- Bây giờ, đặt thời gian sau đó chúng tôi muốn làm mới cơ sở dữ liệu tự động trong Làm mới mọi hộp nhập liệu và nhấn OK .
Đọc thêm:Cách sử dụng hàm cơ sở dữ liệu trong Excel (Có ví dụ)
2. Tạo Cơ sở dữ liệu với Bảng Pivot Cập nhật Tự động trong Excel
Trong hình minh họa này, chúng tôi sẽ tạo dựa trên bảng tổng hợp trên tập dữ liệu nguồn . Khi bảng tổng hợp phục vụ dưới dạng cơ sở dữ liệu trong Excel , kích hoạt tính năng tự động làm mới cho bảng tổng hợp sẽ cập nhật cơ sở dữ liệu Excel tự động của chúng tôi . Các bước sau sẽ hướng dẫn bạn qua quy trình .
Bước 1:Tạo bảng tổng hợp
Giả sử chúng tôi có tập dữ liệu đại diện cho chi tiết bán hàng cho một cửa hàng .
Để thực hiện một bảng tổng hợp ,
- Chọn toàn bộ tập dữ liệu .
- Bắt đầu vào tab Chèn .
- Nhấp trên Bảng tổng hợp nút.
- Chọn Từ Bảng / Phạm vi tùy chọn.
- Trong PivotTable từ bảng hoặc dải ô cửa sổ, nhấp vào trên tùy chọn trang tính mới và nhấn OK .
- Một cơ sở dữ liệu bảng tổng hợp đã được tạo trong trang tính mới .
Bước 2:Bật Tính năng Tự động Làm mới cho Bảng Pivot để Cập nhật Cơ sở dữ liệu Tự động
Phương pháp này sẽ cập nhật bảng tổng hợp mỗi lần sổ làm việc được mở , không phải mỗi lần thay đổi được thực hiện đối với tập dữ liệu. Vì vậy, nó giống như tự động hóa một phần của bảng tổng hợp . Hãy làm theo các bước để bật tự động - làm mới tính năng cho bảng tổng hợp :
- Nhấp chuột phải bất kỳ ô nào của bảng tổng hợp để mở trình đơn ngữ cảnh.
- Chọn Tuỳ chọn PivotTable từ trình đơn ngữ cảnh .
- Từ Tuỳ chọn PivotTable , đi tới Dữ liệu tab và kiểm tra Làm mới dữ liệu khi mở tệp tùy chọn .
- Cuối cùng, nhấn OK để đóng cửa sổ.
Đọc thêm:Cách tạo cơ sở dữ liệu đơn giản trong Excel VBA
3. Tự động làm mới bảng tổng hợp để cập nhật cơ sở dữ liệu tự động bằng mã VBA trong Excel
Nhiệm vụ: Sử dụng VBA đơn giản mã chúng tôi có thể tự động cập nhật bảng tổng hợp của chúng tôi khi chúng tôi thay đổi bất kỳ dữ liệu nguồn nào . Quan trọng nhất là nó xảy ra ngay lập tức không giống như phương pháp trước đó nơi chúng tôi cần đóng và mở lại tệp một lần nữa để xem cập nhật .
Giải pháp: Để biến điều đó thành hiện thực, hãy làm theo hướng dẫn!
- Đúng - nhấp chuột tên trang tính và chọn Xem mã tùy chọn.
Mã: Chèn VBA sau mã trong trình chỉnh sửa cơ bản trực quan .
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
Đầu ra: Mã VBA này sẽ chạy bất cứ lúc nào chúng tôi thay đổi dữ liệu di động trong tệp nguồn. Tất cả bảng tổng hợp liên quan đến nguồn sẽ được cập nhật theo đó và ngay lập tức .
Để kiểm tra quy trình trên, chúng tôi đã tạo bảng tổng hợp trong một trang tính có tên pivot_table dựa trên dữ liệu nguồn trong trang tính có tên VBA . Chúng tôi đã thay đổi số lượng của Apple từ 50 đến 30 . Cơ sở dữ liệu trong trang tính khác có tên pivot_table_VBA cũng được cập nhật theo đó tự động .
Ghi chú :Nếu chúng tôi không muốn tự động - làm mới tất cả các bảng tổng hợp trong sổ làm việc thay vì chỉ là một tài khoản cụ thể , chúng ta có thể sử dụng đoạn mã sau. Mã này sẽ chỉ cập nhật bảng tổng hợp trong trang tính pivot_table_VBA khi chúng tôi thay đổi nguồn dữ liệu.
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("pivot_table_VBA ").PivotTables("PivotTable2").PivotCache.Refresh
End SubTrong mã này, pivot-pivot_table_VBA là tên trang tính có chứa PivotTable2. Chúng tôi có thể dễ dàng kiểm tra tên của trang tính và bảng tổng hợp .
Đọc thêm:Cách tạo cơ sở dữ liệu với biểu mẫu trong Excel
4. Tạo cơ sở dữ liệu cập nhật tự động có dữ liệu được tham chiếu từ trang tính khác trong Excel
Công việc :Tạo cơ sở dữ liệu bảng có dữ liệu sắp tới từ khác trang tính bảng . Với bất kỳ thay đổi nào trong nguồn dữ liệu , cơ sở dữ liệu nên cập nhật tự động .
Giải pháp :Đây là bảng trong trang tính có tên source_table với tên sản phẩm và đơn giá cá nhân của họ .
Bây giờ, chúng tôi đã tạo cơ sở dữ liệu (trong trang tính cơ sở dữ liệu ) đại diện cho chi tiết bán hàng . Tập dữ liệu chứa cột có tên là “ UnitPrice ”. Chúng tôi cần tham chiếu từng trong số dữ liệu trong cột Đơn vị giá vào bảng trong trang tính source_table. Để chèn dữ liệu vào cột UnitPrice trong bảng cơ sở dữ liệu-
- Nhập dấu bằng (=) trong ô F3 (đơn giá của Apple) .
- Bắt đầu vào bảng source_table .
- Nhấp ô C3 (đơn giá của Apple).
- Nhấn Enter.
- Bây giờ, bằng cách sử dụng Xử lý điền , điền vào UnitPrice cột có dữ liệu tham chiếu từ bảng nguồn trong trang tính source_table .
Đầu ra :Nếu chúng tôi thay đổi bất kỳ dữ liệu nào trong bảng nguồn trong trang tính source_table , cơ sở dữ liệu được cập nhật tự động .
Đọc thêm: Cách duy trì cơ sở dữ liệu khách hàng trong Excel
Những điều cần nhớ
Sử dụng mã VBA trong phương pháp 3 tự động hóa bảng tổng hợp của chúng tôi nhưng nó mất hoàn tác lịch sử . Nếu một thay đổi được thực hiện, chúng tôi không thể quay lại đến giai đoạn trước . Đây là một nhược điểm của việc sử dụng mã VBA để cập nhật bảng tổng hợp tự động.
Kết luận
Bây giờ, chúng ta đã biết cách tạo cơ sở dữ liệu trong Excel tự động cập nhật với 4 phương pháp phù hợp. Hy vọng rằng nó sẽ khuyến khích bạn sử dụng tính năng này một cách tự tin hơn. Mọi thắc mắc hoặc đề xuất đừng quên để vào khung bình luận bên dưới.
Các bài viết có liên quan
- Cách tạo Cơ sở dữ liệu Khoảng không quảng cáo trong Excel (3 Phương pháp Dễ dàng)
- Tạo Cơ sở dữ liệu Quan hệ trong Excel (Với các Bước Dễ dàng)
- Cách Tạo Cơ sở dữ liệu Có thể Tìm kiếm trong Excel (2 Thủ thuật Nhanh)