Các hàm liên kết và hoán vị trong Excel loại trừ lẫn nhau. Điều này có nghĩa là các ô đã hoán vị sẽ không hoạt động như các liên kết trên trang tính của bạn. Nói cách khác, bất kỳ thay đổi nào bạn thực hiện đối với các ô ban đầu sẽ không được phản ánh trong bản sao đã hoán vị.
Tuy nhiên, các dự án của bạn thường yêu cầu bạn chuyển vị và liên kết các ô / cột. Vì vậy, có một cách để sử dụng cả hai chức năng? Tất nhiên là có, và chúng tôi sẽ giới thiệu cho bạn bốn phương pháp khác nhau để làm điều đó.
Có thể nói rằng những thủ thuật này là một phần của kiến thức Excel trung cấp, nhưng nếu bạn làm theo các bước đến điểm T, sẽ không có bất kỳ thử nghiệm và sai sót nào ngay cả khi bạn là người mới hoàn toàn.
Vấn đề dán
Đối với mục đích của bài viết này, giả sử bạn muốn chuyển các cột vào cùng một trang tính. Vậy bạn làm gì? Chọn các cột, nhấn Ctrl + C (Cmd + C trên máy Mac) và chọn đích dán. Sau đó, nhấp vào tùy chọn Dán, chọn Dán Đặc biệt và đánh dấu vào ô phía trước Transpose.
Nhưng ngay sau khi bạn đánh dấu vào hộp, Paste Link sẽ chuyển sang màu xám. May mắn thay, có một số công thức và thủ thuật giúp bạn giải quyết vấn đề này. Hãy xem chúng.
TRANSPOSE - Công thức mảng
Ưu điểm chính của công thức này là bạn không cần phải kéo và thả các ô theo cách thủ công. Tuy nhiên, nó đi kèm với những mặt trái nhất định. Ví dụ: kích thước không dễ thay đổi, có nghĩa là bạn cần sử dụng lại công thức nếu phạm vi ô nguồn thay đổi.
Tuy nhiên, các vấn đề tương tự cũng áp dụng cho các công thức mảng khác và vấn đề này giúp bạn giải quyết vấn đề liên kết chuyển vị khá nhanh chóng.
Bước 1
Sao chép các ô và nhấp vào ô trên cùng bên trái trong khu vực bạn muốn dán các ô vào. Nhấn Ctrl + Alt + V để truy cập cửa sổ Dán Đặc biệt. Bạn cũng có thể làm điều đó từ thanh công cụ Excel.
Bước 2
Khi bạn truy cập vào cửa sổ, đánh dấu vào Định dạng trong Dán, chọn Transpose ở dưới cùng bên phải và nhấp vào OK. Hành động này chỉ thay đổi định dạng chứ không phải các giá trị và có hai lý do bạn cần thực hiện việc này. Đầu tiên, bạn sẽ biết phạm vi ô đã chuyển vị. Thứ hai, bạn giữ lại định dạng của ô ban đầu.
Bước 3
Toàn bộ vùng dán cần được chọn và bạn có thể thực hiện việc này sau khi dán các định dạng. Bây giờ, hãy gõ = TRANSPOSE (‘Phạm vi gốc’) và nhấn Ctrl + Shift + Enter.
Ghi chú: Điều quan trọng là nhấn Enter cùng với Ctrl và Shift. Nếu không, chương trình không nhận dạng được lệnh một cách chính xác và nó sẽ tự động tạo ra các dấu ngoặc nhọn.
Liên kết và Transpose - Phương pháp thủ công
Đúng vậy, Excel là tất cả về tự động hóa và sử dụng các hàm để giúp thao tác trên ô và cột dễ dàng hơn. Tuy nhiên, nếu bạn đang xử lý một phạm vi ô khá nhỏ, liên kết và chuyển vị thủ công thường là giải pháp nhanh nhất. Tuy nhiên, phải thừa nhận rằng có chỗ cho sai sót nếu bạn không đủ cẩn thận.
Bước 1
Chọn các ô của bạn và sao chép / dán chúng bằng tùy chọn Dán Đặc biệt. Lần này, bạn không đánh dấu vào hộp phía trước Transpose và bạn để các tùy chọn trong Dán làm mặc định.
Bước 2
Nhấp vào nút Dán liên kết ở dưới cùng bên trái và dữ liệu của bạn sẽ được dán dưới dạng liên kết.
Bước 3
Đây là phần khó. Bạn cần phải kéo theo cách thủ công rồi thả các ô vào vùng mới. Đồng thời, bạn cần phải cẩn thận để trao đổi các hàng và cột.
Công thức OFFSET
Đây là một trong những công cụ mạnh mẽ nhất để dán các ô, liên kết và chuyển vị chúng. Tuy nhiên, có thể không dễ dàng nếu bạn chưa quen với Excel, vì vậy chúng tôi sẽ cố gắng làm rõ các bước nhất có thể.
Bước 1
Bạn cần chuẩn bị các con số ở bên trái và trên cùng. Ví dụ: nếu có ba hàng, bạn sẽ sử dụng 0-2 và nếu có hai cột, bạn sẽ sử dụng 0-1. Phương thức là tổng số hàng và cột trừ đi 1.
Bước 2
Tiếp theo, bạn cần tìm và xác định ô cơ sở. Ô này phải giữ nguyên khi bạn sao chép / dán và đây là lý do tại sao bạn sử dụng các ký hiệu đặc biệt cho ô. Giả sử ô cơ sở là B2: bạn sẽ cần chèn ký hiệu đô la để tách ô này. Nó sẽ giống như thế này trong công thức: = OFFSET ($ B $ 2.
Bước 3
Bây giờ, bạn cần xác định khoảng cách (theo hàng) giữa ô cơ sở và ô đích. Con số này cần tăng lên khi bạn di chuyển công thức sang bên phải. Vì lý do này, không được có bất kỳ ký hiệu đô la nào trước cột hàm. Thay vào đó, hàng đầu tiên được cố định bằng ký hiệu đô la.
Ví dụ: nếu hàm nằm trong cột F, hàm sẽ trông như thế này: = OFFSET ($ B $ 2, F $ 1.
Bước 4
Giống như các hàng, các cột cũng cần tăng lên sau khi bạn liên kết và chuyển vị. Bạn cũng sử dụng ký hiệu đô la để cố định một cột nhưng cho phép các hàng tăng lên. Để làm rõ điều này, cách tốt nhất là tham khảo ví dụ có thể trông giống như sau: = OFFSET ($ B $ 2, F $ 1, $ E2).
Làm thế nào để Excel tại Excel
Bên cạnh phương pháp đã cho, bạn cũng có thể sử dụng các công cụ của bên thứ ba để liên kết và chuyển vị. Và nếu các phương pháp đã cho không mang lại kết quả khả quan, thì tốt nhất bạn nên sử dụng một công cụ như vậy.
Bạn đã sử dụng một trong những ứng dụng chuyển vị / liên kết đó để thực hiện thao tác này trong Excel chưa? Bạn có hài lòng với kết quả không? Chia sẻ kinh nghiệm của bạn trong phần bình luận bên dưới.