Steven sử dụng Excel cho cơ sở dữ liệu phim được cá nhân hóa. Trong một cột, anh ta có xếp hạng cho mỗi phim theo thang điểm từ một đến bốn, được xác định bằng cách sử dụng một, hai, ba hoặc bốn dấu hoa thị. Anh ấy muốn thêm thông tin cho biết anh ấy có bao nhiêu phim ”, anh ấy có bao nhiêu phim ‘*’, v.v.

Steven đã tìm ra cách đếm số lượng phim ”, nhưng khi anh ấy cố gắng đếm xem có bao nhiêu phim ‘*’ trong cột thì con số này bị sai.

Có nhiều cách để xử lý vấn đề này, nhưng trước tiên hãy nhận xét về việc lựa chọn dấu hoa thị cho một mục đích như sau: Trong lược đồ tổng thể của những thứ là Excel, dấu hoa thị có nhiều mục đích. Nó được sử dụng thường xuyên nhất trong các công thức dưới dạng ký hiệu nhân và hầu như thường xuyên, như một ký tự đại diện trong nhiều đối số công thức. Vì lý do này, không phải là ý kiến ​​hay nếu sử dụng dấu hoa thị cho các mục đích khác, đặc biệt là đối với các mục mà bạn muốn đếm, cũng như xếp hạng phim. Có thể tốt hơn, trong trường hợp này, chỉ cần sử dụng từ số 1 đến số 4 cho xếp hạng, bởi vì các con số rất dễ làm việc và rõ ràng trong cách sử dụng chúng.

Nếu bạn phải sử dụng dấu hoa thị, thì có một số cách bạn có thể kết hợp công thức để thực hiện số đếm. Hàm SUMPRODUCT sẽ hoạt động tốt. Giả sử các dấu hoa thị nằm trong cột C, bạn có thể sử dụng như sau:

=SUMPRODUCT(--(C:C="*"))

=SUMPRODUCT(--(C:C="**"))

=SUMPRODUCT(--(C:C=""))

=SUMPRODUCT(--(C:C="*"))

Lưu ý việc sử dụng hai dấu trừ trong mỗi công thức này. Cách sử dụng này được gọi một cách chính xác nhất là “một ngôi sao kép” (mọt sách, đúng không?)

và được sử dụng để buộc các kết quả Đúng / Sai thành số tương đương (1/0).

Điều này là cần thiết vì một công thức như C: C = “*” trả về True hoặc False và SUMPRODUCT yêu cầu giá trị số. Nếu không có sự chuyển đổi bắt buộc của hàm đơn phân kép, hàm SUMPRODUCT sẽ trả về 0 mọi lúc.

Bạn cũng có thể sử dụng SUMPRODUCT khác một chút để đơn giản kiểm tra độ dài của bất kỳ thứ gì trong cột C. Cách tiếp cận này hoạt động tốt nếu C chỉ chứa dấu hoa thị, nhưng cũng sẽ hoạt động nếu bạn sử dụng thứ gì đó khác với dấu hoa thị:

=SUMPRODUCT(--(LEN(C:C)=1))

=SUMPRODUCT(--(LEN(C:C)=2))

=SUMPRODUCT(--(LEN(C:C)=3))

=SUMPRODUCT(--(LEN(C:C)=4))

Bạn cũng có thể sử dụng hàm SUM thẳng, nhưng các công thức sau phải được nhập bằng Ctrl + Shift + Enter. (Chúng là công thức mảng.)

=SUM(IF(C:C="*",1,0))

=SUM(IF(C:C="**",1,0))

=SUM(IF(C:C="",1,0))

=SUM(IF(C:C="*",1,0))

Lưu ý rằng các ví dụ cho đến nay sử dụng các hàm tính tổng, cụ thể là hàm SUMPRODUCT và SUM. Các hàm này không có vấn đề đã đề cập trước đó là hiểu nhầm dấu hoa thị. Tuy nhiên, bạn gặp phải vấn đề khi sử dụng các hàm đếm. Ví dụ: điều sau sẽ không cho kết quả mong muốn:

=COUNTIF(C:C,"*")

Dấu hoa thị hoạt động như một ký tự đại diện, khớp với bất kỳ thứ gì trong ô. Do đó, bạn sẽ đếm được tất cả các ô trong cột C có chứa bất kỳ thứ gì. Bạn có thể chỉ định rằng bạn muốn dấu hoa thị được coi là một ký tự chữ (thay vì là ký tự đại diện) bằng cách đặt trước nó bằng dấu ngã, theo cách này:

=COUNTIF(C:C,"~*")

Khi nói đến hai dấu hoa thị, bạn có thể nghĩ rằng điều này sẽ hiệu quả:

=COUNTIF(C:C,"~**")

Nó sẽ không; Excel hiểu điều này là “một dấu hoa thị theo nghĩa đen duy nhất theo sau bởi bất kỳ thứ gì.” Nói cách khác, dấu hoa thị đầu tiên là chữ và dấu hoa thị thứ hai vẫn là ký tự đại diện. Mỗi dấu hoa thị phải được đặt trước dấu ngã, theo cách này:

=COUNTIF(C:C,"~*")

=COUNTIF(C:C,"~~")

=COUNTIF(C:C,"~~~*")

=COUNTIF(C:C,"~~~~")

Cuối cùng, nếu bạn muốn bỏ qua việc sử dụng tất cả các công thức cùng nhau, bạn có thể tạo PivotTable tham chiếu đến cột xếp hạng cho phim của bạn.

Nếu bạn sử dụng cột đó như một hàng trong PivotTable và thay đổi phương pháp tổng hợp để nó đếm nội dung của các ô trong cột, bạn có thể nhận được một bản tóm tắt đẹp về số lượng từng loại xếp hạng mà bạn đã trao cho phim.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12849) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.