Excel: Tính toán chính xác các khoản tiền có điều kiện
Trong Excel, bạn có thể tính toán các khoản tiền không tính đến tất cả các yếu tố của khu vực đã chọn - cái gọi là tổng có điều kiện. Chúng tôi sẽ chỉ cho bạn cách thực hiện phép tính hợp lý nhất.
Ba hàm khác nhau cho các khoản tiền có điều kiện trong Excel
Excel cung cấp các tùy chọn và hàm khác nhau cho tổng số có điều kiện. Sự lựa chọn đúng phụ thuộc vào loại điều kiện:
- Nếu một điều kiện cố định áp dụng cho từng giá trị được đề cập, hãy sử dụng hàm SUMIF.
- Nếu một số điều kiện phải được tính đến, đó cũng là hàm SUMIFS kể từ Excel 2007.
- Tuy nhiên, với các điều kiện khác nhau, bạn cần một công thức ma trận hoặc hàm SUM SẢN PHẨM.
- Trong các đoạn sau, chúng tôi giải thích ba chức năng bằng cách sử dụng các ví dụ.
SUMIF: Tổng tiền có điều kiện với các điều kiện cố định
- Giả sử rằng dữ liệu nằm trong các ô từ A1 đến A10 và các giá trị khác nhau trong các ô từ B1 đến C10 được sử dụng cho các điều kiện.
- Nhiệm vụ đầu tiên với một điều kiện cố định sau đó có nghĩa là, ví dụ, tổng hợp tất cả các giá trị trong cột A mà giá trị tương ứng trong cột B xấp xỉ lớn hơn 10.
- Để thực hiện việc này, sử dụng công thức »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- Đối số đầu tiên »B1: B10« xác định khu vực cần đánh giá, theo sau là điều kiện »"> 10 "«. Lưu ý rằng các điều kiện với các ký hiệu hoặc toán tử phải nằm trong dấu ngoặc kép.
- Cuối cùng, »A1: A10« xác định khu vực cần tóm tắt.
- Phạm vi cho các điều kiện và ngày cũng có thể giống hệt nhau, do đó công thức »= SUMIF (A1: A10;"> 10 ")« là đủ.
SUM IF: Số tiền có điều kiện với nhiều điều kiện
Kể từ Excel 2007, hàm SUMIFS mở rộng nguyên tắc này thành nhiều đến tối đa 127 điều kiện:
- Lưu ý thứ tự khác của các đối số. Khu vực được tóm tắt là ở đây đầu tiên. Tiếp theo là một khu vực tiêu chí và điều kiện liên quan, theo đó các khu vực phải luôn được chỉ định riêng.
- Ví dụ: công thức "= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")« tổng hợp tất cả các giá trị trong cột A có giá trị tương ứng trong cột B lớn hơn 10 và các giá trị tương ứng trong cột C nhỏ hơn 100.
SẢN PHẨM SUM: Tổng có điều kiện với các điều kiện thay đổi
Các tiêu chí biến như "nếu giá trị trong cột B lớn hơn giá trị trong cột C" yêu cầu một công thức ma trận để đánh giá:
- Các giá trị được tính đến được chọn bằng cách nhân với một giá trị logic.
- Để thực hiện việc này, nhập khoảng "= SUM (A1: A10 * (B1: B10> C1: C10))" và luôn đóng mục nhập, ngay cả sau khi thay đổi bằng tổ hợp phím [Ctrl] + [Shift] + [Enter], Vì vậy, công thức là trong niềng răng.
- Trong trường hợp này, bạn có thể đạt được kết quả tương tự với chức năng "= SUM SẢN PHẨM (A1: A10; 1 * (B1: B10> C1: C10))".
- Tuy nhiên, bạn phải lưu ý rằng trước tiên bạn phải nhân đối số thứ hai với 1 để chuyển đổi giá trị logic của phép so sánh thành một số.
Lưu ý: Trong thực tế tất cả các trường hợp, bạn có thể tạo tổng số rõ ràng bằng một cột phụ. Điều này cũng được khuyến khích trong quá trình phát triển một phép tính để kiểm tra kết quả của các công thức phức tạp. Mẹo: Chúng tôi sẽ giải thích cách thêm nhiều ô trong Excel tại đây.