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 cách dù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, Cơ sở dữ liệu SQL Server, 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 Lấy Dữ liệu (Power Query).

3. Trong hộp thoại Chọn nguồn dữ liệu , hãy 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 một 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ử dụng 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 bằng Trình soạn thảo Power Query.

Định hình dữ liệu với Trình soạn thảo Power Query

Quy trình

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

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

   

   Mẹo

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

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

   

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

4. Khi bạn hoàn tất, hãy chọn Trang đầu>Đóng & tải.

Kết quả

Dữ liệu mới được 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à dải ô 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 bạn, 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 đế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 một 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 Tải dữ liệu.
4. Chọn Đóng.

Làm mới những 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 Làmmớ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.
• Trong PivotTable, hãy 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 tiên 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 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à xác đị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 phải đăng nhập, hộp thoại Nhập thông tin đăng nhập sẽ xuất hiện.

Ví dụ:

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

  

• Thông tin xác thực SQL Server:

  

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

1. Chọn Dữ liệu>Lấy thiết>đặt Nguồn Dữ liệu.
2. Trong hộp thoại Thiết đặt Nguồn Dữ liệu, hãy 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.

Biên soạn và truyền mã VBA Power Query

Mặc dù tính năng biên soạn trong Trình soạn thảo Power Query không sẵn dùng trong Excel cho Mac, VBA vẫn hỗ trợ biên soạn Power Query. Chuyển mô-đun mã VBA trong một tệp từ Excel for Windows sang Excel for Mac là một quy trình gồm hai bước. Chương trình mẫu được cung cấp cho bạn ở cuối phần 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 trong mô hình đối tượng của Excel cũng hoạt động trong Excel for Mac: đối tượng Queries, đố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 xuất hiện ra.

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

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

   Bạn có thể sử dụng Microsoft OneDrive. Để biết thêm thông tin, hãy xem Đồ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à tải lên dịch vụ trực tuyến.
2. Trong Excel for Mac, chọn Công cụ>Macro>Trình soạn thảo Visual Basic. Cửa sổ Trình soạn thảo Visual Basic sẽ xuất hiện.
3. Bấm chuột phải vào một đối tượng trong cửa sổ Dự án, rồi chọn Nhập Tệp. Hộp thoại Nhập Tệp xuất hiện.
4. Xác định vị trí tệp VBA, rồi chọn Mở.

Mã mẫu

Đâ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 chứa cá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 các 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 kết nối dữ liệu bên ngoài bị tắt, 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 đối với thư mục mức cao nhất chứa các 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 vị trí tệp .txt hoặc .csv rồi chọn Mở. Text Import Wizard xuất hiện.

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

6. Trong 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 Độ rộng được phân tách hoặc Cố định.

   Số Hàng Trong Bắt đầu nhập tại hàng, chọn số hàng để xác đị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 là mặc định.

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

   Phân tách

   Nếu bạn chọn Dấu tách trên trang đầu tiên, bên dưới Dấu phân tách, 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 các dấu tách liên tiếp như một nếu dữ liệu của bạn chứa dấu tách nhiều hơn một 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, hãy chọn ký tự bao quanh các giá trị trong tệp văn bản, 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 ở 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 đã chọn .

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

   Đối với mỗi cột trong Xem trước dữ liệu đã chọn, hãy chọn cột đó, rồi thay đổi nó 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.

   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àmmới Kếtnối>.

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

1. Chọn dữ liệu>từ ODBC SQL Server. Hộp thoại Kết nối với Nguồn Dữ liệu ODBC của SQL Server 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. Bên dưới Xác thực, chọn một phương thức 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 Trình 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. Chọn Trả lại dữ liệu khi bạn đã sẵn sàng. 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ọnKết nốiDữ liệu>, rồi trong 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 dấu nhắc thông tin xác thực của SQL Server, nhập phương pháp 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. Chọn Trả lại dữ liệu khi bạn đã sẵn sàng.

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 các 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ọnKết nốiDữ liệu>. Hộp thoại Kết nối Sổ làm việc 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.