Bạn đã từng nghe nói về Power Query trong Microsoft Excel nhưng luôn nghĩ rằng nó chỉ dành cho các chuyên gia Excel? Hãy gạt bỏ suy nghĩ đó, bởi thực tế, Power Query được thiết kế để thân thiện với người dùng và quan trọng nhất, giúp bạn tiết kiệm rất nhiều công sức khi tổ chức dữ liệu của mình. Công cụ mạnh mẽ này là một phần không thể thiếu đối với bất kỳ ai thường xuyên làm việc với bảng tính, từ việc nhập liệu đơn giản cho đến các thao tác chuyển đổi dữ liệu phức tạp.
Hướng dẫn này dành cho những độc giả mới làm quen với Microsoft Excel hoặc Power Query. Các mẹo được trình bày không yêu cầu bạn phải biết hay sử dụng bất kỳ ngôn ngữ công thức Power Query M nào, và chúng sẽ là một điểm khởi đầu tuyệt vời để làm quen với một số lệnh cơ bản bạn có thể sử dụng trong công cụ thao tác dữ liệu mạnh mẽ này. Bằng cách áp dụng các thủ thuật Power Query Excel dưới đây, bạn sẽ nhanh chóng thấy được hiệu quả trong việc làm sạch, sắp xếp và chuẩn bị dữ liệu cho các phân tích chuyên sâu hơn.
1. Tách các ô theo dấu phân cách để phân lập giá trị
Có nhiều cách trong Excel để tách dữ liệu thành nhiều cột, bao gồm công cụ Text To Columns (Chuyển văn bản thành cột), Flash Fill (Điền nhanh) và các hàm tích hợp sẵn. Tuy nhiên, phương pháp trực quan nhất là khởi chạy Power Query Editor và thực hiện thao tác tại đó.
Hãy tưởng tượng bạn tìm thấy một bảng trên Wikipedia mà bạn muốn chuyển đổi bằng Power Query. Để nhập bảng này, trước tiên, bạn nhấp vào “Get Data” (Tải dữ liệu) trên tab Home (Trang chủ) của thanh công cụ, sau đó chọn From Other Sources (Từ các nguồn khác) > From Web (Từ Web).
Lựa chọn "From Web" trong menu "Get Data" của Excel
Tiếp theo, sau khi dán URL vào trường văn bản trong hộp thoại From Web và nhấp “OK”, bạn chọn bảng trong ngăn bên trái của Navigator (Điều hướng) và nhấp “Transform Data” (Chuyển đổi dữ liệu).
Chọn bảng dữ liệu trong hộp thoại Navigator của Excel và nhấp "Transform Data"
Khi bảng được tải vào Power Query Editor, bạn nhận thấy rằng cột “Career Span” (Thời gian sự nghiệp) chứa hai mảnh dữ liệu—năm mà mỗi cầu thủ bắt đầu sự nghiệp và năm cuối cùng họ thi đấu—và bạn muốn tách chúng thành các cột riêng biệt. Đây là một vấn đề phổ biến khi làm việc với dữ liệu không chuẩn hóa, và Power Query giúp giải quyết nó một cách dễ dàng.
Cột "Career Span" trong Power Query Editor của Excel cần được xử lý
Để làm điều này, bạn nhấp chuột phải vào tiêu đề cột, di chuột qua “Split Column” (Tách cột) và chọn “By Delimiter” (Theo dấu phân cách).
Tùy chọn chia cột theo dấu phân cách được chọn trong Power Query Editor Excel
Dấu phân cách là một ký tự, biểu tượng hoặc khoảng trắng được sử dụng để phân tách các mục trong một chuỗi. Trong trường hợp này, đó là dấu gạch ngang.
Tại thời điểm này, Power Query Editor sẽ xem xét dữ liệu trong cột để xem liệu nó có thể phát hiện một dấu phân cách tiềm năng hay không. Trong ví dụ này, nó đã xác định rằng mỗi ô chứa một dấu gạch ngang và giả định đúng rằng đây là điểm mà ô nên được tách. Tuy nhiên, bạn có thể chọn một dấu phân cách khác từ menu thả xuống đầu tiên nếu cần.
Dấu gạch ngang được chọn làm dấu phân cách để tách cột trong Power Query Editor
Vì chỉ có một dấu phân cách trong mỗi ô của cột đã chọn trong ví dụ này, không cần thực hiện bất kỳ thay đổi nào khác đối với các tùy chọn trong hộp thoại, vì vậy hãy nhấp “OK” và xem dữ liệu mới được chuyển đổi trong Power Query Editor.
Dữ liệu đã được tách thành hai cột "First played" và "Last played" trong Power Query Editor
Để đổi tên các cột mới, bạn nhấp đúp vào tiêu đề cột và nhập nhãn dữ liệu mới. Điều này giúp các cột trở nên dễ hiểu hơn, ví dụ như “Năm bắt đầu” và “Năm kết thúc”.
Đổi tên tiêu đề cột trong Power Query Editor của Excel
Giờ đây, bạn đã tách dữ liệu thành công bằng Power Query Editor, nhưng bạn có nhận thấy rằng các ô trong hai cột mới được căn chỉnh khác nhau không? Điều này là do các giá trị trong cột “First played” (Bắt đầu chơi) đều là số (như được chỉ ra bằng biểu tượng 123 bên cạnh tiêu đề cột), nhưng vì các giá trị trong cột “Last played” (Kết thúc chơi) bao gồm cả văn bản và số, Excel coi đây là một cột văn bản (do đó có ABC trong tiêu đề cột).
Để khắc phục điều này, chọn biểu tượng “ABC” trong cột “Last played” và nhấp vào “Whole Number” (Số nguyên).
Định dạng "Whole Number" được chọn cho cột trong Power Query Editor Excel
Bây giờ, cả hai cột đều được định dạng là tập dữ liệu số.
Hai cột dữ liệu đã được định dạng số trong Power Query Editor Excel
Tuy nhiên, vì một số ô trong cột “Last played” chứa giá trị văn bản, những ô này bây giờ hiển thị dưới dạng lỗi. Nhưng đừng lo lắng—hãy tiếp tục đọc để tìm hiểu cách khắc phục điều này!
2. Thay thế lỗi để sử dụng trong tính toán
Power Query của Excel là một công cụ mạnh mẽ để xử lý mọi lỗi xuất hiện trong dữ liệu của bạn. Việc này đảm bảo tính toàn vẹn và khả năng sử dụng của dữ liệu cho các phân tích hoặc báo cáo sau này.
Lấy truy vấn trong ví dụ trước, một số ô trong cột “Last played” chứa lỗi, vì trước đó chúng chứa giá trị văn bản “Present” (Hiện tại) trong một cột được định dạng là số nguyên.
Các ô lỗi xuất hiện trong cột "Last played" sau khi chuyển đổi định dạng số trong Power Query Editor
Hiện tại, nếu bạn nhấp “Close And Load” (Đóng và Tải) ở góc trên bên trái của Power Query Editor, những ô có giá trị lỗi sẽ bị trống trong bảng kết quả. Mặc dù điều này có vẻ lý tưởng, các ô trống trong một cột Excel có thể gây ra sự cố khi bạn sắp xếp và lọc dữ liệu hoặc tham chiếu tiêu đề cột trong các công thức, vì vậy tốt nhất là điền chúng bằng các giá trị có ý nghĩa.
Để làm điều này, nhấp chuột phải vào tiêu đề cột và chọn “Replace Errors” (Thay thế lỗi).
Menu chuột phải với tùy chọn "Replace Errors" cho cột trong Power Query Editor Excel
Bạn có thể bị cám dỗ chọn “Remove Errors” (Xóa lỗi) trong menu chuột phải. Tuy nhiên, điều này sẽ xóa toàn bộ hàng, không chỉ lỗi, vì vậy chỉ nhấp vào tùy chọn này nếu đó là điều bạn muốn xảy ra. Luôn cân nhắc kỹ lưỡng trước khi xóa dữ liệu vĩnh viễn.
Bây giờ, trong hộp thoại Replace Errors, nhập giá trị bạn muốn xuất hiện thay vì các lỗi. Trong trường hợp này, vì các lỗi xuất hiện khi ô ghi “Present”, bạn có thể nhập năm hiện tại.
Nhập "2025" vào hộp thoại "Replace Errors" trong Power Query Editor Excel để thay thế lỗi
Khi bạn nhấp “OK”, tất cả các giá trị lỗi sẽ được thay thế bằng giá trị mới này.
Cột "Last played" với các giá trị lỗi đã được thay thế bằng "2025" trong Power Query Editor
Vì tất cả các ô trong hai cột bên phải đều chứa cùng loại dữ liệu mà không có lỗi, bạn có thể tạo một cột mới để tính tổng số năm thi đấu của mỗi cầu thủ. Điều này cho phép bạn tạo ra các thông tin chi tiết hữu ích từ dữ liệu đã được làm sạch.
Ở đây, chúng tôi đã nhấp “Custom Column” (Cột tùy chỉnh) trong tab Add Column (Thêm cột), đổi tên cột thành “Years played” (Số năm thi đấu) và sử dụng danh sách các cột trong menu Available Columns (Cột khả dụng) để tạo một phép trừ đơn giản.
Thêm cột tùy chỉnh "Years played" với công thức trừ đơn giản trong Power Query Editor
Sau đó, khi tôi nhấp “OK”, cột mới này—không có lỗi—đã được thêm vào phía bên phải của truy vấn.
Cột tính toán "Years played" mới đã được thêm vào bảng dữ liệu trong Power Query Editor
Khi bạn hoàn tất, nhấp vào biểu tượng “Close And Load” (Đóng và Tải) trong tab Home (Trang chủ) của Power Query Editor để gửi bảng đến một trang tính mới trong sổ làm việc Excel của bạn.
Nút "Close And Load" trên tab Home của Power Query Editor
3. Chuyển đổi dữ liệu (Unpivot) để hỗ trợ phân tích
Khi tạo một tập dữ liệu mới trong Microsoft Excel, nếu có thể, bạn nên tuân thủ nguyên tắc bản ghi-trường (record-field principle), trong đó:
- Mỗi hàng chứa một tập hợp các loại dữ liệu liên quan nhưng khác nhau (còn được gọi là bản ghi).
- Mỗi cột chứa một loại dữ liệu duy nhất (còn được gọi là trường) liên quan đến mỗi bản ghi.
Trong ví dụ đơn giản này, mỗi quốc gia là một bản ghi, và các châu lục, dân số và tiền tệ của chúng là các trường.
Bảng dữ liệu Excel về các quốc gia tuân thủ nguyên tắc ghi chép trường bản ghi
Việc sử dụng định dạng này sẽ chuẩn bị dữ liệu để phân tích thêm, vì bạn có thể dễ dàng lọc và sắp xếp dữ liệu, tham chiếu tiêu đề cột trong các công thức và trực quan hóa thống kê.
So sánh bảng trong ví dụ trên với ảnh chụp màn hình sau, trong đó mỗi cột (trường) chứa các loại dữ liệu giống hệt nhau.
Bảng dữ liệu Excel với các cột đại diện cho từng năm, cần chuyển đổi Unpivot
Kết quả là, không thể lọc dữ liệu theo năm và bạn không thể dễ dàng xem năm nào có lợi nhuận cao nhất cho mỗi công ty. Để thực hiện các phân tích này, bạn sẽ cần unpivot (hoặc làm phẳng) dữ liệu, thao tác này liên quan đến việc chuyển đổi dữ liệu từ bảng “rộng” thành bảng “dài” bằng cách biến mỗi cột năm thành một hàng. Nói cách khác, bạn muốn tất cả các giá trị tài chính được lưu trữ dưới dạng một trường duy nhất.
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu và trong tab Data (Dữ liệu) trên thanh công cụ, nhấp “From Table/Range” (Từ Bảng/Phạm vi).
Nút "From Table/Range" trong tab Data của Excel để nhập dữ liệu vào Power Query
Tại thời điểm này, nếu dữ liệu của bạn chưa được định dạng dưới dạng bảng Excel, bạn sẽ thấy một hộp thoại nhắc bạn khắc phục điều này. Điều này giúp Power Query Editor đọc dữ liệu của bạn dễ dàng hơn.
Sau đó, trong Power Query Editor, chọn tiêu đề của tất cả các cột bạn muốn unpivot. Trong ví dụ này, bạn sẽ cần nhấp vào tiêu đề “2020”, nhấn Shift và sau đó nhấp vào tiêu đề “2024”.
Chọn nhiều cột năm trong Power Query Editor của Excel để thực hiện Unpivot
Bây giờ, trong tab Transform (Chuyển đổi) trên thanh công cụ, mở rộng menu thả xuống “Unpivot Columns” (Bỏ xoay cột) và nhấp “Unpivot Only Selected Columns” (Bỏ xoay chỉ các cột đã chọn).
Tùy chọn "Unpivot Only Selected Columns" được chọn trong Power Query Editor của Excel
Bây giờ, mỗi cột là một trường riêng biệt (công ty, năm và lợi nhuận), và mỗi hàng là một bản ghi chi tiết của công ty.
Bảng dữ liệu đã được chuyển đổi Unpivot trong Power Query Editor của Excel
Cuối cùng, nhấp đúp vào tiêu đề cột để đổi tên chúng sao cho phù hợp với trường mà chúng đại diện, và nhấp vào biểu tượng định dạng số để đảm bảo mỗi cột chứa đúng loại dữ liệu.
Đổi tên và định dạng lại các cột đã Unpivot trong Power Query Editor của Excel
Bây giờ, khi bạn nhấp “Close And Load” (Đóng và Tải) trong tab Home (Trang chủ) trên thanh công cụ, bảng đã được unpivot sẽ được gửi đến một trang tính mới trong sổ làm việc Excel của bạn.
Bảng dữ liệu đã được chuyển đổi Unpivot hiển thị trong Excel
Nhờ vậy, bạn có thể thực hiện các phân tích dữ liệu mà trước đây không thể làm được trước khi làm phẳng bảng, như lọc dữ liệu theo năm và công ty hoặc sắp xếp lợi nhuận của công ty theo thứ tự giảm dần.
Sau khi thêm nhiều dữ liệu hơn vào bảng gốc, như thêm số liệu của một năm khác, hãy chuyển đến bảng đã được Power Query tạo ra trước đó và trong tab Query (Truy vấn), nhấp “Refresh” (Làm mới). Excel sau đó sẽ thêm dữ liệu mới vào bảng kết quả ở định dạng đã được unpivot, giúp bạn không phải quay lại Power Query Editor mỗi lần.
4. Điền các ô trống dựa trên ô phía trên (hoặc phía dưới)
Như đã đề cập trước đó, các ô trống trong tập dữ liệu có thể dẫn đến các vấn đề khi bạn sắp xếp và lọc dữ liệu hoặc sử dụng các công thức tham chiếu tiêu đề cột. Vì vậy, việc điền chúng là một ý tưởng hay để đảm bảo dữ liệu luôn được tổ chức mạch lạc.
Trong ví dụ này, chỉ cầu thủ đầu tiên trong mỗi đội được gán số đội ở cột A, vì vậy nếu bạn sắp xếp lại dữ liệu, bạn sẽ không biết cầu thủ nào thuộc đội nào. Ngoài ra, hiện tại, bạn không thể lọc dữ liệu theo số đội.
Cột số đội trong Excel chỉ được điền một phần, cần được điền đầy đủ
Lý tưởng nhất là các ô A3 đến A5 nên chứa số 1, các ô A7 đến A9 nên chứa số 2, v.v. Việc nhập thủ công các số này sẽ tốn thời gian, đặc biệt nếu, như trong ví dụ này, có nhiều hàng trong tập dữ liệu. Thay vào đó, hãy sử dụng Power Query để điền các ô trống trong vài giây.
Đầu tiên, với bất kỳ ô nào trong dữ liệu đã chọn, trong tab Data (Dữ liệu) trên thanh công cụ, nhấp “From Table/Range” (Từ Bảng/Phạm vi).
Chọn một ô trong bảng dữ liệu và nhấp "From Table/Range" trong tab Data của Excel
Khi Power Query Editor mở ra, hãy chú ý cách các ô trống trong cột được đề cập chứa từ “null”. Để khắc phục điều này, hãy chọn cột bằng cách nhấp vào tiêu đề cột và trong tab Transform (Chuyển đổi), nhấp “Fill” (Điền).
Menu thả xuống "Fill" trong tab Transform của Power Query Editor
Sau đó, chọn xem bạn muốn điền dữ liệu xuống (Down) hay lên (Up). Nhấp “Down” sẽ tìm một ô trong phạm vi đã chọn có chứa giá trị và điền bất kỳ ô trống nào bên dưới bằng cùng giá trị đó. Mặt khác, nhấp “Up” sẽ điền các ô trống phía trên một ô chứa giá trị. Trong trường hợp này, vì chỉ hàng đầu tiên của mỗi đội chứa một số, bạn cần điền xuống dưới.
Cột số đội đã được điền đầy đủ cho mỗi cầu thủ trong Power Query Editor, sẵn sàng để xuất ra Excel
Bây giờ, mỗi cầu thủ đã được gán số đội một cách chính xác, vì vậy bạn có thể nhấp “Close And Load” (Đóng và Tải) trong tab Home để gửi truy vấn đến một trang tính mới.
Bảng Excel đã được làm sạch với số đội, tên cầu thủ và điểm số được điền đầy đủ
Nhờ thay đổi nhanh chóng nhưng quan trọng này, bạn có thể sắp xếp và lọc tập dữ liệu một cách an toàn, biết rằng bạn sẽ không bị mất dấu cầu thủ nào thuộc đội nào.
Nếu bạn có nhiều bảng trong các trang tính Excel riêng biệt, miễn là chúng có cùng tiêu đề cột, bạn có thể sử dụng Power Query để ghép nối dữ liệu thành một bảng duy nhất. Điều này liên quan đến một bước bổ sung (nhưng đơn giản) là tạo kết nối dữ liệu giữa mỗi bảng, trước khi kết hợp các truy vấn trong Power Query Editor.
Power Query là một công cụ mạnh mẽ giúp đơn giản hóa các tác vụ xử lý dữ liệu phức tạp trong Excel. Bằng cách áp dụng các lệnh cơ bản như tách cột, thay thế lỗi, chuyển đổi dữ liệu (unpivot) và điền ô trống, bạn có thể biến các tập dữ liệu lộn xộn thành thông tin có giá trị, dễ dàng phân tích và sử dụng. Những kỹ năng này không chỉ tiết kiệm thời gian mà còn nâng cao độ chính xác và hiệu quả công việc của bạn với dữ liệu.
Bạn có câu hỏi nào về Power Query Excel hoặc muốn chia sẻ những thủ thuật yêu thích của mình không? Hãy để lại bình luận bên dưới để chúng ta cùng thảo luận nhé! Đừng quên theo dõi tienichthuthuat.com để cập nhật thêm nhiều mẹo và hướng dẫn công nghệ hữu ích khác.
