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

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 Dữ liệu > 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.

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.

Quy trình

1. Chọn Dữ > 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 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ợ giúp Excel.

4. 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 trang tính mới.

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ộ và cơ sở dữ liệu Microsoft SQL Server.

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, 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.

Ví dụ:

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

• SQL Server chứng danh:
  
  

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

1. Chọn Dữ liệu > Thiết đặt Nguồn 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 Permissions.

4. 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

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 mà bạn đã lưu trong "Bước một: Excel for Windows" và được tải lên một dịch vụ trực tuyến.

2. Trong Excel for Mac, chọn Công cụ> Macro> sửa Visual Basic. 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