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

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

Trong bài xích này, học Excel Online sẽ giải đáp một vài cách để chuyển đổi các mặt hàng thành những cột (hoặc cột thành hàng) trong Excel, thực tế hai việc đó là một:). Các phương án này sử 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, phân tích và lý giải 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 sử dụng hàm và bí quyết Transpose của Excel.


Xem nhanh


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

Giả sử bạn đã chế tác bảng tính Excel tương tự như mọi gì chúng ta thấy ở vị trí 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, nhưng danh sách các quốc gia hoàn toàn có thể quá dài, vày vậy bọn họ nên chuyển đổi các cột thành hàng để cân xứng 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 thể bảng, tức là tất cả những ô có dữ liệu trong bảng tính, nhấn Ctrl + Home và Ctrl + Shift + End .Sao chép ô đã chọn bởi cách nhấn vào phải vào vùng lựa 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 ô đầu tiên của vùng mà bạn muốn sao chép tài liệu đã gửi đổi.

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

Dán tài liệu được sao chép bằng phương pháp 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 sắc đen nhỏ 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 chọi ngữ cảnh, tiếp nối 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 dịch chuyển các ô bao gồm chứa công thức, phải đảm bảo an toàn có tham chiếu tuyệt vời và hoàn hảo nhất (dấu $) trong tất cả các ô. Điều này là cần thiết vì những công thức của công ty cũng sẽ tiến hành chuyển và các tham chiếu ô auto được điều chỉnh.

Như chúng ta vừa thấy, bản lĩnh 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 quý phái hàng) chỉ vào vài giây. Tôi tin rằng chiến thuật này là 1 trong những cách thực sự tốt nếu chỉ cần biến hóa 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 hình của dữ liệu thuở đầu của bạn, thêm 1 đối số vào vùng hình ảnh hưởng.


*

*

Tuy nhiên, giải pháp làm được nhắc ở 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 dữ liệu trong Excel:

Tùy lựa chọn transpose không thể dùng khi sao chép / dán bảng Excel tương đối đầy đủ hàm, không như phạm vi solo 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 biến hóa nó sang dạng Range (nhấp chuột buộc phải vào bất kỳ ô nào trong bảng với chọn  Table > Convert to lớn Range từ thực đơn ngữ cảnh).Một tiêu giảm nghiêm trọng rộng của phương pháp này là nó tạo ra một bạn dạng sao của dữ liệu ban đầu của các bạn và bất cứ khi nào dữ liệu nguồn cố gắng đổi, bạn phải lặp lại quy trình và xoay bảng một lượt nữa. Chẳng ai ao ước lãng phí thời gian quý giá của họ để gửi đi chuyển lại như thế, đúng không?Làm ráng nào để chuyển một bảng được links với tài liệu ban đầu

Hãy xem cách chúng ta có thể chuyển mặt hàng sang các cột vào Excel bằng phương pháp sử dụng kỹ thuật coppy / dán không còn xa lạ để liên kết các ô vẫn được di chuyển so với tài liệu ban đầu. Điều rất tốt khi thực hiện cách này là bất cứ bao giờ bạn biến hóa dữ liệu trong bảng nguồn, bảng biến hóa cũng sẽ thay đổi và cập nhật cho phù hợp.

Copy hàng bạn có nhu cầu chuyển thay đổi sang các cột (hoặc các cột được đưa 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 phía bên trái của hành lang cửa số hộp thoại.

*

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

*

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

*

Điều này sẽ đổi thay bảng của người tiêu dùng thành một cái gì đó đáng sợ, như các bạn thấy trong ảnh chụp screen dưới đây, cơ mà đừng lo lắng, chỉ cần thêm hai cách nữa và các bạn sẽ đạt được kết quả mong muốn.

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

*

Đây là một phương án nhanh, thanh thanh và đơn giản, bắt buộc không? Hạn chế tuyệt nhất của biện pháp này là định dạng của bảng ban sơ bị mất trong quá trình và các bạn sẽ cần phải phục sinh lại nó bằng tay thủ công (tôi sẽ chỉ cho mình một phương pháp nhanh chóng để gia công điều này trong gợi ý 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 kiếm các cách dễ dàng, thì bạn cũng có thể sử dụng những công thức để thay đổi các cột thành hàng trong bảng Excel của chúng ta và ngược lại.

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

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

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

*

Trước hết, bạ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 bọn họ có 7 cột và 6 hàng, bao gồm tiêu đề. Hãy ghi ghi nhớ những số lượng này, và họ hãy ban đầu chuyển các hàng.

Đếm những ô mà bạn có nhu cầu chuyển đổi. Trong trường hòa hợp của chúng ta 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ớ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à cột như bảng mối cung cấp của bạn. Trong lấy ví dụ này, họ đang 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 với nhập dải tài liệu mà bạn có nhu cầu chuyển đổi trong ngoặc đơn: = TRANSPOSE (array). Trong trường vừa lòng của này, chúng ta nhập =TRANSPOSE($A$1:$G$6).

Lưu ý: Bạn luô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 .

Lưu ý: Bạn đề nghị 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.

Xem thêm: Nhạc Thiếu Nhi Hoạt Hình Vui Nhộn Cá Lớn Cá Bé ♫ Con Chuồn Chuồn ♫ Bé Quét Nhà ♫ Cả Nhà Thương Nhau

Đã xong! Dữ liệu của bọn họ được biến hóa và họ 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:

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 với bất cứ khi nào bạn thay đổi dữ liệu nguồn, bảng chuyến qua cũng sẽ đổi khác theo.

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

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

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

Chúng ta sẽ áp dụng một bảng nhỏ trong ví dụ như này để chúng ta 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 kết hợp các hàm INDIRECT cùng ADDRESS, sẽ hơi phức hợp một chút.

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

*

Nhập công thức tiếp sau đây vào ô bên trái của vùng chọn, giả 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 sản phẩm khác với cùng một và cột không giống với A, bạn sẽ phải áp dụng một công thức tinh vi 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 thích hợp A1 là ô trên cùng bên đề nghị của bảng nguồn của bạn. Ngoài ra, xin để ý việc sử dụng các tham chiếu ô tuyệt đối hoàn hảo và tương đối.

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

*

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

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

*

Nhưng đừng thất vọng, trên đây không phải là một trong vấn đề gì bự cả. Bạn hoàn toàn có thể dễ dàng khôi phục 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 bắt buộc 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 để vươn lên là hàng thành cột vào Excel so 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 gửi tiếp, bên cạnh đó giữ link với bảng thuở đầu và cập nhật auto bất cứ bao giờ 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ỉ hoàn toàn 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ù, chúng ta cũng có thể nhanh chóng phục hồi nó, như ví dụ sinh hoạt trên.

Bây giờ bạn đã có cảm giác ra sao về hàm INDIRECT, bạn cũng có thể muốn tất cả cái nhìn sâu rộng về những gì cách làm thực sự đang làm và trên sao chúng ta lại sử dụng nó kết phù hợp với hàm ADDRESS.

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

Hàm INDIRECT, như tên của nó, được áp dụng để con gián tiếp xem 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 cách làm 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 làm sao 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 những hàm khác và các giá trị của các ô khác. Và đây và đúng là điều chúng ta 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ư đang biết, họ đã sử dụng 3 hàm nữa trong bí quyết là – ADDRESS, COLUMN với ROW.

Hàm ADDRESS được thực hiện để lấy add 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 máy hai. Ví dụ: ADDRESS (8,1) trả về $ A $ 8.

Hàm COLUMN trả về số cột của tham chiếu ô sẽ 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ó 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 nhằm tôi nhắc chúng ta về toàn bộ công thức mà họ đã áp dụng để biến đổi hàng thành các cột vào bảng tính Excel của chúng ta: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Như bạn thấy, vào hàm ADDRESS, bọn họ chỉ định COLUMN (A1) và ROW (A1),và trên đây thực sự là 1 trong những 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 bí quyết khác, hàm ADDRESS mang một cột được trả lại vì chưng hàm COLUMN và thay đổi nó thành một hàng, kế tiếp lấy một hàng và chuyển nó sang trọng cột, tức là hoán đổi các hàng thành các cột.

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

Rất nhiều kiến thức phải ko nào? toàn cục những kiến thức này chúng ta đều rất có thể học được trong khóa học EX101 – Excel tự cơ phiên bản tới chuyên gia của học tập Excel Online. Đây là khóa học giúp đỡ bạn hệ thống kỹ năng một phương pháp đầy đủ, chi 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 bạn cũng có thể thoải mái học bất cứ lúc nào, tiện lợi tra cứu vớt lại kỹ năng khi cần. Bây chừ hệ thống đang sẵn có ưu đãi khôn xiết lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: dacnhiem.vn.Online