Hướng dẫn hoán vị Excel: Đổi hàng thành cột và ngược lại

Hoán vị dữ liệu trong Excel cho phép bạn đảo hướng dữ liệu, biến hàng thành cột và cột thành hàng. Kỹ thuật này đặc biệt hữu ích khi bạn cần cấu trúc lại tập dữ liệu để phân tích, trực quan hóa hoặc lập báo cáo tốt hơn.

Dù bạn đang chuẩn bị dữ liệu cho PivotTable, tạo một chế độ xem tóm tắt hay đơn giản là cần thay đổi bố cục dữ liệu, Excel cung cấp ba phương pháp chính để hoán vị dữ liệu:

1. Dán Đặc biệt (Paste Special) - Chuyển đổi nhanh một lần

2. TRANSPOSE() - Cách tiếp cận bằng công thức động

3. Power Query - Phương pháp nâng cao cho tập dữ liệu lớn

Trong bài viết này, bạn sẽ học từng bước cách dùng mỗi phương pháp, khi nào nên áp dụng và những lưu ý quan trọng. Nếu bạn mới dùng Excel, mời bạn khám phá khóa học Phân tích dữ liệu trong Excel, nơi bạn có thể học các thao tác cơ bản như PivotTable và các hàm logic. 

Câu trả lời nhanh

Mặc dù Excel có nhiều cách để hoán vị dữ liệu như đã đề cập, cách nhanh nhất là dùng Dán Đặc biệt:

• Chọn và sao chép dữ liệu của bạn (Ctrl + C).

• Nhấp chuột phải vào ô đích nơi bạn muốn dữ liệu đã hoán vị xuất hiện.

• Chọn "Paste Special".

• Đánh dấu vào ô "Transpose".

• Nhấp OK.

Cách này lập tức đảo hàng thành cột và cột thành hàng, mang lại giải pháp nhanh cho hầu hết nhu cầu hoán vị.

Hoán vị trong Excel nghĩa là gì?

Hoán vị trong Excel nghĩa là chuyển đổi hàng thành cột và cột thành hàng. Về bản chất là xoay dữ liệu 90 độ, thay đổi hướng nhưng vẫn giữ nguyên thông tin.

Khi bạn hoán vị dữ liệu, hàng thứ nhất trở thành cột thứ nhất, hàng thứ hai thành cột thứ hai, và cứ thế.

Ví dụ, nếu bạn có bộ dữ liệu với tên sản phẩm theo hàng và doanh số theo tháng theo cột, hoán vị sẽ đưa tên sản phẩm lên các cột và doanh số theo tháng xuống các hàng.

Các cách hoán vị trong Excel

Excel cung cấp ba phương pháp riêng để hoán vị dữ liệu, mỗi cách có ưu điểm và tình huống sử dụng riêng. Hãy khám phá từng kỹ thuật theo từng bước để giúp bạn chọn cách phù hợp.

1. Dùng Dán Đặc biệt để hoán vị dữ liệu

Dán Đặc biệt là cách đơn giản nhất để hoán vị dữ liệu trong Excel. Nó tạo một bản sao một lần của dữ liệu theo hướng đã hoán vị. Cùng đi qua ví dụ: Hãy tưởng tượng chúng ta có dữ liệu doanh số theo khu vực ở dạng dọc: 

Ví dụ dữ liệu gốc ở dạng dọc. Ảnh: Tác giả. 

Để hoán vị dữ liệu này từ bố cục dọc sang ngang:

1. Chọn toàn bộ dữ liệu bạn muốn hoán vị (trong ví dụ này là A1:B5).
2. Sao chép dữ liệu (Ctrl+C hoặc nhấp phải chọn Copy).
3. Bấm vào ô nơi bạn muốn dữ liệu đã hoán vị xuất hiện (ví dụ ô D1).
4. Nhấp chuột phải và chọn Paste Special từ menu ngữ cảnh.

Phương pháp Dán Đặc biệt và tùy chọn Transpose. Ảnh: Tác giả. 

5. Trong hộp thoại Paste Special, đánh dấu Transpose và bấm OK.

Kết quả sau khi hoán vị được hiển thị bên dưới:

Kết quả sau khi hoán vị dữ liệu gốc. Ảnh: Tác giả. 

