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 là giúp bạn tiết kiệm rất nhiều công sức khi tổ chức và làm sạch dữ liệu. Trong kỷ nguyên công nghệ số, việc xử lý dữ liệu hiệu quả là chìa khóa để đưa ra các quyết định sáng suốt, và Power Query chính là công cụ mạnh mẽ hỗ trợ bạn điều này.
Power Query là một công cụ tích hợp sẵn trong Excel, cho phép bạn kết nối, biến đổi và kết hợp dữ liệu từ nhiều nguồn khác nhau một cách dễ dàng. Dù bạn là người mới bắt đầu hay đã có kinh nghiệm với Excel, những thủ thuật Power Query mà trithuccongnghe.net giới thiệu dưới đây sẽ là điểm khởi đầu tuyệt vời để bạn làm quen với các lệnh cơ bản và tận dụng sức mạnh của nó. Bài viết này sẽ hướng dẫn bạn từng bước thực hiện các thao tác quan trọng mà không yêu cầu kiến thức về ngôn ngữ Power Query M phức tạp, giúp bạn nhanh chóng trở thành người chủ động trong việc quản lý dữ liệu.
1. Tách Cột Bằng Ký Tự Phân Cách để Cô Lập Giá Trị
Trong công việc hàng ngày, chúng ta thường xuyên đối mặt với các bảng dữ liệu mà thông tin bị gộp chung trong một cột duy nhất, khiến việc phân tích trở nên khó khăn. Ví dụ, bạn có một cột chứa cả tên và họ, hoặc ngày bắt đầu và ngày kết thúc. Excel cung cấp 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à hiệu quả nhất, đặc biệt với dữ liệu lớn hoặc cần xử lý lặp lại, là sử dụng Trình chỉnh sửa Power Query (Power Query Editor).
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, đầu 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 Ribbon, sau đó chọn From Other Sources (Từ các nguồn khác) > From Web (Từ Web).
Tùy chọn "From Web" trong menu "Get Data" của Excel để nhập dữ liệu từ trang web bằ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 trong khung bên trái của Navigator (Điều hướng) và nhấp “Transform Data” (Chuyển đổi dữ liệu). Thao tác này sẽ đưa bạn đến giao diện của Power Query Editor, nơi bạn có thể thực hiện các bước làm sạch và chuyển đổi dữ liệu mạnh mẽ.
Chọn bảng dữ liệu trong hộp thoại Navigator và nhấp "Transform Data" để mở Power Query Editor trong Excel.
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 phần dữ liệu—nă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 để dễ dàng phân tích hơn. Đây là một trường hợp điển hình mà tính năng tách cột của Power Query trở nên vô cùng hữu ích.
Cột "Career Span" (Thời gian sự nghiệp) trong giao diện Power Query Editor của Excel trước khi tách.
Để thực hiện điều này, bạn nhấp chuột phải vào tiêu đề cột, di chuyển chuột qua “Split Column” (Tách cột) và chọn “By Delimiter” (Theo ký tự phân cách). Một ký tự 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, dấu gạch ngang (-) đang làm nhiệm vụ phân tách hai năm.
Chọn tùy chọn "Split Column" (Tách cột) và "By Delimiter" (Theo ký tự phân cách) trong Power Query Editor.
Tại thời điểm này, Power Query Editor sẽ tự động xem xét dữ liệu trong cột để phát hiện ký tự 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à giả định chính xác rằng đây là điểm mà ô nên được tách. Tuy nhiên, bạn có thể chọn một ký tự phân cách khác từ menu thả xuống đầu tiên nếu cần thiết, ví dụ như dấu phẩy, dấu chấm phẩy hoặc khoảng trắng.
Hộp thoại "Split Column by Delimiter" trong Power Query Editor, hiển thị dấu gạch ngang (-) được chọn làm ký tự phân cách.
Vì chỉ có một ký tự 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 thêm bất kỳ thay đổi nào đối với các tùy chọn trong hộp thoại. Bạn chỉ cần nhấp “OK” và xem dữ liệu mới đã được chuyển đổi trong Power Query Editor.
Dữ liệu trong Power Query Editor sau khi tách cột "Career Span" thành hai cột "First played" và "Last played".
Để đổi tên các cột mới cho rõ ràng hơn, bạn chỉ cần nhấp đú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 để làm rõ nội dung dữ liệu sau khi tách.
Bây giờ 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” đề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 coi đây là một cột văn bản (do đó là biểu tượng 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 “Whole Number” (Số nguyên). Thao tác này sẽ đảm bảo rằng tất cả dữ liệu trong cột đều được hiểu là số, tạo tiền đề cho các phép tính sau này.
Chọn định dạng dữ liệu "Whole Number" (Số nguyên) cho cột trong Power Query Editor.
Hiện tại, 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 các giá trị văn bản (ví dụ: “Present” – Hiện tại), 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 một cách dễ dàng!
Hai cột "First played" và "Last played" đã được định dạng kiểu dữ liệu số trong Power Query Editor.
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. Việc có các ô lỗi có thể cản trở quá trình phân tích, sắp xếp hoặc sử dụng công thức, làm giảm độ tin cậy của 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, bở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.
Các ô báo lỗi xuất hiện trong cột "Last played" của Power Query Editor sau khi thay đổi định dạng dữ liệu.
Hiện tại, nếu bạn nhấp vào “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ẽ 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 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, đảm bảo tính toàn vẹn của dữ liệu.
Để làm điều này, bạn 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 trên tiêu đề cột trong Power Query Editor, chọn "Replace Errors" (Thay thế lỗi).
Bạn có thể muốn 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, không chỉ riêng ô lỗi. Vì vậy, bạn chỉ nên nhấp vào tùy chọn này nếu đó là điều bạn muốn xảy ra, trong trường hợp bạn chấp nhận mất dữ liệu của cả một bản ghi. Để giữ lại các bản ghi và chỉ sửa lỗi, “Replace Errors” là lựa chọn tốt 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" để thay thế các giá trị lỗi trong Power Query Editor.
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. Dữ liệu của bạn giờ đây đã 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 tất cả 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 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 minh chứng cho lợi ích của việc làm sạch dữ liệu: tạo ra những thông tin mới có giá trị.
Ở đây, 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ác cột có sẵn) để tạo một phép trừ đơn giản (Cột “Last played” – Cột “First played”).
Tạo cột tùy chỉnh "Years played" (Số năm thi đấu) bằng phép trừ giữa hai cột trong Power Query Editor.
Sau đó, khi tôi 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. Giờ đây, bạn có một trường dữ liệu hoàn toàn mới, được tính toán tự động và chính xác, mở ra nhiều khả năng phân tích hơ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” trong tab Home 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.
Biểu tượng "Close And Load" (Đóng và Tải) trên tab Home trong Power Query Editor để xuất dữ liệu đã xử lý về Excel.
3. Chuyển Dạng 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, bất cứ khi nào 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 loại dữ liệu liên quan nhưng khác nhau (còn được gọi là bản ghi – record), và
- Mỗi cột chứa một loại dữ liệu duy nhất (còn được gọi là trường – field) 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ố, tiền tệ của chúng là các trường. Cấu trúc này làm cho dữ liệu dễ đọc, dễ sắp xếp và dễ phân tích hơn rất nhiều.
Ví dụ bảng dữ liệu trong Excel tuân thủ nguyên tắc record-field: mỗi quốc gia là một bản ghi, thông tin là 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 tiêu đề cột trong công thức và trực quan hóa các số liệu 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, nhưng lại đại diện cho các năm khác nhau. Đây là một cấu trúc dữ liệu “rộng” (wide format) không tối ưu cho phân tích.
Bảng dữ liệu "rộng" trong Excel với các cột là các năm, gây khó khăn cho việc phân tích.
Kết quả là, không thể lọc dữ liệu theo năm một cách hiệu quả, và bạn cũng 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 những phân tích này, bạn cần chuyển dạng dữ liệu (unpivot hay làm phẳng dữ liệu), bao gồm 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 vùng dữ liệu, và trong tab Data (Dữ liệu) trên thanh Ribbon, nhấp “From Table/Range” (Từ Bảng/Phạm vi).
Nút "From Table/Range" (Từ Bảng/Phạm vi) trong tab Data của Excel để nhập dữ liệu vào Power Query Editor.
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. Lý do là Power Query Editor có thể đọc dữ liệu trong định dạng bảng dễ dàng hơn nhiều, giúp quá trình xử lý diễn ra suôn sẻ.
Sau đó, trong Power Query Editor, chọn tiêu đề của tất cả các cột bạn muốn chuyển dạng. Trong ví dụ này, bạn cần nhấp vào tiêu đề “2020”, giữ phím Shift và sau đó nhấp vào tiêu đề “2024” để chọn toàn bộ các cột năm.
Chọn nhiều cột chứa dữ liệu năm (2020-2024) trong Power Query Editor để thực hiện thao tác Unpivot.
Bây giờ, trong tab Transform (Chuyển đổi) trên thanh Ribbon, mở rộng menu thả xuống “Unpivot Columns” (Chuyển dạng cột) và nhấp “Unpivot Only Selected Columns” (Chỉ chuyển dạng các cột đã chọn).
Chọn "Unpivot Only Selected Columns" (Chỉ unpivot các cột đã chọn) trong menu Unpivot Columns trên tab Transform.
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. Dữ liệu đã được “làm phẳng” và tổ chức lại theo cách tối ưu cho phân tích.
Bảng dữ liệu đã được unpivot trong Power Query Editor, chuyển đổi các cột năm thành các hàng "Attribute" và "Value".
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” và “Value” thành “Profit”), và nhấp vào 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.
Đổi tên các cột "Attribute" thành "Year" và "Value" thành "Profit", sau đó định dạng lại kiểu dữ liệu trong Power Query Editor.
Bây giờ, khi bạn nhấp “Close And Load” (Đóng và Tải) trong tab Home trên thanh Ribbon, bảng đã chuyển dạng 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 unpivot và tải về một trang tính mới trong Microsoft Excel, sẵn sàng để phân tích.
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ể thực hiện được khi bảng còn ở dạng “rộ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ông ty theo thứ tự giảm dần. Một điểm cộng lớn là sau khi thêm nhiều dữ liệu hơn vào bảng gốc (ví dụ: thêm số liệu của một năm khác), bạn chỉ cần quay lại bảng do 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 đã chuyển dạng, 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 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 các ô trống này một cách hợp lý là một bước quan trọng trong quá trình làm sạch và chuẩn hóa dữ liệu.
Trong ví dụ này, chỉ có cầu thủ đầu tiên trong mỗi đội được gán số đội trong 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. Hơn nữa, 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 "Team Number" (Số đội) trong Excel chỉ điền một phần dữ liệu, gây khó khăn khi sắp xếp và lọc.
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 đó, hãy sử dụng Power Query để điền các ô trống trong vài giây, tự động hóa một tác vụ tẻ nhạt.
Đầ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 Ribbon, 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" trên tab Data để mở Power Query Editor.
Khi Power Query Editor mở ra, hãy chú ý 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 nhấp vào tiêu đề cột, và trong tab Transform (Chuyển đổi), nhấp “Fill” (Điền).
Mở menu thả xuống "Fill" (Điền) trên tab Transform trong Power Query Editor để điền các ô trống.
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 tất cả các ô trống bên dưới bằng cùng một giá trị đó. Ngược lại, nhấp “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ỉ có hàng đầu tiên của mỗi đội chứa một số, bạn cần điền xuống.
Bằng cách chọn “Fill Down”, Power Query sẽ tự động sao chép giá trị số đội từ hàng đầu tiên xuống tất cả các hàng trống bên dưới, cho đến khi nó gặp một giá trị mới, và quá trình này lặp lại cho toàn bộ cột.
Bây giờ, mỗi cầu thủ đã được gán số đội một cách chính xác, 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.
Cột "Team Number" trong Power Query Editor sau khi điền đầy đủ dữ liệu bằng lệnh "Fill Down", sẵn sàng để tải về Excel.
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 bao giờ mất dấu cầu thủ nào thuộc đội nào. Điều này nâng cao đáng kể khả năng sử dụng và phân tích dữ liệu của bạn trong Excel.
Bảng Excel hiển thị số đội, tên cầu thủ và điểm số đã được chuẩn hóa sau khi xử lý bằng Power Query.
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 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 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 là một công cụ biến đổi dữ liệu vô cùng mạnh mẽ trong Microsoft Excel, không chỉ dành riêng cho các chuyên gia mà còn cực kỳ hữu ích cho người mới bắt đầu. Bốn thủ thuật cơ bản mà trithuccongnghe.net đã giới thiệu—tách cột, thay thế lỗi, chuyển dạng dữ liệu (unpivot) và điền ô trống—là những kỹ năng nền tảng giúp bạn làm sạch, chuẩn hóa và tối ưu hóa dữ liệu một cách hiệu quả, tiết kiệm thời gian và công sức.
Với Power Query, bạn có thể biến những tập dữ liệu phức tạp, lộn xộn thành thông tin có giá trị, dễ dàng phân tích và đưa ra quyết định chính xác hơn. Việc thành thạo các kỹ năng này không chỉ nâng cao hiệu suất làm việc của bạn mà còn mở ra cánh cửa đến với khả năng phân tích dữ liệu chuyên sâu hơn. Đừng ngần ngại khám phá thêm các tính năng khác của Power Query để tối đa hóa tiềm năng của dữ liệu trong công việc hàng ngày của bạn. Hãy bắt đầu áp dụng ngay hôm nay để trải nghiệm sự khác biệt mà Power Query mang lại!