Sử dụng hàm SUM và OFFSET để tìm tổng số cho phạm vi dữ liệu động
Nếu bảng tính Excel của bạn bao gồm các phép tính dựa trên phạm vi thay đổi của các ô, sử dụng hàm SUM và OFFSET cùng nhau trong công thức SUM OFFSET đơn giản hóa nhiệm vụ giữ cho các tính toán được cập nhật.
Tạo một phạm vi động Với hàm SUM và OFFSET
Nếu bạn sử dụng tính toán trong một khoảng thời gian liên tục thay đổi - chẳng hạn như tổng doanh thu trong tháng - hàm OFFSET cho phép bạn thiết lập phạm vi động mà tiếp tục thay đổi khi số liệu bán hàng của mỗi ngày được thêm vào.
Chính nó, hàm SUM thường có thể chứa các ô dữ liệu mới được chèn vào trong phạm vi được tổng kết.
Một ngoại lệ xảy ra khi dữ liệu được chèn vào ô có vị trí hiện tại.
Trong hình ảnh ví dụ đi kèm với bài viết này, số liệu bán hàng mới cho mỗi ngày được thêm vào cuối danh sách, điều này sẽ buộc tổng số liên tục chuyển xuống một ô mỗi khi dữ liệu mới được thêm vào.
Nếu hàm SUM được sử dụng riêng để tổng dữ liệu, cần phải sửa đổi phạm vi ô được sử dụng làm đối số của hàm mỗi khi dữ liệu mới được thêm vào.
Bằng cách sử dụng hàm SUM và OFFSET cùng nhau, tuy nhiên, phạm vi được tổng cộng trở thành động. Nói cách khác, nó thay đổi để chứa các ô dữ liệu mới. Việc bổ sung 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ố
Tham khảo hình ảnh đi kèm với bài viết này để làm theo hướng dẫn này.
Trong công thức này, hàm SUM được sử dụng để tính tổng phạm vi dữ liệu được cung cấp làm đối số của nó. Đ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ổng cộng theo công thức.
Hàm OFFSET được lồng trong hàm SUM và được sử dụng để tạo điểm cuối động cho phạm vi dữ liệu được tổng cộng theo công thức. Điều này được thực hiện bằng cách thiết lập điểm cuối của dải ô thành một ô trên vị trí của công thức.
Cú pháp của công thức:
= SUM (Phạm vi bắt đầu: OFFSET (Tham chiếu, Hàng, Cols))Phạm vi bắt đầu - (bắt buộc) điểm bắt đầu cho phạm vi ô sẽ được tổng bằng hàm SUM. Trong hình ảnh ví dụ, đây là ô B2.
Tham chiếu - (bắt buộc) tham chiếu ô được sử dụng để tính điểm cuối của phạm vi nằm nhiều hàng và cột. Trong hình ảnh ví dụ, đối số Tham chiếu là tham chiếu ô cho chính công thức vì chúng tôi luôn muốn phạm vi kết thúc một ô trên công thức.
Hàng - (bắt buộc) số hàng trên hoặc dưới đối số Tham chiếu được sử dụng để tính toán độ lệch. Giá trị này có thể là số dương, âm hoặc được đặt thành 0.
Nếu vị trí của offset nằm trên đối số Tham chiếu , giá trị này là âm. Nếu nó ở dưới, đối số hàng là dương. Nếu giá trị offset nằm trong cùng một hàng, đối số này bằng 0. Trong ví dụ này, bù đắp bắt đầu một hàng phía trên đối số Tham chiếu , do đó, giá trị cho đối số này là một giá trị âm (-1).
Cols - (bắt buộc) 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à số dương, âm hoặc được đặt thành 0
Nếu vị trí của offset nằm bên trái đối số Tham chiếu , giá trị này là âm. Nếu ở bên phải, đối số Cols là dương. Trong ví dụ này, dữ liệu được tổng cộng nằm trong cùng một cột với công thức để giá trị cho đối số này bằng 0.
Sử dụng công thức SUM OFFSET cho 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ổng số 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 để tạo chỗ cho tổng doanh thu 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, di chuyển công thức xuống 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 tới ô B7 và thêm ô B6 vào phạm vi được tổng kết theo công thức.
Nhập công thức SUM OFFSET
- Nhấp vào ô B6, là vị trí nơi hiển thị kết quả của công thức ban đầu.
- Nhấp vào tab Công thức của trình đơn ribbon .
- Chọn Math & Trig từ ruy-băng để mở danh sách thả xuống chức năng.
- Nhấp vào SUM trong danh sách để hiển thị hộp thoại của hàm.
- Trong hộp thoại, nhấp vào dòng Number1 .
- Nhấp vào ô 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, nhấp vào dòng Number2 .
- Nhập hàm OFFSET sau: OFFSET (B6, -1,0) để tạo điểm cuối động cho công thức.
- Nhấn OK để hoàn thành chức năng và đóng hộp thoại.
Tổng số $ 5679,15 xuất hiện trong ô B7.
Khi bạn bấm vào ô B3, hàm đầy đủ = SUM (B2: OFFSET (B6, -1,0)) xuất hiện trong thanh công thức phía trên trang tính.
Thêm dữ liệu bán hàng của ngày tiếp theo
Để thêm dữ liệu bán hàng trong ngày tiếp theo:
- Nhấp chuột phải vào tiêu đề hàng cho hàng 6 để mở menu ngữ cảnh.
- Trong menu, nhấp vào Chèn để chèn hàng mới vào trang tính.
- Kết quả là công thức SUM OFFSET di chuyển xuống ô B7 và hàng 6 hiện đang trống.
- Bấm vào ô A6 .
- Nhập số 5 để cho biết tổng số bán hàng cho ngày thứ năm đang được nhập.
- Nhấp vào ô B6.
- Nhập số $ 1458.25 và nhấn phím Enter trên bàn phím.
Cell B7 cập nhật lên tổng số mới là $ 7137.40.
Khi bạn bấm vào ô B7, công thức được cập nhật = SUM (B2: OFFSET (B7, -1,0)) xuất hiện trong thanh công thức.
Ghi chú : Hàm OFFSET có hai đối số tùy chọn: Chiều cao và Chiều rộng, được bỏ qua trong ví dụ này.
Các đối số này có thể được sử dụng để nói cho hàm OFFSET hình dạng của đầu ra theo nghĩa là nó có nhiều hàng cao và có nhiều cột rộng.
Bằng cách bỏ qua các đối số này, hàm theo mặc định, 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 là một hàng cao và một cột rộng.