Việc sử dụng Excel rất phổ biến và Excel thường là ứng dụng Office đầu tiên cho nhập liệu, 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. Ít người dùng Access hơn, dù nó có nhiều tính năng xử lý dữ liệu và giao diện thân thiện. Access có tất cả chức năng của ứng dụng cơ sở dữ liệu quan hệ bao gồm tạo bảng, chỉ định và giới hạn kiểu dữ liệu cho mỗi bảng, tạo truy vấn đơn giản và nâng cao, thao tác SQL, và tạo form cho nhập liệu người dùng. Excel, ngoài việc là giao diện quen thuộc hơn, có khả năng biểu đồ nâng cao và chức năng Pivot Table mở rộng.
Tích hợp Excel và Access, cho phép tận dụng sự dễ dàng nhập liệu và khả năng 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 tiện lợi để 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à cơ sở dữ liệu quan hệ như đã nói. Điều này nghĩa là các bảng trong cơ sở dữ liệu Access đều liên quan đến nhau. Mỗi bản ghi trong cơ sở dữ liệu Access phải có thể xác định duy nhất. Điều này đạt được thông qua việc dùng khóa chính (primary key). Khóa chính thường gồm một trường, tuy nhiên, trong một số trường hợp, nó có thể là tổ hợp của nhiều trường. Khóa chính này là bắt buộc và Access tự động thêm cột ID, đóng vai trò khóa chính cho bảng. Trong Excel, khóa chính không phải yêu cầu bắt buộc khi tạo bảng, nên người dùng thường bỏ qua nó.
Tình huống này được hiển thị dưới đây, hình ảnh đầu tiên cho bảng Access ở cả Chế độ Datasheet (Trang dữ liệu) và Design View (Chế độ thiết kế) và khóa chính tự động thêm. Hình thứ hai cho bảng Excel và cách dữ liệu thường được nhập trong Excel.
Việc nhập dữ liệu trong bảng Excel không sai. Tuy nhiên, nếu bạn dự định dùng Excel làm phần mềm nhập liệu rồi nhập bảng vào Access sau (tương tự như dùng Excel như form thân thiện cho Access), thì tốt nhất, từ đầu, hãy thêm khóa chính vào bảng Excel khi thiết lập bảng tính.
Điều này đảm bảo người dùng quen và định hướng với khái niệm khóa chính và loại bỏ dự phòng và trùng lặp dữ liệu sớm nhất có thể. Việc này đủ đơn giản trong Excel khi tạo bảng. Nếu bạn có bảng khá nhỏ, chưa có cột khóa chính, và không có trùng lặp hay dự phòng trong bảng Excel, bạn 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ảng, chọn Insert (Chèn), Table Columns to the Left (Cột bảng sang trái). Sau đó đổi tên cột này và gọi là ID. Để điền cột này, chọn ô đầu của cột và gõ 1, rồi đến ô thứ hai của cột gõ 2, sau đó đánh dấu hai ô trên và kéo xuống dùng Autofill (Tự động điền) để điền phần còn lại của cột.
Cũng có mẹo tiệ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 vào trang tính mới trong Excel. Điều này khuyến khích cho bảng Access với lượng dữ liệu nhỏ. Chọn tất cả hàng trong Access bạn muốn sao chép, vào tab Home, nhóm Clipboard, chọn Copy, hoặc nhấn CTRL-C trên bàn phím.
Sau đó, trên trang tính mới trong Sổ làm việc Excel, nhấn CTRL-V trên bàn phím để dán bảng Access vào trang tính Excel. Bảng Access được dán như một phạm vi có tiêu đề và có thể chuyển thành bảng Excel, bằng cách chọn ô trong phạm vi và nhấn CTRL-T trên bàn phím. Đảm bảo hộp kiểm, my table has headers (bảng của tôi có tiêu đề) được tích, rồi nhấn Ok, để chuyển phạm vi thành bảng Excel.
Tiếp theo từ các điểm trên. Trong Access, thông tin được chia thành các thực thể hoặc chủ đề chính. Ví dụ, cơ sở dữ liệu Access thiết kế cho cửa hàng nhỏ nhiều khả năng chứa các bảng riêng gọi là Sản phẩm (Products), Đơn hàng (Orders), Khách hàng (Customers), và Nhân viên (Employees) (với mối quan hệ được chỉ định). Trong Excel, không hiếm thấy thông tin, sẽ được lưu trong các bảng riêng trong Access, được gộp trong một bảng Excel, như dưới đây.
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 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 lưu trong một bảng lớn thành bốn bảng nhỏ, và đặt mỗi bảng trên trang tính riêng như dưới đây. Cũng khuyến khích khớp tên trường trong bảng Access, với tiêu đề cột trong bảng Excel, để tránh nhầm lẫn và duy trì nhất quán.
2) Loại bỏ trùng lặp trong Excel, để dễ dàng thiết kế bảng sau hoặc tích hợp dữ liệu nguồn vào Access
Thường khó thấy giá trị duy nhất, chỉ bằng mắt, trong bảng Excel chứa nhiều hàng dữ liệu. Giả sử người ta muốn thấy số sản phẩm duy nhất cho bảng Excel, chứa 100 hàng dữ liệu, để thiết kế bảng hiệu quả trong Access dùng thông tin từ sổ làm việc Excel. Sổ làm việc Excel trong ví dụ này dựa trên dữ liệu từ một nhà bán lẻ trực tuyến giả định. Bảng Excel chứa ngày bán sản phẩm, tên sản phẩm, và số lượng bán như dưới đây.
Trước hết, tạo bản sao trang tính chứa bảng trong sổ làm việc, để giữ dữ liệu gốc nguyên vẹn và làm việc trên bản sao. Với ô trong bảng Excel được chọn, để kích hoạt tùy chọn Table Tools (Công cụ bảng) nhạy cảm ngữ cảnh, chọn tab Design (Thiết kế), và trong nhóm Tools (Công cụ), chọn tùy chọn Remove Duplicates (Loại bỏ trùng lặp) như dưới đây.
Hộp thoại Remove Duplicates nên xuất hiện và bỏ chọn Date_Of_Sale (Ngày bán) và Quantity (Số lượng) và nhấp Ok. Hộp thông báo nên bật lên nói bao nhiêu giá trị trùng lặp bị xóa. Nhấp Ok.
Bảng kết quả trong Excel có thể dùng để thu thập thông tin cho bảng Access cho cơ sở dữ liệu Access đề xuất của nhà bán lẻ trực tuyến.
Đọc thêm: Cách sao chép giá trị duy nhất sang trang tính khác trong Excel (5 phương pháp)
3) Dùng hàm TRIM() để xóa khoảng trống không cần thiết và dùng gạch dưới để duy trì nhất quán giữa tên trường Access và tiêu đề cột Excel
Tên trường Access không thể bắt đầu bằng khoảng trống đầu (leading spaces), vì vậy người ta có thể dùng hàm TRIM() trên tên tiêu đề cột của bảng trong Excel, để xóa khoảng trống.
Cũng, giả sử cuối cùng người ta cần nhập dữ liệu vào SQL Server hoặc cơ sở dữ liệu khác như Oracle. Vì vậy, luồng giải pháp tích hợp sẽ là nhập liệu trong Excel, rồi xuất dữ liệu này sang Access, rồi nhập 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 tốt khoảng trống giữa tên trường. Vì vậy, thay vì gọi tên tiêu đề cột trong bảng Excel là Name of Product (Tên sản phẩm), hãy gọi là Name_of_Product, dùng gạch dưới để dễ tích hợp vào cơ sở dữ liệu khác sau này. SQL cũng xử lý phong cách đặt tên bảng này tốt hơn, so với tên có khoảng trống.
Người ta có thể dùng thuộc tính caption (chú thích) trong Access, nên nếu tên trường trong bảng là Name_of_Product, nhập caption hoặc nhãn là Name of Product trong design view. Vì vậy, trong datasheet view hoặc trên form, Name of Product được dùng, trong khi tên trường bên dưới có gạch dưới. Điều này chủ yếu cho mục đích thẩm mỹ cho người xem front-end của cơ sở dữ liệu trong Access.
Đọc tương tự
- Run Time Error 1004: PasteSpecial Method of Range Class Failed
- 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 Ô đã gộp (2 phương pháp)
- Cách Sao chép chỉ ô hiển thị không có tiêu đề dùng VBA
4) Nhập dữ liệu vào Access từ Excel – xử lý hai kịch bản chính
- Trong kịch bản một, người ta có cơ sở dữ liệu Access hoàn toàn trống, và muốn nhập dữ liệu từ Excel (bảng trong Excel đã thiết lập với ID và dự phòng/trùng lặp giảm tối đa) và dùng Excel làm chương trình nhập liệu hoặc form. Với cơ sở dữ liệu Access mở, vào tab External Data (Dữ liệu bên ngoài), nhóm Import & Link (Nhập & Liên kết), chọn Excel như hình.
Phép thuật Get External Data – Excel Spreadsheet (Lấy dữ liệu bên ngoài – Bảng tính Excel) nên bật lên. Điều hướng đến Sổ làm việc Excel chứa dữ liệu. Chọn tùy chọn Link to the data source by creating a linked table (Liên kết đến nguồn dữ liệu bằng cách tạo bảng liên kết). Điều này cho phép nhập liệu trong Excel vì chúng ta muốn người dùng nhập dữ liệu trong Excel. Mọi thay đổi dữ liệu trong Excel sẽ phản ánh trong Access, nhưng không có gì được chỉnh sửa trong Access. Nó gần như chỉ đọc (ready-only) trong Access, về mặt nhập liệu trong datasheet view. Nhấp Ok.
Nếu bảng thiết lập đúng trong Excel, Access sẽ tự động phát hiện và kiểm tra First Row Contains Column Headings (Hàng đầu chứa tiêu đề cột) như dưới đây.
Nhấp Next dẫn đến bước cuối trong quy trình, yêu cầu nhập tên bảng liên kết (linked table name) rồi nhấp Finish.
Thông báo nên bật lên nói bảng đã được liên kết.
Điều này mở ra dữ liệu Excel cho chức năng SQL bổ sung, và bộ xây dựng biểu thức (expression builders) cho tạo truy vấn phức tạp, có sẵn trong môi trường Access. Kết quả truy vấn phức tạp sau có thể dùng trong bảng điều khiển Excel. Khuyến khích, nhưng không bắt buộc, giữ bảng nhập liệu Excel và cơ sở dữ liệu Access với bảng liên kết trong cùng thư mục hoặc mục chỉ để mục đích tổ chức.
- Trong kịch bản hai, người ta có cơ sở dữ liệu Access trống hoặc đã có dữ liệu và muốn nhập bảng Excel, nhưng không liên kết chúng. Người ta muốn chỉnh sửa chúng trong Access. Theo cách đó, bất kỳ thay đổi nào sau này trong trang tính Excel không phản ánh trong Access và người ta có dữ liệu hoàn toàn trong Access để chỉnh sửa và truy vấn. Đây thường là tùy chọn chọn khi cần kiểm soát chặt chẽ kiểu dữ liệu nhập vào bảng.
Lại, với cơ sở dữ liệu Access mở, vào tab External Data, nhóm Import & Link, chọn Excel như hình.
Trong trường hợp này, khi phép thuật bật lên, chọn Import the source data into a new table in the current database (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 bảng trong Access giờ không liên kết với bảng Excel gốc. Nhấp Ok.
Nhấp Next trên màn hình tiếp theo.
Người ta tới được chỉ định thông tin về trường đang nhập, như dưới đây. Phép thuật cũng cho phép chọn trường nào bỏ qua. Vì thường bảng Excel có thể chứa dữ liệu thực sự cần ở bảng khác trong Access, người ta có thể tránh nhập các cột này vào bảng hiện tại.
Người ta có thể để Access thêm khóa chính, hoặc chọn khóa chính riêng. Trong trường hợp này, vì bảng đã có cột ID chỉ định trong Excel, tùy chọn thứ hai được chọn như hình.
Nhấp Next.
Sau đó chọn tên cho bảng trong Access và nhấp Finish.
Bạn sẽ được hỏi có muốn lưu các bước nhập không, hiện tại chỉ nhấp close. Bảng giờ trong Access để chỉnh sửa, nhập, thao tác thêm và không liên kết với sổ làm việc Excel.
5) Dùng Table Analyzer (Phân tích bảng) để chuẩn hóa dữ liệu nhập và tách dữ liệu thành các bảng riêng trong Access
Các phiên bản Access mới hơn, có công cụ Table Analyzer, giúp người dùng tách một bảng thành nhiều bảng. Điều này đặc biệt hữu ích nếu người ta nhập dữ liệu từ một bảng Excel lớn và đang vật vã tách các bảng. Người ta có thể chạy phép thuật này như một phần của quy trình nhập và tích hộp, I would like a wizard to analyze my table after importing the data (Tôi muốn một phép thuật phân tích bảng của tôi sau khi nhập dữ liệu) như dưới đây.
Hoặc truy cập nó, nếu bảng đã nhập như bảng Access độc lập, bằng cách vào Database Tools (Công cụ cơ sở dữ liệu), nhóm Analyze (Phân tích), chọn Analyze Table (Phân tích bảng).
Phép thuật Table Analyzer nên được chạy, hướng dẫn bạn qua các bước tạo bảng riêng.
Table Analyzer, tuy nhiên, không luôn đoán đúng, vì vậy hãy dùng thận trọng.
6) Nhập dữ liệu từ Access vào Excel và dùng VLOOKUP để liên kết dữ liệu và điền cột
Người ta có thể nhập bảng Access vào trang tính Excel. Với sổ làm việc Excel mở, vào tab Data (Dữ liệu), nhóm Get External Data (Lấy dữ liệu bên ngoài), chọn From Access (Từ Access) như dưới đây.
Điều hướng đến cơ sở dữ liệu Access chứa bảng bạn muốn nhập vào bảng tính Excel.
Chọn bảng muốn nhập, trong trường hợp này, ProductT.
Nhấp Ok.
Chọn Import the data as a Table (Nhập dữ liệu như một Bảng), trên trang tính hiện tại, chọn ô A1, rồi Nhấp Ok.
Bảng được nhập như một bảng Excel.
Cùng việc làm cho ba bảng khác trong cơ sở dữ liệu, tức là bảng Orders (Đơn hàng), Employees (Nhân viên), và Customers (Khách hàng).
Giờ người ta có bảng Orders trông như dưới đây.
Bảng này với số trong Customer_ID, Product_ID, và Employee_ID có thể ngược trực giác và gây nhầm lẫn cho người dùng Excel không quen lý thuyết cơ sở dữ liệu quan hệ và bảng. Do đó, người ta có thể thêm ba cột trợ giúp vào bảng gọi là Actual_Customer (Khách hàng thực tế), Actual_Product (Sản phẩm thực tế), và Actual_Employee (Nhân viên thực tế) như dưới đây.
Trong Ô F2, công thức sau được dùng:
=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)
Ở đây, [@[Customer_ID]] tham chiếu ô C2, chứa giá trị tra cứu, Table_TablesToBeImportedIntoExcel.accdb5 tham chiếu bảng Customers đã nhập trên trang Customers, (Excel đặt tên mặc định cho bảng này), đánh dấu dữ liệu, nhưng không phải tiêu đề cột, giống như VLOOKUP bình thường, tên khách hàng ở cột 2, và loại tra cứu là khớp chính xác.
Nhấp đúp và gửi công thức xuống để điền cột Actual_Customer.
Làm tương tự để điền cột Actual_Product và Actual_Employee. Vì vậy, người ta có thể nhanh chóng điền lại bảng Excel với giá trị văn bản thực tế khi cần, dùng chức năng VLOOKUP để định hướng người dùng Excel.
7) Dùng dữ liệu đã thiết lập trong cơ sở dữ liệu quan hệ Access, để thao tác Power Pivot
Power Pivot là công cụ Business Intelligence có sẵn với Excel 2013 và các phiên bản sau. Nó cũng yêu cầu dữ liệu nhập theo cách quan hệ. Vì vậy, những gì bạn có thể làm khi nhập bảng Access vào Excel (đã chuẩn hóa và thiết kế dùng logic dữ liệu quan hệ), là thêm các bảng nhập vào mô hình dữ liệu (data model).
Trong sổ làm việc trống, vào Data, get External data (lấy dữ liệu bên ngoài) và chọn from Access như trường hợp trên. Tuy nhiên, lần này khi được yêu cầu chọn bảng, tích tùy chọn Enable selection of multiple tables (Bật chọn nhiều bảng) trong hộp thoại Select Table (Chọn bảng) và chọn tất cả bảng như dưới đây:
Nhấp Ok.
Đảm bảo tùy chọn PivotTable Report (Báo cáo PivotTable) được chọn, tùy chọn Add this data to the Data Model (Thêm dữ liệu này vào Mô hình dữ liệu) nên được tích vì chúng ta nhập nhiều bảng. Nếu chỉ nhập một bảng mỗi lần, bạn sẽ cần tự tích tùy chọn này. Nếu bạn nhấp vào mũi tên thả xuống bên cạnh Properties (Thuộc tính), bạn nên thấy mối quan hệ giữa các bảng cũng sẽ được nhập.
Nhấp Ok để tạo PivotTableReport, với các bảng đã thêm vào Mô hình dữ liệu. Mối quan hệ nên hiển thị trong Cửa sổ PowerPivot cho Excel, Chế độ Diagram (Diagram view). Vì vậy, dữ liệu sẵn sàng cho thao tác thêm dùng chức năng Power Pivot.
Tải tệp
Exchange-Data-Between-Access-and-Excel
Kết luận
Tích hợp Excel và Access cho phép tận dụng kết hợp tính năng trong Excel và Access. Chìa khóa tích hợp thành công Excel và Access là hiểu một chút về cơ bản thiết kế cơ sở dữ liệu quan hệ.
Hiểu một chút về thiết kế quan hệ và cấu trúc bảng cũng sẽ giúp bạn tốt hơn về việc tận dụng Power Pivot và công cụ Business Intelligence liên quan trong môi trường Excel.
Vui lòng bình luận và cho chúng tôi biết nếu bạn tích hợp Excel và Access, có dùng Power Pivot không, và bất kỳ mẹo nào khác để tích hợp Excel và Access, mà bạn có.
Các bài viết liên quan
- Excel VBA: Sao chép giá trị ô và Dán sang ô khác
- Excel VBA: Sao chép phạm vi sang Sổ làm việc khác
- [Đã khắc phục]: Nhấp chuột phải Sao chép và Dán Không hoạt động trong Excel (11 giải pháp)
- Khác biệt giữa Dán và Dán đặc biệt trong Excel
- Cách Sao chép hàng xen kẽ trong Excel (4 cách)
- Cách Áp dụng VBA PasteSpecial và Giữ định dạng nguồn trong Excel