Bạn đã từng nghe 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ĩ đó, vì 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, giúp bạn tiết kiệm rất nhiều công sức khi tổ chức và xử lý dữ liệu.
Đối với người dùng mới làm quen với Microsoft Excel hoặc Power Query, hướng dẫn này sẽ là điểm khởi đầu lý tưởng. Các mẹo trong bài 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, mà chỉ tập trung vào việc giới thiệu 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. Đây là những kỹ năng thiết yếu giúp bạn nâng cao hiệu quả làm việc với dữ liệu, làm cho việc xử lý dữ liệu Excel trở nên nhanh chóng và ít lỗi hơn.
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 sẵn. Tuy nhiên, phương pháp trực quan nhất là khởi động Power Query Editor và thực hiện thao tác tại đó. Công cụ này đặc biệt hữu ích khi bạn cần làm sạch dữ liệu từ các nguồn khác nhau.
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, trước tiên, bạn nhấp vào “Get Data” trên tab Home của ribbon, sau đó chọn From Other Sources > From Web.
Tùy chọn "From Web" trong menu "Get Data" của Excel, cho phép nhập dữ liệu từ các nguồn web.
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 và nhấp “Transform Data”. Bước này đưa bạn đến môi trường của Power Query Editor, nơi bạn có thể tinh chỉnh dữ liệu.
Bảng dữ liệu được chọn trong hộp thoại Navigator của Excel, với nút "Transform Data" được nhấn để mở Power Query Editor.
Khi bảng được tải vào Power Query Editor, bạn nhận thấy cột “Career Span” 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 trường hợp phổ biến khi bạn cần tách cột Excel để chuẩn hóa dữ liệu.
Cột "Career Span" trong Power Query Editor của Excel, hiển thị dữ liệu khoảng thời gian sự nghiệp cần được tách.
Để thực hiện điều này, nhấp chuột phải vào tiêu đề cột, di chuột qua “Split Column” và chọn “By Delimiter”.
Tùy chọn "By Delimiter" được chọn trong menu "Split Column" của Power Query Editor, chuẩn bị tách dữ liệu.
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.
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 trường hợp 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 dấu phân cách khác từ menu thả xuống đầu tiên nếu cần. Khả năng tự động nhận diện này là một trong những điểm mạnh giúp tối ưu dữ liệu Excel với Power Query.
Dấu gạch ngang (-) được tự động nhận diện và chọn làm dấu phân cách trong hộp thoại "Split Column by Delimiter" của Power Query.
Vì chỉ có một dấu phân cách trong mỗi ô của cột được 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, vì vậy hãy nhấp “OK” và xem dữ liệu mới được chuyển đổi trong Power Query Editor.
Hai cột mới "Career Span.1" và "Career Span.2" sau khi tách thành công trong Power Query Editor, hiển thị năm bắt đầu và kết thúc sự nghiệp.
Để đổi tên các cột mới, nhấp đúp vào tiêu đề cột và nhập nhãn dữ liệu mới. Đây là một bước quan trọng để làm cho dữ liệu của bạn dễ hiểu hơn.
Một tiêu đề cột đang được đổi tên thành "First played" trong Power Query Editor của Excel.
Bây giờ, bạn đã tách dữ liệu thành công bằng cách sử dụ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à ABC trong tiêu đề cột). Việc định dạng dữ liệu Excel đúng cách là rất quan trọng để tránh các lỗi tính toán sau này.
Để 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”.
Tùy chọn "Whole Number" được chọn trong menu định dạng kiểu dữ liệu của một cột trong Power Query Editor, chuyển đổi dữ liệu thành số nguyên.
Bây giờ, cả hai cột đều được định dạng là tập dữ liệu số.
Hai cột "First played" và "Last played" trong Power Query Editor đã được định dạng thành kiểu dữ liệu số nguyên, hiển thị biểu tượng 123.
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! Xử lý lỗi dữ liệu là một phần không thể thiếu trong quá trình làm sạch dữ liệu.
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.
Tiếp tục từ 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” trong một cột được định dạng là số nguyên.
Cột "Last played" trong Power Query Editor hiển thị các lỗi (Error) do chuyển đổi kiểu dữ liệu không phù hợp.
Hiện tại, nếu bạn nhấp “Close And Load” ở 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 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ông thức, vì vậy tốt nhất là điền chúng bằng các giá trị có ý nghĩa. Điều này cải thiện độ tin cậy của dữ liệu Excel và khả năng phân tích.
Để 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”.
Menu chuột phải của cột trong Power Query Editor mở rộng, với tùy chọn "Replace Errors" được chọn để thay thế các giá trị lỗi.
Bạn có thể bị cám dỗ để chọn “Remove Errors” 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ỉ 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.
Bây giờ, trong hộp thoại Replace Errors, nhập giá trị bạn muốn xuất hiện thay cho lỗi. Trong trường hợp này, vì các lỗi xuất hiện khi ô đọc “Present”, bạn có thể nhập năm hiện tại (ví dụ: 2025). Việc điền giá trị lỗi là một kỹ thuật quan trọng để làm sạch dữ liệu.
Giá trị "2025" được nhập vào hộp thoại "Replace Errors" trong Power Query Editor để thay thế các giá trị 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" 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 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 cho mỗi cầu thủ. Đây là một ví dụ tuyệt vời về cách Power Query giúp tính toán dữ liệu hiệu quả.
Tại đây, tôi đã nhấp “Custom Column” trong tab Add Column, đổi tên cột thành “Years played” 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.
Một cột tùy chỉnh "Years played" đang được thêm vào Power Query Editor bằng công thức trừ đơn giản để tính số năm hoạt động.
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 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 Power Query Editor, hiển thị kết quả số năm hoạt động.
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 bảng tính mới trong sổ làm việc Excel của bạn. Đây là bước cuối cùng để tải dữ liệu đã xử lý về Excel.
Biểu tượng "Close And Load" trong Power Query Editor, dùng để tải dữ liệu đã biến đổi về Excel.
3. Chuyển Đổi Dữ Liệu (Unpivot Data) Để 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 tuân theo 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 gọi là bản ghi), và
- Mỗi cột chứa một loại dữ liệu duy nhất, riêng biệ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 họ là các trường. Việc quản lý dữ liệu Excel theo nguyên tắc này giúp tối ưu hóa cho phân tích.
Bảng dữ liệu mẫu trong Excel tuân thủ nguyên tắc record-field, hiển thị thông tin về các quốc gia.
Sử dụng định dạng này 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 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.
Bảng dữ liệu "rộng" trong Excel với các cột đại diện cho từng năm, khó khăn trong phân tích.
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 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 chuyển đổi dữ liệu (unpivot) (hoặc làm phẳng) dữ liệu, bao gồm việc chuyển đổi dữ liệu từ một bảng “rộng” thành một 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. Đây là một kỹ thuật mạnh mẽ để phân tích dữ liệu trong Excel.
Đầu tiên, chọn bất kỳ ô nào trong dữ liệu và trong tab Data trên ribbon, nhấp “From Table/Range”.
Nút "From Table/Range" trong tab Data của Excel, được sử dụng để 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 là một 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 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 tiêu đề của tất cả các cột bạn muốn chuyển đổi (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”.
Nhiều cột năm được chọn trong Power Query Editor để thực hiện thao tác Unpivot.
Bây giờ, trong tab Transform trên ribbon, mở rộng menu thả xuống “Unpivot Columns” và nhấp “Unpivot Only Selected Columns”. Đây là cách để chuyển đổi dữ liệu từ cột sang hàng một cách hiệu quả.
Tùy chọn "Unpivot Only Selected Columns" được chọn trong Power Query Editor để chuyển đổi các cột đã chọn thành hàng.
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, với các cột "Company", "Attribute" (năm) và "Value" (lợi nhuận).
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.
Các cột trong Power Query Editor đã được đổi tên thành "Year" và "Profit" và định dạng lại kiểu dữ liệu để phân tích.
Bây giờ, khi bạn nhấp “Close And Load” trong tab Home trên ribbon, 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 và tải về Microsoft Excel, sẵn sàng cho các phân tích chi tiết.
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ể thực hiện được khi bảng chưa được làm phẳ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ông ty theo thứ tự giảm dần. Power Query thực sự là một công cụ giúp tối ưu hóa phân tích dữ liệu.
Sau khi thêm nhiều dữ liệu hơn vào bảng gốc, như số liệu của một năm khác, hãy chuyển đến bảng đã được Power Query tạo trước đó và trong tab Query, nhấp “Refresh”. 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 Ô Trống Dựa Trên Ô Phía Trên (Hoặc Phía Dưới)
Như tôi đã đề cập trước đó, các ô trống trong một bộ 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. Đây là một bước quan trọng trong việc làm sạch dữ liệu Excel.
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, 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, gây khó khăn khi sắp xếp và lọc dữ liệu.
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 gõ các số này theo cách thủ công sẽ tốn thời gian, đặc biệt nếu, như trong ví dụ này, có nhiều hàng trong bộ dữ liệu. Thay vào đó, hãy sử dụng Power Query để điền ô trống trong Excel 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 ribbon, nhấp “From Table/Range”.
Một ô 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 để chuyển dữ liệu vào Power Query.
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, chọn cột bằng cách nhấp vào tiêu đề cột, và trong tab Transform, nhấp “Fill”.
Menu thả xuống "Fill" trong Power Query Editor của Excel, hiển thị các tùy chọn "Down" và "Up" để điền giá trị vào ô trống.
Sau đó, chọn xem bạn muốn điền dữ liệu xuống (downward) hay lên (upward). Nhấp “Down” sẽ tìm một ô trong phạm vi được chọn 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, 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. Tính năng “Fill Down” trong Power Query là một giải pháp hoàn hảo để tự động điền dữ liệu.
Bây giờ, mỗi cầu thủ được gán đúng số đội, vì vậy bạn có thể nhấp “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 sau khi đã được điền đầy đủ bằng tính năng "Fill Down", sẵn sàng để "Close And Load".
Nhờ thay đổi nhanh chóng nhưng quan trọng này, bạn có thể sắp xếp và lọc bộ dữ liệu, an toàn với kiến thức rằng bạn sẽ không bị mất dấu vết của cầu thủ nào thuộc đội nào. Điều này nâng cao khả năng quản lý dữ liệu Excel của bạn.
Bảng Excel cuối cùng với cột số đội đã được điền đầy đủ, giúp dễ dàng sắp xếp và lọc dữ liệu.
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 để kết hợp dữ liệu vào một bảng duy nhất. Điều này liên quan đến 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 trong Excel không chỉ là một công cụ dành cho chuyên gia mà là một trợ thủ đắc lực cho bất kỳ ai muốn xử lý dữ liệu Excel một cách hiệu quả. Từ việc tách cột, thay thế lỗi, đến chuyển đổi cấu trúc dữ liệu và điền ô trống, Power Query giúp tự động hóa các tác vụ phức tạp, tiết kiệm thời gian và nâng cao độ chính xác của dữ liệu.
Những lệnh cơ bản được giới thiệu trong bài viết này chỉ là phần nổi của tảng băng chìm về khả năng của Power Query. Nắm vững chúng sẽ giúp bạn tự tin hơn khi đối mặt với những bộ dữ liệu lớn và phức tạp. Hãy bắt đầu áp dụng những thủ thuật này vào công việc hàng ngày của bạn để trải nghiệm sự khác biệt mà Power Query mang lại.
Bạn đã sẵn sàng khám phá thêm những tính năng mạnh mẽ khác của Power Query chưa? Hãy chia sẻ kinh nghiệm và những mẹo yêu thích của bạn với chúng tôi trong phần bình luận bên dưới, hoặc đọc thêm các bài viết chuyên sâu về tối ưu dữ liệu Excel trên thoibaocongnghe.net!
