Nếu bạn đang tìm cách lấy và chuyển đổi dữ liệu (Get & Transform) trong Excel, đây chính là nơi phù hợp. Đôi khi cần nhập tập dữ liệu từ nguồn ngoài: file Excel khác, Text/CSV, Web, v.v. Chúng ta có thể lấy dữ liệu và chuyển đổi vào trang tính Excel bằng vài bước đơn giản. Bài viết giới thiệu 4 cách dễ dàng để Get & Transform data trong Excel.
Get & Transform Data là gì?
Bạn có thể dùng Get & Transform trong Excel để nhập hoặc kết nối dữ liệu ngoài, sau đó sửa đổi cho phù hợp nhu cầu: xóa cột, thay đổi kiểu dữ liệu, hợp nhất bảng... Sau đó dùng Excel tạo biểu đồ, báo cáo dựa trên truy vấn. Dữ liệu có thể cập nhật bằng cách Làm mới (Refresh) định kỳ.
4 Ví dụ phù hợp Get & Transform Data trong Excel
Dùng nhóm Get & Transform để nhập và chỉnh sửa nhiều loại dữ liệu ngoài. Bài viết demo lấy dữ liệu từ Excel Workbook, Text/CSV, Web, Table/Range rồi chuyển đổi thành tập dữ liệu mới.
1. Get & Transform Data từ Excel Workbook
Các bước:
- Tab Data → Get Data → From File → From Excel Workbook.
- Hộp thoại Import Data mở ra. Chọn file Excel mong muốn (ví dụ: "19 jun 2022") → Import.
- Cửa sổ Navigator hiện ra. Chọn Sheet1 từ file → Transform Data.
- Cửa sổ Power Query Editor xuất hiện.
- Xóa hàng đầu tiên (thường là hàng rác/tiêu đề không chuẩn).
- Home → Reduce Rows → Remove Rows → Remove Top Rows.
- Hộp thoại Remove Top Rows: ô Number of rows nhập 1 → OK.
- Dữ liệu sau khi xóa hàng đầu.
- Đặt hàng đầu làm tiêu đề: Use First Row as Headers.
- Dữ liệu đã chuyển đổi. Xem các bước trong khung APPLIED STEPS.
- Lưu vào Excel: Close & Load → Close & Load.
Kết quả: tập dữ liệu đã chuyển đổi xuất hiện ở sheet mới.
2. Get & Transform Data từ Text/CSV File
Các bước:
- Tab Data → Get Data → From File → From Text/CSV.
- Hộp thoại Import Data: chọn file Text (ví dụ: "19 jun 2022") → Import.
- Hộp thoại hiện ra → Transform Data.
- Chỉnh sửa tên: viết hoa chữ cái đầu "andrew" → "Andrew", "emma" → "Emma".
- Tab Transform → Text Column → Format → Capitalize Each Word.
- Dữ liệu đã thành "Andrew", "Emma". Xem bước trong APPLIED STEPS.
- Tiếp tục chuyển "Teni" → "Tenis": Tab Home → Replace Values.
- Hộp thoại Replace Values: Value to Find = "Teni", Replace With = "Tenis" → OK.
- Dữ liệu thành "Tenis". Tương tự sửa "Teniss" → "Tenis".
- Dữ liệu cuối cùng và các bước trong APPLIED STEPS.
- Close & Load → Close & Load để lưu.
Kết quả từ file Text.
3. Dùng tính năng Table/Range để chuyển đổi dữ liệu
Có thể lấy dữ liệu từ Table/Range và chuyển đổi trong Excel. Ví dụ có vùng dữ liệu: Tên, Tuổi, Nhóm, Thể thao của học sinh.
Các bước:
- Chọn ô bất kỳ trong bảng (ví dụ C6).
- Tab Data → Get Data → From Other Sources → From Table/Range.
- Xóa bản ghi trùng lặp: Tab Home → Reduce Rows → Remove Rows → Remove Duplicates.
- Hàng trùng đã bị xóa.
- Close & Load → Close & Load.
Kết quả dùng Table/Range.
4. Lấy dữ liệu từ Web và chuyển đổi bằng Power Query
Các bước:
- Tab Data → Get Data → From Other Sources → From Web.
- Hộp thoại From Web: điền URL trang web → OK.
- Cửa sổ Navigator: chọn bảng dữ liệu (ví dụ Table 0) → Transform Data.
- Xóa Column1: Tab Home → Manage Columns → Remove Columns → Remove Columns.
- Column1 đã bị xóa. Làm tương tự xóa Column2.
- Đặt hàng đầu làm tiêu đề: Tab Home → Use First Row as Headers.
- Dữ liệu đã chuyển đổi và các bước trong APPLIED STEPS.
- Close & Load → Close & Load.
Kết quả từ Web.
Tại sao nên dùng Get & Transform?
Nhiều lý do khiến Get & Transform hữu ích:
- Nhập toàn bộ thư mục file text vào một bảng dữ liệu duy nhất.
- Chuyển đổi file xuất khẩu thành bố cục dễ đọc.
- Nạp hàng triệu dòng vào Power Pivot thuận tiện.
Giới hạn của Get & Transform Data
- Xuất quá 1 triệu dòng Excel thường gặp giới hạn.
- Khi biến đổi hàng triệu dòng, chỉ có cách thủ công tốn thời gian để xuất dữ liệu chưa gom nhóm.
- Triển khai truy vấn Get & Transform cho nhiều người dùng đôi khi không tin cậy, đặc biệt với nhiều nguồn dữ liệu và join.
Cuối cùng, Excel không thiết kế cho mô hình dữ liệu phức tạp. Có thể chạy hồi quy tuyến tính nhanh, nhưng sau đó cần nền tảng chặt chẽ hơn.
Kết luận
Bài viết trình bày 4 cách Get & Transform data trong Excel. Hãy áp dụng cách phù hợp. Hy vọng hữu ích. Thắc mắc hãy bình luận. Đừng quên chia sẻ cách khác nếu có. Ghé ExcelDemy để đọc thêm nhiều bài tương tự. Cảm ơn!