Nhập và định hình dữ liệu trong Excel for Mac (Power Query)

Nhập nguồn dữ liệu

Bạn có thể nhập dữ liệu vào Excel bằng Power Query từ nhiều nguồn dữ liệu khác nhau: Sổ làm việc Excel, Văn bản/CSV, XML, JSON, SQL Server Database, Danh sách SharePoint Online, OData, Bảng Trống và Truy vấn Trống.

1. Chọn Lấy Dữ>liệu.

   

2. Để chọn nguồn dữ liệu mong muốn, hãy chọn Tải Dữ liệu (Power Query).

3. Trong hộp thoại Chọn nguồn dữ liệu, chọn một trong các nguồn dữ liệu sẵn có.

   

4. Kết nối với nguồn dữ liệu. Để tìm hiểu thêm về cách kết nối với từng nguồn dữ liệu, hãy xem Nhập dữ liệu từ nguồn dữ liệu.

5. Chọn dữ liệu bạn muốn nhập.

6. Tải dữ liệu bằng cách bấm vào nút Tải.

Kết quả

Dữ liệu đã nhập sẽ xuất hiện trong trang tính mới.

Các bước tiếp theo

Để định hình và chuyển đổi dữ liệu bằng cách sử Trình soạn thảo Power Query, hãy chọn Chuyển đổi Dữ liệu. Để biết thêm thông tin, hãy xem Định hình dữ liệu Trình soạn thảo Power Query.

Định hình dữ liệu bằng Trình soạn thảo Power Query

Thủ tục

1. Chọn Lấy Dữ>liệu (Power Query).

2. Để mở ứng dụng Trình soạn thảo truy vấn, hãy chọn Khởi chạy ứng Trình soạn thảo Power Query.

   

   Mẹo

   Bạn cũng có thể truy nhập danh Trình soạn thảo truy vấn bằng cách chọn Lấy Dữ liệu (Power Query), chọn nguồn dữ liệu, rồi bấm Tiếp.

3. Định hình và chuyển đổi dữ liệu của bạn bằng Trình soạn thảo truy vấn cách sử dụng dữ liệu như trong Excel for Windows.

   

   Để biết thêm thông tin, hãy Power Query trợ trợ giúp Excel.

4. Khi bạn hoàn tất, chọn Đóng Trang>chủ & tải.

Kết quả

Dữ liệu mới nhập sẽ xuất hiện trong một trang tính mới.

Làm mới nguồn dữ liệu

Bạn có thể làm mới các nguồn dữ liệu sau: tệp SharePoint, danh sách SharePoint, thư mục SharePoint, OData, tệp văn bản/CSV, sổ làm việc Excel (.xlsx), tệp XML và JSON, bảng và phạm vi cục bộ, cơ sở dữ liệu microsoft SQL Server và thư mục.

Làm mới lần đầu tiên

Lần đầu tiên bạn tìm cách làm mới nguồn dữ liệu dựa trên tệp trong truy vấn sổ làm việc của mình, bạn có thể cần cập nhật đường dẫn tệp.

1. Chọn Dữ liệu, mũi tên bên cạnh Lấy Dữ liệu, rồi chọn Thiết đặt Nguồn Dữ liệu. Hộp thoại Thiết đặt nguồn dữ liệu xuất hiện.
2. Chọn kết nối, rồi chọn Thay đổi Đường dẫn Tệp.
3. Trong hộp thoại Đường dẫn tệp, chọn vị trí mới, rồi chọn Lấy Dữ liệu.
4. Chọn Đóng.

Làm mới các lần tiếp theo

Để làm mới:

• Tất cả các nguồn dữ liệu trong sổ làm việc, chọn Làm mới Dữ>liệu Tất cả.
• Một nguồn dữ liệu cụ thể, bấm chuột phải vào bảng truy vấn trên trang tính, rồi chọn Làm mới.
• Một PivotTable, chọn một ô trong PivotTable, rồi chọn Phân tích PivotTable Làm>mới Dữ liệu.

Nhập và xóa thông tin xác thực

Lần đầu bạn truy nhập SharePoint, SQL Server, OData hoặc các nguồn dữ liệu khác yêu cầu quyền, bạn phải cung cấp thông tin xác thực thích hợp. Bạn cũng có thể muốn xóa thông tin xác thực để nhập thông tin xác thực mới.

Nhập thông tin xác thực

Khi bạn làm mới truy vấn lần đầu tiên, bạn có thể được yêu cầu đăng nhập. Chọn phương pháp xác thực và chỉ định thông tin đăng nhập để kết nối với nguồn dữ liệu và tiếp tục làm mới.

Nếu cần đăng nhập, hộp thoại Nhập thông tin xác thực sẽ xuất hiện.

Ví dụ:

• Thông tin xác thực SharePoint:

  

• SQL Server xác thực của bạn:

  

Xóa thông tin xác thực