Lưu ý dữ liệu đã được xoay hoàn toàn — những gì trước đây ở hàng nay ở cột và ngược lại. Tên khu vực vốn ở cột A giờ trải ngang hàng 1, và số liệu doanh số ở cột B giờ trải ngang hàng 2.

Khi nào dùng cách này:

• Cho các chuyển đổi dữ liệu nhanh, một lần
• Khi bạn cần giải pháp đơn giản không dùng công thức
• Cho tập dữ liệu nhỏ không cần cập nhật thường xuyên

Ưu điểm: Đơn giản và nhanh với vài cú nhấp chuột. 

Hạn chế: Tạo bản sao tĩnh, không tự cập nhật khi dữ liệu gốc thay đổi.

2. Dùng hàm TRANSPOSE()

Hàm TRANSPOSE() cung cấp lựa chọn động thay cho Dán Đặc biệt. Không như phương pháp trước tạo bản sao tĩnh, hàm này duy trì liên kết trực tiếp với dữ liệu gốc, tự động cập nhật kết quả hoán vị khi nguồn thay đổi.

Hãy dùng lại dữ liệu doanh số theo khu vực để minh họa cách hoạt động:

1. Trước hết, bạn cần chọn vùng đích nơi dữ liệu đã hoán vị sẽ xuất hiện. Với ví dụ dữ liệu gồm 5 hàng x 2 cột, bạn cần chọn một vùng 2 hàng x 5 cột.

2. Khi các ô đích đang được chọn, nhập công thức TRANSPOSE() trên thanh công thức:

Hàm TRANSPOSE() trong Excel. Ảnh: Tác giả. 

3. Trong Excel hiện đại (2019 hoặc Microsoft 365), chỉ cần nhấn Enter. Với các phiên bản cũ hơn, bạn phải nhấn Ctrl+Shift+Enter vì đây là công thức mảng.

Kết quả sẽ tương tự ví dụ trước, nhưng có điểm khác quan trọng: nếu bạn thay đổi giá trị ở dữ liệu gốc, phiên bản đã hoán vị sẽ tự động cập nhật theo.

Kết quả sau khi dùng hàm TRANSPOSE(). Ảnh: Tác giả. 

Khi nào dùng cách này:

• Khi dữ liệu nguồn thay đổi thường xuyên
• Khi bạn cần duy trì liên kết trực tiếp giữa dữ liệu gốc và dữ liệu đã hoán vị
• Để tạo báo cáo hoặc bảng điều khiển động cần tự động làm mới

Ưu điểm: Tạo liên kết động tự cập nhật khi dữ liệu nguồn thay đổi. 

Hạn chế: Cần chọn đúng kích thước vùng đích; với phiên bản Excel cũ cần hiểu công thức mảng.

3. Dùng Power Query để hoán vị dữ liệu

Power Query là công cụ chuyển đổi dữ liệu mạnh mẽ nhất của Excel, hoàn hảo cho tập dữ liệu lớn và các biến đổi phức tạp. Dù cần nhiều bước hơn các cách trước, nó mang lại tính linh hoạt vượt trội.

Hãy dùng lại dữ liệu doanh số theo khu vực để minh họa cách Power Query có thể hoán vị dữ liệu:

1. Bắt đầu bằng cách nhấp vào thẻ Data trên dải băng Excel, rồi chọn Get Data (Power Query).

Cách khởi động Power Query trong Excel. Ảnh: Tác giả. 

2. Trong hộp thoại hiện ra, bạn sẽ thấy nhiều nguồn dữ liệu. Vì ta đang làm ví dụ nhỏ đã có sẵn trong Excel, hãy chọn Blank table để nhập dữ liệu thủ công. Lưu ý, sự đa dạng nguồn dữ liệu là một trong những ưu thế cốt lõi của Power Query.

Chọn nguồn dữ liệu Blank Table. Ảnh: Tác giả. 

3. Ở màn hình tiếp theo, nhập dữ liệu của bạn (hoặc dán từ trang tính) vào bảng. Hãy đánh dấu Use first row as headers nếu dữ liệu có tiêu đề cột.

Nhập dữ liệu cho Power Query. Ảnh: Tác giả. 

