Nhập dữ liệu vào Excel

Bạn có thể phải sử dụng dữ liệu từ nhiều nguồn khác nhau để phân tích. Trong Excel, bạn có thể nhập dữ liệu từ các nguồn dữ liệu khác nhau. Một số nguồn dữ liệu như sau:

Cơ sở dữ liệu Microsoft Access
Trang web
Tệp văn bản
Bảng SQL Server
Khối phân tích SQL Server
Tệp XML

Bạn có thể nhập đồng thời bất kỳ số lượng bảng nào từ cơ sở dữ liệu.

Nhập dữ liệu từ cơ sở dữ liệu Microsoft Access

Chúng ta sẽ tìm hiểu cách nhập dữ liệu từ cơ sở dữ liệu MS Access. Làm theo các bước dưới đây -

Step 1 - Mở một sổ làm việc trống mới trong Excel.

Step 2 - Nhấp vào tab DATA trên Ribbon.

Step 3 - Bấm From Accesstrong nhóm Lấy Dữ liệu Bên ngoài. CácSelect Data Source hộp thoại xuất hiện.

Step 4- Chọn tệp cơ sở dữ liệu Access mà bạn muốn nhập. Các tệp cơ sở dữ liệu Access sẽ có phần mở rộng .accdb.

Hộp thoại Chọn Bảng xuất hiện hiển thị các bảng được tìm thấy trong cơ sở dữ liệu Access. Bạn có thể nhập tất cả các bảng trong cơ sở dữ liệu cùng một lúc hoặc chỉ nhập các bảng đã chọn dựa trên nhu cầu phân tích dữ liệu của bạn.

Step 5 - Chọn hộp Cho phép lựa chọn nhiều bảng và chọn tất cả các bảng.

Step 6- Bấm OK. CácImport Data hộp thoại xuất hiện.

Khi bạn quan sát, bạn có các tùy chọn sau để xem dữ liệu bạn đang nhập vào sổ làm việc của mình -

Table
Báo cáo PivotTable
PivotChart
Báo cáo Power View

Bạn cũng có một tùy chọn - only create connection. Hơn nữa, Báo cáo PivotTable được chọn theo mặc định.

Excel cũng cung cấp cho bạn các tùy chọn để đưa dữ liệu vào sổ làm việc của bạn -

Trang tính hiện có
Bảng tính mới

Bạn sẽ tìm thấy một hộp kiểm khác đã được chọn và vô hiệu hóa - Add this data to the Data Model. Bất cứ khi nào bạn nhập bảng dữ liệu vào sổ làm việc của mình, chúng sẽ tự động được thêm vào Mô hình Dữ liệu trong sổ làm việc của bạn. Bạn sẽ tìm hiểu thêm về Mô hình Dữ liệu trong các chương sau.

Bạn có thể thử từng tùy chọn để xem dữ liệu bạn đang nhập và kiểm tra cách dữ liệu xuất hiện trong sổ làm việc của bạn -

Nếu bạn chọn Table, Tùy chọn trang tính hiện tại bị tắt, New worksheettùy chọn được chọn và Excel sẽ tạo nhiều trang tính bằng số bảng bạn đang nhập từ cơ sở dữ liệu. Các bảng Excel xuất hiện trong các trang tính này.
Nếu bạn chọn PivotTable Report, Excel nhập các bảng vào sổ làm việc và tạo PivotTable trống để phân tích dữ liệu trong các bảng đã nhập. Bạn có một tùy chọn để tạo PivotTable trong một trang tính hiện có hoặc một trang tính mới.

Bảng Excel cho các bảng dữ liệu đã nhập sẽ không xuất hiện trong sổ làm việc. Tuy nhiên, bạn sẽ tìm thấy tất cả các bảng dữ liệu trong danh sách trường PivotTable, cùng với các trường trong mỗi bảng.
Nếu bạn chọn PivotChart, Excel nhập các bảng vào sổ làm việc và tạo một PivotChart trống để hiển thị dữ liệu trong các bảng đã nhập. Bạn có một tùy chọn để tạo PivotChart trong một trang tính hiện có hoặc một trang tính mới.

Bảng Excel cho các bảng dữ liệu đã nhập sẽ không xuất hiện trong sổ làm việc. Tuy nhiên, bạn sẽ tìm thấy tất cả các bảng dữ liệu trong danh sách trường PivotChart, cùng với các trường trong mỗi bảng.
Nếu bạn chọn Power View Report, Excel nhập các bảng vào sổ làm việc và tạo Báo cáo Power View trong một trang tính mới. Bạn sẽ học cách sử dụng Báo cáo Power View để phân tích dữ liệu trong các chương sau.

Bảng Excel cho các bảng dữ liệu đã nhập sẽ không xuất hiện trong sổ làm việc. Tuy nhiên, bạn sẽ tìm thấy tất cả các bảng dữ liệu trong danh sách trường Báo cáo Power View, cùng với các trường trong mỗi bảng.
Nếu bạn chọn tùy chọn - Only Create Connection, một kết nối dữ liệu sẽ được thiết lập giữa cơ sở dữ liệu và sổ làm việc của bạn. Không có bảng hoặc báo cáo nào xuất hiện trong sổ làm việc. Tuy nhiên, các bảng đã nhập được thêm vào Mô hình Dữ liệu trong sổ làm việc của bạn theo mặc định.

Bạn cần chọn bất kỳ tùy chọn nào trong số này, dựa trên ý định nhập dữ liệu để phân tích dữ liệu. Như bạn đã quan sát ở trên, bất kể tùy chọn bạn đã chọn là gì, dữ liệu được nhập và thêm vào Mô hình Dữ liệu trong sổ làm việc của bạn.

Nhập dữ liệu từ một trang web

Đôi khi, bạn có thể phải sử dụng dữ liệu được làm mới trên một trang web. Bạn có thể nhập dữ liệu từ bảng trên trang web vào Excel.

Step 1 - Mở một sổ làm việc trống mới trong Excel.

Step 2 - Nhấp vào tab DATA trên Ribbon.

Step 3 - Bấm From Web bên trong Get External Datanhóm. CácNew Web Query hộp thoại xuất hiện.

Step 4 - Nhập URL của trang web từ nơi bạn muốn nhập dữ liệu, vào hộp bên cạnh Địa chỉ và nhấp vào Đi.

Step 5- Dữ liệu trên trang web hiện ra. Sẽ có các biểu tượng mũi tên màu vàng bên cạnh dữ liệu bảng có thể được nhập.

Step 6- Nhấp vào các biểu tượng màu vàng để chọn dữ liệu bạn muốn nhập. Thao tác này sẽ chuyển các biểu tượng màu vàng thành hộp màu xanh lục có dấu kiểm như trong ảnh chụp màn hình sau đây.

Step 7 - Nhấp vào nút Nhập sau khi bạn đã chọn những gì bạn muốn.

Các Import Data hộp thoại xuất hiện.

Step 8 - Chỉ định nơi bạn muốn đặt dữ liệu và nhấp vào Ok.

Step 9 - Sắp xếp dữ liệu để phân tích và / hoặc trình bày thêm.

Sao chép-dán dữ liệu từ web

Một cách khác để lấy dữ liệu từ trang web là sao chép và dán dữ liệu cần thiết.

Step 1 - Chèn một trang tính mới.

Step 2 - Sao chép dữ liệu từ trang web và dán vào trang tính.

Step 3 - Tạo một bảng với dữ liệu đã dán.

Nhập dữ liệu từ tệp văn bản

Nếu bạn có dữ liệu trong .txt hoặc là .csv hoặc là .prn, bạn có thể nhập dữ liệu từ các tệp đó coi chúng như tệp văn bản. Làm theo các bước dưới đây -

Step 1 - Mở một trang tính mới trong Excel.

Step 2 - Nhấp vào tab DATA trên Ribbon.

Step 3 - Bấm From Texttrong nhóm Lấy Dữ liệu Bên ngoài. CácImport Text File hộp thoại xuất hiện.

Bạn có thể thấy rằng .prn, .txt and .csv tệp văn bản mở rộng được chấp nhận.

Step 4- Chọn tệp. Tên tệp đã chọn xuất hiện trong hộp Tên tệp. Nút Mở chuyển thành nút Nhập.

Step 5 - Nhấp vào nút Nhập. Text Import Wizard – Step 1 of 3 hộp thoại xuất hiện.

Step 6 - Nhấp vào tùy chọn Delimited để chọn loại tệp và nhấp vào Tiếp theo.

Các Text Import Wizard – Step 2 of 3 hộp thoại xuất hiện.

Step 7 - Trong Dấu phân cách, chọn Other.

Step 8- Trong hộp bên cạnh Khác, nhập | (Đó là dấu phân cách trong tệp văn bản bạn đang nhập).

Step 9 - Nhấp vào Tiếp theo.

Các Text Import Wizard – Step 3 of 3 hộp thoại xuất hiện.

Step 10 - Trong hộp thoại này, bạn có thể đặt định dạng dữ liệu cột cho từng cột.

Step 11- Sau khi bạn hoàn thành việc định dạng dữ liệu của các cột, hãy nhấp vào Kết thúc. CácImport Data hộp thoại xuất hiện.

Bạn sẽ quan sát những điều sau:

Bảng được chọn để xem và có màu xám. Bảng là tùy chọn xem duy nhất bạn có trong trường hợp này.
Bạn có thể đặt dữ liệu trong một trang tính hiện có hoặc một trang tính Mới.
Bạn có thể chọn hoặc không chọn hộp kiểm Thêm dữ liệu này vào Mô hình Dữ liệu.
Bấm OK sau khi bạn đã lựa chọn.

Dữ liệu xuất hiện trên trang tính bạn đã chỉ định. Bạn đã nhập dữ liệu từ tệp Văn bản vào sổ làm việc Excel.

Nhập dữ liệu từ Workbook khác

Bạn có thể phải sử dụng dữ liệu từ một sổ làm việc Excel khác để phân tích dữ liệu của mình, nhưng người khác có thể duy trì sổ làm việc khác.

Để cập nhật dữ liệu từ sổ làm việc khác, hãy thiết lập kết nối dữ liệu với sổ làm việc đó.

Step 1 - Bấm DATA > Connections trong nhóm Kết nối trên Ruy-băng.

Các Workbook Connections hộp thoại xuất hiện.

Step 2- Nhấp vào nút Thêm trong hộp thoại Kết nối Sổ làm việc. CácExisting Connections hộp thoại xuất hiện.

Step 3 - Bấm Browse for More…cái nút. CácSelect Data Source hộp thoại xuất hiện.

Step 4 - Nhấp vào New Source button. CácData Connection Wizard hộp thoại xuất hiện.

Step 5 - Chọn Other/Advancedtrong danh sách nguồn dữ liệu và nhấp vào Tiếp theo. Hộp thoại Thuộc tính liên kết dữ liệu xuất hiện.

Step 6 - Đặt thuộc tính liên kết dữ liệu như sau:

Nhấn vào Connection chuyển hướng.
Nhấp vào Sử dụng tên nguồn dữ liệu.
Nhấp vào mũi tên xuống và chọn Excel Files từ danh sách thả xuống.
Bấm OK.

Các Select Workbook hộp thoại xuất hiện.

Step 7- Duyệt đến vị trí bạn có sổ làm việc cần nhập. Bấm OK.

Các Data Connection Wizard hộp thoại xuất hiện với Chọn Cơ sở dữ liệu và Bảng.

Note- Trong trường hợp này, Excel xử lý mỗi trang tính được nhập dưới dạng bảng. Tên bảng sẽ là tên trang tính. Vì vậy, để có những tên bảng có ý nghĩa, hãy đặt tên / đổi tên các bảng sao cho phù hợp.

Step 8- Nhấp vào Tiếp theo. CácData Connection Wizard hộp thoại xuất hiện với Lưu tệp kết nối dữ liệu và kết thúc.

Step 9- Nhấp vào nút Kết thúc. CácSelect Table hộp thoại xuất hiện.

Như bạn quan sát, Tên là tên trang tính được nhập dưới dạng TABLE. Bấm OK.

Kết nối Dữ liệu với sổ làm việc bạn đã chọn sẽ được thiết lập.

Nhập dữ liệu từ các nguồn khác

Excel cung cấp cho bạn các tùy chọn để chọn nhiều nguồn dữ liệu khác nhau. Bạn có thể nhập dữ liệu từ những thứ này trong vài bước.

Step 1 - Mở một sổ làm việc trống mới trong Excel.

Step 2 - Nhấp vào tab DATA trên Ribbon.

Step 3 - Bấm From Other Sources trong nhóm Lấy Dữ liệu Bên ngoài.

Menu thả xuống với nhiều nguồn dữ liệu khác nhau xuất hiện.

Bạn có thể nhập dữ liệu từ bất kỳ nguồn dữ liệu nào trong số này vào Excel.

Nhập dữ liệu bằng kết nối hiện có

Trong phần trước đó, bạn đã thiết lập kết nối dữ liệu với sổ làm việc.

Bây giờ, bạn có thể nhập dữ liệu bằng kết nối hiện có đó.

Step 1 - Nhấp vào tab DATA trên Ribbon.

Step 2 - Bấm Existing Connectionstrong nhóm Lấy Dữ liệu Bên ngoài. Hộp thoại Kết nối Hiện có xuất hiện.

Step 3 - Chọn kết nối từ nơi bạn muốn nhập dữ liệu và nhấp vào Mở.

Đổi tên kết nối dữ liệu

Sẽ hữu ích nếu các kết nối dữ liệu bạn có trong sổ làm việc của mình có tên có ý nghĩa để dễ hiểu và dễ định vị.

Step 1 - Đi tới DATA > Connectionstrên Ribbon. CácWorkbook Connections hộp thoại xuất hiện.

Step 2 - Chọn kết nối mà bạn muốn đổi tên và nhấp vào Thuộc tính.

Các Connection Propertieshộp thoại xuất hiện. Tên hiện tại xuất hiện trong hộp Tên kết nối -

Step 3- Chỉnh sửa tên kết nối và nhấp vào OK. Kết nối dữ liệu sẽ có tên mới mà bạn đã đặt.

Làm mới kết nối dữ liệu bên ngoài

Khi bạn kết nối sổ làm việc Excel của mình với nguồn dữ liệu bên ngoài, như bạn đã thấy trong các phần trên, bạn muốn giữ cho dữ liệu trong sổ làm việc của mình được cập nhật phản ánh những thay đổi được thực hiện đối với nguồn dữ liệu bên ngoài theo thời gian.

Bạn có thể làm điều này bằng cách làm mới các kết nối dữ liệu bạn đã thực hiện với các nguồn dữ liệu đó. Bất cứ khi nào bạn làm mới kết nối dữ liệu, bạn sẽ thấy những thay đổi dữ liệu gần đây nhất từ nguồn dữ liệu đó, bao gồm bất kỳ thứ gì mới hoặc đã được sửa đổi hoặc đã bị xóa.

Bạn có thể chỉ làm mới dữ liệu đã chọn hoặc tất cả các kết nối dữ liệu trong sổ làm việc cùng một lúc.

Step 1 - Nhấp vào tab DATA trên Ribbon.

Step 2 - Bấm Refresh All trong nhóm Kết nối.

Khi bạn quan sát, có hai lệnh trong danh sách thả xuống - Làm mới và Làm mới tất cả.

Nếu bạn nhấp vào Refresh, dữ liệu đã chọn trong sổ làm việc của bạn được cập nhật.
Nếu bạn nhấp vào Refresh All, tất cả các kết nối dữ liệu đến sổ làm việc của bạn đều được cập nhật.

