Bài viết minh họa cách tạo cơ sở dữ liệu trong Excel tự động cập nhật nhờ 4 phương pháp hữu ích. Tính năng quan trọng khi làm việc với dữ liệu động. Khi database phụ thuộc dữ liệu từ nguồn khác, tự động cập nhật theo dữ liệu nguồn rất quan trọng. Hãy đi vào ví dụ để làm quen 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 tự động cập nhật trong Excel
Nhiệm vụ: Trích xuất (Extract) Dự báo thời tiết 14 ngày cho New York, Mỹ từ Web và tạo cơ sở dữ liệu Excel tự động cập nhật.
Phân tích vấn đề: Tìm thấy vài trang web cung cấp dự báo thời tiết trực tuyến. Ví dụ này dùng dự báo cho New York, Mỹ từ link https://www.timeanddate.com/weather/usa/new-york/ext. Có thể sao chép (copy) bảng (table) trong chụp màn hình sau vào worksheet Excel để tạo database. Nhưng đáng tiếc, database không cập nhật theo dữ liệu trang web trừ khi kết nối (connect) nó với link nguồn (source link). Nếu database được kết nối với link nguồn, Excel hỗ trợ (facilitates) tùy chọn cập nhật (update) database thủ công (manually) và tự động (automatically).
Giải pháp: Cần làm theo các bước sau để tự động cập nhật database dự báo thời tiết.
Bước 1: Kết nối với Web
- Vào tab Dữ liệu (Data) trong Ribbon Excel.
- Bấm vào Từ Web (From Web) từ tab Lấy & Chuyển đổi (Get & Transform).
- Trong hộp thoại (dialogue box), dán (paste) URL nơi muốn trích xuất dữ liệu (extract data) cho database và bấm OK.
- Trong cửa sổ Trình điều hướng (Navigator), thấy các bảng (tables) tìm được trên trang web. Với Xem bảng (Table View), thấy dữ liệu trích xuất (data extracted) dạng bảng (table).
- Để chỉnh sửa (edit) bảng dữ liệu trích xuất trước khi tải (load) vào database Excel, bấm (click) vào Chuyển đổi dữ liệu (Transform Data).
Bước 2: Chuyển đổi dữ liệu (Transform Data)
- Trong Trình soạn thảo Power Query (Power Query Editor), có thể xóa cột không cần (remove unwanted columns). Chuột phải vào tiêu đề cột (column header) chọn Xóa (Remove).
- Đã xóa vài cột chỉ giữ 6 cột để nhập (import) vào database.
- Từ tab Trang chủ (Home), bấm nút Đóng & Tải (Close & Load).
- Chọn tùy chọn Đóng & Tải (Close & Load).
Bước 3: Nhập dữ liệu vào Database Excel
- Trong cửa sổ Nhập dữ liệu (Import Data), bấm vào tùy chọn Sheet hiện có (Existing worksheet) và chọn ô bắt đầu (starting cell) để nhập dữ liệu (import data), bấm OK thực thi.
- Đã tạo (created) thành công database Excel với dữ liệu kết nối (connected) với nguồn trang web (website source).
Bước 4: Bật tính năng Tự động cập nhật (Auto Update)
- Bấm vào bảng database.
- Vào tab Dữ liệu (Data) trong Ribbon Excel.
- Bấm nút Làm mới tất cả (Refresh All).
- Chọn tùy chọn Thuộc tính kết nối (Connection Properties).
- Bây giờ, đặt (set) thời gian (time) sau đó muốn làm mới (refresh) database tự động (automatically) trong ô Làm mới mỗi (Refresh every) và bấm OK.
Xem thêm: Cách dùng hàm Database trong Excel (Với ví dụ)
2. Tạo Database với Bảng Pivot tự động cập nhật trong Excel
Minh họa này tạo (create) bảng Pivot (pivot table) dựa trên tập dữ liệu nguồn (source dataset). Vì bảng Pivot đóng vai trò (serves) như database trong Excel, bật (enabling) tính năng tự động làm mới (auto refresh) cho bảng Pivot sẽ cập nhật (update) database Excel tự động. Các bước (steps) sau hướng dẫn quy trình (process).
Bước 1: Tạo Bảng Pivot (Create a Pivot Table)
Giả sử có tập dữ liệu (dataset) biểu diễn chi tiết bán hàng (sale details) cho cửa hàng (shop).
Để tạo (make) bảng Pivot,
- Chọn (Select) toàn bộ tập dữ liệu (whole dataset).
- Vào tab Chèn (Insert).
- Bấm nút Bảng Pivot (Pivot Table).
- Chọn (Choose) tùy chọn Từ Bảng/Phạm vi (From Table/Range).
- Trong cửa sổ Bảng Pivot từ bảng hoặc phạm vi (PivotTable from table or range), bấm vào tùy chọn Sheet mới (New worksheet) và bấm OK.
- Một database bảng Pivot đã tạo (created) trong sheet mới (new worksheet).
Bước 2: Bật tính năng Tự động làm mới cho Bảng Pivot để cập nhật Database tự động
Phương pháp này cập nhật (update) bảng Pivot mỗi lần (each time) workbook được mở (opened), không phải mỗi lần thay đổi tập dữ liệu. Vậy đây là tự động hóa một phần (partial automation) của bảng Pivot. Làm theo bước để bật (enable) tính năng tự động làm mới (auto-refreshing) cho bảng Pivot:
- Chuột phải (Right-click) bất kỳ ô (cell) nào của bảng Pivot mở menu ngữ cảnh (context menu).
- Chọn Tùy chọn Bảng Pivot (PivotTable Options) từ menu ngữ cảnh.
- Từ cửa sổ Tùy chọn Bảng Pivot (PivotTable Options), tab Dữ liệu (Data) tick (check) tùy chọn Làm mới dữ liệu khi mở file (Refresh data when opening the file).
- Cuối cùng, bấm OK đóng cửa sổ.
Xem 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 Pivot cập nhật Database tự động dùng mã VBA trong Excel
Nhiệm vụ: Dùng mã VBA đơn giản có thể tự động cập nhật (automatically update) bảng Pivot khi thay đổi (change) bất kỳ dữ liệu nguồn (source data). Quan trọng nhất: xảy ra ngay lập tức (immediately) không như phương pháp trước (previous method) cần đóng (close) và mở lại (reopen) file để thấy cập nhật (updates).
Giải pháp: Để làm được, hãy làm theo hướng dẫn!
- Chuột phải (Right-click) tên worksheet và chọn Xem mã (View Code).
Mã (Code): Chèn (Insert) mã VBA sau vào trình soạn thảo Visual Basic.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Kết quả (Output): Mã VBA này chạy bất cứ khi thay đổi dữ liệu ô (change cell data) trong file nguồn (source file). Tất cả bảng Pivot liên quan đến nguồn (source) sẽ cập nhật (updated) tương ứng và ngay lập tức (instantly).
Để kiểm tra (check) thủ tục trên, đã tạo bảng Pivot trong sheet tên pivot_table dựa trên dữ liệu nguồn (source data) trong sheet tên VBA. Thay đổi số lượng (quantity) của Táo (Apple) từ 50 thành 30. Database ở sheet khác tên pivot_table_VBA cũng cập nhật (updated) tương ứng tự động (automatically).
Lưu ý (Notes): Nếu không muốn tự động làm mới (auto-refresh) tất cả bảng Pivot trong workbook mà chỉ một bảng cụ thể (specific one), dùng mã sau. Mã này chỉ cập nhật (update) bảng Pivot trong sheet pivot_table_VBA khi thay đổi nguồn dữ liệu (data source).
Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("pivot_table_VBA ").PivotTables("PivotTable2").PivotCache.Refresh End SubTrong mã, pivot-pivot_table_VBA là tên sheet chứa PivotTable2. Dễ dàng kiểm tra tên worksheet và bảng Pivot.
Xem thêm: Cách tạo Cơ sở dữ liệu có Form trong Excel
4. Tạo Database tự động cập nhật có dữ liệu tham chiếu từ Sheet khác trong Excel
Nhiệm vụ: Tạo bảng database có dữ liệu (data) đến (coming) từ bảng (table) worksheet khác (another worksheet). Với bất kỳ thay đổi (change) trong dữ liệu nguồn (source data), database phải cập nhật (update) tự động (automatically).
Giải pháp: Đây là bảng (table) trong sheet tên source_table với tên sản phẩm (product name) và đơn giá (unit price) riêng lẻ.
Bây giờ, đã tạo (created) database (trong sheet database) biểu diễn chi tiết bán hàng (sale details). Tập dữ liệu chứa cột (column) tên "Đơn giá (UnitPrice)". Cần tham chiếu (reference) từng dữ liệu (data) trong cột Đơn giá (UnitPrice column) đến bảng (table) trong sheet source_table. Để chèn dữ liệu vào cột UnitPrice trong bảng database-
- Gõ dấu bằng (=) trong ô F3 (đơn giá Táo).
- Đến (Go) sheet source_table.
- Bấm (Click) ô C3 (đơn giá Táo).
- Nhấn Enter.
- Dùng Fill Handle, điền cột Đơn giá (UnitPrice) với dữ liệu tham chiếu (reference data) từ bảng nguồn (source table) trong sheet source_table.
Kết quả (Output): Nếu thay đổi (change) bất kỳ dữ liệu (data) trong bảng nguồn (source table) trong sheet source_table, database được cập nhật tự động (updated automatically).
Xem thêm: Cách duy trì Cơ sở dữ liệu khách hàng trong Excel
Lưu ý quan trọng
Dùng mã VBA phương pháp 3 tự động hóa (automates) bảng Pivot nhưng mất (loses) lịch sử hoàn tác (undo history). Nếu thay đổi (change) được làm, không thể quay lại (cannot go back) giai đoạn trước (previous stage). Đây là nhược điểm dùng mã VBA cập nhật bảng Pivot tự động.
Kết luận
Bây giờ biết cách tạo database trong Excel tự động cập nhật với 4 phương pháp phù hợp. Hy vọng khuyến khích bạn dùng tính năng này tự tin hơn. Mọi câu hỏi hoặc góp ý đừng quên để trong khung bình luận dưới.
Bài viết liên quan
- Cách tạo Cơ sở dữ liệu tồn kho 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 mẹo nhanh)