Đếm các ô có phân biệt chữ hoa chữ thường

Tháng Hai 28 14:45 2019 Viết bởi tác giả Hoàng Quốc Uy

Để đếm số lượng từng sản phẩm theo tên có phân biệt chữ hoa và chữ thường thì bạn cần phải dùng phối kết hợp hai hàm SUMPRODUCT và EXACT, ví dụ như trong hình dưới đây:

CÔNG THỨC CHUNG

=SUMPRODUCT((–EXACT(value, range))

Trong đó:

+ value là giá trị cụ thể hay địa chỉ của Ô chứa giá trị cần so sánh để đếm; Còn range là tên cột chứa giá trị cần đếm;

+ SUMPRODUCT là hàm có chức năng nhân các thành phần có thứ tự tương ứng của các mảng với nhau, cuối cùng thì cộng các kết quả đó với nhau.

++ Cú pháp của hàm SUMPRODUCT:

SUMPRODUCT(mảng 1, mảng 2, …, mảng n)

++ Ví dụ: = SUMPRODUCT({3;4;8;6;1;9},{2;7;6;7;5;3})

Công thức trên sẽ được tính như sau: 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3 = 6 + 28 + 48 + 42 + 5 + 27 = 156

+ Hàm EXACT dùng để so sánh hai chuỗi văn bản và trả về TRUE nếu chúng hoàn toàn giống nhau, FALSE nếu khác. Hàm EXACT phân biệt chữ hoa, chữ thường nhưng bỏ qua khác biệt về định dạng.

++ Dùng dấu — trước hàm EXACT để chuyển các giá trị logic về dạng 1′s và 0′s hay 1; và 0 (trong đó TRUE = 1 và FALSE =0);

Và công thức được sử dụng như trong hình vẽ trên.

GIẢI THÍCH CỤ THỂ CÁCH MÀ EXCEL XỬ LÝ VỚI HÀM Ở Ô E5:

+ Đầu tiên nó sẽ xử lý hàm EXACT, cụ thể nó sẽ so sánh giá trị ở ô D5 với lần lượt các ô thuộc vùng dữ liệu lần lượt từ B5 đến B11, cách thức xử lý được mô tả dưới đây:

=EXACT(“ayako”,”ayako”) // trả về giá trị TRUE
=EXACT(“ayako”,”Ayako”) // trả về giá trị FALSE

+ Cứ xử lý như vậy và nó trả về kết quả là một tập hợp, cụ thể:

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

+ Tiếp theo đó, công thức:

–{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

Trả về giá trị là một tập hợp dạng số 1 và 0, cụ thể:

{0;0;0;0;1;1;1}

+ Cuối cùng, vì công thức SUMPRODUCT({0;0;0;0;1;1;1}) chỉ có duy nhất 1 tập hợp nên nó bỏ qua phép toán nhân các phần tử của các tập hợp với nhau, mà nó tiến hành phép toán cộng luôn:

=> Kết quả sẽ là: 3