GIAO DIỆN
CƠ BẢN
TÌM KIẾM
BIỂU ĐỒ
CÁC CHỦ ĐỀ
BÀI MỚI NHẤT
Dự án mới của mình là gamehow.net, mời anh em ghé thăm và góp ý ạ.

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).

banquyen png
Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.

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.

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.

bang luong nhan vien JPG

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é.

bang luong nhan vien JPG

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.

AND
AVEDEV
AVERAGE
AVERAGEA
COUNT
COUNTA
DEVSQ
LARGE
MAX
MAXA
MEDIAN
MIN
MINA
OR
PERCENTILE
PRODUCT
QUARTILE
RANK
SKEW
SMALL
STDEV
STDEVA
STDEVP
STDEVPA
SUM
SUMSQ
TRIM
VAR
VARA
VARP
VARPA

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é!

Cùng chuyên mục:

Cách chèn link ảnh vào Excel trên máy tính

Cách chèn link ảnh vào Excel trên máy tính

Ngoài ra, bài viết còn hướng dẫn cách chèn link ảnh trực tuyến hàng loạt…

Cách tách và gộp ô trong Excel

Cách tách và gộp ô trong Excel

Trong bảng tính Microsoft Excel, có thể sẽ có lúc các bạn cần tách ô…

Hàm MONTH() trong Excel

Hàm MONTH() trong Excel

Cách dùng hàm MONTH() trong Excel

Hàm YEAR () trong Excel

Hàm YEAR () trong Excel

Cách dùng hàm YEAR () trong Excel

Hàm HLOOKUP() trong Excel

Hàm HLOOKUP() trong Excel

Cách dùng hàm HLOOKUP() trong Excel

Hàm VLOOKUP() trong Excel

Hàm VLOOKUP() trong Excel

Cách dùng hàm VLOOKUP() trong Excel

Hàm LOOKUP() trong Excel

Hàm LOOKUP() trong Excel

Cách dùng hàm LOOKUP() trong Excel

Hàm ABS() trong Excel

Hàm ABS() trong Excel

Cách dùng hàm ABS() trong Excel

Hàm SUM() trong Excel

Hàm SUM() trong Excel

Cách dùng hàm SUM() trong Excel

Hàm SQRT() trong Excel

Hàm SQRT() trong Excel

Cách dùng hàm SQRT() trong Excel

Hàm MOD() trong Excel

Hàm MOD() trong Excel

Cách dùng hàm MOD() trong Excel

Hàm SUMIF() trong Excel

Hàm SUMIF() trong Excel

Cách dùng hàm SUMIF() trong Excel

Hàm SUBTOTAL() trong Excel

Hàm SUBTOTAL() trong Excel

Cách dùng hàm SUBTOTAL() trong Excel

Hàm ROUND() trong Excel

Hàm ROUND() trong Excel

Cách dùng hàm ROUND() trong Excel

Hàm SUMPRODUCT() trong Excel

Hàm SUMPRODUCT() trong Excel

Cách dùng hàm SUMPRODUCT() trong Excel

Hàm COUNT() trong Excel

Hàm COUNT() trong Excel

Cách dùng hàm COUNT() trong Excel

Hàm COUNTA() trong Excel

Hàm COUNTA() trong Excel

Cách dùng hàm COUNTA() trong Excel

Hàm COUNTBLANK() trong Excel

Hàm COUNTBLANK() trong Excel

Cách dùng hàm COUNTBLANK() trong Excel

Hàm MAX() trong Excel

Hàm MAX() trong Excel

Cách dùng hàm MAX() trong Excel

Hàm MIN() trong Excel

Hàm MIN() trong Excel

Cách dùng hàm MIN() trong Excel

Top