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

Excel for the Mac kết hợp công nghệ Power Query (còn được gọi là Get & Transform) để cung cấp khả năng lớn hơn khi nhập, làm mới và xác thực nguồn dữ liệu, quản lý nguồn dữ liệu Power Query, xóa thông tin xác thực, thay đổi vị trí của nguồn dữ liệu dựa trên tệp và định hình dữ liệu vào bảng phù hợp với yêu cầu của bạn. Bạn cũng có thể tạo truy vấn Power Query truy vấn bằng cách sử dụng VBA.

Lưu ý: SQL Server liệu cơ sở dữ liệu của bạn chỉ có thể được nhập vào phiên bản Người dùng nội bộ Beta.

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.

Chọn Dữ liệu > Lấy Dữ liệu.
Để chọn nguồn dữ liệu mong muốn, hãy chọn Tải Dữ liệu (Power Query).
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ó.
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.
Chọn dữ liệu bạn muốn nhập.
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.

Kết quả điển hình cho truy vấn

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.

Lưu ý: Tính năng này thường sẵn dùng cho người đăng ký Microsoft 365, chạy Phiên bản 16.69 (23010700) trở lên của Excel for Mac. Nếu bạn là người đăng ký Microsoft 365, hãy đảm bảo bạn có phiên bản Office mới nhất.

Quy trình

Chọn Dữ > Lấy Dữ liệu (Power Query).
Để mở ứng dụng Trình soạn thảo truy vấn, hãy chọn Khởi chạy Trình soạn thảo Power Query.

Mẹ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.
Đị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ợ giúp Excel.
Khi bạn thực hiện xong, chọn Trang chủ > Đóng & 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.

Kết quả điển hình cho truy vấn

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.

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.
Chọn kết nối, rồi chọn Thay đổi Đường dẫn Tệp.
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.
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, hãy chọn Dữ > Làm mới 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.

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.

Ví dụ:

Thông tin xác thực SharePoint:
SQL Server chứng danh:

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

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

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

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.
Trong Excel, hãy đảm bảo visual basic Trình soạn thảo mở bằng cách nhấn ALT+F11.
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.
Nhập tên tệp, đảm bảo phần mở rộng tệp là .bas, rồi chọn Lưu.
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

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.
Trong Excel for Mac, chọn Công cụ > Macro > Visual Basic cho Trình soạn thảo. Cửa sổ Visual Basic Trình soạn thảo xuất hiện.
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.
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