CategoryChia sẻ Exel Kỹ năng xử lý số liệu

Liên kết dữ liệu giữa các sheet hoặc các file trong Exel

Tháng Tư 17, 2021 Off By Anh bán hàng

Khi bạn xử lý Exel, việc liên kết các dữ liệu trong các Sheet hay các file của Exel là công việc sẽ thực hiện thường xuyên, ở đây tôi sẽ hướng dẫn cách liên kết dữ liệu từ đơn giản đến nâng cao. Có 2 cách là dùng các hàm exel để liên kết hoặc liên kết bằng pivot trong exel. Ở hướng dẫn này hướng dẫn cơ bản sử dụng các hàm trong Exel để liên kết dữ liệu giữa các sheet và các file trong exel lại với nhau.

Liên kết dữ liệu giữa các sheet trong exel:

Ví dụ ở bảng trên tôi có 2 sheet là Tonghop và banhang, để liên kết 2 sheet với nhau được thực hiện như sau:

Liên kết dữ liệu giữa các sheet hoặc các file trong Exel

Ở sheet Tonghop bạn chọn 1 ô sau đó gõ hàm như sau:

Bạn gõ ký từ = ở ô cần liên kết sau đó chọn ô liên kết như sau.

Sau đó qua sheet banhang chọn ô cần liên kết và nhần enter

Kết quả sẽ như sau:

Bạn có thể thấy giá trị sản phẩm đã xuất hiện bên sheet tonghop

Để liên kết các dòng khác bạn có 2 cách:

Kéo lệnh xuống bằng dấu vuông như sau: Nhân vào dấu vuống sau và kéo xuống sẽ tự động copy lệnh để lấy thêm giá trị khác:

Và kết quả như sau:

Vì bên kia chỉ có giá trị Táo, Cam, Bưởi nên ô B8 giá trị bằng 0, giả sử tôi bổ sung Lê ở bên sheet bán hàng như sau:

Bên sheet tonghop sẽ hiển thị như sau:

Như vậy có thể thấy mọi thay đổi ở sheet banhang đều được liên kết với sheet tonghop.

Bạn có thể cấu trúc dữ liệu liên kết các sheet với nhau sẽ là Tên sheet!Ô tham chiếu

Dùng copy hoặc dán lệnh để copy hàm liên kết:

Bạn chọn copy sau đó chọn vùng cần dán dữ liệu liên kết và chuột phải chọn Paste như bình thường.

Cách liên kết dữ liệu các file trong exel

Tương tự như giữ các sheet bạn cũng có thể liên kết dữ liệu giữa các file đơn giản cùng các thao tác trên, nhưng khác là bạn làm việc trên các file khác nhau.

Ví dụ tôi tạo 1 file book2 để liên kết dữ liệu với file hướng dẫn 1

Tại file book1 tương tự như phương pháp trên chọn ô cần liên kết và gõ =

Sau đó chuyển sang file huongdan1 và chọn ô cần liên kết:

Và nhấn enter để kết thúc hàm, bạn có thể thấy như sau:

Tương tự như liên kết sheet thì việc copy lệnh sang các ô khác cung tương tự, tuy nhiên môt điểm cần lưu ý khi liên kết file là các hàm sẽ có cố định ô trong exel, cố định ô là gì bạn có thể tìm hiểu ở đây:

Chuyển đổi giữa tham chiếu tương đối tuyệt đối và hỗn hợp trong exel của Microsoft

Với copy hay kéo hàm dữ liệu theo từng sheet thì dữ liệu sẽ nhảy đúng, nhưng khi liên kết theo file thì bạn sẽ thấy dữ liệu không nhảy mà kết quả giống như ô ban đầu, nguyên nhân do khi liên kết dữ liệu giữa các file exel với nhau thì ô tham chiếu luôn được để giá trị tuyệt đối, vì vậy bạn cần chuyển sang giá trị tương đối để có thể copy hàm hay kéo hàm sang các ô khác.

Bạn có thể thấy giá trị $ ở các ô tham chiếu nhằm cố định ô tham chiếu:

='[huong dan 1.xlsx]Banhang’!$B$5

Vì vậy trước khi copy hay kéo hàm tham chiếu thì xóa các ký tự này đi để chuyển thành tham chiếu tương đối.

Chuyển thành: ='[huong dan 1.xlsx]Banhang’!B5

Sử dụng một số hàm tính toán liên kết dữ liệu giữa các sheet hoặc các file trong exel:

Việc sử dụng các hàm để liên kết dữ liệu giữa các sheet và các file exel là tương tự nhau, chỉ có một lưu ý là khi liên kết dữ liệu giữa các file bạn cần chuyển ô tham chiếu tuyệt đối thành tương đối để có thể kéo hoặc copy hàm sang các ô khác tương ứng.

Ví dụ tôi liên kết dữ liệu 2 file và tính lợi nhuận như sau, tại file book1 tạo thêm 1 cột lợi nhuận.

Lợi nhuận sẽ bằng doanh thu trừ cho chi phí ở file huongdan1, bạn gõ = ở ô C4 tương ứng tính toán cho lợi nhuận của táo ở file Book1 và sau đó chuyển sang file huongdan1 đặt hàm tương ứng doanh thu trừ cho chi phí.

Sau đó enter kết quả như sau:

Bạn chuyển ô tham chiếu từ tuyệt đối thành tương đối trong trường hợp liên kết dữ liệu theo file này để có thể copy hay kéo hàm để tính cho các sản phẩm còn lại.

='[huong dan 1.xlsx]Banhang’!$C$6-‘[huong dan 1.xlsx]Banhang’!$D$6

Chuyển thành: ='[huong dan 1.xlsx]Banhang’!C6-‘[huong dan 1.xlsx]Banhang’!D6