HƯỚNG DẪN SỬ DỤNG HÀM ANCHORED COUNTIF (đếm số lần xuất hiện) TRONG EXCEL

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

Xem thêm : Excel 2003

Ví dụ trên chỉ ra một đặc thù chính của hàm là nó sẽ vẫn hoạt động giải trí mặc dầu bạn có sắp xếp lại những 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 tiên phong để hiểu về những 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 những cách trên một cách cụ thể, giờ đây 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 thuần. Để mở màn, bạn cần biết trường ( khoanh vùng phạm vi ) mình muốn vận dụng nó .
Sử dụng tài 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ó thao tác với tài liệu thanh toán giao dịch ( nơi những tài liệu có tương quan hoàn toàn có thể sẽ được lặp lại ) thay vì dữ iệu tóm tắt ( nơi tài liệu sẽ được tổng hợp tóm tắt và chỉ một vài đối tượng người tiêu dùng đặc biệt quan trọng mới được xuất hiện )
Bảng tất cả chúng ta đang sử dụng chỉ ra tài liệu bán hàng của rất nhiều loại sản phẩm. Mỗi hàng là một thanh toán giao dịch, và với mỗi hàng tất cả chúng ta có giá bán ( Sales Price ) và loại mẫu sản phẩm ( Product ) đã bán .
Trong trường hợp này, tất cả chúng ta sẽ vận dụng hàm Anchored COUNTIF vào trường “ Product ” bởi nó là trường chứa những 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 – khoanh vùng phạm vi mà hàm sẽ tìm kiếm
Criteria – giá trị đơn cử mà hàm đang tìm kiếm ( giá trị tiêu chuẩn )
Chúng ta sẽ vận dụng công thức hàm COUNTIF như một nền tảng của quy 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 ( khởi đầu và kết thúc là những ô bạn đã chọn ). Với ô tiên phong, điều này có vẻ như 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 để vận dụng với những ô 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 giải trí đượ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 để triển khai đ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 giải trí đó là dãy tham chiếu của bạn đã được gắn ( móc nối ) với ô tiên phong. Lưu ý rằng khi bạn kéo công thức xuống, công thức COUNITF sẽ lan rộng ra để bao hàm tổng thể những đối tượng người tiêu dù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 những 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 mẫu sản phẩm đã bán thật đơn thuần sau khi ta đã vận dụng hàm Anchored COUNTIF. Chỉ cần vận dụng hàm MAX với những 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 loại 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 tiên phong hoặc thanh toán giao dịch tiên phong 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 lập là tìm ra tổng thể những đối tượng người dùng là số 1 trong cột này .

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 lập giá trị nhỏ nhất của loại sản phẩm. Về cơ bản, mỗi mẫu sản phẩm sẽ được thanh toán 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 hoàn toà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 tiên phong sẽ có giá thấp nhất của mẫu sản phẩm. Vì vậy, bạn hoàn toàn có thể sử dụng logic tương tự như như phần trên để tìm ra những dòng có “ 1 ” để xác lập 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, tất cả chúng ta đã dùng trực quan để xác lập những giá trị đơn cử. Chúng ta hoàn toàn 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 loại sản phẩm cháy khách 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, thế cho nên 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ờ tất cả chúng ta sẽ tạo một key tra cứu đơn thuần, đó là nối giá trị của hàm với tên mẫu sản phẩm .

Sau khi key tra cứu được tạo, ta sẽ có một côt với những giá trị đặc biệt quan trọng của mỗi thanh toán 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ờ để cung ứng nhu yếu, hãy lập ra một cái bảng đơn thuần với loại sản phẩm và thứ hạng đã có và chạy công thức lookup để xác lập top 3 thanh toán giao dịch theo giá cả .

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. Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm 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 kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online

Source: https://bem2.vn
Category: TỔNG HỢP