Việc chuyển tài liệu trong Excel là một trong những vấn đề không còn xa lạ với nhiều người dùng. Nhất là sau khoản thời gian xây dựng một bảng phức hợp thì câu hỏi chuyển tài liệu sẽ khiến cho chúng trở nên rõ ràng, giúp phân tích giỏi hơn hoặc để vẽ thiết bị thị.Bạn vẫn xem: biện pháp xoay bảng ngang thành dọc vào excel

Trong bài xích này, học tập Excel Online sẽ lí giải một vài bí quyết để chuyển đổi các sản phẩm thành các cột (hoặc cột thành hàng) vào Excel, thực tế hai vấn đề đó là một:). Các giải pháp này cần sử dụng được trong tất cả các phiên bạn dạng Excel 2016, 2013, 2010, 2007 và thấp hơn. Bao hàm nhiều khả năng rất có thể xảy ra, giải thích các lỗi điển hình nổi bật nhất và gửi ra các ví dụ nổi bật về việc áp dụng hàm và công thức Transpose của Excel.Bạn đã xem: Xoay mặt hàng thành cột trong excel

Chuyển đổi các hàng thành các cột vào Excel bằng phương pháp sử dụng Paste Special (Transpose)

Giả sử bạn đã tạo ra bảng tính Excel tương tự như đa số gì bạn thấy ở đoạn trên của hình bên dưới đây. Tên tổ quốc được thu xếp theo những cột, dẫu vậy danh sách các quốc gia rất có thể quá dài, vị vậy họ nên đổi khác các cột thành mặt hàng để phù hợp với màn hình:

*

*

*

Chọn hàng cùng cột bạn muốn chuyển. Nếu bạn có nhu cầu xoay toàn thể bảng, có nghĩa 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 ô đang chọn bằng cách nhấn vào phải vào vùng chọn và chọn Copytừ trình đối kháng ngữ cảnh hoặc nhấn Ctrl + C hoặc nhấp vào nút copy trên tab trang chủ tab > Clipboard group.Chọn ô đầu tiên của vùng mà bạn có nhu cầu sao chép tài liệu đã chuyển đổi.

Bạn đang xem: Xoay hàng thành cột trong excel

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

Dán dữ liệu được sao chép bằng phương pháp sử dụng tính năng Paste Special > Transpose. Bạn rất 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 sắc đen bé dại bên dưới nút Paste, với chọn Transpose .Nhấp chuột buộc phải vào ô đích cùng chọn Paste Specialtừ trình đối kháng ngữ cảnh, kế tiếp chọn Transpose .Và cuối cùng, cách sớm nhất là nhấn Ctrl + alternative text + V , chọn Transposevà nhấn OK .


*

Lưu ý: Nếu bạn dịch chuyển các ô bao gồm chứa công thức, phải đảm bảo có tham chiếu tuyệt vời (dấu $) trong tất cả các ô. Điều này là quan trọng vì các công thức của doanh nghiệp cũng sẽ được chuyển và những tham chiếu ô tự động được điều chỉnh.

