Hàm SUMIF trong Excel và tính tổng theo nhiều điều kiện

Excel là một chương trình cực kỳ chức năng. Ngay cả bộ tính năng tích hợp cũng đủ để hoàn thành hầu hết mọi tác vụ. Và bên cạnh những cái tiêu chuẩn, quen thuộc với nhiều người, cũng có những cái mà ít người từng nghe đến. Nhưng đồng thời, chúng không ngừng hữu ích. Họ có chuyên môn hẹp hơn và không phải lúc nào cũng cần đến họ. Nhưng nếu bạn biết về chúng, thì vào thời điểm quan trọng, chúng có thể rất hữu ích.

Hôm nay chúng ta sẽ nói về một trong những chức năng như vậy – TÓM TẮT.

Nếu người dùng phải đối mặt với nhiệm vụ tổng hợp một số giá trị, tập trung vào các tiêu chí nhất định thì cần phải sử dụng hàm TÓM TẮT. Công thức sử dụng hàm này lấy các điều kiện này làm đối số, sau đó tính tổng các giá trị đáp ứng chúng, sau đó giá trị tìm thấy được nhập vào ô nơi nó được viết. 

Mô tả chi tiết hàm SUMIFS

Trước khi xem xét chức năng TÓM TẮT, trước tiên bạn phải hiểu phiên bản đơn giản hơn của nó là gì – TỔNG HỢP, vì hàm mà chúng ta đang xem xét dựa trên đó. Mỗi người trong chúng ta có lẽ đã quen thuộc với hai chức năng thường được sử dụng – TÓM TẮT (thực hiện tính tổng các giá trị) và NẾU NHƯ (kiểm tra một giá trị theo một điều kiện cụ thể).

Nếu bạn kết hợp chúng, bạn sẽ có được một chức năng khác – TỔNG HỢP, kiểm tra dữ liệu theo tiêu chí do người dùng chỉ định và chỉ tính tổng những số đáp ứng các tiêu chí đó. Nếu chúng ta nói về phiên bản tiếng Anh của Excel thì hàm này được gọi là SUMIF. Nói một cách đơn giản, tên ngôn ngữ là bản dịch trực tiếp của tên tiếng Anh. Chức năng này có thể được sử dụng cho nhiều mục đích khác nhau. Đặc biệt, nó có thể được sử dụng thay thế VPR, tức là viết ra

Sự khác biệt chính giữa chức năng TÓM TẮT  từ chức năng thông thường TỔNG HỢP là một số tiêu chí được sử dụng. Cú pháp của nó thoạt nhìn khá phức tạp, nhưng khi xem xét kỹ hơn, hóa ra logic của hàm này rất đơn giản. Trước tiên, bạn cần chọn phạm vi nơi dữ liệu sẽ được kiểm tra, sau đó đặt các điều kiện tuân thủ để thực hiện phân tích. Và một thao tác như vậy có thể được thực hiện với một số lượng điều kiện khá lớn.

Cú pháp chính nó là:

SUMIFS(phạm vi tổng, phạm vi điều kiện1, điều kiện1, [dải_điều kiện2, điều kiện2], …)

Ở những nơi thích hợp cần đặt các mảng ô phù hợp với từng trường hợp cụ thể. 

Hãy xem xét các đối số chi tiết hơn:

1. Tổng_phạm vi. Đối số này, cũng như phạm vi của điều kiện 1 và điều kiện 1, là bắt buộc. Đó là một tập hợp các ô cần được tính tổng.
2. Điều kiện_range1. Đây là phạm vi mà điều kiện sẽ được kiểm tra. Nó được ghép nối với đối số tiếp theo – Condition1. Việc tính tổng các giá trị tương ứng với tiêu chí được thực hiện trong các ô được chỉ định trong đối số trước đó.
3. Điều kiện1. Đối số này chỉ định các tiêu chí cần được kiểm tra. Ví dụ, nó có thể được đặt theo cách này: “> 32”.
4. Phạm vi điều kiện 2, Điều kiện 2… Ở đây, các điều kiện sau được đặt theo cách tương tự. Nếu cần chỉ định nhiều điều kiện thì các đối số Phạm vi Điều kiện 3 và Điều kiện 3 sẽ được thêm vào. Cú pháp tương tự cho các đối số sau.

Chức năng này cho phép xử lý tối đa tới 127 cặp điều kiện và phạm vi. 

Bạn có thể sử dụng nó trong nhiều lĩnh vực cùng một lúc (chúng tôi sẽ chỉ cung cấp một số lĩnh vực, danh sách thực sự còn dài hơn):

1. Kế toán. Ví dụ, thật tốt khi sử dụng chức năng TÓM TẮT để tạo báo cáo tóm tắt, theo quý về chi tiêu trên một số tiền nhất định chẳng hạn. Hoặc tạo báo cáo về một sản phẩm từ một loại giá nhất định.
2. Quản lý bán hàng. Ở đây chức năng này cũng có thể rất hữu ích. Ví dụ: chúng ta phải đối mặt với nhiệm vụ chỉ tổng hợp giá vốn hàng hóa đã bán cho một khách hàng nhất định tại một thời điểm nhất định. Và trong tình huống như vậy, hàm TÓM TẮT có thể rất hữu ích
3. Giáo dục. Hôm nay chúng tôi sẽ đưa ra nhiều ví dụ thực tế hơn từ lĩnh vực này. Đặc biệt, bạn có thể sử dụng nó để có được bản tóm tắt điểm của học sinh. Bạn có thể chọn cho một môn học hoặc cho từng lớp riêng lẻ. Một người có thể ngay lập tức đặt ra một số tiêu chí để chọn đánh giá, điều này rất thuận tiện và có thể tiết kiệm rất nhiều thời gian.

Như bạn có thể thấy, phạm vi ứng dụng của chức năng này rất rộng. Nhưng đây không phải là công đức duy nhất của nó. Chúng ta hãy xem thêm một vài lợi ích mà tính năng này mang lại:

1. Khả năng thiết lập nhiều tiêu chí. Tại sao đây là một lợi thế? Bạn có thể sử dụng chức năng bình thường TỔNG HỢP! Và tất cả chỉ vì nó thuận tiện. Không cần phải thực hiện các tính toán riêng biệt cho từng tiêu chí. Tất cả các hành động có thể được lập trình trước, thậm chí trước đó. bảng dữ liệu sẽ được hình thành như thế nào. Đây là một tiết kiệm thời gian tuyệt vời.
2. Tự động hóa. Thời đại hiện đại là thời đại của tự động hóa. Chỉ người biết cách tự động hóa công việc của mình một cách hợp lý mới có thể kiếm được nhiều tiền. Chính vì vậy mà khả năng làm chủ Excel và các hàm TÓM TẮT đặc biệt, nó rất quan trọng đối với bất kỳ ai muốn xây dựng sự nghiệp. Biết một chức năng cho phép bạn thực hiện nhiều hành động cùng một lúc. Và ở đây chúng ta chuyển sang lợi ích tiếp theo của tính năng này.
3. Tiết kiệm thời gian. Chỉ vì một chức năng thực hiện nhiều nhiệm vụ cùng một lúc.
4. Sự đơn giản. Mặc dù thoạt nhìn cú pháp khá nặng do tính cồng kềnh nhưng trên thực tế, logic của hàm này rất đơn giản. Đầu tiên, một phạm vi dữ liệu được chọn, sau đó là một phạm vi giá trị sẽ được kiểm tra xem có tuân thủ một điều kiện nhất định hay không. Và tất nhiên, bản thân điều kiện cũng phải được chỉ định. Và như vậy nhiều lần. Trên thực tế, hàm này chỉ dựa trên một cấu trúc logic, điều này làm cho nó đơn giản hơn hàm nổi tiếng. VPR mặc dù thực tế là nó có thể được sử dụng cho các mục đích tương tự, cũng có tính đến số lượng tiêu chí lớn hơn. 

Đặc điểm của việc sử dụng hàm SUMIFS

Có một số tính năng khi sử dụng chức năng này mà bạn cần chú ý. Trước hết, hàm này bỏ qua các phạm vi có chuỗi văn bản hoặc giá trị rỗng, vì các kiểu dữ liệu này không thể được cộng lại với nhau theo mẫu số học mà chỉ được nối với nhau như chuỗi. Chức năng này không thể làm điều này. Bạn cũng cần chú ý đến các điều kiện sau:

1. Bạn có thể sử dụng các loại giá trị này làm điều kiện để chọn ô nhằm thêm các giá trị có trong chúng: giá trị số, biểu thức boolean, tham chiếu ô, v.v. 
2. Nếu văn bản, biểu thức logic hoặc dấu hiệu toán học đang được kiểm tra thì các tiêu chí đó sẽ được chỉ định thông qua dấu ngoặc kép.
3. Không thể sử dụng thuật ngữ dài hơn 255 ký tự.
4. Có thể sử dụng tiêu chí gần đúng để chọn giá trị bằng ký tự đại diện. Dấu chấm hỏi dùng để thay thế một ký tự đơn và dấu nhân (dấu hoa thị) dùng để thay thế nhiều ký tự. 
5. Các giá trị Boolean nằm trong phạm vi tổng sẽ tự động được chuyển đổi thành giá trị số theo loại của chúng. Do đó, giá trị “TRUE” biến thành 1 và “FALSE” – thành 0. 
6. Nếu #VALUE! xuất hiện lỗi trong một ô, điều đó có nghĩa là số lượng ô trong phạm vi điều kiện và tổng là khác nhau. Bạn cần đảm bảo rằng kích thước của các đối số này là như nhau. 

Ví dụ về cách sử dụng hàm SUMIFS

Chức năng TÓM TẮT Hóa ra nó không phức tạp như thoạt nhìn. Nhưng để rõ ràng hơn, hãy xem một số ví dụ thực tế về cách bạn có thể sử dụng hàm TÓM TẮT. Điều này sẽ làm cho việc đi sâu vào chủ đề dễ dàng hơn nhiều.

