Cách chuyển đổi cột thành hàng trong Excel bằng Power Query

Trong hướng dẫn này của Bachkhoatrithuc.vn, bạn sẽ tìm hiểu cách sử dụng Power Query để chuyển đổi cột thành hàng trong Excel. Một trong những bước dọn dẹp dữ liệu phổ biến nhất là lấy dữ liệu trải rộng trên các cột và chuyển đổi dữ liệu đó thành các hàng dữ liệu. Nâng kỹ năng Excel của bạn lên một tầm cao mới bằng cách “bỏ xoay vòng” dữ liệu của bạn bằng Microsoft Power Query. 

Tại sao chuyển đổi cột thành hàng?

Trước khi chúng tôi bắt đầu, bạn có thể thắc mắc tại sao việc lấy dữ liệu có cấu trúc trong nhiều cột và chuyển đổi thành hàng lại hữu ích đến vậy.

Câu trả lời đó là một chút kỹ thuật. Về cơ bản, sẽ tốt hơn nếu mỗi hàng dữ liệu giống như một Record, một điểm dữ liệu duy nhất. Dữ liệu cột được trộn lẫn với nhiều thuộc tính trải rộng trên các cột.

Trong ví dụ bên dưới, bạn có thể thấy dữ liệu trông như thế nào khi bạn chuyển đổi nó từ cột thành hàng trong Excel. Ở phía bên trái là tập hợp các bản ghi khách hàng, với các loại dự án khác nhau trong các cột. Ở phía bên phải, tôi đã bỏ xoay dữ liệu và chuyển đổi dữ liệu đó sang định dạng hàng.

Ngoài ra, nếu bạn là người dùng PivotTable, điều rất quan trọng là dữ liệu của bạn phải ở dạng hàng thay vì cột khi bạn sử dụng nó để tạo PivotTable. Dữ liệu trong cột đơn giản là không cung cấp chính xác cho Bảng Pivot.

Tóm lại: chuyển đổi dữ liệu từ cột thành hàng giúp làm việc dễ dàng hơn. Power Query làm cho thao tác này trở nên dễ dàng với hai lần bấm.

Làm quen với Microsoft Power Query dành cho Excel

Power Query được gọi chính thức là Get and Transform Data (Nhận và chuyển đổi dữ liệu) trong Excel 2016, là công cụ có 2 chức năng chính:

1. Bạn có thể sử dụng nó để lấy dữ liệu, chẳng hạn như trỏ nó tới các tệp Excel, cơ sở dữ liệu và thậm chí cả các trang web để kéo dữ liệu xuống và đưa vào bảng tính.
2. Nó có thể giúp bạn chuyển đổi dữ liệu, có nghĩa là sắp xếp lại nó một cách nhanh chóng, thêm nhiều cột hơn hoặc hợp nhất các bộ dữ liệu khác với nó.

Khi bạn gửi dữ liệu tới Power Query để làm việc, nó sẽ mở ra trong một cửa sổ mới ở đầu Excel, nơi bạn có thể áp dụng các chuyển đổi dữ liệu.Power Query là một công cụ chuyên sâu và mạnh mẽ nằm bên trong Excel. Thay vì sắp xếp lại dữ liệu theo cách thủ công và lặp lại quy trình, Power Query cho phép bạn xây dựng một tập hợp các bước mà bạn có thể áp dụng nhiều lần cho dữ liệu của mình.

Cài đặt Power Query

Power Query được tích hợp sẵn trong Excel 2016 nhưng bạn có thể cài đặt nó cho các phiên bản Excel cũ hơn dưới dạng phần bổ trợ. Rất tiếc, Power Query chỉ khả dụng cho Excel trên Windows.

Để cài đặt Power Query cho Excel trên Windows, (chỉ cần cho Excel 2013 cho Excel 2010, vì tính năng này được tích hợp sẵn trong Excel 2016), hãy truy cập trang web của Microsoft để tải xuống và cài đặt phần bổ trợ Power Query.

Gửi dữ liệu tới Power Query

Power Query được sử dụng bên trong Excel, nhưng nó sẽ mở ra trong một cửa sổ mới nằm ở đầu Excel.

Để bỏ xoay dữ liệu trong Excel, trước tiên bạn cần chuyển đổi dữ liệu Excel của mình thành một cái bảng nếu nó chưa có ở định dạng đó. Bạn có thể chuyển giao bảng dữ liệu cho Power Query để làm việc với dữ liệu của mình.