Như chúng ta vừa thấy, bản lĩnh Transpose của Excel chất nhận được bạn thực hiện biến đổi từ mặt hàng sang cột (hoặc cột sang trọng 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ự xuất sắc nếu chỉ cần biến hóa bảng tính một đợt duy nhất. Phương pháp này cũng sao chép các định dạng của dữ liệu lúc đầu của bạn, thêm 1 đối số vào vùng ảnh hưởng.

Tuy nhiên, cách làm được đề cập ở bên trên có hai nhược điểm khiến cho nó không phải là chiến thuật hoàn hảo cho việc chuyển tiếp tài liệu trong Excel:

Tùy chọn transpose không thể sử dụng khi xào nấu / dán bảng Excel không hề thiếu hàm, không như phạm vi đơn giản. Trong trường phù hợp này, bạn cần phải sao chép bảng không tồn tại tiêu đề cột, hoặc đổi khác nó lịch sự dạng Range (nhấp chuột cần vào bất kỳ ô như thế nào trong bảng với chọn  Table > Convert khổng lồ Range từ menu ngữ cảnh).Một giảm bớt nghiêm trọng rộng của phương thức này là nó tạo ra một bản sao của dữ liệu lúc đầu của bạn và bất cứ bao giờ dữ liệu nguồn thay đổi, bạn phải lặp lại quy trình và chuyển phiên bảng một đợt nữa. Chẳng ai muốn lãng phí thời gian quý giá của họ để gửi đi gửi lại như thế, đúng không?Làm nuốm nào để đưa một bảng được liên kết với tài liệu ban đầu

Hãy coi cách chúng ta có thể chuyển sản phẩm sang các cột vào 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 ô sẽ được di chuyển so với dữ liệu ban đầu. Điều tốt nhất khi thực hiện cách này là bất cứ khi nào bạn chuyển đổi dữ liệu trong bảng nguồn, bảng biến đổi cũng sẽ chuyển đổi và update cho phù hợp.

Copy hàng bạn muốn chuyển thay đổi sang những cột (hoặc những cột được đưa sang hàng).Chọn một ô trống trong và một trang hoặc một bảng tính khác.Mở hộp thoại Paste Special, như đã lý giải trong bí quyết trước đó và chọn Paste Linkở góc dưới phía trái của hành lang cửa số hộp thoại.


*

Bạn đã có kết quả tương tự như sau:

Chọn tài liệu mới này và mở vỏ hộp thoại Find & Replace cụ của Excel (bạn hoàn toàn có thể nhấn Ctrl + H để đưa đến tab Replace ngay).Thay thế toàn bộ các ký kết tự “=” bởi “xxx” (bạn cũng rất có thể thay núm nó bằng bất kỳ ký tự nào khác không mãi sau ở bất kỳ nơi nào trong tài liệu thực của bạn).

Điều này sẽ đổi mới bảng của khách hàng thành một cái nào đấy đáng sợ, như các bạn thấy trong hình ảnh chụp màn hình hiển thị dưới đây, tuy vậy đừng lo lắng, chỉ cần thêm hai bước nữa và bạn sẽ đạt được công dụng mong muốn.

Sao chép bảng bằng giá trị “xxx”, và kế tiếp áp dụng Paste Special > Transpose  để chuyển đổi các cột thành hàng, hoặc những hàng thành cột, tùy trực thuộc vào nhu yếu của bạn.Và cuối cùng, mở hộp thoại Find and Replace một lần nữa để hòn đảo ngược sự nắm đổi, có nghĩa là thay thế toàn bộ “xxx” bằng “=” để khôi phục link đến các ô gốc. Đã xong!

Đây là một phương án nhanh, nhẹ nhàng và 1-1 giản, yêu cầu không? Hạn chế độc nhất vô nhị của giải pháp này là format của bảng ban sơ bị mất trong quá trình và các bạn sẽ cần phải khôi phục lại nó thủ công (tôi đang chỉ cho mình một cách nhanh chóng để gia công điều này trong lý giải này).

Chuyển tài liệu trong Excel bởi công thức

Nếu bạn không tìm kiếm kiếm các cách dễ dàng, thì bạn có thể sử dụng các công thức để biến hóa các cột thành sản phẩm trong bảng Excel của công ty và ngược lại.

Chuyển sản phẩm sang cột vào Excel bằng hàm TRANSPOSE

Như tên thường gọi của nó, hàm Transpose được thiết kế theo phong cách đặc biệt nhằm chuyển tài liệu trong Excel. Nếu bạn không 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, hãy bảo đảm bạn tuân hành đúng theo công việc dưới đây.

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

Trước hết, bạn phải xác định đúng chuẩn số hàng và cột trong bảng của bạn. Bảng mẫu của họ có 7 cột với 6 hàng, bao hàm tiêu đề. Hãy ghi nhớ những con số này, và họ hãy ban đầu chuyển những hàng.

Đếm những ô mà bạn muốn chuyển đổi. Trong trường đúng theo của bọn họ là 7 cột và 6 hàng.Chọn một phạm vi trống. Vì hàm TRANSPOSE chuyển làn đường dọc với ngang của một dải ô đang chọn, bạn phải chọn một vùng có cùng số hàng với cột như bảng mối cung cấp của bạn. Trong ví dụ như này, chúng ta đang lựa chọn 6 cột với 7 hàng.Nhấn F2 để vào chính sách chỉnh sửa.Viết hàm TRANSPOSE cùng nhập dải dữ liệu mà bạn có nhu cầu chuyển thay đổi trong ngoặc đơn: = TRANSPOSE (array). Trong trường hợp của này, bọn họ nhập =TRANSPOSE($A$1:$G$6).

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

Nhấn Ctrl + Shift + Enter .

Đã xong! Dữ liệu của họ được thay đổi và chúng ta có 7 cột được đổi khác thành 6 cột, đúng mực như chúng ta muốn:

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

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

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

Xem thêm: Những Smartphone Chụp Ảnh Đẹp Nhất Thế Giới Không Thể Bỏ Qua

Thêm vào đó, cho dù hàm TRANSPOSE xuất sắc và dễ dàng sử dụng, nhưng chắc chắn nó thiếu tính linh động và bởi vì đó rất có thể không yêu cầu là cách tốt nhất để sử dụng trong vô số 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 mặt hàng và trái lại này khá như thể với phương pháp trước đó. Điểm mạnh bạo chính của nó ưu việt hơn hàm Transpose là nó được cho phép chỉnh sửa được vào bảng chuyển đổi, mặt khác giữ kết nối với bảng ban đầu.

Chúng ta sẽ áp dụng một bảng nhỏ dại trong lấy ví dụ này để chúng ta cũng có thể tập trung vào quá trình chuyển chứ chưa phải trên dữ liệu chính vì chúng ta vẫn sử dụng phối hợp các hàm INDIRECT cùng ADDRESS, đang hơi phức hợp một chút.

Giả sử chúng ta có dữ liệu trong 4 cột (A – D) cùng 5 sản phẩm (1 – 5):

Nhập công thức dưới đây vào ô phía trái của vùng chọn, mang sử là ô A7 với nhấn phím Enter:

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

Nếu tài liệu của bạn bước đầu ở một hàng khác với cùng một và cột không giống với A, các 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 phù hợp A1 là ô trên cùng bên bắt buộc của bảng mối cung cấp của bạn. Ngoài ra, xin lưu ý việc sử dụng các tham chiếu ô tuyệt đối hoàn hảo và tương đối.

Sao chép công thức qua mảng mà bạn muốn đặt tài liệu được chuyển như hay lệ bằng cách kéo một chữ thập màu đen nhỏ dại ở góc dưới bên nên của một ô hoặc một dải ô sẽ chọn.

Được rối đấy! Trong bảng bắt đầu tạo của bạn, tất cả các cột được gửi sang hàng.

Tuy nhiên, những ô được chuyển trông khôn xiết mộc mạc với ngô nghê so với dữ liệu ban đầu.

Nhưng chớ thất vọng, trên đây không phải là 1 trong những vấn đề gì lớn cả. Bạn hoàn toàn có thể dễ dàng phục hồi lại định dạng nguồn theo cách này:

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

Tóm lại, INDIRECT hình như là một phương pháp linh hoạt hơn để trở thành hàng thành cột vào Excel so với hàm TRANSPOSE.

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

Những hạn chế : bên trên thực tế, tôi chỉ rất 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ũng có thể nhanh chóng phục hồi nó, như ví dụ ngơi nghỉ trên.

Bây giờ bạn đã có cảm giác thế nào về hàm INDIRECT, chúng ta cũng có thể muốn gồm cái chú ý sâu hơn về đông đảo gì công thức thực sự đang làm và tại sao bọn họ 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 áp dụng để con gián tiếp tìm hiểu thêm một ô. Ví dụ, nếu bạn có nhu cầu có quý hiếm của ô B1 vào ô A8, thì các bạn nhập bí quyết này vào A8 =INDIRECT(“B1”).

Nhưng sức mạnh thực của hàm INDIRECT là nó hoàn toà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 những ô khác. Và đây chính xác là điều họ sẽ kéo dài theo. Nếu chúng ta theo dõi phần này, các bạn sẽ dễ dàng hiểu tất cả các phần còn lại:)

