TÍNH TOÁN NHIỀU TRANG TÍNH CHỈ VỚI MỘT CÔNG THỨC: THAM CHIẾU 3-D TRONG EXCEL

Bài hướng dẫn ngắn này giải thích điều cốt lõi của tham chiếu 3-D trong Excel và chỉ bạn cách tính toán nhiều trang tính chỉ với một công thức 3-D.

Một trong những tính năng tham chiếu  tuyệt vời nhất của Excel chính là tham chiếu 3D, hay còn được biết đến như tham chiếu trong không gian ba chiều.

Một tham chiếu 3D trong Excel tham chiếu đến cùng một ô hay dải ô trên nhiều trang tính. Đây là cách rất thuận tiện và nhanh chóng nhằm tính toán dữ liệu ở nhiều trang tính có cùng cấu trúc, và nó có thể là sự thay thế lý tưởng cho tính năng CONSOLIDATE trong Excel. Nghe có vẻ mơ hồ, nhưng đừng lo lắng, các ví dụ sau đây sẽ giúp mọi thứ rõ ràng hơn.

Như đã nói ở trên, tham chiếu 3D trong Excel cho phép bạn tham chiếu một hay nhiều ô ở nhiều trang tính. Nói cách khác, nó không chỉ tham chiếu một dải ô, mà còn một dải các tên của trang tính. Điểm mấu chốt ở đây chính là tất cả các trang tính được tham chiếu phải có cùng cấu trúc và có cùng loại dữ liệu. Hãy xem xét ví dụ dưới đây.

Giả sử bạn có các bảng báo cáo doanh số hàng tháng nằm ở 4 trang tính khác nhau:

Điều bạn tìm kiếm đó là tính tổng tất cả doanh số, cụ thể là cộng tổng của mỗi tháng ở bốn trang tính. Giải pháp rõ ràng nhất chính là cộng các ô có chứa tổng doanh số mỗi tháng như mọi khi:

=Jan!B6+Feb!B6+Mar!B6+Apr!B6

Nhưng nếu bạn có 12 trang tính cho cả năm luôn thì sao? Hay thậm chí là nhiều trang tính hơn cho vài năm? Sẽ có rất nhiều việc phải làm. Thay vào đó, bạn có thể dùng hàm SUM có tham chiếu 3D để tính tất cả trang tính.

=SUM(Jan:Apr!B6)

Công thức SUM này thực hiện cùng một phép tính như công thức dài ở phía trên, cụ thể là cộng tất cả các giá trị ở ô B6 của tất cả các trang tính – các trang tính này nằm giữa hai trang tính biên mà bạn định rõ, Tháng Một và Tháng Tư trong ví dụ này:

Mẹo: Nếu bạn muốn sao chép công thức 3-D cho một vài ô và bạn không muốn các tham chiếu ô bị thay đổi, thì bạn có thể khóa chúng bằng cách thêm ký hiệu $, cụ thể là bằng cách sử dụng tham chiếu ô tuyệt đối như =SUM(Jan:Apr!$B$6).

Nếu bạn không muốn tính tổng của từng tháng, không sao, bạn có thể thêm trực tiếp dải ô cần tính vào công thức 3-D:

=SUM(Jan:Apr!B2:B5)

Nếu bạn muốn tính tổng doanh thu của mỗi sản phẩm riêng rẽ, thì hãy lập một bảng sản phẩm trong đó các sản phẩm được sắp xếp theo đúng thứ tự ở trang tính hàng tháng, rồi nhập công thức 3-D này vào ô trên cùng, trong trường hợp này là ô B2:

=SUM(Jan:Apr!B2)

Hãy nhớ sử dụng tham chiếu ô tương đối – tham chiếu ô không có $, để công thức được điều chỉnh theo từng ô khi được sao chép xuống các ô dưới:

Dựa trên các công thức trên, hãy rút ra cú pháp chung cho tham chiếu 3-D và công thức 3-D trong Excel:

Tham chiếu 3-D trong Excel

First_sheet:Last_sheet!cell hay
First_sheet:Last_sheet!range

Công thức 3-D trong Excel:

=Function(First_sheet:Last_sheet!cell) hay
=Function(First_sheet:Last_sheet!range)

Khi sử dụng các công thức 3-D này trong Excel, tất cả các trang tính nằm giữa trang đầu tiên và trang cuối cùng đều được thêm vào phép tính.

Để lập công thức có tham chiếu 3-D trong câu lệnh, hãy làm theo các bước sau:

Nhấp vào ô mà bạn muốn nhập công thức 3-D.

