Việc chuyển dữ liệu trong Excel là một vấn đề quen thuộc với không ít người dùng. Nhất là sau khoản thời gian xây dựng một bảng tinh vi thì câu hỏi 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ị.

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

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


Xem nhanh


Chuyển dữ liệu trong Excel bằng công thức
Chuyển đổi những 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ư như phần đông gì chúng ta thấy ở trong phần trên của hình dưới đây. Tên đất nước được thu xếp theo những cột, nhưng lại danh sách những quốc gia rất có thể quá dài, bởi vì vậy chúng ta nên biến đổi các cột thành mặt hàng để tương 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 cục bộ 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 ô đang 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 đối chọi 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 muốn sao chép tài liệu đã gửi đổi.

Lưu ý: Điều đặc trưng là bạn lựa chọn 1 ô nằm ko kể phạm vi đựng dữ liệu ban sơ của bạn, làm thế nào cho vùng xào luộc và vùng dán không chồng chéo. Ví dụ: nếu như bạn có 4 cột với 10 hàng, khi chúng ta chuyển vị trí 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 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 trang chủ tab > Clipboard group,, nhấp vào mũi tên màu đen nhỏ tuổi bên dưới nút Paste, cùng chọn Transpose .Nhấp chuột đề nghị vào ô đích và 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 + alternative text + V , chọn Transposevà nhấn OK .

*

Lưu ý: Nếu bạn di chuyển các ô bao gồm chứa công thức, phải bảo vệ có tham chiếu tuyệt vời nhất (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 người sử dụng cũng sẽ tiến hành chuyển và những tham chiếu ô tự động hóa được điều chỉnh.

Như bạn vừa thấy, tác dụng Transpose của Excel chất nhận được bạn thực hiện thay đổi từ hàng sang cột (hoặc cột sang trọng hàng) chỉ vào vài giây. Tôi tin rằng giải pháp này là một trong cách thực sự tốt nếu chỉ cần chuyển đổi 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 ban sơ của bạn, thêm 1 đối số vào vùng hình ảnh hưởng.


*

*

Tuy nhiên, bí quyết làm được kể ở trên có hai nhược điểm khiến cho nó chưa phải là phương án 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ể dùng khi coppy / dán bảng Excel không thiếu hàm, không như phạm vi đối chọi giản. Trong trường hòa 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 biến hóa nó sang trọng dạng Range (nhấp chuột đề nghị vào ngẫu nhiên ô như thế nào trong bảng với chọn  Table > Convert to lớn Range từ menu ngữ cảnh).Một giảm bớt nghiêm trọng rộng 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 cố gắng đổi, bạn phải lặp lại quy trình và chuyển phiên bảng một lượt nữa. Chẳng ai mong muốn lãng phí thời hạn quý giá của họ để đưa đi chuyển lại như thế, đúng không?Làm nuốm nào để gửi một bảng được liên kết với dữ liệu ban đầu

Hãy xem cách chúng ta có thể chuyển sản phẩm sang những cột vào Excel bằng phương pháp sử dụng kỹ thuật sao chép / dán thân quen để liên kết những ô đang được dịch rời so với dữ liệu ban đầu. Điều cực tốt khi áp dụng cách này là bất cứ lúc nào bạn biến hóa dữ liệu vào bảng nguồn, bảng đổi khác 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 và 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 bí quyết 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 đã có hiệu quả tương từ như sau:

*

Chọn tài liệu mới này với mở vỏ hộp thoại Find và Replace nuốm 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ý tự “=” bởi “xxx” (bạn cũng có thể thay cố gắng nó bằng ngẫu nhiên ký tự nào không giống không lâu dài ở bất kỳ nơi như thế nào trong dữ liệu thực của bạn).

*

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

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

*

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

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

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

Chuyển hàng sang cột vào Excel bởi hàm TRANSPOSE

Như tên gọi của nó, hàm Transpose được thiết kế theo phong cách đặc biệt để chuyển dữ liệu trong Excel. Nếu chúng ta 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 vệ bạn tuân hành đúng theo quá trình dưới đây.

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

Đếm các ô mà bạn có nhu cầu chuyển đổi. Trong trường vừa lòng của họ là 7 cột và 6 hàng.Chọn một phạm vi trống. Vì hàm TRANSPOSE chuyển qua làn đường khác dọc cùng ngang của một dải ô đã chọn, bạn phải chọn một vùng gồm cùng số hàng với cột như bảng nguồn của bạn. Trong ví dụ như này, bọn họ đang lựa chọn 6 cột với 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 có nhu cầu chuyển thay đổi trong ngoặc đơn: = TRANSPOSE (array). Trong trường hợp của này, họ 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.

Đã xong! Dữ liệu của chúng ta được chuyển đổi và bọn họ có 7 cột được thay đổ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 chủ yếu 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ẽ biến đổi theo.

Đ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 đưa đổi, như bạn thấy trong hình ảnh chụp screen ở trên.Nếu có ngẫu nhiên ô trống như thế nào trong bảng nguốn, các ô được gửi sẽ chứa giá trị 0 cầm thế.Bạn chẳng thể chỉnh sửa ngẫu nhiên ô làm sao trong bảng được biến hóa bởi bởi vì hàm TRANSPOSE nhờ vào rất nhiều vào tài liệu nguồn. Nếu bạn cố gắng biến đổi giá trị của ô, Excel đang 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 nó thiếu tính linh động và do đó có thể không đề xuất là cách cực tốt để sử dụng trong vô số tình huống.

Chuyển mặt 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 đổi khác cột sang hàng và ngược lại này khá giống với biện pháp trước đó. Điểm bạo gan 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, đồng thời giữ liên kết với bảng ban đầu.

Chúng ta sẽ thực hiện một bảng nhỏ tuổ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 hẳn trên dữ liệu cũng chính vì chúng ta đang sử dụng kết hợp các hàm INDIRECT và ADDRESS, đã hơi tinh vi một chút.

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

*

Nhập công thức sau đây vào ô phía 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 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 cần của bảng mối cung cấp của bạn. Ngoài ra, xin để ý việc sử dụng những tham chiếu ô hoàn hảo 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ư hay lệ bằng cách kéo một chữ thập color đen nhỏ ở góc bên dưới bên đề nghị của một ô hoặc một dải ô đã chọn.

*

Được rối đấy! Trong bảng new 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 hết sức mộc mạc với ngô nghê so với dữ liệu ban đầu.

*

Nhưng đừng thất vọng, đây không phải là 1 vấn đề gì khủng cả. Bạn rất có thể dễ dàng phục sinh lại định dạng nguồn theo phong cách này:

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

*

Tóm lại, INDIRECT ngoài ra là một biện pháp linh hoạt hơn để đổi mới 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ỳ ô 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 tự động hóa bất cứ lúc nào bạn thực hiện chuyển đổ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 – việc đị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ụ sống trên.

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

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

Hàm INDIRECT, như thương hiệu 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 trong ô A8, thì chúng ta 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ó rất có thể biến ngẫu nhiên chuỗi như thế nào thành tham chiếu, 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à những giá trị của các ô khác. Và đây đúng là điều bọn họ 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 toàn bộ các phần còn lại:)

Như đã biết, chúng ta đã thực hiện 3 hàm nữa trong bí quyết là – ADDRESS, COLUMN và 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à cột mà các bạn chỉ định. Hãy 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 ô đã cho. Ví dụ, =COLUMN(A3) trả về 1, chính vì cột A là cột máy nhất. Như chúng ta có thể dễ dàng đoán được, hàm ROW chuyển động tương tự cho các hàng.

Xem thêm: Giọng Ải Giọng Ai 2 - Giọng Ải Giọng Ai Mùa 2

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

Như bạn thấy, vào hàm ADDRESS, chúng ta chỉ định COLUMN (A1) và ROW (A1),và đây thực sự là 1 trong những mưu mẹo! Hãy lưu giữ rằng, hàm ADDRESS diễn giải số thứ nhất dưới dạng hàng với thứ nhị là cột. Nói phương pháp khác, hàm ADDRESS rước một cột được trả lại vày hàm COLUMN và thay đổi nó thành một hàng, sau đó lấy một sản phẩm và đưa nó quý phái cột, tức là hoán đổi các hàng thành những cột.

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

Rất nhiều kỹ năng và kiến thức phải ko nào? toàn cục những kỹ năng này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel tự cơ phiên bản tới siêng gia của học Excel Online. Đây là khóa học khiến cho bạn hệ thống kỹ năng một phương pháp đầy đủ, bỏ ra tiết. Hơn nữa không còn 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, thuận tiện tra cứu vãn lại kiến thức và kỹ năng khi cần. Bây giờ hệ thống đang xuất hiện ưu đãi khôn cùng lớn cho chính mình khi đk tham gia khóa học. Chi tiết xem tại: vietmac.com.vn.Online