Kiến thức về hàm đếm Excel giá trị khác biệt và duy nhất trong Excel

Hàm đếm là một loại hàm cơ bản nhưng cực kỳ quan trọng trong Microsoft Excel. Nhờ ứng dụng của nó, công việc kế toán, kiểm toán hay thống kê dữ liệu trong doanh nghiệp trở nên dễ dàng và chính xác hơn…

Trong bài viết này, 123job.vn sẽ đem tới cho bạn những thông tin bổ ích về hàm đếm là gì và những ứng dụng thực tế của nó. Hơn nữa, hàm đếm có điều kiện cùng với những cách kết hợp của nó với hàm Excel khác như hàm SUM, hàm AND… cũng sẽ lần lượt được giới thiệu. Để thành thạo lĩnh vực tin học văn phòng, bạn không thể không biết những thủ thuật quan trọng đó. 

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

1. Đế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 Microsoft Excel của mình và bạn cần dùng hàm đếm để đếm các tên duy nhất trong cột đó. Giải pháp là sử dụng hàm SUM kết hợp vớihàm IF và hàm đếm COUNTIF:

=SUM(IF(COUNTIF(range, range)=1,1,0))

Chú thích: Đây là một công thức hàm đếm có điều kiện kiểu mảng trong Excel, vì vậy hãy chắc chắn bạn đã nhấn Ctrl + Shift + Enter để hoàn tất công thức. Một khi bạn làm điều đó, Microsoft Excel sẽ tự động đóng khung công thức trong {dấu ngoặc nhọn} như trong ảnh chụp màn hình của chúng ta bên dưới.

Trong ví dụ này, chúng ta đang thực hiện dùng hàm đếm để đếm tên duy nhất trong phạm vi A2: A10, vì vậy công thức hàm đếm có điều kiện của chúng ta có dạng sau:

=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))

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

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

Nếu danh sách trong Microsoft Excel chứa cả số và giá trị văn bản, và bạn chỉ muốn dùng hàm đếm để đếm các giá trị văn bản duy nhất, bạn cần thêm hàm ISTEXT để bổ trợ vào công thức mảng được thảo 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 sẽ lần lượt trả về giá trị TRUE nếu một giá trị được đánh giá là văn bản, giá trị FALSE nếu dữ liệu cho trước không phải văn bản. Dấu sao (*) trong công thức này hoạt động như toán tử AND trong các công thức mảng, hàm IF Excel sẽ trả về 1 chỉ khi một giá trị có cả văn bản và giá trị đó là duy nhất, hoặc trả về 0 nếu không thỏa mãn điều kiện. Và sau khi chức năng của hàm SUM lọc thêm cả 1 lần xuất hiện đại diện cho những giá trị trùng lặp, bạn sẽ lập tức nhận được một số giá trị văn bản duy nhất trong phạm vi đã được định sẵn.

Đừng quên nhấn Ctrl + Shift + Enter để thực hiện nhập đúng công thức mảng và bạn sẽ nhận được một kết quả chính xác tương tự như sau:

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

Như bạn thấy trong ảnh chụp màn hình ở trên, công thức hàm đếm có điều kiện sẽ trả về tổng số giá trị văn bản duy nhất, trừ các phần ô trống, số, giá trị logic của TRUE và FALSE, và lỗi.

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

Để sử dụng hàm đếm để đếm các giá trị số duy nhất trong một danh sách dữ liệu, hãy sử dụng công thức hàm đếm theo mảng như chúng ta đã sử dụng để thực hiện đếm các giá trị văn bản duy nhất với sự khác biệt duy nhất là bạn sử dụng hàm ISNUMBER thay vì sử dụng hàm ISTEXT Excel 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ị số duy nhất trong Excel

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

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

Và sau đó, bạn có thể sử dụng mộthàm đếm COUNTIF trong Excel đơn giản để có thể tính các giá trị duy nhất:

=COUNTIF(B2:B10, “unique”)

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

