Tham chiếu 3D trong excel: Tính toán trên nhiêu trang tính (nhiều sheet)

Trong bài này mình sẽ hướng dẫn cách tính toán trên nhiều trang tính (nhiều sheet) trong Excel bằng một công thức 3D. Với cách này bạn có thể liên kết dữ liệu trên sheet trong Excel dễ dàng.

Đây là một tính năng rất tuyệt vời và mạnh mẽ, nó giúp ta có thể tham chiếu đến một hay nhiều ô trên một trang tính khác. Cách làm này ta gọi là tham chiếu ba chiều (3D).

1. Tham chiếu 3D trong Excel là gì?

Tham chiếu 3D là cách tham chiếu đến các ô trong nhiều trang tính, tức là bạn đang ở trang tính này thì hoàn toàn có thể tham chiếu đến những trang tính khác rất dễ dàng.

Nói một cách dễ hiểu, nếu bạn muốn lấy dữ liệu từ một sheet khác thì hãy sử dụng tham chiếu 3D. Nó khác biệt với tham chiếu tương đối - tuyệt đối mà ta đã học ở bài trước.

Bài viết này được đăng tại [free tuts .net]

Khi giải quyết bài toán thống kê thì điều quan trọng nhất là dữ liệu ở các bảng phải có cùng cấu trúc và kiểu dữ liệu. Ví dụ như:

• Thống kê cuối năm: Mỗi tháng sẽ có một bảng thống kê, như vậy trong năm sẽ có 12 bảng và đặt ở 12 bảng tính khác nhau, nhưng có cùng cấu trúc.

Ví dụ: Bảng lương nhân viên trong 4 tháng có cấu trúc giống nhau.

Trong hình có 4 bảng tính từ T1 -> T4, có chung cấu trúc form và kiểu dữ liệu.

Giả sử mình muốn tính tổng lương mà nhân viên Cường nhận được trong 4 tháng, thì có thể áp dụng tham chiếu 3D để trỏ tới 4 ô D3 trên mỗi sheet.

* Thực tế thì Excel không quan tâm cấu trúc form của bạn là gì, mà chỉ quan tâm đến công thức tham chiếu. Vì vậy cấu trúc form không ảnh hưởng gì đến tham chiếu 3D, mà nó chỉ ảnh hưởng đến các bài toán thực tế mà thôi.

Ví dụ: Bạn có 2 sheet không có chung cấu trúc như sau:

• Sheet 1 là bảng danh sách sinh viên
• Sheet 2 là bảng danh sách khoa

Lúc này ở sheet 1 bạn muốn lấy thông tin khoa ở sheet 2 thì có thể sử dụng tham chiếu 3D. Và bài này các sheet không có chung cấu trúc form.

2. Cách tạo tham chiếu 3D trong Excel

Công thức chung để tạo tham chiếu 3D là:

'Tên sheet'![dãy ô]

* Lưu ý: Tên sheet bạn có thể không cần phải đặt cặp dấu nháy đơn, nhưng khi bạn chạy công thức thì Excel nó tự thêm dấu nháy đó.

Tạo công thức bằng lệnh

Ta sẽ chọn vào ô cần nhập công thức, sau đó gõ công thức thủ công theo quy tắc sau.

VD1: Để tham chiếu đến ô D3 nằm trong sheet có tên là T1 thì ta dùng công thức sau:

'T1'!D3

VD2: Để tham chiếu đến dãy ô từ D1 đến D6 trong sheet có tên là T4 thì công thức như sau:

'T1'!D3:D6

VD3: Để tham chiếu đến ô D1 từ bảng tính T1, T4 thì ta sử dụng công thức sau:

'T1'!D3, 'T4'!D3

VD4: Để thao chiếu đến ô D1 trong bảng tính T1, T2, T3, T4 thì ta có thể sử dụng công thức ở VD3. Tuy nhiên nếu có 100 sheet thì công thức sẽ rất dài, vì vậy ta sẽ áp dụng toán tử phạm vi : để tạo ra dãy sheet.

'T1:T4'!D3

* Lưu ý:

• Khi bạn thay đổi thứ tự, thêm trang tính, xóa trang tính thì sẽ ảnh hưởng đến các tham chiếu 3D đang trỏ đến những sheet đó.

Tạo công thức bằng chuột

• Chọn ô cần tạo công thức
• Nhập dấu =
• Click vào các sheet và các ô cần lấy dữ liệu, excel sẽ tự động tạo ra các tham chiếu. Bạn có thể vừa thao tác vừa nhìn trên thanh công thức thì sẽ thấy.

3. Ví dụ về tham chiếu 3D trong Excel

Bây giờ ta sẽ thực hành một vài ví dụ để bạn hiểu rõ hơn nhé.

Mình sẽ lấy hàm SUM để demo, đây là hàm dùng để tính tổng trong Excel.

Ta sẽ lấy lại bảng dữ liệu ở phần 1 nhé: Dữ liệu các sheet kia sẽ khác nhưng mình sẽ không show ra, các bạn tham khảo công thức thôi nhé.

Bài 1: Lấy lương tháng 4 của nhân viên Cường.

='T4'!D3

Bài 2: Tính tổng số lương của nhân viên tên nhận được trong cả 4 tháng.

=SUM('T1:T4'!D3)

Bài 3: Tính tổng toàn bộ số lương của 4 nhân viên trong 4 tháng.

=SUM('T1:T4'!D3:D6)

* Mẹo: nếu bạn có quá nhiều tham chiếu rườm rà thì hãy đặt tên name range để dễ quản lý hơn nhé.

4. Một số hàm thường dùng tham chiếu 3D trong Excel

Bảng dưới đây là những hàm chúng ta hay sử dụng kết hợp với tham chiếu 3D.

Trên là những thông tin cơ bản về cách sử dụng tham chiếu 3D để viết công thức Excel. Chúc bạn học tốt nhé!