Cập nhật nội dung chi tiết về Cách Dùng Hàm Sumif Trong Excel mới nhất trên website Photomarathonasia.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Trong bài viết này, Học Excel Online sẽ giải thích và cung cấp một số ví dụ công thức cho dữ liệu dạng số, văn bản, ngày tháng và những kí tự đại diện.
Nếu bạn đang phải đối mặt với nhiệm vụ tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm là những gì mà bạn cần. Hướng dẫn này sẽ giải thích ngắn gọn về cú pháp của hàm và cách sử dụng chung, và tiếp đó bạn sẽ bổ sung thêm kiến thức trong việc thực hành với một số ví dụ công thức của hàm .
Hàm SUMIF, còn được gọi là tính tổng có điều kiện của Excel, được sử dụng để thêm các ô, dựa trên một điều kiện hoặc tiêu chí nhất định.
Nếu bạn đã đọc hướng dẫn về hàm COUNTIF trên trang blog này, thì bạn sẽ không gặp bất kỳ khó khăn nào khi hiểu hàm SUMIF, bởi cú pháp và cách sử dụng của nó cũng tương tự. Do đó, cú pháp của hàm SUMIF như sau:
SUMIF (range, criteria, [sum_range])
Như bạn thấy, hàm SUMIF có 3 đối số – 2 đối số đầu là bắt buộc, còn đối số thứ 3 là tùy chọn.
range – dải của các ô được đánh giá theo tiêu chí của bạn, ví dụ A1: A10.
– điều kiện/tiêu chí cần phải đáp ứng. Các tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày, biểu thức logic, tham chiếu ô, hoặc một hàm Excel khác. Ví dụ: bạn có thể nhập các tiêu chí như “5”, “”, “10/25/2014”, “<5”, “A1”, v.v …
Lưu ý: Hãy chú ý rằng bất kỳ điều kiện dạng văn bản hoặc điều kiện có chứa các ký hiệu toán học thì đều phải được đặt trong dấu nháy kép (“”). Đối với các điều kiện số, dấu nháy kép là không bắt buộc.
sum_range – các ô dùng để tính tổng, nếu điều kiện được đáp ứng. Đối số này là tùy bạn chọn, và bạn chỉ cần sử dụng nó nếu bạn muốn tính tổng các ô khác (mà đã được định nghĩa trong đối số range). Nếu đối số sum_range bị bỏ qua, khi đó Excel sẽ tính tổng các ô tương tự mà các tiêu chí được đặt ra (nghĩa là chúng đã được chỉ định trong đối số range).
range-A2: A8
criteria – “bananas”
sum_range – C2: C8
Đặt nó lại với nhau, và bạn nhận được công thức: = SUMIF (A2: A8, “bananas”, C2: C8)
= SUMIF (A2: A8, F1, C2: C8)
Chú thích: Tham số sum_range thực sự chỉ xác định ô trên cùng bên trái của dải được tính tổng. Khu vực còn lại thì lại được xác định bởi kích thước của đối số range.
Trong thực tế, điều này có nghĩa là đối số sum_range không nhất thiết phải có cùng kích thước với đối số dải, nghĩa là nó có thể khác nhau về số lượng hàng và cột. Tuy nhiên, ô đầu tiên (tức là ô trên cùng bên trái) của dải tính tổng phải luôn luôn ở đúng cột. Ví dụ, trong công thức SUMIF ở trên, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí là C2: C100 làm đối số , và kết quả vẫn sẽ như cũ. Tuy nhiên, hướng đi tốt nhất vẫn là cung cấp và có cùng kích thước.
SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng:
Hãy nhìn vào một số công thức SUMIF mà bạn có thể sử dụng để thêm vào các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định.
Lưu ý: Trong các công thức hàm SUMIF, một dấu so sánh theo sau bởi một số hoặc một văn bản phải luôn luôn được đặt trong dấu nháy kép (“”).
Tính tổng nếu bằng
= (có thể bỏ qua)
Tính tổng các giá trị trong các ô A2:A10 mà có giá trị bằng với giá trị trong ô D1.
Tính tổng nếu nhỏ hơn hoặc bằng
<=
=SUMIF(A2:A10, “<=10”, B2:B10)
Tính tổng các giá trị trong các ô
B2:B10 nếu giá trị trong ô tương ứng trong cột A nhỏ hơn hoặc bằng 10.
Cách sử dụng Hàm SUMIF với tiêu chí văn bản
Ngoài các con số, hàm SUMIF cho phép bạn thêm các giá trị mà có sự phụ thuộc vào việc liệu một ô tương ứng trong một cột khác có thể chứa một văn bản cho trước hay không.
Tính tổng nếu bằng
Đối chiếu hoàn toàn:=SUMIF(A2:A8, “bananas”, C2:C8)
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa chính xác từ “bananas” và không dư một từ hay kí tự nào. Các ô chứa “green bananas”, “bananas green” or “bananas!” không bao gồm trong trường hợp này.
Đối chiếu từng phần:=SUMIF(A2:A8, “*bananas*”, C2:C8)
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “green bananas”, “bananas green” or “bananas!” sẽ được tính trong trường hợp này.
Tính tổng nếu khác
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví như “yellow bananas” hoặc “bananas yellow”, thì chúng sẽ được tính tổng.
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác. Các ô như “yellow bananas” or “bananas yellow” sẽ không thỏa tiêu chí này.
Để biết thêm thông tin về giá trị khớp 1 phần, vui lòng xem ví dụ về hàm SUMIF với các kí tự đại diện.
Và bây giờ, hãy xem công thức chính xác “Tính tổng nếu khác” qua các thao tác. Như được minh họa trong ảnh chụp màn hình bên dưới, nó cho biết lượng tồn kho của tất cả các sản phẩm khác ngoài “goldfinger bananas”:
Sử dụng dấu so sánh với tham chiếu ô
Công thức 1: = SUMIF (A2: A8, “=” & F1, C2: C8)
Công thức 2: = SUMIF (A2: A8, F1, C2: C8)
Công thức SUMIF với kí tự đại diện
Nếu bạn mục đích của bạn là việc tính tổng có điều kiện các ô dựa trên tiêu chí “văn bản” và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn cần phải sử dụng các kí tự đại diện trong công thức SUMIF của bạn.
Hãy xem xét các kí tự đại diện sau đây:
Dấu hoa thị (*) – đại diện cho nhiều kí tự
Dấu hỏi (?) – đại diện cho một kí tự duy nhất ở một vị trí cụ thể
Ví dụ 1. Các giá trị tính tổng dựa trên đối chiếu từng phần:
= SUMIF (A2: A8, “bananas *”, C2: C8) – tiêu chí bao gồm các văn bản kết thúc với dấu hoa thị (*).
Nếu bạn chỉ muốn tính tổng những ô bắt đầu hoặc kết thúc với một văn bản nhất định, thì bạn chỉ cần thêm một dấu * trước hoặc sau văn bản:
= SUMIF (A2: A8, “bananas*”, C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “bananas”.
= SUMIF (A2: A8, “*bananas”, C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “bananas”.
Ví dụ 2. Tổng giá trị với số kí tự nhất định
Trong trường hợp bạn muốn tính tổng một số giá trị nếu giá trị này có chính xác 6 chữ cái, thì bạn sẽ sử dụng công thức sau:
= SUMIF (A2: A8, “??????”, C2: C8)
Ví dụ 3. Tính tổng các ô có giá trị tương ứng với giá trị văn bản
Nếu bảng tính của bạn chứa các loại dữ liệu khác nhau và bạn chỉ muốn tổng các ô tương ứng với giá trị văn bản, thì các công thức SUMIF sau sẽ có ích cho bạn:
= SUMIF (A2: A8, “? *”, C2: C8) – cộng giá trị trong các ô C2: C8 nếu ô tương ứng trong cột A chứa ít nhất 1 kí tự.
= SUMIF (A2: A8, “*”, C2: C8) – bao gồm các ô có vẻ là rỗng, chứa các chuỗi có độ dài bằng 0 (là kết quả của các công thức khác), ví dụ: = “”.
Cả hai công thức trên đều bỏ qua các giá trị không phải là văn bản như các lỗi, các phép toán luận, các chữ số và ngày tháng.
Ví dụ 4. Sử dụng * hoặc ? như những kí tự bình thường
Nếu bạn muốn * hoặc ? được coi như là một chữ chứ không phải là một kí tự đại diện, thì hãy sử dụng dấu ngã trước đó (~). Ví dụ: công thức SUMIF sau sẽ thêm các giá trị trong các ô C2: C8 nếu một ô trong cột A nằm trong cùng một hàng mà chứa một dấu chấm hỏi:
Tổng các số lớn nhất hoặc nhỏ nhất trong một dải:
Để tính tổng các số lớn nhất hoặc nhỏ nhất trong dải, hãy sử dụng hàm SUM cùng với hàm LARGE hoặc SMALL.
Ví dụ 1. Thêm một vài số lớn nhất / nhỏ nhất
Nếu bạn muốn tính tổng chỉ một số ít, ví dụ như 5 số, thì bạn có thể gõ chúng trực tiếp trong công thức, như sau:
= SUM (LARGE (B1: B10, {1,2,3,4,5})) – tổng 5 số lớn nhất
Chú thích. Nếu có 2 hoặc nhiều hơn số được buộc cho vị trí cuối cùng, chỉ có số đầu tiên xuất hiện sẽ được tính. Như bạn thấy, số 9 thứ hai không được thêm vào trong ví dụ trên.
Ví dụ 2. Thêm vào các giá trị lớn/nhỏ
= SUM (LARGE (B1: B50, ROW (INDIRECT (“1:15”)))) = SUM (SMALL (B1: B50, ROW (INDIRECT (“1:15”))))
Nếu bạn muốn thêm nhiều số, thay vì liệt kê tất cả trong công thức, bạn có thể tổ chức các hàm ROW và hàm INDIRECT trong công thức SUM của bạn. Trong hàm INDIRECT, hãy sử dụng số hàng đại diện cho số giá trị bạn muốn thêm vào. Ví dụ, các công thức sau đây tính tổng 15 số lớn nhất và nhỏ nhất, lần lượt là:
Ví dụ 3. Tính tổng một biến số trong các giá trị lớn nhất / nhỏ nhất
Nếu bạn không muốn thay đổi công thức mỗi khi bạn muốn tính tổng một số ô khác nhau, bạn có thể đặt số đó trong một ô nào đó thay vì nhập nó vào công thức. Và sau đó, bạn có thể sử dụng hàm ROW và INDIRECT như đã trình bày ở trên và tham chiếu một ô chứa biến, như trong trường hợp này là ô E1:
= SUM (LARGE (B1: B50, ROW (INDIRECT (“1:” & E1)))) – tính tổng các giá trị hàng đầu mà có chứa 1 ô thay đổi
Hãy nhớ rằng, đây là các công thức mảng, vì vậy bạn phải nhấn Ctrl + Shift + Enter để hoàn thành chúng.
Làm thế nào để tính tổng các ô tương ứng với các ô trống
Nếu “trống” thì có nghĩa là các ô đó hoàn toàn không có chứa gì – không có công thức, không có chuỗi chiều dài bằng 0 được trả lại bởi một số hàm Excel khác, khi đó bạn có thể sử dụng “=” làm tiêu chí, như trong công thức SUMIF sau:
= SUMIF (A2: A10, “=”, C2: C10)
Nếu “trống” bao gồm các chuỗi độ dài bằng không (ví dụ: ô có công thức như = “”), thì bạn có thể sử dụng “” làm tiêu chí:
= SUMIF (A2: A10, “”, C2: C10)
Làm thế nào để tính tổng các ô tương ứng với các ô không trống
Công thức trên tính tổng các giá trị tương ứng với tất cả các ô không rỗng, bao gồm các chuỗi có độ dài bằng không.
Cách sử dụng Hàm SUMIF với ngày tháng
Nói chung, bạn sử dụng hàm SUMIF để tính tổng một cách có điều kiện các giá trị dựa trên ngày cũng giống như cách bạn sử dụng văn bản và các tiêu chí số.
Tính tổng các giá trị ô dựa trên 1 ngày tháng nhất định
=SUMIF(B2:B9,”10/29/2014″,C2:C9)
Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là ngày 29/10/2014
Tính tổng các giá trị ô nếu ngày ở ô tương ứng lớn hơn hoặc bằng với ngày đã cho
Tính tổng các gí trị ô nếu ngày ở ô tương ứng lớn hơn ngày ở 1 ô khác.
Trong trường hợp bạn muốn tính tổng các giá trị dựa trên ngày hiện thời, thì bạn phải sử dụng hàm SUMIF kết hợp với hàm TODAY (), như sau:
Tính tổng các giá trị trong các ô dựa trên ngày hiện thời
=SUMIF(B2:B9, TODAY(), C2:C9)
Tính tổng các giá trị ô mà nằm trong quá khứ, có nghĩa là trước ngày hiện thời.
=SUMIF(B2:B9, “<“&TODAY(), C2:C9)
Tính tổng các giá trị ô mà nằm trong tương lai, có nghĩa là sau ngày hiện thời.
Tính tổng các giá trị ô nếu các ngày tương ứng đều được xảy ra trong 1 tuần (có nghĩa là ngày hiện thời + 7 ngày nữa)
=SUMIF(B2:B9, “=”&TODAY()+7, C2:C9)
Cách tính tổng các giá trị ứng với trong một quãng thời gian nhất định
Câu hỏi sau đây được hỏi thường xuyên trên các diễn đàn và blog của Excel – “Làm cách nào để tính tổng các giá trị ứng với thời gian nằm giữa hai ngày?”
Công thức này có thể có vẻ hơi khó khăn nếu bạn chỉ lướt qua, nhưng khi nếu đi sâu vào nó, thì bạn sẽ thấy nó rất đơn giản. Hàm SUMIF đầu tiên đã thêm tất cả các ô trong C2: C9, nếu ô tương ứng trong cột B lớn hơn hoặc bằng ngày bắt đầu (trong ví dụ này là ngày 1 tháng 10). Tiếp đó, bạn chỉ cần phải loại đi bất kỳ giá trị nào xảy ra vào sau ngày kết thúc (Oct-10 hay ngày 31/10) – là những giá trị được trả về bởi hàm SUMIF thứ hai.
Làm thế nào để tính tổng các giá trị trong một vài cột
Vì vậy, làm thế nào để bạn tìm thấy tổng số apples (táo) được bán ở tất cả các tiểu bang trong ba tháng qua?
Như bạn đã biết, kích thước của sum_range được xác định bởi kích thước của tham số range. Đó là lý do tại sao bạn không thể sử dụng công thức như = SUMIF (A2: A9, “apples”, C2: E9) bởi vì nó sẽ thêm các giá trị tương ứng với “Apples” mà chỉ ở trong cột C. Và đây không phải là những gì chúng ta đang tìm kiếm.
Giải pháp hợp lý và đơn giản nhất cho thấy chính nó là tạo ra một cột để trợ giúp tính từng tổng số cho mỗi hàng và sau đó cột này sẽ được tham chiếu trong tiêu chí sum_range.
Đi trước và đặt công thức SUM đơn giản trong ô F2, sau đó điền vào cột F: = SUM (C2: E2)
Sau đó, bạn có thể viết một công thức SUMIF đơn giản như sau:
= SUMIF (A2: A9, “apples”, F2: F9)
hoặc là
= SUMIF (A2: A9, H1, F2: F9)
Nếu bạn muốn làm mà không có một cột trợ giúp, sau đó bạn có thể viết một công thức SUMIF riêng cho mỗi cột bạn muốn tính tổng, và sau đó thêm các kết quả trả về với hàm SUM:
= SUM (SUMIF (A2: A9, I1, C2: C9), SUMIF (A2: A9, I1, D2: D9), SUMIF (A2: A9, I1, E2: E9))
Một cách khác là sử dụng một công thức mảng phức tạp hơn (nhưng cũng đừng quên nhấn Ctrl + Shift + Enter):
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}}
Cả hai công thức trên sẽ đưa ra kết quả là 2070 trong trường hợp trên.
1. Các tham số của range và sum_range phải là các dải, chứ không phải mảng:
Các tham số đầu tiên (range) và thứ ba (sum_range) trong công thức SUMIF của bạn phải luôn là một tham chiếu dải, như A1: A10. Nếu bạn bất chấp mà bỏ qua điều này, ví dụ là trường hợp tham chiếu một mảng như {1,2,3}, thì Excel với trả lại với một thông báo lỗi.
Công thức đúng: = SUMIF (A1: A3, “flower”, C1: C3)
Công thức sai: = SUMIF ({1,2,3}, “flower”, C1: C3)
2. Làm thế nào để tính tổng giá trị từ các trang tính hoặc bảng tính khác:
Củng giống như hầu hết các hàm Excel khác, SUMIF có thể tham chiếu đến các trang tính và bảng tính khác, miễn là chúng vẫn đang mở.
= SUMIF ([Book1.xlsx] Sheet1! $A$2:$A$9, “apples”, [Book1.xlsx] Sheet1! $F$2:$F$9)
Ví dụ: công thức sau đây sẽ tính tổng các giá trị trong các ô F2: F9 trong Sheet 1 (trang tính 1) của Book 1 (bảng tính 1) nếu có một ô tương ứng trong cột A và có trang tính đó cũng có chứa “apples”:
Tuy nhiên, công thức này sẽ không hoạt động ngay khi Book 1 bị đóng lại. Điều này xảy ra vì các dải được tham chiếu bởi các công thức SUMIF trong bảng tính đã đóng, vậy nên nó sẽ không được tham chiếu đến các mảng. Mặt khác, cũng do không có mảng nào được cho phép trong range và đối số sum_range, nên sau đó, công thức SUMIF sẽ trả về một thông báo lỗi #VALUE!.
3. Hãy chắc chắn range và sum_range có cùng kích cỡ:
Như đã lưu ý ở phần đầu của hướng dẫn này, trong các phiên bản hiện đại của Microsoft Excel, các tham số range và sum_range không nhất thiết phải bằng nhau. Từ phiên bản Excel 2000 trở về trước, range và sum_range có kích thước không đồng đều có thể gây ra vấn đề. Tuy nhiên, ngay cả trong các phiên bản mới nhất của Excel 2010 và 2013, các công thức phức tạp mà ở đó, nếu có ít hàng và/hoặc cột hơn , thì sẽ không hợp lệ. Đó là lý do tại sao nó được coi là một minh họa tốt để người dùng chú ý rằng phải luôn luôn giữ đối số range và sum_range cùng kích thước và hình dạng.
4. Làm thế nào để công thức SUMIF của bạn hoạt động nhanh hơn:
– – – – –
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
Định dạng theo điều kiện với Conditional formatting
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập báo cáo với Pivot Table…
Rất nhiều kiến thức phải không nào? 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 EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học.
Hàm Sumif Là Gì, Ý Nghĩa Hàm Sumif, Dùng Sumif Trong Excel
Hàm SUMIF là gì?
SUMIF là hàm được giới thiệu lần đầu trong Excel 2007. Sự ra đời của hàm này đã mang lại thắc mắc của nhiều người rằng hàm SUMIF khác gì hàm SUM, tại sao phải dùng nó trong khi đã có hàm SUM.
Công thức hàm SUMIF: =SUMIF(range,criteria,sum_range)
Nếu bạn cần thao tác các giá trị xuất hiện trong đối số phạm vi (tức là trích xuất năm từ ngày tháng để sử dụng trong tiêu chí), hãy xem hàm SUMPRODUCT và / hoặc FILTER.
Ý nghĩa của hàm SUMIF trong Excel
SUMIF cho phép mở rộng khả năng của hàm SUM. Thay vì cho phép tính tổng trong một phạm vi nhất định, để tính tổng với SUMIF tất cả các ô bắt buộc phải thỏa mãn được những điều kiện mà người dùng đưa vào tham số criteria.
Hàm SUMIF tiện lợi hơn rất nhiều nếu như bạn muốn tính tổng doanh thu của một đơn vị, doanh số của một nhóm nhân viên, hoặc doanh thu trong khoảng thời gian nhất định hoặc là tổng lương theo điều kiện nào đó.
Cách dùng hàm SUMIF trong Excel (cơ bản đến nâng cao)
Hàm SUMIF nhiều điều kiện (SUMIF nâng cao)
Nếu bạn đang cần tính tổng các giá trị có nhiều điều kiện (là OR). Điều này nghĩa là có ít nhất một trong các điều kiện thì sẽ được đáp ứng? Giải pháp đơn giản nhất cho bạn đó là tính được tổng tất cả các kết quả được trả về bởi một số hàm SUMIF.
Tính tổng bằng hàm SUMIF nhiều điều kiện (cách cơ bản)
Bài toán về cách tính tổng số sản phẩm do Mike và John cung cấp:
=SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)
Tính tổng bằng hàm SUMIF nhiều điều kiện (cách nâng cao)
Tính tổng các sản phẩm KTE với người cung cấp không phải là “JAMES”
Với cách tính đơn giản ở ví dụ trên, bạn sẽ cần tính tổng bằng hàm SUMIF 3 người cung cấp là JONE, SCARLET và MICHELS, nhưng ở đây chúng ta sẽ dùng công thức:
(C2: C12 là phạm vi ô cần tính tổng
A2: A12, KTE là ô đầu tiên phạm vi tiêu chí và tiêu chí
Sau đó nhấn phím Enter để nhận kết quả.
Hàm SUMIF kết hợp VLOOKUP (SUMIF nâng cao)
Ví dụ bài toán doanh thu dùng hàm SUMIF và VLOOKUP
Nội dung là nhập kết quả tên nhân viên và doanh số của tương ứng đạt được vào trong bảng 3. Bên cạnh đó có thể tra cứu thêm doanh số của từng nhân viên khác khi thay đổi họ tên. Do đó, bạn cần sử dụng đến hai hàm SUMIF và hàm Vlookup, để tính tổng doanh số của nhân viên với điều kiện cho trước.
Bước 1: Chúng ta sẽ áp dụng công thức vào bảng.
Công thức sẽ là: =SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)
Bước 2: Bạn sẽ nhập công thức bên trên tại ô C12 ở Bảng 3, rồi điền tên nhân viên muốn tính tổng doanh thu tại ô B12. Ở đây mình sẽ tính tổng doanh thu của Phí Thanh Lan.
Trước tiên điều cần làm là đổi cột đó về lại định dạng Number, rồi điều chỉnh để hiện dấu phẩy phân cách ở trong ô Format Cells. Còn phần Decimal places có thể tùy chỉnh tùy theo số mà bạn thấy dễ tính toán.
Bước 3: Bây giờ thì bạn đã có thể đổi tên của bất cứ nhân viên nào, không cần nhập công thức tính khác mà vẫn có kết quả chính xác.
Ví dụ mình sẽ nhập thông tin mã số nhân viên MS01 vào ô B12 tên nhân viên Trần Thu Hà, thì kết quả doanh thu cần tìm kiếm sẽ cho ra kết quả chính xác.
Hàm SUMIF kết hợp hàm AND (SUMIF nâng cao)
Giả sử như bạn hiện đang có một bảng liệt kê hàng trái cây từ nhiều nhà cung cấp. Và bạn có tên quả ở trong cột A, tên nhà cung cấp ở trong cột B và số lượng lại nằm trong cột C.
sum_range – C2: C9
criteria_range1 – A2: A9
criteria1 – “apples”
criteria_range2 – B2: B9
criteria2 – “Pete”
Bây giờ lại tất cả những thông số trên và bạn nhận được công thức cuối cùng như sau:
=SUMIFS(C2: C9, A2: A9, “táo”, B2: B9, “Pete”)
Để có thể khiến cho việc chỉnh sửa công thức đơn giản hơn, bạn có thể thay thế những tiêu chuẩn văn bản “apples” và “Pete” bằng những tham chiếu ô. Trong trường hợp bên dưới, bạn sẽ không cần phải thay đổi công thức để tính toán được lượng trái cây từ những nhà cung cấp khác nhau:
=SUMIFS(C2: C9, A2: A9, F1, B2: B9, F2).
Cách sử dụng Hàm SUMIF nhiều sheet
Cũng ở bài toán trên, làm thế nào để bạn có thể tìm được tổng số apples (táo) được bán ở tất cả các tiểu bang trong ba tháng vừa qua?
Như các bạn đã biết, kích thước của sum_range được xác định bởi kích thước của tham số range. Đó là lý do mà bạn không thể sử dụng công thức
=SUMIF(A2: A9, “apples”, C2: E9). Bởi vì nó sẽ tự thêm những giá trị tương ứng với “Apples” chỉ có ở trong cột C.
Giải pháp hợp lý và đơn giản nhất đó là nó tạo ra một cột để trợ giúp tính từng tổng số cho mỗi hàng và sau đó cột này sẽ được tham chiếu ở trong tiêu chí sum_range.
Đặt công thức SUM đơn giản bên trong ô F2, tiếp đến điền vào cột F: = SUM (C2: E2). Và sau đó, bạn có thể viết được một công thức SUMIF đơn giản:
=SUMIF(A2: A9, “apples”, F2: F9) hoặc là =SUMIF (A2: A9, H1, F2: F9)
Trong những công thức trên, sum_range có cùng kích thước với dải, tức là 1 cột và 8 hàng, và kết quả sẽ chính xác hơn. Nếu muốn làm mà không cần cột trợ giúp, thì có thể viết một công thức SUMIF riêng cho từng cột mà bạn muốn tính tổng. Sau đó thêm các kết quả về với hàm SUM như sau:
=SUM(SUMIF (A2: A9, I1, C2: C9),SUMIF (A2: A9, I1, D2: D9),SUMIF (A2: A9, I1, E2: E9))
Một cách khác là sử dụng một công thức mảng phức tạp hơn (nhưng cũng đừng quên nhấn Ctrl + Shift + Enter):
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}
Cách Dùng Hàm Weekday Trong Excel
Hàm WEEKDAY rất hữu ích trong phân tích của Excel. Giả sử bạn muốn xác định thời gian cần thiết để hoàn thành một dự án nhất định, chức năng của hàm này sẽ giúp ích cho bạn trong việc loại bỏ các ngày cuối tuần khỏi khung thời gian nhất định. Vì vậy, nó đặc biệt hữu ích khi bạn lập kế hoạch và lên lịch làm việc cho các dự án kinh doanh của mình.
Định nghĩa hàm Weekday
Hàm Weekday là hàm trả về kết quả giá trị là một số từ 0 đến 7, tương đương với 7 giá trị đó các thứ trong tuần.
Số 1 tương ứng là chủ nhật, từ số 2 đến số 7 tương đương là thứ 2 cho đến thứ 7 trong một tuần. Tất cả đều được quy ước mặc định là một số nguyên.
Hàm Weekday thường được áp dụng nhiều nhất khi cần tính toán ngày tháng năm cần trả về là thứ mấy trong năm của bảng Excel, đặc biệt xuất hiện nhiều đối với các bảng tính toán của nhân sự và bảng biểu.
Cú pháp của hàm Weekday
Các lỗi thường gặp trong Hàm WEEKDAY:
#VALUE! lỗi – Xảy ra khi serial_number đã cho hoặc [return_type] đã cho không phải là số.#NUM! lỗi – Xảy ra với các trường hợp: Đối số serial_number đã cho là số nhưng nằm ngoài phạm vi cho cơ sở ngày hiện tại. Đối số return_type đã cho không phải là một trong các giá trị được phép (1-3 hoặc 11-17).
Hướng dẫn sử dụng Hàm weekday trong Excel
Đây là một hàm dựng sẵn hoàn toàn có thể được sử dụng làm hàm bảng tính trong Excel. Để hiểu cách sử dụng của hàm này, bạn hãy xem xét ví dụ như sau:
Bạn hãy xem làm thế nào bạn có thể phân biệt chủ nhật và các ngày trong tuần. Nó hoàn toàn có thể được thực hiện bằng cách sử dụng các quy tắc định dạng có điều kiện.
Ở đây, bạn đặt return_type là 1 vì muốn Chủ nhật là ngày đầu tiên trong tuần. Vì vậy, ở đây một ngày thứ Sáu sẽ là ngày cuối tuần. Tuy nhiên khi bạn nhìn vào mặt thẩm mỹ thì không được rõ ràng, để cải thiện hơn việc trình bày dữ liệu, bạn hoàn toàn có thể sử dụng định dạng có điều kiện để làm nổi bật các ngày trong tuần với các màu sắc khác nhau.
Kết quả như sau:
Cách Dùng Hàm Average Trong Excel
1. Hướng dẫn dùng hàm Average Excel
Cú pháp hàm Average là =AVERAGE(number1,[number2],…).
Trong đó:
number1: đối số thứ nhất có thể là số, tham chiếu ô hoặc phạm vi chứa số mà các bạn muốn tính trung bình, bắt buộc.
number2: là các số, tham chiếu ô hoặc phạm vi chứa số bổ sung mà các bạn muốn tính trung bình tối đa 255 số, tùy chọn.
Người dùng cần lưu ý khi sử dụng:
Hàm AVERAGE có tối đa 256 đối số, có thể là số, tên, phạm vi hoặc tham chiếu ô có chứa số.
Đối số tham chiếu ô hoặc phạm vi có chứa giá trị logic, văn bản hay ô rỗng thì hàm tự động bỏ qua, riêng giá trị 0 sẽ được tính.
Những giá trị logic, biểu thị số dạng văn bản khi nhập trực tiếp vào danh sách các đối số trong hàm sẽ được đếm.
Nếu đối số là văn bản hay giá trị không thể chuyển đổi thành dạng số sẽ báo lỗi.
Bước 1:
Tại ô điểm số trung bình đầu tiên cho học sinh đầu tiên, người dùng nhập công thức =AVERAGE(C2,D2,E2) rồi nhấn Enter.
Để tính trung bình cộng của những ô tiếp theo, người dùng chỉ cần trỏ chuột vào ô kết quả đầu tiên và dùng chuột kéo xuống các ô phía dưới.
2. Sửa lỗi #VALUE dùng hàm Average
Trong khi sử dụng hàm Average để tính trung bình cộng dữ liệu trên Excel, người dùng sẽ gặp một số lỗi phổ biến như báo lỗi #VALUE!. Lỗi này xảy ra khi đối số trong phép tính mà hàm Average không thể diễn giải các giá trị đó là giá trị số.
Để khắc phục lỗi #VALUE!, bạn cần dùng thêm 2 hàm IF và hàm ISERROR bỏ qua vùng tham chiếu chứa lỗi, để tính trung bình các giá trị bình thường còn lại.
Công thức hàm là =AVERAGE(IF(ISERROR(C2:E2),””,C2:E2)).
Do đây là công thức mảng nên người dùng cần nhập tổ hợp phím Ctrl + Shift + Enter để hiển thị 2 dấu ngoặc đầu và cuối hàm. Nếu không có dấu ngoặc kết quả vẫn sẽ báo lỗi.
Bạn đang đọc nội dung bài viết Cách Dùng Hàm Sumif Trong Excel trên website Photomarathonasia.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!