Nếu bạn có một tập dữ liệu và bạn muốn chuyển nó trong Excel (Điều này có nghĩa là chuyển đổi hàng thành cột và cột thành hàng), thực hiện thủ công là hoàn toàn không thể!
Nội dung
Chuyển đổi dữ liệu bằng cách sử dụng dán đặc biệt
Paste Special có thể làm được nhiều điều đáng kinh ngạc, một trong số đó là chuyển dữ liệu trong Excel.
Giả sử bạn có một tập dữ liệu giống như sau:
Dữ liệu có các vùng trong cột và phần tư trong hàng.
Bây giờ vì một số lý do, nếu bạn cần chuyển đổi dữ liệu này, bạn có thể sử dụng dán đặc biệt theo cách sau:
Thao tác này sẽ sao chép và dán dữ liệu ngay lập tức, nhưng theo cách đã được chuyển đổi.Dưới đây là bản demo cho thấy toàn bộ quá trình.
Các bước hiển thị ở trên sao chép các giá trị, công thức (nếu có) và định dạng.Nếu bạn chỉ muốn sao chép giá trị, hãy chọn "Giá trị" trong hộp thoại dán đặc biệt.
Lưu ý rằng dữ liệu được sao chép là tĩnh và nếu bạn thực hiện bất kỳ thay đổi nào đối với tập dữ liệu gốc, những thay đổi đó sẽ không được phản ánh trong dữ liệu đã chuyển đổi.
Nếu bạn muốn liên kết các ô đã hoán vị này với các ô ban đầu, bạn có thể kết hợp sức mạnh của Tìm và Thay thế bằng Dán Đặc biệt.
Chuyển đổi dữ liệu bằng cách dán đặc biệt và tìm và thay thế
Dán Đặc biệt một mình cung cấp cho bạn dữ liệu tĩnh.Điều này có nghĩa là nếu dữ liệu ban đầu của bạn thay đổi và dữ liệu bạn muốn chuyển vị cũng được cập nhật, thì bạn cần sử dụng lại dán đặc biệt để chuyển vị.
Đây là một thủ thuật thú vị mà bạn có thể sử dụng để chuyển đổi dữ liệu và vẫn liên kết dữ liệu đó với các ô ban đầu.
Giả sử bạn có một tập dữ liệu giống như sau:
Dưới đây là các bước để chuyển đổi dữ liệu nhưng giữ nguyên liên kết:
- Chọn tập dữ liệu (A1: E5).
- Sao chép nó (Control + C hoặc nhấp chuột phải và chọn Nhân bản).
- Bây giờ bạn có thể dán dữ liệu đã hoán vị vào vị trí mới.Trong ví dụ này, tôi muốn sao chép trong G1: K5, vì vậy hãy nhấp chuột phải vào ô G1 và chọn Dán Đặc biệt.
- Trong hộp thoại Dán đặc biệt, bấm vào nút Dán liên kết.Điều này sẽ cung cấp cho bạn cùng một tập dữ liệu, nhưng ở đây các ô được liên kết với tập dữ liệu ban đầu (ví dụ: G1 được liên kết với A1, G2 được liên kết với A2, v.v.).
- Với dữ liệu mới được sao chép này được chọn, nhấn Control + H (hoặc vào Trang chủ -> Chỉnh sửa -> Tìm và Chọn -> Thay thế).Thao tác này sẽ mở hộp thoại Tìm và Thay thế.
- Trong hộp thoại Tìm và Thay thế, sử dụng các lệnh sau:
- Trong Tìm gì: =
- Thay thế bằng:! @ # (Lưu ý rằng tôi đã sử dụng! @ # Vì nó là sự kết hợp duy nhất của các ký tự và không có khả năng là một phần của dữ liệu của bạn. Bạn có thể sử dụng bất kỳ bộ ký tự duy nhất nào như vậy).
- Nhấp vào Thay thế Tất cả.Điều này sẽ thay thế các dấu bằng trong công thức và bạn sẽ có! @ # Theo sau là tham chiếu ô trong mỗi ô.
- Nhấp chuột phải và sao chép tập dữ liệu (hoặc sử dụng Control + C).
- Chọn một vị trí mới, nhấp chuột phải và chọn Dán Đặc biệt.Trong ví dụ này, tôi dán nó vào ô G7.Bạn có thể dán nó vào bất cứ đâu bạn muốn.
- Trong hộp thoại Dán Đặc biệt, chọn Transpose và bấm OK.
- Sao chép-dán dữ liệu chuyển vị mới được tạo này vào vị trí nơi nó được tạo.
- Bây giờ, hãy mở lại hộp thoại Tìm và Thay thế và thay thế! @ # Bằng =.
Điều này sẽ cung cấp cho bạn dữ liệu được liên kết chuyển đổi.Dữ liệu đã chuyển đổi sẽ tự động cập nhật nếu bạn thực hiện bất kỳ thay đổi nào đối với tập dữ liệu gốc.
Lưu ý: Vì A1 trống trong dữ liệu ban đầu của chúng tôi, bạn cần xóa số 1 trong G0 theo cách thủ công.Số 0 xuất hiện khi chúng tôi dán liên kết, vì liên kết đến một ô trống vẫn trả về 0.Ngoài ra, bạn cần định dạng tập dữ liệu mới (bạn chỉ có thể sao chép-dán định dạng từ tập dữ liệu gốc).
Chuyển dữ liệu bằng hàm TRANSPOSE trong Excel
Hàm TRANSPOSE của Excel - như tên gọi - có thể được sử dụng để chuyển dữ liệu trong Excel.
Giả sử bạn có một tập dữ liệu giống như sau:
Dưới đây là các bước để chuyển đổi nó:
- Chọn các ô mà bạn muốn chuyển đổi tập dữ liệu.Lưu ý rằng bạn cần chọn số ô chính xác làm dữ liệu thô.Vì vậy, ví dụ: nếu bạn có 2 hàng và 3 cột, bạn cần chọn 3 hàng và 2 cột ô mà bạn muốn chuyển dữ liệu.Trong trường hợp này, vì có 5 hàng và 5 cột, bạn cần chọn 5 hàng và 5 cột.
- Nhập = TRANSPOSE (A1: E5) vào ô hiện hoạt (phải là ô trên cùng bên trái được chọn và nhấn Control Shift Enter.
Điều này sẽ chuyển đổi tập dữ liệu.
Đây là những gì bạn cần biết về chức năng TRANSPOSE:
- Đây là một hàm mảng, vì vậy bạn cần sử dụng Control-Shift-Enter thay vì chỉ Enter.
- Bạn không thể xóa một phần của kết quả.Bạn cần xóa toàn bộ mảng giá trị được trả về bởi hàm TRANSPOSE.
- Hàm chuyển vị chỉ sao chép giá trị, không sao chép định dạng.
Chuyển dữ liệu với Power Query
Power Query là một công cụ mạnh mẽ cho phép bạn nhanh chóng chuyển dữ liệu trong Excel.
Power Query CóExcel 2016("Nhận và chuyển đổi" trong tab "Dữ liệu"), nhưng nếu bạn đang sử dụngExcel 2013 hoặc 2010, bạn cần cài đặt nó dưới dạng tiện ích bổ sung.
Giả sử bạn có một tập dữ liệu giống như sau:
Dưới đây là các bước để chuyển đổi dữ liệu này bằng cách sử dụng Power Query:
trong Excel 2016
Lưu ý rằng vì ô trên cùng bên trái của tập dữ liệu của chúng tôi trống nên nó có tên chung là Column1 (được hiển thị bên dưới).Bạn có thể xóa ô này khỏi dữ liệu đã hoán vị.
Trong Excel 2013/2010
Trong Excel 2013/10, bạn cần cài đặt Power Query dưới dạng bổ trợ.
Bấm vào đâyTải xuống plugin và nhận hướng dẫn cài đặt.
Sau khi cài đặt Power Query, hãy chuyển đến Power Query -> Excel Data -> From Table.
Thao tác này sẽ mở hộp thoại Tạo bảng.Bây giờ hãy làm theo các bước tương tự như được hiển thị trong Excel 2016.
Bạn cũng có thể thích các hướng dẫn Excel sau:
- Nhân trong Excel bằng cách sử dụng Dán Đặc biệt.
- Nối các phạm vi Excel (có và không có dấu phân cách).
