Bạn đã từng nghe đến Power Query trong Microsoft Excel nhưng luôn nghĩ rằng công cụ này chỉ dành cho những chuyên gia Excel? Thực tế, Power Query được thiết kế để thân thiện với người dùng và quan trọng hơn cả là giúp bạn tiết kiệm rất nhiều thời gian, công sức khi tổ chức và làm sạch dữ liệu. Đây chính là một trong những tính năng mạnh mẽ nhất của Excel mà bất kỳ ai làm việc với dữ liệu cũng nên biết.
Bài viết này dành cho những độc giả mới làm quen với Microsoft Excel hoặc Power Query. Các thủ thuật dưới đâ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ữ M nào của Power Query. Chúng sẽ cung cấp một đ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ỳ hữu ích trong công cụ thao tác dữ liệu mạnh mẽ này, giúp bạn biến dữ liệu thô thành thông tin có giá trị một cách dễ dàng.
1. Tách Cột Bằng Dấu Phân Cách Để Cô Lập 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à linh hoạt nhất chính là mở Power Query Editor và thực hiện thao tác tại đó.
Hãy hình dung bạn tìm thấy một bảng trên Wikipedia mà bạn muốn biến đổi bằng Power Query. Để nhập bảng này, đầu tiên, bạn click vào “Get Data” (Lấy dữ liệu) trong tab Home trên thanh ribbon, sau đó chọn From Other Sources (Từ các nguồn khác) > From Web (Từ Web).
Tùy chọn Nhập Dữ Liệu Từ Web trong menu Get Data của Excel, minh họa bước đầu tiên để sử dụng Power Query.
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 cần thiết ở ngăn bên trái của Navigator và click “Transform Data” (Chuyển đổi dữ liệu). Thao tác này sẽ đưa bạn đến Power Query Editor để bắt đầu quá trình xử lý dữ liệu.
Bảng dữ liệu đã chọn trong hộp thoại Navigator của Excel, với tùy chọn Transform Data được nhấn mạnh để chuyển vào Power Query Editor.
Khi bảng đượ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 mảnh dữ liệu – năm bắt đầu sự nghiệp của mỗi cầu thủ và năm cuối cùng họ thi đấu. Bạn muốn tách hai giá trị này thành các cột riêng biệt để dễ dàng phân tích hơn.
Cột "Career Span" trong Power Query Editor của Excel chứa dữ liệu cần tách, minh họa vấn đề dữ liệu ban đầu.
Để thực hiện điều này, bạn click 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).
Menu chuột phải trong Power Query Editor với tùy chọn "Split Column" (Tách cột) theo dấu phân cách được chọn, hướng dẫn cách tách dữ liệu trong Excel.
Một 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 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 một dấu gạch ngang (-) và tự động nhận định đâ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.
Hộp thoại Split Column by Delimiter trong Power Query Editor, hiển thị dấu gạch ngang được tự động nhận diện làm dấu phân cách để tách cột.
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 hãy click “OK” và xem dữ liệu mới đã được biến đổi trong Power Query Editor.
Power Query Editor của Excel sau khi tách cột "Career Span" thành hai cột "First Played" và "Last Played", hiển thị dữ liệu đã được phân chia.
Để đổi tên các cột mới cho dễ hiểu, bạn chỉ cần click đúp vào tiêu đề cột và nhập nhãn dữ liệu mới, ví dụ “First played” (Năm bắt đầu) và “Last played” (Năm kết thúc).
Đổi tên tiêu đề cột trong Power Query Editor của Excel, một bước quan trọng để làm sạch và tổ chức 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ó nhận thấy 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ố (được chỉ định 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 biểu tượng ABC trong tiêu đề cột). Để có thể tính toán chính xác, bạn cần đồng nhất định dạng dữ liệu.
Để khắc phục điều này, chọn biểu tượng “ABC” trong cột “Last played” và click “Whole Number” (Số nguyên). Thao tác này sẽ đảm bảo tất cả các giá trị trong cột được hiểu là số.
Tùy chọn định dạng dữ liệu "Whole Number" (Số Nguyên) được chọn cho một cột trong Power Query Editor, giúp chuẩn hóa kiểu dữ liệu.
Giờ đây, cả hai cột đều được định dạng là 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 (như “Present”), những ô này giờ đây sẽ 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 tình trạng này một cách hiệu quả!
2. Thay Thế Lỗi Để Dữ Liệu Có Thể 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, đả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 cho thấy sự không tương thích giữa kiểu dữ liệu.
Cột "Last Played" trong Power Query Editor hiển thị các giá trị lỗi sau khi chuyển đổi kiểu dữ liệu, minh họa vấn đề cần khắc phục.
Hiện tại, nếu bạn click “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ị bỏ trống trong bảng kết quả. Mặc dù điều này có vẻ hợp lý, như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 các tiêu đề cột trong 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 để duy trì tính nhất quán của dữ liệu.
Để làm điều này, click chuột phải vào tiêu đề cột và chọn “Replace Errors” (Thay thế lỗi).
Menu chuột phải của một cột trong Power Query Editor của Excel với tùy chọn "Replace Errors" (Thay thế Lỗi) được chọn, giải pháp xử lý lỗi hiệu quả.
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, tùy chọn này sẽ xóa toàn bộ hàng chứ không chỉ riêng lỗi, vì vậy chỉ click tùy chọn này nếu đó là điều bạn muốn xảy ra. Trong đa số trường hợp, thay thế lỗi là lựa chọn an toàn hơ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 ở nơi ô có chữ “Present”, 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.
Nhập giá trị '2025' vào hộp thoại Replace Errors trong Power Query Editor để thay thế các giá trị lỗi, giúp chuẩn hóa dữ liệu.
Khi bạn click “OK”, tất cả các giá trị lỗi sẽ được thay thế bằng giá trị mới này, giúp dữ liệu trở nên sạch sẽ và sẵn sàng cho các phép tính.
Cột "Last played" trong Power Query Editor sau khi các giá trị lỗi được thay thế bằng năm '2025', hiển thị dữ liệu đã được làm sạch.
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ó 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 của mỗi cầu thủ. Điều này cho phép bạn có cái nhìn sâu sắc hơn về dữ liệu.
Ở đây, tôi đã click “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ác cột có sẵn) để tạo một phép trừ đơn giản: [Last played] - [First played].
Tạo một cột tùy chỉnh "Years played" trong Power Query Editor để tính toán số năm hoạt động của cầu thủ, minh họa khả năng tính toán của Power Query.
Sau đó, khi tôi click “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. Giờ đây, bạn có một trường dữ liệu hữu ích để phân tích hiệu suất và kinh nghiệm của các cầu thủ.
Cột tính toán "Years played" đã được thêm vào bảng trong Power Query Editor, thể hiện kết quả sau khi áp dụng các phép tính.
Khi bạn hoàn tất, click biểu tượng “Close And Load” (Đóng và Tải) trong tab Home của Power Query Editor để gửi bảng đến một bảng tính mới trong workbook Excel của bạn.
Biểu tượng "Close And Load" trong Power Query Editor của Excel, hoàn tất quá trình xử lý dữ liệu và tải kết quả về bảng tính.
3. Chuyển Đổi Định Dạng Dữ Liệu (Unpivot Data) Để Phân Tích Hiệu Quả
Khi tạo một tập dữ liệu mới trong Microsoft Excel, nếu có thể, tôi thường 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 loại dữ liệu liên quan nhưng khác nhau (còn gọi là bản ghi), và
- Mỗi cột chứa một loại dữ liệu duy nhất (còn 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à 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í dụ về các quốc gia, châu lục và dân số, minh họa nguyên tắc "record-field" cho dữ liệu chuẩn.
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 thống kê một cách hiệu quả.
Hãy 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 (ở đây là các năm). Đây là một định dạng dữ liệu “rộng” (wide format), thường không tối ưu cho phân tích.
Bảng dữ liệu doanh thu công ty theo năm trong Excel, minh họa định dạng dữ liệu "rộng" cần được chuyển đổi (unpivot).
Kết quả là, việc lọc dữ liệu theo năm trở nên khó khăn, và bạn không thể dễ dàng xem năm nào mang lại 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 vùng dữ liệu, và trong tab Data (Dữ liệu) trên thanh ribbon, click “From Table/Range” (Từ Bảng/Phạm vi).
Nút "From Table/Range" trong tab Data của Excel, dùng để nhập dữ liệu vào Power Query Editor để bắt đầu quá trình unpivot.
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 nhở bạn khắc phục điều này. Việc này nhằm 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 click tiêu đề “2020”, nhấn Shift và sau đó click tiêu đề “2024” để chọn toàn bộ các cột năm.
Các cột năm được chọn trong Power Query Editor của Excel, chuẩn bị cho thao tác unpivot để chuyển đổi dữ liệu.
Bây giờ, trong tab Transform (Chuyển đổi) trên thanh ribbon, mở rộng menu thả xuống “Unpivot Columns” (Hủy xoay cột) và click “Unpivot Only Selected Columns” (Chỉ hủy xoay các cột đã chọn).
Tùy chọn "Unpivot Only Selected Columns" trong tab Transform của Power Query Editor, minh họa cách chuyển đổi dữ liệu từ cột sang hàng.
Ngay lập tức, bạn sẽ thấy dữ liệu của mình được chuyển đổi. Mỗi cột ban đầu giờ đây là một trường riêng biệt (ví dụ: 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, tối ưu hóa cho việc phân tích.
Bảng dữ liệu đã được unpivot trong Power Query Editor, hiển thị cấu trúc dữ liệu tối ưu cho việc phân tích.
Cuối cùng, click đú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ụ: “Year” (Năm) và “Profit” (Lợi nhuận)), và click các biểu tượng định dạng số để đảm bảo mỗi cột chứa loại dữ liệu chính xác.
Các cột trong Power Query Editor sau khi được đổi tên và định dạng lại, đảm bảo dữ liệu chuẩn bị sẵn sàng cho phân tích.
Bây giờ, khi bạn click “Close And Load” trong tab Home trên thanh ribbon, bảng đã được unpivot sẽ được gửi đến một bảng tính mới trong workbook 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 và lọc.
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 không thể, như lọc dữ liệu theo năm và công ty hoặc sắp xếp lợi nhuận công ty theo thứ tự giảm dần. Hơn nữa, sau khi thêm dữ liệu mới vào bảng gốc, như số liệu của một năm khác, bạn chỉ cần quay lại bảng được tạo bởi Power Query trước đó, và trong tab Query, click “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 đã unpivot, giúp bạn không phải quay lại Power Query Editor mỗi lần, tiết kiệm thời gian đáng kể.
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 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 tính nhất quán và khả năng sử dụng của dữ liệu.
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. Do đó, 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. Ngoài ra, hiện tại, bạn không thể lọc dữ liệu theo số đội một cách hiệu quả.
Cột số đội trong Excel chỉ được điền một phần, minh họa vấn đề dữ liệu cần được xử lý bằng Power Query.
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 chỉ trong vài giây.
Đầu tiên, với bất kỳ ô nào trong vùng dữ liệu được chọn, trong tab Data trên thanh ribbon, click “From Table/Range”.
Ô trong bảng dữ liệu Excel được chọn, và nút "From Table/Range" trong tab Data được làm nổi bật, bước đầu để sử dụng Power Query.
Khi Power Query Editor mở ra, hãy lưu ý cách các ô trống trong cột đang xét chứa từ “null”. Để khắc phục điều này, chọn cột bằng cách click tiêu đề cột, và trong tab Transform, click “Fill” (Điền).
Menu thả xuống "Fill" trong Power Query Editor của Excel đang mở, hiển thị các tùy chọn để điền ô trống trong cột.
Sau đó, chọn xem bạn muốn điền dữ liệu theo chiều xuống (“Down”) hay chiều lên (“Up”). Click “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ị đó. Ngược lại, click “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.
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ể click “Close And Load” trong tab Home để gửi truy vấn đến một bảng tính mới.
Cột số đội trong Power Query Editor đã được điền đầy đủ cho mỗi cầu thủ, và nút "Close And Load" được chọn, hoàn tất việc làm sạch dữ liệu.
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, không lo mất dấu vết cầu thủ nào thuộc đội nào. Điều này cải thiện đáng kể khả năng phân tích và quản lý dữ liệu của bạn.
Bảng Excel cuối cùng với số đội, tên cầu thủ và điểm số, minh họa dữ liệu đã được làm sạch và tổ chức hoàn chỉnh bằng Power Query.
Nếu bạn có nhiều bảng trong các bảng 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 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.
Kết luận
Qua bài viết này, tincongngheso.com hy vọng bạn đã nắm bắt được 4 thủ thuật Power Query Excel cơ bản nhưng vô cùng mạnh mẽ, giúp bạn đơn giản hóa quá trình xử lý và làm sạch dữ liệu. Từ việc tách cột bằng dấu phân cách, thay thế các giá trị lỗi, chuyển đổi dữ liệu từ dạng rộng sang dạng dài (unpivot), cho đến việc tự động điền các ô trống, Power Query chứng minh mình là một công cụ không thể thiếu cho bất kỳ ai làm việc với lượng lớn dữ liệu trong Excel.
Việc làm chủ những kỹ năng này không chỉ giúp bạn tiết kiệm thời gian mà còn nâng cao độ chính xác và khả năng phân tích của các báo cáo. Hãy bắt đầu áp dụng Power Query vào công việc hàng ngày của bạn để biến những tác vụ Excel phức tạp trở nên dễ dàng và hiệu quả hơn.
Bạn có bất kỳ thủ thuật Power Query yêu thích nào khác hoặc câu hỏi nào về các tính năng này không? Hãy chia sẻ ý kiến của bạn trong phần bình luận bên dưới và cùng tincongngheso.com khám phá thêm sức mạnh của Excel!
