Hướng dẫn how to create a database in excel that updates automatically - cách tạo cơ sở dữ liệu trong excel cập nhật tự động

Bài viết này minh họa cách tạo cơ sở dữ liệu trong Excel cập nhật theo cách giúp đỡ của 4 phương pháp hữu ích. Nó có một tính năng quan trọng cần thiết trong 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, cập nhật tự động theo dữ liệu nguồn là rất quan trọng. Hãy cùng đi sâu vào các ví dụ để làm quen với các phương pháp.create a database in Excel that updates automatically with the help of 4 useful methods. It’s an important feature required while working with dynamic data. When the database depends on data from another source, automatic update according to the source data is very crucial. Let’s dive into the examples to get familiar with the methods.

Tải xuống Sổ làm việc thực hành

Tải xuống sách bài tập thực hành này để tập thể dục trong khi bạn đang đọc bài viết này.

4 Phương pháp hữu ích để tạo cơ sở dữ liệu tự động cập nhật trong Excelin Excel

Nhiệm vụ: Trích xuất dự báo thời tiết 14 ngày cho New York, Hoa Kỳ từ Web và tạo cơ sở dữ liệu Excel mà tự động cập nhật.: Extract a 14-day weather forecast for New York, USA from the web and create an Excel database that’ll update automatically.

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ừ //www.timeanddate.com/weather/usa/new-york/ext liên kết trang web. Chúng tôi có thể sao chép bảng trong ảnh chụp màn hình sau vào bảng tính Excel để tạo cơ sở dữ liệu. Nhưng thật không may, cơ sở dữ liệu sẽ không cập nhật theo dữ liệu trang web trừ khi chúng tôi kết nối nó với liên kết nguồn. Nếu cơ sở dữ liệu được kết nối với liên kết nguồn, Excel tạo điều kiện cho 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.: We’ve found several websites that provide weather forecasts online. For this example, we’ll use the forecast for New York, the USA from the //www.timeanddate.com/weather/usa/new-york/ext website link. We could copy the table in the following screenshot into an Excel worksheet to create a database. But unfortunately, the database is not going to update according to the website data unless we connect it to the source link. If the database is connected to the source link, Excel facilitates us with options to update the database manually and automatically.

Giải pháp: Chúng tôi cần làm theo các bước dưới đây để tự động cập nhật cơ sở dữ liệu dự báo thời tiết.: We need to follow the steps below to automatically update our weather forecasting database.

Bước 1: Kết nối với web

• Chuyển đến tab Dữ liệu trong Ribbon Excel.to the Data tab in the Excel Ribbon.
• Nhấp vào tùy chọn từ Web từ tab GET & Biến đổi.on the From Web option from the Get & Transform tab.

• 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.dialogue box, paste the URL from where we want to extract data for our database and hit OK.

• Trong cửa sổ Navigator, chúng ta có thể thấy các bảng được tìm thấy trên trang web. Với sự trợ giúp của chế độ xem bảng, chúng ta có thể thấy dữ liệu được trích xuất ở dạng bảng.Navigator window, we can see the tables found on the website. With the help of the Table View, we can see the data extracted in a table form.
• Để 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 vào tùy chọn Biến đổi dữ liệu & NBSP;edit the extracted data table before loading it into the Excel database, click on the Transform Data option.

Bước 2: Biến đổi dữ liệu

• Trong trình chỉnh sửa truy vấn Power, chúng ta có thể loại bỏ các cột không mong muốn. Để làm điều đó, nhấp chuột phải vào tiêu đề cột và chọn tùy chọn Xóa.Power Query Editor, we can remove unwanted columns. To do that, right-click on the column header and choose the Remove option.

• Chúng tôi đã loại bỏ một số cột và chỉ giữ 6 cột để nhập vào cơ sở dữ liệu.removed several columns and kept only 6 columns to import into the database.
• Bây giờ, từ tab Home, nhấp vào nút Đóng & Tải.Home tab, click on the Close & Load button.
• Chọn tùy chọn Đóng & Tải.the Close & Load option.

Bước 3: Nhập dữ liệu vào cơ sở dữ liệu Excel

• Trong cửa sổ Nhập dữ liệu, nhấp vào tùy chọn bảng tính hiện có và chọn ô bắt đầu để nhập dữ liệu và nhấp OK để thực thi lệnh.Import Data window, click on the Existing worksheet option and choose the starting cell to import data, and click OK to execute the command.

• 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 với nguồn trang web.created an Excel database with data that is connected to a website source.

Bước 4: Bật tính năng cập nhật tự động

• Nhấp vào bảng cơ sở dữ liệu.on the database table.
• Chuyển đến tab Dữ liệu trong Ribbon Excel.to the Data tab in the Excel Ribbon.
• Nhấp vào tùy chọn từ Web từ tab GET & Biến đổi.on the Refresh All button.
• 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.the Connection Properties option.

