Việc chuyển dữ liệu trong Excel là một vấn đề quen thuộc với nhiều người dân dùng. Nhất là sau thời điểm xây dựng một bảng tinh vi thì vấn đề chuyển tài liệu sẽ khiến chúng trở cần rõ ràng, giúp phân tích xuất sắc hơn hoặc để vẽ vật thị.
Bạn đang xem: Chuyển hàng ngang thành dọc trong excel
Trong bài này, học Excel Online sẽ khuyên bảo một vài biện pháp để đổi khác các hàng thành những cột (hoặc cột thành hàng) vào Excel, thực tế hai việc đó là một:). Các chiến thuật này sử dụng được trong tất cả các phiên bạn dạng Excel 2016, 2013, 2010, 2007 với thấp hơn. Bao gồ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ụ nổi bật về việc áp dụng hàm và cách làm Transpose của Excel.
Xem nhanh
Chuyển dữ liệu trong Excel bởi công thức
Chuyển đổi các 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ử chúng ta đã tạo nên bảng tính Excel tựa như như số đông gì chúng ta thấy ở phần trên của hình bên dưới đây. Tên quốc gia được sắp xếp theo các cột, cơ mà danh sách các quốc gia hoàn toàn có thể quá dài, bởi vì vậy họ nên đổi khác các cột thành sản phẩm để cân 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 toàn 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 ô đã chọn bởi 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 home tab > Clipboard group.Chọn ô thứ nhất của vùng mà bạn có nhu cầu sao chép dữ liệu đã gửi đổi.Lưu ý: Điều đặc biệt là bạn chọn 1 ô nằm kế bên phạm vi cất dữ liệu ban đầu của bạn, làm thế nào cho vùng coppy và vùng dán không ông chồng chéo. Ví dụ: nếu như bạn có 4 cột với 10 hàng, khi bạn chuyển địa điểm của bảng thì sẽ có đượ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 color đen nhỏ dại bên dưới nút Paste, với chọn Transpose .Nhấp chuột yêu cầu vào ô đích cùng chọn Paste Specialtừ trình solo ngữ cảnh, sau đó 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 đảm bảo có tham chiếu tuyệt vời (dấu $) trong toàn bộ các ô. Điều này là quan trọng vì các công thức của công ty cũng sẽ tiến hành chuyển và những tham chiếu ô tự động được điều chỉnh.
Như 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 đổi 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ự tốt nếu chỉ cần thay đổ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 hình của dữ liệu thuở đầu của bạn, thêm 1 đối số trong vùng hình ảnh hưởng.
Tuy nhiên, biện pháp làm được nhắc ở trên có hai nhược điểm khiến cho nó chưa hẳn là phương án hoàn hảo cho việc chuyển tiếp dữ liệu trong Excel:
Tùy lựa chọn transpose không thể dùng khi sao chép / dán bảng Excel khá đầy đủ hàm, không giống như phạm vi đơn giản. Trong trường phù hợp này, bạn cần phải sao chép bảng không có tiêu đề cột, hoặc biến hóa nó sang trọng dạng Range (nhấp chuột buộc phải vào ngẫu nhiên ô như thế nào trong bảng và chọn Table > Convert khổng lồ Range từ thực đơn ngữ cảnh).Một hạn chế nghiêm trọng rộng của phương thức này là nó tạo nên một phiên bản sao của dữ liệu lúc đầu của chúng ta và bất cứ lúc nào dữ liệu nguồn nuốm đổi, bạn cần phải lặp lại quá trình và xoay bảng một lần nữa. Chẳng ai mong lãng phí thời gian quý giá của họ để gửi đi gửi lại như thế, đúng không?Làm thay nào để chuyển một bảng được liên kết với dữ liệu ban đầuHãy coi cách chúng ta 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 coppy / dán không còn xa lạ để liên kết những ô vẫn được dịch rời so với dữ liệu ban đầu. Điều rất tốt 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 biến hóa cũng sẽ chuyển đổi và cập nhật cho phù hợp.
Copy hàng bạn muốn chuyển thay đổi sang những cột (hoặc những 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ư đã lý giải trong giải pháp trước đó và chọn Paste Linkở góc dưới phía trái của cửa sổ hộp thoại.Bạn sẽ có kết quả 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 nỗ lực của Excel (bạn rất có thể nhấn Ctrl + H để đưa đến tab Replace ngay).Thay thế toàn bộ các ký tự “=” bởi “xxx” (bạn cũng hoàn toàn có thể thay cố kỉnh nó bằng ngẫu nhiên ký trường đoản cú nào khác không trường thọ ở bất kỳ nơi nào trong tài liệu thực của bạn).Điều này sẽ đổi thay bảng của doanh nghiệp thành một cái gì đó đáng sợ, như chúng ta thấy trong ảnh chụp màn hình hiển thị dưới đây, nhưng mà đừng lo lắng, chỉ việc 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à tiếp nối áp dụng Paste Special > Transpose để biến đổi các cột thành hàng, hoặc những hàng thành cột, tùy thuộc vào yêu cầu của bạn.Và cuối cùng, mở hộp thoại Find and Replace một lần tiếp nữa để đảo ngược sự cố kỉnh đổi, có nghĩa là thay thế toàn bộ “xxx” bằng “=” để khôi phục liên kết đến những ô gốc. Đã xong!Đây là một giải pháp nhanh, dìu dịu và đơn giản, nên không? Hạn chế độc nhất vô nhị của cách này là format của bảng lúc đầu bị mất trong quá trình và bạn sẽ cần phải phục sinh lại nó bằng tay (tôi sẽ chỉ cho mình một cách nhanh chóng để gia công điều này trong trả lời này).
Chuyển tài liệu trong Excel bằng công thứcNếu bạn không tìm kiếm những cách dễ dàng dàng, thì chúng ta cũng có thể sử dụng những công thức để chuyển đổi các cột thành hàng trong bảng Excel của công ty 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ế đặc biệt nhằm chuyển tài liệu trong Excel. Nếu bạn không có khá 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 thủ đúng theo quá trình dưới đây.
Trong ví dụ này, chúng ta sẽ thay đổ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 xác định đúng đắn số hàng cùng cột vào bảng của bạn. Bảng mẫu mã của họ có 7 cột cùng 6 hàng, bao gồm tiêu đề. Hãy ghi ghi nhớ những con số này, và 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 thích hợp của 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 sang làn đường khác dọc và ngang của một dải ô sẽ chọn, bạn cầ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 lấy một ví dụ này, họ đang chọn 6 cột cùng 7 hàng.Nhấn F2 để vào cơ chế chỉnh sửa.Viết hàm TRANSPOSE với nhập dải tài liệu mà bạn có nhu cầu chuyển thay đổi trong ngoặc đơn: = TRANSPOSE (array). Trong trường thích hợp của này, chúng ta 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 cần nhấn Ctrl + Shift + Enter vì đây là một công thức mảng, nhấn Enter nó sẽ không còn hoạt động.
Xem thêm: ✅ Công Thức Tính Hình Vuông, Công Thức Tính Diện Tích Hình Vuông
Đã xong! Dữ liệu của chúng ta được biến đổi và bọn họ có 7 cột được biến hóa thành 6 cột, đúng đắn như họ muốn:
Ưu điểm của hàm TRANSPOSE:
Lợi ích bao gồm của việc sử dụng hàm TRANSPOSE là bảng được chuyển giữ lại kết nối với bảng nguồn và 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ẽ đổi khác theo.
Điểm yếu đuối của hàm TRANSPOSE:
Định dạng bảng thuở đầu không được lưu giữ trong bảng đưa đổi, như bạn thấy trong hình ảnh chụp màn hình ở trên.Nếu có ngẫu nhiên ô trống làm sao trong bảng nguốn, những ô được gửi sẽ chứa giá trị 0 rứa thế.Bạn tất yêu chỉnh sửa ngẫu nhiên ô như thế nào trong bảng được biến đổi bởi vì hàm TRANSPOSE dựa vào rất các vào dữ liệu nguồn. Nếu bạn cố gắng biến hóa 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 tốt và dễ sử dụng, nhưng chắc chắn rằng nó thiếu hụt tính hoạt bát và bởi vì đó có thể không bắt buộc là cách rất tốt để sử dụng trong không ít 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 thay đổi cột sang sản phẩm và ngược lại này khá tương đương với cách trước đó. Điểm mạnh khỏe chính của nó ưu việt rộng hàm Transpose là nó được cho phép chỉnh sửa được vào bảng gửi đổi, mặt khác giữ liên kết với bảng ban đầu.
Chúng ta sẽ áp dụng một bảng bé dại trong lấy một ví dụ này để bạn có thể tập trung vào quy trình chuyển chứ không 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 và ADDRESS, đang hơi phức tạp một chút.
Giả sử các bạn có dữ liệu trong 4 cột (A – D) và 5 mặt hàng (1 – 5):
Nhập công thức tiếp sau đây vào ô phía 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ắt đầu ở một hàng khác với cùng 1 và cột không giống với A, bạn sẽ phải thực hiện 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 phù hợp A1 là ô trên cùng bên bắt buộc của bảng mối cung cấp của bạn. Ngoài ra, xin xem xét việc sử dụng các 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 muốn đặt tài liệu được đưa như thường lệ bằng phương pháp kéo một chữ thập color đen nhỏ ở góc dưới bên nên của một ô hoặc một dải ô vẫn chọn.Được rối đấy! Trong bảng bắt đầu tạo của bạn, toàn bộ các cột được gửi sang hàng.
Tuy nhiên, các ô được chuyển trông siêu mộc mạc và ngô nghê so với dữ liệu ban đầu.
Nhưng chớ thất vọng, phía trên không phải là một vấn đề gì to cả. Bạn có thể dễ dàng phục hồi lại định hình nguồn theo cách này:
Sao chép bảng ban đầu;Chọn bảng đưa đổi;Nhấp chuột buộc phải vào bảng biến hóa và chọn Formatting dưới Paste Options.Tóm lại, INDIRECT dường như là một cách linh hoạt rộng để đổi mới hàng thành cột vào Excel đối với hàm TRANSPOSE.
Ưu điểm : bạn có thể dễ dàng chỉnh sửa bất kỳ ô như thế nào trong bảng gửi tiếp, mặt khác giữ liên kết với bảng ban sơ và cập nhật tự động hóa 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ế : bên trên thực tế, tôi chỉ rất có thể thấy một điểm – câu hỏi đị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ụ sống trên.
Bây giờ bạn đã có cảm giác ra sao về hàm INDIRECT, chúng ta cũng có thể muốn tất cả cái chú ý sâu rộng về đầy đủ gì phương pháp thực sự đang có tác dụng và tại sao họ lại thực hiện nó kết hợp với hàm ADDRESS.
Hàm INDIRECT với ADDRESS – cú pháp cùng logic
Hàm INDIRECT, như tên của nó, được thực hiện để loại gián tiếp tìm hiểu thêm một ô. Ví dụ, nếu bạn muốn có quý giá của ô B1 trong ô A8, thì chúng ta nhập phương pháp này vào 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, 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ác và những giá trị của các ô khác. Và đây đúng là điều chúng ta sẽ tiếp tục 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ư đang biết, họ đã thực hiện 3 hàm nữa trong phương pháp là – ADDRESS, COLUMN và ROW.
Hàm ADDRESS được áp dụng để lấy showroom của một ô theo số hàng với cột mà các bạn chỉ định. Hãy lưu giữ điều này: đầu tiên, hàng, cột thứ hai. Ví dụ: ADDRESS (8,1) trả về $ A $ 8.
Hàm COLUMN trả về số cột của tham chiếu ô đang cho. Ví dụ, =COLUMN(A3) trả về 1, cũng chính vì cột A là cột vật dụng nhất. Như chúng ta cũng có thể dễ dàng đoán được, hàm ROW vận động tương tự cho các hàng.
Và bây giờ, hãy nhằm tôi nhắc chúng ta về toàn cục công thức mà họ đã thực hiện để biến đổi hàng thành những cột trong bảng tính Excel của bọn 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 trong những 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 phương pháp khác, hàm ADDRESS đem một cột được trả lại do hàm COLUMN và biến đổi nó thành một hàng, tiếp đến lấy một mặt hàng và đưa nó sang trọng cột, có nghĩa 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ố hàng, tương xứng -> Hàm ADDRESS chuyển đổi cột lịch sự hàng và hàng sang cột -> Hàm INDIRECT xuất ra dữ 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 tất cả gì ghê gớm cả đúng không?
Rất nhiều kiến thức và kỹ năng phải không nào? toàn bộ những kỹ năng và kiến thức 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 chuyên gia của học tập Excel Online. Đây là khóa học giúp cho bạn hệ thống kỹ năng một biện pháp đầy đủ, đưa ra tiết. Hơn nữa không hề có giới hạn về thời hạn học tập nên bạn cũng có thể thoải mái học bất kể lúc nào, dễ ợt tra cứu vớt lại kiến thức khi cần. Hiện nay hệ thống đang xuất hiện ưu đãi siêu lớn cho mình khi đk tham gia khóa học. Chi tiết xem tại: giasuviet.edu.vn.Online