HàmTRANSPOSE trong Excel có tác dụng chuyển đổi hàng thành cột, cột thành hàng, giúp bạn thay đổi cách sắp xếp dữ liệu một cách nhanh chóng mà không làm mất dữ liệu, không phải nhập tay lại. Mời bạn cùng khám phá công thức và cách dùng hàm TRANSPOSEtrong bài sau nhé!
1Công thức hàm TRANSPOSE
HàmTRANSPOSEtrong Excel có chức năng chuyển đổi hàng thành cột và ngược lại, được sử dụng khi bạn muốn thay đổi cách hiển thị của một bảng dữ liệu có sẵn. Điều này sẽ giúp bạn tiết kiệm được thời gian hơn so với việc nhập dữ liệu lại từ đầu để dữ liệu hiển thị theo ý muốn.
Cú pháp của hàm TRANSPOSE:
Lưu ý:
- HàmTRANSPOSE chỉ được sử dụng trongcông thức mảng[tức là công thức sẽ được áp dụng trong nhiều ô]. Do đó, nếu bạn có phiên bản mới nhất của Microsoft 365thì bạn có thể nhập công thức ở phía trên cùng bên trái của phạm vi đầu ra, sau đó nhấnEnter để xác nhận dùng công thức mảng.Nếu không sử dụng phiên bản trên thì sau khi hoàn thành công thức bạn cần nhấnCtrl + Shift + Enter.
- Khi dùng hàm TRANSPOSE để chuyển hàng thành cột, cột thành hàng cho một vùng dữ liệu thì khi thay đổi dữ liệu ở vùng dữ liệu gốc, vùng dữ liệu đích được áp dụng công thức mảng với hàm TRANSPOSE cũng sẽ thay đổi theo.
- Không nên lạm dụng công thức mảng trên phạm vi quá lớn, vì điều này có thể dẫn đến việc phần mềm bị treo, chậm phản hồi hoặc bịlỗi #N/Ado không chọn đúng số lượng cột, dòng cho mảng đích.
2Một số ví dụ về hàm TRANSPOSE
Ứng dụng phổ biến của hàm TRANSPOSE là để chuyển các hàng thành cột và cột thành hàng, sử dụng khi bạn muốn thay đổi cách trình bày của một bảng dữ liệu bất kỳ trong trang tính.
Giả sử bạn muốn thay đổi cách trình bày của bảng để các dữ liệu về Kho, Mã loại thuốc, ID thuốc được hiển thị từ chiều dọc sang chiều ngang như sau:
Để có kết quả như hình trên, bạn sẽ cần thực hiện các thao tác:
Bước 1:Chọn mảng đích bằng cách nhấn chọn vào ô đầu tiên và kéo thả chuột đến hết ô cuối cùng trong vùng bạn định xuất kết quả. Lưu ý hãy chọn mảng đích với số lượng hàng, cột đối xứng với số lượng hàng, cột trong mảng gốc.
Ví dụ, mảng bạn muốn chuyển đổi có 2 cột, 3 dòng thì hãy chọn mảng đích có 3 cột và 2 dòng. Nếu chọn không đúng, các ô dư ra sẽ bị lỗi #N/A.
Bước 2:Bạn gõ công thức hàm TRANSPOSE vào ô đầu tiên bên trái trong mảng vừa chọn. Chẳng hạn ở ví dụ bên dưới bạn sẽ gõ =TRANSPOSE[A1:B3] vào ô D1.
Như đã đề cập, nếu Excel của bạn là phiên bản mới nhất thì bạn có thể nhấnEnterđể áp dụng công thức mảng. Tuy nhiên, do thao tác trong bài viết được thực hiện trên Excel 2016, không phải phiên bản mới nhất nênsẽ cần nhấnCtrl + Shift + Enterđể áp dụng công thức mảng cho dữ liệu.
Trong thanh công thức, bạn sẽ thấy 2 dấu ngoặc nhọn được thêm vào ký tự đầu tiên và cuối cùng của công thức. Đây là dấu hiệu cho thấy ô được chọn đang áp dụng công thức mảng. Kết quả thu được sẽ như hình dưới.
Một số cách dùng nâng cao:
Bên cạnh tính năng chuyển dữ liệu hàng thành cột và cột thành hàng thì hàm TRANSPOSE còn có thể được ứng dụng trong việcnối dữ liệu trong 1 mảng. Bạn có thể tham khảo cách làmtại đâynhé!
3 Một số mẹo sử dụng hàm
Khi sử dụng hàm TRANSPOSE để chuyển dữ liệu từ hàng sang cột và ngược lại, bạn có thể áp dụng một số mẹo sau để giúp thao tác được nhanh chóng hơn:
- Sau khi nhập =TRANSPOSE[, bạn có thể sử dụng chuột để chọn dải ô bằng cách nhấn chọn và kéo thả chuột từ đầu đến cuối dải ô.
- Sau khi nhập xong mảng gốc vào công thức, không nhấn riêng Enter nếu máy bạn không được càiphiên bản mới nhất của Microsoft 365. Thay vào đó, hãy nhấnCtrl + Shift + Enter.
- Việc dùng hàm TRANSPOSE để hoán đổi cột thành hàng, hàng thành cột sẽ không giúp bạn giữ lại định dạng ô. Bạn có thể dùng một cách khác là sao chép các ô gốc và khi dán, hãy nhấn chuột phải, sau đó sử dụng tùy chọn Transpose [T] trong Paste Options. Kết quả sẽ được như minh họa bên dưới.
Tuy nhiên, cách làm này sẽ tạo dữ liệu trùng lặp và khi bạn thực hiện chỉnh sửa ở một ô bất kỳ trong mảng gốc, dữ liệu sẽ không được cập nhật vào bảng sau khi chuyển đổi. Bạn có thể xem ví dụ dưới đây để so sánh cách hoạt động của hàm TRANSPOSE và cách làm dùng tính năng Transpose trong tùy chọn Paste Options:
Ban đầu, bạn dùng hàm TRANSPOSE để tạo ra bảng màu xanh dương [phía trên] và dùng thao tác Copy> Paste Options > Transposenhư hướng dẫn ở trên để tạo ra bảng xanh lục [phía dưới]. Hãy chú ý đến ô chứa số 345, vì thao tác tiếp theo chúng ta sẽ tiến hành đổi số 345 tại ô B1 thành một con số khác.
Tại ô B1, bạn hãy thay 345 thành 34567.
Bạn sẽ thấy hàm TRANSPOSE sẽ thay đổi dữ liệu trong mảng theo dữ liệu gốc, còn cách làm bên dưới thì không.
Như vậy, tùy theo nhu cầu của mình bạn có thể chọn cách chuyển đổi phù hợp:
- Nếu bạn cần tạo một mảng riêng để tiện theo dõi và việc giữ lại mảng gốc là cần thiết, hãy sử dụng hàm TRANSPOSE để chuyển cột thành hàng, hàng thành cột theo ý muốn. Hàm này sẽ thay đổi dữ liệu tại mảng đích nếu như dữ liệu tại mảng gốc thay đổi và bạn sẽ không phải mất công rà soát lại, cập nhật các dữ liệu được thay đổi.
- Nếu bạn không cần giữ lại mảng ban đầu và muốn thay đổi toàn bộ cách trình bày cho mảng, hãy cân nhắc cách dùng tính năng Transpose trong Paste Options. Khi có mảng dữ liệu được chuyển đổi, bạn có thể xóa đi mảng cũ để tránh trùng lặp.
Tham khảo một số mẫu laptop kinh doanh tại Điện máy XANH:
Trên đây là hướng dẫn cách dùng hàm TRANSPOSE chuyển dữ liệu từ hàng sang cột và ngược lại trong Excel. Mong rằng từ những thông tin của bài viết, bạn sẽ hiểu hơn về hàm TRANSPOSE và ứng dụng vào công việc một cách hiệu quả nhất nhé!
Việc chuyển dữ liệu trong Excel là một vấn đề quen thuộc với nhiều người dùng. Nhất là sau khi xây dựng một bảng phức tạp thì việc chuyển dữ liệu sẽ khiến chúng trở nên rõ ràng, giúp phân tích tốt hơn hoặc để vẽ đồ thị.
Trong bài này, Học Excel Online sẽ hướng dẫn một vài cách để chuyển đổi các hàng thành các cột [hoặc cột thành hàng] trong Excel, thực ra hai việc đó là một:]. Các giải pháp này dùng được trong tất cả các phiên bản Excel 2016, 2013, 2010, 2007 và thấp hơn. Bao gồm nhiều khả năng có thể xảy ra, giải thích các lỗi điển hình nhất và đưa ra các ví dụ điển hình về việc sử dụng hàm và công thức Transpose của Excel.
Chuyển đổi các hàng thành các cột trong Excel bằng cách sử dụng Paste Special [Transpose]
Giả sử bạn đã tạo bảng tính Excel tương tự như những gì bạn thấy ở phần trên của hình dưới đây. Tên quốc gia được sắp xếp theo các cột, nhưng danh sách các quốc gia có thể quá dài, vì vậy chúng ta nên thay đổi các cột thành hàng để phù hợp với màn hình:
- Chọn hàng và cột bạn muốn chuyển. Nếu bạn muốn xoay toàn bộ bảng, tức là tất cả các ô có dữ liệu trong bảng tính, nhấn Ctrl + Home và Ctrl + Shift + End .
- Sao chép ô đã chọn bằng cách nhấp chuột phải vào vùng chọn và chọn Copytừ trình đơn ngữ cảnh hoặc nhấn Ctrl + C hoặc nhấp vào nút copy trên tab Home tab > Clipboard group.
- Chọn ô đầu tiên của vùng mà bạn muốn sao chép dữ liệu đã chuyển đổi.
Lưu ý: Điều quan trọng là bạn chọn một ô nằm ngoài phạm vi chứa dữ liệu ban đầu của bạn, sao cho vùng sao chép và vùng dán không chồng chéo. Ví dụ: nếu bạn có 4 cột và 10 hàng, khi bạn chuyển vị trí của bảng thì sẽ có 10 cột và 4 hàng.
- Dán dữ liệu được sao chép bằng cách sử dụng tính năng Paste Special > Transpose. Bạn có thể mở hộp thoại Excel Paste Special theo 3 cách:
- Trên tab Home tab > Clipboard group,, nhấp vào mũi tên màu đen nhỏ bên dưới nút Paste, và chọn Transpose .
- Nhấp chuột phải vào ô đích và chọn Paste Specialtừ trình đơn ngữ cảnh, sau đó chọn Transpose .
- Và cuối cùng, cách nhanh nhất là nhấn Ctrl + Alt + V , chọn Transposevà nhấn OK .
Lưu ý: Nếu bạn di chuyển các ô có chứa công thức, phải đảm bảo có tham chiếu tuyệt đối [dấu $] trong tất cả các ô. Điều này là cần thiết vì các công thức của bạn cũng sẽ được chuyển và các tham chiếu ô tự động được điều chỉnh.
Như bạn vừa thấy, tính năng Transpose của Excel cho phép bạn thực hiện chuyển đổi từ hàng sang cột [hoặc cột sang hàng] chỉ trong vài giây. Tôi tin rằng giải pháp này là một cách thực sự tốt nếu 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 sao chép các định dạng của dữ liệu ban đầu của bạn, thêm một đối số trong vùng ảnh hưởng.
Tuy nhiên, cách làm được đề cập ở trên có hai nhược điểm khiến nó không phải là giải pháp hoàn hảo cho việc chuyển tiếp dữ liệu trong Excel:
- Tùy chọn transpose không thể dùng khi sao chép / dán bảng Excel đầy đủ hàm, không như phạm vi đơn giản. Trong trường hợp này, bạn cần phải sao chép bảng không có tiêu đề cột, hoặc chuyển đổi nó sang dạng Range [nhấp chuột phải vào bất kỳ ô nào trong bảng và chọn Table > Convert to Range từ menu ngữ cảnh].
- Một hạn chế nghiêm trọng hơn của phương pháp này là nó tạo ra một bản sao của dữ liệu ban đầu của bạn và bất cứ khi nào dữ liệu nguồn thay đổi, bạn cần phải lặp lại quá trình và xoay bảng một lần nữa. Chẳng ai muốn lãng phí thời gian quý giá của họ để chuyển đi chuyển lại như thế, đúng không?
Làm thế nào để chuyển một bảng được liên kết với dữ liệu ban đầu
Hãy xem cách bạn có thể chuyển hàng sang các cột trong Excel bằng cách sử dụng kỹ thuật sao chép / dán quen thuộc để liên kết các ô đã được di chuyển so với dữ liệu ban đầu. Điều tốt nhất khi sử dụng cách này là bất cứ khi nào bạn thay đổi dữ liệu trong bảng nguồn, bảng chuyển đổi cũng sẽ thay đổi và cập nhật cho phù hợp.
- Copy hàng bạn muốn chuyển đổi sang các cột [hoặc các cột được chuyển sang hàng].
- Chọn một ô trống trong cùng một trang hoặc một bảng tính khác.
- Mở hộp thoại Paste Special, như đã giải thích trong cách trước đó và chọn Paste Linkở góc dưới bên trái của cửa sổ hộp thoại.
Bạn sẽ có kết quả tương tự như sau:
- Chọn dữ liệu mới này và mở hộp thoại Find and Replace thế của Excel [bạn có thể nhấn Ctrl + H để chuyển đến tab Replace ngay].
- Thay thế tất cả các ký tự “=” bằng “xxx” [bạn cũng có thể thay thế nó bằng bất kỳ ký tự nào khác không tồn tại ở bất kỳ nơi nào trong dữ liệu thực của bạn].
Điều này sẽ biến bảng của bạn thành một cái gì đó đáng sợ, như bạn thấy trong ảnh chụp màn hình dưới đây, nhưng đừng lo lắng, chỉ cần thêm hai bước nữa và bạn sẽ đạt được kết quả mong muốn.
- Sao chép bảng bằng giá trị “xxx”, và sau đó áp dụng Paste Special > Transpose để chuyển đổi các cột thành hàng, hoặc các hàng thành cột, tùy thuộc vào nhu cầu của bạn.
- Và cuối cùng, mở hộp thoại Find and Replace một lần nữa để đảo ngược sự thay đổi, tức là thay thế tất cả “xxx” bằng “=” để khôi phục liên kết đến các ô gốc. Đã xong!
Đây là một giải pháp nhanh, nhẹ nhàng và đơn giản, phải không? Hạn chế duy nhất của cách này là định dạng của bảng ban đầu bị mất trong quá trình và bạn sẽ cần phải khôi phục lại nó bằng tay [tôi sẽ chỉ cho bạn một cách nhanh chóng để làm điều này trong hướng dẫn này].
Chuyển dữ liệu trong Excel bằng công thức
Nếu bạn không tìm kiếm các cách dễ dàng, thì bạn có thể sử dụng các công thức để chuyển đổi các cột thành hàng trong bảng Excel của bạn và ngược lại.
Chuyển hàng sang cột trong Excel bằng hàm TRANSPOSE
Như tên gọi của nó, hàm Transpose được thiết kế đặc biệt để chuyển dữ liệu trong Excel. Nếu bạn không có nhiều kinh nghiệm với các công thức Excel nói chung, và các hàm array nói riêng, hãy đảm bảo bạn tuân thủ đúng theo các bước dưới đây.
Trong ví dụ này, chúng ta sẽ chuyển đổi một bảng liệt kê các tiểu bang Hoa Kỳ theo dân số:
Trước hết, bạn cần phải xác định chính xác số hàng và cột trong bảng của bạn. Bảng mẫu của chúng ta có 7 cột và 6 hàng, bao gồm tiêu đề. Hãy ghi nhớ những con số này, và chúng ta hãy bắt đầu chuyển các hàng.
- Đếm các ô mà bạn muốn chuyển đổi. Trong trường hợp của chúng ta là 7 cột và 6 hàng.
- Chọn một phạm vi trống. Vì hàm TRANSPOSE chuyển hướng dọc và ngang của một dải ô đã chọn, bạn cần phải chọn một vùng có cùng số hàng và cột như bảng nguồn của bạn. Trong ví dụ này, chúng ta đang chọn 6 cột và 7 hàng.
- Nhấn F2 để vào chế độ chỉnh sửa.
- Viết hàm TRANSPOSE và nhập dải dữ liệu mà bạn muốn chuyển đổi trong ngoặc đơn: = TRANSPOSE [array]. Trong trường hợp của này, chúng ta nhập =TRANSPOSE[$A$1:$G$6].
Lưu ý: Bạn luôn cần phải sử dụng tham chiếu tuyệt đối trong hàm TRANSPOSE.
- Nhấn Ctrl + Shift + Enter .
Lưu ý: Bạn cần nhấn Ctrl + Shift + Enter vì đây là một công thức mảng, nhấn Enter nó sẽ không hoạt động.
Đã xong! Dữ liệu của chúng ta được chuyển đổi và chúng ta có 7 cột được thay đổi thành 6 cột, chính xác như chúng ta muốn:
Ưu điểm của hàm TRANSPOSE:
Lợi ích chính của việc sử 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 cứ khi nào bạn thay đổi dữ liệu nguồn, bảng chuyển tiếp cũng sẽ thay đổi theo.
Điểm yếu của hàm TRANSPOSE:
- Định dạng bảng ban đầu không được lưu trong bảng chuyển đổi, như bạn thấy trong ảnh chụp màn hình ở trên.
- Nếu có bất kỳ ô trống nào trong bảng nguốn, các ô được chuyển sẽ chứa giá trị 0 thay thế.
- Bạn không thể chỉnh sửa bất kỳ ô nào trong bảng được chuyển đổi bởi vì hàm TRANSPOSE phụ thuộc rất nhiều vào dữ liệu nguồn. Nếu bạn cố gắng thay đổi giá trị của ô, 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].
Thêm vào đó, dù hàm TRANSPOSE tốt và dễ sử dụng, nhưng chắc chắn nó thiếu tính linh hoạt và do đó có thể không phải là cách tốt nhất để sử dụng trong nhiều tình huống.
Chuyển Hàng thành cột / cột thành hàng bằng cách sử dụng Hàm INDIRECT
Phương pháp chuyển đổi cột sang hàng và ngược lại này khá giống với cách trước đó. Điểm mạnh chính của nó ưu việt hơn hàm Transpose là nó cho phép chỉnh sửa được trong bảng chuyển đổi, đồng thời giữ kết nối với bảng ban đầu.
Chúng ta sẽ sử dụng một bảng nhỏ trong ví dụ này để 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ì chúng ta 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.
Giả sử bạn có dữ liệu trong 4 cột [A – D] và 5 hàng [1 – 5]:
- Nhập công thức dưới đây vào ô bên trái của vùng chọn, giả sử là ô A7 và nhấn phím Enter:
=INDIRECT[ADDRESS[COLUMN[A1],ROW[A1]]]
Nếu dữ liệu của bạn bắt đầu ở một hàng khác với 1 và cột khác với A, bạn sẽ phải sử 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]]]
Trường hợp A1 là ô trên cùng bên phải của bảng nguồn của bạn. Ngoài ra, xin lưu ý việc sử dụng các tham chiếu ô tuyệt đối và tương đối.
- Sao chép công thức qua mảng mà bạn muốn đặt dữ liệu được chuyển như thường lệ bằng cách 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 một dải ô đã chọn.
Được rối đấy! Trong bảng mới tạo của bạn, tất cả các cột được chuyển sang hàng.
Tuy nhiên, các ô được chuyển trông rất mộc mạc và ngô nghê so với dữ liệu ban đầu.
Nhưng đừng thất vọng, đây không phải là một vấn đề gì lớn cả. Bạn 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à chọn Formatting dưới Paste Options.
Tóm lại, INDIRECT dường như là một cách linh hoạt hơ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 chỉnh sửa bất kỳ ô nào trong bảng chuyển tiếp, đồng thời giữ liên kết với bảng ban đầu và 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ế : trên thực tế, tôi chỉ có thể thấy một điểm – việc định dạng dữ liệu không được lưu. Mặc dù, bạn có thể nhanh chóng khôi phục nó, như ví dụ ở trên.
Bây giờ bạn đã có cảm giác như thế nào về hàm INDIRECT, bạn có thể muốn có cái nhìn sâu hơn về những gì công thức thực sự đang làm và tại sao chúng ta lại sử dụng nó kết hợp với hàm ADDRESS.
Hàm INDIRECT và ADDRESS – cú pháp và logic
Hàm INDIRECT, như tên của nó, được sử dụng để gián tiếp tham khảo một ô. Ví dụ, nếu bạn muốn có giá trị của ô B1 trong ô A8, thì bạn nhập công thức này trong A8 =INDIRECT[“B1”].
Nhưng sức mạnh thực của hàm INDIRECT là nó có thể biến bất kỳ chuỗi nào thành tham chiếu, bao gồm một chuỗi mà bạn xây dựng bằng cách sử dụng các hàm khác và các giá trị của các ô khác. Và đây chính xác là điều chúng ta sẽ làm tiếp theo. Nếu bạn theo dõi phần này, bạn sẽ dễ dàng hiểu tất cả các phần còn lại:]
Như đã biết, chúng ta đã sử dụng 3 hàm nữa trong công thức là – ADDRESS, COLUMN và ROW.
Hàm ADDRESS được sử dụng để lấy địa chỉ của một ô theo số hàng và cột mà bạn chỉ định. Hãy nhớ điều này: đầu tiên, hàng, cột thứ hai. Ví dụ: ADDRESS [8,1] trả về $ A $ 8.
Hàm COLUMN trả về số cột của tham chiếu ô đã cho. Ví dụ, =COLUMN[A3] trả về 1, bởi vì cột A là cột thứ nhất. Như bạn có thể dễ dàng đoán được, hàm ROW hoạt động tương tự cho các hàng.
Và bây giờ, hãy để tôi nhắc bạn về toàn bộ công thức mà chúng ta đã sử dụng để chuyển đổi hàng thành các cột trong bảng tính Excel của chúng ta: =INDIRECT[ADDRESS[COLUMN[A1],ROW[A1]]]
Như bạn thấy, trong hàm ADDRESS, chúng ta chỉ định COLUMN [A1] và ROW [A1],và đây thực sự là một mưu mẹo! Hãy nhớ rằng, hàm ADDRESS diễn giải số đầu tiên dưới dạng hàng và thứ hai là cột. Nói cách khác, hàm ADDRESS lấy một cột được trả lại bởi hàm COLUMN và thay đổi nó thành một hàng, sau đó lấy một hàng và chuyển nó sang cột, tức là hoán đổi các hàng thành các cột.
Hàm COLUMN và ROW 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 xuất ra dữ liệu đã được xoay sang dữ liệu khác trong bảng tính Excel của bạn. Không có gì ghê gớm cả đúng không?
Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online