Hướng Dẫn Cách Sử Dụng Hàm Countif Để Đếm Số Lần Xuất Hiện Trong Excel

Hàm Anchored COUNTIF là một trong những thủ thuật được yêu thích trong Excel. Việc áp dụng kỹ thuật này mang lại rất nhiều lợi ích đặc biệt, đây là một hàm xứng đáng để học. Nó không giống như các hàm lookup thông thường, hàm Anchored COUNTIF là một công cụ tuyệt vời có thể được sử dụng kết hợp với hàm lookup để nâng cao hiệu suất của bạn khi làm việc với dữ liệu.

Hướng dẫn sử dụng hàm

Trong ví dụ trên,chúng ta có bảng dữ liệu về hoạt động bán hàng gồm hai cột là Sản phẩm đã bán (Product) và Giá bán của sản phẩm (Sales Price). Bởi vì bảng này được sắp xếp theo sản phẩm, nên chúng ta có thể dễ dàng nhận thấy hàm Anchored COUNTIF đang hoạt động như thế nào. Mỗi sản phẩm sẽ có số thứ tự tăng dần, và khi một sản phẩm mới xuất hiện thì hàm sẽ tính toán lại từ đầu.

Ví dụ trên chỉ ra một đặc điểm chính của hàm là nó sẽ vẫn hoạt động cho dù bạn có sắp xếp lại các giá trị trong bảng.

rằng số lần xuất hiện của đối tượng có thể bị thay đổi sau khi sắp xếp lại. Ví dụ, sản phẩm Longhorn đứng thứ 3 lại đứng thứ 4 sau khi re-sort.

Khi nào thì nên sử dụng hàm Anchored COUNTIF?

Hãy coi hàm Anchored COUNTIF là bước đầu tiên để hiểu về các giá trị. Sau khi lập công thức, sẽ có bốn cách cơ bản giúp bạn tối ưu hóa đầu ra của hàm Anchored COUNTIF

• Xác định lần xuất hiện lớn nhất của đối tượng bất kỳ
• Xác định lần xuất hiện đầu tiên của đối tượng bất kỳ
• Xác định giá trị lớn nhất và nhỏ nhất của một đối tượng
• Đưa ra key tra cứu giá trị đầu tiên, lớn nhất và nhỏ nhất của đối tượng.

Lập công thức hàm Anchored COUNTIF là việc khá đơn giản. Để bắt đầu , bạn cần biết trường (phạm vi) mình muốn áp dụng nó.

Bảng chúng ta đang sử dụng chỉ ra dữ liệu bán hàng của rất nhiều sản phẩm. Mỗi hàng là một giao dịch, và với mỗi hàng chúng ta có giá bán (Sales Price) và loại sản phẩm (Product) đã bán.

Trong trường hợp này, chúng ta sẽ áp dụng hàm Anchored COUNTIF vào trường "Product" bởi nó là trường chứa các giá trị lặp lại.

Nhìn chung, hàm Anchored COUNTIF thực sự là một hàm COUNTIF đơn, không phải là hàm kết hợp. Nó có cú pháp như sau:

Range- phạm vi mà hàm sẽ tìm kiếm

Criteria- giá trị cụ thể mà hàm đang tìm kiếm (giá trị tiêu chuẩn)

Chúng ta sẽ áp dụng công thức hàm COUNTIF như một nền tảng của quá trình này.

: Bắt đầu viết công thức

Lựa chọn trường bạn muốn sắp xếp theo thứ tự

Gõ dấu hai chấm ":" để tạo một dãy

Chú ý rằng khi bạn gõ dấu hai chấm, thì một dãy giống hệt sẽ xuất hiện đằng sau dấu hai chấm. Điều bạn vừa làm là tạo một dãy tham chiếu trong Excel (bắt đầu và kết thúc là các ô bạn đã chọn). Với ô đầu tiên, điều này có vẻ không quan trọng, nhưng nó sẽ có nghĩa hơn khi bạn kéo công thức xuống để áp dụng với các ô khác.

: Thêm dấu phẩy để đóng Range

Chọn ô tiêu chuẩn là ô giống với ô gốc mà bạn đã tham chiếu

Đóng công thức bằng dấu ngoặc đơn

Quay trở lại công thức và cố định giá trị đầu tiên trong dãy

