John có một trang tính chứa các bản ghi được sử dụng trong hệ thống theo dõi chi phí. Số bản ghi được nhập vào cột A, vị trí trong cột B và chi phí trong cột C. Không phải tất cả bản ghi đều có giá trị chi phí được nhập vào cột C. John muốn xác định số lượng bản ghi “có vị trí X và chi phí <> 0”.

Động lực đầu tiên của bạn có thể là sử dụng một trong các hàm trang tính được thiết kế để đếm, chẳng hạn như CountIf. Vấn đề duy nhất là CountIf không cho phép kiểm tra hai điều kiện khi tính toán một giải pháp. Tuy nhiên, có một số giải pháp bạn có thể sử dụng mà không cần thêm cột bổ sung hoặc tính toán trung gian.

Giải pháp đầu tiên (và có lẽ là đơn giản nhất) là sử dụng hàm trang tính SUMPRODUCT. Hàm này cho phép bạn đếm hoặc tính tổng dữ liệu từ một cột, hàng hoặc mảng với bao nhiêu tiêu chí bạn muốn. Cú pháp cơ bản như sau:

=SUMPRODUCT( (CONDITION1)  (CONDITION2)  (CONDITION3) * (DATACELLS) )

Trong trường hợp cụ thể này, bạn có thể đặt công thức lại với nhau như sau:

=SUMPRODUCT((B2:B101="X")*(C2:C101>0))

Điều này làm là cung cấp hai điều kiện khác nhau được kiểm tra.

Đầu tiên, các ô trong cột B được kiểm tra để xem chúng có bằng “X” hay không, sau đó các ô tương ứng trong cột C được kiểm tra xem chúng có bằng 0. Cả hai điều kiện đều trả về True (1) hoặc False (0). Sau đó, các kết quả này được nhân với nhau, dẫn đến 1 hoặc 0. Sau đó, hàm SUMPRODUCT cộng chúng lại với nhau, dẫn đến số lượng tích lũy.

Một giải pháp khác là tạo một công thức mảng sẽ thực hiện việc tính toán cho bạn. Công thức mảng khác với công thức thông thường, ở chỗ chúng hoạt động trên một số ô, lặp đi lặp lại chúng để tạo ra kết quả. Hãy xem xét công thức sau:

=(B2="X")*(C2>0)

Điều này trả về một giá trị duy nhất, 1 hoặc 0. Công thức sử dụng cùng một logic cơ bản được mô tả trong phần giải thích trước đó của giải pháp SUMPRODUCT. Hai phép so sánh logic trả về 1 hoặc 0, được nhân với nhau, kết quả là 1 hoặc 0 là một câu trả lời. Bây giờ, hãy xem xét công thức sau:

=SUM((B2:B101="X")*(C2:C101>0))

Công thức này bây giờ trông rất giống với công thức SUMPRODUCT trước đó, nhưng nó sẽ không hoạt động đúng như một công thức thẳng. Điều này là do SUM không được thiết kế để hoạt động theo kiểu lặp đi lặp lại trên một phạm vi ô. Nếu bạn nhập công thức này dưới dạng công thức mảng (nhấn Shift + Ctrl + Enter để nhập), thì Excel hiểu rằng bạn muốn làm việc lần lượt qua từng phạm vi để tính tổng cuối cùng, đó là tổng số các bản ghi. đáp ứng các tiêu chí đã nêu.

Các cách khác nhau mà bạn có thể sử dụng công thức mảng là một chủ đề khá rộng.

Để biết thêm thông tin về cách hoạt động của công thức mảng, hãy xem các vấn đề khác của _WordTips, _ hoặc tham khảo trang Web sau:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Tùy chọn thứ ba là sử dụng các hàm trang tính cơ sở dữ liệu để trả về số lượng. Sử dụng các tiêu chí này, bạn thiết lập một “bảng tiêu chí” trong trang tính của mình và sau đó hàm sử dụng các tiêu chí để phân tích các bản ghi. Các bước sau giả định rằng các nhãn cột cho ba cột là RecNum, Location và Cost:

  1. Tìm một vài ô trống, trên cùng một trang tính với bản ghi của bạn hoặc trên một trang tính khác. (Vì lợi ích của ví dụ này, tôi giả sử bạn đang sử dụng cột J và K.)

  2. Trong ô J1, nhập từ Vị trí.

  3. Trong ô K1, nhập từ Chi phí.

  4. Trong ô J2, nhập X.

  5. Trong ô K2, nhập> 0. Bây giờ bạn đã nhập bảng tiêu chí của mình vào các ô J1: K2.

  6. Chọn các ô J1: K2.

  7. Chọn Tên từ menu Chèn, sau đó chọn Xác định. Excel sẽ hiển thị hộp thoại Xác định Tên. (Xem Hình 1.)

  8. Nhập tên Tiêu chí, sau đó bấm OK.

  9. Trong ô mà bạn muốn có số lượng bản ghi đáp ứng tiêu chí của mình, hãy nhập như sau:

=DCOUNT(B1:C101,2,Criteria)

Lưu ý rằng đối số đầu tiên được sử dụng với DCOUNT là cột thứ hai và thứ ba trong danh sách bản ghi của bạn. Đối số này cũng bao gồm các nhãn cột, các nhãn này cần thiết để DCOUNT có thể xác định vị trí phù hợp tiêu chí từ bảng tiêu chí (đối số thứ ba).

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

Mẹo này (2815) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.