1. Chọn Thiết đặt Nguồn>Dữ liệu Lấy>Dữ liệu.
2. Trong hộp thoại Thiết đặt NguồnDữ liệu, chọn kết nối bạn muốn.
3. Ở dưới cùng, chọn Xóa Quyền.
4. Xác nhận đây là điều bạn muốn thực hiện, rồi chọn Xóa.

Tác giả và truyền Power Query mã VBA

Mặc dù tính năng biên soạn Trình soạn thảo Power Query sẵn dùng trong Excel for Mac nhưng VBA vẫn hỗ Power Query tác giả. Chuyển mô-đun mã VBA trong tệp từ Excel for Windows sang Excel for Mac là một quy trình gồm hai bước. Một chương trình mẫu được cung cấp cho bạn ở cuối mục này.

Bước một: Excel for Windows

1. Trên Excel Windows, phát triển truy vấn bằng cách sử dụng VBA. Mã VBA sử dụng các thực thể sau đây trong mô hình đối tượng của Excel cũng hoạt động trong Excel for Mac: Đối tượng Truy vấn, đối tượng WorkbookQuery, Thuộc tính Workbook.Queries. Để biết thêm thông tin, hãy xem Tham khảo về VBA trong Excel.

2. Trong Excel, hãy đảm bảo Trình soạn thảo Visual Basic đang mở bằng cách nhấn ALT+F11.

3. Bấm chuột phải vào mô-đun, rồi chọn Xuất Tệp. Hộp thoại Xuất sẽ xuất hiện.

4. Nhập tên tệp, đảm bảo phần mở rộng tệp là .bas, rồi chọn Lưu.

5. Tải tệp VBA lên dịch vụ trực tuyến để giúp dễ truy nhập tệp từ máy Mac.

   Bạn có thể sử dụng Microsoft OneDrive. Để biết thêm thông tin, xem mục Đồng bộ tệp với OneDrive trên Mac OS X.

Bước hai: Excel for Mac

1. Tải tệp VBA xuống tệp cục bộ, tệp VBA bạn đã lưu trong "Bước một: Excel for Windows" và được tải lên dịch vụ trực tuyến.
2. Trong Excel for Mac, chọn Trình soạn thảo>Visual Basic macro> công cụ. Cửa sổ Trình soạn thảo Visual Basic xuất hiện.
3. Bấm chuột phải vào một đối tượng trong cửa sổ Project, rồi chọn Nhập Tệp. Hộp thoại Nhập Tệp xuất hiện.
4. Tìm tệp VBA, rồi chọn Mở.

Mã mẫu

Dưới đây là một số mã cơ bản mà bạn có thể điều chỉnh và sử dụng. Đây là truy vấn mẫu tạo danh sách có giá trị từ 1 đến 100.

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub

Nhập và làm mới tệp TXT hoặc CSV

1. Mở sổ làm việc Excel.

2. Nếu bạn nhận được cảnh báo bảo mật về việc tắt kết nối dữ liệu ngoài, hãy chọn Bật Nội dung.

3. Nếu hộp thoại Cấp Quyền truy nhập Tệp xuất hiện, hãy chọn Chọn, rồi chọn Cấp Quyền truy nhập vào thư mục mức cao nhất có chứa tệp nguồn dữ liệu.

4. Chọn Dữ liệu>từ văn bản (thừa tự). Hộp thoại Finder xuất hiện.

5. Xác định .txt hoặc .csv tệp, rồi chọn Mở. Trình hướng dẫn Nhập Văn bản sẽ xuất hiện.

   Mẹo Kiểm tra liên tục ngăn Xem trước dữ liệu đã chọn để xác nhận lựa chọn của bạn.

6. Ở trang đầu tiên, hãy làm như sau:
   Loại Tệp Để chọn loại tệp văn bản, hãy chọn Phân tách hoặc Độ rộngcố định.

   Số Hàng Trong Bắt đầu nhập tại hàng, chọn số hàng để chỉ định hàng dữ liệu đầu tiên mà bạn muốn nhập.

   Bộ Ký tự Trong Nguồn gốc tệp, chọn bộ ký tự được sử dụng trong tệp văn bản. Trong hầu hết các trường hợp, bạn có thể để cài đặt này ở chế độ mặc định.

