Lưu ý: Microsoft Access không hỗ trợ nhập dữ liệu Excel có nhãn nhạy cảm được áp dụng. Giải pháp thay thế là bạn có thể loại bỏ nhãn trước khi nhập rồi áp dụng lại nhãn sau khi nhập. Để biết thêm thông tin, hãy xem mục Áp dụng nhãn nhạy cảm cho tệp và email của bạn trong Office.
Bài viết này hướng dẫn bạn cách di chuyển dữ liệu từ Excel sang Access và chuyển đổi dữ liệu sang các bảng có liên quan để bạn có thể sử dụng Microsoft Excel và Access cùng nhau. Để tóm tắt, Access phù hợp nhất để thu thập, lưu trữ, truy vấn và chia sẻ dữ liệu, đồng thời Excel là cách tốt nhất để tính toán, phân tích và trực quan hóa dữ liệu.
Hai bài viết, Sử dụng Access hoặc Excel để quản lý dữ liệu của bạn và 10 lý do hàng đầu để sử dụng Access với Excel, thảo luận về chương trình phù hợp nhất cho một tác vụ cụ thể và cách sử dụng Excel và Access cùng nhau để tạo ra một giải pháp thực tiễn.
Khi bạn di chuyển dữ liệu từ Excel sang Access, có ba bước cơ bản để xử lý.
Lưu ý: Để biết thông tin về việc lập mô hình dữ liệu và mối quan hệ trong Access, hãy xem Mục cơ bản về thiết kế cơ sở dữ liệu.
Bước 1: Nhập dữ liệu từ Excel vào Access
Nhập dữ liệu là một thao tác có thể diễn ra suôn sẻ hơn nếu bạn dành chút thời gian để chuẩn bị và dọn dẹp dữ liệu. Việc nhập dữ liệu giống như di chuyển sang một ngôi nhà mới. Nếu bạn dọn dẹp và sắp xếp đồ đạc trước khi di chuyển, thì việc giải lập vào nhà mới sẽ dễ dàng hơn nhiều.
Dọn dẹp dữ liệu của bạn trước khi nhập
Trước khi nhập dữ liệu vào Access, bạn nên:
-
Chuyển đổi các ô có chứa dữ liệu không phải là nguyên tử (nghĩa là nhiều giá trị trong một ô) thành nhiều cột. Ví dụ: một ô trong cột "Kỹ năng" có chứa nhiều giá trị kỹ năng, chẳng hạn như lập trình C#, "Lập trình VBA" và "Thiết kế web" nên được chia nhỏ để phân tách các cột chỉ chứa một giá trị kỹ năng.
-
Sử dụng lệnh TRIM để loại bỏ các khoảng trắng ở đầu, cuối và nhiều chỗ nhúng.
-
Loại bỏ ký tự không in ra.
-
Tìm và sửa lỗi chính tả và dấu câu.
-
Loại bỏ các hàng trùng lặp hoặc trường trùng lặp.
-
Hãy đảm bảo rằng các cột dữ liệu không chứa định dạng hỗn hợp, đặc biệt là các số được định dạng dưới dạng văn bản hoặc ngày tháng được định dạng dưới dạng số.
Để biết thêm thông tin, hãy xem các chủ đề trợ giúp sau của Excel:
-
Lọc các giá trị đơn nhất hoặc loại bỏ những giá trị trùng lặp
-
Chuyển đổi ngày tháng được lưu dưới dạng văn bản sang dạng ngày tháng
Lưu ý: Nếu nhu cầu dọn dẹp dữ liệu của bạn phức tạp hoặc bạn không có thời gian hoặc tài nguyên để tự động hóa quy trình, bạn có thể cân nhắc sử dụng nhà cung cấp bên thứ ba. Để biết thêm thông tin, hãy tìm kiếm "phần mềm làm sạch dữ liệu" hoặc "chất lượng dữ liệu" bằng công cụ tìm kiếm yêu thích của bạn trong trình duyệt Web.
Chọn kiểu dữ liệu tốt nhất khi bạn nhập
Trong quá trình nhập trong Access, bạn muốn đưa ra lựa chọn tốt để bạn nhận được ít (nếu có) lỗi chuyển đổi sẽ yêu cầu can thiệp thủ công. Bảng sau đây tóm tắt cách định dạng số Excel và kiểu dữ liệu Access được chuyển đổi khi bạn nhập dữ liệu từ Excel vào Access và cung cấp một số mẹo về các kiểu dữ liệu tốt nhất để chọn trong Trình hướng dẫn Nhập Bảng tính.
|
Định dạng số Excel |
Kiểu dữ liệu Access |
Chú thích |
Biện pháp tốt nhất |
|---|---|---|---|
|
Văn bản |
Văn bản, Bản ghi nhớ |
Kiểu dữ liệu Văn bản Access lưu trữ dữ liệu chữ và số tối đa 255 ký tự. Kiểu dữ liệu Bản ghi nhớ Access lưu trữ dữ liệu chữ và số tối đa 65.535 ký tự. |
Chọn Bản ghi nhớ để tránh cắt bớt bất kỳ dữ liệu nào. |
|
Number, Percentage, Fraction, Scientific |
Number |
Access có một kiểu dữ liệu Số khác nhau dựa trên thuộc tính Cỡ Trường (Byte, Số nguyên, Số nguyên Dài, Đơn, Kép, Thập phân). |
Chọn Đúp để tránh mọi lỗi chuyển đổi dữ liệu. |
|
Ngày |
Ngày |
Cả Access và Excel đều sử dụng cùng một số ngày tuần tự để lưu trữ ngày. Trong Access, phạm vi ngày sẽ lớn hơn: từ -657.434 (ngày 1 tháng 1 năm 100 SAU ĐÓ) đến 2.958.465 (ngày 31 tháng 12 năm 9999 SAU). Vì Access không nhận ra hệ thống ngày tháng năm 1904 (được dùng trong Excel cho Macintosh), bạn cần chuyển đổi ngày trong Excel hoặc Access để tránh nhầm lẫn. Để biết thêm thông tin, hãy xem Thay đổi hệ thống ngày, định dạng hoặc diễn giải năm có hai chữ số và Nhập hoặc nối kết đến dữ liệu trong sổ làm việc Excel. |
Chọn Ngày. |
|
Thời gian |
Thời gian |
Access và Excel đều lưu trữ các giá trị thời gian bằng cách sử dụng cùng một kiểu dữ liệu. |
Chọn Thời gian, thường là mặc định. |
|
Tiền tệ, Kế toán |
Tiền tệ |
Trong Access, kiểu dữ liệu Tiền tệ lưu trữ dữ liệu dưới dạng số 8 byte với độ chính xác đến bốn vị trí thập phân và được dùng để lưu trữ dữ liệu tài chính và ngăn làm tròn các giá trị. |
Chọn Tiền tệ, thường là mặc định. |
|
Boolean |
Có/Không |
Access sử dụng -1 cho tất cả các giá trị Yes và 0 cho tất cả các giá trị No, trong khi Excel sử dụng 1 cho tất cả các giá trị TRUE và 0 cho tất cả các giá trị FALSE. |
Chọn Có/Không, tùy chọn này sẽ tự động chuyển đổi các giá trị cơ bản. |
|
Siêu kết nối |
Siêu kết nối |
Siêu kết nối trong Excel và Access chứa URL hoặc địa chỉ Web mà bạn có thể bấm và theo dõi. |
Chọn Siêu kết nối, nếu không Access có thể sử dụng kiểu dữ liệu Văn bản theo mặc định. |
Sau khi dữ liệu nằm trong Access, bạn có thể xóa dữ liệu Excel. Đừng quên sao lưu sổ làm việc Excel gốc trước khi xóa.
Để biết thêm thông tin, hãy xem chủ đề trợ giúp Access Nhập hoặc liên kết đến dữ liệu trong sổ làm việc Excel.
Tự động gắn thêm dữ liệu theo cách dễ dàng
Một vấn đề phổ biến mà người dùng Excel gặp phải là chắp thêm dữ liệu có cùng cột vào một trang tính lớn. Ví dụ: bạn có thể có một giải pháp theo dõi tài nguyên bắt đầu trong Excel nhưng bây giờ đã phát triển để bao gồm các tệp từ nhiều nhóm làm việc và bộ phận. Dữ liệu này có thể nằm trong các trang tính và sổ làm việc khác nhau hoặc trong các tệp văn bản là nguồn cấp dữ liệu từ các hệ thống khác. Không có lệnh giao diện người dùng hoặc cách dễ dàng để chắp thêm dữ liệu tương tự trong Excel.
Giải pháp tốt nhất là sử dụng Access, nơi bạn có thể dễ dàng nhập và gắn thêm dữ liệu vào một bảng bằng trình hướng dẫn Nhập Bảng tính. Hơn nữa, bạn có thể gắn thêm nhiều dữ liệu vào một bảng. Bạn có thể lưu các thao tác nhập, thêm chúng dưới dạng các tác vụ Microsoft Outlook đã lên lịch và thậm chí sử dụng macro để tự động hóa quy trình.
Bước 2: Chuẩn hóa dữ liệu bằng cách sử dụng Trình hướng dẫn Phân tích Bảng
Nhìn lướt qua, bước qua quá trình chuẩn hóa dữ liệu của bạn có thể có vẻ là một nhiệm vụ khó khăn. May mắn thay, chuẩn hóa bảng trong Access là một quy trình dễ dàng hơn nhiều, nhờ Trình hướng dẫn Phân tích Bảng.
1. Kéo các cột đã chọn vào bảng mới và tự động tạo mối quan hệ
2. Sử dụng các lệnh nút để đổi tên bảng, thêm khóa chính, đặt cột hiện có làm khóa chính và hoàn tác hành động cuối cùng
Bạn có thể sử dụng trình hướng dẫn này để thực hiện các thao tác sau:
-
Chuyển đổi bảng thành một tập hợp các bảng nhỏ hơn và tự động tạo mối quan hệ khóa chính và khóa ngoại giữa các bảng.
-
Thêm khóa chính vào trường hiện có chứa các giá trị duy nhất hoặc tạo trường ID mới sử dụng kiểu dữ liệu Số Tự động.
-
Tự động tạo mối quan hệ để thực thi tính toàn vẹn tham chiếu với xếp tầng cập nhật. Xóa xếp tầng không được thêm tự động để ngăn không cho vô tình xóa dữ liệu, nhưng bạn có thể dễ dàng thêm xóa xếp tầng sau này.
-
Tìm kiếm dữ liệu dư thừa hoặc trùng lặp trong các bảng mới (chẳng hạn như cùng một khách hàng với hai số điện thoại khác nhau) và cập nhật dữ liệu này theo ý muốn.
-
Sao lưu bảng ban đầu và đổi tên bằng cách chắp thêm "_OLD" vào tên bảng. Sau đó, bạn tạo một truy vấn xây dựng lại bảng gốc, với tên bảng ban đầu để mọi biểu mẫu hoặc báo cáo hiện có dựa trên bảng gốc sẽ hoạt động với cấu trúc bảng mới.
Để biết thêm thông tin, hãy xem Chuẩn hóa dữ liệu của bạn bằng cách dùng Trình phân tích Bảng.
Bước 3: Kết nối với dữ liệu Access từ Excel
Sau khi dữ liệu đã được chuẩn hóa trong Access và một truy vấn hoặc bảng đã được tạo để tái cấu trúc dữ liệu ban đầu, vấn đề đơn giản là kết nối với dữ liệu Access từ Excel. Dữ liệu của bạn hiện nằm trong Access dưới dạng nguồn dữ liệu ngoài và do đó có thể được kết nối với sổ làm việc thông qua kết nối dữ liệu, là nơi chứa thông tin được sử dụng để định vị, đăng nhập và truy nhập nguồn dữ liệu ngoài. Thông tin kết nối được lưu trữ trong sổ làm việc và cũng có thể được lưu trữ trong tệp kết nối, chẳng hạn như tệp Kết nối Dữ liệu Office (ODC) (phần mở rộng tên tệp.odc) hoặc tệp Tên Nguồn Dữ liệu (phần mở rộng.dsn). Sau khi bạn kết nối với dữ liệu ngoài, bạn cũng có thể tự động làm mới (hoặc cập nhật) sổ làm việc Excel của bạn từ Access bất cứ khi nào dữ liệu được cập nhật trong Access.
Để biết thêm thông tin, hãy xem Nhập dữ liệu từ các nguồn dữ liệu ngoài (Power Query).
Đưa dữ liệu của bạn vào Access
Phần này sẽ hướng dẫn bạn qua các giai đoạn chuẩn hóa dữ liệu sau đây: Tách các giá trị trong cột Nhân viên bán hàng và Địa chỉ thành các phần nguyên tử nhất, tách các đối tượng liên quan thành các bảng riêng, sao chép và dán các bảng đó từ Excel vào Access, tạo mối quan hệ chính giữa các bảng Access mới được tạo, đồng thời tạo và chạy truy vấn đơn giản trong Access để trả về thông tin.
Dữ liệu ví dụ ở dạng không chuẩn hóa
Trang tính sau đây chứa các giá trị không phải là nguyên tử trong cột Nhân viên bán hàng và cột Địa chỉ. Cả hai cột nên được chia thành hai hoặc nhiều cột riêng biệt. Trang tính này cũng chứa thông tin về nhân viên bán hàng, sản phẩm, khách hàng và đơn hàng. Thông tin này cũng nên được chia xa hơn nữa, theo chủ đề, thành các bảng riêng biệt.
|
Nhân viên bán hàng |
ID Đơn hàng |
Ngày Đặt hàng |
ID Sản phẩm |
Qty |
Giá |
Tên Khách hàng |
Địa chỉ |
Điện thoại |
|---|---|---|---|---|---|---|---|---|
|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
$7,00 |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
$9,75 |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
$16,75 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
$5,25 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
$4,50 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
$9,75 |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
|
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
$16,75 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
$7,25 |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
$16,75 |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
$7,00 |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Thông tin trong các bộ phận nhỏ nhất của nó: dữ liệu nguyên tử
Làm việc với dữ liệu trong ví dụ này, bạn có thể sử dụng lệnh Văn bản thành Cột trong Excel để phân tách các phần "nguyên tử" của một ô (chẳng hạn như địa chỉ đường, thành phố, tiểu bang và mã bưu điện) thành các cột riêng biệt.
Bảng sau đây hiển thị các cột mới trong cùng một bảng tính sau khi chúng đã được tách để làm cho tất cả các giá trị nguyên tử. Lưu ý rằng thông tin trong cột Người bán hàng đã được chia thành các cột Họ và Tên và thông tin trong cột Địa chỉ đã được chia thành các cột Địa chỉ Đường, Thành phố, Tiểu bang và Mã ZIP. Dữ liệu này ở dạng "biểu mẫu bình thường đầu tiên".
|
Họ |
Tên |
|
Địa chỉ Đường |
Thành phố |
Tiểu bang |
Mã bưu điện |
|---|---|---|---|---|---|---|
|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
|
Adams |
Ellen |
Vòng tròn Columbia 1025 |
Kirkland |
WA |
98234 |
|
|
Hance |
Jim |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
|
Koch |
Reed |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Chia nhỏ dữ liệu thành các chủ đề được sắp xếp trong Excel
Một vài bảng dữ liệu ví dụ theo sau hiển thị cùng một thông tin từ trang tính Excel sau khi bảng đã được tách thành các bảng dành cho nhân viên bán hàng, sản phẩm, khách hàng và đơn hàng. Thiết kế bảng không phải là bản cuối cùng, nhưng nó đi đúng hướng.
Bảng Nhân viên bán hàng chỉ chứa thông tin về nhân viên bán hàng. Lưu ý rằng mỗi bản ghi có một ID duy nhất (ID Nhân viên Bán hàng). Giá trị ID Nhân viên bán hàng sẽ được sử dụng trong bảng Đơn hàng để kết nối đơn hàng với nhân viên bán hàng.
|
Nhân viên bán hàng |
||
|---|---|---|
|
ID Nhân viên bán hàng |
Họ |
Tên |
|
101 |
Li |
Yale |
|
103 |
Adams |
Ellen |
|
105 |
Hance |
Jim |
|
107 |
Koch |
Reed |
Bảng Sản phẩm chỉ chứa thông tin về sản phẩm. Lưu ý rằng mỗi bản ghi có một ID duy nhất (ID Sản phẩm). Giá trị ID Sản phẩm sẽ được sử dụng để kết nối thông tin sản phẩm với bảng Chi tiết Đơn hàng.
|
Các sản phẩm |
|
|---|---|
|
ID Sản phẩm |
Giá |
|
A-2275 |
16.75 |
|
B-205 |
4.50 |
|
C-789 |
7.00 |
|
C-795 |
9.75 |
|
D-4420 |
7.25 |
|
F-198 |
5.25 |
Bảng Khách hàng chỉ chứa thông tin về khách hàng. Lưu ý rằng mỗi bản ghi có một ID duy nhất (ID Khách hàng). Giá trị ID Khách hàng sẽ được sử dụng để kết nối thông tin khách hàng với bảng Đơn hàng.
|
Khách hàng |
||||||
|---|---|---|---|---|---|---|
|
Customer ID |
Tên |
Địa chỉ Đường |
Thành phố |
Tiểu bang |
Mã bưu điện |
Điện thoại |
|
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
|
1003 |
Adventure Works |
Vòng tròn Columbia 1025 |
Kirkland |
WA |
98234 |
425-555-0185 |
|
1005 |
Fourth Coffee |
7007 Cornell St |
Redmond |
WA |
98199 |
425-555-0201 |
Bảng Đơn hàng chứa thông tin về đơn hàng, nhân viên bán hàng, khách hàng và sản phẩm. Lưu ý rằng mỗi bản ghi có một ID duy nhất (ID Đơn hàng). Một số thông tin trong bảng này cần được chia thành một bảng bổ sung chứa chi tiết đơn hàng để bảng Đơn hàng chỉ chứa bốn cột — ID đơn hàng duy nhất, ngày đặt hàng, ID nhân viên bán hàng và ID khách hàng. Bảng hiển thị ở đây vẫn chưa được chia thành bảng Chi tiết Đơn hàng.
|
Đơn hàng |
|||||
|---|---|---|---|---|---|
|
ID Đơn hàng |
Ngày Đặt hàng |
ID Nhân viên Bán hàng |
ID Khách hàng |
ID Sản phẩm |
Qty |
|
2349 |
3/4/09 |
101 |
1005 |
C-789 |
3 |
|
2349 |
3/4/09 |
101 |
1005 |
C-795 |
6 |
|
2350 |
3/4/09 |
103 |
1003 |
A-2275 |
2 |
|
2350 |
3/4/09 |
103 |
1003 |
F-198 |
6 |
|
2350 |
3/4/09 |
103 |
1003 |
B-205 |
1 |
|
2351 |
3/4/09 |
105 |
1001 |
C-795 |
6 |
|
2352 |
3/5/09 |
105 |
1003 |
A-2275 |
2 |
|
2352 |
3/5/09 |
105 |
1003 |
D-4420 |
3 |
|
2353 |
3/7/09 |
107 |
1005 |
A-2275 |
6 |
|
2353 |
3/7/09 |
107 |
1005 |
C-789 |
5 |
Chi tiết đơn hàng, chẳng hạn như ID sản phẩm và số lượng được di chuyển ra khỏi bảng Đơn hàng và được lưu trữ trong bảng có tên Chi tiết Đơn hàng. Hãy nhớ rằng có 9 đơn hàng, vì vậy điều đó có ý nghĩa là có 9 bản ghi trong bảng này. Lưu ý rằng bảng Đơn hàng có ID duy nhất (ID Đơn hàng), ID này sẽ được tham chiếu từ bảng Chi tiết Đơn hàng.
Thiết kế cuối cùng của bảng Đơn hàng sẽ trông giống như sau:
|
Đơn hàng |
|||
|---|---|---|---|
|
ID Đơn hàng |
Ngày Đặt hàng |
ID Nhân viên Bán hàng |
ID Khách hàng |
|
2349 |
3/4/09 |
101 |
1005 |
|
2350 |
3/4/09 |
103 |
1003 |
|
2351 |
3/4/09 |
105 |
1001 |
|
2352 |
3/5/09 |
105 |
1003 |
|
2353 |
3/7/09 |
107 |
1005 |
Bảng Chi tiết Đơn hàng không chứa cột nào yêu cầu giá trị duy nhất (nghĩa là không có khóa chính), do đó, mọi cột đều có thể chứa dữ liệu "dư thừa". Tuy nhiên, không nên có hai bản ghi trong bảng này hoàn toàn giống nhau (quy tắc này áp dụng cho bất kỳ bảng nào trong cơ sở dữ liệu). Trong bảng này, nên có 17 bản ghi — mỗi bản ghi tương ứng với một sản phẩm theo một đơn hàng riêng lẻ. Ví dụ, theo thứ tự 2349, ba sản phẩm C-789 bao gồm một trong hai phần của toàn bộ đơn hàng.
Do đó, bảng Chi tiết Đơn hàng sẽ trông giống như sau:
|
Chi tiết Đơn hàng |
||
|---|---|---|
|
ID Đơn hàng |
ID Sản phẩm |
Qty |
|
2349 |
C-789 |
3 |
|
2349 |
C-795 |
6 |
|
2350 |
A-2275 |
2 |
|
2350 |
F-198 |
6 |
|
2350 |
B-205 |
1 |
|
2351 |
C-795 |
6 |
|
2352 |
A-2275 |
2 |
|
2352 |
D-4420 |
3 |
|
2353 |
A-2275 |
6 |
|
2353 |
C-789 |
5 |
Sao chép và dán dữ liệu từ Excel vào Access
Giờ đây, thông tin về nhân viên bán hàng, khách hàng, sản phẩm, đơn hàng và chi tiết đơn hàng đã được chia thành các đối tượng riêng biệt trong Excel, bạn có thể sao chép dữ liệu đó trực tiếp vào Access, nơi dữ liệu sẽ trở thành bảng.
Tạo mối quan hệ giữa các bảng Access và chạy truy vấn
Sau khi đã di chuyển dữ liệu sang Access, bạn có thể tạo mối quan hệ giữa các bảng, rồi tạo truy vấn để trả về thông tin về các chủ đề khác nhau. Ví dụ: bạn có thể tạo một truy vấn trả về ID Đơn hàng và tên của nhân viên bán hàng cho các đơn hàng đã nhập từ 05/03/09 đến 08/03/2009.
Ngoài ra, bạn có thể tạo biểu mẫu và báo cáo để giúp nhập dữ liệu và phân tích doanh số dễ dàng hơn.
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.
