Bạn đang xem bài viết Hướng Dẫn Sử Dụng Hàm Sumif Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hàm SUMIF trong Excel có thể được hiểu là sự kết hợp giữa hàm SUM với hàm IF, nghĩa là cộng có điều kiện. SUMIF sẽ được dùng trong không ít các công việc, ví dụ như tổng hợp thống kê.
Excel có hàm SUM để cộng giá trị của một dãy hàng hay một dãy cột, cấu trúc dạng SUM(A20:F20) hoặc SUM(A1:A6). Tuy nhiên phép cộng sẽ có chọn lọc hơn nếu áp theo một tiêu chí nào đó, và lúc đó chúng ta sẽ dùng hàm SUMIF hoặc SUMIFS.
Hướng dẫn cách sử dụng hàm SUMIF trong Excel
Khi dùng hàm SUMIF chúng ta có cấu trúc như sau:
=SUMIF(khoảng xét tiêu chí, tiêu chí, khoảng tính)
Như vậy, hàm SUMIF sẽ xét trong một dãy ô hoặc dãy cột, kiểu như A20:F20 (từ ô cột A hàng 20 đến ô cột F hàng 20) hay A1:A6. Bất kỳ ô nào có giá trị đạt tiêu chí sẽ được dùng để tham chiếu sang ô tương ứng trong khoảng tính, kiểu như A21:F21 hay B1:B6.
Trong khi đó với tiêu chí để so sánh, ngoại trừ tiêu chí dùng giá trị chữ số, bất kỳ tiêu chí nào dùng ký tự text hoặc dùng biểu thức toán học đều cần đặt trong ngoặc kép.
Ví dụ, chúng ta có một bảng thu nhập của các thành viên trong công ty, với phần kê thu nhập ở cột D và phần thông tin chức vụ ở cột C. Lúc đó để có thống kê tự động tổng thu nhập của những ai là nhân viên, chúng ta sẽ có cấu trúc kiểu như sau:
=SUMIF(C2:C10,”Nhân viên”,D2:D10)
Thực ra trong 3 tham số của hàm SUMIF nêu trên chúng ta có thể bỏ trống khoảng tính, và lúc đó khoảng tính sẽ chính là khoảng xét tiêu chí.
Với ví dụ trên, nếu muốn thống kê tự động thu nhập của những ai có thu nhập từ 10 triệu trở lên thì chúng ta dùng cấu trúc kiểu như sau:
Hướng dẫn cách sử dụng hàm SUMIFS trong Excel
So với SUMIF, chữ “S” trong hàm SUMIFS chính là thể hiện của việc có nhiều tiêu chí cần thỏa mãn hơn, từ 2 tiêu chí trở lên. Hàm SUMIFS 2 tiêu chí có cấu trúc như sau:
=SUMIFS(khoảng tính, khoảng xét tiêu chí 1, tiêu chí 1, khoảng xét tiêu chí 2, tiêu chí 2)
Vẫn với ví dụ nêu trên, nếu muốn thống kê tự động thu nhập của những ai là nhân viên đồng thời có thu nhập từ 10 triệu đồng trở lên, chúng ta sẽ phải dùng hàm SUMIFS 2 tiêu chí như sau:
S
Với những hàm IF, AND hay OR trong Excel thì những ai đã từng học lập trình sẽ dễ dàng nắm bắt được hơn, tuy nhiên những ai chưa từng học thì hoàn toàn có thể nắm được sau một vài ví dụ.
Hàm Sumif Là Gì ? Cách Sử Dụng Hàm Sumif Trong Excel
Hàm SUMIF là gì ?
Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.
Công thức và cú pháp hàm SUMIF SUMIF( phạm vị, điều kiện, [sum_range] )phạm vi Phạm vi của các ô mà bạn muốn áp dụng các tiêu chí chống lại. tiêu chí Các tiêu chí được sử dụng để xác định các ô cần thêm. sum_range Không bắt buộc. Đây là phạm vi của các ô để tổng hợp lại với nhau. Nếu tham số này bị bỏ qua, nó sử dụng phạm vi là sum_range .
Hàm SUMIF trả về một giá trị số.
Áp dụng đối vớiExcel cho Office 365, Excel 2023, Excel 2023, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Cách sử dụng hàm SUMIFHãy nhìn vào hình ảnh bên phải. Nếu bạn chỉ muốn thêm các giá trị lớn hơn năm, bạn có thể viết công thức SUMIF như sau:
Vì các ô A2 và A5 chứa một giá trị lớn hơn năm, chúng sẽ là các ô duy nhất được cộng với nhau bởi công thức, dẫn đến giá trị là 17.
Ví dụ về hàm SUMIF Sử dụng hàm sumif với con sốVí dụ 1: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi A1: A5 nhỏ hơn hoặc bằng 10.
SUMIF(A1:A5,"<=10")Ví dụ 2: Hàm SUMIF sau đây cho kết quả chính xác như nhau. Nó sử dụng toán tử & để nối biểu tượng ‘nhỏ hơn hoặc bằng’ và giá trị trong ô C1.
SUMIF(A1:A5,"<="&C2)Ví dụ 3: Hàm SUMIF bên dưới (ba đối số, đối số cuối cùng là phạm vi cần tính tổng) tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa giá trị 25.
Ví dụ 4: Hàm SUMIF sau đây cho kết quả chính xác tương tự (đối số thứ hai đề cập đến ô D1).
Sử dụng hàm sumif với chữ văn bảnVí dụ 5: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác “circle”.
Ví dụ 6: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 không chứa chính xác “triangle”.
Ví dụ 7: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “circle” + 1 ký tự. Một dấu hỏi (?) Khớp với chính xác một ký tự.
Ví dụ 8: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa một chuỗi gồm 0 hoặc nhiều ký tự + le. Dấu hoa thị (*) khớp với một loạt các ký tự bằng 0 hoặc nhiều hơn.
Ví dụ 9: Các hàm SUMIF bên dưới các giá trị tổng trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “triangle” hoặc “circle8”.
Sử dụng hàm SUMIF thứ ngày thángLưu ý: hàm DATE trong Excel chấp nhận ba đối số: năm, tháng và ngày.
Ví dụ 12: Hàm SUMIFS bên dưới (có chữ S ở cuối) tính tổng doanh số giữa hai ngày (bao gồm cả ngày bắt đầu và ngày kết thúc).
Lưu ý: hàm SUMIFS trong Excel tính tổng các giá trị dựa trên hai hoặc nhiều tiêu chí (đối số đầu tiên là phạm vi cần tính tổng, theo sau là hai hoặc nhiều cặp phạm vi / tiêu chí). Điều chỉnh ngày để tính tổng doanh số trong một tháng, năm cụ thể, v.v.
Mẹo Sử Dụng Hàm Sumifs Trong Excel
Lượt Xem:984
Khi bạn có danh sách dữ liệu và bạn cần thực hiện tổng của một vài ô trong danh sách này, hàm SUMIFS là hàm để sử dụng.
Vâng, trên thực tế, có 2 chức năng để làm điều này:
Giống như sự khác biệt giữa COUNTIF và COUNTIFS , sự khác biệt là số lượng tiêu chí
Với COUNTIF, bạn có thể đếm các hàng cho một tiêu chí
= COUNTIF ( Phạm vi 1, Tiêu chí1 )
Với COUNTIFS bạn có thể đếm các hàng cho 256 tiêu chí
= COUNTIFS ( Phạm vi 1, Tiêu chí1, Phạm vi 2, Tiêu chí2, Phạm vi 3, …)
Việc xây dựng là như nhau, bạn chỉ cần thêm tiêu chí khác
Nhưng đối với SUMIF và SUMIFS, cách viết công thức cũng khác nhau
= SUMIF ( Phạm vi 1, Tiêu chí1, Cột để tính tổng )
= SUMIFS ( Cột để tổng hợp, Phạm vi 1, Tiêu chí1, Phạm vi 2, Tiêu chí2, … )
Với SUMIFS, cột để tổng hợp là tham số đầu tiên nhưng với hàm SUMIF, cột để tổng hợp là cột cuối cùng.
Bây giờ, hãy xem cách sử dụng hàm trong ví dụ. Hãy lấy bảng tính này, nơi chúng tôi có doanh số bán hàng cho tất cả khách hàng của chúng tôi, theo quốc gia, theo thể loại và số lượng của mỗi lần bán hàng.
Câu hỏi hay và câu trả lời rất dễ
Đầu tiên, chúng ta chọn cột để tính tổng (cột E)
= SUMIFS ($ E $ 2: $ E $ 263
Sau đó, chúng tôi chọn phạm vi tiêu chí đầu tiên (cột Quốc gia)
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263, “Đức”
Sau đó, chúng tôi chọn phạm vi thứ hai của tiêu chí và giá trị thứ hai
= SUMIFS ($ E $ 2: $ E $ 263, $ B $ 2: $ B $ 263, “Đức”, $ D $ 2: $ D $ 263, “Uống”)
Lý do tốt nhất để sử dụng hàm SUMIFS là thực hiện lựa chọn giữa 2 ngày. Để làm điều này, chúng ta phải xem xét
Một lựa chọn vào một ngày lớn hơn (ngày đầu tiên)
Một lựa chọn khác vào một ngày thấp hơn (ngày cuối cùng)
Vì vậy, trong cùng một dữ liệu trước đó, nếu chúng ta muốn tất cả doanh thu từ ngày 01/01/2017 đến ngày 31/01/2017, công thức là
= SUMIFS ($ E $ 2: $ E $ 263
Và đối với tiêu chí đầu tiên
Và đối với tiêu chí thứ hai trên CỘT CÙNG
Nhìn vào tiêu chí thứ hai, dấu lôgic là <01/02/2017 . Nếu bạn muốn phải viết ngày cuối cùng của tháng, bài kiểm tra sẽ như sau: <= 31/01/2017
Như bạn có thể thấy ở đây, tham chiếu của cột được viết với tên của cột. Điều này là do chúng tôi đã chèn dữ liệu vào một Bảng.
Hướng Dẫn Cách Sử Dụng Hàm Vlookup Kết Hợp Với Hàm Sum Hoặc Sumif Trong Excel
Nhiệm vụ có thể khác nhau, nhưng có cùng bản chất – bạn muốn tìm và tính tổng các giá trị dựa trên một hay nhiều tiêu chí trong Excel. Đó là loại giá trị gì? Bất cứ giá trị số nào. Vậy tiêu chí là gì? Bất kỳ tiêu chí nào 🙂 Bắt đầu từ một chữ sô hay một tham chiếu đến một ô chứa giá trị chính xác, rồi kết thúc bằng toán tử logic và công thức trong Excel trả về kết quả.
Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.
HÀM VLOOKUP VÀ HÀM SUM TRONG EXCEL – TÍNH TỔNG CỦA CÁC GIÁ TRỊ TRÙNG KHỚPGiả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.
Giải pháp là sử dụng mảng trong thông số thứ ba (col_index_num) của hàm VLOOKUP trong Excel. Đây là công thức hàm VLOOKUP mẫu:
=SUM(VLOOKUP(lookup_value, lookup_range, {2,3,4}, FALSE))
Như bạn có thể thấy, chúng ta sử dụng mảng {2,3,4} trong câu lệnh thứ ba để hiển thị vài giá trị cần tìm trong cùng công thức VLOOKUP để tính tổng các giá trị ở cột 2, 3, và 4.
Và bây giờ, hãy điều chỉnh sự kết hợp của hàm VLOOKUP và hàm SUM đối với dữ liệu của chúng ta để tính tổng tất cả doanh số từ cột B đến cột M trong bảng trên:
=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))
Quan trọng! Vì bạn đang tạo một công thức mảng, hãy chắc rằng bạn nhấp Ctrl+Shift+Enter thay vì chỉ nhấn mỗi Enter khi bạn gõ xong. Khi bạn thực hiện xong, Microsoft Excel sẽ đóng khung công thức của bạn trong dấu ngoặc nhọn như thế này:
{=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}
Nếu bạn nhấp Enter, thì chỉ có giá tị đầu tiên trong mảng được xử lý, điều này sẽ cho kết quả sai.
Như bạn có thể thấy, việc sử dụng hàm VLOOKUP cùng với hàm SUM rất dễ. Tuy nhiên, đây không phải là giải pháp hoàn hảo, đặc biệt khi bạn đang thao tác với bảng lớn. Điểm mấu chốt ở đây là việc sử dụng công thức mảng có thể gây ảnh hưởng ngược lại hoạt động của sổ làm việc vì mỗi giá trị trong mảng cần một hàm VLOOKUP riêng biệt. Vì thế, càng có nhiều giá trị trong mảng thì càng có nhiều công thức mảng trong sổ làm việc, Excel sẽ chạy càng chậm.
Bạn có thể loại bỏ vấn đề này bằng cách sử dụng hàm INDEX và hàm MATCH thay vì hàm VLOOKUP và hàm SUM, và tôi sẽ cung cấp cho bạn vài ví dụ công thức trong bài viết tiếp theo.
CÁCH THỰC HIỆN CÁC PHÉP TÍNH KHÁC VỚI HÀM VLOOKUP TRONG EXCELTính giá trị trung bình
{=AVERAGE(VLOOKUP(A2, ‘Bảng cần tìm’!$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ ô A2 trong “Bảng cần tìm’ và tính giá trị trung bình ở các cột B,C,D trên cùng một hàng.
Tìm giá trị lớn nhất
{=MAX(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’ rồi tìm giá trị lớn nhất ở các cột B, C, D trên cùng một hàng.
Tìm giá trị nhỏ nhất
{=MIN(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’ rồi tìm giá trị nhỏ nhất ở các cột B, C, D trên cùng một hàng.
Tính phần trăm trên tổng số
{=0.3*SUM(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’, tính tổng giá trị ở các cột B, C, D trên cùng một hàng, rồi tính 30% trên tổng số.
Lưu ý. Vì các công thức trên đều là công thức mảng, nên hãy nhớ nhấn Ctrl+Shift+Enter để nhập chúng vào ô được chính xác.
Nếu ta thêm các công thức trên vào bảng “Tổng doanh số” của ví dụ trước, thì kết quả sẽ tương tự như thế này:
Trong trường hợp thông số cần tìm là một mảng thay vì một giá trị riêng lẻ, thì hàm VLOOKUP sẽ không có tác dụng bởi vì nó không thể tìm kiếm trong các mảng dữ liệu. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP – hàm tương tự với hàm VLOOKUP nhưng có thao tác với mảng và với các giá trị riêng lẻ.
Hãy xem xét các ví dụ sau, để bạn hiểu hơn điều mà tôi nói. Giả sử, bạn có một bảng liệt kê tên khách hàng, sản phẩm mua, và số lượng (Bảng chính). Bạn cũng có bảng thứ hai liệt kê giá sản phẩm (Bảng cần tìm). Nhiệm vụ của bạn là lập công thức tính tổng các đơn hàng đặt bởi các khách hàng xác định.
=SUM(LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Bởi vì đây là công thức mảng, hãy nhớ nhấn Ctrl+Shift+Enter để hoàn thành.
Chúng ta sẽ đặt hàm SUM qua một bên, bởi vì công dụng của nó quá rõ ràng, và chỉ tập trung vào 3 thành phần được được nhân với nhau:
LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)
Hàm LOOKUP này tìm kiếm hàng hóa được liệt kê trong cột C trong bảng chính, và trả về giá tiền tương ứng từ cột B trong bảng cần tìm.
Thông số này trả về số lượng mỗi sản phẩm mua bởi khách hàng, số lượng được liệt kê ở cột D trong bảng chính. Khi được nhân với giá tiền, điều này được trả về bởi hàm VLOOKUP ở trên, hàm này sẽ cung cấp cho bạn giá tiền của mỗi sản phẩm đã mua.
Công thức này so sánh tên khách hàng ở cột B với tên khách hàng ở ô G1. Nếu trùng khớp, nó sẽ trả về “1”, nếu không thì nó sẽ trả về “0”. Bạn sử dụng nó chỉ để xóa tên khách hàng không xuất hiện ở ô G1, bởi vì tất cả chúng ta đều biết bất cứ số nào nhân với 0 đều bằng 0.
Bởi vì công thức của chúng ta là công thức mảng nên nó lặp lại quá trình trên cho mỗi giá trị trong mảng cần tìm. Và cuối cùng, hàm SUM tính tổng các kết quả của phép nhân. Chẳng có gì khó khăn cả, đúng không nào?
Lưu ý. Để công thức LOOKUP hoạt động chính xác thì bạn cần phải lọc cột cần tìm trong bảng cần tìm theo thứ tự tăng dần (từ A đến Z). Nếu việc lọc không được chấp nhận đối với dữ liệu của bạn, thì hãy thử qua công thức SUM/TRANSPOSE được gợi ý bởi Leo.
HÀM VLOOKUP VÀ HÀM SUMIF – TÌM KIẾM VÀ TÍNH TỔNG CÁC GIÁ TRỊ ĐÁP ỨNG CÁC TIÊU CHUẨN NHẤT ĐỊNHBảng chính chứa nhiều mục có cùng số chứng minh nhân dân theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng chính.
Trước khi lập công thức, hãy để tôi nhắc bạn cú pháp của hàm SUMIF:
SUMIF(range, criteria, [sum_range])
range – thông số này đã tự giải thích cho chính nó rồi, chỉ là một dải ô mà bạn muốn đánh giá thông qua các tiêu chuẩn nhất định.
criteria – điều kiện nói cho công thức biết cần tính tổng giá trị nào. Nó có thể được dùng dưới dạng số, tham chiếu ô, biểu thức, hay hàm Excel khác.
sum_range – thông số này là tùy chọn, nhưng rất quan trọng đối với chúng ta. Nó xác định dải ô nơi mà các giá trị của ô tương ứng sẽ được cộng. Nếu loại bỏ, Excel sẽ tính tổng tất cả giá trị của các ô đã được xác định ở câu lệnh thứ nhất (thông số đầu tiên).
Hãy nhớ những điều trên, giờ chúng ta hãy xác định ba thông số cho hàm SUMIF của mình. Có thể bạn vẫn còn nhớ, chúng ta muốn tính tổng tất cả doanh số của một người xác định. Tên của người đó được nhập vào ô F2 trong bảng chính (hãy xem lại hình ảnh ở trên).
Range – bởi vì chúng ta đang tìm kiếm thông qua số chứng minh nhân dân của người bán, nên thông số range cho hàm SUMIF của chúng ta chính là cột B ở bảng chính. Bây giờ, bạn có thể nhập dải ô B:B, hay nếu bạn đổi dữ liệu của mình thành bảng, bạn có thể sử dụng tên cột để thay thế: Main_table[ID]
Criteria – bởi vì chúng ta có tên người bán hàng ở bảng khác (bảng khác), nên chúng ta phải công thức VLOOKUP để tìm số chứng minh nhân dân tương ứng của người đó. Tên của người đó được nhập ở ô F2 trong bảng chính, vì thế chúng ta sử dụng công thức này để tìm kiếm nó:
VLOOKUP($F$2,Lookup_table,2,FALSE)
Tất nhiên, bạn có thể nhập tên trong tiêu chuẩn cần tìm của hàm VLOOUP, nhưng sử dụng tham chiếu ô tuyệt đối thì tốt hơn bởi vì nó tạo ra công thức chung có hiệu quả cho bất cứ tên nào được nhập vào ô xác định.
Sum range – đây là phần dễ nhất. Bởi vì các con số thể hiện doanh thu của chúng ta nằm ở cột C có tên “Doanh số”, chúng ta chỉ cần đặt Main_table[Sales].
Bây giờ, tất cả những gì bạn cần là tập hợp tất cả các phần của công thức lại và công thức SUMIF + VLOOKUP của bạn đã sẵn sàng:
=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học
Cập nhật thông tin chi tiết về Hướng Dẫn Sử Dụng Hàm Sumif Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!