Cập nhật tất cả các kết nối dữ liệu trong sổ làm việc

Bạn có thể có một số kết nối dữ liệu với sổ làm việc của mình. Bạn cần cập nhật chúng theo thời gian để sổ làm việc của bạn có quyền truy cập vào dữ liệu gần đây nhất.

Step 1 - Bấm vào bất kỳ ô nào trong bảng có chứa liên kết đến tệp dữ liệu đã nhập.

Step 2 - Nhấp vào tab Dữ liệu trên Ruy-băng.

Step 3 - Bấm Làm mới Tất cả trong nhóm Kết nối.

Step 4- Chọn Làm mới Tất cả từ danh sách thả xuống. Tất cả các kết nối dữ liệu trong sổ làm việc sẽ được cập nhật.

Tự động làm mới dữ liệu khi một sổ làm việc được mở

Bạn có thể muốn có quyền truy cập vào dữ liệu gần đây từ các kết nối dữ liệu với sổ làm việc của bạn bất cứ khi nào sổ làm việc của bạn được mở.

Step 1 - Bấm vào bất kỳ ô nào trong bảng có chứa liên kết đến tệp dữ liệu đã nhập.

Step 2 - Nhấp vào tab Dữ liệu.

Step 3 - Nhấp vào Kết nối trong nhóm Kết nối.

Hộp thoại Kết nối Sổ làm việc xuất hiện.

Step 4- Nhấp vào nút Thuộc tính. Hộp thoại Thuộc tính kết nối xuất hiện.

Step 5 - Nhấp vào tab Sử dụng.

Step 6 - Đánh dấu vào tùy chọn - Làm mới dữ liệu khi mở tệp.

Bạn cũng có một tùy chọn khác - Remove data from the external data range before saving the workbook. Bạn có thể sử dụng tùy chọn này để lưu sổ làm việc với định nghĩa truy vấn nhưng không có dữ liệu bên ngoài.

Step 7- Bấm OK. Bất cứ khi nào bạn mở sổ làm việc của mình, dữ liệu cập nhật sẽ được tải vào sổ làm việc của bạn.

Tự động làm mới dữ liệu theo định kỳ

Bạn có thể đang sử dụng sổ làm việc của mình để giữ nó mở trong thời gian dài hơn. Trong trường hợp như vậy, bạn có thể muốn dữ liệu được làm mới định kỳ mà không cần bất kỳ sự can thiệp nào từ bạn.

Step 1 - Bấm vào bất kỳ ô nào trong bảng có chứa liên kết đến tệp dữ liệu đã nhập.

Step 2 - Nhấp vào tab Dữ liệu trên Ruy-băng.

Step 3 - Nhấp vào Kết nối trong nhóm Kết nối.

Hộp thoại Kết nối Sổ làm việc xuất hiện.

Step 4 - Nhấp vào nút Thuộc tính.

Hộp thoại Thuộc tính kết nối xuất hiện. Đặt các thuộc tính như sau:

Nhấn vào Usage chuyển hướng.
Kiểm tra tùy chọn Refresh every.
Nhập 60 là số phút giữa mỗi thao tác làm mới và nhấp vào Ok.

Dữ liệu của bạn sẽ được tự động làm mới sau mỗi 60 phút. (tức là cứ sau một giờ).

Bật làm mới nền

Đối với các tập dữ liệu rất lớn, hãy cân nhắc chạy làm mới nền. Điều này trả lại quyền kiểm soát Excel cho bạn thay vì khiến bạn đợi vài phút trở lên để quá trình làm mới kết thúc. Bạn có thể sử dụng tùy chọn này khi bạn đang chạy một truy vấn trong nền. Tuy nhiên, trong thời gian này, bạn không thể chạy truy vấn cho bất kỳ loại kết nối nào truy xuất dữ liệu cho Mô hình Dữ liệu.