Cách Chuyển Bảng Dọc Sang Ngang Trong Excel

Việc chuyển tài liệu trong Excel là 1 trong những vấn đề rất gần gũi với đa số người dùng. Nhất là sau thời điểm xây dựng một bảng tinh vi thì bài toán chuyển tài liệu sẽ khiến cho chúng trở cần rõ ràng, góp phân tích tốt hơn hoặc nhằm vẽ trang bị thị.

Bạn đang xem: Cách chuyển bảng dọc sang ngang trong excel

Trong bài bác này, học tập Excel Online sẽ gợi ý một vài phương pháp để chuyển đổi các mặt hàng thành những cột (hoặc cột thành hàng) vào Excel, thực ra hai vấn đề đó là một:). Các chiến thuật này 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, lý giải các lỗi điển hình nổi bật nhất và đưa ra các ví dụ nổi bật về việc sử dụng hàm và phương pháp Transpose của Excel.


Xem nhanh


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

Giả sử các bạn đã chế tác bảng tính Excel tương tự như như rất nhiều gì bạn thấy ở phần trên của hình dưới đây. Tên nước nhà được sắp xếp theo những cột, tuy nhiên danh sách các quốc gia rất có thể quá dài, vì chưng vậy họ nên biến đổi các cột thành sản phẩm để cân xứng với màn hình:

*

Chọn hàng và cột bạn có nhu cầu chuyển. Nếu bạn muốn xoay toàn cục bảng, tức là tất cả những ô có tài liệu trong bảng tính, nhấn Ctrl + Home và Ctrl + Shift + End .Sao chép ô đã chọn bằng cách bấm vào phải vào vùng chọn và chọn Copytừ trình solo 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 ô trước tiên của vùng mà bạn có nhu cầu sao chép tài liệu đã đưa đổi.

Lưu ý: Điều quan trọng đặc biệt là bạn lựa chọn một ô nằm không tính phạm vi cất dữ liệu ban sơ của bạn, làm sao cho vùng xào luộc và vùng dán không ông chồng chéo. Ví dụ: nếu như khách hàng có 4 cột với 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 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 trang chủ tab > Clipboard group,, nhấp vào mũi tên màu sắc đen nhỏ bên dưới nút Paste, và chọn Transpose .Nhấp chuột buộc phải vào ô đích và chọn Paste Specialtừ trình solo ngữ cảnh, tiếp đến chọn Transpose .Và cuối cùng, cách nhanh nhất có thể là nhấn Ctrl + alt + V , chọn Transposevà nhấn OK .

*

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

Như bạn vừa thấy, nhân kiệt Transpose của Excel cho phép bạn thực hiện thay đổi từ sản phẩm sang cột (hoặc cột sang trọng hàng) chỉ vào vài giây. Tôi tin rằng phương án này là một trong cách thực sự xuất sắc nếu chỉ cần thay đổi bảng tính một đợt duy nhất. Phương pháp này cũng xào luộc các định hình 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, giải pháp làm được đề cập ở bên trên có hai nhược điểm khiến nó không hẳn là giải pháp hoàn hảo cho bài toán chuyển tiếp dữ liệu trong Excel:

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

Hãy xem cách chúng ta có thể chuyển sản phẩm sang các cột vào Excel bằng phương pháp sử dụng kỹ thuật coppy / 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 rất tốt khi áp 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ẽ đổi khác và update cho phù hợp.

