Hàm chuyển hàng thành cột trong excel

Việc đưa dữ liệu vào Excel là 1 vấn đề thân quen với khá nhiều người tiêu dùng. Nhất là sau khi phát hành một bảng phức hợp thì việc gửi dữ liệu đang khiến bọn chúng trở cần rõ ràng, giúp so với xuất sắc hơn hoặc để vẽ đồ thị.

You watching: Hàm chuyển hàng thành cột trong excel

Trong bài xích này, Học Excel Online đang hướng dẫn một vài ba phương pháp để thay đổi những mặt hàng thành các cột (hoặc cột thành hàng) vào Excel, thực chất hai việc sẽ là một:). Các phương án này sử dụng được vào tất cả những phiên bản Excel năm 2016, 2013, 2010, 2007 và rẻ rộng. Bao gồm nhiều năng lực rất có thể xảy ra, giải thích các lỗi nổi bật độc nhất vô nhị với đưa ra những ví dụ nổi bật về vấn đề thực hiện hàm với bí quyết Transpose của Excel.


Xem nhanh


Chuyển tài liệu vào Excel bởi công thức
Chuyển thay đổi những sản phẩm thành những cột trong Excel bằng phương pháp thực hiện Paste Special (Transpose)

Giả sử bạn sẽ sản xuất bảng tính Excel tương tự giống như các gì các bạn thấy ở chỗ trên của hình sau đây. Tên non sông được sắp xếp theo các cột, nhưng mà list các đất nước hoàn toàn có thể vượt lâu năm, bởi vì vậy chúng ta cần biến đổi những cột thành sản phẩm để tương xứng với màn hình:

*

Chọn mặt hàng và cột bạn có nhu cầu đưa. Nếu bạn muốn chuyển phiên toàn cục bảng, tức là tất cả những ô bao gồm dữ liệu vào bảng tính, nhấn Ctrl + Home và Ctrl + Shift + End .Sao chnghiền ô đang lựa chọn bằng cách nhấp chuột phải vào vùng lựa chọn và chọn Copytrường đoản cú trình đối chọi ngữ chình họa hoặc nhấn Ctrl + C hoặc nhấp vào nút copy trên tab Home tab > Clipboard group.Chọn ô thứ nhất của vùng cơ mà bạn muốn xào luộc dữ liệu sẽ biến hóa.

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

Dán tài liệu được coppy 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ở vỏ 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ỏ mặt dưới nút Paste, cùng chọn Transpose .Nhấp con chuột nên vào ô đích cùng chọn Paste Specialtừ bỏ trình solo ngữ chình họa, tiếp nối chọn Transpose .Và sau cuối, phương pháp nkhô nóng độc nhất là nhấn Ctrl + Alt + V , chọn Transposevà nhấn OK .

*

Lưu ý: Nếu chúng ta dịch chuyển những ô có cất cách làm, buộc phải đảm bảo bao gồm tham mê chiếu tuyệt đối hoàn hảo (dấu $) vào toàn bộ những ô. Vấn đề này là cần thiết do những phương pháp của bạn cũng sẽ được gửi với những ttê mê chiếu ô auto được kiểm soát và điều chỉnh.

Nlỗi chúng ta vừa thấy, anh tài Transpose của Excel cho phép bạn triển khai thay đổi tự mặt hàng quý phái cột (hoặc cột thanh lịch hàng) chỉ trong vài giây. Tôi tin rằng phương án này là 1 phương pháp đích thực xuất sắc trường hợp chỉ việc biến hóa bảng tính một lượt duy nhất. Pmùi hương pháp này cũng coppy các định dạng của tài liệu ban sơ của người tiêu dùng, thêm 1 đối số trong vùng ảnh hưởng.


*

*

Tuy nhiên, bí quyết làm được kể nghỉ ngơi trên có nhị nhược điểm khiến cho nó không phải là giải pháp hoàn hảo và tuyệt vời nhất mang lại Việc chuyển tiếp tài liệu trong Excel:

Tùy chọn transpose quan trọng dùng Lúc coppy / dán bảng Excel tương đối đầy đủ hàm, không như phạm vi đơn giản dễ dàng. Trong ngôi trường phù hợp này, bạn cần phải coppy bảng không có tiêu đề cột, hoặc đổi khác nó thanh lịch dạng Range (bấm chuột cần vào bất kỳ ô như thế nào vào bảng và chọn  Table > Convert to lớn Range tự menu ngữ cảnh).Một giảm bớt cực kỳ nghiêm trọng rộng của phương pháp này là nó tạo nên một phiên bản sao của dữ liệu thuở đầu của công ty cùng bất kể lúc nào dữ liệu mối cung cấp biến đổi, bạn cần phải tái diễn quy trình cùng chuyển phiên bảng một lần tiếp nữa. Chẳng ai hy vọng lãng phí thời gian quý giá của mình nhằm đưa đi gửi lại như vậy, đúng không?Làm vậy như thế nào nhằm gửi một bảng được links với dữ liệu ban đầu

Hãy coi giải pháp chúng ta cũng có thể ship hàng lịch sự các cột trong Excel bằng cách áp dụng nghệ thuật xào luộc / dán không còn xa lạ nhằm link các ô đã có dịch 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ứ bao giờ bạn chuyển đổi tài liệu trong bảng mối cung cấp, bảng đổi khác cũng sẽ biến hóa với cập nhật cho phù hợp.

