Hướng dẫn cách tham chiếu dữ liệu giữa các sheet trong Excel có ví dụ minh hoạ

Trong quá trình làm việc với Excel, đôi khi trong một số trường hợp, chúng ta cần phải tham chiếu dữ liệu giữa các sheet, lấy dữ liệu từ trang tính khác hoặc tổng hợp dữ liệu Excel từ nhiều trang tính, để có thể tham chiếu hai hay nhiều sheet chúng ta cần thực hiện những hàm nào? Trong bài viết dưới đây, mình sẽ chia sẻ về các cách tham chiếu giữa các sheet trong Excel một cách cụ thể, chi tiết và kèm theo ví dụ minh họa để các bạn có thể dễ hiểu hơn.

1. Cách tham chiếu dữ liệu giữa các sheet trong excel bằng hàm Vlookup 

 

Để có thể tham chiếu dữ liệu giữa hai hay nhiều sheet trong Excel thì chúng ta có thể dùng hàm Vlookup theo công thức như sau:

= VLOOKUP(Lookup_value, Table_array, Col_index_num, [Range_lookup])

 

Trong đó:

• Lookup_value có nghĩa là giá trị cần dò tìm
•  
• Table_array có nghĩa là bảng giới hạn để dò tìm và dùng phím F4 để cố định bảng dò tìm cho mục đích sao chép công thức.
•  
• Col_index_num có nghĩa là số thứ tự của cột dữ liệu trong bảng cần dò tìm
•  
• Range_lookup có nghĩa là giá trị logic (True=1, False=0)
•  

• Trong trường hợp nếu Range_lookup = 1 (True) có nghĩa là so sánh tương đối

•  

• Trong trường hợp nếu Range_lookup = 0 (False) có nghĩa là so sánh chính xác
•  
•  Trong trường hợp nếu như chúng ta bỏ qua thành phần này khi viết công thức thì Excel tự động hiểu là Range_lookup = 1

 

Sau đó chúng ta cần kết hợp với hàm IFERROR để kiểm tra từng trang tính, trong trường hợp nếu hàm VLOOKUP đầu tiên không tìm thấy kết quả phù hợp trên trang tính đầu tiên thì hãy tìm kiếm trong sheet tiếp theo.

 

Ví dụ:  Hãy áp dụng cách tham chiếu bằng hàm Vlookup, chúng ta hãy điền tên và số số lượng hàng hóa trong trang tính WEST và EAST vào bảng Summary dưới đây

 

 

 

Đầu tiên, chúng ta cần dùng hàm VLOOKUP để tìm kiếm số lượng đơn hàng tròn A2 trên trang tính EAST và trả về giá trị từ cột B (cột thứ 2 trong table_array A2:C6). Trong trường hợp nếu không tìm thấy kết quả phù hợp thì chúng ta hãy tìm kiếm trang tính WEST, còn nếu cả hai hàm VLOOKUP không thành công thì trả về giá trị “Not found” theo công thức như sau:

=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 2, FALSE), “Not found”))

 

 

 

 

Tiếp theo để trả lại số lượng, chúng ta chỉ cần thay đổi địa chỉ cột thành 3 theo công thức như sau:

=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 3, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 3, FALSE), “Not found”))

2. Cách tham chiếu dữ liệu trong excel bằng hàm indirect

Để có thể tham chiếu dữ liệu giữa các sheet trong excel bằng hàm indirect chúng ta hãy cùng tìm hiểu ví dụ sau đây:

 

 

 

Với dữ liệu trên, chúng ta có doanh thu các sản phẩm của 3 thành phố là HCM;Ha Noi; Da Nang tương ứng với ba sheet và dữ liệu của ba tỉnh như sau tương ứng với từng sheet:

.

 

 

Vậy hãy tổng hợp ở sheet tổng hợp như sau:

 

 

 

Chúng ta sẽ có nhiều cách để tổng hợp dữ liệu từ nhiều sheet, chỉ ba sheet như trên là đơn giản, nhưng thử tưởng tượng nếu con số là 15, 25,... sheet thì sao, chúng ta đặt lệnh cho từng sheet 1 với mỗi tên sheet khác nhau, nhưng nếu thống nhất đúng tên sheet với tên của từng phân vùng dữ liệu thì thật đơn giản. Ở trường hợp này, đầu tiên chúng ta sẽ dùng hàm Vlookup như sau ở ô B3 như sau:

=VLOOKUP(B$2,HCM!$A$2:$B$5,2,0) 

 

và cho kết quả như hình dưới đây

 

Tuy nhiên, chúng ta nên chú ý lệnh của sheet HCM sẽ không có cấu trúc sheet là ‘sheet’!(vùng dữ liệu) do HCM là một cụm từ không có khoảng trống, còn trong trường hợp của sheet Ha Noi sẽ thực hiện công thức như sau:

 =VLOOKUP(B$2,‘Ha Noi’!$A$2:$B$5,2,0)

 

 

 

 

Tiếp theo, chúng ta cần sử dụng hàm Indirect để tự động hóa lệnh 1 lần như sau tương tự ở ô B3 theo công thức như sau 

=VLOOKUP(B$2,INDIRECT(“‘”&$A3&”‘!$A$2:$B$5”),2,0)

 

 

Và kết quả trả về như hình minh họa sau đây:

 

 

 

 

Tiếp theo, chúng ta có thể thực hiện tương tự theo công thức như sau : 

=VLOOKUP(B$2,‘Ha Noi’!$A$2:$B$5,2,0)

và

=VLOOKUP(B$2,INDIRECT(“‘”&$A4&”‘!$A$2:$B$5”),2,0)

 

Sau đó, chúng ta kéo thả chuột cho các ô còn lại kết quả trả về như sau: 

 

 

 

3. Một số lưu ý khi tham chiếu dữ liệu giữa các sheet trong excel

• Khi chúng ta tạo tham chiếu tới trang tính khác bằng cách chọn ô thì phần mềm Excel luôn mặc định là  tham chiếu tuyệt đối. Do vậy, nếu như chúng ta định sao chép công thức mới lập sang các ô khác thì hãy chắc rằng kí hiệu cố định ($) được bỏ khỏi ô tham chiếu để đưa tham chiếu về dạng tương đối hoặc hỗn hợp.
•  
• Trong trường hợp nếu tên trang tính chứa dấu cách hoặc ký tự không phải chữ cái, chúng ta phải đặt tên trang tính trong dấu nháy chẳng hạn như một tham chiếu ngoại tới ô A1 trong trang tính có tên là Thống kê thì có công thức như sau: ‘Thống kê’!A1.

 

Qua bài viết chia sẻ của mình trên đây về các cách tham chiếu dữ liệu giữa hai hay nhiều sheet trong Excel, hy vọng sẽ hữu ích đối với các bạn và các bạn sẽ có thể ứng dụng thành công được các cách thực hiện này.