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

Bạn đang xem: Cách chuyển dữ liệu từ hàng ngang sang hàng dọc

Trong bài bác này, học tập Excel Online sẽ lí giải một vài cách để biến hóa các mặt hàng thành các cột (hoặc cột thành hàng) vào Excel, thực tế hai bài toán đó là một:). Các giải pháp này dùng được trong toàn bộ các phiên bạn dạng Excel 2016, 2013, 2010, 2007 cùng 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 nhất và chuyển ra những ví dụ điển hình về việc sử dụng hàm và phương pháp Transpose của Excel.


Xem nhanh


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

Giả sử chúng ta đã chế tác bảng tính Excel giống như như phần lớn gì các bạn thấy ở vị trí trên của hình dưới đây. Tên giang sơn được sắp xếp theo những cột, tuy thế danh sách những quốc gia có thể quá dài, bởi vì vậy chúng ta nên biến hóa các cột thành hàng để tương 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 có nhu cầu xoay tổng thể bảng, có nghĩa 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 ô sẽ chọn bằng cách nhấn vào phải vào vùng lựa chọn và chọn Copytừ trình đối kháng 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 đã chuyển đổi.

Lưu ý: Điều quan trọng là bạn chọn 1 ô nằm quanh đó phạm vi cất dữ liệu thuở đầu của bạn, sao cho vùng sao chép và vùng dán không chồng chéo. Ví dụ: nếu khách hàng có 4 cột với 10 hàng, khi bạn chuyển địa điểm của bảng thì sẽ có 10 cột với 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 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 đen bé dại bên dưới nút Paste, với chọn Transpose .Nhấp chuột nên 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 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 ô có chứa công thức, phải bảo đảm an toàn có tham chiếu hoàn hảo và tuyệt vời 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 bạn cũng sẽ được chuyển và những tham chiếu ô auto được điều chỉnh.

Như chúng ta vừa thấy, tính năng Transpose của Excel chất nhận được bạn thực hiện thay đổi từ mặ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 phương án này là một cách thực sự xuất sắc 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 coppy các định dạng của dữ liệu lúc đầu của bạn, thêm 1 đối số vào vùng hình ảnh hưởng.


*

*

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

Tùy chọn transpose không thể sử dụng khi coppy / dán bảng Excel vừa đủ hàm, không phải như phạm vi đối kháng 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 cùng chọn  Table > Convert lớn Range từ menu ngữ cảnh).Một tiêu giảm nghiêm trọng rộng của phương thức này là nó tạo nên một bản sao của dữ liệu ban đầu của bạn và bất cứ lúc nào dữ liệu nguồn nuố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 để chuyển đi gửi lại như thế, đúng không?Làm cụ nào để đưa một bảng được links với tài liệu ban đầu

Hãy coi cách chúng ta có thể chuyển mặt hàng sang những cột vào Excel bằng cách sử dụng kỹ thuật sao chép / dán thân thuộc để liên kết các ô đang được di chuyển so với dữ 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ứ khi nào bạn biến hóa dữ liệu trong bảng nguồn, bảng đổi khác cũng sẽ đổi khác và cập nhật cho phù hợp.

