Cách tính tổng các số từ nhiều bảng. Hàm Sumif, cũng như sumif theo hai tiêu chí

Cách tính vàoExcel chọn lọc tế bào , không phải mọi thứ liên tiếp mà theo điều kiện? Nếu bạn nhập công thức theo cách thủ công vào cái bàn lớn, thì đây là một thời gian rất dài. Anh ấy sẽ giúp chúng tôi hàm Excel"SUMMESLI."
Chúng tôi có một bảng như vậy.
Chúng tôi thêm các ô có số tiền vào dòng "kế hoạch" - đây là dòng đầu tiên, thứ tư và thứ tám.
Tại ô F1 chúng ta viết công thức. Trên tab “Trang chủ”, trong phần “công thức” - “Toán học”, hãy chọn chức năng “SUMIF”.Trong hộp thoại xuất hiện, ở dòng “Phạm vi”, hãy chỉ ra phạm vi (cột) trong đó từ “kế hoạch” được viết. Đối với chúng tôi, đây là cột B.
Trong dòng “Tiêu chí”, chúng tôi viết những gì chúng tôi đang tìm kiếm. Chúng tôi viết từ “kế hoạch”, p.ch. Chúng tôi tính toán số tiền kế hoạch. Nếu chúng ta đếm số tiền “đã phát hành”, chúng ta sẽ viết tiêu chí – “đã phát hành”.
Trong phần "Tiêu chí", bạn có thể viết một số ký hiệu.
* (dấu hoa thị) có nghĩa là số lượng ký tự bất kỳ trong một từ. Ví dụ: "mor*" - sẽ tìm kiếm các từ bắt đầu bằng "mor" và bất kỳ phần kết thúc nào (mor, sea, Sailor, v.v.). Để tìm tất cả các họ bắt đầu bằng chữ “P” và kết thúc bằng chữ “v”, bạn cần viết tiêu chí như thế này - “R*v” (hoặc “R*V”). Tìm tên - Rublev, Ryleev, v.v.
? (dấu hỏi) chỉ một ký tự. Ví dụ: tiêu chí “chair” sẽ tìm kiếm ghế, bàn, v.v. Bạn có thể viết một số dấu hỏi. Ví dụ: "Ivan?????" (Ivanov, Ivanchenko, v.v.)
Để biết các ký hiệu khác có thể chèn vào công thức, hãy xem bài viết “Các ký hiệu trong công thức Excel”.
Trong dòng “Sum_range”, chúng tôi chỉ ra phạm vi (cột) chứa các số mà chúng tôi cần thêm. Đối với chúng tôi, đây là cột C. Hộp thoại được điền như thế này. Nhấp vào "OK".


Kết quả là công thức sau.=SUMIF(B2:B10,"kế hoạch",C2:C10)
Công thức đã được tính toán.


Trong ô F2 chúng ta viết công thức sau.=SUMIF(B2:B10,"đã phát hành",C2:C10)
Để biết thêm ví dụ về tính toán có điều kiện, hãy xem bài viết “Hàm SUMIF trong Excel”.
Bây giờ chúng ta cần tính tỷ lệ phần trăm trong Excel.
Trong ô F3, chúng ta sẽ viết một công thức tính toán phần trăm trung bình thực hiện kế hoạch của công ty.Đây là lúc hàm AVERAGEIF của Excel phát huy tác dụng.
Đặt định dạng ô thành “phần trăm”.Trên tab “Công thức”, chọn “Hàm khác” - “Thống kê” và chọn hàm “AVERAGEIF”.Chúng ta điền vào hộp thoại như trong công thức trước, chỉ có điều chúng ta viết “tiêu chí” là “%” - đây là cách nó được viết trong bảng của chúng ta. Kết quả là công thức sau.
=AVERAGEIF(B2:B10,"%";C2:C10)
Bảng đã tính toán điều này.


Cách tính chênh lệch, chiết khấu bằng cách nhân cột với một số, xem bài viết "

Hãy xem xét cách tính tổng trong Excel theo điều kiện . Bạn có thể thêm vào Excel không chỉ theo cột và hàng mà còn có thể chọn dữ liệu từ bảng để thêm theo nhiều điều kiện khác nhau.
Để biết cách đặt điều kiện đơn giản để đếm dữ liệu, hãy xem bài viết “Hàm IF trong Excel”.
Ở đây chúng ta sẽ xem xét hàm "SUMIFS" trongExcel , tính tổng các số thỏa mãn nhiều điều kiện, chọn dữ liệu cụ thể để cộng theo nhiều điều kiện. Bạn có thể chỉ định tối đa 127 điều kiện.
Ngoài ra còn có hàm “SUMMIF”, nhưng bạn có thể chỉ định một điều kiện trong đó. Xem bài viết “Hàm SUMIF trong Excel”.
Vì vậy, chúng tôi có bảng này.
Chúng ta cần tính số lượng táo đã bán mà người quản lý Vasechkin đã bán cho người mua Ivanov.
Đặt công thức trong ô F5.
Trên tab “Công thức”, trong phần “Thư viện hàm”, chọn “Toán học”. Trong danh sách xuất hiện, nhấp vào chức năng “SUMIFS”. Một cửa sổ như thế này sẽ xuất hiện.

Cửa sổ này chứa các hàng cho một phạm vi và một điều kiện. Tuy nhiên, bạn có thể viết tới 127 điều kiện.
Vì vậy, hãy điền vào hộp thoại như thế này.
Trong dòng “Phạm vi tổng hợp”, chúng tôi viết phạm vi D2-D8 - đây là cột có số tiền mua.
Trong dòng “Phạm vi điều kiện1”, chúng tôi viết phạm vi của cột có người quản lý. Chúng tôi có B2-B8.
ghi chú.
Khi chúng ta đặt con trỏ vào dòng này, nó sẽ tự động xuất hiện dòng mới cho các điều kiện. Nếu có nhiều điều kiện thì một thanh cuộn sẽ xuất hiện, nhờ đó chúng ta sẽ chuyển sang cửa sổ điều kiện tiếp theo.
Trong dòng “Điều kiện1”, chúng tôi viết họ của người quản lý. Chúng tôi đã viết - Vasechkin.
Trong dòng “Phạm vi điều kiện 2”, chúng tôi viết phạm vi của cột khách hàng – A2:A8.
Ở dòng “Điều kiện 2”, họ viết họ của người mua – Ivanov.
Di chuyển trường điều kiện bằng thanh cuộn và viết điều kiện tiếp theo.
Trong dòng “Phạm vi điều kiện3”, chúng tôi viết phạm vi C2:C8.
Trong dòng “Điều kiện3”, chúng tôi viết “quả táo”.

Nhấp vào "OK". Đây là kết quả.


Công thức ở ô A5 như sau.
=SUMIFS(D2:D8;B2:B8;"Vasechkin";A2:A8;"Ivanov";C2:C8;"táo")
Nếu bạn cần tính số tiền mà người quản lý Vasechkin đã bán cho Ivanov tất cả các loại trái cây khác nhau, thì chúng ta sẽ viết công thức sau với hai điều kiện.
=SUMIFS(D2:D8,B2:B8,”Vasechkin”,A2:A8,”Ivanov”)
Nếu bạn cần tính số lượng táo mà người quản lý Vasechkin và Sergeeva đã bán cho Ivanov, thì chúng ta sẽ viết công thức như thế này.
=SUMIFS(D2:D8;B2:B8;"Vasechkin";A2:A8;"Ivanov";C2:C8;"táo")+
SUMIFS(D2:D8;B2:B8;"Sergeeva";A2:A8;"Ivanov";C2:C8;"táo")
Chúng tôi viết công thức này như thế này. Đầu tiên, chúng tôi chèn hàm “SUMIFS” theo Vasechkin. Nhấp vào "OK". Trong thanh công thức ở cuối công thức, chúng ta đặt dấu “+” và chèn hàm “SUMIFS” theo Sergeeva. Hóa ra chúng tôi đã kết hợp các chức năng riêng biệt cho từng người quản lý.
Chú ý!
Trong các điều kiện của hàm “SUMIFS” này, số ô trong phạm vi tổng và số ô trong phạm vi điều kiện phải giống nhau. Ví dụ: tất cả đều có phạm vi từ 3 đến 8 ô. Nếu có phạm vi tổng từ 3 đến 10 ô. Nhưng phạm vi điều kiện là từ 3 đến 8 thì công thức sẽ tính không chính xác. Điều này phải được tính đến khi thiết lập hàm SUMIFS.
Có một hàm khác trong Excel tính toán chọn lọc theo điều kiện. Bạn có thể chỉ định phạm vi có độ dài khác nhau trong đó, nhưng bạn chỉ có thể chỉ định một điều kiện. Để biết thêm thông tin về cách sử dụng hàm “SUMIF” và về các ký hiệu có thể chèn vào tiêu chí hàm, hãy xem bài viết “

Hàm SUMIF cũng như SUMIF theo 2 tiêu chí

Hãy tưởng tượng một bảng trong đó tên của các phòng ban (hoặc tài khoản, hoặc thứ gì khác) được trộn lẫn trong các hàng.

Tính tổng các ô theo tiêu chí
Cần tính toán tổng cộng cho từng bộ phận. Nhiều người thực hiện việc này bằng cách sử dụng bộ lọc và viết bằng bút vào ô.
Mặc dù điều này có thể được thực hiện dễ dàng và đơn giản chỉ bằng một chức năng - SUMIF.
SUMIF– Tính tổng các ô thỏa mãn một điều kiện xác định (chỉ có thể xác định một điều kiện). Hàm này cũng có thể được sử dụng nếu bảng được chia thành các cột thành các kỳ (hàng tháng, mỗi tháng có ba cột - Thu nhập | Chi phí | Chênh lệch) và bạn cần tính tổng số tiền cho tất cả các kỳ chỉ cho Thu nhập, Chi phí và Chênh lệch.

Tổng cộng có ba đối số cho SUMIF: Phạm vi, Tiêu chuẩn, Tổng_Phạm vi.
=SUMIF(A1:A20000 ; A1 ; B1:B20000)
=SUMIF(A1:A20000,A1,B1:B20000)

• Phạm vi(A1:A20000) - biểu thị phạm vi có tiêu chí. Những thứ kia. cột để tìm giá trị được chỉ định bởi đối số Tiêu chuẩn.
• Tiêu chuẩn(A1) - giá trị (văn bản hoặc số, cũng như ngày) cần tìm thấy trong Phạm vi. Có thể chứa các ký tự đại diện "*" và "?". Những thứ kia. biểu thị như Tiêu chuẩn"*khối lượng*" sẽ tính tổng các giá trị trong đó từ "khối lượng" xuất hiện. Trong trường hợp này, từ “khối lượng” có thể xuất hiện ở bất kỳ đâu trong văn bản hoặc chỉ có thể có một từ này trong một ô. Và bằng cách chỉ định “khối lượng*”, tất cả các giá trị bắt đầu bằng “khối lượng” sẽ được tổng hợp. "?" - chỉ thay thế một ký tự, tức là Bằng cách chỉ định "mas?a", bạn có thể tính tổng các dòng có cả giá trị "khối lượng" và giá trị "mặt nạ", v.v.
  Nếu tiêu chí được viết trong một ô và bạn vẫn cần sử dụng các ký tự đại diện thì bạn có thể tạo liên kết đến ô này bằng cách thêm ký tự cần thiết. Giả sử chúng ta cần tính tổng các giá trị chứa từ "tổng". Từ “tổng” viết ở ô A1, ở cột A có thể có các cách viết khác nhau chứa từ “tổng”: “kết quả tháng 6”, “kết quả tháng 7”, “kết quả tháng 3”. Công thức khi đó sẽ trông như thế này:
  =SUMIF(A1:A20000 ;"*"& A1 &"*"; B1:B20000)
  "*"& A1 &"*" - dấu & (dấu và) kết hợp nhiều giá trị thành một. Những thứ kia. kết quả sẽ là "*tổng*".
  Để hiểu rõ hơn cách thức hoạt động của công thức, tốt hơn hết bạn nên sử dụng công cụ Tính công thức:
  Tất cả các tiêu chí văn bản và tiêu chí có dấu hiệu logic, toán học phải được đính kèm trong dấu ngoặc kép(=SUMIF(A1:A20000 ;"tổng"; B1:B20000)). Nếu tiêu chí là số thì bạn không cần sử dụng dấu ngoặc kép. Nếu bạn muốn tìm trực tiếp dấu chấm hỏi hoặc dấu hoa thị, bạn phải đặt dấu ngã (~) trước dấu hỏi đó.
  Bạn có thể tìm hiểu về dấu ngã và các tính năng của nó trong bài viết này:
