Đánh giá lequocthai.com:
Các công cụ có sẵn trong Excel thường có thể hoàn thành công việc, nhưng chúng thường thiếu sự linh hoạt và độ ổn định cần thiết cho những công việc chuyển đổi và xử lý dữ liệu phức tạp. Power Query không những thực hiện những việc này nhanh hơn, mà còn hiệu quả hơn. Dưới đây là năm thao tác bạn nên chuyển từ các công cụ trên ribbon Excel sang sử dụng Power Query Editor.
Trước khi bắt đầu: Tải dữ liệu của bạn
Chọn bất kỳ ô nào trong tập dữ liệu của bạn, sau đó trong tab Data, nhấp vào “From Table/Range”.
Sau khi bạn hoàn thành việc thiết lập các truy vấn trong Power Query, nhấp vào “Close & Load” trong tab Home để đưa dữ liệu trở lại một trang tính mới.
1. Sử dụng Cột Điều kiện (Thay vì các hàm IF lồng nhau)
Các hàm logic thông thường trong Excel thường phải dựa vào những chuỗi văn bản dài, được tạo thủ công, vốn rất khó xây dựng và càng khó sửa chữa hơn.
Tình huống
Bạn cần phân loại doanh số thành “small”, “medium”, và “large” dựa trên giá trị.
Thông thường, bạn sẽ thêm một cột mới và sử dụng công thức IF lồng nhau:
=IF([@Sales]>1000,"Large",IF([@Sales]>500,"Medium","Small"))
Tuy nhiên, chỉ một dấu phẩy đặt sai vị trí cũng có thể làm hỏng cả công thức. Việc kiểm tra logic trong một thanh công thức dài như vậy cũng rất phiền phức.
Hướng dẫn tạo Cột Điều kiện
Sau khi tải dữ liệu vào Power Query Editor, trong tab Add Column, nhấp vào “Conditional Column”.
Trong hộp thoại, đặt tên cho cột mới và thiết lập logic của bạn bằng các menu thả xuống. Trong trường hợp này, quy tắc đầu tiên là trả về từ “Large” nếu giá trị trong cột Sales lớn hơn 1,000.
Ở đây, không giống với công thức Excel thông thường, bạn không cần đặt văn bản trong dấu ngoặc kép.
Sau đó, nhấp vào “Add Clause” và lặp lại quy trình cho điều kiện mức trung bình.
Cuối cùng, nhập giá trị Else là Small và nhấp vào “OK”.
Cột điều kiện mới sẽ xuất hiện, và bạn đã sẵn sàng đóng và tải bảng mới vào trang tính.
Tại sao Cột Điều kiện tốt hơn hàm IF lồng nhau
Phương pháp này tạo ra một thao tác trực quan mà bất kỳ ai cũng có thể kiểm tra ngay lập tức. Bạn không cần viết bất kỳ mã nào, và nếu ngưỡng giá trị thay đổi, bạn chỉ cần nhấp vào biểu tượng bánh răng trong ngăn Applied Steps để cập nhật quy tắc mà không cần động đến bất kỳ dấu ngoặc nào.
2. Sử dụng Merge Queries (Thay vì VLOOKUP/XLOOKUP)
Liên kết hai tập dữ liệu khác nhau là một công việc cơ bản trong Excel, nhưng việc dựa vào các công thức tham chiếu ô thường dẫn đến các tham chiếu bị lỗi và hiệu suất chậm.
Tình huống
Bạn có một bảng Sales và một danh sách Product Master nằm trên các trang tính riêng biệt, và bạn cần kéo giá đơn vị từ danh sách Master vào bảng Sales.
Mặc dù XLOOKUP có thể làm điều này, nhưng nó trở thành một rủi ro nếu tệp nguồn bị di chuyển, tiêu đề cột bị xóa hoặc tập dữ liệu mở rộng đáng kể.
Cách hợp nhất các truy vấn (Merge Queries)
Bước đầu tiên là lưu bảng đầu tiên dưới dạng một truy vấn (query) trong Power Query. Để làm điều này, tải bảng vào Power Query Editor, và dưới mũi tên Close & Load, nhấp vào “Close & Load To”.
Sau đó, trong hộp thoại, chọn “Only Create Connection”, và nhấp vào “OK”.
Lúc này, ngăn “Queries and Connections” sẽ mở ra với kết nối bạn vừa tạo.
Bây giờ, lặp lại quy trình này cho bảng kia để cả hai kết nối đều hiển thị trong ngăn.
Tiếp theo, nhấp chuột phải vào truy vấn bạn muốn thêm dữ liệu vào (ví dụ: Sales_Data) và chọn “Edit”.
Trong tab Home, nhấp vào “Merge Queries”, chọn bảng Product Master từ danh sách thả xuống, chọn cột SKU khớp nhau trong cả hai bản xem trước, và nhấp vào “OK”.
Nhấp vào biểu tượng “Expand” trong tiêu đề của cột mới, chọn cột bạn muốn chuyển từ bảng này sang bảng kia (trong trường hợp này là “Price”), và nhấp vào “OK”.
Khi bạn nhấp vào “Close & Load”, truy vấn Sales vẫn được lưu dưới dạng kết nối. Vì vậy, nhấp chuột phải vào nó, chọn “Load To”, chọn “Table”, và chọn “New worksheet”. Sau khi nhấp “OK”, bảng đã hợp nhất sẽ được tải vào một trang tính mới.
Tại sao Merge Queries tốt hơn
Các hàm tra cứu hoạt động ở cấp độ từng ô, trong khi tính năng hợp nhất truy vấn (Merge Queries) của Power Query hoạt động ở cấp độ bảng. Điều này rất quan trọng trong các workbook phức tạp:
- Linh hoạt: Bạn có thể hợp nhất nhiều bảng, không chỉ hai.
- Ổn định: Các kết nối được lưu trong Power Query không bị ảnh hưởng bởi việc di chuyển tệp hoặc thay đổi trang tính.
- Hiệu suất: Xử lý dữ liệu lớn hiệu quả hơn nhiều so với hàng nghìn công thức XLOOKUP.
3. Sử dụng công cụ Unpivot (Thay vì chuyển đổi dữ liệu thủ công)
Các công cụ Excel thông thường khiến việc chuyển đổi dữ liệu từ định dạng ngang (nhiều cột) sang định dạng dọc (nhiều dòng) – cần thiết cho PivotTables và phân tích – trở nên khó khăn.
Tình huống
Bạn nhận được một báo cáo với các danh mục ở cột đầu tiên, nhưng các tháng lại là các tiêu đề riêng biệt. Để dùng dữ liệu này trong PivotTable, bạn cần các tháng nằm trong một cột dọc duy nhất.
Trong Excel thông thường, bạn sẽ phải sao chép, dán và chuyển vị từng tháng một cách thủ công — một quy trình tốn thời gian và dễ nhầm lẫn mà bạn phải lặp lại mỗi khi có dữ liệu mới.
Cách sử dụng công cụ Unpivot
Sau khi tải bảng dữ liệu ngang của bạn vào Power Query Editor, chọn các cột bạn không muốn chuyển đổi (nhấn giữ Ctrl khi nhấp vào tiêu đề nếu có nhiều cột), nhấp chuột phải vào một trong những tiêu đề đó, và chọn “Unpivot Other Columns”.
Power Query ngay lập tức chuyển đổi dữ liệu, tạo ra một cột thuộc tính (attribute) và một cột giá trị (value).
Nhấp đúp vào các tiêu đề để đổi tên chúng thành Month và Revenue, và đảm bảo định dạng số chính xác bằng cách nhấp vào biểu tượng định dạng bên cạnh tên cột.
Tại sao Unpivot tốt hơn
Công cụ Unpivot là động và không làm thay đổi dữ liệu gốc. Trong Excel tiêu chuẩn, nếu bạn thêm một cột mới, bạn sẽ phải cấu trúc lại trang tính thủ công. Tuy nhiên, Power Query tự động phát hiện bất kỳ tiêu đề tháng mới nào được thêm vào nguồn dữ liệu và chuyển chúng vào danh sách dọc của bạn trong lần làm mới tiếp theo.
4. Sử dụng “From Folder” (Thay vì sao chép-dán thủ công)
Việc sao chép dữ liệu thủ công từ nhiều tập tin là một trong những nguyên nhân phổ biến nhất gây ra sai sót trong bảng tính Excel.
Tình huống
Mỗi tuần, khi nhận được báo cáo doanh số khu vực mới nhất, bạn mở tập tin, sao chép dữ liệu và dán nó vào cuối trang tính chính. Quy trình này không chỉ chậm mà còn rất dễ vô tình bỏ sót một hàng hoặc dán trùng dữ liệu.
Cách nhập dữ liệu từ một thư mục
Đầu tiên, đảm bảo tất cả các tập tin nguồn của bạn nằm trong một thư mục chuyên dụng duy nhất trên máy tính. Để tránh lỗi, hãy đảm bảo thư mục này chỉ chứa các tập tin bạn định hợp nhất.
Trong Excel, nhấp vào Data > Get Data > From File > From Folder.
Duyệt đến thư mục chuyên dụng của bạn và nhấp vào “Open”.
Mở rộng menu thả xuống “Combine” và chọn “Combine & Transform Data”.
Chọn một tập tin mẫu hoặc trang tính để làm mẫu và nhấp vào “OK”. Đảm bảo tất cả các workbook đều dùng chung tên trang tính và tiêu đề cột, vì tập tin mẫu sẽ quy định cấu trúc cho toàn bộ quá trình hợp nhất.
Khi hài lòng với kết quả, nhấp vào “Close & Load”.
Tại sao nhập từ thư mục tốt hơn
Kết hợp các workbook Excel bằng Power Query có nghĩa là bạn không bao giờ phải mở từng tập tin báo cáo riêng lẻ nữa — thay vào đó, về sau bạn chỉ cần thả các báo cáo mới vào thư mục và nhấp vào “Refresh” trong tập tin Excel chính của mình. Điều này loại bỏ hoàn toàn sai sót và tiết kiệm hàng giờ làm việc mỗi tuần.
5. Sử dụng công cụ Split Column (Thay vì các phương pháp tách cột thông thường)
Có nhiều cách để tách cột bằng giao diện Excel thông thường, nhưng chúng đi kèm những hạn chế có thể gây ra vấn đề sau này.
Tình huống
Bạn có một cột chứa tên đầy đủ mà bạn cần tách thành tên và họ.
Trong ribbon Excel thông thường, bạn có thể dùng trình hướng dẫn Text to Columns, nhưng kết quả là tĩnh, nghĩa là nếu bạn thêm tên mới hoặc cập nhật tên cũ, việc tách sẽ không tự động cập nhật. Bạn cũng có thể dùng hàm TEXTSPLIT, nhưng điều này dẫn đến các cột không nhất quán nếu một tên có chứa chữ viết tắt tên đệm.
Cách tách cột
Sau khi tải dữ liệu vào Power Query Editor, nhấp chuột phải vào tiêu đề cột muốn tách, và trong menu Split Column, chọn “By Delimiter”.
Chọn “Space” làm dấu phân cách.
Khi hoàn tất, nhấp “OK”, đổi tên các cột, sau đó đóng Editor và tải tập dữ liệu kết quả vào workbook Excel.
Bất kỳ ô trống nào sẽ hiển thị trong bảng Excel cuối cùng. Nếu muốn khắc phục trong Power Query Editor, nhấp chuột phải vào tiêu đề cột và chọn “Replace Values” để tự động điền bằng số không hoặc N/A.
Tại sao tách cột trong Power Query tốt hơn
- Nhất quán: Bạn có thể chọn tách ở dấu phân cách ngoài cùng bên trái, ngoài cùng bên phải hoặc mỗi lần xuất hiện, đảm bảo cấu trúc dữ liệu nhất quán ngay cả với các đầu vào khác nhau (ví dụ: “John A. Smith”).
- Động: Giống như mọi thao tác Power Query, việc tách là một phần của quy trình có thể làm mới. Nếu tên đầy đủ trong nguồn thay đổi, bảng đầu ra sẽ tự động cập nhật sau khi làm mới.
- Kiểm soát: Bạn có thể dễ dàng xem và sửa đổi logic tách trong ngăn Applied Steps.
Sau khi thành thạo năm thao tác này, bạn có thể tối ưu hóa hơn nữa quy trình của mình bằng cách tìm hiểu thêm các lệnh Power Query thiết yếu để xử lý ngay cả những tập dữ liệu phức tạp nhất chỉ với một cú nhấp chuột.
Tags: Power Query, Excel Data Processing, Data TransformationPower Query, Excel Data Processing, Data Transformation