7. Ở trang thứ hai, hãy làm như sau:

   Phân tách

   Nếu bạn đã chọn Phân tách ở trang đầu tiên, bên dưới Dấu tách, hãy chọn ký tự phân tách hoặc sử dụng hộp kiểm Khác để nhập dấu tách không được liệt kê.

   Chọn Coi dấu tách liên tiếp là dấu tách nếu dữ liệu của bạn chứa dấu tách gồm nhiều ký tự giữa các trường dữ liệu hoặc nếu dữ liệu của bạn chứa nhiều dấu tách tùy chỉnh.

   Trong hạn định Văn bản, chọn ký tự bao quanh giá trị trong tệp văn bản của bạn, ký tự này thường là ký tự dấu ngoặc kép (").
   Độ rộng cố định

   Nếu bạn chọn Độ rộng cố định trên trang đầu tiên, hãy làm theo hướng dẫn để tạo, xóa hoặc di chuyển đường ngắt trong hộp Xem trước dữ liệu được chọn.

8. Ở trang thứ ba, hãy làm như sau:

   Đối với mỗi cột bên dưới mục Xem trước dữ liệu đã chọn, hãy chọn dữ liệu đó, rồi thay đổi thành định dạng cột khác nếu bạn muốn. Bạn có thể đặt thêm định dạng ngày và chọn Nâng cao để thay đổi cài đặt dữ liệu số. Bạn cũng có thể chuyển đổi dữ liệu sau khi nhập dữ liệu.

   Chọn Hoàn tất. Hộp thoại Nhập Dữ liệu xuất hiện.

9. Chọn nơi bạn muốn thêm dữ liệu: trên trang tính hiện có, trên trang tính mới hoặc trong PivotTable.

10. Chọn OK.
    Để đảm bảo kết nối đang hoạt động, hãy nhập một số dữ liệu, rồi chọn Làm mới Kết>nối.

Kết nối với Cơ sở dữ liệu SQL (ODBC)

1. Chọn Dữ>liệu SQL Server ODBC. Hộp thoại Kết nối SQL Server Nguồn Dữ liệu ODBC xuất hiện.

   

2. Nhập máy chủ vào hộp Tên Máy chủ và tùy ý nhập cơ sở dữ liệu vào hộp Tên Cơ sở dữ liệu.

   Lấy thông tin này từ người quản trị cơ sở dữ liệu.

3. Trong Xác thực, chọn một phương pháp từ danh sách: Tên người dùng/Mật khẩu, Kerberos hoặc NTLM.

4. Nhập thông tin xác thực vào hộp Tên Người dùng và Mật khẩu.

5. Chọn Kết nối. Hộp thoại Bộ dẫn hướng xuất hiện.

6. Trong ngăn bên trái, dẫn hướng đến bảng bạn muốn, rồi chọn bảng đó.

7. Xác nhận câu lệnh SQL ở ngăn bên phải. Bạn có thể thay đổi câu lệnh SQL khi bạn thấy phù hợp.

8. Để xem trước dữ liệu, hãy chọn Chạy.

9. Khi bạn đã sẵn sàng, hãy chọn Trả về Dữ liệu. Hộp thoại Nhập Dữ liệu xuất hiện.

   

10. Chọn nơi bạn muốn thêm dữ liệu: trên trang tính hiện có, trên trang tính mới hoặc trong PivotTable.

11. Để đặt thuộc tính kết nối trên tab Sửdụng và Định nghĩa của hộp thoại Thuộc tính, hãy chọn Thuộc tính. Sau khi nhập dữ liệu, bạn cũng có thể chọn Kết nối Dữ>liệu, rồitrong hộp thoại Thuộc tính Kết nối, chọn Thuộc tính.

12. Chọn OK.

13. Để đảm bảo kết nối đang hoạt động, hãy nhập một số dữ liệu, rồi chọn Làm mới Dữ liệu>Tất cả.

Kết nối với cơ sở dữ liệu khác (Microsoft Query)

Nếu bạn muốn sử dụng nguồn bên ngoài không phải là Cơ sở dữ liệu SQL (ví dụ: FileMaker Pro), bạn có thể sử dụng trình điều khiển Kết nối Cơ sở dữ liệu Mở (ODBC) được cài đặt trên máy Mac của mình. Thông tin về trình điều khiển có sẵn trên trang web này. Sau khi đã cài đặt trình điều khiển cho nguồn dữ liệu của bạn, hãy làm theo các bước sau:

1. Chọn Dữ liệu>từ Cơ sở dữ liệu (Truy vấn Microsoft).

2. Thêm nguồn dữ liệu cho cơ sở dữ liệu của bạn, rồi chọn OK.

3. Tại lời nhắc SQL Server xác thực, hãy nhập phương thức xác thực, tên người dùng và mật khẩu.

   

4. Ở bên trái, chọn mũi tên bên cạnh máy chủ để xem cơ sở dữ liệu.

5. Chọn mũi tên bên cạnh cơ sở dữ liệu bạn muốn.

6. Chọn bảng bạn muốn.

7. Để xem trước dữ liệu, hãy chọn Chạy.

8. Khi bạn đã sẵn sàng, hãy chọn Trả về Dữ liệu.

9. Trong hộp thoại Nhập Dữ liệu, hãy chọn vị trí bạn muốn đặt dữ liệu: trên trang tính hiện có, trên trang tính mới hoặc trong PivotTable.

10. Chọn OK.

11. Để đảm bảo kết nối đang hoạt động, hãy nhập một số dữ liệu, rồi chọn Làm mới Dữ liệu>Tất cả.

Xóa thông tin đăng nhập của bạn

Nếu quyền của bạn không hoạt động, bạn có thể không xóa chúng trước, rồi đăng nhập.

1. Chọn Kết nối Dữ>liệu. Hộp thoại Kết nối Sổ làm việc sẽ xuất hiện.

2. Chọn kết nối bạn muốn trong danh sách, rồi chọn Xóa Quyền.