CHUYỂN DỮ LIỆU TỪ CỘT SANG HÀNG TRONG EXCEL

Việc chuyển tài liệu trong Excel là một trong những vấn đề thân quen với đa số người dùng. Nhất là sau khi xây dựng một bảng phức hợp thì bài toán chuyển dữ liệu sẽ khiến chúng trở phải rõ ràng, góp phân tích tốt hơn hoặc để vẽ đồ gia dụng thị.

Bạn đang xem: Chuyển dữ liệu từ cột sang hàng trong excel

Bạn đang xem: Chuyển tài liệu từ cột sang mặt hàng trong excel

Trong bài xích này, học tập Excel Online sẽ chỉ dẫn một vài phương pháp để biến đổi các sản phẩm 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 chiến thuật này dùng được trong tất cả 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, lý giải các lỗi nổi bật nhất và đưa ra các ví dụ điển hình nổi bật về việc sử dụng hàm và phương pháp Transpose của Excel.

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ử bạn đã tạo thành bảng tính Excel giống như như rất nhiều gì các bạn thấy ở trong phần trên của hình dưới đây. Tên giang sơn được sắp xếp theo các cột, dẫu vậy danh sách những quốc gia rất có thể quá dài, bởi vì vậy họ nên đổi khác các cột thành mặt hàng để phù hợp với màn hình:

*

*

*

Chọn hàng và cột bạn có nhu cầu chuyển. Nếu bạn muốn xoay toàn cục 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ằng cách bấm chuột phải vào vùng chọn và chọn Copytừ trình solo 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 ô đầu 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 quan trọng là bạn lựa chọn 1 ô nằm bên cạnh phạm vi đựng dữ liệu lúc đầu của bạn, làm sao để cho vùng coppy và vùng dán không ông xã chéo. Ví dụ: nếu như 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ẽ sở hữu được 10 cột với 4 hàng.

Dán dữ 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, với chọn Transpose .Nhấp chuột nên 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 sớm nhất là nhấn Ctrl + alt + V , chọn Transposevà nhấn OK .


*

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

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

Tuy nhiên, bí quyết làm được đề cập ở trên có hai nhược điểm khiến nó không hẳn là giải pháp 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 luộc / dán bảng Excel tương đối đầy đủ hàm, không giống như phạm vi đối kháng 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 đổi khác nó thanh lịch dạng Range (nhấp chuột buộc phải vào bất kỳ ô nào trong bảng và chọn  Table > Convert to lớn Range từ thực đơn ngữ cảnh).Một tiêu giảm nghiêm trọng rộng của cách 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ứ bao giờ dữ liệu nguồn cụ đổi, bạn phải lặp lại quy trình và xoay bảng một đợt nữa. Chẳng ai mong muốn lãng phí thời gian quý giá của mình để chuyển đi đưa lại như thế, đúng không?Làm chũm nào để chuyển một bảng được links với tài liệu ban đầu

