Cách Đếm Không Trùng Trong Excel, Hàm Đếm Nhưng Loại Bỏ Đếm Trùng

Cách Đếm Không Trùng Trong Excel, Hàm Đếm Nhưng Loại Bỏ Đếm Trùng

Excel cơ bản dtnguyen những hàm cơ bản trong excel, những hàm excel cơ bản, cách sử dụng những hàm cơ bản trong excel 2010, Hàm trong excel, Học excel trực tuyến, hướng dẫn học excel cơ bản
Nội dung chính
1 Cách đếm những giá trị duy nhất trong Excel2 Cách hoạt động giải trí của công thức giá trị độc lạ của Excel2. 6 Đếm những hàng duy nhất và độc lạ trong Excel

Trong hướng dẫn này, lingocard.vn Học Excel Online sẽ hướng dẫn các bạn cách đếm các giá trị duy nhất trong Excel bằng công thức và cách đếm tự động các giá trị khác biệt trong bảng. Ngoài ra, Học Excel Online cũng sẽ đề cập đến một số công thức ví dụ để đếm các tên, văn bản, số, giá trị phân biệt chữ hoa và chữ thường, ….

Đang xem : đếm không trùng trong excel
Khi thao tác với một tập dữ liệu lớn trong Excel, bạn hoàn toàn có thể sẽ cần phải biết có bao nhiêu giá trị trùng lặp và duy nhất ở đó. Và đôi lúc, bạn hoàn toàn có thể muốn chỉ đếm riêng những giá trị riêng không liên quan gì đến nhau ( khác nhau ) .

Trước tiên hãy nhắc lại một số định nghĩa cần biết

Giá trị duy nhất – đây là những giá trị Open trong list duy nhất một lần. Giá trị độc lạ – tổng thể những giá trị khác nhau trong list, nghĩa là giá trị duy nhất cộng với lần Open lần tiên phong đại diện thay mặt cho những giá trị trùng lặp. Ảnh chụp màn hình hiển thị sau đây biểu lộ sự độc lạ :

*

*

Và giờ đây, hãy xem cách bạn hoàn toàn có thể đếm những giá trị duy nhất và độc lạ trong Excel sử dụng những công thức .
Tổng hợp kiến thức và kỹ năng về Excel cơ bản

Cách đếm các giá trị duy nhất trong Excel

Dưới đây là một trách nhiệm phổ cập mà toàn bộ người dùng Excel chắc như đinh đều phải thực thi. Bạn có một list những tài liệu và bạn cần phải tìm ra số lượng những giá trị duy nhất trong list đó. Làm thế nào để bạn làm điều đó ? Bạn sẽ tìm ra một vài công thức để đếm những giá trị duy nhất trong số những công thức dưới đây :

Đếm các giá trị duy nhất trong một cột

Giả sử bạn có một cột tên trong bảng tính Excel của mình và bạn cần đếm những tên duy nhất trong cột đó. Giải pháp là sử dụng hàm SUM phối hợp với IF và COUNTIF :
= SUM ( IF ( COUNTIF ( range, range ) = 1,1,0 ) ) Chú thích. Đây là một công thức mảng, vì thế hãy chắc như đinh nhấn Ctrl + Shift + Enter để hoàn tất công thức. Một khi bạn làm điều đó, Excel sẽ tự động hóa đóng khung công thức trong { dấu ngoặc nhọn } như trong ảnh chụp màn hình hiển thị bên dưới .
Trong ví dụ này, tất cả chúng ta đang đếm tên duy nhất trong khoanh vùng phạm vi A2 : A10, vì thế công thức của tất cả chúng ta có dạng sau :
= SUM ( IF ( COUNTIF ( A2 : A10, A2 : A10 ) = 1,1,0 ) )
*
Ngoài ra, trong bài viết này, Học Excel Online cũng đưa đến cho những bạn một số ít công thức lan rộng ra dựa trên công thức giá trị duy nhất của Excel để vận dụng vào nhiều trường hợp khác nhau. Do đó, bạn cần hiểu không thiếu về phương pháp hoạt động giải trí cũng như sự biến hóa giữa công thức gốc và công thức lan rộng ra .
Cách hoạt động giải trí của công thức đếm những giá trị duy nhất trong Excel
Như bạn thấy, 3 công dụng khác nhau được sử dụng trong công thức tìm giá trị duy nhất là – SUM, IF và COUNTIF. Nhìn một cách đơn cử ta có :
Hàm COUNTIF đếm số lần mỗi giá trị Open trong khoanh vùng phạm vi được chỉ định. Trong ví dụ này, COUNTIF ( A2 : A10, A2 : A10 ) trả về mảng { 1 ; 2 ; 2 ; 1 ; 2 ; 2 ; 2 ; 1 ; 2 } .
Hàm IF nhìn nhận mỗi giá trị trong mảng trả về bởi COUNTIF, giữ lại cả những giá trị Open lần đầu đại diện thay mặt cho những giá trị trùng lặp, và sửa chữa thay thế tổng thể những giá trị khác bằng số không. Vì vậy, hàm IF ( COUNTIF ( A2 : A10, A2 : A10 ) = 1,1,0 ) trở thành IF ( 1 ; 2 ; 2 ; 1 ; 2 ; 2 ; 2 ; 1 ; 2 ) = 1,1,0, Trong đó 1 là một giá trị duy nhất và 0 là một giá trị trùng lặp .
Cuối cùng, hàm SUM thêm những giá trị trong mảng trả về bởi IF và cho ra tổng số những giá trị duy nhất, đó là đúng mực những gì tất cả chúng ta muốn. Mẹo. Để xem một phần đơn cử trong công thức giá trị duy nhất của Excel, bạn hãy chọn phần đó trong thanh công thức và nhấn phím F9 .