5. Đếm các giá trị khác biệt trong Excel bằng hàm đếm (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ố các giá trị khác biệt được trình bày trong một danh sách, ta sử dụng công thức hàm đếm như sau:
=SUM(1/COUNTIF(range, range))

Hãy nhớ rằng, đó là một công thức hàm đếm mảng, và do đó bạn nên nhấn phím tắt Ctrl + Shift + Enter thay vì phím Enter như những thao tác thông thường.

Ngoài ra, bạn có thể sử dụng chức năng của hàm đếm, tính tổng SUMPRODUCT và hoàn thành công thức theo cách thông thường trong Microsoft Excel bằng cách nhấn phím Enter:

=SUMPRODUCT(1/COUNTIF(range, range))

Ví dụ: Để sử dụng hàm đếm đếm các giá trị khác biệt trong phạm vi A2: A10, bạn có thể lựa chọn cách sau:

=SUM(1/COUNTIF(A2:A10,A2:A10))

Hoặc

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

Đếm các giá trị khác biệt trong Excel bằng hàm đếm

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

1. 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 sử dụng hàm đếm để đếm các giá trị duy nhất trong Excel, bạn có thể sử dụng các biến thể khác nhau của công thức đếm số Excel cơ bản để xử lý các loại giá trị cụ thể ví dụ như số, văn bản và các giá trị thể hiện sự phân biệt chữ hoa và chữ thường.

Hãy nhớ rằng tất cả các công thức được cho dưới đây là các công thức hàm đếm dạng mảng và yêu cầu nhấn vào tổ hợp phím Ctrl + Shift + Enter khi hoàn tất công thức.

2. Đế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 có chứa các giá trị khác biệt có chứa các ô trống, bạn nên thêm một hàm IF trong Excel để thực hiện kiểm tra phạm vi quy định cho khoảng trống (công thức hàm đếm Microsoft Excel cơ bản được thảo luận ở trên sẽ trực tiếp trả về lỗi #DIV/0 trong trường hợp này):

=SUM(IF(range””,1/COUNTIF(range, range), 0))

Ví dụ: Sử dụng hàm đếm để đếm các giá trị khác biệt trong dải A2: A10, chúng ta sử dụng công thức mảng sau:

=SUM(IF(A2:A10””,1/COUNTIF(A2:A10, A2:A10), 0))

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

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

Để thực hiện hàm đếm đếm các giá trị văn bản khác biệt trong một cột, chúng ta sẽ sử dụng phương pháp khá tương tự mà đã sử dụng để thực hiện loại trừ các ô trống.

Như bạn có thể dễ dàng đoán được, chúng ta chỉ đơn giản là thực hiện thêm hàm ISTEXT vào công thức giá trị khác biệt trong Microsoft Excel:

=SUM(IF(ISTEXT(range),1/COUNTIF(range, range),””))

Và đây là một ví dụ khá rõ ràng về công thức thực tế:

=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

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

Để thực hiện đếm các giá trị số khác biệt (số, ngày tháng và thời gian), ta sẽ sử dụng hàm ISNUMBER trong Excel:

=SUM(IF(ISNUMBER(range),1/COUNTIF(range, range),””))

Ví dụ, dùng hàm đếm để đếm tất cả các số khác nhau trong phạm vi A2: A10, chúng ta sẽ sử dụng công thức sau:

=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),””))

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

Tương tự như việc thực hiện đếm các giá trị duy nhất có phân biệt chữ hoa và chữ thường, cách đơn giản nhất để chúng ta đếm các giá trị khác biệt phân biệt chữ hoa chữ thường là trực tiếp thêm cột trợ giúp bằng công thức mảng xác định các giá trị duy nhất kể cả khi những lần xuất hiện trùng lặp đầu tiên. 

Công thức này về cơ bản cũng khá là giống như công thức mà chúng ta đã sử dụng để tính các giá trị duy nhất phân biệt ký tự chữ hoa chữ thường, với một thay đổi nhỏ trong một tham chiếu ô của hàm đếm nhưng lại tạo ra sự khác biệt lớn:

=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)

Tất nhiên, bạn vẫn sẽ phải nhớ rằng tất cả các công thức mảng được tồn tại trong Microsoft Excel yêu cầu nhấn Ctrl + Shift + Enter.

Sau khi công thức được cho trên kết thúc, hãy viết một công thức của hàm đếm có điều kiện COUNTIF thông thường như =COUNTIF(B2:B10, “distinct”) để thực hiện đếm các giá trị khác biệt:

Đếm các giá trị khác biệt

Nếu bạn không thể thực hiện thêm một cột trợ giúp vào bảng tính thì có thể sử dụng công thức hàm đếm mảng phức tạp sau để tiến hành đếm các giá trị khác biệt phân biệt chữ hoa chữ thường mà không cần trực tiếp tạo cột bổ sung:

=SUM(IFERROR(1/IF($A$2:$A$10””, FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), “”), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))

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

6.1. Công thức hàm đếm cho các hàng duy nhất

=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))

6.2. Công thức hàm đếm cho các hàng khác biệt

=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))

Công thức hàm đếm cho các hàng

III. Hướng dẫn cách sử dụng hàm SUMPRODUCT - hàm đếm để đếm theo nhiều điều kiện

1. Nguyên tắc đếm của hàm SUMPRODUCT

Hàm đếm SUMPRODUCT là một hàm xử lý các dữ liệu dạng mảng. Cấu trúc của hàm SUMPRODUCT trong Microsoft Excel đơn giản chỉ là các mảng:

=SUMPRODUCT(array1, [array2], [array3], …)

Nếu chỉ nhìn vào cấu trúc đơn giản trên thì bạn có hình dung được hàm SUMPRODUCT có sức mạnh thế nào không?

Sức mạnh của hàm đếm này nằm ở chữ “”Mảng-Array”, bởi vì:

• Phạm vi đếm của mảng rất linh hoạt, có thể bao gồm nhiều vùng dữ liệu được trực tiếp kết nối lại hoặc một phần nhất định trong mỗi phần tử của 1 vùng dữ liệu cụ thể.
• Việc tính toán theo mảng bằng hàm đếm này gần như không gặp giới hạn gì

