Không có gì lạ khi sử dụng Excel để tạo cơ sở dữ liệu nhỏ. Ví dụ: bạn có thể lưu giữ danh sách các thành viên câu lạc bộ những người nuôi chó xù của mình trong Excel hoặc bạn có thể sử dụng danh sách này để duy trì danh sách các địa chỉ liên hệ bán hàng đang hoạt động của mình. Trong những trường hợp đó, bạn có thể tự hỏi làm thế nào bạn có thể đếm được số lượng bản ghi đáp ứng nhiều hơn một tiêu chí.

Giả sử rằng bạn đang phân tích danh sách thành viên của mình và bạn muốn xác định số lượng bản ghi trong đó cột giới tính chứa “F”

và cột thành phố chứa một thành phố cụ thể, chẳng hạn như “Norwood”. Tất nhiên, điều này sẽ hữu ích vì nó sẽ trả lời câu hỏi nóng bỏng là có bao nhiêu thành viên nữ trong nhóm của bạn sống ở Norwood.

Excel bao gồm một số hàm trang tính tiện dụng để xác định số lượng bản ghi trong danh sách. Làm thế nào bạn có thể sử dụng chúng trong một tình huống mà hai tiêu chí phải được đáp ứng có thể không rõ ràng ngay lập tức.

Hãy xem xét sáu cách cụ thể để bạn có thể đạt được mục tiêu mong muốn của các thành viên nữ từ Norwood. (Giả sử rằng cột C là cột giới tính và cột F là cột thành phố.)

Cách đầu tiên để giải quyết vấn đề là sử dụng hàm COUNTIFS. Nếu cột giới tính của bạn là cột C và cột thành phố của bạn là cột F, bạn có thể sử dụng công thức sau:

=COUNTIFS(C1:C500,"F",F1:F500,"Norwood")

Nó tìm kiếm trong cột giới tính © cho bất kỳ ô nào chứa “F” và cột thành phố (F) cho bất kỳ ô nào chứa “Norwood.” Kết quả là số lượng bản ghi thỏa mãn cả hai tiêu chí.

Cách tiếp cận thứ hai là sử dụng hàm DCOUNTA. Chức năng này cho phép bạn xác định một tập hợp các tiêu chí và sử dụng các tiêu chí đó làm cơ sở để phân tích danh sách dữ liệu. Giống như tất cả các hàm dữ liệu trong Excel, DCOUNTA dựa trên ba tham số: phạm vi dữ liệu, cột để sử dụng trong so sánh và phạm vi tiêu chí. Để sử dụng hàm, hãy thiết lập bảng tiêu chí trong vùng không sử dụng trong trang tính của bạn. Ví dụ, bạn có thể thiết lập như sau trong các ô từ AA1 đến AB2: (Xem Hình 1.)

image

Hình 1. Bảng tiêu chí cho hàm DCOUNTA.

Sau đó, giả sử bảng dữ liệu ban đầu của bạn nằm trong ô A1: K500 (rõ ràng là một câu lạc bộ những người nuôi chó xù lớn), thì bạn có thể sử dụng cách sau để xác định số lượng:

=DCOUNTA(A1:K500,1,AA1:AB2)

Kết quả là số lượng đáp ứng các tiêu chí bạn đã chỉ định trong AA1: AB2.

Cũng lưu ý rằng tên bạn đã sử dụng trong AA1 và AB1 phải khớp chính xác với nhãn bạn đã sử dụng trong bản ghi bảng của mình. Khi làm như vậy, nội dung của cột Giới tính (cột C) phải là “F” và nội dung của cột Thành phố (cột F) phải là “Norwood” để bản ghi được thêm vào số lượng.

Giải pháp thứ ba là sử dụng công thức mảng để trả về một câu trả lời duy nhất.

Công thức mảng sử dụng một cách thú vị hàm SUM và một chút số học Boolean để xác định xem một bản ghi có được tính hay không.

Hãy xem xét những điều sau:

=SUM((C2:C500="F")*(F2:F500="Norwood"))

Chỉ cần nhập công thức trên vào một ô, sau đó hoàn tất bằng cách nhấn Ctrl + Shift + Enter; điều này cho phép Excel biết bạn đang nhập công thức mảng. Công thức hoạt động vì nó so sánh nội dung của từng hàng trong mảng, theo các tiêu chí được chỉ định trong công thức. Đầu tiên nó so sánh nội dung của cột C với “F”; nếu nó khớp, thì phép so sánh trả về True, là giá trị số 1.

Nội dung của cột F sau đó được so sánh với “Norwood”. Nếu so sánh đó là đúng, thì 1 được trả về. Do đó, 1 1 sẽ bằng 1 và giá trị này được thêm vào SUM của mảng. Nếu một trong hai phép so sánh là Sai, thì giá trị số 0 được trả về và 1 0 bằng 0 (cũng như 0 0 và 0 1), điều này không ảnh hưởng đến SUM đang chạy.

