Cách liên kết hàng với cột trong Excel

Excel cung cấp nhiều cách khác nhau để dán thông tin mà bạn đã sao chép. Bạn có thể thấy các phương pháp khác nhau này khi chọn tùy chọn Dán Đặc biệt từ menu Chỉnh sửa. Hai trong số các phương pháp dán phổ biến nhất là hoán vị và liên kết

• chuyển vị. Thao tác này sẽ xoay hướng dữ liệu đã sao chép của bạn 90 độ. Nếu trước đó bạn đã sao chép năm ô từ một cột, chúng sẽ được dán thành năm ô liên tiếp
• liên kết. Bạn làm điều này bằng cách chọn Paste Link từ hộp thoại Paste Special. Nó khiến Excel dán các tham chiếu vào dữ liệu gốc thay vì các giá trị đã sao chép. Khi bạn thay đổi dữ liệu gốc, thông tin sẽ tự động thay đổi tại nơi bạn đã thực hiện Paste Link

Thật không may, có vẻ như hai tùy chọn này loại trừ lẫn nhau. Nếu bạn chọn tùy chọn Chuyển đổi, nút Dán Liên kết sẽ chuyển sang màu xám để bạn không thể chọn được nữa

Có hai cách bạn có thể khắc phục điều này. Một liên quan đến việc sửa đổi quy trình dán và một liên quan đến việc sử dụng công thức. Phương pháp đầu tiên như sau

1. Sao chép dữ liệu bạn muốn chuyển đổi và liên kết, như bình thường
2. Chọn bất kỳ ô nào trong một trang tính không sử dụng
3. Chọn Dán đặc biệt từ menu Chỉnh sửa. Thao tác này sẽ hiển thị hộp thoại Dán Đặc biệt. (Xem Hình 1. )

Hình 1. Hộp thoại Dán Đặc biệt

4. Nhấp vào nút Dán liên kết. Thông tin của bạn được dán, dưới dạng liên kết, trong trang tính. Hãy chắc chắn rằng nó vẫn được chọn
5. Nhấn Ctrl + H để hiển thị hộp thoại Thay thế. (Xem Hình 2. )

Hình 2. Hộp thoại Thay thế

6. Trong khu vực Find What, hãy nhập dấu bằng
7. Trong khu vực Replace With, nhập dấu thăng theo sau là dấu bằng
8. Nhấp vào Thay thế tất cả. Tất cả thông tin bạn vừa dán về cơ bản được chuyển đổi từ công thức thành văn bản. Hãy chắc chắn rằng nó vẫn được chọn
9. Nhấn Ctrl + C để sao chép phạm vi vào Clipboard
10. Chọn ô mà bạn muốn phạm vi được chuyển đổi và liên kết cuối cùng cư trú
11. Chọn Dán đặc biệt từ menu Chỉnh sửa. Thao tác này sẽ hiển thị hộp thoại Dán Đặc biệt
12. Chọn hộp kiểm Transpose
13. Bấm vào đồng ý. Thông tin được dán, nhưng nó được chuyển vào vị trí cuối cùng. Hãy chắc chắn rằng nó vẫn được chọn
14. Nhấn Ctrl + H để hiển thị hộp thoại Thay thế
15. Trong khu vực Find What, nhập dấu thăng theo sau là dấu bằng
16. Trong khu vực Replace With, nhập dấu bằng
17. Nhấp vào Thay thế tất cả. Tất cả thông tin bạn vừa dán được chuyển đổi trở lại công thức

Điều này có vẻ giống như nhiều bước, nhưng nó không tệ trong thực tế. Ngoài ra, nếu bạn thấy mình thực hiện quy trình này rất nhiều, bạn có thể tạo một macro thực hiện việc đó cho bạn

Khi bạn sao chép và dán các ô trong Excel, bạn có thể dán chúng dưới dạng liên kết hoặc chuyển đổi vị trí của chúng. Excel không cho phép thực hiện cả hai cùng một lúc. Thật không may, bạn thường cần liên kết và hoán vị. Nhưng có ba cách để thực hiện điều này. Thực hiện thủ công, sử dụng công thức mảng {=TRANSPOSE()} hoặc Công cụ Giáo sư Excel

nội dung

Vấn đề. Nút “Dán liên kết” bị mờ đi

Khi bạn đánh dấu vào “Transpose”, nút “Paste Link” sẽ chuyển sang màu xám

Bạn đã sao chép một dải ô. Bây giờ bạn muốn dán chúng. Nhưng thay vì chỉ dán chúng, bạn cũng muốn dán các liên kết và hoán vị (trao đổi hàng và cột) chúng

Vấn đề. Ngay sau khi bạn đánh dấu vào “Tranpose”, “Paste Link” sẽ chuyển sang màu xám

Phương pháp 1. Sử dụng hàm OFFSET

cách giải quyết. Sử dụng công thức OFFSET để dán các liên kết và chuyển đổi chúng cùng một lúc

Chức năng OFFSET rất mạnh nhưng thật không may, đặc biệt đối với người mới bắt đầu không dễ sử dụng. Nói một cách đại khái, bạn xác định một ô cơ sở và cho Excel biết giá trị nào được tính từ ô cơ sở sẽ trả về

Đầu tiên. Chuẩn bị số 0-2 (số hàng tối đa – 1) ở trên cùng và 0 đến 1 ở phía bên tay trái. Chúng tôi sử dụng điều này như một tài liệu tham khảo. Chắc chắn có nhiều cách tao nhã hơn (e. g. bằng ROW() hoặc COLUMN()) nhưng đây là phương pháp nhanh nhất và dễ nhất. Số sau khớp với hình trên

1. Trong trường hợp của chúng tôi, ô cơ sở là ô góc trên cùng bên trái của phạm vi mà chúng tôi muốn chuyển đổi và liên kết. Ô cơ sở này sẽ không thay đổi khi chúng ta sao chép và dán công thức để chúng ta chèn các dấu $
2. Phần thứ hai của công thức OFFSET xác định ô mục tiêu của bạn cách xa ô cơ sở bao nhiêu hàng. Chúng tôi muốn con số này tăng lên khi chúng tôi kéo công thức sang phía bên tay phải. Đó là lý do tại sao chúng tôi không thêm dấu $ trước cột (“F”). Nhưng khi sao chép công thức, hàng đầu tiên phải được cố định bằng ký hiệu $
3. Tương tự cho các cột. Khi kéo công thức xuống, chúng tôi muốn chỉ số cột tăng lên. Đó là lý do tại sao chúng tôi sửa cột chứ không sửa hàng

Công thức trong ví dụ trên trông như thế này

=OFFSET($B$2,F$1,$E2) 

Phương pháp 2. Công thức mảng “TRANSPOSE”

Các bước dán đồng thời link và chuyển vị trí trong Excel

Với công thức mảng, bạn không phải kéo và thả các ô theo cách thủ công. Tuy có ưu điểm là không phải làm thủ công nhưng cách này phức tạp hơn một chút. Hơn nữa, nó đi kèm với những nhược điểm của tất cả các công thức mảng. Kích thước của chúng không (dễ dàng) thay đổi. Vì vậy, nếu phạm vi ô của dữ liệu nguồn của bạn thay đổi, bạn thường phải thiết lập lại công thức mảng

Thực hiện theo các bước sau để đặt công thức mảng để dán các ô được liên kết với nguồn của chúng và chuyển đổi vị trí của chúng (các số tương ứng với hình ảnh ở phía bên tay phải)

1. Sao chép các ô mà bạn muốn chuyển đổi và liên kết
2. Chọn ô trên cùng bên trái của khu vực bạn muốn dán chúng vào và mở cửa sổ dán đặc biệt bằng cách nhấn Ctrl + Alt + v
3. Chọn “Định dạng”
4. Chọn “Transpose” và xác nhận với “OK”. Bây giờ, thay vì các giá trị, chỉ định dạng ban đầu được chuyển đổi. Có hai lý do cho bước này. Nhận biết phạm vi chính xác cho các ô được chuyển đổi và đồng thời đã có định dạng của các ô ban đầu
5. Chọn toàn bộ khu vực dán (hoặc đảm bảo rằng nó vẫn được chọn sau khi dán các định dạng)
6. Nhập công thức này

=TRANSPOSE('Original Range')
và nhấn Ctrl + Shift + Enter. Nếu bạn chỉ nhấn Enter (không có Ctrl + Shift) thì Excel sẽ không nhận ra công thức của bạn là công thức mảng. Dấu ngoặc nhọn sẽ được tạo tự động
Phương pháp 3. Dán dưới dạng liên kết và chuyển đổi thủ công
Chúng ta hãy xem phương pháp rõ ràng. Làm thủ công. Nếu phạm vi ô của bạn không quá lớn thì đó thường là giải pháp nhanh nhất. Tất nhiên, làm mọi thứ theo cách thủ công là một nguồn có thể xảy ra lỗi
Sao chép và dán dữ liệu của bạn bằng "Dán đặc biệt" bằng cách nhấn Ctrl + Alt + v trên bàn phím cùng lúc
Có một nút “Dán liên kết” ở góc dưới cùng bên trái của cửa sổ dán đặc biệt. Nhấp vào nó và bạn sẽ thấy Excel dán dữ liệu dưới dạng liên kết
Bây giờ – và đây là phần khó chịu – bạn phải kéo và thả các ô vào vị trí mới của chúng để các hàng và cột được hoán đổi
Bạn có muốn tăng năng suất của mình trong Excel không?
Nhận dải băng Giáo sư Excel



Tìm hiểu thêm
Tải xuống bản dùng thử miễn phí
Thêm hơn 120 tính năng tuyệt vời cho Excel
Phương pháp 4. Giáo sư Công cụ Excel
Giáo sư Công cụ Excel. Dán dưới dạng Liên kết và Chuyển đổi với 2 lần nhấp
Vì tất cả các phương pháp trước đây đều có một số nhược điểm lớn, nên việc dán dưới dạng liên kết và chuyển đổi dữ liệu là một trong những chức năng đầu tiên chúng tôi đưa vào phần bổ trợ Excel của mình
Chọn dữ liệu của bạn và nhấn sao chép ở phía bên trái của ruy-băng Giáo sư Excel
Chọn ô mục tiêu của bạn và nhấn “Dán liên kết và chuyển đổi” ngay bên cạnh nút sao chép
Hàm này hoạt động trên sổ làm việc miễn là sổ làm việc nguồn và đích được mở đồng thời