Excel SUM và OFFSET Công thức

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2024-01-07 19:07

Nếu trang tính Excel của bạn bao gồm các phép tính dựa trên một phạm vi ô thay đổi, hãy sử dụng hàm SUM và hàm OFFSET cùng nhau trong công thức SUM OFFSET để đơn giản hóa công việc cập nhật các phép tính.

Hướng dẫn trong bài viết này áp dụng cho Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013 và Excel 2010.

Tạo Phạm vi Động với Chức năng SUM và OFFSET

Nếu bạn sử dụng các phép tính trong một khoảng thời gian liên tục thay đổi - chẳng hạn như xác định doanh số bán hàng trong tháng - hãy sử dụng hàm OFFSET trong Excel để thiết lập phạm vi động thay đổi khi số liệu bán hàng của mỗi ngày được thêm vào.

Tự nó, hàm SUM thường có thể phù hợp với việc chèn các ô dữ liệu mới vào phạm vi được tính tổng. Một ngoại lệ xảy ra khi dữ liệu được chèn vào ô nơi hàm hiện đang được đặt.

Trong ví dụ bên dưới, số liệu bán hàng mới cho mỗi ngày được thêm vào cuối danh sách, buộc tổng số liên tục dịch chuyển xuống một ô mỗi lần khi dữ liệu mới được thêm vào.

Để làm theo hướng dẫn này, hãy mở một trang tính Excel trống và nhập dữ liệu mẫu. Trang tính của bạn không cần phải được định dạng như ví dụ, nhưng hãy đảm bảo nhập dữ liệu vào các ô giống nhau.

Nếu chỉ sử dụng hàm SUM để tổng dữ liệu, thì phạm vi ô được sử dụng làm đối số của hàm sẽ cần được sửa đổi mỗi khi dữ liệu mới được thêm vào.

Bằng cách sử dụng các hàm SUM và OFFSET cùng nhau, phạm vi được tính tổng trở nên động và thay đổi để phù hợp với các ô dữ liệu mới. Việc thêm các ô dữ liệu mới không gây ra vấn đề vì phạm vi tiếp tục điều chỉnh khi mỗi ô mới được thêm vào.

Cú pháp và Đối số

Trong công thức này, hàm SUM được sử dụng để tổng phạm vi dữ liệu được cung cấp làm đối số. Điểm bắt đầu cho phạm vi này là tĩnh và được xác định là tham chiếu ô đến số đầu tiên được tính tổng bởi công thức.

Hàm OFFSET được lồng bên trong hàm SUM và tạo ra một điểm cuối động cho phạm vi dữ liệu được tính bằng công thức. Điều này được thực hiện bằng cách đặt điểm cuối của dải ô thành một ô phía trên vị trí của công thức.

Cú pháp công thức là:

=SUM (Phạm vi Bắt đầu: OFFSET (Tham chiếu, Hàng, Cols))
Các đối số là:

Phạm vi Bắt đầu: Điểm bắt đầu cho phạm vi ô sẽ được tính tổng bởi hàm SUM. Trong ví dụ này, điểm bắt đầu là ô B2.
Tham chiếu: Tham chiếu ô bắt buộc được sử dụng để tính toán điểm cuối phạm vi. Trong ví dụ, đối số Tham chiếu là tham chiếu ô cho công thức vì phạm vi kết thúc một ô phía trên công thức.
Rows: Số hàng ở trên hoặc dưới đối số Tham chiếu được sử dụng để tính toán độ lệch là bắt buộc. Giá trị này có thể là dương, âm hoặc được đặt thành không. Nếu vị trí bù nằm trên đối số Tham chiếu, giá trị là âm. Nếu độ lệch dưới đây, đối số Hàng là dương. Nếu phần bù nằm trong cùng một hàng, đối số bằng không. Trong ví dụ này, phần bù bắt đầu một hàng phía trên đối số Tham chiếu, vì vậy giá trị cho đối số là âm (-1).
Cols: Số cột ở bên trái hoặc bên phải của đối số Tham chiếu được sử dụng để tính toán độ lệch. Giá trị này có thể là dương, âm hoặc được đặt thành không. Nếu vị trí bù nằm ở bên trái của đối số Tham chiếu, giá trị này là âm. Nếu độ lệch sang phải, đối số Cols là dương. Trong ví dụ này, dữ liệu được tính tổng nằm trong cùng một cột với công thức, vì vậy giá trị cho đối số này bằng 0.


Sử dụng Công thức SUM OFFSET để Tổng Dữ liệu Bán hàng
Ví dụ này sử dụng công thức SUM OFFSET để trả về tổng số cho các số liệu bán hàng hàng ngày được liệt kê trong cột B của trang tính. Ban đầu, công thức được nhập vào ô B6 và tính tổng dữ liệu bán hàng trong bốn ngày.
Bước tiếp theo là di chuyển công thức SUM OFFSET xuống một hàng để nhường chỗ cho tổng doanh số của ngày thứ năm. Điều này được thực hiện bằng cách chèn một hàng mới 6, chuyển công thức sang hàng 7.
Kết quả của việc di chuyển, Excel sẽ tự động cập nhật đối số Tham chiếu vào ô B7 và thêm ô B6 vào phạm vi được tính bằng công thức.

Chọn ô B6, là vị trí mà kết quả công thức sẽ hiển thị ban đầu.

Chọn tab Công thứccủa dải băng.







Chọn Math & Trig.







Chọn SUM.






Trong hộp thoại Đối số Hàm, đặt con trỏ vào hộp văn bản Number1.

Trong trang tính, chọn ô B2để nhập tham chiếu ô này vào hộp thoại. Vị trí này là điểm cuối tĩnh cho công thức.






Trong hộp thoại Đối số Hàm, đặt con trỏ vào hộp văn bản Number2.

Nhập OFFSET (B6, -1, 0). Hàm OFFSET này tạo thành điểm cuối động cho công thức.







Chọn OKđể hoàn thành chức năng và đóng hộp thoại. Tổng số xuất hiện trong ô B6.








Thêm Dữ liệu Bán hàng Ngày Tiếp theo
Để thêm dữ liệu bán hàng của ngày hôm sau:

Nhấp chuột phải vào tiêu đề hàng cho hàng 6.

Chọn Chènđể chèn một hàng mới vào trang tính. Công thức SUM OFFSET di chuyển xuống một hàng đến ô B7 và hàng 6 hiện đang trống.






Chọn ô A6và nhập số 5để cho biết rằng tổng doanh số của ngày thứ năm đang được nhập.

Chọn ô B6, nhập $ 1458,25, sau đó nhấn Enter.






Cập nhật ô B7 lên tổng số tiền mới là $ 7137,40.

Khi bạn chọn ô B7, công thức cập nhật sẽ xuất hiện trong thanh công thức.
=SUM (B2: OFFSET (B7, -1, 0))
Hàm OFFSET có hai đối số tùy chọn: Chiều cao và Chiều rộng, không được sử dụng trong ví dụ này. Các đối số này cho hàm OFFSET biết hình dạng của đầu ra về số lượng hàng và cột.
Bằng cách bỏ qua các đối số này, hàm sẽ sử dụng chiều cao và chiều rộng của đối số Tham chiếu, thay vào đó, trong ví dụ này, đối số này cao một hàng và rộng một cột.