Copy sản phẩm bạn có nhu cầu biến đổi sang trọng những cột (hoặc các cột được chuyển 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ư đang lý giải vào phương pháp trước đó với chọn Paste Linkở góc bên dưới phía trái của cửa sổ hộp thoại.

*

Bạn sẽ có công dụng tương tự nhỏng sau:

*

Chọn dữ liệu bắt đầu này cùng msống vỏ hộp thoại Find và Replace gắng của Excel (bạn có thể nhấn Ctrl + H để gửi đến tab Replace ngay).Txuất xắc ráng toàn bộ các ký từ “=” bởi “xxx” (chúng ta cũng có thể thay thế nó bởi bất kỳ cam kết từ nào khác ko lâu dài làm việc ngẫu nhiên chỗ nào trong dữ liệu thực của bạn).

*

Vấn đề này đã biến chuyển bảng của người tiêu dùng thành một chiếc nào đó đáng sợ, như chúng ta thấy trong hình ảnh chụp screen sau đây, nhưng mà đừng lo ngại, chỉ cần thêm nhì bước nữa và bạn sẽ đạt được kết quả mong ước.

Sao chxay bảng bằng quý hiếm “xxx”, với sau đó áp dụng Paste Special > Transpose  để đổi khác những cột thành mặt hàng, hoặc những mặt hàng thành cột, tùy nằm trong vào nhu cầu của người sử dụng.Và sau cuối, mở vỏ hộp thoại Find and Replace một lần nữa để đảo ngược sự chuyển đổi, có nghĩa là sửa chữa thay thế toàn bộ “xxx” bởi “=” nhằm Phục hồi link cho các ô cội. Đã xong!

*

Đây là 1 chiến thuật nkhô giòn, thanh thanh cùng dễ dàng và đơn giản, đề xuất không? Hạn chế duy nhất của bí quyết này là định hình của bảng thuở đầu bị mất trong quá trình và bạn sẽ rất cần được Phục hồi lại nó bằng tay thủ công (tôi vẫn chỉ cho mình một biện pháp lập cập để gia công điều đó trong trả lờ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 tìm các bí quyết dễ dàng, thì bạn cũng có thể sử dụng những cách làm nhằm đổi khác những cột thành mặt hàng trong bảng Excel của khách hàng và trở lại.

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

Nlỗi tên gọi của nó, hàm Transpose có phong cách thiết kế đặc biệt quan trọng nhằm đưa tài liệu trong Excel. Nếu chúng ta ko có tương đối nhiều kinh nghiệm cùng với các bí quyết Excel nói tầm thường, và những hàm array nói riêng, hãy đảm bảo bạn tuân thủ phù hợp công việc sau đây.

Trong ví dụ này, chúng ta 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 khẳng định đúng chuẩn số hàng với cột trong bảng của doanh nghiệp. Bảng mẫu mã của họ có 7 cột và 6 hàng, bao gồm tiêu đề. Hãy ghi ghi nhớ đông đảo con số này, cùng chúng ta hãy bắt đầu gửi các sản phẩm.

Đếm những ô nhưng bạn có nhu cầu đổi khác. Trong trường thích hợp của bọn họ là 7 cột cùng 6 hàng.Chọn một phạm vi trống. Vì hàm TRANSPOSE chuyển làn phân cách dọc với ngang của một dải ô vẫn chọn, bạn cần phải lựa chọn một vùng có cùng số hàng với cột nlỗi bảng mối cung cấp của người tiêu dùng. Trong ví dụ này, chúng ta đã lựa chọn 6 cột và 7 sản phẩm.Nhấn F2 để vào chế độ sửa đổi.Viết hàm TRANSPOSE cùng nhập dải dữ liệu nhưng mà bạn muốn biến hóa vào ngoặc đơn: = TRANSPOSE (array). Trong ngôi 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 sử dụng tmê man chiếu tốt đối trong hàm TRANSPOSE.

*

Nhấn Ctrl + Shift + Enter .

Lưu ý: Bạn buộc phải nhấn Ctrl + Shift + Enter vì đó là một cách làm mảng, nhấn Enter  nó sẽ không còn hoạt động.

See more: Bố Mẹ Ca Sĩ Cẩm Ly Là Ai - Ba Chị Em Cẩm Ly Đưa Bố Mẹ Đi Du Lịch

Đã xong! Dữ liệu của bọn họ được chuyển đổi và bọn họ có 7 cột được biến đổi thành 6 cột, đúng đắn nhỏng chúng ta 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 chuyển lưu lại liên kết cùng với bảng mối cung cấp cùng bất kể khi nào bạn đổi khác dữ liệu nguồn, bảng chuyến qua cũng biến thành chuyển đổi theo.

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

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

Thêm vào đó, mặc dù hàm TRANSPOSE tốt với dễ dàng thực hiện, mà lại chắc hẳn rằng nó thiếu hụt tính linh hoạt và vì thế có thể không phải là biện pháp tốt nhất có thể để thực hiện trong tương đối nhiều tình huống.

Chuyển Hàng thành cột / cột thành mặt hàng bằng phương pháp thực hiện Hàm INDIRECT

Phương pháp đổi khác cột quý phái sản phẩm với ngược trở lại này hơi kiểu như với phương pháp trước đó. Điểm dũng mạnh thiết yếu của chính nó ưu việt hơn hàm Transpose là nó có thể chấp nhận được chỉnh sửa được vào bảng đổi khác, mặt khác duy trì liên kết với bảng ban sơ.

Chúng ta đang sử dụng một bảng nhỏ tuổi vào ví dụ này nhằm chúng ta cũng có thể tập trung vào quá trình chuyển chđọng chưa hẳn trên dữ liệu bởi vì chúng ta đã thực hiện kết hợp những hàm INDIRECT và ADDRESS, sẽ tương đối phức tạp một chút.

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

*

Nhập phương pháp sau đây vào ô bên trái của vùng lựa 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 chúng ta ban đầu ở 1 mặt hàng không giống với một và cột không giống với A, bạn sẽ nên thực hiện một bí quyết phức hợp rộng 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 mặt bắt buộc của bảng mối cung cấp của doanh nghiệp. Ngoài ra, xin chú ý bài toán thực hiện các tham mê chiếu ô tuyệt vời nhất và kha khá.

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

*

Được rối đấy! Trong bảng mới chế tạo của công ty, tất cả những cột được gửi quý phái mặt hàng.

Tuy nhiên, những ô được chuyển trông siêu mộc mạc cùng ngô ngớm so với tài liệu ban sơ.

*

Nhưng đừng thuyệt vọng, trên đây không phải là 1 trong vụ việc gì mập cả. Quý khách hàng có thể thuận tiện phục sinh lại format mối cung cấp Theo phong cách này:

Sao chnghiền bảng ban đầu;Chọn bảng gửi đổi;Nhấp con chuột đề nghị vào bảng thay đổi cùng chọn Formatting dưới Paste Options.

*

Tóm lại, INDIRECT có lẽ là một trong những giải pháp linc hoạt hơn nhằm đổi thay sản phẩm thành cột vào Excel so với hàm TRANSPOSE.

Ưu điểm : chúng ta cũng có thể thuận lợi chỉnh sửa bất kỳ ô như thế nào vào bảng chuyển tiếp, đồng thời duy trì liên kết với bảng lúc đầu và update tự động hóa bất kể khi nào chúng ta triển khai đổi khác tài liệu nguồn.

Những hạn chế : trên thực tế, tôi chỉ rất có thể thấy một điểm – việc định hình tài liệu ko được lưu lại. Mặc cho dù, chúng ta có thể lập cập khôi phục nó, nhỏng ví dụ ở bên trên.

Bây tiếng bạn đã sở hữu cảm giác thế nào về hàm INDIRECT, chúng ta cũng có thể hy vọng bao gồm cái nhìn sâu rộng về các gì bí quyết đích thực đã làm cùng tại vì sao bọn họ 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ỏng thương hiệu của chính nó, được sử dụng để loại gián tiếp tìm hiểu thêm một ô. lấy ví dụ, ví như bạn có nhu cầu có giá trị của ô Bmột trong các ô A8, thì các bạn nhập phương pháp này trong A8 =INDIRECT(“B1”).

*
Nhưng sức mạnh thực của hàm INDIRECT là nó có thể trở thành ngẫu nhiên chuỗi làm sao thành tsay đắm chiếu, gồm 1 chuỗi nhưng chúng ta thi công bằng cách áp dụng các hàm khác cùng những quý giá của các ô không giống. Và phía trên đúng là điều chúng ta sẽ làm tiếp theo sau. Nếu bạn theo dõi và quan sát phần này, bạn sẽ dễ dàng gọi toàn bộ những phần còn lại:)

Nhỏng vẫn biết, chúng ta đang sử dụng 3 hàm nữa vào công thức là – ADDRESS, COLUMN và ROW.

Hàm ADDRESS được sử dụng để mang shop của một ô theo số hàng với cột mà lại bạn hướng dẫn và chỉ định. Hãy lưu giữ điều này: trước tiên, hàng, cột sản phẩm công nghệ hai. Ví dụ: ADDRESS (8,1) trả về $ A $ 8.

Hàm COLUMN trả về số cột của tmê mệt chiếu ô đã cho. ví dụ như, =COLUMN(A3) trả về 1, bởi vì cột A là cột đầu tiên. Nhỏng chúng ta cũng có thể tiện lợi đoán được, hàm ROW hoạt động tương tự như cho các sản phẩm.

See more: Cách Chèn Và Bỏ Ngắt Trang Trong Excel 2007 Mới Nhất 2020, Hướng Dẫn Cách Xóa Ngắt Trang Trong Excel

Và bây giờ, hãy nhằm tôi kể các bạn về toàn bộ công thức mà bọn họ đã sử dụng nhằm chuyển đổi mặt 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ỏng chúng ta thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) và ROW (A1),cùng trên đây đích thực là 1 trong mưu mẹo! Hãy hãy nhớ là, hàm ADDRESS diễn giải số thứ nhất dưới dạng hàng và thứ nhì là cột. Nói cách không giống, hàm ADDRESS mang một cột được trả lại vày hàm COLUMN và chuyển đổi nó thành một sản phẩm, tiếp đến đem một sản phẩm cùng đưa nó sang cột, tức là hoán đổi những hàng thành các cột.

Hàm COLUMN và ROW trả lại số cột cùng số sản phẩm, tương ứng -> Hàm ADDRESS chuyển đổi cột sang sản phẩm cùng sản phẩm sang cột -> Hàm INDIRECT xuất ra tài liệu đã có được luân chuyển sang trọng dữ liệu khác vào bảng tính Excel của chúng ta. Không có gì gớm ghê cả đúng không?

Rất những kiến thức nên không nào? Toàn cỗ rất nhiều kỹ năng và kiến thức này các bạn đông đảo có thể học tập được trong 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 đào tạo giúp bạn hệ thống kỹ năng một biện pháp không thiếu, cụ thể. mà còn không còn có giới hạn về thời hạn học tập đề nghị bạn có thể dễ chịu và thoải mái học tập bất cứ thời gian như thế nào, dễ dãi tra cứu vớt lại kỹ năng và kiến thức Lúc cần. Hiện nay hệ thống đang sẵn có chiết khấu rất to lớn cho mình Lúc ĐK tsay đắm gia khóa đào tạo. Chi huyết coi tại: twrising.com.Online


Chuyên mục: Blog