Trong bài viết này, chúng ta sẽ cùng nhau tìm hiểu về cách chuyển hàng ngang thành cột dọc trong Excel, giúp xử lí nhanh chóng các bảng dữ liệu phức tạp, phục vụ tốt cho công việc và học tập, cùng theo dõi nhé!

Chuyển đổi các hàng thành các cột trong Excel bằng Paste Special (Transpose)

Ví dụ ban đã tạo được một bảng tính Excel tương tự như những gì bạn nhìn thấy ở phần trên của hình minh họa bên dưới. Trong đó, tên quốc gia được sắp xếp theo các cột, nhưng danh sách những quốc gia này quá dài, và bạn quyết định tìm cách thay đổi các cột thành các hàng, và ngược lại để phù hợp với màn hình.

Các bước thực hiện như sau:

Bước 1: Đầu tiên, bạn nhấn chọn hàng và cột bạn muốn chuyển. Nếu như bạn muốn xoay toàn bộ bảng, có nghĩa là tất cả những ô có dữ liệu trong bảng tính, hãy nhấn Ctrl + Home và tổ hợp phím Ctrl + Shift + End .

Bước 2: Tiến hành sao chép ô đã chọn bằng việc nhấp chuột phải vào vùng chọn rồi nhấn chọn Copy từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phím Ctrl + C hay nhấp vào nút copy trên tab Home tab > Clipboard group.

Bước 3: Tiếp đó, nhấn chọn ô đầu tiên của vùng mà bạn đang muốn sao chép dữ liệu đã chuyển đổi.

Lưu ý: Điều quan trọng đó là bạn cần chọn một ô nằm ngoài phạm vi có chứa dữ liệu ban đầu của bạn, để sao cho vùng sao chép cùng với vùng dán không bị chồng chéo. Ví dụ như nếu bạn có 4 cột và 10 hàng, thì khi bạn chuyển vị trí của bảng thì lúc này sẽ có 10 cột và 4 hàng.

Bước 4: Cuối cùng, bạn thao tác dán dữ liệu được sao chép bằng việc dùng tính năng Paste Special > Transpose. Bạn cũng có thể mở hộp thoại Excel Paste Special theo 3 cách bên dưới:

• Trên tab  Home tab > Clipboard group,, bạn nhấp vào mũi tên màu đen nhỏ bên dưới nút Paste, rồi nhấn chọn Transpose .
• Nhấp chuột phải vào ô đích và nhấn chọn Paste Special từ trình đơn ngữ cảnh, sau đó bạn chọn Transpose .
• Cuối cùng, cách nhanh nhất đó là nhấn Ctrl + Alt + V , nhấn chọn Transpose và nhấn OK .

Lưu ý: Nếu như bạn tiến hành di chuyển những ô có chứa công thức, bạn cần phải đảm bảo có tham chiếu tuyệt đối (dấu $) trong toàn bộ các ô. Điều này là cần thiết bởi vì những công thức của bạn cũng sẽ được chuyển và những tham chiếu ô tự động được điều chỉnh.

Bạn có thể thấy rằng, tính năng Transpose của Excel giúp bạn thực hiện chuyển đổi từ hàng thành cột (hoặc là cột sang hàng) chỉ trong vài giây. Đây chắc chắn là giải pháp thực sự tốt nếu bạn chỉ cần chuyển đổi bảng tính một lần duy nhất. Phương pháp này cũng giúp sao chép những định dạng của dữ liệu ban đầu của bạn, cũng thêm một đối số trong vùng ảnh hưởng.

• Tùy chọn transpose sẽ không thể sử dụng khi sao chép / dán bảng Excel đầy đủ hàm, không như phạm vi đơn giản. Với trường hợp này, bạn cần phải tiến hành sao chép bảng không có tiêu đề cột, hoặc là thực hiện chuyển đổi nó thành dạng Range  (bạn nhấp chuột phải vào bất cứ ô nào nằm tại bảng và nhấn chọn  Table > Convert to Range từ menu ngữ cảnh).
• Một hạn chế nghiêm trọng hơn điều trên là nó tạo ra một bản sao của dữ liệu ban đầu của bạn và bất kì lúc nào dữ liệu nguồn thay đổi, thì bạn cần phải thực hiện lặp lại quá trình và xoay bảng một lần nữa. Tất nhiên, điều này sẽ khiến tốn rất nhiều thời gian của bạn.

Cách để chuyển một bảng được liên kết với dữ liệu ban đầu

Hãy cùng xem cách chuyển hàng thành các cột trong Excel bằng việc dùng kỹ thuật sao chép / dán quen thuộc để liên kết những ô đã được di chuyển so với dữ liệu ban đầu. Điều tốt nhất khi dùng phương pháp này đó là bất kì khi nào bạn thay đổi dữ liệu tại bảng nguồn, thì bảng chuyển đổi cũng sẽ thay đổi và cập nhật cho phù hợp. Cách làm như sau:

Bước 1: Đầu tiên, bạn thực hiện copy hàng bạn muốn chuyển đổi thành các cột (hoặc là các cột được chuyển sang hàng).

Bước 2: Nhấn chọn một ô trống trong cùng một trang hoặc là một bảng tính khác.

Bước 3: Thực hiện mở hộp thoại Paste Special, và nhấn chọn Paste Link ở góc dưới bên trái của cửa sổ hộp thoại.

Kết quả bạn nhận được sẽ như hình bên dưới:

Bước 4: Tiếp đó, thao tác chọn dữ liệu mới này và tiến hành mở hộp thoại Find and Replace thay thế của Excel (bạn cũng có thể nhấn Ctrl + H để chuyển đến tab Replace ngay).

Bước 5: Tiến hành thay thế tất cả những ký tự “=” bằng “xxx” (bạn cũng có thể thay thế nó bằng bất cứ ký tự nào khác mà không tồn tại ở bất cứ nơi nào khác trong dữ liệu thực của bạn).

Bước 6: Tiến hành sao chép bảng bằng giá trị “xxx”, và tiến hành áp dụng Paste Special > Transpose  để chuyển đổi các cột thành hàng, hoặc là các hàng thành cột, tùy thuộc vào mong muốn của bạn.

Bước 7: Cuối cùng, bạn mở hộp thoại Find and Replace một lần nữa để tiến hành đảo ngược sự thay đổi, có nghĩa là là thay thế tất cả “xxx” bằng “=” để khôi phục được liên kết đến các ô gốc.  Vậy là xong rồi đấy!

Đây được xem là một giải pháp nhanh, nhẹ nhàng và đơn giản. Chỉ có một điểm hạn chế duy nhất là định dạng của bảng ban đầu bị mất trong quá trình, lúc này bạn sẽ cần phải khôi phục lại nó thủ công.

Chuyển dữ liệu trong Excel bằng công thức

Nếu như bạn không tìm kiếm những cách dễ dàng, thì bạn có thể dùng các công thức để chuyển đổi các cột sang hàng trong bảng Excel của bạn và ngược lại.

Chuyển hàng sang cột trong Excel với hàm TRANSPOSE

Hàm Transpose có tác dụng chuyển dữ liệu trong Excel. Nếu như bạn chưa có nhiều kinh nghiệm với những công thức Excel nói chung, và các hàm array nói riêng, thì bạn hãy đảm bảo tuân thủ đúng theo các bước sau đây.

Ở ví dụ này, chúng ta sẽ tiến hành chuyển đổi một bảng liệt kê các tiểu bang Hoa Kỳ theo dân số:

Đầu tiên, bạn cần phải xác định chính xác được số hàng và cột trong bảng của bạn. Bảng mẫu ví dụ này của chúng ta có 7 cột và 6 hàng, bao gồm cả tiêu đề. Hãy ghi nhớ các con số này, và cùng bắt đầu chuyển hàng theo các bước sau:

Bước 1: Đầu tiên, thực hiện đếm những ô mà bạn muốn chuyển đổi. Trường hợp này là 7 cột và 6 hàng.

Bước 2: Tiếp đến, nhấn chọn một phạm vi trống. Bởi vì hàm TRANSPOSE chuyển hướng dọc và ngang của một dải ô đã chọn, bạn sẽ cần chọn một vùng có cùng số hàng và cột như trong bảng nguồn của bạn. Ở ví dụ này, bạn đang chọn 6 cột và 7 hàng.