Đây là bước rất quan trọng bởi hàm Anchored COUNTIF sẽ không hoạt động được nếu không có nó.

Copy công thức xuống dưới để hoàn thành bảng

Cách dễ nhất để thực hiện điều này là nhấp đúp chuột vào dưới góc bên phải của ô bạn muốn copy.

Đăng ký ngay: Khóa học Excel tại nhà

Chìa khóa để hàm Anchored COUNTIF hoạt động đó là dãy tham chiếu của bạn đã được gắn (móc nối) với ô đầu tiên. Lưu ý rằng khi bạn kéo công thức xuống, công thức COUNITF sẽ mở rộng để bao hàm tất cả các đối tượng bạn đang tìm kiếm .

Điều bạn đang bảo Excel làm đó là chỉ ra giá trị này đã xuất hiện bao nhiêu lần. Đây chính là logic để tạo ra số lần xuất hiện các giá trị.

Ví dụ, hãy xem lần xuất hiện thứ tư của . Ta đang hỏi Excel là đã xuất hiện bao nhiêu lần. Công thức sẽ tự đếm mỗi số lần xuất hiện trước đó của . Vì vậy, kết quả trả về là 4.

Nếu bạn kéo xuống dòng tiếp theo thì tiêu chí sẽ đổi thành . Vì chưa có sự xuất hiện nào trước của , nên hàm sẽ tự đếm và để kết quả là 1.

Việc chỉ ra số lượng nhiều nhất của sản phẩm đã bán thật đơn giản sau khi ta đã áp dụng hàm Anchored COUNTIF. Chỉ cần áp dụng hàm MAX với các giá trị của hàm Anchored COUNTIF. Hàm này sẽ cho bạn biết số lần xuất hiện lớn nhất đạt được của một sản phẩm bất kì. Trong trường hợp này là số 5

Lần xuất hiện đầu tiên hoặc giao dịch đầu tiên luôn luôn là số "1" theo như giá trị của hàm Anchored COUNTIF. Vì vậy, điều bạn cần để xác định là tìm ra tất cả các đối tượng là số 1 trong cột này.

Học lập trình VBA trong Excel ở đâu?

Giả dụ,ta muốn xác định giá trị nhỏ nhất của sản phẩm.Về cơ bản, mỗi sản phẩm sẽ được giao dịch với giá thấp nhất. Điều cần làm là sắp xếp theo thứ tự tăng dần của cột dựa vào giá trị trong câu hỏi ( trong TH này là cột giá). (chọn trường bạn muốn sắp xếp, sử dụng phím tắt ALT → A → S → A).Bạn có thể làm điều này trước hoặc sau khi viết công thức hàm Anchored COUNTIF. Bởi vì nó được sắp xếp theo thứ tự tăng dần, nên lần xuất hiện đầu tiê ;n sẽ có giá thấp nhất của sản phẩm. Vì vậy, bạn có thể sử dụng logic tương tự như phần trên để tìm ra các dòng có "1" để xác định giá cả thấp nhất.

Ở ví dụ trước, chúng ta đã dùng trực quan để xác định các giá trị cụ thể. Chúng ta có thể sử dụng hàm Anchored COUNTIF rộng hơn bằng cách tạo ra một công thức tham chiếu. Việc bạn cần làm là tạo một key tra cứu.

Nếu bạn muốn biết top 3 sản phẩm bán chạy trong bảng này. Đầu tiên, hãy sắp xếp cột giá bán theo thứ tự giảm dần, vì vậy thứ tự xuất hiện của mỗi dòng sẽ khớp với sự sắp xếp của "Giá bán". Bây giờ chúng ta sẽ tạo một key tra cứu đơn giản, đó là nối giá trị của hàm với tên sản phẩm.

Sau khi key tra cứu được tạo, ta sẽ có một côt với các giá trị đặc biệt của mỗi giao dịch bán hàng, chúng được sắp xếp theo số lượng bán hàng. Bây giờ để đáp ứng yêu cầu,hãy lập ra một cái bảng đơn giản với sản phẩm và thứ hạng đã có và chạy công thức lookup để xác định top 3 giao dịch theo giá bán.

Hàm này rất có giá trị trong phân tích dữ liệu. Để tận dụng hết lợi ích của hàm, hãy học về các hàm lookup như và trong Excel

Đây chỉ là một trong rất nhiều hàm của Excel. Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH ... Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table ...