Như đã biết, bọn họ đã áp dụng 3 hàm nữa trong công thức là – ADDRESS, COLUMN với ROW.

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

Hàm COLUMN trả về số cột của tham chiếu ô vẫn cho. Ví dụ, =COLUMN(A3) trả về 1, cũng chính vì cột A là cột đồ vật nhất. Như chúng ta cũng có thể dễ dàng đoán được, hàm ROW chuyển động tương tự cho các hàng.

Và bây giờ, hãy nhằm tôi nhắc các bạn về toàn cục công thức mà chúng ta đã sử dụng để thay đổi hàng thành các cột trong bảng tính Excel của bọn chúng ta: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Như bạn thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) với ROW (A1),và đây thực sự là một trong những mưu mẹo! Hãy ghi nhớ rằng, hàm ADDRESS diễn giải số trước tiên dưới dạng hàng với thứ nhị là cột. Nói bí quyết khác, hàm ADDRESS đem một cột được trả lại vị hàm COLUMN và biến hóa nó thành một hàng, tiếp đến lấy một sản phẩm và đưa nó lịch sự cột, tức là hoán đổi những hàng thành các cột.

Hàm COLUMN với ROW trả lại số cột và số hàng, tương ứng -> Hàm ADDRESS đổi khác cột quý phái hàng và hàng thanh lịch cột -> Hàm INDIRECT xuất ra tài liệu đã được chuyển phiên sang dữ liệu khác trong bảng tính Excel của bạn. Không gồm gì ghê gớm cả đúng không?

Rất nhiều kỹ năng phải không nào? toàn bộ những kiến thức này chúng ta đều hoàn toàn có thể học được vào khóa học EX101 – Excel trường đoản cú cơ bản tới siêng gia của học Excel Online. Đây là khóa học giúp bạn hệ thống kỹ năng và kiến thức một cách đầy đủ, đưa ra tiết. Hơn nữa không hề có giới hạn về thời hạn học tập nên chúng ta có thể thoải mái học bất kể lúc nào, dễ ợt tra cứu lại kiến thức khi cần. Hiện giờ hệ thống đang có ưu đãi khôn cùng lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: 12guns.vn.Online