Gõ dấu bằng (=), nhập tên hàm, rồi nhập dấu ngoặc mở, ví dụ =SUM(

Nhấp vào tab của trang tính đầu tiên mà bạn muốn thêm tham chiếu 3-D.

Trong khi nhấp giữ phím Shift, hãy nhấp tab của trang tính cuối cùng – trang tính được thêm vào tham chiếu 3-D của bạn.

Chọn ô hay dải ô mà bạn muốn tính.

Nhấp phím Enter để hoàn thành công thức 3D.

TẠO THÊM TRANG TÍNH MỚI VÀO CÔNG THỨC 3D TRONG EXCEL

Tham chiếu 3D trong Excel có thể mở rộng. Nó có nghĩa là bạn có thể tạo tham chiếu 3-D ở một thời điểm nào đó, rồi chèn trang tính mới vào, rồi di chuyển trang tính đến dải ô mà công thức 3-D tham chiếu:

Giả sử bây giờ đầu năm và bạn chỉ có dữ liệu của vài tháng đầu. Tuy nhiên, trang tính mới có thể được thêm vào mỗi tháng và bạn muốn có các trang tính này trong công thức khi các trang tính này được tạo ra.

Về việc này, hãy tạo một trang tính mới, ví dụ Tháng Mười Hai, rồi biến nó thành trang tính cuối cùng trong tham chiếu 3-D của bạn:

=SUM(Jan:Dec!B2:B5)

Khi một trang tính được thêm vào cửa sổ, chỉ cần di chuyển nó vào giữa Tháng Một và Tháng Mười Hai:

Xong rồi! Bởi vì công thức SUM của bạn chứa tham chiếu 3-D, nó sẽ cộng dải ô được thêm vào (B2:B5) trong tất cả trang tính nằm giữa các trang tính xác định (Jan:Dec!). Chỉ cần nhớ rằng tất cả trang tính được thêm vào tham chiếu 3-D trong Excel nên có cách sắp xếp dữ liệu giống nhau và có cùng loại dữ liệu.

TẠO TÊN CHO MỘT THAM CHIẾU 3-D TRONG EXCEL

Để khiến việc sử dụng công thức 3D trong Excel của bạn dễ dàng hơn, bạn có thể tạo tên cho tham chiếu 3D của mình.

Trên tab Formulas, hãy đi đến nhóm Defined Names rồi nhấp Define Name.

Trong hộp thoại New Name, hãy gõ một cái tên có nghĩa và dễ nhớ vào hộp Name, tối đa là 255 ký tự. Trong ví dụ này, hãy đặt tên đơn gianr thôi, ví dụ: thamchieu-cuatoi

Xóa nội dung của Refers ra khỏi hộp, hãy nhập tham chiếu 3D vào đây theo cách sau đây:

Gõ = (dấu bằng).

Nhấp giữ Shift, nhấp tab của trang tính đầu tiên mà bạn muốn tham chiếu, rồi nhấp vào trang tính cuối cùng.

Hãy chọn ô hay dải ô cần tham chiếu. Bạn cũng có thể tham chiếu toàn bộ cột bằng cách nhấp ký tự cột trong trang tính.

Ở ví dụ này, hãy tạo ra một tham chiếu 3D trong Excel cho toàn bộ cột B từ trang tính Tháng Một đến Tháng Tư. Vì vậy, bạn có kết quả như thế này:

Hãy nhấp nút OK để lưu tham chiếu 3D vừa mới tạo rồi đóng hộp thoại. Xong!

Và bây giờ, để tính tổng các con số ở cột B ở tất cả các trang tính từ Tháng Một đến Tháng Tư, bạn chỉ cần sử dụng công thức đơn giản này:

=SUM(my_reference)

Sau đây là danh sách các hàm trong Excel cho phép sử dụng tham chiếu 3-D:

SUM – tính tổng các giá trị số.

AVERAGE – tính giá trị trung bình của các con số.

AVERAGEA – tính giá trị trung bình, gồm có chữ số, ký tự và các phép lôgic.

COUNT – đếm các ô có số.

COUNTA – đếm các ô không rỗng.

MAX – trả về giá trị lớn nhất.

MAXA – trả về giá trị lớn nhất, bao gồm ký tự và phép lôgic.

MIN – trả về giá trị nhỏ nhất.

MINA – trả về giá trị nhỏ nhất, bao gồm ký tự và phép lôgic.

PRODUCT –  nhân các số.

STDEV, STDEVA, STDEVP, STDEVPA – tính độ lệch dựa trên mẫu của chuỗi các giá trị xác định.

VAR, VARA, VARP, VARPA – trả về phương sai dựa trên mẫu của chuỗi các giá trị.

LƯU Ý: KHI CHÈN, DI CHUYỂN VÀ XÓA TRANG TÍNH SẼ LÀM THAY ĐỔI THAM CHIẾU 3-D TRONG EXCEL

Bởi vì, mỗi tham chiếu 3-D trong Excel được xác định bằng một trang tính bắt đầu và một trang tính kết thúc, nên hãy gọi chúng là điểm nút tham chiếu 3-D, việc thay đổi điểm nút sẽ thay đổi tham chiếu, vì vậy sẽ thay đổi công thức 3-D của bạn. Và bây giờ, hãy xem điều gì sẽ xảy ra khi bạn di chuyển hay xóa điểm nút tham chiếu 3-D, hay chèn, xóa và di chuyển các trang tính nằm trong điểm nút.

Mình sẽ giải thích chi tiết hơn  qua ví dụ để mọi gười dễ hiểu hơn nhé.

Xét công thức 3-D mà chúng ta đã tạo ra trước đó:

Chèn, di chuyển hay sao chép trang tính bên trong điểm nút. Nếu bạn chèn, sao chép hay di chuyển các trang tính giữa điểm nút tham chiếu 3D (trang tính Tháng Một và Tháng Tư trong ví dụ này), thì dải ô tham chiếu (từ ô B2 đến ô B5) ở tất cả các trang tính vừa được thêm vào đều được thêm vào phép tính.

Xóa trang tính, hay di chuyển trang tính ra khỏi điểm nút. Khi bạn xóa bất cứ trang tính nào ở trong điểm nút, hay di chuyển trang tính ra khỏi điểm nút, những trang tính này sẽ được loại ra khỏi công thức 3-D của bạn.

Di chuyển một điểm nút. Nếu bạn di chuyển một điểm nút bất kỳ (trang tính Tháng Một hay Tháng Tư, hay cả hai) đến một vị trí mới trong cùng cửa sổ, Excel sẽ điều chỉnh công thức 3-D để thêm vào các trang tính mới nằm giữa điểm nút, và xóa các trang tính nằm ngoài điểm nút.

Hoán đổi điểm nút. Việc hoán đổi điểm nút tham chiếu 3-D trong Excel sẽ dẫn đến việc thay đổi một trong các trang tính điểm nút. Ví dụ, nếu bạn di chuyển trang tính mở đầu ( Tháng Một) ra phía sau trang tính kết thúc (Tháng Tư), trang tính Tháng Một sẽ bị xóa khỏi tham chiếu 3-D, điều này sẽ dẫn đến kết quả Feb:Apr!B2:B5.

Việc di chuyển trang tính kết thúc (Tháng Tư) ra trước trang tính mở đầu (Tháng Một) cũng có ảnh hưởng tương tự. Trong trường hợp này, trang tính Tháng Tư sẽ bị xóa khỏi tham chiếu 3-D, việc này dẫn đến kết quả  Jan:Mar!B2:B5.

Hãy lưu ý rằng việc khôi phục vị trí ban đầu sẽ không khôi phục tham chiếu 3-D ban đầu. Trong ví dụ trên, ngay cả khi chúng ta di chuyển trang tính Tháng Một về vị trí ban đầu, thì tham chiếu 3-D vẫn có dạng Feb:Apr!B2:B5, và bạn phải chỉnh sửa nó một cách thủ công để thêm trang tính Tháng Một vào phép tính.

Xóa điểm nút. Khi bạn xóa một trong hai trang tính điểm nút, nó sẽ bị xóa khỏi tham chiếu 3D, và điểm nút bị xóa thay đổi theo hướng này:

> Nếu trang tính đầu tiên bị xóa, thì điểm nút thay đổi thành trang tính ở ngay sau trang tính bị xóa. Trong ví dụ này, nếu trang tính Tháng Một bị xóa, thì tham chiếu 3D thay đổi thành Feb:Apr!B2:B5.

> Nếu trang tính cuối cùng bị xóa, thì điểm nút thay đổi thành trang tính ở phía trước. Trong ví dụ này, nếu trang tính Tháng Tư bị xóa, thì tham chiếu 3D thay đổi thành Jan:Mar!B2:B5.

Đây là cách bạn tạo ra và sử dụng tham chiếu 3D trong Excel. Như bạn có thể thấy, đây là cách rất thuận tiện và nhanh chóng để tính toán cùng một dải ô nằm trên nhiều trang tính. Khi việc cập nhật các công thức dài dòng để tham chiếu các trang tính khác nhau có thể trở nên nhàm chán, thì công thức 3-D trong Excel chỉ yêu cầu việc cập nhật một vài tham chiếu, hay bạn chỉ cần chèn các trang tính mới  vào giữa điểm nút tham chiếu 3D mà không cần thay đổi công thức.

Các bạn có thể xem nhiều bài viết hơn tại đây :   blog.hocexcel.online

Cảm ơn các bạn vì đã đọc bài viết này hôm nay. Hẹn gặp lại các bạn vào tuần tới!