Copy hàng bạn muốn chuyển đổi sang các cột (hoặc các cột được chuyển 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 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 đang có công dụng tương từ bỏ như sau:

*

Chọn dữ liệu mới này và mở vỏ hộp thoại Find and Replace cố gắng của Excel (bạn hoàn toàn có thể nhấn Ctrl + H để chuyển đến tab Replace ngay).Thay thế toàn bộ các cam kết tự “=” bởi “xxx” (bạn cũng có thể thay nắ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 ở ngẫu nhiên nơi như thế nào trong tài liệu thực của bạn).

*

Điều này sẽ đổi mới bảng của chúng ta thành một cái nào đấy đáng sợ, như bạn thấy trong hình ảnh chụp màn hình hiển thị dưới đây, tuy vậy đừng lo lắng, chỉ cần thêm hai bước nữa và bạn sẽ đạt được hiệu quả mong muốn.

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

*

Đây là một giải pháp nhanh, thanh thanh và đối chọi giản, phải không? Hạn chế duy nhất của giải pháp này là định hình của bảng ban sơ bị mất trong quy trình và bạn sẽ cần phải phục sinh lại nó thủ công (tôi vẫn chỉ cho chính mình một cách nhanh chóng để làm điều này trong lí giải này).

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

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

Chuyển hàng sang cột trong Excel bằng 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 dữ liệu trong Excel. Nếu 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 đảm bảo bạn vâng lệnh đúng theo công việc dưới đây.

Trong ví dụ như này, họ sẽ biến hóa 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 chính xác số hàng cùng cột vào 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 ghi nhớ những số lượng này, và bọn họ hãy bước đầu chuyển các hàng.

Đếm những ô mà bạn có nhu cầu chuyển đổi. Trong trường vừa lòng của bọn họ 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 dọc và ngang của một dải ô vẫn 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 ví dụ này, họ đang chọn 6 cột và 7 hàng.Nhấn F2 để vào chế độ chỉnh sửa.Viết hàm TRANSPOSE cùng nhập dải dữ 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 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ì đó là một phương pháp mảng, nhấn Enter  nó sẽ không còn hoạt động.

Xem thêm:

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

*

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

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

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

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

Thêm vào đó, cho dù hàm TRANSPOSE giỏi và dễ sử dụng, nhưng chắc hẳn rằng nó thiếu tính linh hoạt và vì đó hoàn toàn có thể không bắt buộc là cách tốt nhất có thể để sử dụng trong tương đối nhiều tình huống.

Chuyển mặt 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 hóa cột sang mặt hàng và ngược lại này khá giống như với bí quyết trước đó. Điểm 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 vào bảng đưa đổi, đôi khi giữ liên kết với bảng ban đầu.

Chúng ta sẽ thực hiện một bảng nhỏ trong lấy một ví dụ này để bạn có thể tập trung vào quy trình chuyển chứ chưa phải trên dữ liệu cũng chính vì chúng ta sẽ sử dụng phối hợp các hàm INDIRECT cùng 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 dưới đây vào ô bên trái của vùng chọn, mang 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 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 vừa lòng A1 là ô trên thuộc bên đề xuất của bảng mối cung cấp của bạn. Ngoài ra, xin chú ý việc sử dụng các tham chiếu ô tuyệt đối hoàn hảo và tương đối.

Sao chép công thức qua mảng mà bạn có nhu cầu đặt tài liệu được chuyển như thường xuyên 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 bắt buộc 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 gửi sang hàng.

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

*

Nhưng chớ thất vọng, phía trên không phải là 1 vấn đề gì phệ cả. Bạn hoàn toàn có thể dễ dàng phục sinh lại định dạng nguồn theo cách này:

Sao chép bảng ban đầu;Chọn bảng gửi đổi;Nhấp chuột yêu cầu vào bảng biến đổi và chọn Formatting dưới Paste Options.

*

Tóm lại, INDIRECT ngoài ra là một bí quyết linh hoạt rộng để đổi thay 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 đưa tiếp, bên cạnh đó giữ liên kết với bảng thuở đầu và cập nhật tự động hóa bất cứ khi nào bạn thực hiện đổi khác 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 – vấ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 sinh nó, như ví dụ sinh sống trên.

Bây giờ bạn đã có cảm giác thế nào về hàm INDIRECT, bạn cũng có thể muốn có cái chú ý sâu hơn về hầu như gì công thức thực sự đang làm 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 cùng 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 xem thêm một ô. Ví dụ, nếu bạn muốn có cực hiếm của ô B1 trong ô A8, thì các bạn nhập cách làm này vào A8 =INDIRECT(“B1”).

*
Nhưng sức khỏe thực của hàm INDIRECT là nó hoàn toàn có thể biến bất kỳ chuỗi nào thành tham chiếu, gồm một chuỗi mà chúng ta xây dựng bằng phương pháp sử dụng những hàm khác và những giá trị của những ô khác. Và đây chính xác là điều chúng ta sẽ cầm lại theo. Nếu bạn theo dõi phần này, các bạn sẽ dễ dàng hiểu tất cả các phần còn lại:)

Như vẫn biết, bọn họ đã thực hiện 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 địa chỉ 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 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, cũng chính vì cột A là cột trang bị nhất. Như bạn có thể dễ dàng đoán được, hàm ROW vận động tương tự cho những hàng.

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

Như chúng ta thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) và ROW (A1),và đây thực sự là 1 mưu mẹo! Hãy nhớ rằng, 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 bí quyết khác, hàm ADDRESS lấy 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ối lấy một sản phẩm và gửi 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 với số hàng, khớp ứng -> Hàm ADDRESS đổi khác cột sang trọng hàng cùng hàng quý phái cột -> Hàm INDIRECT xuất ra tài liệu đã được luân phiên sang tài liệu khác vào bảng tính Excel của bạn. Không có gì gớm ghê 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 vào khóa học EX101 – Excel từ bỏ cơ bạn dạng tới chuyên gia của học Excel Online. Đây là khóa học khiến cho bạn hệ thống kỹ năng và kiến thức một giải pháp đầy đủ, đưa ra tiết. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên chúng ta cũng có thể thoải mái học bất cứ lúc nào, thuận tiện tra cứu giúp lại kiến thức và kỹ năng khi cần. Hiện thời hệ thống đang có ưu đãi khôn cùng lớn cho chính mình khi đk tham gia khóa học. Cụ thể xem tại: intlschool.edu.vn.Online