Phạm vi động tổng hợp điều kiện

Vì vậy, hãy bắt đầu với ví dụ đầu tiên. Giả sử chúng ta có một bảng chứa thông tin về cách học sinh tiếp thu chương trình giảng dạy trong một môn học cụ thể. Có một bộ thang điểm, thành tích được đánh giá theo thang điểm 10. Nhiệm vụ là tìm điểm cho kỳ thi của những học sinh có họ bắt đầu bằng chữ A và điểm tối thiểu của họ là 5.

Cái bàn trông như thế này.

Để tính tổng điểm dựa trên các tiêu chí được mô tả ở trên, chúng ta cần áp dụng công thức sau.

Hãy mô tả các đối số chi tiết hơn:

1. C3:C14 là phạm vi tổng của chúng tôi. Trong trường hợp của chúng tôi, nó trùng với phạm vi điều kiện. Từ đó sẽ có những điểm được chọn dùng để tính số tiền, nhưng chỉ những điểm nằm trong tiêu chí của chúng tôi.
2. “>5” là điều kiện đầu tiên của chúng tôi.
3. B3:B14 là phạm vi tổng thứ hai được xử lý để khớp với tiêu chí thứ hai. Chúng tôi thấy rằng không có sự trùng hợp ngẫu nhiên với phạm vi tổng hợp. Từ đó chúng ta kết luận rằng phạm vi tính tổng và phạm vi của điều kiện có thể giống nhau hoặc không. 
4. “A*” là phạm vi thứ hai, chỉ định việc lựa chọn điểm cho những học sinh có họ bắt đầu bằng A. Trong trường hợp của chúng tôi, dấu hoa thị có nghĩa là bất kỳ số lượng ký tự nào. 

Sau khi tính toán ta được bảng sau.

Như bạn có thể thấy, công thức tổng hợp các giá trị dựa trên phạm vi động và dựa trên các điều kiện do người dùng chỉ định.

Tính tổng có chọn lọc theo điều kiện trong Excel

Bây giờ, giả sử chúng ta muốn lấy thông tin về hàng hóa nào đã được vận chuyển đến quốc gia nào trong quý vừa qua. Sau đó, tìm tổng doanh thu từ các lô hàng trong tháng 7 và tháng 8.

Bảng chính nó trông như thế này. 

Để xác định kết quả cuối cùng, chúng ta cần một công thức như vậy.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Theo kết quả tính toán được thực hiện theo công thức này, chúng ta nhận được kết quả sau.

Chú ý! Công thức này trông khá lớn mặc dù chúng ta chỉ sử dụng hai tiêu chí. Nếu phạm vi dữ liệu giống nhau thì bạn có thể giảm đáng kể độ dài của công thức, như minh họa bên dưới.

Hàm SUMIFS tính tổng giá trị theo nhiều điều kiện

Bây giờ hãy đưa ra một ví dụ khác để minh họa. Trong trường hợp này, bảng vẫn giữ nguyên như trường hợp trước. 

Chúng tôi sử dụng công thức sau (nhưng chúng tôi viết nó dưới dạng công thức mảng, nghĩa là chúng tôi nhập nó thông qua tổ hợp phím CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Sau chức năng TÓM TẮT sẽ tính tổng mảng giá trị dựa trên tiêu chí quy định trong công thức (tức là các quốc gia Trung Quốc và Georgia), mảng kết quả được tính tổng theo hàm thông thường TỔNG, được viết dưới dạng công thức mảng.

Nếu các điều kiện được truyền dưới dạng hằng số mảng cho nhiều hơn một cặp thì công thức sẽ cho kết quả không chính xác.

Bây giờ chúng ta hãy nhìn vào bảng chứa tổng số.

Như bạn thấy, chúng tôi đã thành công. Chắc chắn bạn cũng sẽ thành công. Thành công lớn trong lĩnh vực này. Đây là một chức năng rất đơn giản mà một người mới đặt chân vào con đường học Excel đều có thể hiểu được. Và chúng ta đã biết rằng hàm TÓM TẮT cho phép bạn hoạt động hiệu quả trong bất kỳ lĩnh vực hoạt động nào, từ kế toán đến giáo dục. Ngay cả khi bạn đang xây dựng sự nghiệp ở bất kỳ lĩnh vực nào khác chưa được mô tả ở trên, tính năng này vẫn sẽ giúp bạn kiếm tiền. Đây là lý do tại sao cô ấy có giá trị.

Quan trọng nhất, nó cho phép bạn tiết kiệm thời gian, thật không may, nguồn lực lại có hạn. Có vẻ như có một vài giây để áp dụng hai chức năng, nhưng khi bạn phải thực hiện một số lượng lớn các thao tác lặp đi lặp lại, thì những giây này sẽ cộng lại thành hàng giờ có thể dành cho việc khác. Vì vậy chúng tôi khuyên bạn nên thực hành sử dụng tính năng này. Hơn nữa, nó cực kỳ đơn giản.