Bạn đã từng nghe về Power Query trong Microsoft Excel nhưng luôn nghĩ rằng đây chỉ là công cụ dành cho các chuyên gia Excel? Hãy tạm gác suy nghĩ đó lại, bởi thực tế, Power Query được thiết kế để cực kỳ thân thiện với người dùng và quan trọng nhất, nó giúp bạn tiết kiệm vô số thời gian và công sức trong việc tổ chức, làm sạch và chuẩn bị dữ liệu.
Trong thế giới của dữ liệu số, việc thu thập thông tin chỉ là bước khởi đầu. Thử thách thực sự nằm ở việc chuyển đổi dữ liệu thô, lộn xộn thành định dạng có cấu trúc, dễ dàng phân tích. Đây chính là lúc Power Query phát huy sức mạnh. Nó không chỉ là một công cụ giúp bạn tự động hóa các tác vụ lặp đi lặp lại mà còn là trợ thủ đắc lực giúp nâng cao chất lượng và độ tin cậy của dữ liệu. Bài viết này được biên soạn dành riêng cho những độc giả mới làm quen với Microsoft Excel hoặc Power Query. Các thủ thuật được trình bày không yêu cầu bạn phải biết hay sử dụng bất kỳ công thức ngôn ngữ Power Query M nào, và chúng sẽ là điểm khởi đầu tuyệt vời để bạn làm quen với một số lệnh cơ bản nhưng cực kỳ hiệu quả trong công cụ thao tác dữ liệu mạnh mẽ này.
1. Tách Cột Theo Dấu Phân Cách Để Phân Tách Giá Trị
Trong Excel, có nhiều cách để tách dữ liệu thành nhiều cột, bao gồm công cụ Text To Columns, Flash Fill và các hàm tích hợp. Tuy nhiên, phương pháp trực quan và mạnh mẽ nhất là khởi chạy Power Query Editor và thực hiện thao tác tại đó. Ưu điểm của Power Query là nó tạo ra một quy trình có thể tái sử dụng, giúp bạn tiết kiệm thời gian đáng kể khi phải xử lý dữ liệu tương tự trong tương lai.
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 cần nhấp vào “Get Data” (Lấy 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). Thao tác này sẽ giúp bạn dễ dàng đưa dữ liệu từ internet vào môi trường làm việc của Power Query mà không cần sao chép thủ công, giảm thiểu sai sót.
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 sẽ chọn bảng mong muốn trong ngăn bên trái của Navigator, rồi nhấp vào “Transform Data” (Chuyển đổi dữ liệu). Việc này sẽ đưa dữ liệu vào môi trường Power Query Editor, nơi bạn có thể thực hiện các thao tác làm sạch và biến đổi chuyên sâ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 dữ liệu được tải vào Power Query Editor, bạn nhận thấy cột “Career Span” (Thời gian sự nghiệp) chứa hai phần dữ liệu—năm bắt đầu sự nghiệp và năm cuối cùng cầu thủ thi đấu—và bạn muốn tách chúng thành hai cột riêng biệt. Việc này thường rất khó khăn nếu làm thủ công, nhưng với Power Query sẽ trở nên vô cùng đơn giản.
Để thực hiện, bạn chỉ cần nhấp chuột phải vào tiêu đề cột “Career Span”, di chuyển chuột đến “Split Column” (Tách cột), và chọn “By Delimiter” (Theo dấu phân cách).
Tùy chọn "Split Column" (Tách cột) theo dấu phân cách được chọn trong Power Query Editor của Excel.
Một dấu phân cách (delimiter) 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, đó chính là dấu gạch ngang giữa hai năm.
Tại thời điểm này, Power Query Editor sẽ xem xét dữ liệu trong cột để phát hiện dấu phân cách tiềm năng. Trong ví dụ này, nó đã xác định rằng mỗi ô chứa dấu gạch ngang và giả định chính xác rằng đây là điểm mà ô nên được tách. Tuy nhiên, bạn hoàn toà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 tự động chọn làm dấu phân cách trong hộp thoại "Split Column by Delimiter" của 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 nữa đối với các tùy chọn trong hộp thoại, vì vậy chỉ cần nhấp “OK” và xem dữ liệu mới được chuyển đổi trong Power Query Editor.
Để đổi tên các cột mới, bạn chỉ cần nhấp đúp vào tiêu đề cột và nhập tên mới cho dữ liệu. Ví dụ, đổi tên thành “First played” (Năm bắt đầu) và “Last played” (Năm kết thúc).
Đổi tên tiêu đề cột mới trong Power Query Editor của Excel sau khi tách dữ liệu.
Bây giờ, bạn đã tách dữ liệu thành công bằng Power Query Editor. Tuy nhiên, bạn có để ý 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” đều là số (như được chỉ ra bởi 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” bao gồm cả văn bản và số, Excel xử lý đây là một cột văn bản (do đó là biểu tượng ABC trong tiêu đề cột). Điều này có thể gây khó khăn cho việc phân tích số liệu sau này.
Để khắc phục, hãy chọn biểu tượng “ABC” trong cột “Last played” và nhấp vào “Whole Number” (Số nguyên). Thao tác này sẽ chuyển đổi kiểu dữ liệu của cột thành số, giúp việc xử lý tính toán dễ dàng hơn.
Hai cột "First played" và "Last played" sau khi định dạng lại thành kiểu số trong Power Query Editor của Excel.
Bây giờ, cả hai cột đều được định dạng dưới dạng tập dữ liệu số. Tuy nhiên, vì một số ô trong cột “Last played” trước đây chứa giá trị văn bản (ví dụ: “Present”), những ô này giờ đây hiển thị dưới dạng lỗi. Đừ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ý bất kỳ lỗi nào xuất hiện trong dữ liệu của bạn, đảm bảo tính toàn vẹn và khả năng sử dụng của tập dữ liệu.
Tiếp tục với truy vấn từ ví dụ trước, một số ô trong cột “Last played” chứa lỗi vì chúng trước đây chứa giá trị văn bản “Present” trong một cột được định dạng là số nguyên. Điều này xảy ra khi bạn thay đổi kiểu dữ liệu mà không xử lý các giá trị không tương thích.
Hiện tại, nếu bạn nhấp vào “Close And Load” (Đóng và Tải) ở góc trên cùng 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, nhưng các ô trống trong 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 các tiêu đề cột trong công thức. Do đó, tốt nhất là điền chúng bằng các giá trị có ý nghĩa để duy trì tính nhất quán của dữ liệu.
Để thực hiện đ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). Lưu ý quan trọng là bạn nên chọn “Replace Errors” chứ không phải “Remove Errors” (Xóa lỗi) trong menu chuột phải. Tùy chọn “Remove Errors” sẽ xóa toàn bộ hàng chứ không chỉ riêng lỗi, điều này có thể dẫn đến mất dữ liệu không mong muốn. Chỉ sử dụng “Remove Errors” nếu bạn thực sự muốn xóa toàn bộ hàng chứa lỗi.
Menu chuột phải của cột trong Power Query Editor hiển thị tùy chọn "Replace Errors" để xử lý lỗi dữ liệu.
Bây giờ, trong hộp thoại Replace Errors, hãy nhập giá trị bạn muốn xuất hiện thay vì lỗi. Trong trường hợp này, vì các lỗi xuất hiện ở nơi ô đọc “Present” (Hiện tại), bạn có thể nhập năm hiện tại (ví dụ: 2025) để chỉ ra rằng cầu thủ đó vẫn đang thi đấu.
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. Điều này giúp dữ liệu của bạn trở nên gọn gàng, có thể tính toán được và sẵn sàng cho các phân tích tiếp theo.
Cột "Last played" trong Power Query Editor sau khi các lỗi đã được thay thế bằng giá trị "2025".
Vì tất cả các ô trong hai cột bên phải đều chứa cùng một kiểu dữ liệu mà không có bất kỳ lỗi nào, bạn có thể tạo một cột mới để tính tổng số năm thi đấu cho mỗi cầu thủ. Đây là một ví dụ điển hình về cách Power Query giúp bạn biến đổi dữ liệu thô thành thông tin có ý nghĩa.
Để làm điều này, bạn nhấp vào “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 để tạo một phép trừ đơn giản (ví dụ: [Last played] - [First played]).
Thêm cột tùy chỉnh "Years played" với phép trừ đơn giản giữa hai cột trong Power Query Editor.
Sau đó, khi bạn nhấp “OK”, cột mới này—không có bất kỳ lỗi nào—được thêm vào bên phải của truy vấn. Khi đã hoàn tất các bước làm sạch và chuyển đổi, bạn nhấp vào biểu tượng “Close And Load” trong tab Home của Power Query Editor để gửi bảng đến một bảng tính mới trong sổ làm việc Excel của bạn.
Biểu tượng "Close And Load" trong Power Query Editor để tải dữ liệu đã chuyển đổi về bảng tính Excel.
3. Unpivot Dữ Liệu Để Hỗ Trợ Phân Tích
Khi tạo một bộ dữ liệu mới trong Microsoft Excel, nếu có thể, tôi luôn tuân thủ nguyên tắc record-field (bản ghi-trường), trong đó:
- Mỗi hàng chứa một tập hợp các kiểu 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 kiểu 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à lục địa, dân số, và tiền tệ của chúng là các trường.
Việc sử dụng định dạng này giúp chuẩn bị dữ liệu cho việc phân tích sâu hơn, vì bạn có thể dễ dàng lọc và sắp xếp dữ liệu, tham chiếu các tiêu đề cột trong công thức và trực quan hóa các số liệu thống kê.
Hãy so sánh bảng trong ví dụ trên với ảnh chụp màn hình sau, nơi mỗi cột (trường) chứa các kiểu dữ liệu giống hệt nhau nhưng lại là các biến số khác nhau.
Ví dụ bảng dữ liệu rộng trong Excel với các cột đại diện cho từng năm tài chính của công ty.
Kết quả là, không thể lọc dữ liệu theo năm, và bạn cũng không thể dễ dàng xem năm nào là năm 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, nghĩa là 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 (ví dụ: trường “Lợi nhuận”).
Đầ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 vào “From Table/Range” (Từ Bảng/Phạm vi). 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 một bảng Excel, bạn sẽ thấy một hộp thoại nhắc bạn sửa lỗi này. Điều này là để Power Query Editor có thể đọc dữ liệu của bạn dễ dàng hơn.
Sau đó, trong Power Query Editor, chọn các 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, sau đó nhấp vào tiêu đề “2024” để chọn toàn bộ dải cột năm.
Chọn các cột dữ liệu theo năm (ví dụ: 2020-2024) trong Power Query Editor để 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” (Hủy trục các cột) và nhấp vào “Unpivot Only Selected Columns” (Hủy trục chỉ các cột đã chọn). Lệnh này sẽ biến các cột đã chọn thành các hàng mới, tạo ra một cấu trúc dữ liệu “dài” và linh hoạt hơn cho phân tích.
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 công ty chi tiết.
Bảng dữ liệu đã được "unpivot" trong Power Query Editor, chuyển đổi từ dạng rộng sang dạng dài để dễ phân tích.
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í dụ: “Attribute” thành “Year”, “Value” thành “Profit”), và nhấp vào các biểu tượng định dạng số để đảm bảo mỗi cột chứa đúng kiểu dữ liệu.
Khi bạn nhấp “Close And Load” trong tab Home trên thanh công cụ, bảng đã được unpivot sẽ được gửi đến một bảng tính mới trong sổ làm việc Excel của bạn.
Bảng dữ liệu đã được unpivot hoàn chỉnh trong Microsoft Excel, sẵn sàng cho việc phân tích chuyên sâu.
Kết quả là, bạn có thể thực hiện các phân tích dữ liệu mà trước đây là không thể khi bảng còn ở dạng rộng, chẳng hạn 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. Một điểm cộng lớn của Power Query là sau khi bạn thêm dữ liệu mới vào bảng gốc (ví dụ: thêm số liệu của một năm nữa), bạn chỉ cần quay lại bảng đã được tạo bởi Power Query trước đó, và trong tab Query (Truy vấn), nhấp vào “Refresh” (Làm mới). Excel sau đó sẽ tự động 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 Ô 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 một 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. Do đó, việc điền các ô trống này một cách hợp lý là một thực hành tốt.
Trong ví dụ này, chỉ cầu thủ đầu tiên trong mỗi đội được gán số đội trong cột A. Điều này có nghĩa là nếu bạn sắp xếp lại dữ liệu, bạn sẽ mất thông tin về việc cầu thủ nào thuộc đội nào. Hơn nữa, hiện tại, bạn không thể lọc dữ liệu theo số đội.
Lý tưởng nhất, 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ẽ rất 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 đó, bạn có thể sử dụng Power Query để điền các ô trống chỉ trong vài giây.
Đầu tiên, với bất kỳ ô nào trong dữ liệu đã được chọn, trong tab Data trên thanh công cụ, nhấp vào “From Table/Range”.
Chọn một ô bất kỳ trong bảng dữ liệu Excel và nhấp vào "From Table/Range" trên tab Data để mở Power Query Editor.
Khi Power Query Editor mở ra, bạn sẽ nhận thấy các ô trống trong cột đang xét 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, nhấp vào “Fill” (Điền).
Sau đó, chọn xem bạn muốn điền dữ liệu xuống (Down) hay lên (Up). Nhấp vào “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 một giá trị đó. Ngược lại, nhấp vào “Up” sẽ điền các ô trống phía trên một ô có 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.
Bây giờ, mỗi cầu thủ đều được gán số đội một cách chính xác, vì vậy bạn có thể nhấp vào “Close And Load” trong tab Home để gửi truy vấn đến một bảng tính mới.
Cột "Team Number" sau khi sử dụng tính năng "Fill Down" trong Power Query Editor, đã điền đầy đủ số đội cho từng cầu thủ.
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 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 để gộp dữ liệu vào một bảng duy nhất. Điều này bao gồm bước bổ sung (nhưng đơn giản) là tạo các 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.
Kết luận
Power Query không chỉ là một tính năng bổ sung trong Excel; đó là một công cụ biến đổi giúp bạn tiết kiệm thời gian, giảm thiểu lỗi và nâng cao khả năng phân tích dữ liệu của mình. Từ việc tách các chuỗi phức tạp, xử lý và thay thế lỗi, đến việc chuẩn hóa cấu trúc dữ liệu với thao tác Unpivot và tự động điền các ô trống, Power Query mang lại sức mạnh xử lý dữ liệu chuyên nghiệp đến tay người dùng phổ thông.
Với những thủ thuật cơ bản này, bạn đã có thể bắt đầu hành trình làm chủ Power Query, biến dữ liệu thô thành thông tin có giá trị chỉ với vài cú nhấp chuột, mà không cần đến những công thức phức tạp hay kiến thức lập trình. Hãy tự mình trải nghiệm và khám phá thêm vô vàn khả năng mà Power Query mang lại cho công việc của bạn. Chia sẻ ý kiến của bạn về những thủ thuật này và cách chúng đã giúp ích cho bạn trong việc quản lý dữ liệu Excel nhé!