Hãy xem cách chúng ta cũng có thể chuyển hàng sang những cột trong Excel bằng phương pháp sử dụng kỹ thuật coppy / dán quen thuộc để liên kết các ô đang được dịch rời so với tài liệu ban đầu. Điều tốt nhất khi sử dụng cách này là bất cứ bao giờ bạn biến hóa dữ liệu trong bảng nguồn, bảng chuyển đổi 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 những cột (hoặc các cột được gửi 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ư đã lý giải trong bí quyết trước đó và chọn Paste Linkở góc dưới phía bên trái của cửa sổ hộp thoại.


*

Bạn đang có công dụng tương từ như sau:


Chọn dữ liệu mới này cùng mở hộp thoại Find and Replace núm của Excel (bạn rất có thể nhấn Ctrl + H để chuyển đến tab Replace ngay).Thay thế toàn bộ các ký kết tự “=” bởi “xxx” (bạn cũng hoàn toàn có thể thay cố kỉnh nó bằng bất kỳ ký trường đoản cú nào không giống không trường thọ ở ngẫu nhiên nơi nào trong dữ liệu thực của bạn).


Điều này sẽ trở thành bảng của doanh nghiệp 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 dưới đây, cơ mà đừng lo lắng, chỉ cần thêm hai cách nữa và các bạn sẽ đạt được tác dụng 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 hóa các cột thành hàng, hoặc những hàng thành cột, tùy ở trong 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 nữa để hòn đảo ngược sự cố gắng đổi, có nghĩa là thay thế toàn bộ “xxx” bằng “=” để khôi phục link đến những ô gốc. Đã xong!


Đây là một giải pháp nhanh, nhẹ nhàng và solo giản, cần không? Hạn chế duy nhất của cách này là định dạng của bảng ban sơ bị mất trong quá trình và bạn sẽ cần phải phục hồi lại nó bằng tay thủ công (tôi đang chỉ cho bạn một cách nhanh chóng để làm điều này trong khuyên bảo này).

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

Nếu bạn không tìm kiếm kiếm những cách dễ dàng, thì chúng ta có thể sử dụng những công thức để biến đổi các cột thành 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ởi hàm TRANSPOSE

Như tên thường gọi của nó, hàm Transpose có thiết kế đặc biệt để chuyển dữ liệu trong Excel. Nếu các 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 công việc dưới đây.

Trong lấy một ví dụ này, bọn họ sẽ đổi khác một bảng liệt kê các 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 cùng cột vào bảng của bạn. Bảng mẫu mã của bọn họ có 7 cột và 6 hàng, bao hàm tiêu đề. Hãy ghi lưu giữ những số lượng này, và bọn họ hãy bắt đầu chuyển những hàng.

Đếm các ô mà bạn muốn chuyển đổi. Trong trường phù 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 hướng dọc và 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 mối cung cấp 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ới nhập dải dữ liệu mà bạn muốn chuyển đổi trong ngoặc đơn: = TRANSPOSE (array). Trong trường hợp của này, chúng ta nhập =TRANSPOSE($A$1:$G$6).

Lưu ý: Bạ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 .

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


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

Lợi ích thiết yếu 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ứ bao giờ bạn đổi khác dữ liệu nguồn, bảng chuyển tiếp cũng sẽ chuyển đổi theo.

Xem thêm: Hướng Dẫn Tra Cứu Giấy Phép Lái Xe A1, Cách Tra Cứu Thông Tin Giấy Phép Lái Xe

Điểm yếu hèn của hàm TRANSPOSE:

Định dạng bảng thuở đầu không được lưu trong bảng chuyển đổi, như bạn thấy trong ảnh chụp screen ở trên.Nếu có bất kỳ ô trống như thế nào trong bảng nguốn, những ô được gửi sẽ cất giá trị 0 thay thế.Bạn quan trọng chỉnh sửa ngẫu nhiên ô nào trong bảng được đổi khác bởi bởi 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 đổ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ễ dàng sử dụng, nhưng chắc hẳn rằng nó thiếu thốn tính linh hoạt và vì chưng đó hoàn toàn có thể không cần là cách cực 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 biến đổi cột sang mặt hàng và ngược lại này khá kiểu như 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ó chất nhận được chỉnh sửa được trong bảng chuyển đổi, đôi khi giữ kết nối với bảng ban đầu.

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

Giả sử các bạn có dữ liệu trong 4 cột (A – D) với 5 sản phẩm (1 – 5):


Nhập công thức sau đây vào ô bên trái của vùng chọn, đưa sử là ô A7 với nhấn phím Enter:

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Nếu tài liệu của bạn bước đầu ở một sản phẩm khác với một và cột khác 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 hợp A1 là ô trên cùng bên bắt buộc của bảng nguồn của bạn. Ngoài ra, xin để ý việc sử dụng những tham chiếu ô tuyệt vời và tương đối.

Sao chép phương pháp qua mảng mà bạn muốn đặt dữ liệu được chuyển như hay lệ bằng cách kéo một chữ thập màu sắc đen nhỏ dại ở 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 mới tạo của bạn, toàn bộ các cột được đưa sang hàng.

Tuy nhiên, các ô được chuyển trông vô cùng 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éo cả. Bạn rất có thể dễ dàng khôi phục lại định hình 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 cần vào bảng đổi khác và chọn Formatting dưới Paste Options.


Tóm lại, INDIRECT hình như là một giải pháp linh hoạt hơn để trở nên hàng thành cột trong Excel đối với hàm TRANSPOSE.

Ưu điểm : chúng ta cũng có thể dễ dàng chỉnh sửa ngẫu nhiên ô nào trong bảng gửi tiếp, mặt khác giữ links với bảng lúc đầu và cập nhật tự động hóa bất cứ lúc nào bạn thực hiện biến đổi 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 – việc định dạng tài liệu không được lưu. Mặc dù, chúng ta có thể nhanh chóng phục sinh nó, như ví dụ ở trên.

Bây giờ bạn đã sở hữu cảm giác như thế nào về hàm INDIRECT, bạn cũng có thể muốn tất cả cái nhìn sâu hơn về phần đông gì bí quyết thực sự đang làm cho và tại sao bọn họ lại thực hiện 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ư tên của nó, được thực hiện để con gián tiếp tham khảo một ô. Ví dụ, nếu bạn có nhu cầu có quý giá của ô B1 vào ô A8, thì bạn nhập cách làm này vào A8 =INDIRECT(“B1”).


Nhưng sức mạnh thực của hàm INDIRECT là nó rất có thể biến bất kỳ chuỗi làm sao thành tham chiếu, gồm 1 chuỗi mà bạn xây dựng bằng phương pháp sử dụng các hàm khác và các giá trị của những ô khác. Và đây và đúng là điều chúng ta sẽ kéo dà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ư đang biết, chúng ta đã sử dụng 3 hàm nữa trong công thức 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ới cột mà 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 ô đang cho. Ví dụ, =COLUMN(A3) trả về 1, bởi vì cột A là cột vật dụng nhất. Như bạn có thể dễ dàng đoán được, hàm ROW hoạt độ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 thể công thức mà họ đã thực hiện để thay đổi 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ư các bạn thấy, trong hàm ADDRESS, họ chỉ định COLUMN (A1) với ROW (A1),và trên đây thực sự là 1 trong mưu mẹo! Hãy nhớ rằng, hàm ADDRESS diễn giải số đầu tiên dưới dạng hàng cùng thứ hai là cột. Nói phương pháp khác, hàm ADDRESS đem một cột được trả lại vì chưng hàm COLUMN và biến đổi nó thành một hàng, tiếp nối lấy một mặt hàng và chuyển nó lịch sự cột, có nghĩa là hoán đổi những hàng thành các cột.

Hàm COLUMN cùng ROW trả lại số cột cùng số hàng, khớp ứng -> Hàm ADDRESS biến đổi cột thanh lịch hàng với hàng sang cột -> Hàm INDIRECT xuất ra dữ liệu đã được chuyển phiên sang dữ liệu khác trong 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 và kỹ năng phải không nào? cục bộ những kiến thức này các bạn đều rất có thể học được vào khóa học EX101 – Excel trường đoản cú cơ phiên bản tới siêng gia của học Excel Online. Đây là khóa học giúp đỡ bạn hệ thống kỹ năng và kiến thức một giải pháp đầy đủ, chi tiết. Rộng nữa không hề có số lượng 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, dễ dàng tra cứu vớt lại kỹ năng khi cần. Hiện thời hệ thống đang có ưu đãi cực kỳ lớn cho mình khi đk tham gia khóa học. Cụ thể xem tại: pigeonholebooks.com.Online