Trong bài viết này, Gitiho đã hướng dẫn các bạn cách đếm những điều kiện sa thải trong Excel với hàm SUMPRODUCT. Hãy cùng tò mò kiến thức rất thú vị này nhé!
Hàm SUMPRODUCT trong Excel có tác dụng làtrả về tổng của tổng của các dải ô hoặc mảng tương ứng. Chúng ta có thểm vận dụng hàm này nhằm đếm những điều kiện đào thải trong bảng tính Excel theo những phương pháp được khuyên bảo dưới đây:
Kết phù hợp MATCH cùng SUMPRODUCT đếm những điều kiện thải trừ Excel
Chúng mình thuộc xem bảng ví dụ sau đây nhé:
Yêu cầu: Đếm số lượng bút bi là nhiều loại A, và không phải loại A.
Bạn đang xem: Hàm loại trừ trong excel
Thiết lập vùng điều kiện
Với yêu mong này, ta sẽ thiết lập vùng điều kiện như hình:
Đếm điều kiện thỏa mãn
Trước khi lấn sân vào đếm điều kiện loại trừ, ta sẽ thuộc đếm điều kiện thỏa mãn. Để tiến hành, ta sử dụng đơn giản dễ dàng hàm SUMPRODUCT như sau:
=SUMPRODUCT((vùng 1=điều khiếu nại 1)*(vùng 2=điều kiện 2))Cụ thể: =SUMPRODUCT((A2:A15=D2)*(B2:B15=E2))
Đếm đk loại trừ
Với yêu ước trên ta có thể giải theo phương pháp: Đếm những loại B, C, D rồi tính tổng; hoặc đếm tổng nhiều loại rồi trừ đi loại A. Ta hoàn toàn có thể thực hiện giải pháp đếm một số loại trừ bằng cách kết hợp SUMPRODUCT, ISNA và MATCH.
Công thức
Công thức tổng thể trong trường thích hợp này là:
=SUMPRODUCT((vùng 1=điều kiện 1)*ISNA(MATCH(vùng 2, điều kiện 2,0)))
Ý nghĩa
Đầu tiên, với hàm MATCH ta đang tìm ra địa chỉ của từng quý giá trong vùng 2 vừa lòng điều kiện 2:
Kết phù hợp với hàm ISNA để lấy giá trị về TRUE/FALSE:
Cuối thuộc kết hợp với SUMPRODUCT nhằm đếm theo điều kiện loại trừ:
Trong trường hợp này, bạn hoàn toàn hoàn toàn có thể sử dụng bí quyết chỉ với SUMPRODUCT để sửa chữa thay thế như sau:
=SUMPRODUCT((A2:A15=D2)*(B2:B15E2))
Tuy nhiên, đối với nhiều điều kiện sa thải (chẳng hạn số cây viết chì không phải A hoặc B), hàm MATCH đã là một giải pháp hữu ích.
Tuyệt đỉnh Excel: phát triển thành bậc thầy Excel sau 16 giờ
Khóa học bao gồm các bài xích giảng từ cơ phiên bản đến nâng cấp về Excel. Dù nhiều người đang ở trình độ chuyên môn nào thì cũng hoàn toàn có thể tham gia khóa học. Bạn sẽ được rất nhiều các kỹ năng để gia công việc cấp tốc và hiệu quả trên Excel. Bên cạnh phần triết lý thì còn tồn tại các bài bác tập ứng dụng thực tế và phần lớn tài liệu giỏi được tặng kèm kèm vào khóa học. Tham gia khóa học để lắp thêm những kỹ năng hay mang lại lợi ích cho công việc cũng như sự phân phát triển năng lực của phiên bản thân chúng ta nhé.
Excel là công cụ làm việc gần như bắt buộc phải có với kế toán với kiểm toán. Vậy những hàm thường dùng được áp dụng nhiều tốt nhất là gì?
1. HÀM SUM, AVERAGE
♦ Cú pháp: = SUM (số1, số2,…, số hoặc vùng dữ liệu)
Ý nghĩa: Là hàm tính tổng các giá trị.
♦ Cú pháp: = AVERAGE (giá trị 1, giá trị 2, quý giá 3,… quý hiếm n hoặc vùng dữ liệu).
Ý nghĩa: Là hàm trả về trung bình cộng các tham số đưa vào hoặc của một vùng dữ liệu.
2. HÀM TÌM KIẾM (VLOOKUP)
+ V là viết tắt của Vertical có nghĩa là hàng dọc (thẳng đứng).
+ Lookup là hàm tham chiếu, tìm kiếm kiếm
+ VLOOKUP nghĩa là tìm kiếm theo hướng dọc, theo cột.
♦ Cú pháp: = VLOOKUP(x, vùng tham chiếu, cột thứ n,0).
Ý nghĩa: rước một giá bán trị(x) đem so sánh theo cột của vùng tham chiếu để trả về cực hiếm ở cột tương xứng trong vùng tham chiếu (cột sản phẩm công nghệ n) , 0: so sánh tương tối, 1: đối chiếu tuyệt tối.
Chú ý:
– Vùng tham chiếu: cột đầu tiên của tham chiếu phải bao phủ vừa đầy đủ và tổng thể các giá trị dò tìm. Luôn luôn luôn phải kê ở quý hiếm tuyệt đối.
– Cột cần lấy đếm xem nó là cột thứ từng nào trong bảng tham chiếu. Khi đếm phải đếm trường đoản cú trái qua phải.
3. HÀM IF
♦ Cú pháp: = If (Logical_test,,) tức thị If( Điều khiếu nại , quý hiếm 1, giá trị 2).
Ý nghĩa: Là hàm trả về cực hiếm 1 nếu đk đúng, hàm trả về cực hiếm 2 nếu điều kiện sai.
4. HÀM SUMIF
♦ Cú pháp: = SUMIF((range, criteria,sum_range) tức thị Sumif (Vùng cất điều kiện, điều kiện, vùng yêu cầu tính tổng)
Ý nghĩa: Hàm này trả về giá trị tính tổng của các ô vào vùng đề nghị tính thỏa mãn nhu cầu một điều kiện đưa vào.
Chú ý:
– Do giám sát và đo lường trong ô của Excel, buộc phải hàm SUMIF tính tổng này trên những phiên bạn dạng Excel 2016, Excel 2013, hay những phiên phiên bản đời trước như Excel 2010, 2007, 2003 rất nhiều áp dụng cấu tạo hàm như nhau.
5. HÀM and VÀ OR
♦ Cú pháp: = AND((Logical1; ; ;…) tức thị And(đối 1, đối 2,..)
Ý nghĩa: Hàm này là Phép VÀ, chỉ đúng khi toàn bộ các đối số có giá trị đúng. Các đối số là những hằng, những biểu thức logic.
Chú ý:
– Hàm & có buổi tối đa 256 đối số yêu cầu là các giá trị súc tích hay những mảng hoặc tham chiếu chứa các giá trị logic. Toàn bộ các giá chỉ trị sẽ bị bỏ qua nếu một đối số mảng hoặc tham chiếu có chứa văn bản hoặc ô rỗng.
– các đối số đề nghị là giá bán trị logic hoặc mảng xuất xắc tham chiếu tất cả chứa quý giá logic.
– nếu đối số tham chiếu là quý giá text hoặc Null (rỗng) thì những giá trị kia bị bỏ qua.
– ví như vùng tham chiếu không chứa giá trị súc tích thì hàm trả về lỗi #VALUE!
♦ Cú pháp: = OR((Logical1; ; ;…) tức thị Or(đối 1, đối 2,..)
Ý nghĩa: Hàm này là Phép HOẶC, chỉ sai khi tất cả các đối số có giá trị sai.
6. HÀM COUNTIF
♦ Cú pháp: = COUNTIF (range,criteria)
Trong đó:
+ Range: là dãy dữ liệu mà bạn muốn đếm gồm điều kiện.
+ Criteria: là đk để một ô được đếm.
Ý nghĩa: Hàm COUNTIF vào excel dùng để đếm số ô thỏa mãn điều kiện (Criteria) trong phạm vi (Range).
7. HÀM MIN, MAX
♦ Cú pháp: = MAX(number 1, number 2, …)
Ý nghĩa: Trả về giá trị lớn nhất của number1, number 2,.. Hoặc giá trị lớn nhất của cả 1 vùng dữ liệu số.
♦ Cú pháp: = MIN(number 1, number 2, …)
Ý nghĩa: Trả về giá trị bé dại nhất của number1, number 2,.. Hoặc giá chỉ trị nhỏ nhất của tất cả 1 vùng tài liệu số.
8. HÀM LEFT, RIGHT
♦ Cú pháp: = LEFT(chuỗi, ký kết tự hy vọng lấy)
Ý nghĩa: tách bóc lấy phần lớn ký tự phía bên trái chuỗi ký tự.
Ví dụ: = LEFT(“THANH HUE”) => Kết quả: = THANH.
♦ Cú pháp: = RIGHT(chuỗi, cam kết tự hy vọng lấy)
Ý nghĩa: tách lấy mọi ký từ bên đề nghị chuỗi ký kết tự.
Ví dụ: = LEFT(“THANH HUE”) => Kết quả: = HUE.
9. HÀM SUBTOTAL
♦ Cú pháp: = SUBTOTAL ((function_num,ref1,,…)
Trong đó:
+ Function_num: Bắt buộc. Số 1-11 xuất xắc 101-111 hướng dẫn và chỉ định hàm thực hiện cho tổng phụ. 1-11 bao gồm những sản phẩm ẩn bằng phương pháp thủ công, còn 101-111 đào thải chúng ra; các ô được thanh lọc ra sẽ luôn được các loại trừ.
+ Ref1 Bắt buộc. Phạm vi hoặc tham chiếu được lấy tên đầu tiên mà bạn có nhu cầu tính tổng phụ đến nó.
+ Ref2,… Tùy chọn. Phạm vi hoặc chuỗi được lấy tên từ 2 mang đến 254 mà bạn muốn tính tổng phụ mang lại nó.
Chú ý:
– Nếu có hàm SUBTOTAL khác lồng để tại các đối số ref1, ref2,.. Thì các hàm lồng này sẽ bị bỏ qua ko được tính nhằm mục đích tránh trường hợp thống kê giám sát 2 lần.
– Đối số Function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao hàm cả những giá trị vào tập số liệu (hàng ẩn). Đối số Function_num giả dụ từ 101 mang đến 111 thì hàm SUBTOTAL chỉ đo lường cho những giá trị không ẩn trong tập số liệu (bỏ qua những giá trị ẩn 0 ).
– Hàm SUBTOTAL sẽ bỏ qua mất không giám sát tất cả những hàng bị ẩn bởi vì lệnh Filter (Auto Filter) không dựa vào vào đối số Function_num được dùng.
– Hàm SUBTOTAL được thiết kế để giám sát cho những cột số liệu theo chiều dọc, nó không có phong cách thiết kế để tính theo hướng ngang.
Xem thêm: Không Thể Vào Facebook Bằng Google Chrome, Hướng Dẫn Sửa Lỗi Chrome Không Vào Được Facebook
– Hàm này chỉ tính toán cho tài liệu 2-D do thế nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi #VALUE.