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 giao dịch.Để á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

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.

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.

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

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

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.

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

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

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

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

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.

Đă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.

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.

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

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.

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.

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.

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.

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 MATCH và LOOKUP  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 Học Excel Online. 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: HocExcel.Online