Hướng dẫn cách sử dụng hàm COUNTIF để đếm số lần xuất hiện trong Excel

G-LEARNING
18 Oct 2017

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àm Anchored COUNTIF là gì?

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

Hàm Anchored COUNTIF được sử dụng điển hình trong các dữ liệu liên quan đến số lần xuất hện. Để áp dụng nó một cách hiệu quả, bạn cần tạo một trường mới và copy công thức vào bảng. Khi hàm Anchored COUTIF hoạt động, nó sẽ cho ra kết quả là số lần xuất hiện theo thứ tự của một đối tượng nào đó trong phạm vi trường mà bạn tham chiếu.

Hãy xem ví dụ dưới đây

cách sử dụng hàm countif để đếm số lần xuất hiện

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.

Dưới đây là một ví dụ khác, lần này các giá trị trong bảng được sắp xếp theo giá (sales price). Trong trường hợp này sẽ khó để nhìn ra, nhưng hàm Anchored COUNTIF vẫn đang cho ra cùng một kết quả- số lần xuất hiện theo thứ tự của mỗi sản phẩm.

cách sử dụng hàm countif cố định điểm đầu để đếm số lần xuất hiện

Xem thêm: Excel 2003

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.

Lưu ý 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.

Bạn sẽ được học cách tối ưu các cách trên một cách chi tiết, bây giờ hãy cùng xem cách lập công thức như thế nào

Làm thế nào để lập công thức Anchored COUNTIF?

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ó.

Sử dụng dữ liệu trong bảng data phía trên. Hàm này sẽ giúp ích nhất trong trường hợp nó  làm việc với dữ liệu giao dịch ( nơi các dữ liệu có liên quan có thể sẽ được lặp lại) thay vì dữ iệu tóm tắt (nơi dữ liệu sẽ được tổng hợp tóm tắt và chỉ một vài đối tượng đặc biệt mới được xuất hiệ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.

bảng dữ liệu mẫu cần đếm số lần bán được của từng sản phẩm

Cú pháp hàm COUNTIF
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ước 1: Bắt đầu viết công thức

cách sử dụng hàm countif để đếm với vùng mở rộng dần

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

cách sử dụng hàm countif

Bước 3: 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.

xác định vùng cần đếm trong hàm countif

Bước 4: Thêm dấu phẩy để đóng Range

cách sử dụng hàm countif để đếm số lần xuất hiện tăng dần

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

hàm countif đếm số lần xuất hiện tăng dần

Bước 6: Đóng công thức bằng dấu ngoặc đơn

cách viết hàm countif

Bước 7: QUAN TRỌNG: 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ó.

cách cố định tọa độ điểm đầu khi đếm bằng hàm countif

Bước 8: 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.

kết quả hàm countif đếm với vùng đếm được mở rộng dần

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

Nó hoạt động như thế nào?

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 Nitro. Ta đang hỏi Excel là Nitro đã 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 Nitro. Vì vậy, kết quả trả về là 4.

phương pháp anchored countif đếm số lần xuất hiện trong vùng đếm mở rộng dần

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

phương pháp anchored countif đếm số lần xuất hiện trong vùng đếm mở rộng dần

Làm thế nào để sử dụng hàm Anchored COUNTIF?

Xác định số lần xuất hiện lớn nhất của đối tượng bất kỳ

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

dùng hàm MAX kiểm tra mặt hàng bán được nhiều lần nhất

Xác định lần xuất hiện đầu tiên của đối tượng bất kì

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.

xác định tại thời điểm lần đầu tiên xuất hiện của các mặt hàng

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

Xác định giá trị lớn nhất hoặc giá trị nhỏ nhất của một đối tượng (yêu cầu đã được sắp xếp hợp lý)

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.

xác định các mặt hàng không trùng lặp với hàm countif

Cung cấp key tra cứu giá trị đầu tiên, lớn nhất và nhỏ nhất của một đối tượng.

Ở 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.

tạo ra giá trị tìm kiếm không trùng lặp

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.

sử dụng hàm Index Match tìm theo giá trị không trùng lặp

Kết luậ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ư INDEX MATCHLOOKUP  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 …

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Gitiho.com. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Gitiho.com

Đánh giá bài viết này

Gitiho.com - Learning, Hiring, Working
Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển Gitiho.com đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án...
Gitiho tự hào khi được đồng hành cùng:
  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Gitiho TẠI ĐÂY



@ 2020 - Bản quyền của Công ty TNHH Công Nghệ Giáo Dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội