Di chuyển dữ liệu từ Excel sang Access

Bài viết này cho bạn biết cách di chuyển dữ liệu của bạn từ Excel để truy nhập và chuyển đổi dữ liệu của bạn sang bảng quan hệ để bạn có thể sử dụng Microsoft Excel và truy nhập cùng nhau. Để tóm tắt, Access là tốt nhất cho việc chụp, lưu trữ, bình thường và chia sẻ dữ liệu và Excel là tốt nhất cho 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ệu10 lý do của bạn để sử dụng Access với Excel, thảo luận về chương trình nào phù hợp nhất với một nhiệm vụ cụ thể và cách dùng Excel và Access để tạo ra một giải pháp thực tế.

Khi bạn di chuyển dữ liệu từ Excel sang Access, có ba bước cơ bản để xử lý.

ba bước cơ bản

Lưu ý: Để biết thông tin về lập mô hình dữ liệu và quan hệ trong Access, hãy xem cơ bản về thiết kế cơ sở dữliệu.

Bước 1: nhập dữ liệu từ Excel đến Access

Nhập dữ liệu là một thao tác có thể trở nên suôn sẻ hơn nếu bạn mất một thời gian để chuẩn bị và làm sạch dữ liệu của bạn. Việc nhập dữ liệu cũng giống như chuyển đến một trang chủ mới. Nếu bạn dọn sạch và sắp xếp tài sản của mình trước khi di chuyển, hãy định cư vào ngôi nhà mới của bạn dễ dàng hơn.

Dọn sạch dữ liệu của bạn trước khi nhập

Trước khi bạn nhập dữ liệu vào Access, trong Excel, đó là một ý kiến hay:

  • Chuyển đổi các ô có chứa dữ liệu không phải là nguyên tử (có nghĩa là nhiều giá trị trong một ô) cho 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 tách ra thành các cột riêng biệt mà mỗi 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 dòng, dấu và nhiều không được nhúng.

  • Loại bỏ các ký tự không in.

  • Tìm và sửa lỗi chính tả và chấm câu.

  • Loại bỏ các hàng trùng lặp hoặc trường trùng lặp.

  • Đảm bảo các cột dữ liệu không chứa các định dạng hỗn hợp, đặc biệt là các số được định dạng là 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 Excel sau đây:

Lưu ý: Nếu nhu cầu dọn dẹp dữ liệu của bạn là 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 của mình, bạn có thể cân nhắc sử dụng một 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 của bạn.

Chọn kiểu dữ liệu tốt nhất khi bạn nhập

Trong suốt thao tác nhập trong Access, bạn muốn thực hiện các 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 thức định dạng số Excel và các kiểu dữ liệu Access được chuyển đổi khi bạn nhập dữ liệu từ Excel đến Access và cung cấp một số Mẹo trên 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ớ

Loại dữ liệu văn bản Access lưu trữ dữ liệu được đánh số lên đến 255 ký tự. Các cửa hàng dữ liệu bản ghi nhớ Access đánh số dữ liệu lên đến 65.535 ký tự.

Chọn bản ghi nhớ để tránh cắt xén mọi dữ liệu.

Số, tỷ lệ phần trăm, phân số, khoa học

Number

Access có một kiểu dữ liệu số khác nhau dựa trên một thuộc tính kích cỡ trường (byte, số nguyên, số nguyên dài, đơn, kép, thập phân).

Chọn đúp để tránh bất kỳ lỗi chuyển đổi dữ liệu nào.

Ngày

Ngày

Truy nhập và Excel đều sử dụng số ngày nối tiếp giống nhau để lưu trữ ngày. Trong Access, phạm vi ngày tháng lớn hơn: từ-657.434 (ngày 1 tháng 1 100 AD) đến 2.958.465 (ngày 31 tháng 12, 9999 AD).

Vì Access không nhận ra hệ thống ngày 1904 (được sử dụng trong Excel cho Macintosh), bạn cần chuyển đổi ngày tháng trong Excel hoặc Access để tránh nhầm lẫn.

