Chắc hắn các bạn đã gặp phải ngôi trường hợp rất cần phải tính tổng theo 1 hay nhiều điều kiện trong Excel. Để làm được điều này họ sẽ sử dụng hàm SUMIF để tính tổng theo 1 điều kiện và SUMIFS để tính tổng theo khá nhiều điều kiện.
Bạn đang xem: Tính tổng theo điều kiện trong excel
Hàm SUMIF – cú pháp và cách sử dụng:
Với hàm SUMIF, các các bạn sẽ tính được tổng dựa vào 1 đk duy nhất.Cú pháp như sau:
=SUMIF(, , )
Chúng ta rất có thể nghĩ ra gần như trường phù hợp sẽ sử dụng đến bí quyết này như sau:
Tính tổng lợi nhuận trong tháng 1 cho một công tyTính tổng lợi nhuận của một nhân viên ATính tổng doanh thu của năm năm nhâm thìn trở về trước
Tính tổng <đại lượng phải tính tổng> nếu như <điều kiện>
Trong lấy ví dụ trên, nhằm tính tổng lợi nhuận của năm 2016 thì họ sẽ thực hiện công thức SUMIF như sau
=SUMIF(A2:A6,2016,B2:B6)
Ở đây, để thuận lợi nhất cho việc sử dụng SUMIF cũng như chuẩn bị cho hầu hết ví dụ phức tạp hơn, bạn cũng có thể viết số năm 2016 vào 1 ô bên trên Excel, ví dụ như ô C1 chẳng hạn. Vì thế khi bọn chúng ta chuyển đổi ô đk C1 thì công dụng của hàm SUMIF cũng sẽ thay đổi theo một cách dễ dàng và họ không buộc phải sửa lại công thức.
=SUMIF(A2:A6,C1,B2:B6)
Hàm SUMIFS – cú pháp và phương pháp sử dụng:
Hàm SUMIFS đã giúp các bạn tính tổng dựa trên nhiều hơn nữa một điều kiện. Tất yếu các chúng ta có thể dùng hàm này để đo lường và tính toán nếu các bạn chỉ có 1 điều kiện, như vậy bạn sẽ phải viết nhiều hơn 1 chữ s. Cú pháp như sau
=SUMIFS(, , , , , …)
Để hiểu được bí quyết này, bọn họ sẽ cùng mọi người trong nhà nhìn vào ví dụ như minh hoạ sau đây.
Trong lấy ví dụ này, bạn cần tính xem nhân viên A vào năm 2015 có doanh số bán hàng là bao nhiêu
chính là cột B, cụ thể hơn là vùng B2:B6 là năm, được lưu giữ tại A2:A6 chính là 2015, họ sẽ lưu ở ô E1 là nhân viên, được lưu tại C2:C6 là nhân viên cấp dưới A, được giữ tại ô F1Công thức của bọn họ để giải quyết bài toán này là
=SUMIFS(B2:B6,A2:A6,E1,C2:C6,F1)Việc lưu đk ra ngoài, ko viết vào trực tiếp vào trong bí quyết sẽ giúp chúng ta có 1 cách làm linh hoạt hơn, khi biến đổi điều khiếu nại cũng dễ dàng hơn và né tránh được lỗi khi thao tác làm việc với những bí quyết phức tạp.
Nếu bảng dữ liệu của những bạn bên dưới không có dữ liệu nào nữa, bạn có thể viết công thức SUMIFS như sau, vẫn gọn hơn vô cùng nhièu:
=SUMIF(B:B,A:A,E1,C:C,F1)
Đến đây, gồm thể chúng ta đã năm được cách áp dụng 2 hàm SUMIF và SUMIFS một cách đơn giản dễ dàng rồi. Hầu như ví dụ minh hoạ và các trường hợp nâng cao của 2 hàm này, lấy ví dụ như như
Tính tổng theo điều kiẹn sử dụng những kí tự đối chiếu lớn hơn, nhỏ dại hơn, lớn hơn hoặc bằng, nhỏ tuổi hơn hoặc bằng: >, =, Tính tổng theo điều kiện, sử dụng những kí tự dấu hỏi chấm, lốt sao: ?, *
mời chúng ta theo dõi 2 videos sau đây và nhớ là download tài liệu kèm theo mặt dưới.
Video nâng cao về 2 hàm SUMIF với SUMIFS đang giúp các bạn sử dụng các ký tự dấu sao và hỏi chấm để tính tổng nâng cao:
Download tài liệu liên quan:
Download tài liệu Excel kèm theo đoạn clip #1
Download tư liệu Excel kèm theo video clip #2
———
Ngoài hàm Sum
If, Sum
If
S giúp tính tổng theo khá nhiều điều kiện, họ còn hoàn toàn có thể sử dụng những hàm khác ví như SUMPRODUCT để gia công việc này. Trong thực tế thì yêu thương cầu đo lường và tính toán và điểm lưu ý dữ liệu sẽ ra quyết định tới việc chúng ta sẽ bắt buộc dùng hàm nào, dùng như thế nào. Vì vậy bạn cần bổ sung thêm cho doanh nghiệp càng nhiều kiến thức về hàm, về tổ chức triển khai dữ liệu thì càng dễ dãi khi làm cho việc.
Tiếp đó là bài toán cần phải kết hợp các phương pháp của Excel nhằm tăng hiệu quả các bước như: định dạng dữ liệu, định hình theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…
Toàn bộ những kiến thức này chúng ta đều hoàn toàn có thể học được trong khóa học Excel tự cơ bản đến chuyên viên dành cho người đi làm
Khi chúng ta nghe tên hàm SUMPRODUCT lần thứ nhất tiên, nó gồm vẻ y như một phương pháp vô dụng tiến hành một tổng số thường thì của hoạt động sản phẩm. Tuy nhiên với cách phía dẫn tiếp sau đây thì thực sự hàm SUMPRODUCT không còn vô dụng 1 chút nào nhé.
1. Hàm SUMPRODUCT trong Excel
Hàm SUMPRODUCT vào học Excellà hàm tính tích các phạm vi hoặc mảng với nhau và trả về tổng số sản phẩm. Điều này nghe có vẻ như khá nhàm chán, nhưng mà hàm nàycó những công dụng cực kỳ linh hoạt có thể được sử dụng để đếm và tính tổng hoạt bát hơn. Vào thực tế, hàm SUMPRODUCT được dùng không hề ít và rất là hữu ích.
Sơ lược về hàm SUMPRODUCT
2. Bí quyết tính của hàm SUMPRODUCT
Về mặt kỹ thuật, hàm này trong Excel sẽ nhân những số trong những mảng đã chỉ định và hướng dẫn và trả về tổng của các thành phầm đó.
Cú pháp của hàm SUMPRODUCT vào Excel rất dễ dàng và dễ dàng hiểu:
= SUMPRODUCT (array1,
Trong đó:- array1: là mảng thứ nhất bắt cần có nếu muốn nhân những thành phần của chính nó rồi cùng tổng lại.
-
- con số mảng về tối thiểu là 1. Trong trường phù hợp này, bí quyết SUMPRODUCT chỉ đơn giản dễ dàng là cộng toàn bộ các phần tử mảng cùng trả về tổng.
- số lượng mảng buổi tối đa là 255 vào Excel 2016, Excel 2013, Excel 2010 với Excel 2007 với 30 trong những phiên bạn dạng Excel trước đó.
- mặc dù SUMPRODUCT chuyển động với những mảng, nhưng mà nó không yêu cầu thực hiện phím tắt mảng ( Ctrl + Shift + Enter).. Bạn cạnh tranh một bí quyết SUMPRODUCT theo phong cách thông thường bằng cách nhấn phím Enter.
Lưu ý:
+ toàn bộ các mảng trong cách làm SUMPRODUCT phải bao gồm cùng con số hàng cùng cột, nếu không, các bạn sẽ nhận được #VALUE! lỗi.
+ Nếu ngẫu nhiên đối số mảng làm sao chứa những giá trị chưa phải là số, bọn chúng sẽ được coi là số không.
+ trường hợp một mảng là một bài kiểm tra logic, nó vẫn dẫn đến những giá trị TRUE với FALSE. Trong số đông các ngôi trường hợp, các bạn cần chuyển đổi chúng thành 1 cùng 0 bằng phương pháp sử dụng toán tử solo nguyên kép (-).
+ Tổng thích hợp không hỗ trợ các ký kết tự đại diện.
3. Ví dụ cách áp dụng hàm SUMPRODUCT
Giả sử chúng ta có số lượng trong các ô A2: A4, giá trong các ô B2: B4 và bạn muốn tìm phát âm tổng số. Nếu khách hàng đang làm bài xích kiểm tra toán làm việc trường, các bạn sẽ tính tích con số với giá cho từng mục, tiếp nối cộng những tổng phụ. Vào Microsoft Excel, chúng ta có thể nhận kết quả bằng một bí quyết SUMPRODUCT duy nhất:=SUMPRODUCT(A2:A4,B2:B4)
3. Cách sử dụng hàm SUMPRODUCT
Hàm sumproduct tính tổng nhiều đk sẽ được áp dụng qua số đông ví dụ cụ thể dưới đây:
+ Trường vừa lòng 1: cách dùnghàm SUMPRODUCT vào Excel với một tiêu chuẩn duy nhất
Ví dụ: Tập dữ liệu dưới đây chứa 6 tên trong những hàng cùng 12 tháng trong những cột. Hãy tưởng tượng sếp của bạn muốn bạn cộng tất cả các số đến tháng Sáu. Ban đầu công thức của bạn bằng cách chọn phạm vi dữ liệu (không có tên và tháng) với nhân nó cùng với mảng chứa các tháng bởi 6.
Các bước thực hiệnhàm tính tổng có đk trong Excel.
Bước 1: Nhập công thức: = SUMPRODUCT(array1,
Trong đó:
Mảng 1 của phương pháp hướng dẫn Excel cộng toàn bộ các số trong sàng lọc C9: N14. Array2 kiểm tra từng số tháng xem nó có bằng 6. Hiệu quả của việc này là TRUE (1) hoặc FALSE (0). Bạn có thể thấy hiệu quả của hàng này trong mặt hàng 16. Chỉ cách cột H công dụng là TRUE, vì vậy kết quả là 1. Để nhân Array1 với Array2 có nghĩa là nhân những số thực với 0 hoặc 1. Trong lấy ví dụ như này, công thức nhân tất cả các số trong cột C bằng 0 và toàn bộ các số tự cột H bằng 1. Tất cả các quý hiếm được nhân với 0 cộng với không. Các số duy nhất sót lại được nhân cùng với 1, trong trường vừa lòng này là tháng 6. Việc thực hiện hàmsumproduct có đk giúp bạn gấp rút thực hiện nay phép tính và mang lại ra tác dụng nhanh và đúng chuẩn nhất.
+ Trường phù hợp 2: sử dụng hàm SUMPRODUCT làm tiêu chí cho những cột
- Hãy liên tục ví dụ trên bằng phương pháp thêm các tiêu chí khác. Sếp của bạn hiện thời muốn biết doanh số bán sản phẩm trong 4 tháng giữa năm là bao nhiêu, vị vậy từ tháng thứ 4 trở lên trên và bao gồm cả tháng 6. Bao gồm nghĩa là shop chúng tôi có một phạm vi dữ liệu to hơn để thêm vào. Làm chũm nào để bạn xử lý nó?
Mô tả:
Công thức áp dụng là:
=SUMPRODUCT(( C9:N14) * (C8:N*>=4) * (C8:N8 = 4) và nhỏ dại hơn hoặc bởi 6 (Trường vừa lòng 2: thực hiện hàm đểlàm tiêu chí cho những cột
Để đã đạt được điều đó, hãy thêm tháng số lượng giới hạn thấp rộng trong ô J4 và giới hạn cao hơn trong ô J5. Hiện thời thay đổi phương pháp để phản chiếu điều này. Nỗ lực vì tìm hiểu thêm số 4, hãy viết ô J4. Thế số 6 bởi ô J5.
Bạn sẽ được công thức ở đầu cuối sẽ là:
=SUMPRODUCT ((C9:N14) * (C8:N8>= J14) * (C8:N8Trường hòa hợp 3: áp dụng hàm đểlàm tiêu chú mang lại cột và hàng
Như vậy, để tính một tiêu chí bổ sung cập nhật trong những hàng, hãy thêm một mảng không giống vào công thức. Chỉ định rằng những tên phải bằng “Lisa” . Như bọn họ vừa tìm hiểu, chúng ta có thể muốn tham khảo một ô tham số thay vì mã hóa nó. Vì chưng vậy, viết Lisa trong ô M4. Hiện thời thêm một mảng chọn những ô B9: B14 bởi với ô tham số của bạn, trong trường đúng theo này là M4. Cột P cho biết thêm hàng nào đáp ứng điều khiếu nại này, là mặt hàng 11.
Xem thêm: Làm Thế Nào Để So Sánh Thời Gian Trong Excel Nhanh, Đơn Giản Nhất
Công thức thực hiện là:
=SUMPRODUCT ((C9:N14) * (C8:N8 >=J4) * (C8:Nchứng chỉ IC3 khiến cho bạn trong các bước cũng như thăng tiến vào sự nghiệp, hãy đọc nhé. Mày mò ngay cuốn sách Excel trường đoản cú cơ bạn dạng đến nâng cao khiến cho bạn nắm chắc kỹ năng và kiến thức Excel.