• Tổng_Phạm vi(B1:B20000)(đối số tùy chọn) - biểu thị một phạm vi số tiền hoặc Giá trị kiểu số, cần được tóm tắt.

Cách thức hoạt động: chức năng tìm kiếm trong Phạm vi giá trị được chỉ định bởi đối số Tiêu chuẩn và nếu tìm thấy kết quả khớp, hãy tính tổng dữ liệu được chỉ định bởi đối số Tổng_Phạm vi. Những thứ kia. nếu chúng ta có tên bộ phận ở cột A và số tiền ở cột B, sau đó chỉ định “Bộ phận phát triển” làm tiêu chí thì kết quả của hàm sẽ là tổng của tất cả các giá trị trong cột B, ngược lại với “ Phòng Phát triển” xuất hiện ở cột A. Trên thực tế, Sum_Range có thể không có cùng kích thước với đối số Range và điều này sẽ không gây ra lỗi trong chính hàm đó. Tuy nhiên, khi xác định các ô để tính tổng, ô trên cùng bên trái trong đối số Sum_Range sẽ được sử dụng làm ô bắt đầu cho tổng, sau đó các ô khớp với kích thước và hình dạng của đối số Phạm vi sẽ được tính tổng.

Một số tính năng
Đối số hàm cuối cùng ( Tổng_Phạm vi- B1:B20000) là tùy chọn. Điều này có nghĩa là nó có thể không được chỉ định. Nếu bạn không chỉ định thì hàm sẽ tính tổng các giá trị được chỉ định bởi đối số Phạm vi. Nó dùng để làm gì? Ví dụ: bạn chỉ cần lấy tổng của những số lớn hơn 0. Cột A là số tiền. Sau đó, chức năng sẽ trông như thế này:
=SUMIF(A1:A20000 ;">0")

Những điều cần cân nhắc: phạm vi tổng Và phạm vi phải bằng nhau về số dòng. Nếu không, bạn có thể nhận được kết quả không chính xác. Sẽ là tối ưu nếu nó trông giống như các công thức tôi đã đưa ra: phạm vi Và phạm vi tổng bắt đầu bằng một dòng và có cùng số dòng: A1:A20000 ; B1:B20000

Tính tổng theo hai tiêu chí trở lên
Nhưng phải làm gì khi có từ 2 tiêu chí trở lên để tính tổng? Giả sử bạn chỉ cần tóm tắt những số tiền liên quan đến một bộ phận và chỉ trong một ngày nhất định. Những người sở hữu Office 2007 và các phiên bản cao hơn hài lòng có thể sử dụng hàm SUMIFS:
=SUMIFS($C$2:$C$50, $A$2:$A$50, $I$3, $B$2:$B$50, $H8)
$C$2:$C$50 - sum_range. Đối số đầu tiên cho biết phạm vi ô chứa số lượng sẽ được thu thập thành một.
$A$2:$A$50 , $B$2:$B$50 - Phạm vi tiêu chí. Chỉ định phạm vi ô để tìm kiếm kết quả khớp dựa trên tiêu chí.
$I$3 , $H8 - tiêu chí. Ở đây, giống như trong SUMIF, các ký tự đại diện được cho phép * Và ? và chúng hoạt động theo cùng một cách.

Điểm đặc biệt của việc chỉ định đối số: đầu tiên, phạm vi của tiêu chí được chỉ định (chúng được đánh số), sau đó, cách nhau bằng dấu chấm phẩy, giá trị (tiêu chí) cần tìm trong phạm vi này được chỉ định trực tiếp - $A$2:$ 50 đô la Úc ; $I$3 . Và không có gì khác. Trước tiên, bạn không nên cố gắng chỉ định tất cả các phạm vi và sau đó là tiêu chí cho chúng - hàm sẽ đưa ra lỗi hoặc tổng hợp sai.

Tất cả các điều kiện được so sánh theo nguyên tắc AND, nghĩa là nếu tất cả các điều kiện được liệt kê đều được đáp ứng. Nếu ít nhất một điều kiện không được đáp ứng, hàm sẽ bỏ qua dòng và không tính tổng bất cứ thứ gì.
Giống như đối với SUMIF, phạm vi tổng và tiêu chí phải bằng nhau về số hàng.

Bởi vì SUMIFS chỉ xuất hiện trong Các phiên bản Excel, bắt đầu từ năm 2007, người dùng không may mắn phải làm gì trong những trường hợp như vậy? phiên bản trước? Rất đơn giản: hãy sử dụng một hàm khác - SUMPRODVEL. Tôi sẽ không mô tả các lập luận, bởi vì... có rất nhiều trong số chúng và chúng đều là các mảng giá trị. Hàm này nhân các mảng được chỉ định bởi các đối số. Tôi sẽ cố gắng mô tả Nguyên tắc chung Sử dụng hàm này để tóm tắt dữ liệu theo nhiều điều kiện.
Để giải quyết vấn đề tính tổng theo một số tiêu chí, hàm sẽ có dạng như sau:
=SUMPRODVEL(($A$2:$A$50 = $I$3)*($B$2:$B$50 = H5); $C$2:$C$50)
$A$2:$A$50 - phạm vi ngày. $I$3 - ngày tiêu chí mà dữ liệu phải được tính tổng.
$B$2:$B$50 - tên các phòng ban. H5 - Tên phòng ban cần tổng hợp dữ liệu.
$C$2:$C$50 - phạm vi với số tiền.

Hãy nhìn vào logic, bởi vì... đối với nhiều người nó sẽ hoàn toàn không rõ ràng nếu chỉ nhìn vào Chức năng này. Nếu chỉ vì chứng chỉ không mô tả cách sử dụng tương tự của nó. Để dễ đọc hơn, hãy giảm kích thước của phạm vi:
=SUMPRODVEL(($A$2:$A$5 = $I$3)*($B$2:$B$5 = H5); $C$2:$C$5)
Vì vậy, biểu thức ($A$2:$A$5 = $I$3) và ($B$2:$B$5 = H5) là Boolean và trả về các mảng Boolean FALSE và TRUE. TRUE nếu ô trong phạm vi $A$2:$A$5 bằng giá trị của ô $I$3 và ô trong phạm vi $B$2:$B$5 bằng giá trị của ô H5 . Những thứ kia. chúng tôi nhận được những điều sau đây:
=TỔNG HỢP((FALSE,TRUE,TRUE,FALSE)*(FALSE,FALSE,TRUE,FALSE), $C$2:$C$50)
Như bạn có thể thấy, trong mảng đầu tiên có hai kết quả khớp với điều kiện và trong mảng thứ hai có một kết quả phù hợp. Tiếp theo, hai mảng này được nhân lên (dấu nhân (*) chịu trách nhiệm cho việc này). Khi nhân, các mảng FALSE và TRUE được ngầm chuyển thành các hằng số 0 và 1 tương ứng ((0;1;1;0)*(0;0;1;0)). Như bạn đã biết, khi nhân với 0 chúng ta bằng 0. Và kết quả là một mảng:
=TỔNG HỢP((0,0,1,0); $C$2:$C$50)
Tiếp theo, mảng (0;0;1;0) được nhân với một mảng số trong phạm vi $C$2:$C$50:
=TỔNG HỢP((0,0,1,0),(10,20,30,40))
Và kết quả là chúng tôi nhận được 30. Những gì chúng tôi cần - chúng tôi chỉ nhận được số tiền đáp ứng tiêu chí. Nếu có nhiều hơn một số tiền thỏa mãn tiêu chí, chúng sẽ được tính tổng.

