GIẢI THÍCH THAM CHIẾU 3-D TRONG EXCEL VÀ CÁCH TÍNH TOÁN ĐƠN GIẢN

Đây là hướng dẫn chi tiết về tham chiếu 3-D trong Excel, giúp bạn hiểu cách thực hiện các phép tính trên nhiều bảng tính chỉ bằng một công thức 3-D duy nhất.

Tham chiếu 3-D, hay còn được gọi là tham chiếu không gian ba chiều trong Excel, là một tính năng thực sự hữu ích. Đây là một cách thuận lợi để tính toán số liệu trên nhiều bảng tính giống nhau về cấu trúc, và rất có thể sẽ thay thế cho việc sử dụng chức năng CONSOLIDATE trong Excel. Có thể bạn sẽ cảm thấy khó hiểu lúc đầu, nhưng mọi thứ sẽ trở nên dễ dàng hơn thông qua các ví dụ mình sắp đưa ra.

THAM CHIẾU 3D TRONG EXCEL LÀ GÌ?

Tham chiếu 3-D, như đã đề cập, cho phép tham chiếu đến một hoặc nhiều ô trên một loạt các bảng tính trong Excel. Điều đặc biệt ở đây là bạn không chỉ tham chiếu vào một dãy ô mà còn bao gồm cả một dãy tên của bảng tính. Quan trọng là các bảng tính này cần có cùng định dạng và dữ liệu tương tự. Hãy quan sát ví dụ sau để hiểu rõ hơn.

Hãy tưởng tượng bạn đang có các bảng báo cáo doanh số theo từng tháng và chúng được phân tách thành 4 bảng tính:

Bạn muốn tính tổng toàn bộ số doanh số, tiêu biểu là tổng doanh số mỗi tháng từ bốn bảng tính. Cách thông thường sẽ là cộng các ô chứa doanh số tổng cộng của từng tháng:

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

Nếu bạn có đến 12 bảng tính cho cả năm, hoặc ngay cả nhiều hơn cho nhiều năm liên tiếp, điều đó sẽ rất mất công. Thay vào đó, bạn có thể sử dụng hàm SUM với tham chiếu 3-D để đơn giản hóa việc tính toán trên mỗi trang tính.

=SUM(Jan:Apr!B6)

Hàm SUM với công thức này sẽ tiến hành phép cộng giống như công thức dài đã nêu trên, nó sẽ cộng doanh số ở ô B6 trên mỗi bảng tính từ Tháng Một đến Tháng Tư:

Lưu ý: Nếu bạn muốn giữ nguyên tham chiếu ô khi sao chép công thức 3-D sang các ô khác mà không muốn chúng biến đổi, bạn nên sử dụng tham chiếu tuyệt đối, chẳng hạn như =SUM(Jan:Apr!$B$6).

Không chỉ tính tổng số doanh số cho mỗi tháng, bạn cũng có thể tính tổng cho một dãy ô cụ thể bằng cách thêm dải ô vào công thức 3-D:

=SUM(Jan:Apr!B2:B5)

Nếu bạn cần tính tổng doanh số riêng rẽ cho từng sản phẩm, chỉ việc tạo một bảng sản phẩm có sắp xếp đúng thứ tự như bảng tính hàng tháng, sau đó nhập công thức 3-D vào ô đầu tiên, ở đây là ô B2:

=SUM(Jan:Apr!B2)

Nhớ rằng nên dùng tham chiếu tương đối – tức là tham chiếu ô không có dấu $, để công thức được tự động điều chỉnh cho mỗi ô khi sao chép xuống các dòng tiếp theo:

Dựa vào ví dụ trên để thấy rằng cú pháp chung cho tham chiếu 3-D và công thức 3-D trong Excel như sau:

Tham chiếu 3-D trong Excel

First_sheet:Last_sheet!cell hoặc
First_sheet:Last_sheet!range

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

=Function(First_sheet:Last_sheet!cell) hoặc
=Function(First_sheet:Last_sheet!range)

Khi

Trong Excel, sử dụng công thức ba chiều (3-D) giúp tính toán tổng các trang tính được nằm giữa trang đầu tiên và trang cuối cùng bạn chọn.

HƯỚNG DẪN TẠO THAM CHIẾU 3-D TRONG EXCEL

Theo các bước dưới đây để tạo công thức với tham chiếu ba chiều (3-D):

Chọn ô mà bạn muốn điền công thức 3-D.