Đếm các giá trị duy nhất dạng văn bản trong Excel

Nếu list Excel chứa cả số và giá trị văn bản, và bạn chỉ muốn đếm những giá trị văn bản duy nhất, bạn cần thêm hàm ISTEXT vào công thức mảng được bàn luận ở trên :
= SUM ( IF ( ISTEXT ( A2 : A10 ) * COUNTIF ( A2 : A10, A2 : A10 ) = 1,1,0 ) )
Như bạn đã biết, hàm Excel ISTEXT trả về TRUE nếu một giá trị được nhìn nhận là văn bản, FALSE nếu không phải văn bản. Dấu sao ( * ) hoạt động giải trí như toán tử AND trong những công thức mảng, hàm IF trả về 1 chỉ khi một giá trị có cả văn bản và giá trị đó là duy nhất, 0 nếu không thỏa mãn nhu cầu điều kiện kèm theo. Và sau khi công dụng SUM lọc thêm cả 1 lần Open đại diện thay mặt cho những giá trị trùng lặp, bạn sẽ nhận được một số ít giá trị văn bản duy nhất trong khoanh vùng phạm vi định sẵn .
Đừng quên nhấn Ctrl + Shift + Enter để nhập đúng công thức mảng và bạn sẽ nhận được một hiệu quả tựa như như sau :
*
Như bạn thấy trong ảnh chụp màn hình hiển thị ở trên, công thức trả về tổng số giá trị văn bản duy nhất, trừ ô trống, số, giá trị logic của TRUE và FALSE, và lỗi .
Xem thêm : Mẹo Sửa Lỗi Phép Nhân Trong Excel Và Cách Sửa, Cách Sửa Lỗi # Value !

Đếm các giá trị số duy nhất trong Excel

Để đếm những giá trị số duy nhất trong một list tài liệu, hãy sử dụng công thức mảng như tất cả chúng ta đã sử dụng để đếm những giá trị văn bản duy nhất với sự độc lạ duy nhất là bạn sử dụng ISNUMBER thay vì ISTEXT trong công thức giá trị duy nhất :
= SUM ( IF ( ISNUMBER ( A2 : A10 ) * COUNTIF ( A2 : A10, A2 : A10 ) = 1,1,0 ) )
*

Đếm các giá trị duy nhất phân biệt chữ hoa chữ thường trong Excel

Nếu bảng của bạn có chứa tài liệu phân biệt chữ hoa chữ thường, cách đơn thuần nhất để đếm những giá trị duy nhất là tạo ra một cột trợ giúp với công thức mảng sau để xác lập những mục trùng lặp và duy nhất :
= IF ( SUM ( ( – EXACT ( $ A $ 2 : USD A $ 10, A2 ) ) ) = 1, ” Unique ”, ” Dupe ” )
Và sau đó, sử dụng một hàm COUNTIF đơn thuần để tính những giá trị duy nhất :
= COUNTIF ( B2 : B10, “ unique ” )
*

Đếm các giá trị khác biệt trong Excel (giá trị duy nhất và 1 xuất hiện đại diện cho các giá trị trùng lặp)

Để có được một số ít những giá trị độc lạ trong một list, ta sử dụng công thức sau :
= SUM ( 1 / COUNTIF ( range, range ) ) Hãy nhớ rằng, đó là một công thức mảng, và do đó bạn nên nhấn phím tắt Ctrl + Shift + Enter thay vì phím Enter như thường thì .

Ngoài ra, bạn có thể sử dụng chức năng SUMPRODUCT và hoàn thành công thức theo cách thông thường bằng cách nhấn phím Enter:

= SUMPRODUCT ( 1 / COUNTIF ( range, range ) ) Ví dụ : để đếm những giá trị độc lạ trong khoanh vùng phạm vi A2 : A10, bạn hoàn toàn có thể chọn :
= SUM ( 1 / COUNTIF ( A2 : A10, A2 : A10 ) )
Hoặc
= SUMPRODUCT ( 1 / COUNTIF ( A2 : A10, A2 : A10 ) )
*

