Việc sử dụng Excel là phổ biến và Excel thường là ứng dụng Office đi kèm để nhập, phân tích và thao tác dữ liệu. Microsoft Access cung cấp khả năng cơ sở dữ liệu quan hệ trong môi trường máy tính để bàn nhỏ gọn. Ngày càng ít người sử dụng Access, mặc dù nó có nhiều tính năng xử lý dữ liệu và giao diện thân thiện với người dùng. Access có tất cả các chức năng của một ứng dụng cơ sở dữ liệu quan hệ bao gồm khả năng tạo bảng, chỉ định và hạn chế các kiểu dữ liệu cho mỗi bảng, tạo các truy vấn đơn giản và nâng cao, thao tác SQL và tạo biểu mẫu cho đầu vào của người dùng. Excel, ngoài giao diện quen thuộc hơn, còn có khả năng lập biểu đồ nâng cao và chức năng và tùy chọn Pivot Table mở rộng.
Tích hợp Excel và Access, cho phép người ta tận dụng khả năng nhập dữ liệu dễ dàng và khả năng lập biểu đồ nâng cao của Excel, kết hợp với giao diện và môi trường cơ sở dữ liệu quan hệ của Access.
Dưới đây là một số mẹo hữu ích để trao đổi dữ liệu giữa hai Ứng dụng Office:
1) Hiểu logic liên quan đến cấu trúc và tạo bảng Access
Access là một cơ sở dữ liệu quan hệ như đã đề cập. Điều này có nghĩa là các bảng trong cơ sở dữ liệu Access đều có liên quan đến nhau. Mỗi bản ghi trong cơ sở dữ liệu Access có thể được nhận dạng duy nhất. Điều này đạt được thông qua việc sử dụng khóa chính. Khóa chính này thường bao gồm một trường, tuy nhiên, trong một số trường hợp, nó có thể là sự kết hợp của nhiều trường. Khóa chính này là bắt buộc và Access sẽ tự động thêm cột ID, cột này đóng vai trò là khóa chính cho bảng. Trong Excel, khóa chính không phải là yêu cầu bắt buộc khi tạo bảng, vì vậy người dùng thường bỏ qua khóa này.
Tình huống này được hiển thị bên dưới, hình ảnh đầu tiên cho thấy bảng Access trong cả Biểu dữ liệu và Dạng xem Thiết kế và khóa chính được thêm tự động. Hình ảnh thứ hai cho thấy bảng Excel và cách thức, dữ liệu rất có thể sẽ được nhập vào Excel.
Việc nhập dữ liệu trong bảng Excel không sai. Tuy nhiên, nếu bạn định sử dụng Excel làm phần mềm nhập dữ liệu và sau đó nhập các bảng vào Access sau này (điều này tương tự như việc sử dụng Excel làm biểu mẫu thân thiện với người dùng cho Access theo một cách nào đó), thì tốt nhất là ngay từ đầu để thêm khóa chính vào bảng Excel, khi thiết lập bảng tính trong Excel.
Điều này là để đảm bảo rằng người dùng làm quen và định hướng với khái niệm khóa chính và loại bỏ sự dư thừa và trùng lặp dữ liệu càng sớm càng tốt. Điều này đủ đơn giản để thực hiện trong Excel khi tạo bảng trong Excel. Nếu bạn có một bảng tương đối nhỏ, chưa chứa cột khóa chính và không có sự trùng lặp hoặc dư thừa trong bảng Excel, bạn có thể dễ dàng thêm cột này. Chỉ cần nhấp chuột phải vào ô trong cột đầu tiên của bạn trong bảng, chọn Chèn, Cột Bảng ở Bên trái. Sau đó đổi tên cột này và gọi nó là ID. Để điền vào cột này, hãy chọn ô đầu tiên của cột và nhập vào 1, sau đó chuyển đến ô thứ hai của cột và nhập vào 2, sau đó đánh dấu hai ô trên và kéo xuống bằng cách sử dụng Tự động điền để điền phần còn lại của cột .
Ngoài ra, một mẹo hữu ích cần nhớ là tên trường trong bảng Access tương đương với tiêu đề cột trong bảng Excel.
Bạn cũng có thể sao chép dữ liệu trong bảng từ Access và dán nó vào một trang tính mới trong Excel. Điều này được khuyến khích đối với bảng Access có lượng dữ liệu nhỏ. Chọn tất cả các hàng trong Access mà bạn muốn sao chép, chuyển đến tab Trang đầu, trong nhóm Bảng tạm, chọn Sao chép hoặc nhấn CTRL-C trên bàn phím của bạn.
Sau đó, trên một trang tính mới trong Sổ làm việc Excel, nhấn CTRL-V trên bàn phím của bạn để dán bảng Access vào trang tính Excel. Bảng Access được dán dưới dạng một dải ô với các tiêu đề và có thể được chuyển đổi thành bảng Excel, bằng cách chọn một ô trong dải ô và nhấn CTRL-T trên bàn phím của bạn. Đảm bảo rằng hộp kiểm, bảng của tôi có tiêu đề được đánh dấu, sau đó nhấn Ok, để chuyển đổi phạm vi thành bảng Excel.
Dẫn đầu từ những điểm nêu trên. Trong Access, thông tin được chia thành các thực thể hoặc chủ thể chính. Ví dụ:cơ sở dữ liệu Access được thiết kế cho một cửa hàng nhỏ sẽ có nhiều khả năng chứa các bảng riêng biệt được gọi là Sản phẩm, Đơn đặt hàng, Khách hàng và Nhân viên (với các mối quan hệ được chỉ định). Trong Excel, không có gì lạ khi xem thông tin, thông tin này sẽ được lưu trữ trong các bảng riêng biệt trong Access, được kết hợp trong một bảng Excel, như được hiển thị bên dưới.
Như đã đề cập, cách nhập dữ liệu vào bảng Excel không sai. Tuy nhiên, nếu Excel và Access sẽ được tích hợp, về mặt Excel là phần mềm nhập dữ liệu và Access cung cấp chức năng truy vấn và cơ sở dữ liệu. Tốt nhất là chia thông tin trong Excel được lưu trữ trong một bảng lớn thành bốn bảng nhỏ hơn và đặt mỗi bảng trên trang tính của chính nó như được hiển thị bên dưới. Ngoài ra, bạn nên khớp tên của các trường trong bảng trong Access, với tiêu đề của các cột trong bảng trong Excel, để tránh nhầm lẫn và duy trì tính nhất quán.
2) Loại bỏ các bản sao trong Excel, để dễ dàng thiết kế bảng sau này hoặc tích hợp dữ liệu nguồn vào Access
Trong nháy mắt, khó có thể nhìn thấy các giá trị duy nhất trong bảng Excel chứa nhiều hàng dữ liệu. Giả sử một người muốn xem số lượng sản phẩm duy nhất cho một bảng Excel, chứa 100 hàng dữ liệu, để thiết kế một bảng hiệu quả trong Access bằng cách sử dụng thông tin thu thập được từ sổ làm việc Excel. Sổ làm việc Excel trong ví dụ này dựa trên một số dữ liệu từ một nhà bán lẻ trực tuyến giả định. Bảng Excel chứa ngày sản phẩm được bán, tên sản phẩm và số lượng đã bán như hình dưới đây.
Trước hết, hãy tạo một bản sao của bảng tính có chứa bảng trong sổ làm việc, để giữ nguyên dữ liệu gốc và sau đó làm việc trên bản sao. Với một ô trong bảng Excel được chọn, để kích hoạt các tùy chọn Công cụ Bảng phân biệt ngữ cảnh, hãy chọn tab Thiết kế và trong nhóm Công cụ, hãy chọn tùy chọn Loại bỏ Bản sao như được hiển thị bên dưới.
Hộp thoại Loại bỏ các bản sao sẽ xuất hiện và bỏ chọn Ngày_Of_Sale và Số lượng và nhấp vào Ok. Một hộp thông báo sẽ bật lên cho biết có bao nhiêu giá trị trùng lặp đã được loại bỏ. Nhấp vào Ok.
Bảng kết quả trong Excel có thể được sử dụng để giúp thu thập thông tin về bảng Access cho cơ sở dữ liệu Access được đề xuất của nhà bán lẻ trực tuyến.
Đọc thêm:Cách sao chép các giá trị duy nhất sang một trang tính khác trong Excel (5 phương pháp)
3) Sử dụng Hàm TRIM () để xóa các khoảng trắng không cần thiết và sử dụng dấu gạch dưới để duy trì tính nhất quán giữa tên trường trong Access và tiêu đề cột trong Excel
Tên trường truy nhập không được bắt đầu bằng dấu cách ở đầu, vì vậy người ta có thể sử dụng hàm TRIM () trên tên của tiêu đề cột của bảng trong Excel, để loại bỏ dấu cách.
Ngoài ra, giả sử cuối cùng người ta cần nhập dữ liệu vào SQL Server hoặc một cơ sở dữ liệu khác, chẳng hạn như Oracle. Vì vậy, quy trình của giải pháp tích hợp sẽ là nhập dữ liệu trong Excel, sau đó xuất dữ liệu này sang Access, sau đó nhập các bảng Access vào cơ sở dữ liệu cấp doanh nghiệp như SQL Server hoặc Oracle. Các cơ sở dữ liệu khác không đọc dấu cách giữa tên các trường rất tốt. Vì vậy, thay vì gọi tên của cột tiêu đề trong bảng Excel, Tên của Sản phẩm gọi nó là Tên_of_Product, sử dụng dấu gạch dưới để dễ dàng tích hợp vào các cơ sở dữ liệu khác. SQL cũng xử lý kiểu đặt tên bảng này tốt hơn nhiều so với tên có khoảng trắng.
Người ta có thể sử dụng thuộc tính chú thích trong Access, vì vậy nếu tên trường trong bảng của bạn là Name_of_Product, hãy nhập chú thích hoặc nhãn làm Tên của sản phẩm trong dạng xem thiết kế. Vì vậy, trong dạng xem biểu dữ liệu hoặc trên biểu mẫu Tên của Sản phẩm được sử dụng thay thế, trong khi tên trường bên dưới có dấu gạch dưới. Điều này chủ yếu nhằm mục đích thẩm mỹ cho người xem phía trước của cơ sở dữ liệu trong Access.
Bài đọc tương tự
- Lỗi Thời gian Chạy 1004:Phương pháp Dán Đặc biệt của Lớp Phạm vi Không thành công
- Công thức để Sao chép và Dán Giá trị trong Excel (5 Ví dụ)
- Cách Dán Liên kết và Chuyển vị trong Excel (8 Cách Nhanh)
- Sao chép và Dán trong Excel với các Ô được Hợp nhất (2 Phương pháp)
- Cách chỉ sao chép các ô có thể nhìn thấy mà không có tiêu đề bằng VBA
4) Nhập dữ liệu vào Access từ Excel - xử lý hai tình huống chính
- Trong trường hợp một, một người có cơ sở dữ liệu Access hoàn toàn trống và một người muốn nhập dữ liệu từ Excel (các bảng trong Excel được thiết lập với ID và sự dư thừa và trùng lặp được giảm thiểu càng nhiều càng tốt) và sử dụng Excel như chương trình hoặc biểu mẫu nhập dữ liệu. Khi cơ sở dữ liệu Access đang mở, hãy chuyển đến tab Dữ liệu ngoài, trong nhóm Nhập &Liên kết, chọn Excel như được hiển thị.
Trình hướng dẫn Lấy Dữ liệu Bên ngoài - Bảng tính Excel sẽ bật lên. Điều hướng đến Sổ làm việc Excel có chứa dữ liệu. Chọn Liên kết đến nguồn dữ liệu bằng cách tạo tùy chọn bảng được liên kết. Điều này cho phép nhập dữ liệu trong Excel vì chúng tôi muốn người dùng nhập dữ liệu trong Excel. Bất kỳ thay đổi nào được thực hiện đối với dữ liệu này trong Excel đều được phản ánh trong Access, nhưng không thể chỉnh sửa gì trong Access. Nó gần như chỉ sẵn sàng trong Access, về mặt nhập dữ liệu ở dạng xem biểu dữ liệu. Nhấp vào Ok.
Nếu bảng được thiết lập chính xác trong Excel, Access sẽ tự động phát hiện và kiểm tra xem Hàng Đầu tiên Có chứa Tiêu đề Cột như được hiển thị bên dưới hay không.
Nhấp vào Tiếp theo sẽ dẫn một người đến bước cuối cùng của quy trình, bước này yêu cầu một người nhập tên bảng được liên kết và sau đó nhấp vào Kết thúc.
Một thông báo sẽ bật lên cho biết bảng đã được liên kết.
Điều này hiển thị dữ liệu Excel với chức năng SQL bổ sung và trình tạo biểu thức để tạo truy vấn phức tạp, có sẵn trong môi trường Access. Kết quả của các truy vấn phức tạp sau này có thể được sử dụng trong bảng điều khiển Excel. Khuyến khích, nhưng không bắt buộc giữ các bảng nhập dữ liệu Excel và cơ sở dữ liệu Access với các bảng được liên kết trong cùng một thư mục hoặc thư mục chỉ cho mục đích tổ chức.
- Trong trường hợp hai, một người có cơ sở dữ liệu Access trống hoặc đã được điền và một người muốn nhập các bảng Excel, nhưng chưa liên kết chúng. Một người muốn chỉnh sửa chúng trong Access. Theo cách đó, bất kỳ thay đổi nào được thực hiện sau này trong trang tính Excel sẽ không được phản ánh trong Access và sau đó người ta có toàn bộ dữ liệu trong Access để chỉnh sửa và truy vấn. Đây thường là tùy chọn được chọn khi một người cần duy trì sự kiểm soát chặt chẽ đối với các loại dữ liệu được nhập vào bảng.
Một lần nữa, khi cơ sở dữ liệu Access của bạn đang mở, hãy chuyển đến tab Dữ liệu Ngoài, trong nhóm Nhập &Liên kết, chọn Excel như được hiển thị.
Trong trường hợp này, khi trình hướng dẫn bật lên, hãy chọn Nhập dữ liệu nguồn vào bảng mới trong cơ sở dữ liệu hiện tại, để tạo bản sao của bảng trong Access hiện được hủy liên kết với bảng Excel gốc. Nhấp vào Ok.
Nhấp vào Tiếp theo trên màn hình tiếp theo.
Sau đó, một người sẽ chỉ định thông tin về các trường mà người ta đang nhập, như được hiển thị bên dưới. Trình hướng dẫn cũng cho phép người dùng chọn các trường cần bỏ qua. Vì thường các bảng Excel có thể chứa dữ liệu thực sự cần có trong các bảng khác trong Access, nên khi đó người ta có thể tránh nhập các cột này vào bảng hiện tại.
Sau đó, một người có thể cho phép Access thêm khóa chính hoặc chọn khóa chính của riêng một người. Trong trường hợp này, vì bảng đã có cột ID được chỉ định trong Excel, nên tùy chọn thứ hai được chọn như được hiển thị.
Nhấp vào Tiếp theo.
Sau đó, chọn tên cho bảng trong Access và nhấp vào Kết thúc.
Bạn sẽ được hỏi có muốn lưu các bước nhập hay không, bây giờ chỉ cần nhấp vào đóng. The table is now in Access for further editing, entry, and manipulation and is unlinked to the Excel workbook.
5) Using Table Analyzer to normalize imported data and split the data into separate tables in Access
Later versions of Access, have the Table Analyzer tool, which helps users separate one table into many tables. This is especially useful if one did import the data from a big Excel table and one is struggling to separate the tables. One can launch this wizard as part of the import process and check the box, I would like a wizard to analyze my table after importing the data as shown below.
Or one can access it, if the tables are already imported as standalone Access tables, by going to Database Tools, in the Analyze group, choose Analyze Table.
The Table Analyzer wizard should be launched, which will guide you through the steps of creating separate tables.
Table Analyzer, however, does not always guess right, so use it with caution.
6) Importing data from Access into Excel and using VLOOKUP to link the data and populate columns
One can import Access tables into Excel worksheets. With an Excel workbook opened, go to the Data tab, in the Get External Data group, choose From Access as shown below.
Navigate to the Access database containing the tables, you want to import into your Excel spreadsheet.
Select the table one wants to import, in this case, the ProductT.
Click Ok.
Choose to Import the data as a Table, on the existing worksheet, choose cell A1, and then Click Ok.
The table is imported as an Excel table.
The same was done for the three other tables in the database, namely Orders, Employees, and Customers tables.
One now has an Orders table which looks like the one below.
This table with numbers in Customer_ID, Product_ID, and Employee_ID can be counter-intuitive and confusing for Excel users not used to the relational database theory and tables. Thus one could add three helper columns to the table called Actual_Customer, Actual_Product, and Actual_Employee as shown below.
In Cell F2, the following formula was used:
=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)
Where, [@[Customer_ID]] refers to cell C2, which contains the lookup value, Table_TablesToBeImportedIntoExcel.accdb5 refers to the imported Customers table on the Customers sheet, (Excel gives this table a default name), highlight the data, but not the column headings, the same way one would for a normal VLOOKUP, the customer name is in column 2, and the type of lookup is an exact match.
Double click and send the formula down to populate the Actual_Customer column.
Do the same thing to populate the Actual_Product and Actual_Employee columns. So one can quickly repopulate Excel tables with actual text values as needed, using the VLOOKUP functionality in order to orientate the Excel users.
7) Using data already set up in a relational Access database, for Power Pivot manipulation
Power Pivot is a Business Intelligence tool available with Excel 2013 and later versions of Excel. It also requires data to be input in a relational manner. So, what you can do when you are importing Access tables into Excel (already normalized and designed using relational data logic), is to add the imported tables to the data model.
In a blank workbook, go to Data, get External data and choose from Access as for the case above. However, this time when asked to select a table, check the Enable selection of multiple tables option in the Select Table dialog box and select all the tables as shown below:
Click Ok.
Make sure the PivotTable Report option is selected, the Add this data to the Data Model should be checked because we imported multiple tables. If you only import one table at a time, you will need to check this option yourself. If you click on the drop-down arrow next to Properties, you should see that the relationships between the tables will also be imported.
Click Ok to create the Pivot TableReport, with the tables already added to the Data Model. The relationships should be visible in the PowerPivot for Excel Window, Diagram view. Thus the data is ready for further manipulation using Power Pivot functionality.
Download Files
Exchange-Data-Between-Access-and-Excel
Kết luận
Excel and Access integration allow one tallow advantage of the combination of features in Excel and Access. The key to integrating Excel and Access successfully is understanding a little bit about relational database design basics.
Understanding a little bit of relational design and table structure will also set you in good stead with respect to taking advantage of Power Pivot and related Business Intelligence tools in the Excel environment.
Please feel free to comment and tell us if you integrate Excel and Access, whether or not you use Power Pivot, and any other tips for integrating Excel and Access, that you may have.
Các bài viết có liên quan
- Excel VBA:Copy Cell Value and Paste to Another Cell
- Excel VBA:Copy Range to Another Workbook
- [Fixed]:Right Click Copy and Paste Not Working in Excel (11 Solutions)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- How to Apply VBA PasteSpecial and Keep Source Formatting in Excel