• Trong cửa sổ Navigator, chúng ta có thể thấy các bảng được tìm thấy trên trang web. Với sự trợ giúp của chế độ xem bảng, chúng ta có thể thấy dữ liệu được trích xuất ở dạng bảng.set the time after which we want to refresh the database automatically in the Refresh every input box and hit OK.

Để 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 vào tùy chọn Biến đổi dữ liệu & NBSP; How to Use Database Functions in Excel [With Examples]

Bước 2: Biến đổi dữ liệuCreate a Database with Pivot Table That Updates Automatically in Excel

Trong trình chỉnh sửa truy vấn Power, chúng ta có thể loại bỏ các cột không mong muốn. Để làm điều đó, nhấp chuột phải vào tiêu đề cột và chọn tùy chọn Xóa.create a pivot table based on a source dataset. As the pivot table serves as a database in Excel, enabling the auto refresh feature for the pivot table will update our Excel database automatically. The following steps will guide you through the process.

Chúng tôi đã loại bỏ một số cột và chỉ giữ 6 cột để nhập vào cơ sở dữ liệu.

Bây giờ, từ tab Home, nhấp vào nút Đóng & Tải.dataset that represents sale details for a shop.

Chọn tùy chọn Đóng & Tải.make a pivot table,

• Bước 3: Nhập dữ liệu vào cơ sở dữ liệu Excelthe whole dataset.
• Trong cửa sổ Nhập dữ liệu, nhấp vào tùy chọn bảng tính hiện có và chọn ô bắt đầu để nhập dữ liệu và nhấp OK để thực thi lệnh.to the Insert tab.
• 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 với nguồn trang web.on the Pivot Table button.
• Bước 4: Bật tính năng cập nhật tự độngthe From Table/Range option.

• Nhấp vào bảng cơ sở dữ liệu.PivotTable from table or range window, click on the New worksheet option and hit OK.

• Nhấp vào nút Làm mới tất cả & NBSP;pivot table database has been created in a new worksheet.

Chọn tùy chọn Thuộc tính kết nối.

Bây giờ, hãy đặt thời gian sau đó chúng tôi muốn tự động làm mới cơ sở dữ liệu trong việc làm mới mọi hộp đầu vào và nhấn OK.update the pivot table each time the workbook is opened, not each time a change is made to the dataset. So, it is like partial automation of the pivot table. Let’s follow the steps to enable the auto–refreshing feature for a pivot table:

• Đọc thêm: Cách sử dụng các chức năng cơ sở dữ liệu trong Excel [với các ví dụ] any cell of the pivot table to open the context menu.
• 2. Tạo cơ sở dữ liệu với bảng Pivot tự động cập nhật trong ExcelPivotTable Options from the context menu.

• Trong hình minh họa này, chúng tôi sẽ tạo một bảng trục dựa trên bộ dữ liệu nguồn. Vì bảng Pivot đóng vai trò là cơ sở dữ liệu trong Excel, việc cho phép tính năng làm mới tự động cho bảng Pivot sẽ tự động cập nhật cơ sở dữ liệu Excel của chúng tôi. Các bước sau đây sẽ hướng dẫn bạn trong suốt quá trình. PivotTable Options window, go to the Data tab and check the Refresh data when opening the file option.

• Bước 1: Tạo bảng trụcOK to close the window.

Hãy nói rằng chúng tôi có một bộ dữ liệu đại diện cho chi tiết bán hàng cho một cửa hàng. How to Create a Simple Database in Excel VBA

Để tạo một bảng xoay,Auto Refresh a Pivot Table to Update Database Automatically Using VBA Code in Excel

Chọn toàn bộ bộ dữ liệu.: Using simple VBA code we can automatically update our pivot table when we change any source data. Most importantly it happens immediately unlike the previous method where we need to close and reopen the file again to see the updates.

Giải pháp: Để làm cho nó xảy ra, hãy làm theo hướng dẫn!: To make it happen let’s follow the guide!

• Bên phải, nhấp vào tên bảng tính và chọn tùy chọn xem Code & NBSP;–click the worksheet name and choose the View Code option.

Mã: Chèn mã VBA sau trong trình soạn thảo Visual Basic.: Insert the following VBA code in the visual basic editor.

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 ô trong tệp nguồn. Tất cả các bảng trục liên quan đến nguồn sẽ được cập nhật cho phù hợp và ngay lập tức.: This VBA code will run anytime we change cell data in the source file. All the pivot tables related to the source will be updated accordingly and instantly.