Nhập dấu bằng (=) theo sau là tên hàm và dấu mở ngoặc, ví dụ: =SUM(

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

Nắm giữ phím Shift và click vào tab trang tính cuối cùng để đưa vào tham chiếu 3-D.

Chọn ô hoặc dãy ô bạn muốn tính toán.

Nhấn Enter để hoàn tất việc nhập công thức 3D.

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

Excel cho phép bạn mở rộng tham chiếu 3D. Điều này có nghĩa là bạn có khả năng thêm trang tính mới vào sau này và di chuyển nó đến khu vực tham chiếu của công thức 3-D:

Giả sử bạn đang ở đầu năm và chỉ có dữ liệu của một vài tháng đầu. Bạn có thể thêm trang tính mới mỗi tháng và mong muốn chúng được tích hợp vào công thức khi được tạo.

Chẳng hạn, tạo trang tính mới – Tháng Mười Hai – và để nó là trang cuối cùng trong tham chiếu 3-D:

=SUM(Jan:Dec!B2:B5)

Sau khi trang tính mới được thêm vào, bạn chỉ việc di chuyển trang đó vào giữa Tháng Giêng và Tháng Mười Hai:

Bây giờ bạn có thể thấy rằng công thức SUM sẽ tự động cộng dồn giá trị của dãy ô (B2:B5) từ tất cả các trang tính nằm giữa trang tính đầu và cuối (Jan:Dec!). Hãy nhớ rằng các trang tính được kết nối với tham chiếu 3-D nên có cùng cách bố trí và loại dữ liệu.

CÁCH ĐẶT TÊN CHO THAM CHIẾU 3-D TRONG EXCEL

Để sử dụng công thức 3D trong Excel một cách thuận tiện hơn, có thể đặt tên cho tham chiếu 3-D của bạn.

Vào tab Formulas, chọn Defined Names và click vào Define Name.

Trong cửa sổ New Name, đặt một tên có ý nghĩa, dễ nhớ trong hộp Name, lưu ý không quá 255 ký tự. Trong trường hợp này, hãy đặt tên đơn giản, ví dụ: thamchieu-cuatoi

Xóa bản ghi hiện hành trong hộp Refers to, và nhập tham chiếu 3-D của bạn với cách sau:

Nhập dấu bằng (=).

Giữ Shift và click tab trang tính đầu tiên bạn muốn tham chiếu, sau đó click vào trang cuối cùng.

Chọn ô hoặc dãy ô cần tham chiếu. Cũng có thể tham chiếu toàn bộ cột bằng cách click vào ký tự cột trên trang tính.

Ví dụ này tạo ra một tham chiếu 3D cho cột B từ Tháng Một đến Tháng Tư như sau:

Sau khi chọn OK, tham chiếu 3D vừa tạo sẽ được lưu và đóng hộp thoại. Vậy là xong!

Bây giờ bạn có thể tính tổng cột B trên tất cả trang tính từ Tháng Một đến Tháng Tư chỉ bằng một công thức đơn giản như sau:

=SUM(my_reference)

HÀM EXCEL HỖ TRỢ THAM CHIẾU 3-D

Dưới đây là danh sách một số hàm trong Excel hỗ trợ sử dụng tham chiếu 3-D:

SUM – dùng để cộng tổng giá trị.

AVERAGE – để tính giá trị trung bình.

AVERAGEA – dùng để tính giá trị trung bình, bao gồm cả số, văn bản và logic.

COUNT – đếm số lượng ô chứa số liệu.

COUNTA – đếm số lượng ô không trống.

MAX – xác định giá trị lớn nhất trong các ô.

MAXA – tìm giá trị lớn nhất, kể cả dữ liệu văn bản và logic.

MIN – lấy giá trị nhỏ nhất.

MINA – có khả năng lấy giá trị nhỏ nhất, bao gồm cả văn bản và logic.

PRODUCT – phép nhân các số lại với nhau.

STDEV, STDEVA, STDEVP, STDEVPA – tính toán sự chệch lệch dựa trên mẫu từ một tập hợp số liệu xác định.

VAR, VARA, VARP, VARPA – đưa ra phương sai của một mẫu từ tập hợp số liệu đã cho.

LƯU Ý: VIỆC CHÈN, DỜI HOẶC XÓA CÁC TRANG TÍNH CÓ THỂ LÀM THAY ĐỔI THAM CHIẾU 3-D TRONG EXCEL

Do tham chiếu 3-D trong Excel được định nghĩa bởi một trang tính đầu và một trang tính cuối, các điểm mốc này khi thay đổi có thể làm biến đổi tham chiếu, và từ đó công thức 3-D của bạn cũng sẽ bị thay đổi. Chúng ta hãy quan sát xem điều gì xảy ra khi bản di chuyển hoặc xóa bỏ các điểm mốc tham chiếu 3-D, hoặc khi chèn, xóa và di chuyển các trang tính nằm giữa các điểm mốc này.

Sẽ được làm rõ hơn qua một ví dụ cụ thể dưới đây.

Hãy xem xét lại công thức 3-D mà chúng ta từng thiết lập:

Chèn, sao chép hoặc di chuyển trang tính nằm giữa các điểm mốc. Khi bạn thực hiện chèn, sao chép hoặc di chuyển các trang tính nằm giữa điểm mốc tham chiếu 3D (ví dụ như từ thành Một đến Tháng Tư trong trường hợp này), thì phạm vi ô tham chiếu (từ B2 đến B5) của tất cả các trang tính mới sẽ được tính toán vào công thức.

Xóa hay chuyển trang tính ra khỏi phạm vi điểm mốc. Khi loại bỏ hoặc dịch chuyển bất cứ trang tính nào ra khỏi phạm vi điểm mốc, trang tính đó sẽ không còn được tính vào công thức 3-D nữa.

Di chuyển điểm mốc. Nếu bạn di chuyển điểm mốc bất kỳ (trang tính Tháng Một hoặc Tháng Tư, hoặc cả hai) đến vị trí mới, Excel tự động chỉnh sửa công thức 3-D để bao gồm các trang tính mới nằm giữa các điểm mốc và loại bỏ những trang tính nằm ngoài.

Đổi chỗ các điểm mốc. Trong Excel, việc đổi chỗ các điểm mốc tham chiếu 3-D sẽ khiến cho một trong các trang tính điểm mốc thay đổi. Chẳng hạn, nếu bạn chuyển trang tính đầu tiên (Tháng Một) sau trang tính cuối cùng (Tháng Tư), trang tính Tháng Một sẽ không còn trong tham chiếu 3-D, dẫn đến kết quả là Feb:Apr!B2:B5.

Chuyển trang tính cuối cùng (Tháng Tư) đến vị trí trước trang tính đầu tiên (Tháng Một) cũng làm thay đổi tương tự. Trong ví dụ này, Tháng Tư khi bị di chuyển sẽ loại bỏ khỏi tham chiếu 3-D, làm thay đổi kết quả thành Jan:Mar!B2:B5.

Chú ý rằng ngay cả khi hoàn tác vị trí ban đầu của điểm mốc, tham chiếu 3-D cũ không tự động khôi phục. Trong ví dụ trên, dù trang tính Tháng Một được chuyển lại vị trí cũ, tham chiếu 3-D vẫn chỉ ra Feb:Apr!B2:B5, buộc bạn phải chỉnh sửa công thức thủ công để thêm trang Tháng Một trở lại.

Xóa bỏ điểm mốc. Khi xóa một trong hai trang tính được xem là điểm mốc, nó sẽ bị loại khỏi tham chiếu 3D, và điểm mốc thay thế được xác định như sau:

> Nếu trang tính đầu tiên bị xóa, điểm mốc mới sẽ là trang tiếp theo sau trang bị xóa. Trong tình huống của ví dụ, nếu Tháng Một bị xóa thì tham chiếu sẽ thay đổi thành Feb:Apr!B2:B5.

> Nếu trang tính cuối cùng bị xóa, điểm mốc mới sẽ là trang trước đó. Với ví dụ, nếu Tháng Tư bị xóa thì tham chiếu 3D biến thành Jan:Mar!B2:B5.

Đó chính là cách bạn tạo và ứng dụng tham chiếu 3D trong Excel. Qua đó, có thể thấy rằng đây là phương pháp hữu ích và nhanh chóng để tính toán dữ liệu trên nhiều trang tính. Trong khi việc cập nhật công thức dài có thể cảm thấy mệt mỏi, các công thức 3-D chỉ yêu cầu bạn cập nhật vài tham chiếu hoặc chèn các trang tính mới mà không cần chỉnh lại công thức đã có.

Xin mời truy cập thêm nhiều bài viết tại:   Siêu Marketing

Cảm ơn bạn đã dành thời gian đọc bài viết ngày hôm nay. Mong được gặp lại các bạn vào tuần sau!

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop