Hoán vị dòng và cột (Transpose) trong Excel

Nếu bạn đang có một vùng dữ liệu trên Excel và bạn muốn hoán vị – chuyển đổi dữ liệu hàng thành cột hoặc ngược lại thì bạn sẽ làm gì? Xóa đi nhập lại là cách mất nhiều thời gian của bạn.

Bài viết dưới đây sẽ hướng dẫn cho bạn thủ thuật hoán vị (Transpose) dữ liệu bằng các công cụ Paste Specials, hàm TRANSPOSE và công cụ Power Query cùng với ưu điểm và hạn chế của mỗi công cụ.

1. Hoán vị dữ liệu bằng Paste Special

Giả sử có cơ sở dữ liệu như dưới đây:

Dữ liệu này có thông tin về các Khu vực ở phần cột và các Quý trong năm ở phần hàng.

Các bược hoán đổi vị trí dòng và cột như sau:

Bước 1: Chọn vùng dữ liệu (A1:E5).

Bước 2: Bấm tổ hợp phím Ctrl + C hoặc chuột phải và chọn Copy

 

Bước 3: Dán dữ liệu cần được hoán vị tại vị trí một mới, sau đó ấn chuột phải và chọn Paste Special.

Bước 4: Trong hộp thoại Paste Special, chọn Transpose dưới góc phải và ấn OK.

Kết quả:

Lưu ý: Hoán vị bằng Paste Special nhanh và đơn giản, tuy nhiên dữ liệu được hoán vị sẽ được Paste Value và không thay đổi nếu dữ liệu gốc thay đổi.

2. Hoán vị dữ liệu trong Excel bằng hàm TRANSPOSE

Hàm TRANSPOSE là một công thức mảng dùng để hoán đổi vị trí dòng và cột

Bước 1: Chọn những ô mà chứa dữ liệu được hoán vị. Lưu ý rằng cần chọn chính xác số ô như dữ liệu gốc. Như ví dụ trên, ta có 5 cột và 5 dòng, vì vậy hãy chọn một vùng chứa 5 cột và 5 dòng.

 

Bước 2: Nhập hàm =TRANSPOSE(A1:E5) và ấn tổ hợp phím Control + Shift + Enter. Sau đó dữ liệu sẽ được hoán vị.

 

Lưu ý: Hàm TRANSPOSE sao chép giá trị của ô vì vậy dữ liệu được hoán vị sẽ thay đổi nếu dữ liệu gốc thay đổi. Một số hạn chế của hàm TRANSPOSE là không thể xóa đi một phần kết quả mà phải xóa toàn bộ kết quả trả về. Và khi có thêm dữ liệu (VD: thêm khu vực Africa thì hàm TRANSPOSE không tự động cập nhật dữ liệu mới được thêm vào).

 

3. Hoán vị dữ liệu trong Excel bằng công cụ Power Query

Được đánh giá là công cụ tốt nhất của Microsoft Excel trong 5 năm gần đây, Power Query giúp quá trình xử lý dữ liệu trở nên dễ dàng hơn nhờ ứng dụng Business Intelligence và tạo ra một số câu lệnh query cực kỳ tối ưu cho người dùng.

Đối với Excel 2016, Power Query có sẵn trong Excel dưới tên là Get & Transform trong tab Data

Các bước thực hiện Transpose bằng Power query như sau:

Bước 1: Chuyển dữ liệu gốc thành dạng Table

Chọn vùng dữ liệu, tab Home, Format as Table, hoặc nhấn phím tắt Ctrl + T và OK

Bước 2: Chọn 1 ô bất kỳ trong Table đã tạo, tab Data, From Table

Bước 3:

Trong giao diện Power Query Editor, tab Transform, Transpose

Tab Transform, Transpose, Use First Row as Headers

Chọn Tab Home, Close & Load

Kết quả:

Nếu có dữ liệu mới được thêm vào dữ liệu gốc như hình dưới

Cập nhật dữ liệu mới bằng cách nhấn chuột phải chọn Refresh, các thao tác Transpose trong Power Query sẽ được thực hiện.

Lưu ý: thao tác Transpose trong Power Query chỉ cần thực hiện 1 lần và khi dữ liệu thay đổi hoặc thêm dòng/cột mới, chỉ cần Refresh để cập nhật dữ liệu mới nhất.

Để lại một bình luận

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

1 + 1 = ? (Nhập Haii để trả lời đúng)

Bài viết liên quan
Focus Cell trong Excel – Tính năng giúp tăng khả năng điều hướng

Đã bao giờ bạn lạc trôi giữa hàng trăm dòng, hàng ngàn cột trên Excel… mà không biết mình đang ở đâu? Nếu “mất phương hướng” là cảm giác quen thuộc

Xem thêm
Trí tuệ nhân tạo AI là gì? Khái niệm, phân loại và vai trò trong cuộc sống

Trong thời đại cách mạng công nghiệp 4.0, trí tuệ nhân tạo AI đang trở thành một phần không thể thiếu trong nhiều lĩnh vực của cuộc sống. Từ các trợ

Xem thêm
Truy vấn lồng (Subquery) trong SQL – Bí kíp giúp xử lý và truy xuất dữ liệu với mức độ phức tạp cao

Truy vấn lồng (Subquery) là một trong những công cụ hiệu quả trong SQL, giúp xử lý và truy xuất dữ liệu với mức độ phức tạp cao hơn so với

Xem thêm
Hàm TRIMRANGE() – Hàm xóa giá trị trống “đỉnh” hơn cả TRIM.

Như bạn đã biết hàm TRIM() trong Excel giúp loại bỏ các ký tự trống (khoảng trắng). Tương tự vậy hàm TRIMRANGE() được sử dụng để xóa các giá trị trống khỏi một phạm vi

Xem thêm