Để biết thêm thông tin, hãy xem mục thay đổi hệ thống ngày, định dạng hoặc giải thích năm chữ sốnhập hoặc liên kết đến dữ liệu trong sổlàm việc Excel.

Chọn ngày tháng.

Thời gian

Thời gian

Truy nhập và các giá trị thời gian trong Access trong Excel bằng cách sử dụng cùng 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, loại tiền tệ dữ liệu lưu trữ dữ liệu như 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 không cho các giá trị.

Chọn tiền tệ, thường là mặc định.

Boolean

Có/Không

Access uses-1 cho tất cả các giá trị có và 0 cho tất cả các giá trị không, 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, vốn sẽ tự động chuyển đổi các giá trị tiềm ẩ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êukết nối, nếu không thì Access có thể sử dụng kiểu dữ liệu văn bản theo mặc định.

Sau khi dữ liệu được 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 tiên trước khi xóa bỏ.

Để biết thêm thông tin, hãy xem mục nhập hoặc liên kết đếnchủ đề trợ giúp truy nhập vào dữ liệu trong sổ làm việc Excel.

Tự động chắp thêm dữ liệu theo cách dễ dàng

Một vấn đề thường gặp của người dùng Excel có dữ liệu phụ thêm với cùng các 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à phòng ban. 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 có 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 để gắn 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à chắp thêm dữ liệu vào một bảng bằng cách sử dụng trình hướng dẫn nhập bảng tính. Ngoài ra, bạn có thể chắp 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 theo lịch biểu nhiệm vụ Microsoft Outlook, 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

Trong nháy mắt đầu tiên, hãy bước qua quy trình chuẩn hóa dữ liệu của bạn có thể có vẻ một nhiệm vụ khó khăn. May mắn thay, các bảng chuẩn hóa trong Access là một quá trình dễ dàng hơn, nhờ trình hướng dẫn phân tích bảng.

trình hướng dẫn phân tích bảng

1. kéo các cột được chọn vào một bảng mới và tự động tạo mối quan hệ