Cách hoạt động của công thức giá trị khác biệt của Excel

Như bạn đã biết, tất cả chúng ta sử dụng hàm COUNTIF để tìm ra số lần mỗi giá trị Open trong khoanh vùng phạm vi được chỉ định. Trong ví dụ trên, tác dụng của hàm COUNTIF là mảng sau : { 2 ; 2 ; 3 ; 1 ; 2 ; 2 ; 3 ; 1 ; 3 } .
Sau đó, một số ít hoạt động giải trí phân loại được triển khai, trong đó mỗi giá trị của mảng được sử dụng như một số ít chia với 1 là cổ tức. Điều này sẽ biến tổng thể những giá trị trùng lặp thành những số phân số tương ứng với số lần Open trùng lặp. Ví dụ, nếu một giá trị Open 2 lần trong list, nó tạo ra 2 mục trong mảng với một giá trị là 0.5 ( 50% = 0.5 ). Và nếu giá trị Open 3 lần, nó sẽ tạo ra 3 mục trong mảng với giá trị là 0.3 ( 3 ). Trong ví dụ của tất cả chúng ta, hiệu quả của 1 / COUNTIF ( A2 : A10, A2 : A10 ) ) là mảng { 0.5 ; 0.5 ; 0.3 ( 3 ) ; 1 ; 0.5 ; 0.5 ; 0.3 ( 3 ) ; 1 ; 0.3 ( 3 ) } .
Khi một trong những hàm này cộng thêm những giá trị trong mảng, tổng của tổng thể những số phân số cho mỗi mục riêng không liên quan gì đến nhau luôn là 1, mặc dầu có bao nhiêu lần Open của mục đó sống sót trong list. Và chính bới toàn bộ những giá trị duy nhất Open trong mảng là lần đại diện thay mặt duy nhất ( 1/1 = 1 ), tác dụng sau cuối được trả về bởi công thức là tổng số những giá trị khác nhau trong list

Công thức đếm các giá trị khác biệt theo những cách khác nhau

Cũng như trường hợp đếm những giá trị duy nhất trong Excel, bạn hoàn toàn có thể sử dụng những biến thể của công thức đếm số Excel cơ bản để giải quyết và xử lý những loại giá trị đơn cử như số, văn bản và những giá trị phân biệt chữ hoa và chữ thường .
Hãy nhớ rằng tổng thể những công thức dưới đây là những công thức mảng và nhu yếu nhấn Ctrl + Shift + Enter khi hoàn tất công thức .

Đếm các giá trị khác biệt bỏ qua ô trống