Bước 3: Nhấn F2 để vào chế độ chỉnh sửa.

Bước 4: Tiến hành viết hàm TRANSPOSE và nhập dải dữ liệu bạn muốn chuyển đổi vào ngoặc đơn: = TRANSPOSE (array). Với trường hợp của này, bạn tiến hành nhập =TRANSPOSE($A$1:$G$6).

Lưu ý: Bạn luôn cần phải dùng tham chiếu tuyệt đối tại hàm TRANSPOSE.

Bước 5: Nhấn tổ hợp phím Ctrl + Shift + Enter .

Lưu ý: Bạn sẽ cần nhấn Ctrl + Shift + Enter vì bên trên là một công thức mảng, nếu nhấn Enter  nó sẽ không hoạt động.

Vậy là lúc này dữ liệu của bạn được chuyển đổi và bạn có 7 cột được thay đổi thành 6 cột, chính xác như điều bạn muốn:

Ưu điểm của hàm TRANSPOSE:

Ưu điểm của việc dùng hàm TRANSPOSE đó là bảng được chuyển giữ lại kết nối với bảng nguồn và bất kì khi nào bạn thay đổi dữ liệu nguồn, thì bảng chuyển tiếp cũng sẽ thay đổi theo.

Điểm yếu của hàm TRANSPOSE:

• Định dạng của bảng ban đầu không được lưu tại bảng chuyển đổi, như bạn thấy trong ảnh chụp màn hình ở trên.
• Nếu như có bất cứ ô trống nào trong bảng nguồn, thì những ô được chuyển sẽ chứa giá trị 0 thay thế.
• Bạn không thể thực hiện chỉnh sửa bất cứ ô nào tai bảng được chuyển đổi vì hàm TRANSPOSE phụ thuộc nhiều vào dữ liệu nguồn. Nếu như bạn cố gắng thay đổi giá trị của ô, thì Excel sẽ báo lỗi “You cannot change part of an array” [Bạn không thể thay đổi một phần của mảng].

Ngoài ra, dù hàm TRANSPOSE tốt và dễ sử dụng, tuy nhiên chắc chắn nó thiếu tính linh hoạt và vì vậy mà  có thể không phải là cách tốt nhất để dùng trong nhiều tình huống.

Xem thêm: Cách sử dụng hàm nhân 2 cột trong Excel

Chuyển Hàng thành cột / cột thành hàng bằng cách dùng Hàm INDIRECT

Phương pháp chuyển đổi cột thành hàng và ngược lại này khá giống với cách trước đó. Điểm mạnh ở đây là nó ưu việt hơn so với hàm Transpose. Hàm INDIRECT cho phép chỉnh sửa được trong bảng chuyển đổi, đồng thời cũng giữ kết nối với bảng ban đầu.

Bảng bên dưới là ví dụ, bạn có thể tập trung vào quá trình chuyển chứ không phải trên dữ liệu bởi vì bạn sẽ sử dụng kết hợp các hàm INDIRECT và ADDRESS, sẽ hơi phức tạp một chút.

Ví dụ bạn có dữ liệu tại 4 cột (A – D) và 5 hàng (1 – 5):

Bước 1: Tiến hành nhập công thức dưới đây vào ô bên trái của vùng chọn, ví dụ là ô A7 và nhấn phím Enter:

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Nếu như dữ liệu bắt đầu ở một hàng khác so với 1 và cột khác với A, bạn sẽ phải dùng một công thức phức tạp hơn một chút:

=INDIRECT(ADDRESS(COLUMN(A1) – COLUMN($A$1) + ROW($A$1), ROW(A1) – ROW($A$1) + COLUMN($A$1)))

Với trường hợp A1 là ô trên cùng bên phải của bảng nguồn. Bên cạnh đó, xin lưu ý đến việc dùng những tham chiếu ô tuyệt đối và tương đối.

