Việc chuyển dữ liệu trong Excel là một vấn đề quen thuộc 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ì câu hỏi chuyển tài liệu sẽ khiến chúng trở phải rõ ràng, góp phân tích giỏi hơn hoặc nhằm vẽ đồ dùng thị.Bạn đã xem: bí quyết đổi dòng thành cột trong excel

Trong bài này, học tập Excel Online sẽ lý giải một vài biện pháp để biến hóa 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 ra hai câu hỏi đó là một:). Các phương án này sử dụng được trong toàn bộ các phiên phiên bản Excel 2016, 2013, 2010, 2007 với thấp hơn. Bao hàm nhiều khả năng có thể xảy ra, giải thích các lỗi nổi bật nhất và gửi ra các ví dụ điển hình về việc thực hiện hàm và bí quyết Transpose của Excel.

Bạn đang xem: Cách đổi dòng thành cột trong excel

Chuyển đổi các 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 đã tạo ra bảng tính Excel tựa như như đều gì các bạn thấy ở vị trí trên của hình dưới đây. Tên đất nước được sắp xếp theo những cột, mà lại danh sách những quốc gia hoàn toàn có thể quá dài, vị vậy bọn họ nên thay đổi các cột thành mặt hàng để cân xứng với màn hình:

*

*

*

Chọn hàng cùng cột bạn có nhu cầu chuyển. Nếu bạn muốn xoay toàn bộ bảng, có nghĩa 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 nhấn vào phải vào vùng chọn và chọn Copytừ trình đối chọi 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 ô trước tiên của vùng mà bạn muốn sao chép dữ liệu đã gửi đổi.

Lưu ý: Điều đặc biệt là bạn chọn 1 ô 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 ông xã chéo. Ví dụ: nếu như khách hàng có 4 cột với 10 hàng, khi chúng ta chuyển địa điểm của bảng thì sẽ sở hữu được 10 cột cùng 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 hoàn toà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, cùng chọn Transpose .Nhấp chuột cần vào ô đích cùng chọn Paste Specialtừ trình đối chọi ngữ cảnh, kế tiếp 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 rời các ô có chứa công thức, phải bảo đảm có tham chiếu tuyệt vờ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 khách hàng 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, tính năng Transpose của Excel có thể chấp 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 chiến thuật này là 1 trong cách thực sự giỏi nếu chỉ cần biến đổi bảng tính một lượt duy nhất. Phương pháp này cũng xào nấu các định hình của dữ liệu ban đầu của bạn, thêm 1 đối số trong vùng ảnh hưởng.

Tuy nhiên, giải pháp làm được nhắc ở bên trên có hai nhược điểm khiến cho nó chưa hẳn là giải pháp hoàn hảo cho vấn đề chuyển tiếp dữ liệu trong Excel:

Tùy lựa chọn transpose không thể dùng khi coppy / dán bảng Excel rất đầy đủ hàm, không như phạm vi đơn giản. Trong trường thích 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 lớn Range từ thực đơn ngữ cảnh).Một tinh giảm nghiêm trọng hơn của phương pháp này là nó tạo ra một phiên bản sao của dữ liệu ban sơ của bạn và bất cứ khi nào dữ liệu nguồn chũm đổi, bạn phải lặp lại quy trình và xoay bảng một đợt nữa. Chẳng ai ao ước lãng phí thời hạn quý giá của mình để gửi đi chuyển lại như thế, đúng không?Làm nắ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 bạn cũng có thể chuyển sản phẩm sang những cột trong Excel bằng cách sử dụng kỹ thuật xào luộc / dán không còn xa lạ để liên kết các ô vẫn được dịch chuyển so với tài liệu ban đầu. Điều tốt nhất có thể khi thực hiện cách này là bất cứ lúc nào bạn đổi khác dữ liệu trong bảng nguồn, bảng biến đổi cũng sẽ biến hóa và update cho phù hợp.

Copy hàng bạn có nhu cầu chuyển đổi sang những cột (hoặc những 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ư đã lý giải 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ó công dụng tương trường đoản cú như sau:


Chọn dữ liệu mới này và mở hộp thoại Find and Replace cụ của Excel (bạn có thể nhấn Ctrl + H để gửi đến tab Replace ngay).Thay thế toàn bộ các ký kết tự “=” bằng “xxx” (bạn cũng hoàn toàn có thể thay thay nó bằng ngẫu nhiên ký từ nào không giống không lâu dài ở ngẫu nhiên nơi làm sao trong tài liệu thực của bạn).


Điều này sẽ biến đổi bảng của công ty 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 lại đừng lo lắng, chỉ cần thêm hai bước 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à kế tiếp áp dụng Paste Special > Transpose  để biế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 yếu của bạn.Và cuối cùng, mở hộp thoại Find and Replace một đợt nữa để đảo ngược sự ráng đổi, có nghĩa là thay thế tất cả “xxx” bằng “=” để khôi phục liên kết đến những ô gốc. Đã xong!


Đây là một phương án nhanh, nhẹ nhàng và đối chọi giản, buộc phải không? Hạn chế tốt nhất của cách này là định hình của bảng lúc đầu bị mất trong quá trình và các bạn sẽ cần phải phục hồi lại nó bằng tay thủ công (tôi đã chỉ cho mình một biện pháp nhanh chóng để làm điều này trong khuyên bảo này).

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

Nếu bạn không kiếm kiếm các cách dễ dàng dàng, thì bạn cũng có thể sử dụng những công thức để đổi khác 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 sản phẩm sang cột trong Excel bởi hàm TRANSPOSE

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

Trong lấy ví dụ 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 và cột vào bảng của bạn. Bảng chủng loại của chúng ta có 7 cột với 6 hàng, bao hàm tiêu đề. Hãy ghi ghi nhớ những con số này, và chúng ta hãy bước đầu chuyển những hàng.

Đếm những ô mà bạn muốn chuyển đổi. Trong trường phù 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 sang làn đường khác dọc với ngang của một dải ô đang chọn, bạn cần phải chọn một vùng bao gồm 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, chúng ta đang lựa 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 cùng 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, 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 biến đổi và chúng ta có 7 cột được đổi khác thành 6 cột, đúng mực như 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ứ lúc nào bạn thay đổi dữ liệu nguồn, bảng sự chuyển tiếp giữa cũng sẽ chuyển đổi theo.

Xem thêm: Mẹo Khôi Phục Hồi Tin Nhắn Đã Xóa Trên Facebook Bị Xóa? Cách Khôi Phục Tin Nhắn Đã Bị Xoá Trên Facebook

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

Định dạng bảng lúc đầu không được lưu trong bảng chuyển đổi, như các bạn thấy trong hình ảnh chụp screen ở trên.Nếu có bất kỳ ô trống như thế nào trong bảng nguốn, các ô được chuyển sẽ cất giá trị 0 chũm thế.Bạn cần yếu chỉnh sửa bất kỳ ô nào trong bảng được đổi khác bởi bởi hàm TRANSPOSE nhờ vào rất các vào tài liệu nguồn. Nếu chúng ta cố gắng biến đổ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 giỏi và dễ sử dụng, nhưng chắc chắn rằng nó thiếu thốn tính linh động và vày đó hoàn toàn có thể không yêu cầu là cách tốt nhấ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 phương pháp sử dụng Hàm INDIRECT

Phương pháp biến đổi cột sang sản phẩm và ngược lại này khá như thể với phương pháp trước đó. Điểm dũng mạnh chính của chính nó ưu việt rộng 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ỏ dại trong lấy một ví dụ này để chúng ta cũng có thể tập trung vào quy trình chuyển chứ không hẳn trên dữ liệu cũng chính vì chúng ta đã sử dụng kết hợp các hàm INDIRECT và ADDRESS, đã 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) cùng 5 hàng (1 – 5):


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

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

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

Sao chép bí quyết qua mảng mà bạn muốn đặt tài liệu được gửi như thường lệ bằng cách kéo một chữ thập màu đen nhỏ dại ở góc bên dưới bên cần của một ô hoặc một dải ô sẽ 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 gửi trông cực kỳ 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à 1 trong những vấn đề gì bự cả. Bạn rất có thể dễ dàng phục hồi lại format 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 đề 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 bí quyết linh hoạt hơn để phát triển thành hàng thành cột vào Excel so với hàm TRANSPOSE.

Ưu điểm : bạn có thể dễ dàng chỉnh sửa bất kỳ ô làm sao trong bảng gửi tiếp, mặt khác giữ link với bảng thuở đầu và cập nhật auto bất cứ khi nào bạn thực hiện chuyển đổ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 phục sinh nó, như ví dụ ở trên.

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

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

Hàm INDIRECT, như tên của nó, được áp dụng để con gián tiếp tham khảo một ô. Ví dụ, nếu bạn muốn có cực hiếm của ô B1 vào ô A8, thì các bạn nhập cách làm 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 ngẫu nhiên chuỗi nào thành tham chiếu, gồm 1 chuỗi mà bạn xây dựng bằng cách sử dụng những hàm khác và những giá trị của các ô khác. Và đây đúng là điều chúng ta 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 toàn bộ các phần còn lại:)

Như đã biết, bọn họ đã sử dụng 3 hàm nữa trong bí quyết là – ADDRESS, COLUMN với ROW.

Hàm ADDRESS được sử dụng để lấy địa chỉ của một ô theo số hàng với cột mà bạn chỉ định. Hãy lưu giữ đ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 đồ vật nhất. Như bạn 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 để tôi nhắc bạn về cục bộ công thức mà họ đã thực hiện để biến hóa 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ư các bạn thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) với ROW (A1),và phía trên thực sự là một 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 cùng thứ nhị là cột. Nói biện pháp khác, hàm ADDRESS mang một cột được trả lại vị hàm COLUMN và đổi khác nó thành một hàng, tiếp đến lấy một mặt hàng và đưa nó lịch sự cột, tức 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à số hàng, tương ứng -> Hàm ADDRESS thay đổi cột lịch sự hàng với hàng quý phái cột -> Hàm INDIRECT xuất ra dữ liệu đã được xoay sang tài 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 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 rất có thể học được vào 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 cho bạn hệ thống kiến thức và kỹ năng một bí quyết đầy đủ, đưa ra tiết. Hơn nữa không hề có số lượng giới hạn về thời gian học tập nên chúng ta cũng có thể thoải mái học bất kể lúc nào, tiện lợi tra cứu giúp lại kỹ năng khi cần. Hiện nay hệ thống đang xuất hiện ưu đãi hết sức lớn cho chính mình khi đk tham gia khóa học. Chi tiết xem tại: upes2.edu.vn.Online