Nếu một cột mà bạn muốn đếm những giá trị độc lạ có chứa những ô trống, bạn nên thêm một hàm IF để kiểm tra khoanh vùng phạm vi lao lý cho khoảng trống ( công thức Excel cơ bản được đàm đạo ở trên sẽ trả về lỗi # DIV / 0 trong trường hợp này ) :
= SUM ( IF ( range ” ”, 1 / COUNTIF ( range, range ), 0 ) ) Ví dụ : đếm những giá trị độc lạ trong dải A2 : A10, sử dụng công thức mảng sau :
= SUM ( IF ( A2 : A10 ” ”, 1 / COUNTIF ( A2 : A10, A2 : A10 ), 0 ) )
*

Công thức đếm các giá trị văn bản khác biệt

Để đếm những giá trị văn bản độc lạ trong một cột, tất cả chúng ta sẽ sử dụng chiêu thức tương tự như mà đã sử dụng để loại trừ những ô trống .
Như bạn hoàn toàn có thể thuận tiện đoán được, tất cả chúng ta chỉ đơn thuần là thêm hàm ISTEXT vào công thức giá trị độc lạ trong Excel :
= SUM ( IF ( ISTEXT ( range ), 1 / COUNTIF ( range, range ), ” ” ) ) Và đây là một ví dụ về công thức trong thực tiễn :
= SUM ( IF ( ISTEXT ( A2 : A10 ), 1 / COUNTIF ( A2 : A10, A2 : A10 ), ” ” ) )

Công thức đếm các giá trị số khác biệt

Để đếm những giá trị số độc lạ ( số, ngày tháng và thời hạn ), ta sử dụng hàm ISNUMBER :
= SUM ( IF ( ISNUMBER ( range ), 1 / COUNTIF ( range, range ), ” ” ) ) Ví dụ, đếm tổng thể những số khác nhau trong khoanh vùng phạm vi A2 : A10, sử dụng công thức sau :
= SUM ( IF ( ISNUMBER ( A2 : A10 ), 1 / COUNTIF ( A2 : A10, A2 : A10 ), ” ” ) )

Đếm các giá trị khác biệt phân biệt chữ hoa chữ thường trong Excel

Tương tự như đếm những giá trị duy nhất có phân biệt chữ hoa chữ thường, cách đơn thuần nhất để đếm những giá trị độc lạ phân biệt chữ hoa chữ thường là thêm cột trợ giúp bằng công thức mảng xác lập những giá trị duy nhất kể cả lần Open trùng lặp tiên phong. Công thức này về cơ bản giống như công thức mà tất cả chúng ta đã sử dụng để tính những giá trị duy nhất phân biệt chữ hoa chữ thường, với một đổi khác nhỏ trong một tham chiếu ô nhưng lại tạo ra sự độc lạ lớn :
= IF ( SUM ( ( – EXACT ( $ A $ 2 : USD A2, $ A2 ) ) ) = 1, ” Distinct ”, ” ” )
Tất nhiên, vẫn phải nhớ rằng tổng thể những công thức mảng trong Excel nhu yếu nhấn Ctrl + Shift + Enter .
Sau khi công thức trên kết thúc, hãy viết một công thức COUNTIF thường thì như = COUNTIF ( B2 : B10, “ distinct ” ) để đếm những giá trị độc lạ :
*
Nếu bạn không hề thêm một cột trợ giúp vào bảng tính thì hoàn toàn có thể sử dụng công thức mảng phức tạp sau để đếm những giá trị độc lạ phân biệt chữ hoa chữ thường mà không cần tạo cột bổ trợ :
= SUM ( IFERROR ( 1 / IF ( $ A $ 2 : USD A $ 10 ” ”, FREQUENCY ( IF ( EXACT ( $ A $ 2 : USD A $ 10, TRANSPOSE ( $ A $ 2 : USD A $ 10 ) ), MATCH ( ROW ( $ A $ 2 : USD A $ 10 ), ROW ( $ A $ 2 : USD A $ 10 ) ), “ ” ), MATCH ( ROW ( $ A $ 2 : USD A $ 10 ), ROW ( $ A $ 2 : USD A $ 10 ) ) ), 0 ), 0 ) )

Đếm các hàng duy nhất và khác biệt trong Excel

Đếm những hàng duy nhất / độc lạ trong Excel giống như việc đếm những giá trị duy nhất và riêng không liên quan gì đến nhau, với sự độc lạ duy nhất là bạn sử dụng hàm COUNTIFS thay vì COUNTIF, hàm này được cho phép bạn chỉ định một vài cột để kiểm tra những giá trị duy nhất .
Ví dụ : để đếm những tên duy nhất hoặc độc lạ dựa trên những giá trị trong cột A ( First Name ) và B ( Last Name ), hãy sử dụng một trong những công thức sau :
Công thức đếm những hàng duy nhất
= SUM ( IF ( COUNTIFS ( A2 : A10, A2 : A10, B2 : B10, B2 : B10 ) = 1,1,0 ) )
Công thức đếm những hàng độc lạ
= SUM ( 1 / COUNTIFS ( A2 : A10, A2 : A10, B2 : B10, B2 : B10 ) )
*
Đương nhiên, không phải khi đếm những hàng duy nhất bạn chỉ hoàn toàn có thể đếm trong số lượng giới hạn 2 cột như ở trong ví dụ, hàm COUNTIFS hoàn toàn có thể giải quyết và xử lý 127 khoanh vùng phạm vi / cặp tiêu chuẩn. Trong bài viết này, Học Excel Online đã trình làng cho những bạn cách đếm những giá trị duy nhất và độc lạ trong Excel. Hy vọng bài viết hoàn toàn có thể đem lại cho những bạn cái nhìn tổng quan cũng như những công thức có ích cho việc làm của bạn. Cảm ơn những bạn đã theo dõi bài viết này .

Xem thêm: Vở Bài Tập Toán Lớp 3 Tập 2 Trang 52, Bài 4 Trang 52 Vbt Toán 3 Tập 2: Viết (Theo Mẫu)

Để hoàn toàn có thể biết thêm nhiều hàm nâng cao trong Excel cũng như sử dụng tốt hơn những công cụ của Excel, những bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới nâng cao của Học Excel Online. Đây là khóa học cụ thể và khá đầy đủ nhất, giúp bạn tăng trưởng kỹ năng và kiến thức một cách khá đầy đủ, có mạng lưới hệ thống. Đặc biệt khóa học này không số lượng giới hạn thời hạn học tập, nên bạn hoàn toàn có thể học tự do và xem lại bất kể khi nào .
Hiện nay mạng lưới hệ thống đang có nhiều khuyến mại khi bạn ĐK khóa học này. Chi tiết xem tại :

Xem thêm bài viết thuộc chuyên mục: Excel

Điều hướng bài viết

Source: https://sgn888.net
Category: Thủ Thuật

Bài viết liên quan