Chuyển đổi sang bảng

Để chuyển đổi dữ liệu thành một bảng, hãy bấm vào bất kỳ đâu bên trong bảng rồi tìm tùy chọn Format as Table trên Ribbon của Excel. Bạn có thể nhấp vào bất kỳ hình thu nhỏ kiểu nào để chuyển đổi các ô phẳng của mình thành một bảng dữ liệu.

Chuyển giao cho Power Query

Khi dữ liệu của bạn ở định dạng bảng, hãy chuyển đến Tab Data trên Ribbon của Excel và nhấp vào nút From Table để gửi dữ liệu bảng tới Power Query để chuyển đổi dữ liệu của bạn.

Điều này sẽ gửi dữ liệu vào một cửa sổ mới mở ra bên trong, nhưng ở trên cùng của Microsoft Excel. Đó là một cửa sổ hoàn toàn mới với rất nhiều tính năng.

Bỏ xoay dữ liệu

Tại thời điểm này, bạn sẽ thấy một cái gì đó tương tự như ảnh chụp màn hình bên dưới. Đây là cửa sổ chứa các công cụ bạn cần để hủy xoay.

Để bỏ xoay dữ liệu trong Excel, hãy tô sáng tất cả các cột mà bạn muốn bỏ xoay. Về cơ bản, Power Query sẽ chuyển đổi từng cột này thành các hàng của riêng chúng. Đối với dữ liệu mẫu ở đây, tôi đã đánh dấu từng cột cho các loại dự án:

Khi bạn đã nhấp vào Unpivot Columns (Bỏ xoay cột), Excel sẽ chuyển đổi dữ liệu cột của bạn thành hàng. Mỗi hàng là một bản ghi của riêng nó, sẵn sàng để đưa vào Bảng Pivot hoặc làm việc với trong biểu dữ liệu của bạn.

Tính năng này cảm thấy như ma thuật. Bây giờ, hãy gửi dữ liệu trở lại Microsoft Excel để làm việc.

Đóng và Tải (Close & Load)

Cửa sổ Power Query có nhiều tính năng mà bạn có thể đi sâu vào với các hướng dẫn khác, nhưng hiện tại, chúng ta đã hoàn tất việc hủy xoay vòng dữ liệu của mình. Hãy tiếp tục và nhấp vào Close & Load để gửi dữ liệu trở lại Excel.

Close & Load gửi dữ liệu trở lại Excel và đưa dữ liệu đó vào bảng tính. Bạn cũng sẽ thấy một cửa sổ truy vấn ở phía bên phải hiển thị truy vấn mà chúng ta vừa tạo để hủy tổng hợp dữ liệu.

Làm mới Query (Truy vấn)

Vì vậy, nếu dữ liệu gốc của bạn thay đổi thì sao? Nếu bạn có nhiều hàng hơn được thêm vào dữ liệu gốc, thì bạn không phải lặp lại toàn bộ quá trình này để bỏ xoay các hàng đó.

Thay vì tạo lại truy vấn từ đầu, bạn cần sử dụng Tùy chọn Refresh Data. Trong ảnh chụp màn hình bên dưới, tôi sẽ thêm một hàng hoàn toàn mới cho một khách hàng hoàn toàn mới vào danh sách của mình nguyên nguồn dữ liệu:

Bây giờ, hãy quay lại tab nơi dữ liệu chưa được tổng hợp của chúng ta được tải. Khi chúng tôi nhấp vào bên trong bảng, bạn sẽ thấy menu Workbook Queries mở bên cạnh dữ liệu của bạn. Nhấn nút Refresh button (bên phải của menu) để làm mới truy vấn.

Hành động này nhìn lại bảng ban đầu và chạy lại các bước truy vấn. Bởi vì chúng tôi đã thêm các hàng mới vào bảng dữ liệu gốc của mình, Power Query sẽ thêm các hàng mới này vào truy vấn và chạy chúng thông qua chuyển đổi không xoay vòng.

Đây là một trong những tính năng yêu thích của tôi về Power Query. Chúng tôi đã thêm dữ liệu vào nguồn ban đầu, nhưng một cú nhấp chuột đã làm mới nguồn đó và hủy tổng hợp dữ liệu mới.