Bước 2: Tiếp đó, tiến hành sao chép công thức qua mảng mà bạn muốn đặt dữ liệu được chuyển bằng việc kéo một chữ thập màu đen nhỏ ở góc dưới bên phải của một ô hoặc là một dải ô đã chọn. Vậy là các dữ liệu đã được chuyển rồi đấy!

Tuy vậy, những ô được chuyển trông rất mộc mạc và ngô nghê với dữ liệu ban đầu.

Nhưng cũng đừng thất vọng sớm, đây không hề là một vấn đề gì lớn cả. Bạn cũng có thể dễ dàng khôi phục lại định dạng nguồn theo cách này:

• Sao chép bảng ban đầu;
• Chọn bảng chuyển đổi;
• Nhấp chuột phải vào bảng chuyển đổi và nhấn chọn Formatting dưới Paste Options.

Tóm lại, hàm INDIRECT là một cách linh hoạt hơn để thực hiện biến hàng thành cột trong Excel so với hàm TRANSPOSE.

Ưu điểm : bạn có thể dễ dàng thực hiện chỉnh sửa bất cứ ô nào trong bảng chuyển tiếp, đồng thời cũng sẽ giữ liên kết với bảng ban đầu và tiến hành cập nhật tự động bất cứ khi nào bạn thực hiện thay đổi dữ liệu nguồn.

Những hạn chế : ở thực tế, bạn chỉ có thể thấy một điểm – việc định dạng dữ liệu sẽ không được lưu. Mặc dù, bạn có thể nhanh chóng khôi phục nó, như ví dụ minh họa ở trên.

Hàm INDIRECT và ADDRESS – cú pháp và logic

Hàm INDIRECT có chức năng gián tiếp tham khảo một ô. Ví dụ như nếu bạn muốn có giá trị của ô B1 ở ô A8, bạn nhập công thức này tại A8 =INDIRECT(“B1”).

Nhưng chức năng thực sự của hàm INDIRECT đó là có thể biến bất cứ chuỗi nào thành tham chiếu, gồm một chuỗi mà bạn xây dựng bằng cách dùng những hàm khác và những giá trị của các ô khác. Và đây chính xác là điều bạn sẽ làm tiếp theo. Nếu như bạn theo dõi phần này, bạn cũng sẽ dễ dàng hiểu toàn bộ các phần còn lại:)

Như đã biết, bạn đã dùng 3 hàm nữa trong công thức là – ADDRESS, COLUMN và ROW.

Hàm ADDRESS được dùng để lấy địa chỉ của một ô theo số hàng và cột mà bạn chỉ định. Và hãy nhớ điều này: đầu tiên, hàng, cột thứ hai. Ví dụ như ADDRESS (8,1) trả về $ A $ 8.

Hàm COLUMN có chức năng trả về số cột của tham chiếu ô đã cho. Ví dụ như, =COLUMN(A3) trả về 1, vì cột A là cột thứ nhất. Như vậy bạn có thể dễ dàng đoán được rằng hàm ROW hoạt động tương tự cho các hàng.

Và bây giờ, chúng ta sẽ nhắc lại toàn bộ công thức mà bạn để dùng để chuyển đổi hàng thành các cột trong bảng tính Excel: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Như bạn thấy, trong hàm ADDRESS, bạn chỉ định COLUMN (A1) và ROW (A1),và đây thực sự là một mưu mẹo! Bạn hãy nhớ rằng, hàm ADDRESS giúp diễn giải số đầu tiên dưới dạng hàng và thứ hai là cột. Hay nói cách khác, hàm ADDRESS sẽ lấy một cột được trả lại bởi hàm COLUMN và thực hiện thay đổi nó thành một hàng, sau đó lấy một hàng và chuyển nó sang cột, có nghĩa là hoán đổi các hàng thành các cột.

Hàm COLUMN và ROW thì thực hiện trả lại số cột và số hàng, tương ứng -> Hàm ADDRESS chuyển đổi cột sang hàng và hàng sang cột -> Hàm INDIRECT giúp xuất ra dữ liệu đã được xoay sang dữ liệu khác tại bảng tính Excel của bạn. Chúc bạn thực hiện thành công!

Xem thêm: Hướng dẫn cách xuống hàng trong Excel