Cách tiếp cận thứ tư và có liên quan chặt chẽ là sử dụng hàm SUMPRODUCT, nhưng không sử dụng trong công thức mảng. Bạn chỉ cần sử dụng thông tin sau trong bất kỳ ô nào bạn muốn biết liệu hai tiêu chí có được đáp ứng hay không:

SUMPRODUCT((C2:C500="F")*(F2:F500="Norwood"))

Hãy nhớ rằng đây không phải là công thức mảng, vì vậy bạn không cần nhấn Ctrl + Shift + Enter. Một lần nữa, công thức lại hoạt động nhờ sự kỳ diệu của toán học Boolean.

Giải pháp khả thi thứ năm, thủ công hơn một chút so với những giải pháp đã được thảo luận, là sử dụng tính năng AutoFilter cùng với một tổng phụ. Giả sử bản ghi dữ liệu của bạn là A1: K500, với các nhãn cột ở hàng 1, bạn sẽ làm theo các bước sau:

  1. Chọn bất kỳ ô nào trong bảng dữ liệu.

  2. Hiển thị tab Dữ liệu của dải băng.

  3. Nhấp vào công cụ Bộ lọc trong nhóm Sắp xếp & Bộ lọc. Excel sẽ hiển thị các chỉ báo thả xuống của Bộ lọc Tự động bên cạnh mỗi nhãn cột trong hàng 1.

  4. Sử dụng chỉ báo thả xuống cho cột giới tính (cột C), chọn F. Danh sách của bạn được lọc tự động để chỉ hiển thị các thành viên nữ.

  5. Sử dụng chỉ báo thả xuống cho cột thành phố (cột F), chọn Norwood. Danh sách của bạn được tự động hiển thị để chỉ hiển thị các thành viên nữ sống ở Norwood.

  6. Ở cuối bảng dữ liệu (hàng 501), trong bất kỳ cột nào bạn muốn, hãy nhập công thức sau:

=SUBTOTAL(3,C2:C500)

Công thức này làm cho hàm SUBTOTAL áp dụng hàm COUNTA để tính tổng phụ. Nói cách khác, nó trả về tổng số tất cả các bản ghi được hiển thị bởi bộ lọc; đây là số lượng mong muốn.

Cách tiếp cận thứ sáu là sử dụng thuật sĩ tính tổng có điều kiện để đưa ra công thức cho bạn. (Trình hướng dẫn tính tổng có điều kiện có sẵn dưới dạng bổ trợ Excel cho Excel 2007 và các phiên bản cũ hơn; nó được bật trên hầu hết các hệ thống theo mặc định. Nó đã bị xóa khỏi Excel 2010.) Hãy làm theo các bước sau để sử dụng trình hướng dẫn tính tổng có điều kiện:

  1. Chọn một ô ở đâu đó trong dữ liệu bạn muốn phân tích.

  2. Hiển thị tab Công thức của ruy-băng.

  3. Trong nhóm Giải pháp (bên phải của ruy-băng), bấm Tổng có điều kiện. Excel hiển thị bước đầu tiên của Trình hướng dẫn tính tổng có điều kiện. Toàn bộ dữ liệu của bạn sẽ được hiển thị trong hộp thoại. (Xem Hình 2.)

  4. Nhấp vào Tiếp theo. Excel sẽ hiển thị bước tiếp theo của trình hướng dẫn.

  5. Trong danh sách thả xuống Cột đến Tổng, hãy chọn Giới tính.

  6. Trong menu thả xuống Cột, hãy chọn Giới tính.

  7. Trong menu thả xuống Là, hãy chọn dấu bằng.

  8. Trong menu thả xuống Giá trị này, chọn F.

  9. Nhấp vào Thêm. Điều kiện bạn chỉ định bây giờ xuất hiện trong hộp thoại.

  10. Trong menu thả xuống Cột, chọn Thành phố.

  11. Trong menu thả xuống Là, hãy chọn dấu bằng.

  12. Trong menu thả xuống Giá trị này, chọn Norwood.

  13. Nhấp vào Thêm. Điều kiện thứ hai bây giờ xuất hiện trong hộp thoại.

  14. Nhấp vào Tiếp theo. Excel hiển thị bước thứ ba của trình hướng dẫn.

  15. Chọn chỉ sao chép công thức vào một ô duy nhất.

  16. Nhấp vào Tiếp theo. Excel hiển thị bước thứ tư (và cuối cùng) của trình hướng dẫn.

  17. Trong trang tính, hãy bấm vào ô bạn muốn chứa kết quả của công thức.

  18. Nhấp vào Kết thúc.

Kết quả là một công thức, phù hợp với các điều kiện bạn đã chỉ định, trong ô bạn đã chọn ở bước 1.

Không nghi ngờ gì nữa, bạn có thể sử dụng vô số giải pháp khả thi khác để tìm ra số lượng bản ghi. Tuy nhiên, đây là “lựa chọn của rất nhiều” và cho phép bạn xác định câu trả lời một cách nhanh chóng và dễ dàng.

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

Mẹo này (7759) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: