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

Bạn đang xem: Chuyển từ dòng sang cột trong excel

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


Xem nhanh


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

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

*

Chọn hàng cùng cột bạn muốn chuyển. Nếu bạn muốn xoay tổng thể 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 ô vẫn chọn bởi cách nhấp chuột 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 tài liệu đã đưa đổi.

Lưu ý: Điều quan trọng là bạn chọn một ô nằm quanh đó phạm vi đựng dữ liệu ban sơ của bạn, sao cho vùng coppy và vùng dán không ck chéo. Ví dụ: nếu 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 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 rất 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 bé dại bên dưới nút Paste, cùng chọn Transpose .Nhấp chuột đề xuất vào ô đích và chọn Paste Specialtừ trình đối kháng ngữ cảnh, tiếp đến chọn Transpose .Và cuối cùng, cách sớm nhất 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 đảm bảo an toàn có tham chiếu hoàn hảo 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à các tham chiếu ô tự động hóa được điều chỉnh.

Như các bạn vừa thấy, anh tài Transpose của Excel chất nhận được bạn thực hiện biến hóa từ sản phẩm sang cột (hoặc cột lịch sự hàng) chỉ vào vài giây. Tôi tin rằng phương án này là 1 cách thực sự xuất sắc nếu chỉ cần biến đổi bảng tính một lần duy nhất. Phương pháp này cũng sao chép các format của dữ liệu lúc đầu của bạn, thêm 1 đối số trong vùng hình ảnh hưởng.


*

*

Tuy nhiên, cách làm được kể ở bên 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 câu hỏi chuyển tiếp tài liệu trong Excel:

Tùy chọn transpose không thể cần sử dụng khi xào nấu / dán bảng Excel đầy đủ hàm, không như phạm vi đối chọi giản. Trong trường thích hợp này, bạn cần phải sao chép bảng không có tiêu đề cột, hoặc thay đổi nó sang trọng dạng Range (nhấp chuột đề xuất vào ngẫu nhiên ô nào trong bảng cùng chọn  Table > Convert to Range từ menu ngữ cảnh).Một giảm bớt nghiêm trọng hơn của phương thức này là nó tạo ra một bản sao của dữ liệu ban sơ của chúng ta và bất cứ khi nào dữ liệu nguồn rứa đổi, bạn cầ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 mỏi lãng phí thời gian quý giá của mình để chuyển đi gửi lại như thế, đúng không?Làm vậy nào để chuyển một bảng được links với tài liệu ban đầu

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