Để kiểm tra quy trình trên, chúng tôi đã tạo một bảng trục trong một tờ 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 Apple từ 50 thành 30. Cơ sở dữ liệu trong bảng khác có tên là PIVOT_TABLE_VBA cũng tự động cập nhật theo cách tự động.check the above procedure, we’ve created a pivot table in a sheet named pivot_table based on the source data in the sheet named VBA. We changed the quantity of Apple from 50 to 30. The database in the other sheet named pivot_table_VBA also updated accordingly automatically.

Ghi chú: Nếu chúng tôi không muốn tự động tạo lại các bảng trục trong sổ làm việc mà chỉ là một bảng cụ thể, chúng tôi có thể sử dụng mã sau. Mã này sẽ chỉ cập nhật bảng Pivot trong bảng pivot_table_vba & nbsp; khi chúng tôi thay đổi nguồn dữ liệu.: If we don’t want to auto–refresh all the pivot tables in the workbook rather just a specific one, we can use the following code. This code will only update the pivot table in the sheet pivot_table_VBA  when we change the data source.

Private Sub Worksheet_Change[ByVal Target As Range]
Worksheets["pivot_table_VBA  "].PivotTables["PivotTable2"].PivotCache.Refresh
End Sub

Trong mã này, pivot-pivot_table_vba là tên trang tính có chứa pivottable2. Chúng ta có thể dễ dàng kiểm tra tên của một bảng tính và bảng trục.pivot-pivot_table_VBA is the sheet name that contains the PivotTable2. We can easily check the name of a worksheet and a pivot table.

Đọc thêm: Cách tạo cơ sở dữ liệu có biểu mẫu trong Excel How to Create a Database with Form in Excel

4. Tạo cơ sở dữ liệu cập nhật tự động có dữ liệu được tham chiếu từ một tờ khác trong ExcelCreate a Database That Updates Automatically Having Data Referenced from Another Sheet in Excel

Nhiệm vụ: Tạo một bảng cơ sở dữ liệu có Datacoming từ một công việc khác. Với bất kỳ thay đổi nào trong nguồn gốc, cơ sở dữ liệu sẽ được cập nhật theo cách thức.: Create a database table having data coming from another worksheet table. With any change in the source data, the database should update automatically.

Giải pháp: Dưới đây là một bảng trong trang tính có tên Source_Table với tên sản phẩm và đơn giá riêng lẻ của chúng.: Here is a table in the sheet named source_table with the product name and their individual unit price.

Bây giờ, chúng tôi đã tạo ra một cơ sở dữ liệu [trong cơ sở dữ liệu bảng] đại diện cho chi tiết bán hàng. Bộ dữ liệu chứa một cột có tên là Unit UnitPrice. Chúng ta cần tham chiếu từng dữ liệu trong cột UnitPrice vào bảng trong bảng Source_Table. Để chèn dữ liệu vào cột đơn vị trong bảng cơ sở dữ liệu-database [in sheet database] that represents sale details. The dataset contains a column named “UnitPrice”. We need to reference each of the data in the UnitPrice column to the table in the source_table sheet. To insert data in the UnitPrice column in the database table-

• Loại dấu bằng [=] trong ô F3 [giá đơn vị của Apple].equal sign [=] in cell F3 [unit price of Apple].

• Chuyển đến trang tính của Source_Table.to the sheet source_table.
• Nhấp vào ô C3 [Đơn giá của Apple].cell C3 [unit price of Apple].

• Press Enter.Enter.

• Bây giờ, bằng cách sử dụng tay cầm điền, điền vào cột UnitPrice với dữ liệu tham chiếu từ bảng nguồn trong trang Source_table.Fill Handle, fill up the UnitPrice column with reference data from the source table in sheet 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 Source_table, cơ sở dữ liệu sẽ được cập nhật tự động.: If we change any data in the source table in sheet source_table, the database gets updated automatically.

Đọc thêm: Cách duy trì cơ sở dữ liệu khách hàng trong Excel How to Maintain Customer Database in Excel

Những điều cần ghi nhớ

Sử dụng mã VBA trong Phương pháp 3 tự động hóa các bảng trục của chúng tôi nhưng nó mất lịch sử hoàn tác. Nếu một sự thay đổi được thực hiện, chúng ta không thể quay trở lại giai đoạn trước. Đây là một nhược điểm của việc sử dụng mã VBA để tự động cập nhật các bảng xoay vòng. VBA code in method 3 automates our pivot tables but it loses the undo history. If a change is made, we cannot go back to the previous stage. This is a disadvantage of using a VBA code to update pivot tables automatically.

Sự kết luận

Bây giờ, chúng tôi biết cách tạo cơ sở dữ liệu trong Excel cập nhật tự động với 4 phương thức phù hợp. Hy vọ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. Bất kỳ câu hỏi hoặc đề xuất nào don don quên đặt chúng vào hộp bình luận bên dưới.

Những bài viết liên quan

• Cách tạo cơ sở dữ liệu hàng 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 thủ thuật nhanh]