Ưu điểm của SUMMIROUDS
Nếu các đối số có dấu cộng thay vì dấu nhân:
($A$2:$A$5 = $I$3)+($B$2:$B$5 = H5)
thì các điều kiện sẽ được so sánh bằng nguyên tắc OR: tức là Tổng số sẽ được tính tổng nếu đáp ứng ít nhất một điều kiện: $A$2:$A$5 bằng giá trị của ô $I$3 hoặc ô phạm vi $B$2:$B$5 bằng giá trị của ô H5 .
Đây chính là ưu điểm của SUMPRODVEL so với SUMIFS. SUMIFS không thể cộng giá trị bằng nguyên tắc OR mà chỉ sử dụng nguyên tắc AND (phải đáp ứng tất cả các điều kiện).

sai sót
Không thể sử dụng các ký tự đại diện * và ? trong SUMPRODVEL. Bạn có thể sử dụng nó chính xác hơn, nhưng chúng sẽ được coi không phải là các ký tự đặc biệt mà là dấu hoa thị và dấu chấm hỏi. Tôi coi đây là một bất lợi đáng kể. Mặc dù điều này có thể được giải quyết bằng cách sử dụng các hàm khác bên trong SUMPRODVEL, nhưng vẫn sẽ rất tuyệt nếu hàm này bằng cách nào đó có thể sử dụng các ký tự đại diện.

Trong ví dụ này, bạn sẽ tìm thấy một vài ví dụ về hàm để hiểu rõ hơn về những gì được viết ở trên.

Tải xuống ví dụ

(41,5 KiB, 8.356 lượt tải xuống)

Tổng thành chương trình excel Có lẽ ai cũng làm được. Nhưng với phiên bản cải tiến của lệnh SUM, được gọi là SUMIF, khả năng của thao tác này được mở rộng đáng kể.

Từ tên của lệnh, bạn có thể hiểu rằng nó không chỉ tính toán số lượng mà còn tuân theo một số điều kiện logic.

SUMIF và cú pháp của nó

Hàm SUMIF cho phép bạn tính tổng các ô đáp ứng một tiêu chí nhất định (điều kiện được chỉ định). Các đối số lệnh như sau:

1. Phạm vi – các ô cần được đánh giá dựa trên tiêu chí (điều kiện được chỉ định).
2. Tiêu chí – xác định ô nào trong phạm vi sẽ được chọn (được viết trong dấu ngoặc kép).
3. Phạm vi tổng là các ô thực tế cần được tính tổng nếu chúng đáp ứng tiêu chí.

Hóa ra hàm chỉ có 3 đối số. Nhưng đôi khi cái sau có thể bị loại trừ, và khi đó nhóm sẽ chỉ làm việc theo phạm vi và tiêu chí.

Hàm SUMIF hoạt động như thế nào trong Excel?

Hãy xem xét ví dụ đơn giản nhất, phần này sẽ trình bày rõ ràng cách sử dụng hàm SUMIF và mức độ thuận tiện của nó trong việc giải quyết một số vấn đề nhất định.

Chúng tôi có một bảng hiển thị tên nhân viên, giới tính của họ và mức lương tích lũy trong tháng Giêng. Nếu chỉ cần tính tổng số tiền cần phải trả cho nhân viên thì chúng ta sử dụng hàm SUM, biểu thị tất cả các mức lương dưới dạng một phạm vi.

Chúng tôi viết ra các lập luận.

1. Phạm vi trong trong trường hợp này sẽ là danh sách tất cả các vị trí của nhân viên, vì chúng ta sẽ cần xác định mức lương. Vì vậy, chúng tôi đặt E2:E14 xuống.
2. Tiêu chí lựa chọn trong trường hợp của chúng tôi là người bán. Chúng tôi đặt từ đó trong dấu ngoặc kép và đặt nó làm đối số thứ hai.
3. Phạm vi tổng hợp là tiền lương, vì chúng ta cần biết tổng tiền lương của tất cả nhân viên bán hàng. Do đó F2:F14.

Hóa ra là 92900. Đó là. chức năng tự động xử lý danh sách các vị trí, chỉ chọn nhân viên bán hàng từ họ và tổng hợp lương của họ.

Tương tự, bạn có thể tính lương của tất cả người quản lý, nhân viên thu ngân và nhân viên bảo vệ. Khi bảng nhỏ, dường như mọi thứ đều có thể được tính thủ công, nhưng khi làm việc với danh sách có vài trăm vị trí thì nên sử dụng SUMIF.

Hàm SUMIF trong Excel có nhiều điều kiện

Nếu để ký hiệu chuẩn Lệnh SUMIF thêm hai chữ cái nữa vào cuối - MN (SUMMESLI), có nghĩa là nó có nghĩa là một hàm có một số điều kiện. Nó được sử dụng khi có nhiều hơn một tiêu chí cần được chỉ định.

Cú pháp sử dụng hàm đa tiêu chí

SUMIFS có thể có bao nhiêu đối số tùy thích nhưng tối thiểu là 5.

1. Phạm vi tổng hợp. Nếu trong SUMIF anh ấy đứng cuối, thì ở đây anh ấy đứng ở vị trí đầu tiên. Nó cũng chỉ ra các ô cần được tính tổng.
2. Phạm vi của điều kiện 1 là các ô cần được đánh giá dựa trên tiêu chí đầu tiên.
3. Điều kiện 1 – xác định các ô mà hàm sẽ chọn từ phạm vi đầu tiên của điều kiện.
4. Phạm vi của điều kiện 2 là các ô cần được đánh giá dựa trên tiêu chí thứ hai.
5. Điều kiện 2 – xác định các ô mà hàm sẽ chọn từ phạm vi thứ hai của điều kiện.

Ví dụ sử dụng

• người bán;
• một ngươi phụ nư.

Vì vậy, chúng ta sẽ sử dụng lệnh SUMIFS.

Chúng tôi viết ra các lập luận.

• phạm vi tổng hợp – các ô có mức lương;
• phạm vi điều kiện 1 – các ô cho biết vị trí của nhân viên;
• điều kiện 1 – người bán;
• phạm vi điều kiện 2 – các ô cho biết giới tính của nhân viên;
• điều kiện 2 – nữ (f).

Kết quả: tất cả người bán nữ đều nhận được tổng cộng 51.100 rúp trong tháng 1.

SUMIF trong Excel với điều kiện động

Các hàm SUMIF và SUMIFS rất tốt vì chúng tự động điều chỉnh theo các điều kiện thay đổi. Những thứ kia. chúng ta có thể thay đổi dữ liệu trong các ô và số lượng sẽ thay đổi theo chúng. Ví dụ, khi tính lương, hóa ra chúng ta đã quên tính đến một nhân viên làm nhân viên bán hàng. Chúng ta có thể thêm một dòng nữa thông qua nút bên phải chuột và lệnh INSERT.

Chúng tôi có một dòng bổ sung. Xin lưu ý ngay rằng phạm vi điều kiện và tổng đã tự động mở rộng đến dòng 15.

Chúng ta sao chép dữ liệu của nhân viên và dán vào danh sách chung. Số tiền trong các ô tóm tắt đã thay đổi. Các chức năng phản hồi lại sự xuất hiện của một nữ nhân viên bán hàng khác trong dãy sản phẩm.

Tương tự, bạn không chỉ có thể thêm mà còn có thể xóa bất kỳ dòng nào (ví dụ: khi một nhân viên bị sa thải), thay đổi các giá trị (thay thế “Tháng Một” bằng “Tháng Hai” và thay thế mức lương mới), v.v.

Từ đó bạn cần tìm ra bao nhiêu bút chìđược bán bởi đại diện bán hàng Ivanov V. Tháng Một.

VẤN ĐỀ: Làm thế nào để tóm tắt dữ liệu theo một số tiêu chí??

GIẢI PHÁP: Cách 1:

BDSUMM(A1:G16;F1;I1:K2)

DSUM(A1:G16,F1,I1:K2)

Từ cơ sở dữ liệu chúng tôi đã chỉ định A1:G16 chức năng BDSUM lấy và tóm tắt dữ liệu cột Số lượng(lý lẽ " Cánh đồng" = F1) theo giá trị được chỉ định trong các ô I1:K2 (Người bán = Ivanov; Sản phẩm = Bút chì;Tháng = tháng Giêng) tiêu chuẩn.

Nhược điểm: Danh sách các tiêu chí phải có trên trang tính.

LƯU Ý: Số lượng tiêu chí tổng bị giới hạn bởi RAM.

KHU VỰC ỨNG DỤNG: Bất kỳ phiên bản Excel nào

Cách 2:

TỔNG HỢP((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

TỔNG HỢP((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)

LÀM THẾ NÀO NÓ HOẠT ĐỘNG:

Hàm SUMPRODVEL tạo thành các mảng giá trị TRUE và FALSE, theo tiêu chí đã chọn, trong bộ nhớ Excel.

Nếu các phép tính được thực hiện trong các ô của trang tính (để rõ ràng, tôi sẽ trình bày toàn bộ hoạt động của công thức như thể các phép tính diễn ra trên trang tính chứ không phải trong bộ nhớ), thì các mảng sẽ trông như thế này:

Rõ ràng là nếu, ví dụ, D2=Bút chì, thì giá trị sẽ là TRUE và nếu D3=Thư mục, thì FALSE (vì tiêu chí chọn sản phẩm trong ví dụ của chúng tôi là giá trị bút chì).

Biết rằng giá trị TRUE luôn bằng 1 và FALSE luôn bằng 0 nên chúng ta tiếp tục làm việc với mảng như với các số 0 và 1.
Bằng cách nhân các giá trị mảng kết quả với nhau một cách tuần tự, chúng ta nhận được MỘT mảng số 0 và số 1. Khi đáp ứng được cả ba tiêu chí lựa chọn, ( IVANOV, BÚT CHÌ, THÁNG 1) I E. tất cả các điều kiện lấy giá trị TRUE, chúng tôi nhận được 1 (1*1*1 = 1), nhưng nếu ít nhất một điều kiện không được đáp ứng, chúng tôi nhận được 0 (1*1*0 = 0; 1*0*1 = 0; 0*1*1 = 0).

Bây giờ tất cả những gì còn lại là nhân mảng kết quả với một mảng chứa dữ liệu mà cuối cùng chúng ta cần tính tổng (phạm vi F2:F16) và trên thực tế, tính tổng những gì không được nhân với 0.

Bây giờ hãy so sánh các mảng thu được bằng công thức và với phép tính từng bước trên trang tính (được đánh dấu màu đỏ).

Tôi nghĩ mọi thứ đều rõ ràng :)

NHƯỢC ĐIỂM: TỔNG HỢP - công thức mảng "nặng". Khi tính toán trên phạm vi dữ liệu lớn, thời gian tính toán lại tăng lên rõ rệt.

LƯU Ý

KHU VỰC ỨNG DỤNG: Bất kỳ phiên bản Excel nào

Cách 3: Công thức mảng

TỔNG(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

TỔNG(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))

LÀM THẾ NÀO NÓ HOẠT ĐỘNG: Hoàn toàn giống như Phương pháp số 2. Chỉ có hai điểm khác biệt - công thức này được nhập bằng cách nhấn Ctrl+Shift+Enter, và không chỉ bằng cách nhấn Đi vào và mảng 0 và 1 không được nhân với phạm vi tổng mà được chọn bằng hàm IF.

NHƯỢC ĐIỂM: Công thức mảng khi tính toán trên phạm vi dữ liệu lớn làm tăng đáng kể thời gian tính toán lại.

LƯU Ý: Số lượng mảng được xử lý được giới hạn ở 255.

KHU VỰC ỨNG DỤNG: Bất kỳ phiên bản Excel nào

Cách 4:

SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)