Copy hàng bạn có nhu cầu chuyển thay đổi sang những cột (hoặc những 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 giải pháp 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 vẫn có hiệu quả tương từ như sau:

*

Chọn tài liệu mới này và mở hộp thoại Find và Replace nuốm của Excel (bạn có thể nhấn Ctrl + H để chuyển đế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 cố gắng nó bằng ngẫu nhiên ký tự nào không giống không vĩnh cửu ở ngẫu nhiên nơi như thế nào trong dữ liệu thực của bạn).

*

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

Sao chép bảng bằng giá trị “xxx”, và kế tiếp áp dụng Paste Special > Transpose  để chuyển đổi các cột thành hàng, hoặc các 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 lần nữa để hòn đảo ngược sự cố đổi, tức là thay thế tất cả “xxx” bằng “=” nhằm khôi phục link đến những ô gốc. Đã xong!

*

Đây là một giải pháp nhanh, nhẹ nhàng và đối kháng giản, đề nghị không? Hạn chế tốt nhất của giải pháp này là định hình của bảng thuở đầu bị mất trong quy trình và các bạn sẽ cần phải khôi phục lại nó bằng tay (tôi đang chỉ cho bạn một cách nhanh chóng để gia công đ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, thì chúng ta cũng có thể sử dụng các công thức để biến đổi các cột thành mặt hàng trong bảng Excel của người tiêu dùng và ngược lại.

Chuyển mặt hàng sang cột vào Excel bằng 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 nhằm chuyển dữ liệu trong Excel. Nếu bạn không có khá nhiều kinh nghiệm với những 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 quá trình dưới đây.

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

Đếm những ô mà bạn có nhu cầu chuyển đổi. Trong trường thích hợp của chúng ta là 7 cột và 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 cùng ngang của một dải ô đã chọn, bạn phải chọn một vùng bao gồm cùng số hàng với cột như bảng nguồn của bạn. Trong lấy ví dụ này, họ đang lựa chọn 6 cột và 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 vừa lòng của này, chúng ta 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 nên nhấn Ctrl + Shift + Enter vì đấy là một cách làm mảng, nhấn Enter  nó sẽ không còn hoạt động.

Xem thêm: Mẫu Biên Bản Nghiệm Thu Hoàn Thành Công Trình, Mẫu Biên Bản Nghiệm Thu Công Trình

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

*

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

Lợi ích chủ yếu của việc áp dụng hàm TRANSPOSE là bảng được đưa giữ lại kết nối với bảng nguồn với bất cứ lúc nào bạn chuyển đổi dữ liệu nguồn, bảng sự chuyển tiếp giữa cũng sẽ biến hóa theo.

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

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

Chuyển hàng 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 hàng và trái lại này khá như là với bí quyết trước đó. Điểm táo bạo chính của chính nó ưu việt hơn hàm Transpose là nó cho phép 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ỏ tuổi trong lấy ví dụ như 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 chính vì chúng ta đang sử dụng phối hợp các hàm INDIRECT cùng ADDRESS, đang hơi phức tạp một chút.

Giả sử các bạn có tài liệu trong 4 cột (A – D) với 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 dữ liệu của bạn bước đầu ở một mặt hàng khác với cùng một và cột không giống với A, các bạn sẽ phải thực hiện 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 phải của bảng nguồn của bạn. Ngoài ra, xin để ý việc sử dụng những tham chiếu ô hoàn hảo và tuyệt vời nhất và tương đối.

Sao chép bí quyết qua mảng mà bạn có nhu cầu đặt dữ liệu được gửi như hay lệ bằng cách kéo một chữ thập màu đen nhỏ ở góc dưới bên phải của một ô hoặc một dải ô sẽ chọn.

*

Được rối đấy! Trong bảng new tạo của bạn, tất cả các cột được đưa 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 chớ thất vọng, đây không phải là 1 vấn đề gì bự cả. Bạn 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 đư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 hình như là một bí quyết linh hoạt hơn để biến đổi hàng thành cột vào Excel đối với hàm TRANSPOSE.

Ưu điểm : chúng ta có thể dễ dàng chỉnh sửa ngẫu nhiên ô như thế nào trong bảng chuyển tiếp, đôi khi 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 biến hóa 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 hồi nó, như ví dụ nghỉ ngơi trên.

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

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

Hàm INDIRECT, như thương hiệu của nó, được thực hiện để con gián tiếp tham khảo một ô. Ví dụ, nếu bạn muốn có giá trị của ô B1 vào ô A8, thì chúng ta 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ó hoàn toàn có thể biến ngẫu nhiên chuỗi làm sao thành tham chiếu, bao gồm một chuỗi mà các bạn xây dựng bằng phương pháp sử dụng các hàm không giống và các giá trị của các ô khác. Và đây chính xác là điều họ sẽ cầm lạ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ư sẽ biết, họ đã áp dụng 3 hàm nữa trong phương pháp là – ADDRESS, COLUMN cùng ROW.

Hàm ADDRESS được sử dụng để lấy add của một ô theo số hàng với cột mà các bạn chỉ định. Hãy nhớ điều này: đầu tiên, hàng, cột lắp thêm 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, chính vì cột A là cột lắp thêm 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.

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

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

Hàm COLUMN và ROW trả lại số cột và số hàng, tương xứng -> Hàm ADDRESS biến hóa cột quý phái hàng và hàng sang cột -> Hàm INDIRECT xuất ra tài liệu đã được luân chuyể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? toàn bộ những kỹ năng này chúng ta đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chăm gia của học Excel Online. Đây là khóa học khiến cho bạn hệ thống kiến thức một giải pháp đầy đủ, chi tiết. Rộng nữa không còn có giới hạn về thời gian học tập nên chúng ta có thể thoải mái học bất kể lúc nào, dễ dàng tra cứu giúp lại kiến thức và kỹ năng khi cần. Hiện giờ hệ thống đang xuất hiện ưu đãi siêu lớn cho chính mình khi đk tham gia khóa học. Cụ thể xem tại: upes2.edu.vn.Online