4. Sau khi bấm Next, Power Query Editor sẽ mở với dữ liệu đã nạp. Bây giờ, nhấp thẻ Transform trên dải băng và chọn Transpose từ menu.

Chuyển đổi dữ liệu bằng tính năng Transpose trong Power Query. Ảnh: Tác giả. 

5. Bạn sẽ thấy dữ liệu được hoán vị ngay trong cửa sổ xem trước. Lưu ý Power Query tự động đảo hàng và cột. Thanh công thức hiển thị Table.Transpose(Source) cho biết phép biến đổi đang được áp dụng.

Kết quả hoán vị trong Power Query. Ảnh: Tác giả. 

6. Khi đã hài lòng với phép biến đổi, nhấp Close & Load trong thẻ Home để đưa dữ liệu đã hoán vị về trang tính Excel.

Khi nào dùng cách này:

• Cho tập dữ liệu lớn nơi các phương pháp khác có thể rườm rà
• Khi bạn cần thực hiện các biến đổi bổ sung ngoài việc hoán vị
• Là một phần của quy trình làm sạch và chuẩn bị dữ liệu phức tạp hơn
• Cho các tác vụ lặp lại mà bạn muốn tự động hóa

Ưu điểm:

• Lý tưởng cho tập dữ liệu lớn và biến đổi dữ liệu phức tạp
• Tạo truy vấn có thể làm mới khi dữ liệu nguồn thay đổi
• Cung cấp thêm các tùy chọn làm sạch dữ liệu trong cùng quy trình
• Có thể lưu như một quy trình lặp lại

Hạn chế:

• Nhiều bước hơn so với các phương pháp khác
• Độ khó học cao hơn với người mới bắt đầu
• Mở một cửa sổ trình chỉnh sửa riêng

Hoán vị cột, hàng và bảng

Sau khi đã tìm hiểu ba phương pháp chính để hoán vị dữ liệu trong Excel, hãy điểm qua một số tình huống cụ thể bạn có thể gặp.

Cách hoán vị cột thành hàng trong Excel

Khi bạn có dữ liệu theo cột và cần đưa về hàng, hãy dùng bất kỳ phương pháp nào đã nêu:

Dùng Dán Đặc biệt:

1. Chọn và sao chép dữ liệu theo cột của bạn (Ctrl + C).
2. Nhấp chuột phải vào ô đầu tiên của hàng mới nơi bạn muốn dữ liệu xuất hiện.
3. Nhấp Paste Special, đánh dấu Transpose.
4. Nhấp OK để thấy các cột được chuyển thành hàng.

Cách này sắp xếp lại ngay lập tức nhưng tạo bản sao tĩnh.

Dùng hàm TRANSPOSE():

1. Chọn một vùng trống có số hàng khớp với số ô trong các cột gốc.

2. Gõ =TRANSPOSE(A1:A5) (chỉnh theo nhu cầu).

3. Nhấn Enter (hoặc Ctrl + Shift + Enter ở phiên bản cũ).

Cách này giữ dữ liệu đã hoán vị luôn liên kết với cột gốc, đảm bảo tự động cập nhật.

Cách hoán vị hàng thành cột trong Excel

Nếu dữ liệu của bạn được sắp theo hàng nhưng cần hiển thị theo cột, bạn có thể dùng tính năng Paste Special của Excel để chuyển đổi nhanh một lần hoặc hàm TRANSPOSE() cho cách tiếp cận động.

Dùng Dán Đặc biệt:

1. Chọn và sao chép dữ liệu theo hàng của bạn (Ctrl + C).
2. Nhấp chuột phải vào ô đích nơi bạn muốn các cột xuất hiện.
3. Nhấp Paste Special và đánh dấu ô Transpose.
4. Nhấp OK để thấy các hàng được chuyển thành cột.

Lưu ý các bước hoàn toàn giống khi hoán vị cột thành hàng. Excel xem hàng và cột là có thể thay thế cho nhau.

Dùng hàm TRANSPOSE():

1. Chọn một vùng trống có số cột bằng số ô trong hàng gốc.

2. Gõ =TRANSPOSE(A1:F1) (điều chỉnh phạm vi tùy nhu cầu).

3. Nhấn Enter hoặc Ctrl + Shift + Enter  .

Giờ đây, mọi thay đổi ở hàng gốc sẽ tự động cập nhật ở các cột đã hoán vị.

Nếu bạn làm với tập dữ liệu lớn cần cập nhật thường xuyên, Power Query là cách hiệu quả khác để tự động hóa chuyển đổi cột–hàng hoặc hàng–cột trong khi vẫn giữ liên kết dữ liệu. Ở phần trước, tôi đã nêu các bước sử dụng Power Query để hoán vị. 

Cách hoán vị bảng trong Excel

Khi làm việc với toàn bộ bảng hoặc vùng có cấu trúc:

1. Với bảng nhỏ, chỉ cần chọn toàn bộ phạm vi và dùng Paste Special → Transpose, như tôi đã trình bày trước đó.
2. Với bảng lớn có nhiều cột và hàng, Power Query cho phép kiểm soát tốt hơn và xử lý hiệu quả mọi kích thước bảng.
3. Với bảng mà chỉ cần hoán vị một phần, bạn có thể cần chia nhỏ cách làm — hoán vị một số phần trong khi giữ nguyên các phần khác

Hãy nhớ rằng hoán vị một bảng sẽ đổi không chỉ dữ liệu mà còn cả tiêu đề. Trong nhiều trường hợp, bạn sẽ muốn hàng đầu tiên trở thành cột đầu tiên (và ngược lại) để giữ nhãn dữ liệu chính xác.

Những điều cần cân nhắc thêm

Khi hoán vị dữ liệu trong Excel, có vài lưu ý quan trọng giúp bạn tránh lỗi thường gặp và cải thiện quy trình làm việc:

Xử lý ô trống

Hàm TRANSPOSE() có thể thay ô trống bằng số 0, điều này không phải lúc nào cũng mong muốn. Để giữ nguyên giá trị trống khi dùng hàm này, bạn có thể sử dụng câu lệnh IF() lồng nhau:

=TRANSPOSE(IF(A1:F5="","",A1:F5))	

Công thức này kiểm tra nếu ô trong phạm vi gốc trống thì giữ trống ở kết quả hoán vị thay vì chuyển thành 0.

Duy trì liên kết tới dữ liệu nguồn

Như đã bàn, ba phương pháp hoán vị xử lý liên kết đến dữ liệu nguồn khác nhau:

• Dán Đặc biệt tạo bản sao tĩnh, không có kết nối với dữ liệu gốc.

• Hàm TRANSPOSE() duy trì liên kết động, tự động cập nhật khi dữ liệu nguồn thay đổi.

• Power Query cho phép bạn làm mới kết nối khi cần, giúp kiểm soát thời điểm cập nhật.

Hãy chọn phương pháp phù hợp dựa trên tần suất dữ liệu nguồn thay đổi và việc bạn có cần cập nhật tự động hay không.

Dùng TRANSPOSE() với định dạng có điều kiện

Khi dùng hàm TRANSPOSE(), lưu ý rằng định dạng sẽ không được mang theo. Nếu dữ liệu gốc dùng định dạng có điều kiện để làm nổi bật giá trị quan trọng, bạn sẽ cần áp dụng quy tắc định dạng có điều kiện mới cho vùng đã hoán vị.

Điều này áp dụng cho mọi định dạng trực quan, gồm màu ô, phông chữ và đường viền. Chỉ có giá trị được hoán vị, không phải cách trình bày trực quan của chúng.

Kết luận

Hoán vị dữ liệu trong Excel là một kỹ năng hữu ích có thể cải thiện đáng kể việc tổ chức và phân tích dữ liệu của bạn. Mỗi trong ba phương pháp đã tìm hiểu đều có ưu điểm riêng tùy theo nhu cầu cụ thể.

Để học thêm các kỹ thuật thao tác dữ liệu nâng cao trong Excel, hãy cân nhắc khám phá lộ trình kỹ năng Excel Fundamentals của chúng tôi, bao quát nhiều kỹ năng giúp nâng cao năng lực phân tích dữ liệu. Nếu bạn quan tâm mở rộng kỹ năng chuyển đổi dữ liệu ngoài Excel, hãy xem hướng dẫn Cách hoán vị ma trận trong R để biết các kỹ thuật tương tự trong môi trường R.