Vậy hàm SUMPRODUCT sẽ thực hiện việc đếm dựa trên nguyên tắc cụ thể nào?

Bản chất của việc đếm trong hàm đếm này là mỗi phần tử trong 1 vùng thỏa mãn điều kiện thì sẽ được tính là 1. Kết quả cần đếm của hàm đếm là tổng của các giá trị thỏa mãn điều kiện đó. Như vậy thì trong thao tác đếm theo điều kiện có thể hiểu gần giống như việc tính tổng các giá trị thỏa mãn điều kiện, chỉ khác là mỗi giá trị thỏa mãn này sẽ hoàn toàn không phụ thuộc một giá trị nào mà chỉ có giá trị cụ thể là 1.

2. Ứng dụng hàm SUMPRODUCT để thực hiện đếm dữ liệu theo nhiều điều kiện 

Dưới đây là một số ví dụ cực kỳ cụ thể để giúp chúng ta làm quen với việc dùng hàm đếm SUMPRODUCT để đếm:

Ví dụ

Yêu cầu thứ 1: Dựa vào bảng dữ liệu A1:D18 và dùng hàm đếm đếm số mặt hàng Cam bán trong tháng 6.

Trong yêu cầu này chúng ta sẽ có 2 điều kiện đếm:

• Mặt hàng là Cam. Xét trong phạm vi cột Tên hàng với điều kiện “Cam”
• Tháng 6. Xét trong cột Ngày, mỗi phần tử (ô) trong cột ngày chỉ xét giá trị cụ thể trong phần tháng bằng 6

Như vậy đối với mỗi giá trị thỏa mãn cả 2 điều kiện này thì được tính là 1. Kết quả cuối cùng của hàm đếm có điều kiện này là tổng các giá trị thỏa mãn cả 2 điều kiện này.

Xét điều kiện thứ 1 trong hàm đếm:

Theo cách viết thông thường thì chúng ta sẽ viết như sau:

C2=”Cam”

C3=”Cam”

…

C18=”Cam”

Khi thực hiện viết công thức hàm đếm dưới dạng mảng thì chúng ta có thể viết gọn trong hàm đếm là C2:C18=”Cam”. Khi viết như trên thì chúng ta có thể hiểu là xét mỗi giá trị trong vùng C2:C18, ô nào có giá trị tương ứng là Cam.

Xét điều kiện thứ 2 trong hàm đếm:

Để xét dữ liệu tháng của một Ngày thì chúng ta sử dụng hàm MONTH. Để thực hiện xét mỗi giá trị trong cột Ngày có tháng bằng 6 thì chúng ta có thể viết hàm có dạng như sau:

MONTH(A2:A18)=6

Hàm MONTH trong Excel thông thường chỉ xét được trên 1 giá trị. Do đó nếu chúng ta chọn cách viết thông thường thì chỉ viết dạng:

Month(A2)=6

Month(A3)=6

…

Month(A18)=6

Khi thực hiện công thức hàm đếm viết dưới dạng công thức mảng thì có thể viết điều kiện trong đó là Month(A2:A18)=6.

Do hàm đếm Sumproduct là hàm dạng mảng trong Excel nên chúng ta có thể sử dụng trực tiếp cách viết rút gọn này.

Hai điều kiện cụ thể trên là xảy ra đồng thời, nên trong hàm SUMPRODUCT trong Microsoft Excel có thể viết như sau:

=SUMPRODUCT((C2:C18=”Cam”)*(MONTH(A2:A18)=6)*1)

Mỗi điều kiện trong hàm đếm có điều kiện này được viết trong dấu ngoặc đơn, liên kết với nhau bởi dấu *.

Kết quả của các điều kiện sẽ lần lượt trả về TRUE hoặc FALSE chứ không phải bằng 1. Do đó chúng ta phải nhân thêm giá trị 1 để quy kết quả về dạng số tương ứng để có thể tính tổng được.

Kết quả như sau:

Kết quả

(Các vùng tham chiếu cụ thể như A2:A18, C2:C18 có thể được cố định lại bằng phím F4)

Yêu cầu 2: Dùng hàm đếm để đếm số lần bán của ca 1 trong tháng 6 với mặt hàng Táo

Trong yêu cầu cụ thể này có tới 3 điều kiện: Ca1, Tháng 6 và Mặt hàng “Táo”

Khi thực hiện tăng thêm điều kiện thì chúng ta chỉ cần tăng thêm các thành phần cụ thể trong hàm SUMPRODUCT - hàm đếm có điều kiện như sau:

=SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18=”Táo”)*1)

Kết quả:

Kết quả

IV. Kết luận

Trên đây là cách sử dụng hàm đếm với những ví dụ cụ thể để sử dụng hàm và thực hiện kết hợp với các hàm, ký tự đặc biệt khác để tìm giá trị thỏa mãn điều kiện trong cột, mảng, vùng dữ liệu... Chúc bạn sớm thành thạo những thủ thuật này để áp dụng linh hoạt trong công việc của mình nhé!