Trên Google Sheets, chúng ta không thể sử dụng COUNTIF để bỏ qua các hàng ẩn. Thay vào đó, chúng ta cần sử dụng COUNTIFS. Để hiểu rõ hơn, hãy cùng tìm hiểu cách sử dụng COUNTIFS để bỏ qua các hàng ẩn trong Google Sheets.
Bắt đầu với COUNTIFS và việc loại bỏ các hàng ẩn trong Google Sheets
Bạn có biết có bao nhiêu cách để ẩn một hàng hoặc nhiều hàng trên Google Sheets?
Thông qua bài viết này, chúng ta sẽ tìm hiểu về 4 cách tiêu chuẩn để ẩn hàng trên Google Sheets:
- Chọn các hàng muốn ẩn, nhấn chuột phải và chọn “Hide rows 6 – 7” (hoặc bất kỳ số hàng nào).
- Nhấp vào menu “Data”, chọn “Create a filter”, không chọn “Victor” và “Ralph”, sau đó nhấp “OK”.
- Chọn các hàng muốn ẩn, nhấn chuột phải và chọn “View more row actions” và “Group rows 6 – 7”, sau đó bật hoặc tắt nút + hoặc -.
- Sử dụng Slicer thông qua menu “Data” và “Add a Slicer”.
Sau khi ẩn các hàng bằng bất kỳ cách nào trên, chúng ta sẽ sử dụng công thức SUBTOTAL trong ô C13:
=SUBTOTAL(103,C2:C11)Công thức trên sẽ trả lại kết quả là 8. Kết quả này chính là số hàng được tính toán trong danh sách, bỏ qua các hàng đã bị ẩn.
Chúng ta sẽ sử dụng cùng phương pháp SUBTOTAL trong việc tính số lượng bỏ qua các hàng ẩn khi sử dụng COUNTIFS.
Cách tính số lượng bỏ qua các hàng ẩn trong COUNTIFS
Trong ví dụ, giả sử các bạn muốn tính số lượng các giá trị trong cột D, lớn hơn hoặc bằng 3000. Đầu tiên, hãy xem công thức COUNTIF thông thường:
=COUNTIF(D2:D11, ">=3000")Đây là công thức COUNTIFS tương ứng:
=COUNTIFS(D2:D11, ">=3000")Bây giờ, chúng ta sẽ chỉnh sửa công thức COUNTIFS này để bỏ qua các hàng ẩn.
Cú pháp của hàm COUNTIFS như sau:
COUNTIFS(criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...])Trong công thức trên, chúng ta sẽ sử dụng “criteria_range1” là D2:D11 cho công thức SUBTOTAL. Nếu có hai cột (criteria_range1 và criteria_range2), bạn có thể chọn một trong các cột để thực hiện kiểm tra.
Công thức SUBTOTAL này có thể là một mảng hoặc một công thức không phải mảng. Tiếp theo, chúng ta sẽ xem ví dụ về COUNTIFS bỏ qua các hàng ẩn.
1. Cách sử dụng cột trợ giúp
Hãy làm theo các bước sau:
- Hiển thị các hàng đã bị ẩn (nếu có).
- Đặt công thức SUBTOTAL này vào ô E2 và sao chép nó đến ô E11:
=SUBTOTAL(103,D2)- Đặt công thức COUNTIFS này vào ô F2:
=COUNTIFS(D2:D11, ">=3000", E2:E11, "=1")Công thức COUNTIFS trên sẽ bỏ qua các hàng ẩn trong kết quả.
2. COUNTIFS bỏ qua các hàng ẩn mà không cần cột trợ giúp
SUBTOTAL là một trong số các hàm không mở rộng trên Google Sheets. Trong trường hợp này, chúng ta sẽ sử dụng hàm LAMBDA.
Nếu bạn không muốn kéo công thức SUBTOTAL trong ô E2 xuống, bạn có thể sử dụng công thức MAP sau đây trong ô E2:
=MAP(D2:D11, LAMBDA(row, SUBTOTAL(103, row)))Lưu ý: Trước khi nhập công thức MAP vào ô E2, bạn phải xóa tất cả các giá trị trong ô E2:E11, bao gồm cả các hàng đã ẩn. Nếu có công thức SUBTOTAL không phải mảng nào đó trong ô E2:E11, việc này sẽ gây ra lỗi #REF!
Công thức MAP này sẽ lặp lại từng giá trị trong “array1”, từ đó trả lại kết quả mảng SUBTOTAL.
Có thể sử dụng công thức trên trong COUNTIFS để bỏ qua các hàng ẩn.
Tài nguyên
Dưới đây là một số tài nguyên liên quan:
- Google Sheets Query Hidden Row Handling with Virtual Helper Column
- SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column]
- How to Omit Hidden or Filtered out Values in Sum [Google Doc Spreadsheet]
- Vlookup Skips Hidden Rows in Google Sheets – Formula Example
- Check Whether a Row Is Hidden and Highlight the Row above It in Google Sheets
- Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets
- Subtotal with Condition in Google Sheets [Step by Step Guide]
- Grouping and Subtotal in Google Sheets and Excel
- How to Subtotal Up to the First Blank Cell in a Column in Google Sheets
- Subtotal Function With Conditions in Excel and Google Sheets
Viết bởi Crawlan.com.
