Dữ liệu trong Excel rất đa dạng và phong phú và cần phải có nhiều cách xử lý khác biệt để cân xứng với từng loại dữ liệu. Trong nội dung bài viết này chúng ta cùng mày mò về một chủ đề khó nhưng khôn xiết thú vị là: Đếm số quý giá không trùng trong một danh sách. Hãy cùng Học Excel Online tìm hiểu xem khó ở chỗ nào và thú vị ở chỗ nào nhé.
Bạn đang xem: Đếm không trùng có điều kiện
Giả sử chúng ta có 1 bảng dữ liệu như sau:
Một yêu thương cầu đưa ra là đếm xem vào cột nhân viên có từng nào người, cột Ngày có bao nhiêu ngày, cột thương hiệu hàng có bao nhiêu món đồ nhưng ko tính những giá trị trùng nhau. Vậy bọn họ sẽ giải quyết và xử lý ra sao với việc này?
Xem nhanh
Cách máy 1: Thêm cột phụ và cần sử dụng hàm COUNTIF
Đây là biện pháp làm cơ bản, dễ tiến hành và cũng dễ hiểu. Nhược điểm duy nhất của cách này là làm trải qua không ít bước với tăng số công thức đề nghị tính lên nhiều.Ví dụ cùng với cột Ngày, họ sẽ tạo thành 1 cột phụ là cột F và sử dụng hàm COUNTIF trên đó. Hàm COUNTIF không còn xa lạ gì với chúng ta phải không nào. Nếu bạn không nhớ hàm COUNTIF dùng gắng nào thì xem tại trên đây nhé:
Hàm Count
If vào excel, hướng dẫn rõ ràng và gồm ví dụ minh họa
Để riêng biệt giá trị tại chiếc đó mở ra lần đồ vật mấy thì chúng ta dùng hàm COUNTIF như sau:
Thứ 1: Đếm số lần lộ diện tại mỗi loại trong vùng tính trường đoản cú dòng đầu tiên tới cái đang đếm. Điều này rất quan trọng bởi khi tính theo cách này thì quý hiếm COUNTIF đếm được sẽ lần lượt là 1, 2 , 3… tăng cao theo số lần xuất hiện thêm của đk (criteria) sẽ xét. Khác với bài toán chọn Range là cả vùng dữ liệu từ C2:C11, tại chỗ này chỉ xét từ C2 tăng cao theo những dòng. Quý giá điểm đầu C2 vào vùng Range được cầm định.Thứ 2: Sau khi sử dụng hàm COUNTIF chúng ta sẽ ra được công dụng tại cột F trường đoản cú F2:F11. Mỗi 1 giá bán trị hàng đầu thể hiện nay là ngày tương ứng tại cột C xuất hiện thêm lần vật dụng 1, tức là chưa bị trùng. Những giá trị mập hơn 1 là trùng. Khi đó hiệu quả đếm quý giá không trùng chỉ việc dùng hàm COUNTIF vào cột F với đk là số 1
Kết quả thu được là 9
Như vậy khi áp dụng hàm COUNTIF trong cột phụ thì giải quyết và xử lý yêu mong trên khá dễ dàng dàng.
Nhược điểm
Cách này tuy dễ dàng nhưng lại sở hữu nhược điểm là tính thủ công. Bạn phải chọn một cột phụ. Dùng phương pháp tại cột phụ. Dùng phương pháp tính kết quả. Không ít bước nên không nào? hơn thế nữa với bảng dữ liệu lớn thì bài toán này khiến tăng thêm một lượng bí quyết rất lớn, cùng với mỗi mẫu dữ liệu là một công thức.
Vậy phải trong những bảng tài liệu lớn và tài năng Excel của bạn tốt thì tại sao bọn họ không tìm hiểu cách thứ hai nhỉ?
Cách máy 2: công thức mảng SUMPRODUCT kết hợp FREQUENCY
Hẳn bạn thấy FREQUENCY là một trong những hàm khá lạ yêu cầu không. Đây là 1 trong những hàm xử lý tài liệu dạng mảng, dùng để đếm số lần mở ra của những giá trị trong 1 phạm vi làm sao đó. Vào yêu ước của câu hỏi này, còn nếu không sử dụng cột phụ thì họ phải sử dụng những công thức xử lý tài liệu theo mảng nhằm tính.
Nếu bạn chưa chắc chắn về hàm Frequency rất có thể xem tại bài xích viết: Chức năng và cú pháp hàm FREQUENCY trong Excel
Cấu trúc hàm FREQUENCY như sau:
=FREQUENCY(data_array,bins_array)
Nguyên tắc của hàm Frequency là đếm xem những giá trị trong tham số bins_array được tái diễn bao nhiêu lần vào data_array. Về bản chất thì đây y hệt như việc cần sử dụng hàm COUNTIF nhằm đếm từng bộ phận trong bins_array (criteria) vào vùng data_array (range) vậy.
Ứng dụng nguyên tắc trên, họ xét 2 câu chữ sau:
Số thứ tự cái của từng dòng dữ liệu trong cột đề xuất đếm quý hiếm không trùng sẽ lần lượt là từ 1 đến 10 (ứng với những dòng từ C2:C11). Coi vùng này là bins_array. Giả sử xét A2:A11 chính là nội dung chúng ta cần.Đếm xem mỗi giá trị xuất hiện thêm trong cột ngày là giá trị thứ mấy với hàm MATCH. Vùng này được xem là data_array. Hàm MATCH được viết như sau:
Khi kiểm tra hiệu quả hàm MATCH bằng cách bôi đen cách làm trên thanh Formulas Bar cùng nhấn phím F9 ta có:
Khi kết hợp 2 nguyên tố trên vào hàm Frequency ta được:
Việc còn sót lại là đếm số giá chỉ trị to hơn 0 vào mảng tạo thành bởi hàm Frequency là ra kết quả.
Tuy nhiên vì cách làm chỉ đặt trong một ô cần hàm đếm giá chỉ trị lớn hơn 0 phải dùng đến hàm SUMPRODUCT. Biện pháp làm như sau:
Xét các giá trị tạo ra bởi mảng trong hàm Frequency so sánh với 0, lấy các giá trị >0Việc so sánh Frequency>0 sẽ khởi tạo ra các mảng là hiệu quả True/False. Lúc đó chuyển các giá trị True/False về dạng 1/0 bằng phương pháp thêm 2 dấu trừ nghỉ ngơi trước biểu thức so sánh đó
Kết trái cũng ra là 9
Như vậy chỉ việc đặt 1 biểu thức ở một ô C13 họ cũng ra được tác dụng giống như phương pháp thứ 1.
Ưu điểm:
Khắc phục được điểm yếu của phương pháp 1Giúp chúng ta nâng cao hơn tài năng sử dụng Excel và các tư duy về thực hiện hàm, xử lý dữ liệu, loại dữ liệu trên Excel.Ứng dụng trong việc trích xuất dữ liệu không trùng từ bảng dữ liệu bằng phương pháp sử dụng hàmNhược điểm:
Công thức khá phức tạp nên cần hiểu kỹ về thực chất dữ liệu với cách áp dụng công thức mảngNâng cao:
Không phải lúc nào bọn họ cũng áp dụng cột STT vào bảng dữ liệu. Vậy cho nên việc sử dụng thẳng vùng A2:A11 vào tham số bins_array vào hàm Frequency bắt buộc thay đổi bằng cách khác. Theo chúng ta thì chúng ta cũng có thể dùng giải pháp nào? Hãy share với học tập Excel Online cách giải quyết trong trường thích hợp này nhé.
Tham khảo:
Hướng dẫn giải pháp dùng hàm SUMPRODUCT để đếm theo không ít điều kiện
Tổng phù hợp các cách thức loại bỏ dữ liệu lặp
Cách lọc ra một danh sách các giá trị tuyệt nhất và khác hoàn toàn trong Excel
Ngoài ra học Excel Online xin giới thiệu với những bạn Khóa học Excel tự cơ bạn dạng tới nâng cấp dành mang đến người đi làm. Đây là 1 trong những khóa học rất đầy đủ kỹ năng và hỗ trợ rất hay vời cho mình trong việc làm quen với Excel, sử dụng những công cụ, những hàm trong Excel để giúp bạn làm việc trên Excel được xuất sắc hơn, kết quả hơn. Cụ thể xem tại:
anh em góp giùm, mình có yêu mong như sau:cột A1aa1232bbây giờ mong dùng cách làm để đếm số lần mở ra của các đối tượng người tiêu dùng nhưng loại bỏ đếm trùng, ngơi nghỉ đây hiệu quả là 5 (1;a;2;3;b)
ndu96081631Huyền thoại GPE
anh em giúp giùm, mình bao gồm yêu mong như sau:cột A1aa1232bbây giờ ý muốn dùng cách làm để đếm số lần xuất hiện của các đối tượng người tiêu dùng nhưng vứt bỏ đếm trùng, ở đây kết quả là 5 (1;a;2;3;b)
Câu hỏi này sẽ đề cập bên trên diển bọn nhiều lần lắm rồi (tại chúng ta không chịu tìm)Nói lại lần nữa:- trả sử vùng dử liệu của người sử dụng là A1:A8- Vậy dùng công thức này:=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8))
Nguyễn Xuân Sơn
Thành viên hay trực
Nếu có khá nhiều dữ liệu hơn thế thì không chuẩn đâu bạn. Nếu ngại dùng phương pháp thì bạn dùng Fivottable và kết hợp Subtotal thì chuẩn chỉnh nhất
Nếu có rất nhiều dữ liệu hơn thì không chuẩn đâu bạn. Giả dụ ngại dùng bí quyết thì các bạn dùng Fivottable và kết hợp Subtotal thì chuẩn chỉnh nhất
Xin bạn cho 1 trường phù hợp không chuẩn của công thức trên cùng với ---> Tôi thì thấy cách làm trên không tương quan gì mang đến dử liệu các hay ít, chỉ đặc biệt quan trọng phải xem dử liệu gồm cell rổng hay là không mà thôi
Thêm nữa: File lắp kèm của chúng ta đâu đúng yêu cầu của tác giả
Cái tác giả cần là: Đếm xem bao gồm bao nhiêu phần tử không trùng trong một vùng mang đến trước(cái của công ty là COUNTIF)
Xin bạn cho một trường hòa hợp không chuẩn của phương pháp trên với ---> Tôi thì thấy phương pháp trên không liên quan gì cho dử liệu các hay ít, chỉ quan trọng đặc biệt phải xem dử liệu bao gồm cell rổng hay là không mà thôi
Thêm nữa: File gắn kèm của bạn đâu đúng yêu cầu của tác giả
Cái tác giả cần là: Đếm xem tất cả bao nhiêu thành phần không trùng trong một vùng mang đến trước(cái của người sử dụng là COUNTIF)
Mình ko nói nó sai, chỉ là không đúng yêu ước của tác giả:Tác đưa chỉ nên biết trong nguyên 1 đóng góp dử liệu thì bao gồm bao nhiêu bộ phận không trùng
Ví dụ
A1 = a
A2 = 1A3 = 5A4 = a
A5 = 1Thì công dụng mong mong muốn là 3 (tức bao gồm 5 cell nhưng chỉ bao gồm 3 phần tử)
nhưng kì lạ làm sao mà cách làm =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)) lại có thể tính được số tài liệu không đụng hàng nhỉ. Ai hoàn toàn có thể giải say đắm hộ tôi được không ? tôi thấy COUNTIF(A1:A8,A1:A8) có mức giá trị bởi 0 mà
VD: bao gồm dãy số liệu trường đoản cú A1:A6 (Không đếm ô rỗng)
nhưng kì khôi làm sao mà bí quyết =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)) lại có thể tính được số dữ liệu không trùng lặp nhỉ. Ai hoàn toàn có thể giải ham mê hộ tôi được không ? tôi thấy COUNTIF(A1:A8,A1:A8) có mức giá trị bằng 0 mà
Cái này là mà nói bằng lời thì khá khó, lấy 1 lấy ví dụ như với mảng tất cả 3 số 2 đi :=SUMPRODUCT(1/COUNTIF(2;2;2,2;2;2))=SUMPRODUCT(1/3;3;3) (đếm trong mảng 2;2;2 thì số 2 mở ra 3 lần, đếm 3 lần bởi thế thì đạt được mảng 3;3;3 sinh hoạt trên).=SUMPRODUCT(1/3;3;3) = 1/3 + 1/3 + 1/3 = 1 --> Chỉ có 1 giá trị là số 2.Có thể cần sử dụng SUM, lúc ấy phải dứt bằng Ctrl Shift Enter vì đây là công thức mảng, tại chỗ này dùng SUMPRODUCT một thể hơn vì nó vẫn là hàm mảng --> chỉ việc Enter.
Xem thêm: " the flash " dead or alive (tv episode 2017), the flash season 3 episode 21 recap and review
Bạn làm cho thêm vài lấy một ví dụ với mảng có các thành phần khác nhau --> thoải mái và tự nhiên hiểu.
VD: gồm dãy số liệu từ bỏ A1:A6 (Không đếm ô rỗng)
tôi cũng đang khám phá các áp dụng của hàm sumproduct, hơi kinh ngạc chút xíu lúc nó có thể sử dụng đếm và thải trừ các kí tự trùng. Nhờ vào bạn giải thích rõ cho mình cấu trúc này nhé,SUMPRODUCT((A1:A6"")/COUNTIF(A1:A6;A1:A6&"")) nó tương đối lạ . Thanks !!
Hi! Em bao gồm một bảng như thế này1.thảo2.nam3.hoa4.thảo5.nam6....Làm vậy nào tôi có thể tổng phù hợp được các đối tượng người dùng trên vào trong 1 bảng nhưng mỗi thương hiệu chỉ lộ diện một lần.Tôi không thích dùng các hàm thanh lọc điều kiện. Muốn mọi người chỉ giáo.thanks!