2. dùng lệnh nút để đổi tên bảng, thêm khóa chính, tạo cột hiện có một 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 quan hệ khóa chính và đối ngoại giữa các bảng.

  • Thêm khóa chính vào trường hiện có có chứa các giá trị duy nhất hoặc tạo một 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 khảo với các bản Cập Nhật xếp tầng. Xóa xếp tầng sẽ không tự động được thêm vào để tránh vô tình xóa dữ liệu nhưng bạn có thể dễ dàng thêm xếp tầng sau đó.

  • Tìm kiếm các bảng mới cho dữ liệu dư thừa hoặc trùng lặp (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 tùy chọn này như mong muốn.

  • Sao lưu bảng gốc và đổi tên nó bằng cách nối kết "_OLD" thành tên của nó. Sau đó, bạn tạo một truy vấn để cấu trúc lại bảng gốc, với tên bảng gốc để 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 mục 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 truy nhập dữ liệu 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 cấu trúc lại dữ liệu gốc, đó là một vấn đề đơn giản kết nối với dữ liệu Access từ Excel. Dữ liệu của bạn hiện đang ở 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, đây là một bộ chứa thông tin được dùng để xác định vị trí, đă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 một 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 (. DSN Extension). 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ừ nguồn dữ liệu bên ngoài (Power query).

Nhận 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 của bạn: phá vỡ các giá trị trong các cột của nhân viên bán hàng và địa chỉ thành các phần nhất của số nguyên tử, tách các đối tượng liên quan vào bảng của riêng họ, sao chép và dán các bảng đó từ Excel vào Truy nhập, tạo mối quan hệ chính giữa các bảng truy nhập mới được tạo và tạo và chạy truy vấn đơn giản trong Access để trả về thông tin.

Ví dụ về dữ liệu trong biểu mẫu 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 người bán hàng và cột địa chỉ. Cả hai cột đều phải được phân tách 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ề người 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 phân tách thêm, theo chủ đề, vào 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

Các hoạt động phiêu lưu

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

$5,25

Các hoạt động phiêu lưu

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

$4,50

Các hoạt động phiêu lưu

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

Các hoạt động phiêu lưu

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

$7,25

Các hoạt động phiêu lưu

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, sậy

2353

3/7/09

A-2275

6

$16,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, sậy

2353

3/7/09

C-789

5

$7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Thông tin trong phần nhỏ nhất: dữ liệu nguyên tử

Làm việc với dữ liệu trong ví dụ này, bạn có thể 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 ô (chẳng hạn như địa chỉ đường phố, thành phố, tiểu bang và mã bưu chính) vào các cột rời rạc.

Bảng sau đây Hiển thị các cột mới trong cùng một trang tính sau khi chúng được tách ra để 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 tên và các cột tên đầu tiên và thông tin trong cột địa chỉ đã được chia thành địa chỉ đường, thành phố, tiểu bang và cột mã ZIP. Dữ liệu này nằm trong "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

Ngang

Đại

2302 Harvard Ave

Bellevue

WA

98227

Adams

Ellen

1025 Columbia Circle

Kirkland

WA

98234

Hance

Jim

2302 Harvard Ave

Bellevue

WA

98227

Koch

Reed

7007 Cornell St Redmond

Redmond

WA

98199

Ngắt dữ liệu ra thành các chủ đề có tổ chức trong Excel

Một số bảng ví dụ về dữ liệu theo dõi cùng một thông tin từ trang tính Excel sau khi nó được phân tách thành các bảng dành cho người bán hàng, sản phẩm, khách hàng và đơn hàng. Thiết kế của bảng không phải là cuối cùng, nhưng trên đúng theo dõi.

Bảng Salesngười 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ó ID duy nhất (ID người 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 các đơn hàng đến nhân viên bán hàng.

Người bán hàng

ID nhân viên bán hàng

Họ

Tên

101

Ngang

Đại

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ó 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ào bảng chi tiết đơn hàng.

Sản

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ó 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 đến 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

Các hoạt động phiêu lưu

1025 Columbia Circle

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, người bán hàng, khách hàng và sản phẩm. Lưu ý rằng mỗi bản ghi có ID duy nhất (ID đơn hàng). Một số thông tin trong bảng này cần được phân tách thành một bảng bổ sung có chứa các 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 được hiển thị ở đây vẫn chưa được tách vào 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

Các chi tiết đơn hàng, chẳng hạn như ID sản phẩm và số lượng được chuyển ra khỏi bảng đơn hàng và được lưu trữ trong một bảng có tên là chi tiết đơn hàng. Hãy nhớ rằng có 9 đơn hàng, vì vậy điều này 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), mà sẽ được gọi 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ác cột cần có các giá trị duy nhất (nghĩa là, không có khóa chính), do đó, nó vẫn ổn cho bất kỳ hoặc tất cả các cột để chứa dữ liệu "dư thừa". Tuy nhiên, không có hai bản ghi nào trong bảng này sẽ hoàn toàn giống hệt 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 tương ứng với một sản phẩm theo một đơn hàng riêng lẻ. Ví dụ, trong 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.

Bảng chi tiết đơn hàng nên, do đó, 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

Bây giờ, thông tin về người bán hàng, khách hàng, sản phẩm, đơn hàng và chi tiết đơn hàng đã được chia ra thành các đối tượng riêng lẻ trong Excel, bạn có thể sao chép dữ liệu đó trực tiếp vào Access, nơi nó sẽ trở thành bảng.

Tạo mối quan hệ giữa các bảng Access và chạy một truy vấn

Sau khi bạn đã di chuyển dữ liệu của mình sang Access, bạn có thể tạo mối quan hệ giữa các bảng và sau đó tạo các truy vấn để trả về các đối tượng khác nhau. Ví dụ, bạn có thể tạo một truy vấn trả về ID thứ tự và tên của người bán hàng cho các đơn hàng được nhập vào giữa 3/05/09 và 3/08/09.

Ngoài ra, bạn có thể tạo biểu mẫu và báo cáo để làm cho mục 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ể nhờ chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm sự hỗ trợ trong Cộng đồng Giải pháp hoặc đề xuất tính năng hay cải tiến mới trên Excel User Voice.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×