Copy hàng bạn muốn chuyển thay đổi sang các cột (hoặc các cột được gửi 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 biện pháp trước đó và chọn Paste Linkở góc dưới bên trái của hành lang cửa số hộp thoại.

*

Bạn vẫn có công dụng tương từ bỏ như sau:

*

Chọn dữ liệu mới này cùng mở vỏ hộp thoại Find và Replace cầm của Excel (bạn có thể nhấn Ctrl + H để chuyển đến tab Replace ngay).Thay thế toàn bộ các cam kết tự “=” bằng “xxx” (bạn cũng hoàn toàn có thể thay cầm cố nó bằng bất kỳ ký tự nào khác không vĩnh cửu ở bất kỳ nơi nào trong dữ liệu thực của bạn).

*

Điều này sẽ trở thành bảng của công ty thành một cái gì đấy đáng sợ, như các bạn thấy trong ảnh chụp màn hình dưới đây, tuy thế đừng lo lắng, chỉ việc thêm hai bước nữa và bạn sẽ đạt được tác dụng mong muốn.

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

*

Đây là một phương án nhanh, dìu dịu và đối kháng giản, đề xuất không? Hạn chế độc 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 phục sinh lại nó thủ công bằng tay (tôi đã chỉ cho chính mình một cách nhanh chóng để gia công điều này trong hướng dẫn này).

Chuyển tài 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ì chúng ta cũng có thể sử dụng các công thức để thay đổi các cột thành sản phẩm trong bảng Excel của người tiêu dùng và ngược lại.

Chuyển hàng sang cột trong Excel bằng hàm TRANSPOSE

Như tên thường gọi của nó, hàm Transpose được thiết kế đặc biệt nhằm chuyển tài liệu trong Excel. Nếu bạn không có khá nhiều kinh nghiệm với các công thức Excel nói chung, và những hàm array nói riêng, hãy bảo đảm bạn tuân hành đúng theo các bước dưới đây.

Trong lấy một ví dụ này, họ sẽ biế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 đúng đắn số hàng cùng cột trong bảng của bạn. Bảng chủng loại của họ có 7 cột và 6 hàng, bao gồm tiêu đề. Hãy ghi lưu giữ những số lượng này, và bọn họ hãy ban đầu chuyển những hàng.

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

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

*

Nhấn Ctrl + Shift + Enter .

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

Xem thêm:

Đã xong! Dữ liệu của chúng ta được biến đổi và chúng ta có 7 cột được chuyển đổi thành 6 cột, đúng chuẩn như bọn họ muốn:

*

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

Lợi ích bao gồm của việc thực hiện hàm TRANSPOSE là bảng được gửi giữ lại liên kết với bảng nguồn cùng bất cứ khi nào bạn chuyển đổi dữ liệu nguồn, bảng nối tiếp cũng sẽ chuyển đổi theo.

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

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

Thêm vào đó, dù hàm TRANSPOSE tốt và dễ sử dụng, nhưng chắc chắn rằng nó thiếu thốn tính linh động và bởi đó hoàn toàn có thể không cần là cách cực tốt để sử dụng trong không ít tình huống.

Chuyển sản phẩm thành cột / cột thành hàng bằng cách sử dụng Hàm INDIRECT

Phương pháp đổi khác cột sang mặt hàng và trái lại này khá tương tự với phương pháp trước đó. Điểm to gan chính của chính nó ưu việt rộng hàm Transpose là nó có thể chấp nhận được chỉnh sửa được trong bảng chuyển đổi, đồng thời giữ liên kết với bảng ban đầu.

Chúng ta sẽ sử dụng một bảng nhỏ tuổi trong ví dụ này để chúng ta có thể tập trung vào quy trình chuyển chứ không phải trên dữ liệu chính vì chúng ta sẽ sử dụng kết hợp các hàm INDIRECT cùng ADDRESS, đang hơi phức tạp một chút.

Giả sử chúng ta có tài liệu trong 4 cột (A – D) và 5 hàng (1 – 5):

*

Nhập công thức tiếp sau đây vào ô bên trái của vùng chọn, trả sử là ô A7 cùng nhấn phím Enter:

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

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

Sao chép bí quyết qua mảng mà bạn có nhu cầu đặt tài liệu được đưa như hay lệ bằng phương pháp kéo một chữ thập color đen nhỏ dại ở góc dưới bên cần của một ô hoặc một dải ô vẫn 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 khôn cùng mộc mạc cùng ngô nghê so với tài liệu ban đầu.

*

Nhưng đừng thất vọng, phía trên không phải là một trong những vấn đề gì béo cả. Bạn rất có thể dễ dàng phục hồi lại format nguồn theo phong cách này:

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

*

Tóm lại, INDIRECT bên cạnh đó là một giải pháp linh hoạt hơn để phát triển thành hàng thành cột trong Excel đối với hàm TRANSPOSE.

Ưu điểm : bạn cũng có thể dễ dàng chỉnh sửa bất kỳ ô nào trong bảng đưa tiếp, đồng thời giữ link với bảng lúc đầu và cập nhật tự động bất cứ khi nào bạn thực hiện đổi khác 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 – bài toán định dạng tài liệu không được lưu. Mặc dù, bạn cũng có thể nhanh chóng phục sinh 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, bạn có thể muốn tất cả cái quan sát sâu hơn về phần lớn gì cách làm thực sự đang làm cho và trên sao chúng ta lại áp dụng nó kết hợp với hàm ADDRESS.

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

Hàm INDIRECT, như thương hiệu của nó, được sử dụng để con gián tiếp xem thêm một ô. Ví dụ, nếu bạn muốn có quý giá của ô B1 vào ô A8, thì bạn nhập phương pháp này vào A8 =INDIRECT(“B1”).

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

Như vẫn biết, bọn họ đã sử dụng 3 hàm nữa trong cách làm là – ADDRESS, COLUMN cùng ROW.

Hàm ADDRESS được áp dụng để lấy showroom của một ô theo số hàng với cột mà chúng ta chỉ định. Hãy ghi nhớ điều này: đầu tiên, hàng, cột thiết 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, bởi vì cột A là cột sản phẩm nhất. Như bạn 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 để tôi nhắc các bạn về toàn cục công thức mà bọn họ đã sử dụng để biến hóa hàng thành những cột trong bảng tính Excel của bọn chúng ta: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Như chúng ta thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) cùng ROW (A1),và phía trên thực sự là 1 trong mưu mẹo! Hãy lưu giữ rằng, hàm ADDRESS diễn giải số đầu tiên dưới dạng hàng và thứ nhị là cột. Nói phương pháp khác, hàm ADDRESS lấy một cột được trả lại do hàm COLUMN và đổi khác nó thành một hàng, kế tiếp lấy một mặt hàng và gửi nó sang cột, có nghĩa là hoán đổi các hàng thành những cột.

Hàm COLUMN và ROW trả lại số cột với số hàng, tương ứng -> Hàm ADDRESS biến hóa cột thanh lịch hàng với hàng sang cột -> Hàm INDIRECT xuất ra dữ liệu đã được luân phiên 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 và kỹ năng phải ko nào? cục bộ những kiến thức này các bạn đều hoàn toàn có thể học được vào khóa học EX101 – Excel trường đoản cú cơ phiên bản tới chăm gia của học tập Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một giải pháp đầy đủ, đưa ra tiết. Hơn nữa không còn có số lượng giới hạn về thời hạn học tập nên chúng ta cũng có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu vớt lại kỹ năng khi cần. Bây giờ hệ thống đang có ưu đãi cực kỳ lớn cho mình khi đăng ký tham gia khóa học. Chi tiết xem tại: halfpeeledapple.com.Online