Cập nhật nội dung chi tiết về Cách Sử Dụng Countif, Sumif, Và Averageif Trong Excel mới nhất trên website Beiqthatgioi.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.
Học Microsoft Excel chủ yếu xoay quanh các công thức và các hàm. Kết hợp đủ những cái này, và bạn có thể làm bất cứ điều gì với một bảng tính.
Trong hướng dẫn này, bạn sẽ tìm hiểu cách làm thế nào để sử dụng ba công thức Excel mạnh mẽ: SUMIF, COUNTIF, và AVERAGEIF.
Trong ảnh chụp màn hình ở trên, bạn có thể thấy rằng chúng tôi có một danh sách các giao dịch ở phía bên trái. Nếu tôi muốn tập trung vào chi tiêu của tôi, tôi có thể sử dụng ba công thức này để theo dõi nó.
Ở phía bên phải, hộp Dining Out Expense sử dụng ba công thức để giúp tôi theo dõi các chi phí của tôi:
COUNTIF – được sử dụng để đếm số lần mà “Restaurant” xuất hiện trong danh sách.
SUMIF – tính toán tất cả chi phí cho các mục có gắn nhãn “Restaurant.”
AVERAGIEF – Tính trung bình tất cả các chi phí “Restaurant” của tôi trong danh sách.
Tổng quát hơn, đây là những gì mỗi công thức làm cho bạn, và làm thế nào bạn có thể sử dụng chúng:
COUNTIF – đếm số mặt hàng thoả một điều kiện, chẳng hạn như đếm số lần một cái tên xuất hiện trong danh sách.
AVERAGEIF – Tính trung bình có điều kiện; ví dụ, bạn có thể tính trung bình các loại của bạn để kiểm tra.
Các công thức này cho phép bạn thêm logic vào bảng tính của bạn. Hãy xem xét cách làm thế nào để sử dụng mỗi công thức.
COUNTIF, SUMIF, và AVERAGEIF trong Excel (Video hướng dẫn nhanh)
Nếu bạn muốn tìm hiểu với bài viết, hướng dẫn từng bước, hãy đọc tiếp. Tôi sẽ chia sẻ những lời khuyên về cách sử dụng các công thức này và các ý tưởng cho thấy tại sao chúng hữu ích.
Cách sử dụng SUMIF trong Excel
Sử dụng tab có tiêu đề SUMIF trong bảng tính ví dụ miễn phí cho phần này của hướng dẫn.
Hãy hình dung về SUMIF như là một cách để cộng các giá trị đáp ứng quy tắt đặt ra. Chúng ta có thể cộng một danh sách các giá trị từ một thể loại nhất định, hoặc tất cả các giá trị lớn hơn hoặc nhỏ hơn một số tiền cụ thể.
Tôi muốn biết hai điều:
Tổng cộng những gì tôi đã chi tiêu tại nhà hàng trong tháng.
Tất cả các chi tiêu lớn hơn $50 trong tháng, từ bất kỳ thể loại nào.
Thay vì thêm dữ liệu bằng tay, chúng ta có thể viết hai công thức SUMIF để tự động hoá quá trình. Tôi sẽ đặt kết quả trong hộp Restaurant Expense màu xanh lá cây ở bên phải. Hãy xem xét cách làm thế nào.
Tổng chi phí nhà hàng
=SUMIF(B2:B17,"Restaurant",C2:C17)
Để tìm tổng chi phí nhà hàng của tôi, tôi sẽ tổng hợp tất cả các giá trị với các loại chi phí “Restaurant”, nó là ở cột B.
Đây là công thức mà tôi sẽ sử dụng trong ví dụ này:
Lưu ý rằng mỗi phần được phân cách bằng dấu phẩy. Công thức này có ba điều quan trọng:
Nhìn vào những gì có trong ô B2 đến B17 cho các loại chi phí
Sử dụng “Restaurant” làm tiêu chí cho những gì cần tổng hợp
Sử dụng các giá trị trong ô C2 đến C17 để cộng tất cả số tiền
Khi tôi nhấn enter, Excel sẽ tính toán tổng số chi phí nhà hàng của tôi. Sử dụng SUMIF, thật dễ dàng để tạo ra các số liệu thống kê nhanh chóng để giúp bạn theo dõi các dữ liệu của một các loại cụ thể.
Chúng ta đã kiểm tra cho một thể loại cụ thể, nhưng bây giờ chúng ta hãy tổng hợp tất cả các giá trị lớn hơn một số tiền từ bất kỳ thể loại. Trong trường hợp này, tôi muốn tìm thấy tất cả các chi phí lớn hơn $50.
Hãy viết một công thức đơn giản để tìm tổng của tất cả các chi phí lớn hơn $50:
=COUNTIF(các ô cần đếm, tiêu chí để đếm)
Ví dụ này sử dụng một ký hiệu lớn hơn, nhưng bổ sung thêm: hãy thử tổng hợp tất cả các chi phí nhỏ, chẳng hạn như tất cả các chi phí dưới hoặc bằng 20$.
Cách sử dụng COUNTIF trong Excel
Trong khi SUMIF được sử dụng để thêm giá trị thoả một điều kiện nhất định, COUNTIF sẽ đếm số lần thứ gì đó xuất hiện trong một tập hợp dữ liệu.
Đây là định dạng chung cho công thức COUNTIF:
=COUNTIF(B2:B17,"Clothing")
Sử dụng cùng một tập hợp dữ liệu, hãy đếm hai mẩu thông tin quan trọng:
Số lượng mua quần áo mà tôi đã thực hiện trong một tháng
Số lượng mua 100$ hoặc lớn hơn
Số lượng mua quần áo
COUNTIF trước tiên sẽ tìm kiếm loại chi phí và đếm số lượng các chi phí “Clothing” trong các giao dịch của tôi.
Công thức sau cùng sẽ là:
Công thức đó tìm kiếm cột “Expense Type”, đếm số lần mà nó thấy Clothing. Kết quả là 2.
Sử dụng tab có tiêu đề AVERAGEIF trong bảng tính ví dụ miễn phí cho phần này của hướng dẫn.
Số lượng mua hàng trên 100$
=AVERAGEIF(Ô cần kiểm tra, những thứ cần kiểm tra, các ô để tính trung bình thoả điều kiện)
Bây giờ, chúng ta hãy đếm số lượng các giao dịch là 100$ hoặc lớn hơn trong danh sách của tôi.
Đây là công thức mà tôi sẽ sử dụng:
Đây là một công thức đơn giản, công thức gồm hai phần: đơn giản là chỉ định cho Excel danh sách dữ liệu để đếm, và các quy tắc để đếm. Trong trường hợp này, chúng ta đang kiểm tra ô C2 đến C17, đối với tất cả các giá trị lớn hơn 100$.
=AVERAGEIF(B2:B17,"Restaurant",C2:C17)
Cách sử dụng AVERAGEIF trong Excel
Tiếp theo, chúng ta hãy xem cách làm thế nào để sử dụng một công thức AVERAGEIF. Bây giờ, không ngạc nhiên khi AVERAGEIF có thể sử dụng để tính trung bình các giá trị cụ thể, dựa trên một điều kiện mà chúng ta sẽ cung cấp cho Excel.
Các định dạng cho một công thức AVERAGEIF là:
Định dạng công thức AVERAGEIF là gần như tương tự công thức SUMIF.
Hãy sử dụng công thức AVERAGEIF để tính hai số liệu thống kê quan trọng về chi tiêu của tôi:
Tính trung bình chi phí nhà hàng của tôi.
Trung bình của tất cả các chi phí ít hơn $25.
Trung bình chi phí nhà hàng
Để tính trung bình chi phí nhà hàng của tôi, tôi sẽ viết một công thức AVERAGEIF để tính trung bình tất cả số tiền dựa trên các thể loại.
Có ba phần cho công thức này, mỗi phần cách nhau bằng dấu phẩy:
B2:B17 chỉ định các ô để kiểm tra thoả một điều kiện. Vì các loại chi phí được xác định trong cột này.
“Restaurant” cho công thức một cái gì đó để tìm kiếm.
Cuối cùng, C2:C17 là các ô để tính trung bình trong tính toán của chúng ta.
Cuối cùng, Excel tính trung bình chi phí các chuyến đi nhà hàng của tôi. Tôi đã đưa ra công thức đó.
Bạn cũng có thể thử công thức này bằng cách thay thế “Restaurant” với các thể loại khác, như “Clothing.”
Tính trung bình của các chi phí ít hơn 25$
Nếu tôi muốn theo dõi chi phí mua hàng nhỏ hơn của tôi và muốn tính trung bình, tôi có thể viết một AVERAGEIF cho tất cả chi phí ít hơn một số tiền.
Đây là công thức mà tôi sẽ sử dụng để làm điều đó:
Công thức này đơn giản chỉ cần kiểm tra các giá trị trong cột C, và tính trung bình tất cả giá trị lớn hơn 25$.
Tổng kết
Trong hướng dẫn này, bạn đã biết cách làm thế nào để sử dụng ba công thức toán học có điều kiện để xem xét dữ liệu của bạn. Cho dù bạn đang tính tổng, đếm số lượt, hoặc tính trung bình, thì các hàm này cũng nâng cao kỹ năng Excel mà bạn có thể đưa vào sử dụng.
Đối với tất cả các công thức “IF” trong hướng dẫn này, quan trọng là bạn có thể áp dụng các điều kiện để bạn tính toán trong Excel.
Ngoài COUNTIF, SUMIF, và AVERAGEIF, còn có những câu lệnh “IF” chung có thể được sử dụng cho các điều kiện khác. Hãy theo dõi hướng dẫn của chúng tôi về Cách sử dụng các câu lệnh IF đơn giản.
Học cách sử dụng Ngày và Giờ trong Excel kết hợp với các công thức này để làm việc với các giá trị dựa trên ngày tháng.
Hàm Excel VLOOKUP có thể được sử dụng để tìm các giá trị phù hợp từ nhiều danh sách. Tìm hiểu thêm về Cách sử dụng hàm VLOOKUP trong hướng dẫn này.
Cách Sử Dụng Hàm Sumif Và Sumifs Trong Excel
Như tên cho thấy công thức SUMIF và SUMIFS được hình thành bằng cách kết hợp các hàm SUM và IF . Trong tiếng Anh đơn giản điều này ngụ ý rằng các chức năng này có thể thêm các mục hoặc các ô đáp ứng các tiêu chí cụ thể.
Hãy hiểu chi tiết hơn:
Bạn biết hàm SUM có khả năng thêm các mục và chức năng SUMIF mở rộng khả năng của hàm SUM bằng cách cho phép bạn chọn SUM chỉ những mục đáp ứng các tiêu chí cụ thể.
Đồng thời chức năng SUMIFS là một chức năng tương đối mới hơn (được giới thiệu với Excel 2007) mở rộng khả năng của một hàm SUMIF bằng cách cung cấp cho bạn cơ sở để chỉ thêm những mục đáp ứng một bộ tiêu chí.
Chức năng SUMIF trong Excel:
Theo Microsoft Excel SUMIF được định nghĩa như là một chức năng “Thêm các ô được chỉ định bởi một điều kiện hoặc tiêu chuẩn nhất định”.
Cú pháp của hàm SUMIF như sau:
=SUMIF(range, criteria [, sum_range])
Ở đây, ‘range’ dùng để chỉ các ô mà bạn muốn được đánh giá bởi ‘criteria’.
‘criteria’ đề cập đến điều kiện xác định mục nào sẽ được thêm vào. ‘criteria’ có thể là một số, biểu thức hoặc một chuỗi văn bản.
‘sum_range’ là một đối số tùy chọn, nó xác định các ô sẽ được thêm vào. Nếu ‘sum_range’ đối số được bỏ qua sau đó SUMIF xử lý ‘range’ như ‘sum_range’.
Làm thế nào để sử dụng SUMIF trong Excel:
Bây giờ chúng ta hãy thử và hiểu làm thế nào để áp dụng một công thức SUMIF.
Chúng ta hãy thử áp dụng một SUMIF để tìm ra giải pháp.
Vài ví dụ về SUMIF:
Chúng ta hãy xem xét một số ví dụ mà có thể sử dụng Chức năng SUMIF:
Công thức này kết quả vào: 129
Ví dụ 2: Trong cùng bảng như trên, viết công thức để tìm tổng số tiền kiếm được trước ngày “01/01/2012”.
Ví dụ 3: Trong ví dụ này, chúng tôi có bảng biểu diễn của một số trường học tại các sự kiện địa phương. Ở đây, nhiệm vụ của chúng tôi là tìm tổng số giải thưởng do Trường C giành được trong tất cả các sự kiện.
Công thức này kết quả là 24 là D5 + D7 + D9.
Hàm SUMIFS trong Excel:
Trong các ví dụ trên chúng ta đã thấy làm thế nào để sử dụng chức năng SUMIF, bây giờ chúng ta hãy di chuyển đến chức năng SUMIFS. Như tôi đã nói trước rằng SUMIFS là một phần mở rộng của hàm SUMIF, nó có khả năng thêm các mục từ một phạm vi đáp ứng được nhiều điều kiện.
Theo Microsoft Excel SUMIFS có thể được định nghĩa như là một chức năng “Thêm các ô được chỉ định bởi một bộ điều kiện hoặc tiêu chuẩn nhất định”.
Cú pháp của hàm SUMIFS như sau:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Ở đây, ‘sum_range’ xác định các ô cần được thêm vào dựa trên các điều kiện nhất định. Đó là một trường bắt buộc.
‘criteria_range1’ xác định phạm vi đầu tiên mà các tiêu chí đầu tiên sẽ được đánh giá.
‘ criteria1’ chỉ rõ điều kiện cần được đánh giá trong ‘ criteria_range1’.
‘ criteria_range2’, ‘criteria2’ chỉ định các phạm vi khác và các điều kiện tương ứng. Đó là một đối số tùy chọn. SUMIFS hỗ trợ tổng cộng 127 tiêu chuẩn phạm vi.
Làm thế nào để sử dụng SUMIFS trong Excel:
Để hiểu cách sử dụng hàm SUMIFS, hãy lấy ví dụ:
Vì vậy, chúng tôi sẽ cố gắng áp dụng các chức năng SUMIFS như:
Vài ví dụ về các mục tiêu:
Ví dụ 1: Trong ví dụ này chúng ta lại có một bảng bán hàng, và nhiệm vụ là tìm tổng số đơn vị bán hàng của người bán hàng “Steve” vào năm 2012.
Công thức này cho kết quả 907.
Ví dụ 2: Trong ví dụ này, chúng tôi sẽ cố gắng tìm tổng số tiền được tăng lên từ khu vực “West”, nơi bán các mặt hàng lớn hơn 500.
Điều này cho 38 như là kết quả mà là một khoản B4 và D4.
Vài điểm quan trọng về SUMIF và SUMIFS Công thức:
Cả hai công thức SUMIF và SUMIFS đều hỗ trợ các ký tự đại diện. Bạn có thể sử dụng ký tự đại diện (như: ‘*’ và ‘?’) Trong “criteria”.
Trong SUMIF các ô trong ‘ range’ đối số và ‘sum_range’ không nhất thiết phải có cùng kích thước và hình dạng. Nhưng điều này không đúng trong trường hợp của SUMIFS.
Cách Sử Dụng Hàm Sumif Trong Excel
Trong ví dụ này, tham số thứ ba là C2: C6. Đối với mọi giá trị trong A2: A6 khớp với D2, giá trị tương ứng trong C2: C6 sẽ được tổng hợp.
Bạn cũng có thể sử dụng dải ô được đặt tên trong hàm SUMIF. Phạm vi được đặt tên là tên mô tả cho tập hợp các ô hoặc dải ô trong trang tính. Nếu bạn không chắc chắn về cách thiết lập phạm vi được đặt tên trong bảng tính của mình, hãy đọc hướng dẫn của chúng tôi về Thêm phạm vi được đặt tên .
Điều này sẽ cho phép chúng tôi thay thế A2: A6 làm tham số đầu tiên với phạm vi được đặt tên là năm , như sau:
=SUMIF(A2:A6, D2, C2:C6) ‘First parameter uses a standard range
=SUMIF(years, D2, C2:C6) ‘First parameter uses a named range called years
Câu hỏi: Tôi có câu hỏi về cách viết công thức sau trong Excel.
Tôi có một vài tế bào, nhưng tôi chỉ cần tổng của tất cả các tế bào âm tính. Vì vậy, nếu tôi có 8 giá trị, A1 đến A8 và chỉ A1, A4 và A6 là số âm thì tôi muốn B1 là tổng (A1, A4, A6).
Trả lời: Bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị âm như bạn đã mô tả ở trên. Ví dụ:
Công thức này sẽ chỉ tổng hợp các giá trị trong các ô A1: A8 trong đó giá trị là âm (ví dụ: <0).
Câu hỏi: Trong Microsoft Excel, tôi đang cố gắng đạt được những điều sau đây với hàm IF:
Nếu một giá trị trong bất kỳ ô nào trong cột F là “thực phẩm” thì hãy cộng giá trị của ô tương ứng trong cột G (ví dụ ô tương ứng cho F3 là G3). Hàm IF được thực hiện trong một ô khác hoàn toàn. Tôi có thể làm điều đó cho một cặp tế bào nhưng tôi không biết làm thế nào để làm điều đó cho toàn bộ một cột. Bạn có thể giúp không?
Tại thời điểm này, tôi đã có điều này:
Trả lời: Công thức này có thể được tạo bằng cách sử dụng công thức SUMIF thay vì sử dụng hàm IF :
=SUMIF(F1:F10,”=food”,G1:G10)
Điều này sẽ đánh giá 10 hàng dữ liệu đầu tiên trong bảng tính của bạn. Bạn có thể cần phải điều chỉnh phạm vi cho phù hợp.
Tôi nhận thấy rằng bạn tách các tham số của mình bằng dấu chấm phẩy, vì vậy bạn có thể cần phải thay thế dấu phẩy trong công thức trên bằng dấu chấm phẩy.
Sử Dụng Hàm Thống Kê Nâng Cao Sumif,Dsum,Countif,Dcount, Dmax, Dmin Trong Excel
Hướng dẫn sử dụng hàm toán học và thống kê nâng cao: Tính tổng có một điều kiện dùng hàm Sumif, tính tổng nhiều điều kiện dùng hàm Dsum, hàm Countif dùng để đếm có một điều kiện, hàm Dcount đếm có nhiều điều kiện, hàm DMax, DMin tìm giá trị lớn nhất và nhỏ nhất có điều kiện trong Excel.
DSUM – 1 trong những hàm nằm trong nhóm hàm cơ sở dữ liệu trong Excel
Mô tả: Hàm thực hiện tính tổng các giá trị trong 1 trường hay 1 cột của 1 cơ sở dữ liệu với điều kiện cho trước trong Excel.
Cú pháp: DSUM(database, field, criteria).
– database: Là danh sách hay cơ sở dữ liệu, với các hàng là thông tin các bản ghi, các cột dữ liệu là các trường và hàng đầu tiên của danh sách phải chứa tiêu đề của cột.
– field: Tên trường (cột) dữ liệu được dùng trong hàm, có thể nhập tên cột, vị trí cột hoặc thứ tự cột trong bảng dữ liệu.
– criteria: Là phạm vi ô chứa điều kiện xác định. Phạm vi ô phải chứa tiêu đề cột và chứa ít nhất 1 giá trị nằm trong tiêu đề cột.
– Có thể dùng bất kì phạm vi nào cho đối số criteria sao cho nó có chứa tiêu để cột và ít nhất 1 giá trị nằm trong tiêu đề cột.
– Để đảm bảo thao tác trên toàn dữ liệu hãy nhập 1 dòng trống bên dưới tiêu đề cột trong phạm vi điều kiện.
– Không nên để phạm vi điều kiện chồng lấp lên danh sách.
1, Tính tổng tiền của hãng điện thoại Sam Sung đã bán được:
Đặt điều kiện trường Tên có giá trị bằng ” Sam Sung “.
Bước 1: Tại ô cần tính nhập công thức: =DSUM(B9:G15,G9,E5:E6).
Mô tả công thức: Hàm thực hiện thao tác trong vùng dữ liệu B9:G15, tính tổng các giá trị trong cột G9 sao cho các giá trị trong cột G9 tương ứng với điều kiện có tên điện thoại là Sam Sung tương ứng vùng điều kiện E5:E6.
2, Tính tổng tiền bán được của những điện thoại có đơn giá lớn hơn 2,000,000.
Bạn đang đọc nội dung bài viết Cách Sử Dụng Countif, Sumif, Và Averageif Trong Excel trên website Beiqthatgioi.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!