5 cách chuyển đổi dữ liệu trong Excel (Hướng dẫn từng bước)
Chuyển đổi dữ liệu có nghĩa là thay đổi hướng của một mảng bằng cách chuyển đổi các hàng của nó thành các cột hoặc ngược lại. Trong hướng dẫn này, chúng tôi sẽ chia sẻ với bạn năm phương pháp khác nhau để chuyển đổi hướng của một mảng và đạt được kết quả mong muốn.
Video: Chuyển đổi dữ liệu trong Excel
Chuyển cột thành hàng bằng Paste Special
Bước 1: Sao chép phạm vi được chuyển đổi
Chọn phạm vi ô mà bạn muốn chuyển đổi hàng và cột, rồi nhấn Ctrl + C để sao chép phạm vi.
Bước 2: Chọn tùy chọn Paste Transpose
Bấm chuột phải vào ô đầu tiên của phạm vi đích, rồi bấm vào Chuyển đổi biểu tượng (Dưới Tùy chọn dán) từ menu chuột phải.
Kết quả
Thì đấy! Phạm vi được chuyển đổi trong thời gian không!
(AD) Chuyển đổi kích thước bảng dễ dàng với Kutools
Việc chuyển đổi bảng chéo (bảng hai chiều) sang danh sách phẳng (danh sách một chiều) hoặc ngược lại trong Excel luôn tốn rất nhiều thời gian và công sức. Tuy nhiên, với Kutools for Excel được cài đặt, nó Chuyển đổi kích thước bảng công cụ sẽ giúp bạn thực hiện việc chuyển đổi một cách nhanh chóng và dễ dàng.
Kutools cho Excel - Bao gồm hơn 300 chức năng tiện dụng, giúp công việc của bạn trở nên dễ dàng hơn rất nhiều. Nhận bản dùng thử miễn phí đầy đủ tính năng trong 30 ngày ngay bây giờ!
Chuyển đổi và liên kết dữ liệu với nguồn
Để tự động chuyển đổi hướng của một phạm vi (chuyển đổi cột thành hàng và ngược lại) và kết nối kết quả đã chuyển đổi với tập dữ liệu gốc, bạn có thể sử dụng bất kỳ phương pháp nào dưới đây:
- Hàm TRANSPOSE (Dễ sử dụng nhưng kết quả không thể chỉnh sửa)
- Các hàm GIÁN TIẾP, ĐỊA CHỈ, CỘT và HÀNG (Công thức lớn nhưng cho phép thay đổi kết quả)
- Dán Đặc biệt và Tìm & Thay thế (Phức tạp nhưng dễ hiểu)
- Power Query (Dễ dàng với kết quả được định dạng trong bảng)
Đổi hàng thành cột bằng hàm TRANSPOSE
Để chuyển hàng thành cột và ngược lại với VẬN CHUYỂN chức năng, vui lòng làm như sau.
Bước 1: Chọn cùng số lượng ô trống như tập hợp ô ban đầu nhưng theo hướng khác
Mẹo: Bỏ qua bước này nếu bạn đang sử dụng Excel 365 hoặc Excel 2021.
Giả sử bảng ban đầu của bạn nằm trong phạm vi A1: C4, có nghĩa là bảng có 4 hàng và 3 cột. Vì vậy, bảng được chuyển đổi sẽ có 3 hàng và 4 cột. Điều đó có nghĩa là bạn nên chọn 3 hàng và 4 cột của các ô trống.
Bước 2: Nhập công thức TRANSPOSE
Nhập công thức bên dưới vào thanh công thức, rồi nhấn Ctrl + Shift + Enter để nhận được kết quả.
Mẹo: Ấn Bản đăng ký hạng mục thi nếu bạn là người dùng Excel 365 hoặc Excel 2021.
=TRANSPOSE(A1:C4)
- Bạn nên thay đổi A1: C4 đến phạm vi nguồn thực tế mà bạn muốn chuyển đổi.
- Nếu có bất kỳ ô trống nào trong phạm vi ban đầu, VẬN CHUYỂN chức năng sẽ chuyển đổi các ô trống thành 0 (số không). Để loại bỏ các kết quả bằng XNUMX và giữ lại các ô trống trong khi chuyển đổi, bạn cần tận dụng lợi thế của IF chức năng:
-
=TRANSPOSE(IF(A1:C4="","",A1:C4))
Kết quả
Các hàng được chuyển đổi thành các cột và các cột được chuyển đổi thành các hàng.
Xoay dữ liệu bằng các hàm INDIRECT, ADDRESS, COLUMN và ROW
Mặc dù công thức trên khá dễ hiểu và dễ sử dụng, nhưng nhược điểm của nó là bạn không thể chỉnh sửa hoặc xóa bất kỳ ô nào trong bảng đã xoay. Vì vậy, tôi sẽ giới thiệu một công thức sử dụng GIÁN TIẾP, ĐỊA CHỈ NHÀ, CỘT và HÀNG chức năng. Giả sử bảng ban đầu của bạn nằm trong phạm vi A1: C4, để thực hiện chuyển đổi từ cột sang hàng và giữ cho dữ liệu được xoay vòng kết nối với tập dữ liệu nguồn, vui lòng thực hiện theo các bước bên dưới.
Bước 1: Nhập công thức
Nhập công thức dưới đây vào ô trên cùng bên trái của phạm vi đích (A6 trong trường hợp của chúng tôi), và nhấn đăng ký hạng mục thi:
=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
- Bạn nên thay đổi A1 đến ô trên cùng bên trái của phạm vi nguồn thực tế mà bạn sẽ chuyển vị và giữ nguyên các ký hiệu đô la. Ký hiệu đô la ($) trước chữ cái cột và số hàng biểu thị một tham chiếu tuyệt đối, giúp giữ nguyên chữ cái cột và số hàng khi bạn di chuyển hoặc sao chép công thức sang các ô khác.
- Nếu có bất kỳ ô trống nào trong phạm vi ban đầu, công thức sẽ chuyển đổi các ô trống thành 0 (số không). Để loại bỏ các kết quả bằng XNUMX và giữ lại các ô trống trong khi chuyển đổi, bạn cần tận dụng lợi thế của IF chức năng:
-
=IF(INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))=0,"",INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1))))
Bước 2: Sao chép công thức sang các ô bên phải và bên dưới
Chọn ô công thức, rồi kéo bộ điều khiển điền của ô đó (hình vuông nhỏ màu xanh lục ở góc dưới bên phải của ô) xuống rồi sang phải tới bao nhiêu hàng và cột tùy ý.
Kết quả
Các cột và hàng được chuyển đổi ngay lập tức.
Chuyển đổi cột thành hàng bằng Dán Đặc biệt và Tìm & Thay thế
Một vài bước nữa với phương pháp Dán Đặc biệt cùng với tính năng Tìm và Thay thế cho phép bạn chuyển đổi dữ liệu trong khi liên kết các ô nguồn với các ô đã chuyển đổi.
Bước 1: Sao chép phạm vi được chuyển đổi
Chọn phạm vi ô bạn muốn chuyển đổi, sau đó nhấn Ctrl + C để sao chép phạm vi.
Bước 2: Áp dụng tùy chọn Dán liên kết
-
Bấm chuột phải vào một ô trống, rồi bấm Dán đặc biệt.
-
Nhấp vào Paste Link.
Bạn sẽ nhận được kết quả như hình bên dưới:
Bước 3: Tìm và Thay thế các dấu bằng (=) từ kết quả Dán Liên kết
-
Chọn phạm vi kết quả (A6: C9) và hãy nhấn Ctrl + H, và sau đó thay thế = với @EO (hoặc bất kỳ (các) ký tự nào không tồn tại trong phạm vi đã chọn) trong Tìm và Thay thế thoại.
-
Nhấp vào Thay thế Tất cả, rồi đóng hộp thoại. Sau đây cho thấy dữ liệu sẽ trông như thế nào.
Bước 4: Chuyển đổi kết quả Paste Link đã thay thế
Chọn phạm vi (A6: C9) và hãy nhấn Ctrl + C để sao chép nó. Nhấp chuột phải vào một ô trống (ở đây tôi đã chọn A11) và chọn Chuyển đổi biểu tượng từ Tùy chọn dán để dán kết quả đã chuyển đổi.
Bước 5: Lấy lại dấu bằng (=) để liên kết kết quả đã chuyển đổi với dữ liệu gốc
-
Giữ dữ liệu kết quả đã chuyển đổi A11: D13 đã chọn, rồi nhấn Ctrl + Hvà thay thế @EO với = (ngược lại với bước 3).
-
Nhấp vào Thay thế Tất cả, rồi đóng hộp thoại.
Kết quả
Dữ liệu được chuyển đổi và liên kết với các ô ban đầu.
Chuyển đổi và liên kết dữ liệu với nguồn với Power Query
Power Query là một công cụ tự động hóa dữ liệu mạnh mẽ cho phép bạn dễ dàng chuyển đổi dữ liệu trong Excel. Bạn có thể hoàn thành công việc bằng cách làm theo các bước dưới đây:
Bước 1: Chọn phạm vi cần chuyển đổi và mở Power Query Biên tập viên
Chọn phạm vi dữ liệu sẽ được chuyển đổi. Và sau đó, trên Ngày tab, trong Nhận và chuyển đổi dữ liệu nhóm, nhấp Từ Bảng / Phạm vi.
- Nếu phạm vi dữ liệu đã chọn không có trong một bảng, một Tạo bảng hộp thoại sẽ bật lên; nhấp chuột OK để tạo một bảng cho nó.
- Nếu bạn đang sử dụng Excel 2013 hoặc 2010 và bạn không thể tìm thấy Từ Bảng / Phạm vi trên Ngày tab, bạn sẽ cần tải xuống và cài đặt nó từ microsoft Power Query cho trang Excel. Sau khi cài đặt, đi đến Power Query tab, nhấp vào Từ bảng trong Dữ liệu Excel nhóm.
Bước 2: Chuyển cột thành hàng bằng Power Query
-
Tới Chuyển đổi chuyển hướng. bên trong Sử dụng hàng đầu tiên làm tiêu đề menu thả xuống, chọn Sử dụng Tiêu đề làm Hàng đầu tiên.
-
Nhấp vào Chuyển đổi.
Bước 3: Lưu dữ liệu đã chuyển đổi vào trang tính
trên Tập tin tab, nhấp vào Đóng và tải để đóng cửa sổ Power Editor và tạo một trang tính mới để tải dữ liệu đã chuyển đổi.
Kết quả
Dữ liệu đã chuyển đổi được chuyển thành bảng trong trang tính mới được tạo.
- Các tiêu đề cột bổ sung được tạo ở hàng đầu tiên như minh họa ở trên. Để tăng hàng đầu tiên bên dưới tiêu đề thành tiêu đề cột, hãy chọn một ô trong dữ liệu và nhấp vào Query > Chỉnh sửa. Sau đó, chọn Chuyển đổi > Sử dụng hàng đầu tiên làm tiêu đề. Cuối cùng, chọn Trang Chủ > Đóng và tải.
- Nếu có bất kỳ thay đổi nào đối với tập dữ liệu gốc, bạn có thể cập nhật dữ liệu đã chuyển đổi ở trên với những thay đổi đó bằng cách nhấp vào Refresh biểu tượng bên cạnh cái bàn trong Truy vấn & Kết nối ngăn, hoặc bằng cách nhấp vào Refresh nút trên Query tab.
(AD) Chuyển đổi phạm vi với Kutools trong vài cú nhấp chuột
Sản phẩm Phạm vi chuyển đổi Tiện ích trong Kutools for Excel có thể giúp bạn dễ dàng chuyển đổi (chuyển đổi) một cột dọc thành nhiều cột hoặc ngược lại hoặc chuyển đổi một hàng thành nhiều hàng hoặc ngược lại.
Kutools cho Excel - Bao gồm hơn 300 chức năng tiện dụng, giúp công việc của bạn trở nên dễ dàng hơn rất nhiều. Nhận bản dùng thử miễn phí đầy đủ tính năng trong 30 ngày ngay bây giờ!
Các bài liên quan
- Làm cách nào để nhanh chóng chuyển đổi phạm vi và bỏ qua các ô trống trong Excel?
- Khi chúng tôi dán một phạm vi dưới dạng chuyển đổi, các ô trống cũng sẽ được dán. Tuy nhiên, đôi khi, chúng tôi chỉ muốn chuyển đổi phạm vi bỏ qua các ô trống như ảnh chụp màn hình bên dưới, Có cách nào để nhanh chóng chuyển đổi phạm vi và bỏ qua khoảng trống trong Excel không?
- Làm thế nào để chuyển mỗi 5 hoặc n hàng từ một cột thành nhiều cột?
- Giả sử, bạn có dữ liệu dài trong cột A và bây giờ, bạn muốn chuyển mỗi 5 hàng từ cột A thành nhiều cột, chẳng hạn như chuyển A1: A5 thành C6: G6, A6: A10 thành C7: G7, v.v. ảnh chụp màn hình sau được hiển thị. Làm thế nào bạn có thể giải quyết công việc này mà không cần sao chép và dán nhiều lần trong Excel?
- Làm thế nào để chuyển / chuyển đổi các cột và hàng thành một cột?
- Khi bạn sử dụng trang tính Excel, đôi khi, bạn sẽ gặp vấn đề này: làm thế nào bạn có thể chuyển đổi hoặc chuyển đổi một dải dữ liệu thành một cột duy nhất? (Xem ảnh chụp màn hình sau :) Bây giờ, tôi giới thiệu ba thủ thuật nhanh để bạn giải quyết vấn đề này.
- Làm thế nào để chuyển vị / chuyển đổi các cột và hàng thành một hàng?
- Làm cách nào để nối nhiều hàng và cột thành một hàng dài? Có thể, nó có vẻ dễ dàng đối với bạn, bởi vì bạn có thể sao chép từng cái một và nối chúng thành một hàng theo cách thủ công. Tuy nhiên, điều này có thể tốn thời gian và tẻ nhạt nếu có hàng trăm hàng và cột. Ở đây tôi sẽ nói về một số thủ thuật nhanh chóng để giải quyết nó.
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
Mục lục
- Video: Chuyển đổi dữ liệu trong Excel
- Chuyển cột thành hàng bằng Paste Special
- Chuyển đổi và liên kết dữ liệu với nguồn
- Với chức năng TRANSPOSE
- Với các hàm GIÁN TIẾP, ĐỊA CHỈ, CỘT và HÀNG
- Với Paste Special và Find & Replace
- Với Power Query
- Các bài liên quan
- Các công cụ năng suất văn phòng tốt nhất
- Nhận xét